DTS run from xp_cmdshell in Stored Proc fails inside a trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS run from xp_cmdshell in Stored Proc fails inside a trigger

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-07 : 09:03:35

Mark writes "I have a DTS which consist of an ActiveX script task that:

1. Opens a recordset based on a return from a stored proc
2. Opens a 2nd recordset, based on fields from the 1st
3. Saves the recordset to an XML file

This DTS is called from within a stored procedure:

Alter Procedure db_PSL.SP_XML_UpdateCategory @category_id int

As
/* set nocount on */
declare @cmdstr varchar(1000)
print (convert(smalldatetime,getdate()))
select @cmdstr = 'dtsrun /E /SdbServer /G "{A9313F95-ACB7-4312-808F-1F7D33D2A08F}" /A "category_id":"8"="' + convert(varchar,@category_id) + '"'
print 'Running Update Category = ' + @cmdstr
exec master..xp_cmdshell @cmdStr

return
----------------------------------------------------------------
That stored procedure is then called by a trigger:

CREATE TRIGGER [UpdateXML] ON [dbo].[tblLinks]
FOR INSERT, UPDATE, DELETE
AS

declare @category_id int

select @category_id = category_id from Deleted
if @category_id is not null
begin
exec db_psl.sp_xml_updatecategory @category_id
print 'Executing sp_xml_updatecategory from deleted. Cat = ' + convert(varchar,@category_id)
end
else
begin
select @category_id = category_id from Inserted
exec db_psl.sp_xml_updatecategory2 @category_id
print 'Executing sp_xml_updatecategory from Inserted. Cat = ' + convert(varchar,@category_id)
end

---------------------------------------------------------

My question: If I manually run the Stored Procedure or the DTS using Enterprise Manager, the package runs with no problems. If I update a filed which fires the trigger, the DTS script errors out when opening the second connection inside of the activeX script, the error is a timeout, I have tried every combination and still no solution, any help?"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources