BCP Utility - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

BCP Utility

Author

Topic

Tiwari
Starting Member

18 Posts

Posted - 2002-07-17 : 07:24:15

Any body can help? How to use bcp utility please give some example to make me more clear and i can able to run that example on query analyzer

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-17 : 07:31:34

bcp is an os executable and will never run (directly) in qa....I'd suggest you read up on the BCP utility in Books Online and come back with specific questions.

<O>

Tiwari
Starting Member

18 Posts

Posted - 2002-07-17 : 07:36:44

i m using the code below for BCP but it is not working.
Please guide me where i m wrong.And if possible pls modify the given lines of code.

DECLARE @CHRCMD AS VARCHAR(40)
DECLARE @INTRESULT VARCHAR(10)

SET @chrCmd = 'bcp "select top 1 * from applicator " queryout '+ 'C:\Prashant\DUMMY.txt' + '-c'
exec @intResult = master..xp_cmdshell @chrCmd, No_Output
print @intResult

Note:I am trying it on the server and database logged in.Thats why i have not given the Servername ,DB name and Password.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-17 : 07:40:32

You have to specify a server and login on the command line because bcp runs on its own connection, it knows nothing about whether it's being run from Query Analyzer or whether you are already connected to the SQL Server.

Go back and read about bcp in Books Online (again), it states very clearly which settings are required.

Edited by - robvolk on 07/17/2002 07:40:49

Tiwari
Starting Member

18 Posts

Posted - 2002-07-17 : 07:48:13

even after giving server name,uid and pwd it is not working

DECLARE @CHRCMD AS VARCHAR(40)
DECLARE @INTRESULT VARCHAR(10)

SET @chrCmd = 'bcp "select top 1 * from applicator " queryout '+ 'C:\Prashant\DUMMY.txt' + '-c'+ '-SNilgiri -Usa -P123456'
exec @intResult = master..xp_cmdshell @chrCmd, No_Output
print @intResult

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-17 : 07:52:16

Probably because your @chrcmd variable is declared too small for the new command:

DECLARE @CHRCMD AS VARCHAR(1000)
DECLARE @INTRESULT int --this should be int, not varchar

SET @chrCmd = 'bcp "select top 1 * from applicator " queryout C:\Prashant\DUMMY.txt -c -SNilgiri -Usa -P123456'
exec @intResult = master..xp_cmdshell @chrCmd, No_Output
print @intResult

The original 40 character length probably cut off the added text you needed.

You don't need to concatenate the strings together unless you have variable/dynamic portions of the command.

Tiwari
Starting Member

18 Posts

Posted - 2002-07-17 : 08:00:38

But still its not working

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-17 : 08:06:11

Do this:

DECLARE @CHRCMD AS VARCHAR(1000)
DECLARE @INTRESULT int --this should be int, not varchar

SET @chrCmd = 'bcp "select top 1 * from applicator " queryout C:\Prashant\DUMMY.txt -c -SNilgiri -Usa -P123456'
exec master..xp_cmdshell @chrCmd

And see if the output file is created. If something goes wrong you'll get an error message, and you'll have to post that error before we can go further.

Tiwari
Starting Member

18 Posts

Posted - 2002-07-18 : 02:47:12

quote:

Do this:

DECLARE @CHRCMD AS VARCHAR(1000)
DECLARE @INTRESULT int --this should be int, not varchar

SET @chrCmd = 'bcp "select top 1 * from applicator " queryout C:\Prashant\DUMMY.txt -c -SNilgiri -Usa -P123456'
exec master..xp_cmdshell @chrCmd

And see if the output file is created. If something goes wrong you'll get an error message, and you'll have to post that error before we can go further.

Tiwari
Starting Member

18 Posts

Posted - 2002-07-18 : 02:48:54

[quote]
Do this:

DECLARE @CHRCMD AS VARCHAR(1000)
DECLARE @INTRESULT int --this should be int, not varchar

SET @chrCmd = 'bcp "select top 1 * from applicator " queryout C:\Prashant\DUMMY.txt -c -SNilgiri -Usa -P123456'
exec master..xp_cmdshell @chrCmd

And see if the output file is created. If something goes wrong you'll get an error message, and you'll have to post that error before we can go further.

It is still not working.
The o/p is
Output
1.SQLState = S0002, NativeError = 208
2.Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'applicator'.
3.SQLState = 37000, NativeError = 8180
4.Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
5.NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources