SQL Server 2008 R2: Export data using bcp command and store bcp output into variable

Question

I want to export data using bcp command and the output of the bcp command should be stored in the variable.

My try:

DECLARE @Result varchar(max)
DECLARE @SQL nvarchar(max)

SET @SQL = N'Execute xp_cmdshell ''bcp "SELECT * FROM EMP" QueryOut  "E:\BCP\Result.pec"   -T -t@_@ -c -o "E:\BCP\LogReport.txt"'''

EXEC sp_executesql @SQL, N'@Result nvarchar(75) OUTPUT', @Result =@Result output

PRINT(@Result)

But getting an error in the output:

output
------------------------------------------------------------------------------
bcp: Unable to open output file E:\BCP\LogReport.txt: No such file or directory
NULL

Questions:
1. How to store the above output result into the variable?
2. Given permission to the file and folder too, but still getting this error.

Where is the drive & directory located? Server side of client side? Note that any directory you pass should be located server side and that the SQL Server account needs access to that directory. — TT.
– TT., Commented Aug 29, 2018 at 9:26

Killer Queen · Accepted Answer · 2018-08-29 09:24:27Z

2

---------------------------------------- 1 -------------------------------------------

DECLARE @Result TABLE
(error_msg VARCHAR(800))

DECLARE 
    @SQL varchar(8000),
    @Result_var VARCHAR(MAX)=''

SET @SQL = N'bcp "SELECT * FROM <MY_DATABASE>.<MY_SCHEMA>.<MY_TABLE>" QueryOut  "E:\BCP\Result.pec"   -T -t@_@ -c -o "E:\BCP\LogReport.txt"'''

-- Insert the output from xp_cmdshell into the table @Result
INSERT INTO @Result (error_msg)
EXEC xp_cmdshell @SQL

-- Merge the rows
SELECT @Result_var = @Result_var + ' ' + ISNULL(error_msg,'') FROM @Result

-- Output in variable
PRINT @Result_var
--------------------------------------------------------------------------------------

2. For me, your command works, check access to folders and subfolders (for database user), check permissions on the sql server.

edited Aug 29, 2018 at 9:24

answered Aug 29, 2018 at 9:19

Killer Queen

7569 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

STLDev Over a year ago

It would be good to point out here that the OP's issue was caused by trying to execute an external command, bcp.exe, by using sp_executesql, instead of xp_cmdshell.

Collectives™ on Stack Overflow

SQL Server 2008 R2: Export data using bcp command and store bcp output into variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related