DATA Step, Macro, Functions and more

SQL PassThru - Multiple Database Connections

Reply
Regular Contributor
Posts: 212

SQL PassThru - Multiple Database Connections

Hi. Is it possible to merge two tables that reside on two seperate databases.  In my case table ivas.ods_iv_recon_selected_mp sits on database ibscrprd, but table imapsscr.ods_bi_recon_selected_mp resides on database ivasprd.  

 

The query below doesn't work obviously because I have only a Connect To statement for the ibscrprd database. Do you know if it's possible to connect to two databases at once using SQL PassThru? And if so, how I might alter my coding attempt below to do so?

 

Any help would be greatly appreciated. Thanks.

 

proc sql;
connect to oracle as db (user="me" password="mypw" path="ibscrprd");
create table myTable as 
select * from connection to db
(select *,
"A" as source,
"-" as RuleFlag,
"-" as rule_nm length = 58,
actual_dlvry_date as ad_dt,
"-" as rule_order
from ivas.ods_iv_recon_selected_mp
where imb_dlvry_zip_5=&ZIP5_QUOTED
union all corresponding
select *,
"B" as source,
"-" as RuleFlag,
"-" as rule_nm length = 58,
actual_dlvry_date as ad_dt,
"-" as rule_order
from imapsscr.ods_bi_recon_selected_mp
where imb_dlvry_zip_5=&ZIP5_QUOTED;
quit; ); disconnect from db; quit;
Super User
Posts: 3,102

Re: SQL PassThru - Multiple Database Connections

[ Edited ]

As long as the databases are on the same server it should be possible. Is this the case? I know how to do it in SQL Server by establishing a connection at the server level and then including the database name on the front of the schema and table names.

 

I'm sure something similar would work for Oracle but I can't research it right now. Maybe someone else will know. 

Regular Contributor
Posts: 212

Re: SQL PassThru - Multiple Database Connections

Yes, they are both on the same server.

Super User
Posts: 5,256

Re: SQL PassThru - Multiple Database Connections

Like @SASKiwi says, this will be Oracle specific syntax within your pass-through block of code.

So research Oracle resources, or get some help from your Oracle DBA/developers.

Data never sleeps
Super User
Posts: 3,102

Re: SQL PassThru - Multiple Database Connections

I think your code should look something like this example. Suggest you run it past your Oracle DBA though - he should know the right syntax

 

proc sql;
connect to oracle as db (user="me" password="mypw" path="ibscrprd");
create table myTable as 
select * from connection to db
(select * from table_a x,table_b@OtherDatabase y where X.row_id = Y.row_id
); 
disconnect from db;
quit;
Regular Contributor
Posts: 212

Re: SQL PassThru - Multiple Database Connections

oh a DB Link. Unfortunately we are not allowed to have a DB Link between these two databases. Smiley Sad
PROC Star
Posts: 1,230

Re: SQL PassThru - Multiple Database Connections

[ Edited ]

@SASKiwi could you show an example of how to connect to to SQL server from SAS at the server level instead of the database name? (i.e. what would go in the odbc.ini file, and how an explicit pass-through query would look)? 

 

Just the other day I was wondering if this is possible.   I work on one server where we have multiple databases, and it would be convenient to thave a server-level connection rather than update the OBDC.ini every time someone creates a new database.

 

 

Super User
Posts: 3,102

Re: SQL PassThru - Multiple Database Connections

[ Edited ]

Sure @Quentin- this example assumes running on Windows but doing the same thing on Unix should be similar:

 

proc sql;
 connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;");
  create table Want  as 
  select * from connection to odbc
  (SELECT *
   FROM [MyDatabase1].[MySchema1].[Table1] as A
   INNER JOIN [MyDatabase2].[MySchema2].[Table2] as B
   ON A.[Key] = B.[Key]
   )
  ;
  disconnect from odbc;
quit;

 

PROC Star
Posts: 1,230

Re: SQL PassThru - Multiple Database Connections

Thanks @SASKiwi.  Sorry for being dense, but with that connection string (noprompt="...") do you pass enough information that you completely avoid the need to pre-define an ODBC data source (in ODBC administrator on Windows or odbc.ini on linux)? 

 

Okay re-reading the docs, it says NOPROMPT "specifies connection options for your data source or database."  So that means with NOPROMPT I should be able to pass all the information needed to connect to a database on a server, without first defining it as an ODBC data source?  [guess that's the same question twice]

 

So the next time someone says "Please pull some data from database X on server Y using account ReaderZ and PasswordZ " I should be able to do it myself from our SAS server, without asking the admin to define a new ODBC connection for me?  (Assuming I don't need to register the database in metadata)  [Same question third time perhaps, but I'm excited ]

 

That would make me sooo happy.

Super User
Posts: 3,102

Re: SQL PassThru - Multiple Database Connections

@Quentin - Does your SAS server run on Windows or Unix? If on Windows then the NOPROMPT completely defines your SQL Server connection and nothing needs to be defined elsewhere. If you are on Unix then I think you are stuck with using ODBC.INI. Try a query like the one I posted anyway. I suspect it may not matter what database/schema you connect to initially as long as you use the 3-stage table strings to reference tables in other SQL Server databases.

PROC Star
Posts: 1,230

Re: SQL PassThru - Multiple Database Connections

Thanks. Will play with it a bit. Our SAS server is Linux. But I have Windows PC SAS too. So will start by trying to get it to work on Windows, then will move on to Linux.  --Q. 
Ask a Question
Discussion stats
  • 10 replies
  • 868 views
  • 2 likes
  • 4 in conversation