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.

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.

Recently I faced a problem with a mobile device (subscriber) which had a suspect database. I tried running the following command to check the database:

 DBCC CHECKDB ('databasename') 

 EXEC sp_resetstatus 'yourDBname';
 ALTER DATABASE yourDBname SET EMERGENCY
 DBCC checkdb('yourDBname')
 ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
 ALTER DATABASE yourDBname SET MULTI_USER 

Typically in most cases you find yourself removing leading zeros but in this case I needed to add leading zeros to a column. 

Remove Leading Zeros

 SELECT CAST(CAST(ColumnName AS INT) AS VARCHAR(10)) FROM TableName

Let's Pad the field

Since I needed 6 chars this will add 3 spaces to the front of the numbers

 SELECT STR(ColumnName, 6) FROM TableName 

Add Leading Zeros

Let's say we need to add zeros to an employee id, well this would be an easy way to accomplish it. First we pad it with spaces and replace those spaces with zeros.

 SELECT REPLACE(STR(ColumnName, 6), SPACE(1), '0') FROM TableName 

SELECT SalesPersonID 
       ,REPLACE(STR(SalesPersonID, 6), SPACE(1), '0') AS PaddedSalesPID  
FROM   Sales.SalesPerson

Note: the SPACE(1) is equivalent to ' ' (That is a Tick Space Tick) So two Ticks with a space in between)

Update Table

UPDATE Sales.SalesPerson 
SET newSalesPID = REPLACE(STR(SalesPersonID, 6), SPACE(1), '0')

Recently I faced an issue with reporting services 2005 when attempting to deliver subscriptions to addresses outside of the organization. Internal addresses received the email based subscription deliveries without any questions. I must have checked and re-checked the settings using RSConfigTool about million times, looking for anything I might have overlooked. The error message I received was, "The e-mail address of one or more recipients is not valid". After some research (which lead me to a lot of dead ended forums) I read the phrase "email relay", that's when the gears started spinning.

I realized that the issue had nothing to do with the configuration of SQL Server Reporting Services; rather, the SMTP server! In order for the messages to be delivered outside of the organization the Reporting Services Server needed to be authorized so-to-speak. Unfortunately I don't have access to Exchange 2003 so I cannot provide screen shots, but for 2007 all you need to do is add the server's IP Address to the SMTP server’s receiver group in the HUB Transport configs.

Then to test your subscription without tweaking the schedule execution time just run the SQL job! First find out the name of the job by using the attached sql script, then execute the sp_start_job. If you have a named instance append $instancename to all three of ReportServer occurrences within the sql script (i.e. ReportServer$InstanceName) for MSSQL 2005. I believe for MSSQL 2008 you would append _InstanceName (i.e. ReportServer_InstanceName), but I am not certain. You should get the results similar to the screen shot attached.

(download)

Click here to download:

sqlshots_Delivering_subscripti.zip (54 KB)

Using the script with a default instance

 SELECT	
    sj.[name] AS [Job Name], 
    c.[Name] AS [Report Name], 
    c.[Path], 
    su.Description, 
    su.EventType, 
    su.LastStatus, 
    su.LastRunTime 
 FROM msdb..sysjobs AS sj INNER JOIN ReportServer..ReportSchedule AS rs 
    ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128)) INNER JOIN 
    ReportServer..Subscriptions AS su 
    ON rs.SubscriptionID = su.SubscriptionID INNER JOIN 
    ReportServer..[Catalog] c 
    ON su.Report_OID = c.ItemID 

Using the script with a named instance

 SELECT	
    sj.[name] AS [Job Name], 
    c.[Name] AS [Report Name], 
    c.[Path], 
    su.Description, 
    su.EventType, 
    su.LastStatus, 
    su.LastRunTime 
 FROM msdb..sysjobs AS sj INNER JOIN ReportServer$InstanceName..ReportSchedule AS rs 
    ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128)) INNER JOIN 
    ReportServer$InstanceName..Subscriptions AS su 
    ON rs.SubscriptionID = su.SubscriptionID INNER JOIN 
    ReportServer$InstanceName..[Catalog] c 
    ON su.Report_OID = c.ItemID 

Execute the report

 USE [msdb] 
 EXEC sp_start_job @job_name = 'AF015D8B-D80D-4D2A-9808-CD1D519B3332' 

Correction
In the attached file listed below I have identified a typo. Currently it states on
Line 19: sp_start_sp (which is incorrect) it should be
Line 19: sp_start_job

Click here to download:

SELECT.Get_RSSubscription_Jobs_v0.02.sql (0 Bytes)

Typically you would use a JOIN clause when querying from multiple tables, but no one said you have to. Say for example you wanted to query two tables (tableA & tableB) which did not have any relationship (no one-to-one at all), yet there are fields in which would permit a comparison. Say street number, pre-direction, street name and the suffix.

Well you would compare
(tableA.street number = tableB.street number) and
(tableA.pre-direction = tableB.pre-direction) and
(tableA.street name = tableB.street name) and
(tableA.suffix = tableB.suffix).

Well I did not have a sample dataset for that illustration but I do have the AdventureWorks database, so here we go.

This first example will illustrate a query using a JOIN clause.

Results

This example illustrates a query without using a JOIN clause.

Results

As you can see the results are the same, but the queries are a tad different.

-- samson

In this particular scenario I needed to concatenate a set of fields into a single column. So essentially I needed to trim all the leading zeros from the street number field first (via my udf_TrimLeadingZeros), then pieced the street number, pre-direction, street name and suffix all into one field. However, every row did not have a pre-direction or a suffix; nonetheless, I needed everything formatted (evenly spaced between words) and displayed as a single string.

So I figured I would use a CASE expression (http://bit.ly/PiYQF) in conjunction with a String Concatenation (http://bit.ly/FJtq9) to achieve my end result. Typically I see a period (.) appended to the pre-direction, but in this case it wasn't required. However if you do need it simply add a period to line 14 between the ticks '. ' of the attached select script (see pre-direction with periods screen shot for details).

Pre-Direction without periods

Pre-Direction with periods

Click here to download:

CREATE.udf_TrimLeadingZeros_v0.01.sql (0 Bytes)

Click here to download:

SELECT.Trim-Case-ConCat_v0.01.sql (0 Bytes)

There are a lot of sites out there that give an in-depth definition of identity columns (this ain't one of them), but I have to admit that it was something I took for granted recently. In my task to clean up geospatial address data, specifically for geo-coding purposes I over looked the goodness of having an identity field as part of table. The data that I received did not have an ID field and for some odd reason I thought nothing of. I figured I would make updates by comparing multiple columns to target specific rows.  Well that worked fine until I realized that I had duplicate data. Digging myself deeper into a whole I kept doing things the hard way, well let's just say I did things the less efficient way.

Once I finally realized what I had been doing was inefficient I decided to introduce an ID field to the table. However old habits are hard to kill. Typically when I create ID fields I always leave (identity) off because I use UDFs to generate my sequential numbering. Reason being if there is ever a need to remove a record or delete a record (which seldom occurs since I use bit fields to designate active versus inactive records) the numbering continues. So if I have rows one through five and physically delete row five (ID 5), my next insert will become ID 6. Making the sequence 1,2,3,4,6.

Some may disagree but I really don't like that and no I am not OCD. It's just that I hate wasting IDs and avoid it whenever possible, hence my dilemma. So I add the column via the UI and save my changes. Then I start to think what is the best way to populate this newly added column. Not seeing the obvious (big rookie mistake on my part) I must have wasted 30 mins to an hour before I stepped away to grab a drink. Then all of a sudden it occurred to me that I could drop the column and re-add it via alter statement and all my worries would be gone. Which I did. You can do the same using the UI.

One simple single line could have prevented a self inflicted heartache because I was being an idiot. Once I ran it, the row was created and populated in sequential order. Just goes to show sometimes you need to step away from the task at hand to see what you are doing wrong! Granted I only needed the column temporarily; nonetheless, it helped a great deal when running bulk updates and having to compare a single column versus five to six. Especially when dealing with hundreds of thousands of records.

ALTER STATEMENT:

ALTER TABLE TableName ADD ID INT IDENTITY(1,1) 

Just to give you a visual of what exactly transpired here is little step by step (example data only):

BEFORE AND AFTER

1) SELECT * FROM TestTable

2) ALTER TABLE TestTable ADD ID INT IDENTITY(1,1)

3) SELECT * FROM TestTable

I work a great deal with SQL Server Reporting Services and in some (if not most) of my previous reports I often needed to calculate the number of days between two given dates. Usually the amount of days a Work Order has been open, how many days it's past due and such. Well using the DateDiff() function it makes it really simple to accomplish. However If you need to calculate age there is a bit more logic required. See Jacob's post at http://bit.ly/2O2qWW for further deatils.

Click here to download:

DateCalculation.sql (0 Bytes)

Attached in the [DOWNLOAD SECTION] is a script titled "FourPartStringParse_v0.01.sql" that will parse out an address. It has limitations though. See the file comments for details. Thanks to @MarlonRibunal for the help!

I tried this route, but the problem with this script is that it counts every space.  So if there happens to be a double space between two words then it gets added to the total count.  

DECLARE @String NVARCHAR(100)
SET @String = 'How many words are  there in this big string?'
SELECT @String,(1 + LEN(@String) - LEN(REPLACE(@String, ' ', ''))) AS WordCount  as you can see in the illustration below.

The following method worked better, but I still had some issues with it as I mentioned before.

Click here to download:

FourPartStringParse_v0.01.sql (0 Bytes)