Search code examples
sqlsql-serverbcp

BCP with WHERE clause Fails

When using BCP to export data from a table to an XML file this works perfectly:

declare @cmd nvarchar(255);
select @cmd = '
    bcp "select AGENT,TERMCD ,MYAMOUNT,CAMPNAME,LCDATE,CAMPAIGNID from [MYDATABASE].[dbo].[CC1] row for xml auto, root(''rows''), elements" '
    + 'queryout "d:\temp\sample.xml" -S DESKTOP-2C2OAE9 -T -w -r -t';
exec xp_cmdshell @cmd;
go

However if I add a simple WHERE clause like below it fails.

declare @cmd nvarchar(255);
select @cmd = '
    bcp "select AGENT,TERMCD ,MYAMOUNT,CAMPNAME,LCDATE,CAMPAIGNID from [MYDATABASE].[dbo].[CC1] WHERE AGENT=''Kelly Martens'' row for xml auto, root(''rows''), elements" '
    + 'queryout "d:\temp\sample.xml" -S DESKTOP-2C2OAE9 -T -w -r -t';
exec xp_cmdshell @cmd;
go

There are the errors:

NULL
Starting copy...
SQLState = 37000, NativeError = 102
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'row'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations
NULL
BCP copy out failed
NULL

I am actually going to have to use LCDATE which is varchar to compare to the current date but I wanted to do something simple initially since I am having problems but if you have thoughts on that too it would be welcome.

Solution

  • Your 'row' table alias moved to after the where, it should be after the table name

    declare @cmd nvarchar(255);
select @cmd = '
    bcp "select AGENT,TERMCD ,MYAMOUNT,CAMPNAME,LCDATE,CAMPAIGNID from [MYDATABASE].[dbo].[CC1] row WHERE AGENT=''Kelly Martens'' for xml auto, root(''rows''), elements" '
    + 'queryout "d:\temp\sample.xml" -S DESKTOP-2C2OAE9 -T -w -r -t';
exec xp_cmdshell @cmd;
go