BCP calling CLR Stored Procedures

Question

Hi,

I am using SQL Server 2008 R2 and have encountered a scenario where I need to export large volumes of data (hundreds of millions of rows) from a table to multiple csv files. One of the columns in the table is encrypted using custom .NET code.

To extract the data and decrypt the column, I have written a CLR Stored Procedure to do it. The CLR stored procedure also contains logic to slice the data into chunks depending on the value of the parameters being passed in.

To define the output of my data I used the following:

        // Define exported column column type and length.
        SqlMetaData[] metadata = new SqlMetaData[] { 
                new SqlMetaData("ID", SqlDbType.Int),
                new SqlMetaData("Name", SqlDbType.VarChar, 50),
                new SqlMetaData("ProtectedData", SqlDbType.VarChar, 50),
                new SqlMetaData("Age", SqlDbType.Int)};

        // Create column record.
        SqlDataRecord record = new SqlDataRecord(metadata);

I then connect to the table and load the results of my query in a DataReader and loops through it. For every loop, I read the values of the row, decrypt the column and issue the following statement:

 SqlContext.Pipe.SendResultsRow(record);

When I execute the CLR Stored Procedure in SSMS, I get the tabular result that I want with the column decrypted. However, when I execute it with BCP i.e.

C:\>bcp "EXEC dbo.ExtractDataProc 0, 1000000" queryout "C:\Temp\DemoData.txt"  -T -S . -d DemoDB -c

I get the following error:

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]BCP host-files must contain at least one column

I was wondering if BCP is compatible with SqlMetaData and SqlDataRecord. If so, how should I code it?

Thank You. :)

Hugs,
Serena

Answer 1

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 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).

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/29/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.aspx