Data Adapter Wizard and Temporary Tables

[Matt Petteys]

I am using the Data adapter wizard to build data adapters and build the
mappings to datasets. I am trying to build a data adapter which is
using a stored procedure for the select command. The stored procedure
uses a temporary table internally. When the wizard gets to the last
step where it regenerates the datadapter codebehind logic, it generates
an error like the following:

Invalid object name '#temptable'.

Obviously the wizard is seeing the temporary table being populated in
the stored procedure and trying to provide mappings for the temporary
table. Anyone know how I can get the wizard to work and still use a
temporary table within the stored procedure?

 

[Bernie Yaeger]

Hi Matt,

I know this is not the direct answer to your question, but don't create a
temp table. Instead, create a real table and always drop and readd (or
truncate) it before you build the next one - this is essentially temp, but
it persists; I've always found this a much better approach.

HTH,

Bernie Yaeger

 

[JimM]

Problem here is collisions in a multi-user situation.

I've run into this problem and backed away toward another solution. However
it later occurred to me that I could wrap the procedure in another procedure
and perhaps the double-layer might not be parsed by the wizard.

Only other suggestion I have is to lay it out by hand in code instead of
using the design surface.

--- Jim ---