03-03-05, 13:52 #1Registered User
- Join Date
- Nov 2002
Unanswered: SQL Statments in an Active X script
What I have is a small DTS package that truncates a table then loads it from a text file. I want to enhance it by sending an e-mail with record counts to our client.
The load is pretty straight forward
delete from marketing..solicit_consumer from marketing..solicit_consumer sc join dsi_use..dnc_tmp dt on sc.consumer_no = dt.consumer_no and sc.solicit_cd = dt.solicit_cd go insert marketing..solicit_consumer select * from dsi_use..dnc_tmp go
Option Explicit Function Main() Dim oPkg, oDataPump, sSQLStatement ' Build new SQL Statement sSQLStatement = "SELECT count (*) FROM dsi_use..dnc_tmp " & _ DTSGlobalVariables("DNC_Count").Value & "'" ' Get reference to the DataPump Task Set oPkg = DTSGlobalVariables.Parent Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask ' Assign SQL Statement to Source of DataPump oDataPump.SourceSQLStatement = sSQLStatement if oDataPump.SourceSQLStatement <> 0 then FrmtEmail () Main = DTSTaskExecResult_Success else Main = DTSTaskExecResult_Failure end if ' Clean Up Set oDataPump = Nothing Set oPkg = Nothing End Function
Right now it gives me a VB script runtime error.
Type Mismatch:'[string: "SELECT count(*) FR"]'
error on line 19
' Assign SQL Statement to Source of DataPump oDataPump.SourceSQLStatement = sSQLStatement if oDataPump.SourceSQLStatement <> 0 then FrmtEmail ()
03-03-05, 15:10 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
If you convert your deletion script into a stored procedure, you can capture the value of @@rowcount and return it either through "RETURN @MYRECORDCOUNT", or through "SELECT @MYRECORDCOUNT AS MYRECORDCOUNT" as a result set at the end of the procedure, or through an output parameter."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."