I've been struggling for the past couple months to use the Visual Studio XSD tool to create typed datasets from my stored procedures.  But I always get an error telling me the temp table is an invalid object name.  I've just discovered that temp tables are actually not allowed in this tool, due to the way it writes the schema to XML (see this article and this article for details).  There are a couple things about typed datasets that would make my life MUCH easier, so I'm still hoping there's a workaround for this...  Does anyone know how to create a typed dataset using a stored procedure that creates a temp table?  One of the articles I linked to mentions using the SET FMTONLY ON within the stored procedure, but when we tried that we either got NO results or a SQL exception.  Anybody else have any experience with this?

Thanks!

eddie

Your link point to a local temp table # with very limited scope but there is another temp table called global temp table ## with longer scope, now I am not saying it will solve your problem but it is worth a try but you should know it is very expensive to use and must be dropped explicitly when you don't need it.  Another option is INTO as in SELECT INTO it will creates a new table on destination.  Run a search for all of the above in SQL Server BOL (books online).  Hope this helps.

I'm sorry, I didn't explain very well...  The links I included are not my posts, but they are the same problem I am having.  My stored procedures are completely different, except they use the "Create table '#..." command, which is what is screwing me up...  Unfortunately I have no control over the stored procedures, I have to use them as-is and cannot make changes to them.  So I was hoping there would be a workaround that I could do from the coding side, since I can't do anything from the sql side...

 Hope that explains it better.

Thanks!

eddie

I've found 2 possible solutions.

1.) temporarily change your stored procedure to select the columns you would like to see in the data table (what you see when you open the xsd file).  Example: SELECT 0 as [CustomerID], 'some text' as [CustomerName] FROM Customer

Comment out the SQL that created the temporary table.  Then you can configure the xsd file so that all the columns are being returned.  Next update your SQL by uncommenting the code you commented out.  You'll still get an error when trying to configure the xsd but you shouldn't need to reconfigure it.  If you need to add more columns then follow the steps above again. 

The typed dataset will work in your gridview.

2.) Use functions to create your tables and then join on them.  Here's a small example for creating a table in a function:

Create FUNCTION dbo.GetMyTable (@CustomerID int)
/*
    Return table
*/
RETURNS @MyTable table
([ID] [int],
[CustomerID [int],
[CustomerName] [int]
)
as
begin

insert into @MyTable
SELECT statement goes here
return
end

If you need more information email me @ jon@strollaway.com