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.
How do I configure a SQLDataAdapter to use the results of a temporary table from a stored procedure?
I also want to produce a strongly typed dataset from the SQLDataAdapter. The VS wizard states "Invalid object name '#MyTempTable'" The table mappings is empty.
Solution:
1）VS has only a limited ability to retrieve tabular metadata from stored procedures, and as you have discovered, temp tables are beyond those limits. You should have a dummy table or view in your database which has the same structure as your temp table. In essence this "publishes" the temp table metadata to the world and lets you easily build strongly-typed datasets from it.
2)you can create the schema directly with the schema tool.