sqlsql-server-2008-r2bcpxp-cmdshell

xp_cmdshell Native Error 208, BCP in SQL Server 2008 R2

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!

Solution

  • [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.