September 30, 2009 at 5:56 am
Hello,
I'm using SQL 2005 Standard and trying to execute some commands, which isn't quite easy. So, the following works fine...
DECLARE @helperStr8000 AS VARCHAR(8000)
SET @helperStr8000='bcp dbname..tablename out "C:\Path\To\Dir\table.blk" -k -T -N -m 0'
EXEC master.dbo.xp_cmdshell @helperStr8000, no_output
...and this works too...
DECLARE @helperStr8000 AS VARCHAR(8000)
SET @helperStr8000='bcp "SELECT * FROM dbname..tablename" queryout "C:\Path\To\Dir\table.blk" -k -T -N -m 0'
EXEC master.dbo.xp_cmdshell @helperStr8000, no_output
...but this makes me sick...
DECLARE @helperStr8000 AS VARCHAR(8000)
SET @helperStr8000='bcp "SELECT * FROM dbname..tablename x WHERE EXISTS (SELECT * FROM otherdb..othertable WHERE col=x.col)" queryout "C:\Path\To\Dir\table.blk" -k -T -N -m 0'
EXEC master.dbo.xp_cmdshell @helperStr8000, no_output
this command is running and running and running and never stops...why? The strange thing is, when running exact the same command on windows command line (cmd.exe => bcp "SELECT * FROM dbname..tablename x WHERE EXISTS (SELECT * FROM otherdb..othertable WHERE col=x.col)" queryout "C:\Path\To\Dir\table.blk" -k -T -N -m 0) all is fine and the file table.blk is created.
I'm running all scripts as administrator. I also tried to build a view for this select statement and let bcp running a SELECT * FROM viewname and I tried to put this bcp command into a batch file with xp_cmdshell and execute this batch file in a second xp_cmdshell call, but all failed. Why is an execution of this bcp-command on windows command line possible, but not under TSQL with xp_cmdshell?
Could someone help?
September 30, 2009 at 7:06 am
My guess your alias references are resulting to these strange findings !
Did you try using aliasses for both objects
DECLARE @helperStr8000 AS VARCHAR(8000)
SET @helperStr8000='bcp "SELECT * FROM dbname..tablename x
WHERE EXISTS (SELECT * FROM otherdb..othertable Y
WHERE Y.col=x.col)" queryout "C:\Path\To\Dir\table.blk" -k -T -N -m 0'
EXEC master.dbo.xp_cmdshell @helperStr8000, no_output
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
September 30, 2009 at 7:34 am
For the case where it never stops, can you see the process running in SQL Server (select * from sys.sysprocesses)?
If so, does it show as blocked... are the CPU and/or IO increasing.
Can you BCP in the list of processes in task manager?
September 30, 2009 at 12:59 pm
When endless running, there is the following situation (Windows XP Prof.):
CPU 0%
BCP is in task list with 0% CPU and nearly 4 MB memory consumption
there is also a cmd.exe in task list with 0% CPU and 2 MB memory c. (don't know if this is in relation to bcp.exe)
SELECT * FROM sys.sysprocesses gives the following important rows => see attached image (spid 54 is the script I'm running)
The real bcp statement I want to execute is:
bcp "SELECT * FROM backuptest3.dbo.arguments x WHERE EXISTS (SELECT * FROM testMeta.dbo.hashs_655 y WHERE y.hash=testMeta.dbo.testHashData('MD5',CAST(x.argument AS VARBINARY(MAX)))+testMeta.dbo.testHashData('MD5',CAST(x.param AS VARBINARY(MAX)))+testMeta.dbo.testHashData('MD5',CAST(x.value AS VARBINARY(MAX))))" queryout "C:\Diplom\backuptest3\backup_152\table_655" -k -T -N -m 0
But this doesn't work too (so the aliases don't seem to be the problem):
bcp "SELECT * FROM testMeta.dbo.hashs_660 x WHERE EXISTS (SELECT * FROM testMeta.dbo.hashs_660 y WHERE x.hash=y.hash)" queryout "C:\Diplom\backuptest3\backup_153\table_660" -k -T -N -m 0
Again: this works =>
bcp "SELECT * FROM testMeta.dbo.hashs_660 x" queryout "C:\Diplom\backuptest3\backup_153\table_660" -k -T -N -m 0
September 30, 2009 at 1:52 pm
info-1153976 (9/30/2009)
SELECT * FROM sys.sysprocesses gives the following important rows => see attached image (spid 54 is the script I'm running)
Spid 54 is the process that is executing xp_cmdshell, but the actual BCP task is being spawned in another process, probably spid 55, which you will see is being blocked by spid 54. You are obviously doing something in the script before running the xp_cmdshell that is locking one of the tables that BCP needs. If you look in the waitresource column of the image, you can see the id of the object that BCP is waiting for.
September 30, 2009 at 2:28 pm
info-1153976 (9/30/2009)
When endless running, there is the following situation (Windows XP Prof.):CPU 0%
BCP is in task list with 0% CPU and nearly 4 MB memory consumption
there is also a cmd.exe in task list with 0% CPU and 2 MB memory c. (don't know if this is in relation to bcp.exe)
SELECT * FROM sys.sysprocesses gives the following important rows => see attached image (spid 54 is the script I'm running)
The real bcp statement I want to execute is:
bcp "SELECT * FROM backuptest3.dbo.arguments x WHERE EXISTS (SELECT * FROM testMeta.dbo.hashs_655 y WHERE y.hash=testMeta.dbo.testHashData('MD5',CAST(x.argument AS VARBINARY(MAX)))+testMeta.dbo.testHashData('MD5',CAST(x.param AS VARBINARY(MAX)))+testMeta.dbo.testHashData('MD5',CAST(x.value AS VARBINARY(MAX))))" queryout "C:\Diplom\backuptest3\backup_152\table_655" -k -T -N -m 0But this doesn't work too (so the aliases don't seem to be the problem):
bcp "SELECT * FROM testMeta.dbo.hashs_660 x WHERE EXISTS (SELECT * FROM testMeta.dbo.hashs_660 y WHERE x.hash=y.hash)" queryout "C:\Diplom\backuptest3\backup_153\table_660" -k -T -N -m 0Again: this works =>
bcp "SELECT * FROM testMeta.dbo.hashs_660 x" queryout "C:\Diplom\backuptest3\backup_153\table_660" -k -T -N -m 0
From the BOL entry on XP_CMDSHELL:
' command_string '
Is the string that contains a command to be passed to the operating system. command_string is varchar(8000) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is required if any spaces are present in the file paths or program names referenced in command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.
I can see at least two pairs of quotation marks in your examples.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2009 at 2:29 pm
Ha, just some minutes before your post, I have the same intuition.
I'm trying to script a backup tool for backing up only some specific tables of a database. So I set snapshot isolation to on and set transaction isolation level to snapshot to warrant a consistent snapshot of all data. My unawareness was, that this also affects the view on data in other databases.
As you can see, my real script is
bcp "SELECT * FROM backuptest3.dbo.arguments x WHERE EXISTS (SELECT * FROM testMeta.dbo.hashs_655 y WHERE y.hash=testMeta.dbo.testHashData('MD5',CAST(x.argument AS VARBINARY(MAX)))+testMeta.dbo.testHashData('MD5',CAST(x.param AS VARBINARY(MAX)))+testMeta.dbo.testHashData('MD5',CAST(x.value AS VARBINARY(MAX))))" queryout "C:\Diplom\backuptest3\backup_152\table_655" -k -T -N -m 0
So I set snapshot isolation for database backuptest3 to on. The script is running in database testMeta and before calling the bcp script I set transaction isolation level=snapshot. I thought this does affect only database backuptest3 because snapshot isolation is not set for testMeta. That's difficult. Is there any proper solution for my problem?
(If you still don't understand what I want to do with this script, then let me know. I will explain it more detailed then.)
September 30, 2009 at 2:32 pm
RBarryYoung (9/30/2009)
From the BOL entry on XP_CMDSHELL:' command_string '
Is the string that contains a command to be passed to the operating system. command_string is varchar(8000) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is required if any spaces are present in the file paths or program names referenced in command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.
I can see at least two pairs of quotation marks in your examples.
Sure, I read this extract too, but this doesn't seem to be the problem, because this syntax works (it doesn't work only in some different constellations). But the problem is another one (thanks to Ian)...read my last post
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply