Connect to SQL Server to update temporary dataset

[Ja5ya]

I am trying to update a variable in my temporary dataset named 'tt' by joining it to a database in sql server. But I am getting the following error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name '##tt'.

Below is the code I used:

proc sql;

connect to sqlsvr as oledb (datasrc="&server_name" user="&sasusr" password="&saspwd");

execute (

 Update a

Set Network = PNSN.network_id

From ##tt as a

  JOIN &database..dbo.Network_Names as PNSN

  ON PNSN.network_gid = PS.network_gid

  AND PNSN.status = 'w'

  WHERE a.Network IS NULL

) by oledb;

disconnect from oledb;

quit;

Please help.

[SASJedi]

In testing, I have found that once you disconnect from MSSQL Server, temp tables are deleted by MS SQL Server.  For example:

proc sql;
connect to odbc as oledb (datasrc="mssql-orion" user=orion password=Metadata0 ) ;
execute(
IF object_id('tempdb..##test') is not null
BEGIN
DROP TABLE ##test
END
create table orion.##test 
(id int, name varchar(12)))by oledb;
execute(insert into orion.##test(ID,Name) values(1,'Mark')) by oledb ;
execute(insert into orion.##test(ID,Name) values(2,'Joe')) by oledb ;
select * from connection to oledb (select * from orion.##test);
disconnect from oledb ;
quit;

This produces the results:

id	name
1	Mark
2	Joe

However, if you check in Microsoft SQL Server Management Studio (MSSMS) the table ##test no longer exists.

I tried this submitting just this in interactive mode:

proc sql;
connect to odbc as oledb (datasrc="mssql-orion" user=orion password=Metadata0 ) ;
execute(
IF object_id('tempdb..##test') is not null
BEGIN
   DROP TABLE ##test
END
create table orion.##test 
(id int, name varchar(12)))by oledb;
execute(insert into orion.##test(ID,Name) values(1,'Mark')) by oledb ;
execute(insert into orion.##test(ID,Name) values(2,'Joe'))  by oledb ;

And as long as PROC SQL remained running, I could see the temp table ##test in MSSMS. As soon as I executed the DISCONNECT statement in SAS, table ##test disappeared from MSSMS, too. 

I tried this directly in MSSMS (without using SAS). The table remained available in MSSMS until I shut down the program. Upon logging back in, table ##test was no longer available.

It then appears to me that this is normal behavior for MS SQL Server temp tables. Consider completing all the required work with the temp table before disconnecting from MS SQL Server, or using a normal table to do your work and explicitly then deleting it when finished. 

[Ja5ya]

This doesn't work out for me as my dataset 'tt' is a temporary dataset in SAS. I'm trying to update it using joins with tables from SQL server.

[SASKiwi]

The code you posted updates a temporary table in SQL Server but you are now saying it is a SAS WORK table? Where do you want the end result? A table in SAS or in SQL Server?

[Kurt_Bremser]

---

@Ja5ya wrote:
This doesn't work out for me as my dataset 'tt' is a temporary dataset in SAS. I'm trying to update it using joins with tables from SQL server.

---

The code you posted is a pure pass-through to SQL Server. It does NOTHING with SAS datasets.

As the term implies, temporary tables exist only for the duration of a connection.

[SASJedi · Accepted solution]

The code you supplied doesn't make any SAS tables at all - the ##test table is an SQL Server temp table. You could add CREATE TABLE WORK.TEST AS just before the final SELECT statement to create a SAS work library table before disconnecting from SQL server to copy the data from SQL Server into the SAS work library. Something like this should do it: 

proc sql;
connect to odbc as oledb (datasrc="mssql-orion" user=orion password=Metadata0 ) ;
execute(
IF object_id('tempdb..##test') is not null
BEGIN
DROP TABLE ##test
END
create table orion.##test 
(id int, name varchar(12)))by oledb;
execute(insert into orion.##test(ID,Name) values(1,'Mark')) by oledb ;
execute(insert into orion.##test(ID,Name) values(2,'Joe')) by oledb ;

create table WORK.TEST as 
select * from connection to oledb (select * from orion.##test);
disconnect from oledb ;
quit;

[Ja5ya]

Thanks!