This is just a post in my continued efforts to publicize all errors that I find in hopes that it will save somebody else a few moments.
I was using the SQL 2005 Management Studio to export the results of a Stored Procedure to a Flat File. This stored procedures made changes to other tables (Deletes, Inserts and Updates) and returned one result set.
I got the followin errors when trying to export this.
- Pre-execute (Error) Messages
Error 0xc02092b4: Data Flow Task: A rowset based on the SQL command was not returned by the OLE DB provider.(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component “Source – Query” (1) failed the pre-execute phase and returned error code 0xC02092B4.(SQL Server Import and Export Wizard)
It seems that the error may have been caused by column headers not getting returned properly or something related to that. I am unsure exactly what the real error was.
The problem seems to have to do with what you use to export the data. The “SQL Native Client” as well as “Microsoft OLE DB Provider for SQL Server” produced the same error above.
I had to use the “.Net Framework Data Provider for SqlServer” in order to get the data exported. This seems like a major problem of SSIS. Although there are workarounds, as it stands right now I was happier with DTS.
try putting
set nocount on
begining of the stored proc
Look for any extra output statements like prints or selects and set the nocount stuff…