Stored procedure blocking a second stored procedur

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

General SQL Server Forums

Database Design and Application Architecture

Stored procedure blocking a second stored procedur

Author

Topic

ssaresky
Starting Member

4 Posts

Posted - 2010-09-07 : 06:00:20

Hi all, wonder if you could help me here. Please, take a look at the following sp:

ALTER Procedure [CFTUser].[sp_MonthlyUpdate]
( @fileName varchar(1000) ) as
begin

declare @cmd varchar(128)
set @cmd = 'DTSRun /S UK-SQL-UAT-010 /E /N CFT_MONTHLY_UPDATE /W 0 /A SourceFile:8='
set @cmd = @cmd + @fileName
execute master.dbo.xp_cmdshell @cmd --runs a dts.

/*
bla, bla, bla...
*/

execute CFTUser.sp_MonthlyUpdateFromDTS

end

Ok, this is a SP that worked fantastically on SQL 2000 and that has been migrated to SQL 2005. This SP is called by the application who's sending the file path in the app server as the parameter for this SP. The DTS does the upload successfully filling up a temporary physical table. The problem is that it now times-out.

For what I found, the first SP is blocking the second one preventing it to perform. It takes so long to fee it and execute that the application considers it a time out and then cancell the request.

I know I can go back into the application and change it so it first call the initial SP executing the DTS and, when it succeeds, then call the second SP directly from the application (instead of executing it from the first SP with the EXEC command).

But in any case I'd love to know what I'm doing wrong here.

Thank you all in advance!

Sebastian

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-07 : 06:16:49

Any reason you still use DTS instead of SSIS?

N 56°04'39.26"
E 12°55'05.63"

ssaresky
Starting Member

4 Posts

Posted - 2010-09-07 : 06:23:03

Yes, the reason is the db admin who didn't implement it :)
But as said, the DTS performs just ok.

The problem is the blockage to the second sp.

Cheers.

Sebastian

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-09-07 : 08:32:14

Do you know if it get's past this stage?
execute master.dbo.xp_cmdshell @cmd --runs a dts.
and into this
/*
bla, bla, bla...
*/

it could be that DTS isn't "handing itself back" to the SP.

Can you substitute a "dummy o/s command" into @cmd and see if the full SP works? If so, then the problem is buried within DTS (or more particularly the way you are calling it)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources