SQL Server – Bcp Error – Protocol Error in TDS Stream

Using Microsoft – SQL Server – Bulkcopy (bcp) to extract some data.

The request was simple enough; based on user’s criteria get data out of the DB Engine.  As the user’s criteria changes a bit, decided to write a Stored Procedure.

Shall we say dbo.usp_getData:


create proc dbo.usp_getData
(
      @CriteriaID int
    , @criteriaParam1 bigint
    , @criteriaParam2 bigint
)
as
     select 
               tblSales.invoiceID
             , tblSales.storeID
     from   dbo.sales tblSales
     where  tblSales.invoiceID between @criteriaParam1 and @criteriaParam2

Everything worked well.

Later we needed to support Other Use Cases:


create proc dbo.usp_getData
(
      @CriteriaID int
    , @criteriaParam1 bigint
    , @criteriaParam2 bigint
)
as

    if (@criteriaID = 1)
    begin
       select 
               tblInvoice.invoiceID
             , tblInvoice.storeID
             , tblInvoice.purchaseDate
             , tblInvoice.statusID
        from   dbo.invoice tblInvoice
        where  tblInvoice.invoiceID 
                 between @criteriaParam1 and @criteriaParam2

    end

    else if (@criteriaID = 2)
    begin

        select 
                   tblInvoice.invoiceID
                 , tblInvoice.storeID

        from   dbo.invoice tblInoice
        where  tblInvoice.invoiceID 
                 between @criteriaParam1 and @criteriaParam2

   end

Command File that gets data from database


echo "TIME START " %TIME%
bcp "EXECUTE [tempdb].[dbo].[usp_GetData] 21, 1000, 4000 " queryout "e:\temp\GetData__1000__4000.csv" -S DBSALES -T -w
echo "TIME END " %TIME%

Soon after we started getting errors.  The errors resembles the one pasted below:


ProtocolErrorInTDSStream

The error basically states “Protocol error in TDS stream”.

Spent the whole day Googling for an inkling as to what I should possible try.

But, nothing worthwhile came up.

Just before it was time to go home, launched SQL Server Profiler and traced the queries for my account.

SQL Profiler – Graphical Output

SQLProfilerTrace - BCP Trace

SQL Profiler – Textual output


declare @p1 int
set @p1=1
exec sp_prepare @p1 output,NULL,N'EXECUTE [tempdb].[dbo].[usp_GetData] 2964037, 2969137, 21 ',1
select @p1

set fmtonly on EXECUTE [tempdb].[dbo].[usp_GetData] 2964037, 2969137, 21  set fmtonly off

set fmtonly off

set fmtonly on EXECUTE [tempdb].[dbo].[usp_GetData] 2964037, 2969137, 21  set fmtonly off

select BCPCollationName(0x0904D00034, 167), BCPCollationName(0x0904D00034, 167), BCPCollationName(0x0904D00034, 167), BCPCollationName(0x0904D00034, 167), BCPCollationName(0x0904D00034, 167), BCPCollationName(0x0904D00034, 167), BCPCollationName(0x0904D00034, 167), BCPCollationName(0x0904D00034, 167), BCPCollationName(0x0904D00034, 167), BCPCollationName(0x0904D00034, 167), BCPCollationName(0x0904D00034, 167), BCPCollationName(0x0904D00034, 231)

EXECUTE [tempdb].[dbo].[usp_GetData] 2964037, 2969137, 21 

And, there was the problem:

  1. set fmtonly on < sql statement>; set fmtonly off
  2. Select BCPCollationName(<hexnumber>,<column-type-id>)

Knowing a bit about SQL Server, the problem was now a bit more obvious.  And, what is the problem you ask?

  1. SQL Server invokes the Query and Stored procedure with “set fmtonly” —  This does not actually do anything.  It simply queries the meta data and says what columns and column type will you be returning
  2. SQL Server prepares itself to accept those columns and column type
  3. Unfortunately, this is done based on the first condition in this case
  4. As the second query returns different columns and return types, things break done

To fix, align the column types.

References:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s