How to know which SQL Server version and edition you are running

SELECT @@VERSION AS [Version]

Will return something like:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

Found it here.

Don't forget to GO after ALTER TRIGGER

Today I found peculiar issue while doing SQL scripts...

A couple of months ago, I did an SQL script that, among other things had an ALTER TRIGGER statement followed by an UPDATE statement. This particular trigger was supposed to add data to a history table. For some strange reason though, I was having trouble doing a particular update statement, because it was being undone. After a couple of hours, I found the cultrip: there was an update statement inside the trigger. What happened? I didn't do a GO statement between the trigger and the update statement.
(Yeah, maybe this is obvious for all the SQL gurus out there, but it was not for me. Lesson learnt. :))

Unable to cast COM object of type...

Was the error that bugged me for three days, any time I tried to connect to a database in SQL Server Management Studio. Re-installing the Management Studio or the whole SQL Server wasn't working. Neither the workarounds for registering some specific DLLs, or event (gasp!) changing some GUIDs in the registry. What eventually fixed my problem was running the following command in the command prompt (Administrator Mode) and rebooting:

sfc /scannow

(SFC stands for System File Checker)

It's kind of weird that this issue has been around since 2005, officially reported to Microsoft in 2009 and it's still bugging people today (like me) using Windows 7, Visual Studio 2010 and SQL Server 2008 R2. For the record here is where I found the fix.

SQL Server BCP

A couple of weeks ago I was writing a small application to perform some ETL (Extract, Transform, Load) operations. As I was researching an efficient way to perform a large number of SQL insert statements, I found out about the BCP (Bulk CoPy) utility for MS SQL Server. This tool performs efficient data insertion (something in the order of thousands of row inserts per second), using as source CSV (Comma Separated Values) files. The mapping is performed through configuration files in a semi-structured format, or using an XML file (the latter is strongly recommended). Basically, the trick resides that unlike in regular inserts, database restrictions (primary keys, foreign keys, unique restrictions, null values, etc.) are not enforced for each row, but only at the end of the introduction, or per each block.

This was good solution for my needs at the time, which was to extract a large number of data from CSV files into a small number of tables, on a secondary database to the system in question. Of course, the mapping is limited: you cannot perform data normalization, only insert/remove columns, or switch its order. This means that either the input data is normalized, or you end up with a denormalized database. In sum, BCP does not replace an ETL tool, but provides an interesting complement in the "Loading" part.

By the way, a very nice book on the subject is Professional SQL Server 2005 Programming, by Robert Vieira, published by Wrox.



Until next time, may you enjoy bulk copying.