Thursday, July 05, 2007

ANSI warnings, NOCOUNT and Temp Tables

So, I'm always needing these code bits. And yet, I forget the exact syntax and I have to look all over the place for the exact code. Well, here it is. First, NOCOUNT. If you don't want SQL Server to return the Row Count after you execute a query, here is the code:


Next, do you get annoying ANSI errors when inserting data? Begone!

Lastly, when working with true temp tables, the ones starting with ## or #, the old standard will not work. This is because Temp tables live in the database TempDB, not your current DB. SO, sysobjects won't have the table info you're looking for.
-- Won't Work!
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='##tmpTable')
Truncate table ##tmpTable
DROP TABLE ##tmpTable
You need this:
-- THis will delete your temp table
IF OBJECT_ID('tempdb..##tmpTable') IS NOT NULL
Truncate table ##tmpTable
DROP TABLE ##tmpTable

Thursday, February 22, 2007

Change a Date Format Using ActiveX and DTS

Lets say you are importing data via DTS and the date is in this format:
So, SQL server isn't going to know what to do with this data. So, you're going to have to use ActiveX copy instead of the plain old Copy Column Task. Here is the code you would use in your ActiveX task within the Data Pump:

' Visual Basic Transformation Script

' Copy each source column to the destination column
Function Main()
DTSDestination("myDate") = left(DTSSource("badDate"),4)+"-"&_
Main = DTSTransformStat_OK
End Function

Substitute myDate for your destination and badDate for the source. This will give you a nice result in the YYYY-MM-DD format.

You can also expand this technique to include times. You would have to use some two more Mid to pull the date and hour, and the right would pull the minutes.

Happy Coding!