Category: SQL

SQL: Maintaining Custom User Data Via Publishing Wizard

We refresh our testing databases periodically with no problems. It is a logical protocol to make sure to alert all users ahead of time so that they may backup any needed custom data that they have entered themselves; however, when I did a data refresh this week something went awry and before I knew it there was a line to my desk, the phone was ringing, and email started piling up.

Fortunately, we did have a backup copy of this database so I was able to query and recover this data. However, I needed a quick and simple way to have the users re-create this data without my involvement. I could set up some type of job to restore the data after each refresh , but the data would only need to exist for a few months and I did not have time to go through the red tape that would be required for this. Given this particular scenario, it was decided to create a SQL script that would do INSERTS for the data.

Creating an INSERT script should not be difficult but it was too timely. Exporting and Importing the data was not ideal. I decided to investigate tools that would create the script for me. And there it was — on my machine all along — the SQL Server Publishing Wizard. I had forgotten all about it. I had just needed to locate it since there was no icon or link; therefore, the entire purpose of this way too-long-winded post :

C:Program FilesMicrosoft SQL Server90ToolsPublishing1.4SqlPubWiz.exe

Yay, now I have a reference and reminder!

Anyway, it is worth mentioning that this tool is way inappropriate for large data. The data needed was a very small portion compared to the millions of rows that exists. I had to query the needed data to insert into a temp table and this made the wizard fast. Ideally, it would have been better to have the ability to query a table for data from within the wizard but I’m sure it is just a matter of time.

Error: Cannot resolve the collation conflict

This error started occurring after one of my SQL Server 2008 databases had been restored:

Msg 468, Level 16, State 9 …

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “SQL_Latin1_General_CP1_CS_AS” in the equal to operation.



Supposedly, this type of error can occur when the tempdb collation does not match the current database collation — especially if using temp tables.

The easiest way to deal with this is to cast one of the field’s collation so it matches the other, or you can cast both if you need. I’m not sure if this is best practice in the long run; however, if you’re just doing some ad-hoc data mining then this is definitely a quick fix:

INNER JOIN [TABLE1].FIELD1 AS EA

ON EA.SERIAL_NBR COLLATE database_default = E.SERIAL_NBR COLLATE database_default

SQL: UNION vs. UNION ALL

I try to avoid UNION clauses in most SQL that I write. Historically, they have taken too much of a toll on performance. Now we have a speedier alternative to the UNION clause — UNION ALL.

In a nutshell, a UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

This difference is that UNION ALL will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

UNION Definition:
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL Definition:
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

A great reference can be found in Pinal Dave’s blog.