Dear SAS Users,
I have set up a DSN that allows me to connect to Azure using a libname.
LIBNAME EREKA_AZ ODBC DSN = 'MYDB' SCHEMA='THESCHEMA';
Is there a way to translate this so I can use SQL PAss through?
PROC SQL NOPRINT;
CONNECT TO ODBC (
DSN = "MYDB"
SCHEMA = 'THESCHEMA'
);
create table test as select * from odbc(
select * from le_table
);
DISCONNECT FROM ODBC;
QUIT;
If you can successfully connect using a LIBNAME then you should be able to do the same with a CONNECT statement in PROC SQL. Please post the log of your SQL PASSTHRU test so we can see what errors you are getting.
I was making an error somewhere. I am able rto connect via the dsn. Thank you.
Back to being unable to connect.
libname AZsand ODBC DSN="EReKA DB Sandbox";
NOTE: Libref AZSAND was successfully assigned as follows:
Engine: ODBC
Physical Name: EReKA DB Sandbox
But no tables appear in the libname.
However,
PROC SQL NOPRINT;
CONNECT TO ODBC (
DSN = "EREKA DB Sandbox"
);
create table az_counts as select * from connection to odbc
(
select ereka_model_seq,count(*) as counter from [dbo].[ereka_model_data]
group by ereka_model_seq
);
DISCONNECT FROM ODBC;
QUIT;
Works. So the DSN is working, just not for a libname.
I have tested the conenction through ODBC-32 bit and SQL Server Management Studio.
It depends on how you've setup the "EReKA DB Sandbox" ODBC connection. If you've specified which database to connect then you don't need to specify the database, but you still have to tell it which schema to connect to, otherwise it will show as blank.
libname AZsand ODBC DSN="EReKA DB Sandbox" schema=dbo;
If you just specified "master" in the "EReKA DB Sandbox" odbc setup, then you'll have to specify the database i.e.: libname AZsand ODBC DSN="EReKA DB Sandbox" database = myDatabase schema=dbo;
For your passthrough again depending on how the odbc is setup you might need to specify the database. Use the "%PUT &SQLXRC. &SQLXMSG. ;" so that you can see the error.
PROC SQL NOPRINT;
CONNECT TO ODBC (
DSN = "EREKA DB Sandbox"
);
create table az_counts as select * from connection to odbc
(
select ereka_model_seq,count(*) as counter from [myDatabase].[dbo].[ereka_model_data]
group by ereka_model_seq
);
%PUT &SQLXRC. &SQLXMSG. ; * SQL Server query return code and message;
DISCONNECT FROM ODBC;
QUIT;
You can also create the ODBC credentials on the fly like below to test out how to connect directly to AZURE
libname Azure odbc complete="driver={SQL Server Native Client 11.0}; uid=myUserID; pwd=myPassword; server=azureServer,port; database=myDatabse" schema=dbo;
PROC SQL NOPRINT;
CONNECT TO ODBC (complete="driver={SQL Server Native Client 11.0}; uid=myUserID; pwd=myPassword; server=azureServer,port; database=myDatabse"
);
create table az_counts as select * from connection to odbc
(
select ereka_model_seq,count(*) as counter from [myDatabase].[dbo].[ereka_model_data]
group by ereka_model_seq
);
%PUT &SQLXRC. &SQLXMSG. ; * SQL Server query return code and message;
DISCONNECT FROM ODBC;
QUIT;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.