topic Re: Connect to SQL Server to update temporary dataset in SAS Programming

Connect to SQL Server to update temporary dataset

Ja5ya — Mon, 05 Oct 2020 12:53:01 GMT

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.

Re: Connect to SQL Server to update temporary dataset

SASJedi — Mon, 05 Oct 2020 14:42:44 GMT

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.

Re: Connect to SQL Server to update temporary dataset

Ja5ya — Tue, 06 Oct 2020 03:40:53 GMT

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.

Re: Connect to SQL Server to update temporary dataset

SASKiwi — Tue, 06 Oct 2020 19:10:38 GMT

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?

Re: Connect to SQL Server to update temporary dataset

Kurt_Bremser — Tue, 06 Oct 2020 21:31:38 GMT

@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.

Re: Connect to SQL Server to update temporary dataset

SASJedi — Thu, 08 Oct 2020 13:31:13 GMT

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;

Re: Connect to SQL Server to update temporary dataset

Ja5ya — Fri, 09 Oct 2020 04:54:09 GMT

Thanks!