Execute store procedure from BCP

Question

Execute store procedure from BCP

manaprime

SSChasing Mays

Points: 650
More actions
May 27, 2008 at 10:02 am

#121617

I have a batch file that executes the BCP command.Now i just want to execute a store procedure with two date input parameters from BCP. The store procedure is working fine in Query Analyser. I just want to execute it from BCP that is in batch file.
thank you

Viewing 8 posts - 1 through 7 (of 7 total)

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

Bulent Gucuk SSCertifiable Points: 5497 More actions · Answer 1

manaprime (5/27/2008)
I have a batch file that executes the BCP command.Now i just want to execute a store procedure with two date input parameters from BCP. The store procedure is working fine in Query Analyser. I just want to execute it from BCP that is in batch file.
thank you

Take a look at the SQLCMD utility. Using it from command line you can connect to SQL server and execute stored procedure or issue an ad hoc queries.

Good Day,

Bulent

Steve Jones - SSC Editor SSC Guru Points: 735657 More actions · Answer 2

Use SQLCMD as mentioned. BCP doesn't run stored procedures.

Gordon-265412 SSCrazy Points: 2898 More actions · Answer 3

You want either osql or SQLCMD and the -Q option for either.

BCP will only execute a query for bulk loading or unloading tables.

Your friendly High-Tech Janitor... 🙂

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 4

Ummm... BCP can execute an "EXEC" as part of that query...

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

BCP.exe does support providing a select statement or the execution of a stored procedure in addition to specifying a table. Suggest that the stored procedure name be fully qualified.

Here is the results of a test with SQL Server 2008:

C:\>bcp.exe "exec [AdventureWorks].[dbo].[uspGetEmployeeManagers] @EmployeeID = 153" queryout "c:\EmployeeManagers.txt"

-SCFEDERL -T -c

Starting copy...

4 rows copied.

Network packet size (bytes): 4096

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

SQL = Scarcely Qualifies as a Language

manaprime SSChasing Mays Points: 650 More actions · Answer 6

Hi.

Thanx for reply i am just running this in batch file

bcp"exec [DBNAME].[dbo].[SProcName]" queryout \\Severname\Projects\temp.CSV -S ServerName -U sa -P -t, -C

but i am getting this error message

bcp is not recognized as an internal or external command, operable program or batch file

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 7

Not sure why the dilema... error message says what's wrong. You've either not installed it when you installed SMS or it's simply not available on the machine that you're using or there's no path to it. Perhaps someone screwed up and didn't install it or deleted it from the server, as well.

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