CODEWITHSUNDEEP
sqlbcp
Purplegoldfish
Purplegoldfish

Reputation: 5284

Problem with BCP writing to .txt file from SQL

Im using Sql2008 trying to run this BCP command but it never creates the file.

-- Export query
  DECLARE @qry2 VARCHAR(1000)
  SET @qry2 = 'SELECT * FROM @SkippedProductsTable'

  -- Folder we will be putting the file in         
  DECLARE @incomingfolder VARCHAR(1000)
  SET @incomingfolder = 'c:\Logs'

  DECLARE @bcpCommand VARCHAR(2000)
  SET @bcpCommand = 'bcp "'+@qry2+'" queryout "'+@incomingfolder+'\SkippedProducts-'+CAST(@StoreMatchCode AS VARCHAR)+'-'+'.txt" -c -T'

  PRINT @bcpCommand
  EXEC MASTER..xp_cmdshell @bcpCommand, no_output

The created command looks like:

bcp "SELECT * FROM @SkippedProductsTable" queryout "c:\Logs\SkippedProducts-1330-.txt" -c -T

Can anyone suggest what could be going wrong? I've never used BCP before and not really sure where to start looking.

As a start I know that the folder deffinately exists at that location

Upvotes: 0

Views: 2033

Answers (1)

JNK
JNK

Reputation: 65147

I think the problem is the SELECT.

You are SELECTing from a table variable that is not declared in the query, so there's nothing for BCP to do.

Table variables only persist for the context they are called in, so even if you have one in a query, and you have dynamic sql or a subproc within that first query, they won't be able to see the table variable.

See this for more info.

Upvotes: 1

Related Questions