Procedure hungs while calling xp_cmdshell

Question

Procedure hungs while calling xp_cmdshell

Sudiendra

SSCommitted

Points: 1839
More actions
March 24, 2009 at 12:54 pm

#132498

We are having a problem related to the execution of xp_cmdshell within a stored procedure that is executed by another stored procedure.
The stored procedure SP1 executes the stored procedure Sp2, which in turn executes master.xp_cmdshell.
When we execute SP1 directly via the command line , it runs to completion with no errors.
When we execute SP2, via the command line , the procedure hangs at the xp_cmdshell step.
Any thoughts or feedback on why this is happening?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sudiendra SSCommitted Points: 1839 More actions · Answer 1

Sorry its other way around Sp2 calls Sp1 and Sp1 has got this xp_cmdshell. Sp1 when executed on command line works. But when SP2 calls Sp1, it hangs

xp_cmdshell query is

SET @cmd = 'bcp "select * from ##OutputExceptions2" queryout ' + @Path + @FileName + ' -c -t, -S ' + @@SERVERNAME + ' -T'

insert #CmdOutput EXEC master..xp_cmdshell @cmd

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 2

You are probably within a blocking transactions. You must have done an insert in the global temp table, or an update, and the table is still locked to the procedure.

When you execute xp_cmdshell to export the data to disk, it opens another connection, which cannot read from the "##" global temp table.

Now what you can do is either find another way of exporting the data, or launch another action, after the completion of the SP1.

Is that code called from .net code?

Cheers,

J-F

Sudiendra SSCommitted Points: 1839 More actions · Answer 3

Thanks a lot for the input.

am not sure of application that calls..but most probably be .NET. Do we have any alternative in .NET..

any suggestions about alternatives ?

Thanks,

Sudhie.

Sudiendra SSCommitted Points: 1839 More actions · Answer 4

J-F

it hangs only when called from other stored proc, when executed itself it will work. so global variables shouldn't be an issue right.

Correct me if i am wrong..

Thanks,

Sudhie.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 5

When you execute that procedure, within sql server, or within a .net application, the connection gets opened with a SPID. That id is your connection ID. If you "block" the table within this connection (i.e update rows from it, or insert data into it), and then execute the cmdshell right after the blocking operation, the table is locked for the cmdshell, and therefore cannot be accessed from another SPID.

When you execute the cmdShell that does the bcp, or whatever exporting operation, there is a new connection created, (with its own SPID), that tries to query the data you have "Just" created within the other connection (your SP). This is where you get blocked.

You can execute the same SP, but insert in a real table (not a global temp table), and then launch ANOTHER SP, (not under the same SP), and the data will not be locked, since the other connection is closed.

I might not be expressiong myself really clearly, so I'll add some pseudo code here.

Calling application --> calls the first SP (that creates the output data, and inserts it in a REAL table)

Calling application --> calls the second sp, that will export the data that is in the REAL table (but not temp) and then could drop the table? Depends on your requirements.

Hope that helps,

Cheers,

J-F

Sudiendra SSCommitted Points: 1839 More actions · Answer 6

Thanks a lot J-F. I have suggested dev to perform those changes. will post the updates here.

Thanks,

Sudhie.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 7

No problem, thanks for your feedback, it is always appreciated to know we can help!

Cheers,

J-F