tableadapter configuration fails with stored procedures and temp t

7 Nov 2007 3:25 PM

MarcG

This issue was raised in posts from 2005 to 2007. The Feedback article dated
2005 at ...
https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=106244&wa=wsignin1.0

provides a workaround that leaves a maintenance headache. I have experienced
the problem with a stored procedure that creates temporary tables, getting
the message "Invalid object name '#temp1'"

Is this bug/feature still active, or is there something that I can do to get
the adapter and table set up properly by the wizard.

Thx
Marc

8 Nov 2007 8:39 AM

WenYuan Wang [MSFT]

Hello Marc,

Thanks for your posting, but I'm afraid to say this is a Product
Limitation.

Karol has explained it in the feedback. Visual Studio TableAdapter wizard
cannot get information about the schema of temp table in stored procedure.
This is caused by known SQL Server limitation. As far as I know, defining
the schema in typed DataSet Designer seems the only workaround.

I aslo tried it on VS 2008 beta2. I'm sorry to say this limitation is still
active.

We are indeed sorry for any inconvenience this may have caused.
Please feel free to update here again, if there is anything we can help
with.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

9 Nov 2007 8:49 AM

WenYuan Wang [MSFT]

Hello Marc,

This is Wen Yuan again. I found a solution is to use Table Valued
Functions. VS IDE cannot get information of Temp Table, but it works fine
with Table Valued Function. I would like to highly recommend taking a look.

Hope this helps. Please feel free to update here again, if there is
anything unclear or you have more concern.
We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

15 Nov 2007 6:31 PM

damonl73

This works for me. Add these lines to the beginning of your sp:

IF 1=0 BEGIN
SET FMTONLY OFF
END

Obviously that statement is never executed, but for whatever reason it
gets the job done.

On Nov 9, 3:49 am, v-wyw***@online.microsoft.com (WenYuan Wang [MSFT])
wrote:
Show quote

> Hello Marc,
>
> This is Wen Yuan again. I found a solution is to use Table Valued
> Functions. VS IDE cannot get information of Temp Table, but it works fine
> with Table Valued Function. I would like to highly recommend taking a look.
>
> Hope this helps. Please feel free to update here again, if there is
> anything unclear or you have more concern.
> We are glad to assist you.
>
> Have a great day,
> Best regards,
>
> Wen Yuan
> Microsoft Online Community Support
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.