DataBase Refresh

[stix]

I have a stored procedure in which I basically copy the schema from one
table into a temporary table.
Select * into dbo.myNewTable from dbo.myoldtable where 1 = 0

The problem is that when I goto alter that table to add the Primary key to
it (as that part of the schema did not copy)
I get an Invalid Object Error dbo.myNewTable does not exist...
BUT IT DOES EXIST!!!

Any one have any ideas as to what I am doing wrong

I open the conection
execute the stored procedure to create the table
I close the connection

I open the connection again
Alter the Table to set a primary key and it errors
Does any one know why ? - I put wait in the procedure
and that does not work.

 

[Mary Chipman [MSFT]]

The problem you are having is related to the scope of temp tables in
SQL Server. A temp table exists for the duration of a connection
UNLESS it is created inside of a stored procedure, in which case it
lives only for the duration of the stored procedure. It does not
persist between calls or between connections. You need to perform all
operations on the temp table inside of the stored procedure, not in
separate calls.

--Mary