BCP Command with Parameters

Question

BCP Command with Parameters

fahamed

SSC-Addicted

Points: 489
More actions
October 22, 2009 at 3:35 am

#216554

How can I execute a Stored Procedure with parameters through BCP command.
I have the following BCP statement and does not work.
bcp "exec MyDB..Letter_UPDATE @UserName @JobNumber" in "'TEST','123456'" -SMyServer -UMyusername -PMyPassword
Stored Procedure name: Letter_UPDATE
Paramters :@UserName, @JobNumber
Passing Data : "TEST", "123456"
All my other BCP commands are working fine, this is the only SP which has parameters and it is killing me. I hope it is a simple solution.......
Help me......

Viewing 6 posts - 1 through 5 (of 5 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

It seems to me that you are using wrong direction. You specified in your command line the direction as in. I think that it should be queryout.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

fahamed SSC-Addicted Points: 489 More actions · Answer 2

Thank you for your reply. However, I tried with queryout and still did not work. Have I used the correct syntax or BCP with SP.....except queryout....

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

You can't use BCP with parameters unless you turn it into dynamic SQL.

--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)

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

I should have paid attention more carefully to the question. It isn’t enough to change the in direction to queryout direction. You also have to specify the parameters’ values in the string. Fallowing your example try to run this BCP:

Bcp “EXEC MyDB..Letter_UPDATE ‘TEST’, ‘123456’” –SmyServer –UmyUserName –PmyPassword.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

fahamed SSC-Addicted Points: 489 More actions · Answer 5

Hi Adi

Thank you for your assistance, it worked....