Re: how to return the result of execute sql programmatically

In message <OGHR$S4SFHA.3392@xxxxxxxxxxxxxxxxxxxx>, Ray5531 <RayAll@xxxxxxxxxxxx> writes 
I'm calling an Execute sql task from my script programmatically and I want
it to return its result which is a count into a global variable?? How should
I specify it?

Thanks


   ' Get Package Object
     Set oPkg2 = DTSGlobalVariables.Parent
     ' Get Exec SQL CustomTask
     Set oExecSQL = oPkg2.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask
     ' Build new SQL Statement
     sSQLStatement ="SELECT COUNT(ACTIVE_STATUS) AS countActive FROM
IF_CONFIG_SETTINGS WHERE (IFCODE =" & _
           iIFCode &  ")"


     ' Assign SQL Statement Exec SQL Task
      oExecSQL.SQLStatement = sSQLStatement
     ' Clean Up
     Set oExecSQL = Nothing
     Set oPkg2 = Nothing

     'EXIT from function if there is another instance of the same package
is running
     IF DTSGlobalVariables("gCountActivePackage").Value > 0 THEN EXIT
FUNCTION



You cannot use an Execute SQL Task from within a script task. To execute a statement directly in script, use ADO.

You can use the script to set the SQLStatement of the task, but let normal workflow execute it. Use the results parameters to get the count into a global variable.



--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

.

Relevant Pages

  • Re: Why wont my script finish?
    ... > use DBI; ... > the script never finishes and the records remain???? ... If that is the actual SQL, TABLE is a reserved word so it would need to ...
    (perl.dbi.users)
  • Re: [Info-Ingres] Running Ingres SQL commands from a shell script ?
    ... Running Ingres SQL commands from a shell script? ... I'd like to know when does the SQL command return nonzero exit status. ...
    (comp.databases.ingres)
  • I have trouble with RECYCLE-BIN in 10g
    ... I am doing some testing on my own test instance. ... The following is the "script" to capture what I did. ... SQL> drop table TEST_TABLE9; ... SQL> exit ...
    (comp.databases.oracle.server)
  • Re: I got error message but it is succesful.
    ... exit the script directly without finishing the rest of the script. ... SQL> SQL> SQL> 4 ...
    (comp.unix.shell)
  • Re: Vista hosting XPe tools/db
    ... Are you running this script on the machine that actually has the SQL server ... machine hosting the database. ... i don't know if you can install a second instance of SQL ...
    (microsoft.public.windowsxp.embedded)
Loading