SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Stored procedure blocking a second stored procedur
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssaresky
Starting Member

Spain
4 Posts

Posted - 09/07/2010 :  06:00:20  Show Profile  Reply with Quote
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

Sweden
30217 Posts

Posted - 09/07/2010 :  06:16:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Any reason you still use DTS instead of SSIS?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

ssaresky
Starting Member

Spain
4 Posts

Posted - 09/07/2010 :  06:23:03  Show Profile  Reply with Quote
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
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 09/07/2010 :  08:32:14  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000