July 7, 2002 at 9:19 am
Hi All,
I have a stored procedure like :
CREATE PROCEDURE TestSP
@InTestSP int,
@OutTestSP int OUTPUT
AS
SET @OutTest = @InTestSP + 2
RETURN 1
GO
In DTS I am trying to pass the input parameter value using a Global Variable (say G1) and retrieve the output parameter value in another global variable (say G2) using an Execute SQL Task. But I am unable to do so.
Inside the Execute SQL statement I have written a query like:
DECLARE @OUTTestSP int
EXEC TestSP ?, @OUTTestSP OUTPUT
SELECT @OUTTestSP TestSPResult
But it gives a syntax error. It works fine if I hardcode the Input parameter value.
Can anybody please help. It is not nesessary to use Execute SQL task also. Any possible way of doing it will work fine.
Thanks in advance,
Soma.
July 10, 2002 at 12:11 pm
Here is a VBScript that could work for you. If not, then I hope it will at least get you started. I only know a little VBScript and so had to change your SP to make it work. Place the following code in an ActiveX task:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
globalval = DTSGlobalvariables("global").value
DTSGlobalvariables("G1").value = 5
' specify the OLE DB provider.
set dbConnection = CreateObject("ADODB.Connection")
dbConnection.Provider = "sqloledb"
dbstr = "Server=Server01;database=TestRobert;UID=username;pwd=password"
dbConnection.open dbstr
getResult = "EXEC TestSP @inTestSP = " & DTSGlobalvariables("G1").value
set SPoutput = dbConnection.execute(getResult)
globalval = DTSGlobalvariables("global").value
DTSGlobalvariables("G2").value = SPoutput("OutTestSP")
msgbox DTSGlobalvariables("G2").value
Main = DTSTaskExecResult_Success
End Function
Here is how I modified the SP:
CREATE PROCEDURE TestSP
@InTestSP int
AS
SELECT @InTestSP + 2 AS [OutTestSP]
RETURN 1
GO
Or perhaps this will help someone else to suggest a better way.
The above connection string must be modified with your server name, database name, valid SQL Server login and the password for that login. I think there is a way to make it use a Windows NT account. If you need that I will see if I can find how to do that in a book I have.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
July 12, 2002 at 12:04 am
Thanks Robert, I have done it using VB Script only, but in a slightly different way. Thanks for giving the idea of using it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply