Dataset designer and complex Stored procedure

[Gerard]

I have many complex SQL Server 2000 stored procedures, using cursors, temp
tables... They all finally return a resultset that could be a join on
several user and temp tables.

Now, when I try to drag some of them on a dataset in VS 2005, the designer
will create a QueriesTableAdapter for a query that returns no data or a
scalar value, which is not what my procedure does.

The only way I found to create the working typed dataset (apart from
manually designing it) is to create a fake SP that returns the correct data
(creating the table in the SP) and then reconfigure the TableAdapter to
connect it to the correct SP. This is a lot of work if I have lots of
procedures, and it isn't easy to maintain if procedures change.

Is there a better way to do it? Where can I find a documentation that would
tell me what are the creterias for the drag and drop operation to work on a
Dataset?

Gerard.

 

[Guest]

Hi,
See "Hooking up the Other Stored Procedures" in below link:
http://www.theserverside.net/tt/articles/showarticle.tss?id=DataSetDesigner

 

[Gerard]

It's an interesting article, but it doesn't help me solve my problem.

I cannot have the Dataset designer create a DataTable from an existing
stored procedure:

- If I just drag-drop the SP onto the Dataset designer, it will create a
Query in the QueriesTableAdapter that returns no data.
- If I use the TableAdapter Wizard to create a TableAdapter, I can select
the SP, but the last screen shows an error:

Generated SELECT statement. Invalid object name '#TEMP'.

#TEMP is the name of one of the temp tables created by the SP.

 

[Gerard]

To be more precise, my main problem is to have the Dataset designer generate
the DataTable.

The only solutions I have found are:

1- Build it by hand in the designer.
2- Create a table that has the same columns as the result set returned by
the SP, drag that and the change the TableAdapter to use the SP.

Both are not very satisfactory and not easy to maintain.