bcp question

Question

Post reply

bcp question

SQLQuest29

SSCrazy Eights

Points: 8264
More actions
September 20, 2010 at 7:43 pm

#146973

Hello All,
I am trying to bcp out some data, but this is really becoming something that I need some help on.
Basically, I was to bcp out a single row from a table into a .xdl file using below command:
BCP "SELECT TOP 1 [DeadlockGraph].query('/TextData/deadlock-list') FROM [dbname].[dbo].[tablename] ORDER BY Id DESC" queryout xmlfile.xdl -T -c –q
This works perfectly using cmd line.
Also, when I go to
cmd --> bcp ==> this has no queryout option
but C:\Program Files\Microsoft SQL Server\90\Tools\Binn ==> bcp has queryout option.
how can I do this using TSQL. I dont want to use any .bat file.
below is what I have tried:
declare @sql varchar(max)
set @sql = '
BCP "SELECT TOP 1 [DeadlockGraph].query('/TextData/deadlock-list') FROM [dbname].[dbo].[tablename] ORDER BY Id DESC" queryout xmlfile.xdl -T -c –q '
exec xp_cmdshell @sql
but this is giving errors.
Can any one help or shed some knowledge ?
Thanks,
Cheers !
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

but this is giving errors

It sure would be nice if you'd post exactly what those errors are. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Oliiii SSCertifiable Points: 5328 More actions · Answer 2

Check your PATH variable, another version of bcp.exe is probably in it (or even the bcp from sybase).

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 3

Hello,

when I use this:

declare @sql varchar(max)

set @sql = '"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\BCP.exe" "SELECT top (1) [Deadlockgraph].query(''/TextData/deadlock-list'') FROM dbname.dbo.tablename" queryout d:\xmlfile.xdl -T -c -q'

exec master..xp_cmdshell @sql

this is the error:

Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1

Procedure expects parameter 'command_string' of type 'varchar'.

when I use this:

DECLARE @sql nvarchar(1000),@Query nvarchar(300)

SET @Query='"SELECT top (1) [Deadlockgraph].query(''/TextData/deadlock-list'') FROM dbname.dbo.tablename'

set @sql = '"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\BCP.exe" '

SET @sql=@SQL+@Query+'" queryout "'+'d:\xmlfile.xdl'+N'" -c -q -T -Slocalhost'

print @sql

exec master..xp_cmdshell @sql

this is the error:

'C:\Program' is not recognized as an internal or external command,

operable program or batch file.

NULL

Any ideas ...

Cheers !

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 4

Any ideas whats wrong ? 😉

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 5

I can't run your exact queries, so I came up the same kind of query as you had.

You basically want the xp_cmdshell to do something like this:

EXEC master..xp_cmdshell '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\BCP.exe" "SELECT top (1) * from pubs.dbo.authors" queryout "C:\temp\authors_top_1.txt" -c -q -T -Slocalhost'

While the error message states that xp_cmdshell does not recoginize the quoted path of BCP command executables. I had the same error message on my local PC too. So this might be a limitation from xp_cmdshell procedure.

But if I use:

EXEC master..xp_cmdshell 'BCP "SELECT top (1) * from pubs.dbo.authors" queryout "C:\temp\authors_top_1.txt" -c -q -T -Slocalhost'

This works fine with the following output:

output

NULL

Starting copy...

NULL

1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total : 1 Average : (1000.00 rows per sec.)

NULL

So I think the simplest solution for you is the verfiy the PATH environment variable in your windows system and make command 'BCP' recognizable from a simple command line. Then you can stay away from those quoted long path of BCP.exe.

Hope this helps, cheers!

Bazinga!

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 6

Hey mate

I've found an ultimate solution for all the non-standard executables to be run in xp_cmdshell.

The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.

Here is the the query with the same functionality and it works perfectly:

exec master..xp_cmdshell 'C:\PROGRA~1\MI6841~1\100\Tools\Binn\BCP.exe "SELECT top (100) * from pubs.dbo.authors" queryout "C:\temp\authors_top_100.txt" -c -q -T -Slocalhost'

Here PROGRA~1 represents "Program Files" while MI6841~1 represents "Microsoft SQL Server".

To get the shortened path names, you have to use dir /X (this is a capital X)in the target directory.

I think this should be a definitive solution to the xp_cmdshell limitation on path names.

Bazinga!

sjimmo SSChampion Points: 11139 More actions · Answer 7

The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.

This is actually a limitation of earlier versions of DOS, which do not understand a space in the path name. Usually, putting the path within quotes will work as well as using the tilde (~)

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 8

sjimmo (9/24/2010)
The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.
This is actually a limitation of earlier versions of DOS, which do not understand a space in the path name. Usually, putting the path within quotes will work as well as using the tilde (~)

The orginal poster and I have already tried to quote the path, but that doesn't work.

That's why we have to revert back to the 'tilde' solution.

Bazinga!

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 9

sqlapprentice (9/24/2010)
sjimmo (9/24/2010)
The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.
This is actually a limitation of earlier versions of DOS, which do not understand a space in the path name. Usually, putting the path within quotes will work as well as using the tilde (~)
The orginal poster and I have already tried to quote the path, but that doesn't work.
That's why we have to revert back to the 'tilde' solution.

Perfect mate .. It did work for me. Cannot imagine that it does not like the full path 🙂

Thanks a lot !

Cheers !

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

SQL_Quest-825434 (9/24/2010)
sqlapprentice (9/24/2010)
sjimmo (9/24/2010)
The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.
This is actually a limitation of earlier versions of DOS, which do not understand a space in the path name. Usually, putting the path within quotes will work as well as using the tilde (~)
The orginal poster and I have already tried to quote the path, but that doesn't work.
That's why we have to revert back to the 'tilde' solution.
Perfect mate .. It did work for me. Cannot imagine that it does not like the full path 🙂
Thanks a lot !
Cheers !

Sorry I didn't get back to this earlier. Looks like you're all set now. It's been a crazy week and I'm still trying to catch up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 11

Jeff Moden (9/30/2010)
SQL_Quest-825434 (9/24/2010)
sqlapprentice (9/24/2010)
sjimmo (9/24/2010)
The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.
This is actually a limitation of earlier versions of DOS, which do not understand a space in the path name. Usually, putting the path within quotes will work as well as using the tilde (~)
The orginal poster and I have already tried to quote the path, but that doesn't work.
That's why we have to revert back to the 'tilde' solution.
Perfect mate .. It did work for me. Cannot imagine that it does not like the full path 🙂
Thanks a lot !
Cheers !
Sorry I didn't get back to this earlier. Looks like you're all set now. It's been a crazy week and I'm still trying to catch up.

Hello Jeff,

That's perfectly fine and at SSC we value everybody's time and effort. I appreciate your concern.

Cheers ! 🙂

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 12

Jeff Moden (9/30/2010)
SQL_Quest-825434 (9/24/2010)
sqlapprentice (9/24/2010)
sjimmo (9/24/2010)
The problem lies in that fact that xp_cmdshell does not recoginize lang windows paths but we can still use shortend path names with tilde (~) in them.
This is actually a limitation of earlier versions of DOS, which do not understand a space in the path name. Usually, putting the path within quotes will work as well as using the tilde (~)
The orginal poster and I have already tried to quote the path, but that doesn't work.
That's why we have to revert back to the 'tilde' solution.
Perfect mate .. It did work for me. Cannot imagine that it does not like the full path 🙂
Thanks a lot !
Cheers !
Sorry I didn't get back to this earlier. Looks like you're all set now. It's been a crazy week and I'm still trying to catch up.

Hello Jeff,

That's perfectly fine and at SSC we value everybody's time and effort. I appreciate your concern.

Cheers ! 🙂

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂