SQL Server Performance Forum – Threads Archive
Dynamic SQL in DTS query
What I need to do is setup a DTS pull from another system. The problem is that the machine that I am pulling from is VERY VERY slow. The only query that I can run without timing out the ODBC connection is : SELECT * FROM SCHEDULE WHERE DATE=’2005-04-25′ What I need to do is setup a DTS job that pulls the schedules for just the current day. The machine that I am pulling from is not a SQL Server machine. I am pretty sure that it’s a VERY old unix box running ingres. If I try a BETWEEN statement, it times out. If I try to put in a calculated function in place of the ‘2005-04-26’, it times out. I need a way to change the sql in the Transform Data Task of the DTS job. Can I just modify the system table and change the sql there? If so, then what table would I change? I know that everyone is going to tell me to just define some variables and put the ? in the query. I have tried this and I get an ODBC error. Maybe I have the syntax wrong. I setup the variable in the GUI and made the following change to the SQL: SELECT * FROM SCHEDULE WHERE DATE=? It parses, but then I get the following error when I run it: Multi-step ODBC DB operation generated errors. Check each OLE DB status value, if available. No work was done. Thanks in advance for any replies to this post. WillLive to Throw
Throw to Live
Have you considered putting this into an sproc? Then having the DTS call the sproc?
The machine that I am pulling from is not a SQL Server machine. I am pretty sure that it’s a VERY old unix box running ingres. What good would it do to put it in a stored procedure on my machine? The sql statement needs to be ran on the other box. Live to Throw
Throw to Live
Ok, here’s my code of my ActiveX object. It’s the first thing executed in the package. Function Main()
Dim SelectStmt SelectStmt = "SELECT * FROM schedule WHERE status=’A’ and DATE= ‘" & CStr(Year(Date)) & "-"
DeleteStmt = "delete FROM IEX..IEXSCHEDULE WHERE CONVERT(CHAR(10),DATE,21)= ‘" & CStr(Year(Date)) & "-" if Month(Date) < 10 then
SelectStmt = SelectStmt & "0" & CStr(Month(Date)) & "-"
DeleteStmt = DeleteStmt & "0" & CStr(Month(Date)) & "-"
else
SelectStmt = SelectStmt & CStr(Month(Date)) & "-"
DeleteStmt = DeleteStmt & CStr(Month(Date)) & "-"
End If if Day(Date) < 10 then
SelectStmt = SelectStmt & "0" & CStr(Day(Date)) & "’"
DeleteStmt = DeleteStmt & "0" & CStr(Day(Date)) & "’"
else
SelectStmt = SelectStmt & CStr(Day(Date)) & "’"
DeleteStmt = DeleteStmt & CStr(Day(Date)) & "’"
End If DTSGlobalVariables("SQLString").Value = SelectStmt
DTSGlobalVariables("DelString").Value = DeleteStmt Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSStep_DTSDataPumpTask_1").CustomTask
oDataPump.SourceSQLStatement = SelectStmt Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSStep_DTSExecuteSQLTask_1").CustomTask
oDataPump.SourceSQLStatement = DeleteStmt
Main = DTSTaskExecResult_Success
End Function
I have verified that the following are the names of my delete and insert portions of the package. DTSStep_DTSDataPumpTask_1 DTSStep_DTSExecuteSQLTask_1 When I run my package, I get the following error: Task ‘DTSStep_DTSDataPumpTask_1’ was not found. I pull up the workflow properties of each object and it’s exactly the same. Not sure what I am doing wrong. Thanks for the quick reply. Keep in mind that I am running SQL Server 7 on this machine. I get this working in SQL Server 2000, but not in 7. DTS isn’t as robust in 7 as it is in 2000. Thanks in advance for any responses to this post. Live to Throw
Throw to Live
Ok, I changed DTSStep to DTSTask and ran it. Now I get the following error: Error Code: 0
Error Source=Microsoft VBScript runtime error
Error Description – object doesn’t support this property or method: ‘oDataPump.SourceSQLStatement’ Error on Line 27
Is there anywhere that shows all of the methods and properties for all of the tasks and connections in the DTS manager? I need to change the SQL for my Execute SQL step and for my Data Transformation step. The Execute SQL step is a yellow cylinder icon with red circular arrows in front of it. The Data Transformation step is just a arrow pointing from the source to the destination server. Live to Throw
Throw to Live
I changed line 27 to reference SQLStatement instead of SourceSQLStatement and it worked! Thanks for any replies to this post. Live to Throw
Throw to Live
Ok, here’s the latest. I changed my ActiveX code to the following: Function Main()
Dim DeleteStmt, SelectStmt, oPkg, oDataPump, sSQLStatement, PullDate PullDate = "2005-05-05"
SelectStmt = "SELECT * FROM schedule WHERE status=’A’ and DATE= ‘"+PullDate+"’ "
DeleteStmt = "delete FROM IEXSTAGE35..IEXSCHEDULE WHERE CONVERT(CHAR(10),DATE,21)= ‘"+PullDate+"’ " DTSGlobalVariables("SQLString").Value = SelectStmt
DTSGlobalVariables("DelString").Value = DeleteStmt Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask
oDataPump.SQLStatement = DeleteStmt Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
oDataPump.SourceSQLStatement = SelectStmt Main = DTSTaskExecResult_Success
End Function
This works fine as written. What I want to be able to do is to dynamically change the value of the PullDate variable so that I can run this for whatever day I want, or maybe pull the days to run from another table. Any ideas? Live to Throw
Throw to Live
Here is what my question boils down to: How do I store the results of a query (one value, not a set) to a global variable? I can do this in 2000 easily, but I can’t find any documentation on it in 7. Upgrading the server is not an option at this point in time. Live to Throw
Throw to Live
I replaced the following line : PullDate = "2005-05-05" with the following code : Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Driver={SQL Server};Server=(local);Trusted_Connection=yes;"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open "SELECT COL_DAT FROM PULLDATE", oConn
PullDate = oRS(0)
oRS.Close
oConn.Close
Set oRS = Nothing
Set oConn = Nothing And it works! Now all I have to do it change the value of the row that I keep in the PULLDATE table and run the DTS job. I am going to scroll through a list of dates and just change the value in the table for each time that I run the job Thanks to all that have posted on this thread. Live to Throw
Throw to Live
]]>