none 2010 problem creating dataset from SQL RRS feed

  • Question

  • I'm taking my first dive into to Report writer in .NET....

    I am tring to create a dataset from a sql stored proc.   It worked great when the stored proc was a simple "select * from....".    But now I've changed the proc to be more complex, it uses several temp tables and at the end returns values from one of the temp tables with a select statement.   The sql statement works perfectly when called in SMS.  There are no parameters for this Stored Proc.

    In the Dataset designeer in my VS2010 vb Winforms project, I go into the table adapter that was working previously...   I remove one column that is no longer in the output from the proc... no problem.   When I try to insert a new column I get a dialog box that says "Failed to inswert Column.  Invalid object name '#Temp1".    #Temp1 is a temp table I create and use in the proc - there is nothing wrong with it; it isn't even the first temp table idefined, nor is it the table I am quering from in th last step of the SP.   I get the same error if I try to create a new TableAdapter.

    To make things even more odd,  if I right click on the dataset and click "Preview Data" and preview in that screen the correct data is returned but the dataset's Fill().   So the data is coming back properly from SQL.

    Any clues?


    Monday, March 7, 2011 5:49 PM

All replies

  • After further review...

    I have found other posts suggesting that when your Stored Proc returns values from a temp table in SQL that this is what happens.   

    How riduculous!!!   If I have a complex Stored Proc that generates exactly the data that is needed, but I have to store the results in a table in order to create a data adapter in .NET?    What if two users run the same report at the same time???

    Guess I'll need to look for a REAL report writer for my .NET apps....


    Monday, March 7, 2011 10:29 PM
  • Ah, I'm confused. Are you talking about how to execute multiple resultset stored procedures or how to call them from reports? Are you using the ReportViewer control or something else? Note that the #temp tables created in stored procedures are never returned to the caller--their results can be used internally in the SP but their ownership (and existence) ends when the SP ends.

    I'm giving a Reporting Services webinar for the next three mornings if you want more inside information on SQL Server and Reorting Services.

    William Vaughn
    Mentor, Consultant, Trainer, MVP

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Monday, March 7, 2011 10:46 PM