Prev: SQL Server 2005 Replication and Third Party Tool
Next: Run Query from a Column Value
From: Muhammad Bilal on 30 Apr 2010 18:04
Hi.

Is it possible to execute a procedure from a BCP Batch file for SQL Server
2000.


Regrds,
Muhammad Bilal
From: John Bell on 1 May 2010 04:49
On Fri, 30 Apr 2010 15:04:02 -0700, Muhammad Bilal
<MuhammadBilal(a)discussions.microsoft.com> wrote:

>Hi.
>
>Is it possible to execute a procedure from a BCP Batch file for SQL Server
>2000.
>
>
>Regrds,
>Muhammad Bilal

Hi Muhammad

Such as:
USE AdventureWorks
GO

CREATE PROCEDURE prc_Contact AS
SELECT FirstName, LastName FROM AdventureWorks.Person.Contact ORDER BY
LastName, Firstname ;
GO

Then from a batch file:

bcp "EXEC AdventureWorks..prc_Contact" queryout Contacts.txt -c -T

John
From: Muhammad Bilal on 1 May 2010 08:56
Thankx for the reply.

But if the procedure take a parameter and there is no output of the
procedure than how it should be done.

Regards,
Muhammad Bilal

"John Bell" wrote:

> On Fri, 30 Apr 2010 15:04:02 -0700, Muhammad Bilal
> <MuhammadBilal(a)discussions.microsoft.com> wrote:
>
> >Hi.
> >
> >Is it possible to execute a procedure from a BCP Batch file for SQL Server
> >2000.
> >
> >
> >Regrds,
> >Muhammad Bilal
>
> Hi Muhammad
>
> Such as:
> USE AdventureWorks
> GO
>
> CREATE PROCEDURE prc_Contact AS
> SELECT FirstName, LastName FROM AdventureWorks.Person.Contact ORDER BY
> LastName, Firstname ;
> GO
>
> Then from a batch file:
>
> bcp "EXEC AdventureWorks..prc_Contact" queryout Contacts.txt -c -T
>
> John
> .
>
From: Erland Sommarskog on 1 May 2010 11:02
Muhammad Bilal (MuhammadBilal(a)discussions.microsoft.com) writes:
> But if the procedure take a parameter

You specify it:

bcp "EXEC AdventureWorks..prc_Contact 199" queryout Contacts.txt -c -T

> and there is no output of the procedure than how it should be done.

Well, if there is no output, then using BCP to call the procedure is
probably not the best of ideas. You would be better off using OSQL:

OSQL -E -Q"EXEC AdventureWorks..prc_Contact 199" -n

Overall, using stored-procedure calls with BCP QUERYOUT is not always
straightforward. If the procedure just runs a query, no sweat. But to
figure out how the result looks like, BCP first runs the batch with
SET FMTONLY ON, which is a sort of no-execute where SQL Server returns
information about result sets. But this method is fragile, and will fail
under several circumstances. For instance if you create a temp table
in the procedure.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: SQL Server 2005 Replication and Third Party Tool
Next: Run Query from a Column Value