February 24, 2009 at 10:19 am
I have a script that includes a line using xm_cmdshell to bcp some data to a file a the hard drive. If I execute just the xp_cmdshell line of the script the file gets created as i expect in less than a second. If I run the entire script it runs fine until it gets to the xp_cmdshell line, then it just hangs and i have to kill the thread to get control back.
What can i do that will allow the xp_cmdshell line to complete?
February 24, 2009 at 1:16 pm
you would be better off using a separate step in the job and use sqlcmd to export the data or use TSQL backup to create a full db backup.
- does the job owner have access to the xp_cmdshell subsystem ?
- does the proxy account have access to the folder you want to create the export file ?
- does the sqlagent have access ...
-don't use xp_cmdshell unless you have no other options.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 24, 2009 at 1:31 pm
can you give us an example of the command line?
I think your command line require a user input, or something
February 24, 2009 at 3:45 pm
The command in question looks like this -
EXEC MASTER..xp_cmdshell 'BCP "EXEC GfmReplication.dbo.zz_ExportXml" queryout c:\Org.xml -S(local) -T -c'
The problem is that my script, being part of a unit test, was enclosed between BEGIN TRAN and ROLLBACK TRAN statements. The BEGIN TRAN statement locked the tables the referenced proc was trying to access. I moved the xp_cmdshell command outside the transaction and solved the problem with a few other changes to the unit test logic.
February 24, 2009 at 11:48 pm
Mark Pratt (2/24/2009)
The command in question looks like this -EXEC MASTER..xp_cmdshell 'BCP "EXEC GfmReplication.dbo.zz_ExportXml" queryout c:\Org.xml -S(local) -T -c'
The problem is that my script, being part of a unit test, was enclosed between BEGIN TRAN and ROLLBACK TRAN statements. The BEGIN TRAN statement locked the tables the referenced proc was trying to access. I moved the xp_cmdshell command outside the transaction and solved the problem with a few other changes to the unit test logic.
Works as designed, 100% as it should be.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply