CODEWITHSUNDEEP
sqlvisual-studio-2010sql-server-2008-r2database-project
Justin Rassier
Justin Rassier

Reputation: 908

SQL Database Project References to on-the-fly Temp Tables

I am trying to take a legacy database that is now on SQL Server 2008 R2 and create a database project within Visual Studio 2010 for it. There are several stored procedures that used some "on-the-fly" temp tables (the kind where you SELECT * INTO #NewTempTable) Obviously this causes some SQL04151 reference warnings in the database project.

Is there any way to fix or just maybe ignore these specific warnings? I know the immediate answer might be to actually declare the temp table within the procedure and define the columns, but at this point, that isn't an option.

Upvotes: 4

Views: 1524

Answers (2)

Keith
Keith

Reputation: 21224

This does not appear to be an issue with the latest database project type for VS 2010, installed as part of SQL Server Data Tools.

Try to install SQL Server Data Tools. After it's installed you'll be able to upgrade your database project to the new type by right-clicking it in Solution Explorer. (The new project type is backwards compatible with SQL 2008 and SQL 2005 and also comes with some significant improvements.)

FYI the new project type has a very generic name of "SQL Server Database Project" and you'll find it in the "SQL Server" template group as opposed to the "Database > SQL Server" group.

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37378

Turn SET FMTONLY OFF in your stored procedures.

From MSDN:

Returns only metadata to the client. Can be used to test the format of the response without actually running the query.

Your issue is that by default Visual Studio has this setting turned on... which allows it to determine the format of the response without running the query, but this doesn't work if temp tables are used.

Here's a discussion of this issue on MS Connect: Improve FMTONLY Intelligence

Per Microsoft's response, this is fixed in 2012, but won't be addressed in earlier versions.

Lastly, here's a related discussion on the FMTONLY issue.

Upvotes: 1

Related Questions