Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL Server Native Client 10 (2008R2), OLEDB Command calling stored procedures. Failure: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid

Answered SQL Server Native Client 10 (2008R2), OLEDB Command calling stored procedures. Failure: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid

  • Friday, April 20, 2012 8:47 PM
     
     
    Sign In to Vote
    0

    Hello,

    I’m currently struggling with a problem calling a stored procedure over SQL Server Native Client 10 (SQL Server 2008 R2 SP1).

    The complete story is that I had a SSIS 2005 package which contained a dataflow task with several OLE DB commands (data flow transformations). These OLE DB commands called stored procedures on a SQL Server instance. I’ve upgraded the package to SSIS 2008 (without any errors/warnings in the process). The provider was correspondingly upgraded to SQLNCLI10.1 (from SQLNCLI.1). The package was working perfectly in the 2005 version, now it sporadically breaks in one of the OLE DB commands (non-deterministically, I’ve tried multiple times for the same database contents and same source input. The error happens in different OLE DB commands. It’s definitely not the input what causes the problem).

    The error message in the SSIS is

    Error: 0xC0202009 at DFT Portfolio_Dimension, DESTO Hist UpdateName [1792]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.

    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E21  Description: "Invalid input parameter values. Check the status values for detail.".

    Error: 0xC020901C at DFT Portfolio_Dimension, DESTO Hist UpdateName [1792]: There was an error with input column "ErrorNumber" (2118) on input "OLE DB Command Input" (1797). The column status returned was: "The status value given to the provider was not valid.".

    Input column may be different in subsequent runs.

    There is also an exception in the SQL Server Profiler log:

    Exception            Error: 8004, Severity: 16, State: 1

    User Error Message       The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.

    I’ve tried multiple things trying to narrow down the problem. The following changes seem to repair the problem (each one, independently):

    -Using SQLOLEDB.1 provider instead of SQLNCLI10.1

    -Setting EngineThreads property on the OLEDB commands to 2 (instead of 5 per default)

    -Calling stored procedures without binding the output parameters. All relevant stored procedures have several input parameter and 3 output parameters: errornumber, errormessage and errorprocedure. If I call the SPs passing null for these parameters, the problem seems to be solved

    Unfortunately neither of this is really an option and I would anyway like to know the reason for this behavior.

    Fortunately I can reproduce the problem on my development machine: SSIS/SQL Server installed locally, running the package from BI Studio, select @@version returns

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)   Jun 17 2011 00:57:23   Copyright (c) Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)  

    The problem is also reproducible on our test server (SQL Server Enterprise Edition with identical version, OS is Windows Server 2008) which executes the same package deployed to msdb by calling dtexec.

    My current hypothesis is some multithreading/race condition issues in the SQL Server Native Client. If there is some additional information to this problem (or even a bugfix?!), I would be happy to know it. Thanks a lot

     

All Replies

  • Friday, April 20, 2012 9:27 PM
     
     
    Sign In to Vote
    0

    Something to try that is mentioned here - that it may be a constraint violation

    http://sortitious.blogspot.com/2009/10/ssis-ole-db-command-error-with-not-null.html

    Add data viewers to the outputs of the offending OLE DB Command. You
    should see the following columns at debug time:

    • ErrorCode: -1071607702
    • ErrorColumn: 12345 (this is an example value)
    • ErrorDescription: The data value violated the schema constraint.

    Chuck

     
  • Friday, April 20, 2012 9:57 PM
     
     
    Sign In to Vote
    0

    Hello Chuck,

    I'll try it out, but I doubt this is the problem. The dataflows works (with exactly the same CSV input and data in the destination database) if I change one of the three points mentioned above. Also the dataflow breaks each time on a different OLE DB command (and the problematic one from the previous run is suddenly green)

     
  • Friday, April 20, 2012 10:22 PM
     
     
    Sign In to Vote
    0

    Error: 0xC020901C at DFT Portfolio_Dimension, DESTO Hist UpdateName [1792]: There was an error with input column "ErrorNumber" (2118) on input "OLE DB Command Input" (1797). The column status returned was: "The status value given to the provider was not valid.".

    Did you make sured the parameters that you are passing to the oledb command data types  exctly matches to the

    data types in the procedure , looks like the issue. yeah OLE DB command little bit guffy always try to avoid it

    or it can be that you are passing a null value to a parameter of the stored procedure parameter when it expects a value

    it always good to use the data viewers in the DFT to verify the output

     
  • Monday, April 23, 2012 3:43 PM
     
     
    Sign In to Vote
    0
    Still looking for help/tips. As I've already said, the problem is not the input or output values. The dataflow task breaks on different OLEDB-commands each time (and changing the provider "solves" the problem)
     
  • Monday, April 23, 2012 10:45 PM
     
     
    Sign In to Vote
    0

    Worst case, you'll need to delete all the SqlNative Source components (and their connection managers if you could), and set them up afresh once again. As hinted by others earlier, there might be datatype mismatches in what your stored proc returns and what SSIS is set up to expect. You colud also run your stored proc as a script simulating success/failure scenarios (so that you could see what values are getting passed back to SSIS) and see if SSIS is able to successfully store those values in the output resultset.

    I am going by the error message you have posted above.

    Hope this helps!

    Cheers!!

    Muqadder.

     
  • Tuesday, April 24, 2012 7:12 PM
     
     Answered
    Sign In to Vote
    0

    I've created a MS connect issue with a small example reproducing the problem

    https://connect.microsoft.com/SQLServer/feedback/details/738605/multithreading-issues-in-sql-server-native-client-when-calling-stored-procedures-with-ssis-oledb-commands

    Please vote if you can also reproduce the bug

     
  • Friday, April 27, 2012 1:08 AM
     
     
    Sign In to Vote
    0

    I am having the same issue.

    I upgraded my SSIS Package from 2005 to 2008.

    I have a stored procedure that runs in a OLEDB Command in a data flow task in 2005. It works perfectly.

    The package runs fine in 2005 when it is upgraded to 2008 the stored proc doesn't execute in oledb command in 2008.

     
  • Friday, April 27, 2012 10:05 AM
     
     
    Sign In to Vote
    0
    I would really appreciate if someone of you, guys, could confirm that the problem is not only on my side. The example is attached to the MS Connect issue. Just scroll to details, expand them and download a 21 Kb large .zip file. It's 2 SQL Scripts and 1 SSIS package, nothing extraordinary
     
  • Friday, April 27, 2012 12:23 PM
     
     
    Sign In to Vote
    0

    I've just tried the reproduction example with SQL Server 2012 + BIDS(SSDT) 2010 + SQLNCLI11.1 Provider with pretty much the same result. Same result means the dataflow task breaks indeterministically (same OLEDB command can run successfully or break). The error messages are a bit different though.

    BIDS/SSDT shows following:

    Error: 0xC0202009 at Data Flow Task, OLE DB Command 1 [57]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E21  Description: "Invalid input parameter values. Check the status values for detail.".
    Error: 0xC020901C at Data Flow Task, OLE DB Command 1 [57]: There was an error with OLE DB Command 1.Inputs[OLE DB Command Input].Columns[ErrorNumber] on OLE DB Command 1.Inputs[OLE DB Command Input]. The column status returned was: "The status value given to the provider was not valid.".
    Error: 0xC0209029 at Data Flow Task, OLE DB Command 1 [57]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "OLE DB Command 1.Inputs[OLE DB Command Input]" failed because error code 0xC020906E occurred, and the error row disposition on "OLE DB Command 1.Inputs[OLE DB Command Input]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
    Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Command 1" (57) failed with error code 0xC0209029 while processing input "OLE DB Command Input" (62). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
    Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.

    The SQL Server profiler shows a bunch of "Exception    String or binary data would be truncated." errors.

    I've checked the types of input/output parameters in SSIS, stored procedures and database columns multiple times, they are fine. I also checked with dataviewers that the data is ok. The dataflow breaks each time on a different OLE DB command, so I feel there is indeed a bug in SNAC 10 AND SNAC 11. I'd like to stand corrected

     
  • Friday, April 27, 2012 3:05 PM
     
     
    Sign In to Vote
    0
    I was able to reproduce your issue and added a vote to the connect item.  I generally steer clear of the native client due to a number of goofy problems that have always seemed to occur when using it for anything but the simplest packages.

    Chuck