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:
SET NOCOUNT ON
Next, do you get annoying ANSI errors when inserting data? Begone!
SET ANSI_WARNINGS OFF
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')
BEGIN
Truncate table ##tmpTable
DROP TABLE ##tmpTable
END
You need this:
-- THis will delete your temp table
IF OBJECT_ID('tempdb..##tmpTable') IS NOT NULL
BEGIN
Truncate table ##tmpTable
DROP TABLE ##tmpTable
END
Lets say you are importing data via DTS and the date is in this format:
YYYYMMDD
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)+"-"&_
+mid(DTSSource("badDate"),5,2)+"-"+right(DTSSource("badDate"),2)
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!
