Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003

    Question Unanswered: Call DTS Package from ASP via SP runs it 2x. Runs fine from w/in SQL

    I have a DTS package that is called from a SP in SQL Server 2000. When it is run from within SQL, the profiler shows that the Application "DTS Designer" is only called once and does it's tasks and returns the recordset.....

    When the SP is called from via ASP, the profiler shows that the Application "DTS Designer" actually runs 2x before returning... asside from killing efficiency, the extra run is also causing my page to take forever (15 minutes)

    ASP Call:
    myDSN=<DSNString> (works fine...)
    Set DataConn = Server.CreateObject("ADODB.Connection")
    DataConn.Open myDSN
    pcuserid = "DBUsername"
    pcpassword = "DBPassword"
    strQuery = "exec usa_runimportcreditdtspkg @importfilename= '" & psFileName & "', @userid= '" & pcuserid & "',@password='" & pcpassword & "' "
    Set rsMain = Server.CreateObject("ADODB.RecordSet")
    rsMain.Open strQuery,DataConn,1,3

    Here is the SP code:
    declare @hr as int,
    @opkg as int -- the object token that will refer to the created PKG

    --Creating the DTS Package Object:
    EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
    IF @hr <> 0
    PRINT '*** Create Package object failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr

    --Loading the Package:
    declare @loadstring as varchar(250)
    set @loadstring = 'LoadFromSQLServer("WEBDEV1", "'+rTrim(@userid)+'", "'+rTrim(@password)+'", 0, , , , "importcredits")'

    EXEC @hr = sp_OAMethod @oPKG,@loadstring, NULL
    IF @hr <> 0
    PRINT '*** Load Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr

    -- clear out the table before proceeding if it exists
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportCredits]')
    and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    delete from ImportCredits where employeeSSN is not null

    -- use the passed filename to set the global variable inside the pkg
    EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("ImportFilename").Value', @importfilename

    --Executing the Package:
    EXEC @hr = sp_OAMethod @oPKG, 'Execute'
    IF @hr <> 0
    PRINT '*** Execute failed'
    EXEC sp_displayoaerrorinfo @oPKG , @hr

    --Cleaning up:
    EXEC @hr = sp_OADestroy @oPKG
    IF @hr <> 0
    PRINT '*** Destroy Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr

    select <Fields>
    from <DTSPopulatedTable> i
    where substring(invoiceno,1,3)<> 'PSI' and amount > 0
    order by customer,employeessn,invoiceno

  2. #2
    Join Date
    Apr 2003
    Evansville, Indiana

    Lightbulb try rewriting the connection string

    Here's a few ideas...I'd try each of these steps in order one at a time until you get the performance you want.

    1) It looks like you are using a DSN as your connection when you create the connection object. First, I'd try using the OLE DB for SQL Server instead. I have found that using the OLE DB provider, rather than a DSN or a "DSN-less" connection, is a lot faster. Either way, you want to do this step. By using an OLE DB connection, you won't need a DSN at all for the ASP connection. This has its advantages too.

    2) Next, I would explicitly tell the Recordset object that I am executing a stored procedure, rather than passing SQL that states "EXEC spXXXX", by using the adCmdStoredProc constant in the last argument of the Open method. You may see some improvements there too.

    3) Last, try using a command object with the recordset object since you are executing stored procedures.

    Here is some sample code to get you started...
    ' try these one at a time, but build them in order.  Do step 1 AND step 2, or do step 1 AND step 3
    Dim strcon, con, rst, cmd ' need cmd object variable only if you try step 3
    ' >>> step 1
    ' create string to use to connect to SQL Server
    strcon = "Provider=SQLOLEDB; Data Source=ServerName; Initial Catalog=DBName; User ID=UserName; Password=Password;"
    ' instantiate connection object
    Set con = Server.CreateObject("ADODB.Connection")
    ' open connection to SQL Server using the connection string
    con.Open strcon
    ' >>> step 2
    ' instantiate recordset object
    Set rst = Server.CreateObject("ADODB.Recordset")
    ' open the recordset object (execute the stored procedure)
    ' you need to use the integer constants for the CursorType, LockType, and Options arguments in ASP (sorry, I don't know them off hand)
    rst.Open "sp_name", con, CursorType, LockType, adCmdStoredProc
    ' >>> step 3
    ' using the command object can be a bit tricky.  I never tried it in ASP, just VB 6, so I'm only guessing on my conversions since the syntax is totally different...
    ' instantiate command object
    Set cmd = Server.CreateObject("ADODB.Command")
    ' set command type and command text
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "sp_name"
    ' create and set the parameter values (only if there are input parms for the sp) otherwise, you don't need these next two groups of code...
    cmd.Parameters.Append("parm1", DataType, adParamInput)
    cmd.Parameters.Append("parm2", DataType, adParamInput)
    cmd.Parameters("parm1").Value = "value for first parameter"
    cmd.Parameters("parm2").Value = "value for second parameter"
    ' associate the command object with the connection object
    cmd.ActiveConnection = con
    ' open the recordset object (execute the stored procedure)
    Set rst = cmd.Execute
    Another thing is that you may want to try tweaking the CursorType and LockType arguments whenever you call the Recordset.Open method for either steps 2 or 3 depending on what you want to do with the recordset. Be sure to only use the minimum amount of locking or cursor fetching capabilities you absolutely need to maximize performance.

    Lastly...whew! With regards to VBScript and ASP, you may want to use the Response.Buffer and flush it at times depending on how you want to display the page in the browser.

    If you have any questions, feel free to ask.


    Last edited by kaeldowdy; 04-05-03 at 18:29.

Posting Permissions

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