I am connecting to a PDW (Parallel Data Warehouse) 2012 database using a Visual Studio 2008 Server Explorer Data Connection and related Query Designer (not an ideal combo I know).
If I run all three statements below at once, I get results:
create table #diagnosis_condition (dx_code varchar(4), condition_code char(3)) WITH (LOCATION = USER_DB)
insert into #diagnosis_condition (dx_code,condition_code) values ('J45','AST')
SELECT * FROM #diagnosis_condition
Results: J45 AST
If after this I delete the "create" and "insert" lines from my query window and re-execute the "select", I get an error:
SQL Execution Error.
Executed SQL statement: SELECT * FROM [#diagnosis_condition]
Error Source: .Net SqlClient Data Provider
Error Message: Invalid object name '#diagnosis_condition'.
If I do the same thing in SQL Server Management Studio with a non-PDW database connection, the temp table persists and I get the same results again.
Why does my Visual Studio PDW connection behave differently? Is this a PDW issue or a Visual Studio Query Designer issue or a user error?? I want to persist the temp table (with more rows of course) so I can run my subsequent code in chunks without recreating the table every time. I’m not new to SSMS but am new to PDW and VS so I may be missing the obvious. Thanks for any help!
I don't have a copy of VS2008 handy, but in VS2013 there is a setting under Options-> Sql Server Tools -> Transact-SQL Editor -> Query Execution -> Advanced that says Disconnect after the query executes.
I'll bet your VS installation has its equivalent option set.