I need to execute a stored procedure from an activeXscript
and I'm not sure about the syntax. Thanks in advance for
your help. Here is my code -

Function Main()

Dim objFSO, strFullNm
Set objFSO = CreateObject ("Scripting.FileSystemObject")

strFullNm = DTSGlobalVariables("gvFilePathRoot").Value &_
"\ebtr_bldg_prop.txt"

If objFSO.FileExists (strFullNm) Then

-- here is the SP that I need to exec
exec
master.dbo.xp_cmdshell 'dtsrun /SMyServer /Ujohn /Pjohn123
/Nmy_dts_pkg /Metl123'

MsgBox "The File Exists"
Else
MsgBox "You still need to create the file"
End If

Set objFSO = nothing

Main = DTSTaskExecResult_Success
End Function

After creating the db connections, you

SQLQuery = "select * from table"
objConnection(0).CommandTimeout = 30
Set objRecordSet(0) = objConnection(0).Execute(SQLQuery)

You do not need to run a stored proc. You can simply shell out to the
cmdline to run DTSRUN.

Stored Proc: Why in an Active Script task ? You could simply check for the
file's existence and then use workflow to call an ExecuteSQL task (To use
the xp_cmdsheell stored proc).

Why do you need to have a seperate package ? You can simply use workflow to
check for the file and not proceed with the rest of the package if it does
not exist.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Daizy" <silvah (AT) wellsfargo (DOT) com> wrote

There is another thing to remember if you have a SP that runs for more than
1 or 2 minutes.. there is a command timeout setting you will need to set
higher. Here is how my connection is setup...

'Create a connection object
Set cn = CreateObject("ADODB.Connection")
'Connect using the OLE DB provider for SQL Server - SQLOLEDB
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "computername.domain.com"
cn.Properties("Initial Catalog").Value = "COSY_STAGE"
cn.Properties("Integrated Security").Value = "SSPI"
cn.ConnectionTimeout = 0
cn.CommandTimeout = 0
cn.open
cn.sp_DTS_Load_COSy_Stage

Watch out for EXACT capitalization of the DTS name (sp_DTS_Load_COSy_Stage)
as it will cause it not to work.

Good luck
tonyo

"Daizy" <silvah (AT) wellsfargo (DOT) com> wrote