DTS Fail Sometime

Question

Post reply

DTS Fail Sometime

richard_zeng

SSC Rookie

Points: 39
More actions
April 21, 2005 at 9:14 am

#67395

I am using DTS to transfer table from sql server (2000) to another sql server(2000),most time it works perfectly, but sometime I get error as following:
Step 'step_copy_t_tt_table' failed
Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 42000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '?'.
Step Error code: 800400AA
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:1131
Step Execution Started: 4/20/2005 12:22:25 PM
Step Execution Completed: 4/20/2005 12:24:19 PM
Total Step Execution Time: 113.578 seconds
Progress count in Step: 0
On this step I am using activex Script
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
   Dim oPkg, oTask
   Dim sSourceTable
   ' Derive the new table names

   ' Get reference to the DataPump Task
   Set oPkg = DTSGlobalVariables.Parent

   Set oTask = oPkg.Tasks("Task_Copy_t_tt_Tables").CustomTask
   ' Set the new values
   oTask.ResetObjectsList
   oTask.AddObjectForTransfer "T"& DTSGlobalVariables("Clusterid").Value, "dbo", 8
   oTask.AddObjectForTransfer "TT"& DTSGlobalVariables("Clusterid").Value, "dbo", 8
   oTask.IncludeLogins = False
   oTask.IncludeUsers = Fasle
   ' Clean Up
   Set oTask = Nothing
   Set oPkg = Nothing
   Main = DTSTaskExecResult_Success
End Function
any suggestion is welcome.
Richard

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

I'm guessing that in your code you have something like

Select something from table where id = ?

The failures are probably happening when the global variable (= to ?) is set nothing.

POst some more information on what you are trying to do if this is not the case

------------------------------
The Users are always right - when I'm not wrong!

richard_zeng SSC Rookie Points: 39 More actions · Answer 2

I believe it is not the case, the Error Description varies from time to time.

Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '7'.

and also I set global varibles in first step, if there is an error on it DTS will stop running to next step.

philcart SSC-Forever Points: 47797 More actions · Answer 3

The message looks like a SQL error, rather than a VBScript error. Try looking at profiler while this is running. You might find the problem there.

--------------------
Colt 45 - the original point and click interface