Mar
25
Written by:
PaulEdwards
Thursday, March 25, 2010 4:00 PM
Today I was generating a typed DataSet, using the Data Source Configuration Wizard in Visual Studio (“Add New Data Source…” from the Data Sources window or Data menu). I had two stored procedures, accessing a linked server, in my database that I wanted added to my DataSet.
I arrived at the “Choose Your Database Objects” stage, but the wizard was not correctly displaying one of my stored procedures:
After completing the wizard the stored procedure that was not displaying correctly, (usp_sel_quotes in the above picture) was not listed in the Data Sources window, and no DataTable or TableAdapter objects were created, so not terribly useful.
I eventually found a solution to this issue and it was related to the fact that my stored procedure accepts some parameters that are used when querying the linked server, so the SQL query statement is constructed and then executed against the linked server with a final statement of:
EXEC (@SQLstmt)
The solution I found was on this page, http://social.msdn.microsoft.com/forums/en-US/winformsdatacontrols/thread/c492d11b-21f2-4a50-97b0-d1e464b8694e, and is in the 6th response to a question that is unrelated to this issue. This was the only reference I could find for this gotcha, hence why I am posting it here.
The solution is to alter the stored procedure so that the final EXEC statement is commented out and then in it’s place is a dummy SELECT statement that returns the columns that would be returned from the EXEC statement, looking something like this:
SELECT Column1Value AS Column1, Column2Value AS Column2…
Re-run the Data Source Configuration Wizard with the modified procedure and the stored procedure is now listed in the Data Sources window and the DataTable and TableAdapter objects also created.
Before executing the stored procedure, using the generated typed DataSet and objects, it will have to be reverted back to the original with the final EXEC statement, and then you're good to go.