August 26, 2006 at 12:59 am
I am having a problem using an "Execute SQL" Task to run a stored procedure and dump the results into a global variable so I can get to it via an ActiveX script. Strangely enough, this works for the three other stored procedures and scripts (there are many "Execute SQL" and "ActiveX Script" Tasks in my DTS package), but this one keeps giving me a Runtime Error "Object Required: rs" when I first try to use the variable rs in the script.
After digging on the internet and messing with my code (I wish there was some sort of debugging tools for DTS packages using VBScript) I created a simple script to test it:
Function Main()
DIM rs1
DIM rs2
SET rs1 = DTSGlobalVariables("rs_ReferralDetail").value
SET rs2 = DTSGlobalVariables("rs_SNPHL").value
IF rs1 IS NOTHING THEN MsgBox "rs1 IS NOTHING!"
IF rs2 IS NOTHING THEN MsgBox "rs2 IS NOTHING!"
Main = DTSTaskExecResult_Success
End Function
This gives me a message box that says "rs2 IS NOTHING!" meaning the second DTSGlobalVariables is not returning my recordset.
I know the global variable and output parameter of the Execute SQL task are correctly set up (it is nearly identical to the other 3 that work, with the exception of the stored procedure name and the global variable name). I know the stored procedure returns a set of data just like the other ones. But for some reason it cannot be retrieved with DTSGlobalVariables in the script.
Has anyone ever heard of such a thing? Or does anyone have any suggestions? This is blowing my mind...
-- Stephen Cook
August 26, 2006 at 1:21 am
Bah, these things always become clear a few minutes after posting for help...
Apparently, using a table variable in a stored procedure screws with DTS's ability to execute said stored procedure and dump the results into a dispatch-type global variable.
Furthermore, using a temp table instead causes an error message when I click the "Parameters" button in the "Execute SQL Task Properties" dialog... something along the lines of "Invalid object name '#tmp'"... Thus I cannot even get to the right screen to try and set up an output parameter (not that I would expect it to work anyway).
So, problem (sorta) solved, all I have to do is convert my stored procedure so it uses several UNIONs (if I am clever enough).
But, has anyone ever heard of such a thing? I can't find anything about this anywhere, and I am still curious.
-- Stephen Cook
August 30, 2006 at 2:06 pm
Regarding the temp table, you can test with a global temp table (create in another connection like query analyzer). When I'm done with my testing, I change the global temp table back into a regular temp table, but I do this via the Disconnected Edit. The "Live" parsing within DTS sure is a pain at times.
September 5, 2006 at 11:04 am
Thanks for the tip, I never used the "Disconnected Edit" before.
-- Stephen Cook
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply