Hi,

I have to call a DTS package from ASP, for this I have created a stored
procedure that calls DTS package. I am calling that stored procedure from
ASP, now the problem is that DTS package is executing two times.

But If I am executing the stored procedure that calls DTS package, from
Query Analyser then it is executing perfectly.

I am not getting how this is happening.

Thanks in advance for any help.

Run a Profiler trace to see how many times the proc is being called. The
same proc will not behave that differently when called from different
locations.


--
Darren Green
http://www.sqldts.com

"Aamti" <Aamti (AT) discussions (DOT) microsoft.com> wrote

Thanks for your reply.

I have checked, I am calling the procedure only once. I am attaching the
code snippet that I am using to call the DTS package

********************** Prcedure script **********************
ALTER PROCEDURE DBO.p_execute_dts_for_statistics
(
@p_year_id INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @DTS_package INT
DECLARE @iReturn INT
DECLARE @v_servername VARCHAR(200)
DECLARE @DTS_PACKAGE_NAME VARCHAR(200)
SET @DTS_PACKAGE_NAME = 'AACStatisticsCalculation'
--
SELECT @v_servername = @@SERVERNAME
--
--Create an instance of a DTS package
EXEC @iReturn = sp_OACreate 'DTS.Package', @DTS_package OUTPUT
IF @@ERROR <> 0
BEGIN
GOTO ErrorHandler
END
--Load DTS package
EXEC @iReturn = sp_OAMethod @DTS_package,
'LoadFromSQLServer',
NULL,
@ServerName = @v_servername,
@Flags = 256, -- NT authentication
@PackageName = @DTS_PACKAGE_NAME

IF @iReturn <> 0
BEGIN
RAISERROR('Error while loading package from SQL Server',16,-1)
END
IF @@ERROR <> 0
BEGIN
GOTO ErrorHandler
END
--Set any properties of the package
EXEC @iReturn = sp_OASetProperty @DTS_package,
'GlobalVariables
("p_year_id").Value',
@p_year_id
--
IF @@ERROR <> 0
BEGIN
GOTO ErrorHandler
END
--
IF @iReturn <> 0
BEGIN
RAISERROR('Error while setting value of global variable p_year_id of
DTS package',16,-1)
END
--
EXEC @iReturn = sp_OAMethod @DTS_package, 'Execute'
IF @@ERROR <> 0
BEGIN
GOTO ErrorHandler
END
IF @iReturn <> 0
BEGIN
RAISERROR('Error while executing package',16,-1)
END
--destroy the instance
EXEC sp_OADestroy @DTS_package
RETURN
--
ErrorHandler:
RETURN -1
END
********************** Prcedure script **********************

************************* ASP code to call the procedure *******
<%
Dim cmdCalculateStats
Dim adoPrmCalculateStats
Set cmdCalculateStats = server.CreateObject("ADODB.Command")
Set adoPrmCalculateStats = server.CreateObject("ADODB.Parameter")
cmdCalculateStats.CommandTimeout = 5000
conAdodb.CommandTimeout = 5000
cmdCalculateStats.ActiveConnection = conAdodb
cmdCalculateStats.CommandType = adCmdStoredProc
cmdCalculateStats.CommandText = "DBO.p_execute_dts_for_statistics"
Set adoPrmCalculateStats = cmdCalculateStats.CreateParameter("@p_year_id",
adInteger ,adParamInput, 4, intSimulationYear)
cmdCalculateStats.Parameters.Append adoPrmCalculateStats
cmdCalculateStats.Execute
If err.number <> 0 Then
Call subFreeAllocatedMemeory
Response.End
End If
rstDTSErrorMessage.Open "Select TOP 1 * from DBO.DTS_ERROR_MESSAGES ORDER BY
START_TIME DESC",conAdodb
if not (rstDTSErrorMessage.EOF and rstDTSErrorMessage.BOF) then
if trim(rstDTSErrorMessage("status")) = "Failure" then
call subFreeAllocatedMemeory
Response.End
end if
end if
Response.End
%>
************************* ASP code to call the procedure *******

Regards,
Aamti

"Darren Green" wrote: