wetmatter nonsense

Needing some sort of variation or at least a break from my certification studies I decided to revisit a title I purchased a while back: "Beginning SQL Server 2008 for Developers" from @Apress written by Robin Dewson. Truly glad I did because one of the problems I have when illustrating data related issues to clients is having to piece together a bloody spreadsheet. I typically would query the tables then query the INFORMATION_SCHEMA.COLUMNS or sys.columns to get the column names to use as headers. Then I would copy and paste them into the predetermined rows.

Well not any more because page 42-43 of this awesome book had life changing screenshots of the Options dialog. Big deal right? Well a big deal for me at least! Following the path of Query Results --> SQL Server --> Results to Grid lead me to a sudden state of euphoria. There I saw it as the lights of heaven filled my home, blurring everything around me as if a spotlight and magnify focused in on the wonderful words that read: "Include column headers when copying or saving the results". Was it really true, are my days of querying sys.columns or the INFORMATION_SCHEMA.COLUMNS finally over? In a way, maybe, not really, but at the very least delivering requested spreadsheets to clients will be a bit faster. I was so excited I tried this on MSSQL 2005 and sure enough it is there!

What you need to do is: 

Step 1 - Go to Tools --> Options

Step 2 - Expand Query Results --> SQL Server --> Results to Grid

Then check the box titled: Include column headers when copying or saving the results

Step 3 - Copy some cells

If you have tabs opened already, open a new query window and try it. You should see the menu item "Copy with Headers" as a right click option

Step 4 - Then paste your results into the spreadsheet

Just a side note, if you are dealing with datetime data types you should consider making the predetermined columns in the spreadsheet as a text category by formatting the cells, under the Number tab select the "Text" category.

Cover image taken from Apress.

Loading mentions Retweet

A few friends of mine asked me how to hide tagged photos and videos of them on facebook. Well there is a way, but it does not necessarily hide the photos of you entirely. It just hide them from the people who are visiting your profile who happen to be looking through your photos. However if someone else (a friend or stalker) has a photo of you in one of their albums and they tag you then that photo will still be visible to the friends of the person who tagged you. Also depending on their privacy settings your photo can be available to nearly anyone for that matter. Yea... instant FAME! Unless it is a non-flattering picture of course. Then we would have to repost it everywhere to amuse other people. See what are friends for! So in short you are only hiding them from being viewed on your profile. With that said if you still want to "pseudo" hide them, then this is how you would do it.

Loading mentions Retweet

I was in need of creating a retweet bot that would find all instances of #sqlsatphx in twitter and retweet the specific post by changing the hash tag from #sqlsatphx to #sqlsat47. Not knowing exactly how to accomplish this I turned to google. There I found a video that was helpful, but the overall special effects of the video made it difficult to follow. I won't get into the details, but you can see for yourself. I am not knocking them in anyway, but I would have suggested less of the special effects. Here is their link. http://twitter.hyveup.tv/2009/07/how-to-create-auto-retweet-twitter-bot.html. I am basically following their steps with a few modifications that I felt were necessary to accomplish my desired end results. 

Well to give you an example of what I was looking to do, here is a post that i was targeting:

And this was my expected end results:

Create your twitter account

This is my targeted conversation I want to RT and post with my account.

Get twitter atom search string

Creating a pipe via Yahoo Pipes

Create a TwitterFeed.com account

Visit twitterfeed.com sign-in or signup for an account. Once that is all said and done you will need to name the feed and paste the RSS URL that you copied earlier and into the RSS Feed URL field.

Then be sure to associate the twitterfeed to your twitter account and DO NOT authenticate using the OAuth option. Type in the username and password.

Once you setup your setting like in the screenshot above, click the Continue to step 2 button and on Step 2 is where you will setup the twitter service.

Once that is all setup it will take approximately 30 mins before it gets posted on twitter.

End results...

Loading mentions Retweet

Well recently I needed a query that would display all the table names along with a column count so I just tossed this one together. I did some surfing around and I noticed that some folks have turned queries very similar to this into a function which might be the route that I pursue as well. However this is just a simple SELECT statement as you can see below.

 
SELECT	t.name         AS [Table Name] 
        ,COUNT(c.name) AS [Column Count] 
FROM sys.tables t 
        JOIN sys.columns c on t.object_id = c.object_id 
GROUP BY t.name 
ORDER BY t.name 

Here are the results I ran this query against the Reporting Services database since I didn't have the AdventureWorks readily available.

Here is a look at the Estimated Execution Plans

Here is the Actual Execution Plans

Hopefully you may find this helpful and if you did here is the script... cheers!

Click here to download:

SELECT.GetTableNameAndColumnCount_v0.01.sql (0 KB)

Loading mentions Retweet

     

Click here to download:

Wild_Ahi_Sliders_tags_true_foo.zip (1177 KB)

The Wild Ahi Sliders from True Food Kitchen come with wasabi, radishes, cucumber and a sesame brioche. Don't be mislead, the wasabi is merely a nice little portion which gives you a perfect essence, no epic nasal burns here. I ordered mine with the sweet potato hash which was absolutely amazing and the Green Arnie (Matcha Green Tea & Honey Lemonade) for my drink which was very refreshing and a perfect match. If you get the opportunity to stop in, look for Nate he's the cool guy at the bar. He'll get you squared away. True Food Kitchen is a cool place, great atmosphere, with awesome people. Highly recommended!

Loading mentions Retweet

Sent via BlackBerry from T-Mobile

Loading mentions Retweet

Today I was working on a SELECT query, nothing special or anything to get excited about. Looking through a single table for a few pieces of data. For this example I will refer to the Northwind database.

 
SELECT [ProductID] 
       ,[ProductName] 
       ,[SupplierID] 
       ,[CategoryID] 
       ,[QuantityPerUnit] 
       ,[UnitPrice]
       ,[UnitsInStock]
       ,[UnitsOnOrder]
       ,[ReorderLevel]
       ,[Discontinued]
 FROM [Northwind].[dbo].[Products]
 WHERE (CategoryID = 3) OR (UnitPrice = 14.00) OR (UnitsInStock > 100) 

Looks fairly straight forward right and it is. I mean there is nothing wrong with it. I can execute it and it returns 25 rows exactly what I was searching for. Well I decided to check out the Estimated Execution Plans (CTRL+L) while you are in SSMS to see what it looked like.

One thing I did not notice until now was the fact I can place the cursor over the lines or data flow arrows and get a little more detail about what is being returned. I knew that hovering over the icons returned some information about each, but I really didn't know what the data represented.

Then I remembered reading a post a while back that said it is better in this particular scenario to use a UNION ALL instead. Matter of fact here is the link: donetheaven.com. I love books marks! So the re-write would look like the following.

 
SELECT [ProductID]
       ,[ProductName]
       ,[SupplierID]
       ,[CategoryID]
       ,[QuantityPerUnit]
       ,[UnitPrice]
       ,[UnitsInStock]
       ,[UnitsOnOrder]
       ,[ReorderLevel]
       ,[Discontinued]
 FROM [Northwind].[dbo].[Products]
 WHERE (CategoryID = 3)
 UNION ALL
 SELECT [ProductID]
       ,[ProductName]
       ,[SupplierID]
       ,[CategoryID]
       ,[QuantityPerUnit]
       ,[UnitPrice]
       ,[UnitsInStock]
       ,[UnitsOnOrder]
       ,[ReorderLevel]
       ,[Discontinued]
 FROM [Northwind].[dbo].[Products]
 WHERE (UnitPrice = 14.00)
 UNION ALL
 SELECT [ProductID]
       ,[ProductName]
       ,[SupplierID]
       ,[CategoryID]
       ,[QuantityPerUnit]
       ,[UnitPrice]
       ,[UnitsInStock]
       ,[UnitsOnOrder]
       ,[ReorderLevel]
       ,[Discontinued]
 FROM [Northwind].[dbo].[Products]
 WHERE (UnitsInStock > 100)  

This query returned 27 rows and the execution plans for this turned out quite different.Take a look.

Loading mentions Retweet

Sent via BlackBerry from T-Mobile

Loading mentions Retweet

As you may have heard SQL Saturday Phoenix is underway. We are aiming for an end of July time frame and are in need of the community's involvement. We need presenters, sponsors and volunteers. All is open to suggestions so please, PLEASE voice your opinion and participate. 

If you are interested in supporting SQL Saturday Phoenix please signup using the appropriate form below. Please pass this information on.

• Presenters Signup form
• Sponsors Signup form
• Volunteers Signup form
• Suggestions form

Loading mentions Retweet

This is a quick vid that steps you through the process of creating a database snapshot, restoring a database from a snapshot and dropping a snapshot. I typically use snapshot while I am in a testing mode. I quickly create a snapshot and make all my modifications, run through my test and blow it all away by restoring from my recent snapshot. It is very useful and I use it often on my local development instances. Hopefully you will find it just as useful.

Create Database Snapshot

 CREATE DATABASE AdventureWorks_20100401_1700 ON
 (
    NAME = AdventureWorks_Data
    ,FILENAME = '<file path>\AdventureWorks_20100401_1700.ss'
 )
 AS SNAPSHOT OF AdventureWorks; 

Revert from a Database Snapshot

 RESTORE DATABASE AdventureWorks FROM
 DATABASE_SNAPSHOT = 'AdventureWorks_20100401_1700'; 

Drop Database Snapshot

 DROP DATABASE AdventureWorks_20100401_1700 

Updated 3/24/2010 - Note: SQL Server 2005 Standard Edition does not support Database Snapshots. SQL Server 2005 Enterprise and Developer Editions do.

For more information about Database Snapshots visit the Microsoft site for Database Snapshots

Loading mentions Retweet