Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    32

    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
    Code:
    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
    After I have an Active X script to format an e-mail but I need the counts from the SQL statement. I have tried to use the following with no luck.

    Code:
    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
    The actualy format of the e-mail I think will work if ony I can get the main function to work.

    Right now it gives me a VB script runtime error.

    Type Mismatch:'[string: "SELECT count(*) FR"]'
    error on line 19
    Line 19 is somewhere within these lines
    Code:
    ' Assign SQL Statement to Source of DataPump
    	oDataPump.SourceSQLStatement = sSQLStatement
    
    
    if oDataPump.SourceSQLStatement <> 0 then
        FrmtEmail ()
    help...

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •