none
Error: A rowset based on the SQL command was not returned by the OLE DB provider.

    Question

  • I have a dataflow task. On which I have OLEDB as my source. I connect to my database and execute a stored proc. the stored proc results in a result set with only one row and two columns. First Column is an integer and the second row is a varchar(max) with xml script in it. Not that it should matter because it is in varchar(max).

    Anyway, it give me an error

    [OLE DB Source [321]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.

     

    What am I doing wrong?

    Can I not have a stroed proc that returns a result set as my data source?

     

    Monday, May 14, 2007 7:39 PM

Answers

  • you must put set nocount on and set nocount off around your stored procedure body. SInce the rowcount returned by the stored procedure is returned before the actual resultset, SSIS attempts to read that in as the resultset, which is why it complains about an invalid resultset. set nocount on will supress the rowcount message and only return the resultset.
    Wednesday, June 06, 2007 10:42 PM

All replies

  • varchar(max) maps to DT_TEXT in SSIS, it should not be a problem for OLEDB source adapter to deal with columns type in that. Have you tried to use an external tool to see whether your stored proc returns expected results there from the provider?

     

    >Can I not have a stroed proc that returns a result set as my data source?

    Yes you can use a stored proc at SSIS OLEDBSource as your data source. It's not using the stored proc that had caused your problem I believe.

    Tuesday, May 15, 2007 5:52 PM
  • you must put set nocount on and set nocount off around your stored procedure body. SInce the rowcount returned by the stored procedure is returned before the actual resultset, SSIS attempts to read that in as the resultset, which is why it complains about an invalid resultset. set nocount on will supress the rowcount message and only return the resultset.
    Wednesday, June 06, 2007 10:42 PM
  • Almost correct. If you have an action that performs a select , insert, update or delete these by default generate a message with the number of rows affected. SSIS recieves this but with no associated rowset and thus the error. using SET NOCOUNT ON supresses these messages so SSIS will process the first rowset it recieves.

     

    i.e. if you have this in your procedure

    select top 1 @variable = column from table

     

    select column2 from another table where col3 = @variable

     

    The first select generates a 1 row affected thus confusing SSIS

    Wednesday, July 18, 2007 2:12 PM