SQL PassThru - Multiple Database Connections

[buechler66]

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;

[SASKiwi]

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. 

[buechler66]

Yes, they are both on the same server.

[LinusH]

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.

[SASKiwi]

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;

[buechler66]

oh a DB Link. Unfortunately we are not allowed to have a DB Link between these two databases. 😞

[Quentin]

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

[SASKiwi]

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;

[Quentin]

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.

[SASKiwi]

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

[Quentin]

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.