Search code examples
sqlsql-serversql-server-2005bcp

SQL Server 2005 write to file with bcp

Attempting to write query results to file. Using the following in SQL Server 2005:

EXEC xp_cmdshell "bcp 'select License + '-' + ISNULL(Name,'') as Employer from People' queryout 'c:\People\text.txt' -c -x -T,"

I've played around with the single and double quotes, with differing syntax errors. In the current form, this returns the error "Copy direction must be either 'in', 'out' or 'format'."

I've also toyed around with this on the command line and I always get "cannot find the file specified.

Full text of stored procedure:

EXEC
master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC
master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC xp_cmdshell "bcp 'select contrLice + '-' + ISNULL(contrName,'') as Employer from Contractors' queryout 'c:\Contractors\text.txt' -c -x -T,"

Any advice?

Solution

  • You need to double up the single quotes:

    exec xp_cmdshell "bcp 'select License + ''-'' + coalesce(Name, '''') as Employer from People' queryout 'c:\People\text.txt' -c -x -T,"