BCP utility from Stored Procedure or export result for a Stored Procedure


Export Stored Procedure result with bcp:
 

declare @sql varchar(8000)

select

@sql = ‘bcp "exec test_for_forum.dbo.testCard" queryout c:\test\mytab2.txt -c -t, -T -S’+ @@servername

exec

master..xp_cmdshell @sql

Use bcp in a Stored Procedure:

ALTER

PROCEDURE [dbo].[testBcp]

AS

BEGIN

SET NOCOUNT ON;

declare @sql varchar(8000)

–select @sql = ‘bcp test_for_forum.dbo.Dates out c:\test\mytab.txt -c -t, -T -S’+ @@servername

select @sql = ‘bcp "SELECT TOP 5 * FROM test_for_forum.dbo.Dates" queryout c:\test\mytab.txt -c -t, -T -S’+ @@servername

exec

master..xp_cmdshell @sql

END

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s