BCP command calling Stored procedure with dynamic SQL and not having the first statement as a Select fails with the following Error "Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column"

  • Article

Environment
SQL Server 2005 AND SQL Server 2008. The same command works fine in SQL SERVER 2000.
Steps to Reprocreate proc p1
as
declare @typ sysname
select @typ = name from (select 'ntext' as name) as t <== FMTONLY mode does not execute this statement (used to work in SQL2000)
declare @sql varchar(1000)
set @sql = 'select system_type_id, user_type_id from sys.types where name = ''' + @typ + ''''
exec (@sql)
go

-- This fails because result-set metadata cannot be discovered
bcp.exe "exec testdb.dbo.p1" queryout out.txt -c -T -S.

Explanation of the issue

BCP client uses the SET FMTONLY mechanism to analyze the column metadata of the first result-set returned from the stored proc. This FMTONLY mechanism works by making a best attempt to pseudo-execute the statements inside the proc to determine metadata of the result-set. Some non-side-effecting statements actually do get fully executed under FMTONLY mode. This particular case is broken because in SQL2000 we used to execute select-stmts-with-assignments whereas in SQL2005 we do not execute these. This was a side-effect of the parser generating a CStmtSelect instead of CStmtAssignWithQuery, and select statements do not get executed under FMTONLY. Due to this change in behavior, the select statement that constructs the dynamic sql statement within the proc is not getting executed. The FMTONLY mechanism is brittle and is not guaranteed to work for dynamic-sql statements. There are many other situations where the FMTONLY mechanism will fail to discover the result-set metadata. In SQL 2000 this particular case just happened to work.

Possible fixes

A. Engine: Execute select-with-assignment statements just like SQL2000 (possibly under traceflag to minimize impact). However, this is a somewhat risky fix and not a lasting/full fix as FMTONLY is generally a brittle mechanism. This fix is not recommended.

B. BCP: Do not issue the separate FMTONLY call in the queryout case since the column metadata will be sent to client with the actual result-set anyways. This is one of the preferred long-term fix. BCP client should use FMTONLY (or the new metadata APIs) only where strictly necessary.  

C.

Possible workarounds for the user

1. Do not construct dynamic-SQL that depends on the result of a select statement. In general, any use of dynamic-SQL in procedures that will need to be BCP-ed should be reconsidered.
2. Put a dummy select in the procedure that returns the same (shape) result-set as the actual result-set.
    a. e.g. to fix this repro case, one would add the following statement to the proc 'if (1=2) select system_type_id, user_type_id from sys.types'
3. Use SQLCMD instead of BCP.
   a. sqlcmd -S. -h-1 -s, -W -w 65000 -Q "set nocount on; exec p1;" -r1 -m-1 2>err.txt 1>data.txt&(echo ==DATA==&type data.txt)&(echo ==MSGS==&type err.txt)
   b. A known caveat with this approach is that BCP writes a blank space for NULL values whereas SQLCMD writes a "NULL". This can be worked around by doing something like a post-pass perl script.
4. Use the openrowset trick.
   a. bcp.exe "SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;', 'set fmtonly off; exec testdb.dbo.p1')" queryout out.txt -c -T -S.
   b. There are some caveats with this workaround
      i. The proc/query being executed should not have any side-effects as the query can get executed twice (once during compilation and again during actual execution).
ii. Because of multiple executions and going through the loopback DQ path, it will run slower (as compared to before, i.e. not using OPENROWSET).

Rajendra Jain,
Support Engineer, Microsoft SQL Server

Reviewed By
Sourabh Agarwal
Technical Lead, Microsoft SQL Server