2

I've been trying to work on taking the result of a large and multiply-joined SELECT statement, and email the query result as a CVS file. I have the query correct and the emailing down, but I'm having trouble automating the export of the result as a CVS file. From what I've been reading, the best bet for auto-exporting query results is a tool called "BCP".

I attempted to use BCP like this in Management Studio:

USE FootPrint;

DECLARE @sql VARCHAR(2048);
DECLARE @dir VARCHAR(50);

SET @dir = 'C:\Users\bailey\Desktop';
SET @sql = 'bcp "SELECT TOP 10 * FROM datex_footprint.Shipments" queryout "' + @dir + '" -c -t, -T';

EXEC master..xp_cmdshell @sql;

FootPrint is the name of a specific database, and datex_footprint a schema. (This is not the real query, just a test one).

When I run this, the error I get is: "SQLState=S0002, NativeError = 208" "Error = [Microsoft][SQL Server Native Client 10.0][SQL Server] Invalid object name 'datex_footprint.Shipments'."

I am 100% positive that datex_footprint.Shipments is the correct schema\table access for the data I'm trying to test on.

Does anyone see what I'm missing or doing wrong in trying to export this result to a CSV file? Specifically, though, I'm trying to automate this process. I know how to export results into a CSV file, but I want to do it in T-SQL so I can automate the generation of the file by time of day.

Any help would be appreciated!

Mister R2
  • 861
  • 5
  • 12
  • 22

3 Answers3

4

[SOLVED]

I figured out what I was doing wrong. I was not identifying the view in complete form. I was using "schema.Table/View", instead of "database.schema.table/view". Also, I added a "-S" + @@SERVERNAME flag -- this tells the BCP utility to use the server SQL Server is currently connected to for the query.

The correct code to generate a CSV file of a SELECT-query's results in T-SQL, SQL Server 2008 is:

DECLARE @sql VARCHAR(8000);

SELECT @sql = 'bcp "SELECT * FROM FootPrint.datex_footprint.Shipments" queryout "C:\Users\bailey\Desktop\FlatTables\YamotoShipping.csv" -c -t, -T -S' + @@SERVERNAME;

exec master..xp_cmdshell @sql;

So once I added "FootPrint." to identify the database, it worked.

NOTE: I'm running SQL Server 2008 R2.

Mister R2
  • 861
  • 5
  • 12
  • 22
0

I have got same error but I have resolved it in a different way. I have added the default database to my ID then BCP started looking the tables in my default database and processed the files.

Kumar Kannaveni
  • 1
0

After searching and reading the documentation of bcp, what I found was that when we have copy the data we should be using GLobal temp table i.e. ## instead of #.. because in tempdb it will collide and wont allow to copy the data to the destination file.

Example:

DECLARE @OutputFilePath nvarchar(max); SET @OutputFilePath = 'C:\OutputData'
DECLARE @ExportSQL nvarchar(max); SET @ExportSQL = N'EXEC xp_cmdshell ''bcp 
"SELECT * FROM LW_DFS_DIT.dbo.##Mytemptable " queryout "' + @OutputFilePath + 
'\OutputData.txt" -T -c -t '''

EXEC(@ExportSQL)

Hope this would help

Hadi Mirzaei
  • 222
  • 2
  • 16
Abhishek Malviya
  • 1