Hmmm...What are the events that are being captured by the profiler?
-Mike
>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=
(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
>BEGIN
> PRINT '*** Create Package object failed'
> EXEC sp_displayoaerrorinfo @oPKG, @hr
> RETURN
>END
>
>--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
>BEGIN
> PRINT '*** Load Package failed'
> EXEC sp_displayoaerrorinfo @oPKG, @hr
> RETURN
>END
>
>-- 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)
> BEGIN
> delete from ImportCredits where employeeSSN is not null
> END
>
>-- 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
>BEGIN
> PRINT '*** Execute failed'
> EXEC sp_displayoaerrorinfo @oPKG , @hr
> RETURN
>END
>
>--Cleaning up:
>EXEC @hr = sp_OADestroy @oPKG
>IF @hr <> 0
>BEGIN
> PRINT '*** Destroy Package failed'
> EXEC sp_displayoaerrorinfo @oPKG, @hr
> RETURN
>END
>
>select
>from i
>where substring(invoiceno,1,3)<> 'PSI' and amount > 0
>order by customer,employeessn,invoiceno
