Menu
I am learning Visual Studio 2012's "database project" system, using Visual Studio 2012 with Update 1, plus SSDT.
I am finding it very good at finding real problems in my database, especially programming errors in stored procedures where someone has removed a field from a database table but didn't go through and verify that all the stored procedures execute without errors. So validation of your .sql scripts via the "build" command in Visual Studio 2012 is very handy. I would hate to abandon it.
But I also note that whenever a #TEMPTABLE is used in a stored procedure, even when "Enable Extended Transact-SQL verification for common objects" is turned off, I still get "build errors" involving #temptable.field references in stored procedures.
What steps does the Database Project take to determine the schema of a temporary table? Since my temporary tables by definition don't exist in the main schema, they didn't get into my Database Project when I imported a real production SQL database into Visual Studio via the Import Database option, right after creating the database.
Should I be creating "#TEMPTABLE.SQL" files and adding them to my project?
Sample error:
c:\dev\...\dbo\Stored Procedures\xyz.sql(95,96): Warning: SQL71502: Procedure: [dbo].[proc123] has an unresolved reference to object [#temptable1].[somefield1].
If there was a way to include a script that defined the temptables in use once, and include it into the various places where it's necessary to know about these if the T-SQL is to be thoroughly validated, that would be fine, and if Turning of Extended Verification did what I think it's supposed to do, then perhaps nothing would be necessary.
Forum post suggests this isn't possible to fix and that all I can do is effectively turn off this warning at a file level, which is kind of horrible.
A question on this same subject but for Visual Studio 2010 suggests that this is an area where this technology has been just flat out broken and Microsoft has known for years about it and done nothing about it. Is anything better now in VS2012.U1+SSDT_Dec2012?
493
Now, to see where this table exists; go to “Object Explorer -> Databases -> System Databases-> tempdb -> Temporary Tables”. You will see your temporary table name along with the identifier.
To create a Global Temporary Table, add the “##” symbol before the table name. Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed. Global Table Name must have an Unique Table Name.
The syntax for creating a temp table with the select statement is as shown: SELECT column_list INTO #temporary_table_name FROM TABLE_NAME WHERE conditional_expression; We use the select statement followed by the name of the temporary table. The name of a temp table in SQL Server starts with a # sign.
Thats a problem with temp table, you should use table valued functions.
151
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
