Typed DataSet fails with sproc containing #tables

Question

Hello,

I'm having some problems using the DataSet wizard ("TableAdapter Configuration Wizard")  in VS2005. I'm telling it to base the select on a simple sproc (that takes two params, both int), however, once I get to the final screen ("Wizard Results"), it has the following message for me.

---------
The Wizard has detected the following problems when configuring the TableAdapter: "SomeProc":

Details:

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

To add these components to your dataset, click Finish.
---------

The upset of all this seems to be that the DataSet has no columns listed. If I manually attempt to "Add->Column", I get the same "Invalid object name '#temptable'".

The server is SQL2000, and I'm wondering if that may be why it fails?

The sproc is fairly simple, just create a new temp table, insert into it, and then return on the subsequent select.

Regards,
Svend

Answer 1

Obviously the wizard is having problems with the column name "#temptable"...a couple of ideas:

1. always use fully qualified names:  TableName.ColumnName

2. If in doubt about conflicting names always use brackets:

[tablename].[columnname]

Answer 2

DMan1 (or anyone who can help!)

I'm having the same problem and bracketed my names but it hasn't resolved the problem. If I use a non temporary table it works fine, here's the final query in the proc that generates the results set (column names mangled )

SELECT 
    ROW_NUMBER() OVER(ORDER BY (([foo].[col1] - [#bar].[col1]) / [foo].[col1])) AS Rank,
    [foo].[col2] AS name2, [foo].[col3] AS name3, 
    [foo].[col4], 
    [foo].[col5] As name5,
    ([foo].[col1] - [#bar].[col1]) / [foo].[col1] AS name6,
    [#bar].[col1] AS name7,
    [foo].[col1] AS name8
FROM [foo]
INNER JOIN [#bar] ON [foo].[col3] = [#bar].[col3]

If I use [#bar] throuought the proc then it fails, but [bar] is OK. (It also works if I comment out this query).

Is this just a bug with the wizard?

Edit: I found a workaround. Create the table adapter with the stored proc codes as [bar] then go back & alter the proc to use [#bar]. The preview in the table adapter still works a treat.

Thanks

Brian

Answer 3

Has anyone found a better solution to this yet?

 

I have a sql 2000 box with hundreds of SP's of which many have #tables and I don't want to have to change each one twice just to overcome this little bug.

 

I tried using aliases when calling the tables but it seems its the SELECT INTO #table that causes the issue.

 

Any suggestions?

 

Is MS going to patch this?

 

Thanks

 

Dave

Answer 4

 

Hi,

 

I have the same problem here.

Using VS2005 and SQL 2000.

 

Any suggestions? My stored procedure works well, and I can´t use an existent table.

 

Regards,

Martin

 

Answer 5

I have the same problem.  I tried changing from #TableName to TableName but when I reconfigure my table adaptor I get the same error:

Generated Select Statement
Invalid Object Name #Tempsubordinate

and now (after removing the #) I get this...

Generated Select Statement
Invalid Object Name Tempsubordinate

I can run the stored proc and it returns the records I would expect I just can't get the table adapter to use the stored proc.

Answer 6

I have a way around this problem.  It's a pain but it worked for me but maybe not for everyone.  It seems that a table that is created and dropped during the stored procedure (sp) is the problem.  I commented every reference of the temporary table from my sp and reconfigured my table adaptor.  Try changing your sp so it doesn't create or drop the table, reconfigure the table adaptor, and set your sp back.

Here's what they say to do at ExpertS-exChange.com (I think their url name is funny):
http://www.experts-exchange.com/Programming/Languages/.NET/Q_20726709.html

Basically, go away from using the typed dataset xsd in your project and bind your control to a returned dataset in code instead of at design time.

This most likely also means you would have to perform any design-time control property modification in code as well.  This may be a better coding practice (if I had the time to write all that code).


To repeat the problem create this sp in SQL Server 2000 and use the table adaptor creation wizard screens.

Code Block

CREATE PROCEDURE [dbo].[TEMP_TABLE_ISSUE] AS
BEGIN
   CREATE TABLE #TempTableIssue (Id INT NOT NULL)

   INSERT INTO  #TempTableIssue VALUES (1)

   SELECT * FROM #TempTableIssue

   DROP TABLE #TempTableIssue
END

Answer 7

I know this post is really old, but I had the same problem and was able to resolve it by changing my stored proc from using temp tables to using table variables.  Hopefully it helps someone.

Answer 8

I am guessing that you are using SELECT ... INTO #TempTable FROM someTable to create your stored procedure.

 

Probably your tool for creating your typed dataset is calling the SP without providing input parameters (essentially setting them all to null). This results in the query not running, or returning zero rows. Hence, no temp table exists in this case, and .Net receives an error.

 

If this is the case you have a few options:

 

1) Manually create your typed dataset. Persist it and make it a part of your project. Sahil Malik has a good book on it from APress called Ori ADI,Net 2,0.

2) Create your temp table first. Then populate it with your query. Since the temp table already exists, then the select statement from the temp table will work. You can do this temporarily to make your data object.

3) Don't use a temp table. Use inline view or query.

4) Instead of using a temp table, create a table function. Call the table function from your stored procedure.

 

In short, be sure that whatever object is selected from in the final query will exist if the stored procedure is called without parameters.

 

Cheers,

Ben

Answer 9

This really funny.
I just want to share as I don't want someone to suffer from what I suffered.

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.

Answer 10

Hi.

Thanks a lot - been trying to get this to work now for days - and those few lines fixed everything.

Great

Espen

Answer 11

That solved my similar problem too! God Bless You Man!

Answer 12

Wo those lines worked for me too!  SQL2008.

Answer 13

Even today this is a very helpfull post!!! Thanks a lot

Answer 14

Works for me too. So glad to find this post.

Answer 15

Works for me too. So glad to find this post.

um yeah -- x2!

---

James Crandall ~ http://javitechnologies.com Spatial Database Solutions

Answer 16

Works for me too.glad to find this post.

Thanks 

Answer 17

This is great!  I played around with it a bit more and all you really need is SET FMTONLY OFF without the IF statement.  My only concern is that with SQL 2012, the SET FMTONLY is already advised against, and may not be valid in future versions.

Answer 18

Thanksss a ton .... for such a simple yet perfect solution !!! N yes i was wondering too... what makes this work ?? as u said this line will never execute. Strange!!! :P

Answer 19

This is suuuuch a messed up solution. As far as "Why" this fixes the issue; don't know, don't care. But thanks for sharing!

Answer 20

This really funny.
I just want to share as I don't want someone to suffer from what I suffered.

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.

ALL HAIL Google!!!! If this was not for Google indexing this solution that makes no sense and is not on any books would have never been found. Go figure

Answer 21

Hi,

Can you send me sample procedure using MySql. 

Answer 22

Just so you know, 8 years later, your answer helped big time. Thank you!

Answer 23

Really it worked. Thank!!!

Answer 24

It works !!! :-)

Answer 25

Thanks . This is also working for me.