BookmarkSubscribeRSS Feed
shwilliams4
Obsidian | Level 7

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;

 

 

4 REPLIES 4
SASKiwi
PROC Star

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.

shwilliams4
Obsidian | Level 7

I was making an error somewhere. I am able rto connect via the dsn. Thank you.

shwilliams4
Obsidian | Level 7

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.

 

Jansen
Calcite | Level 5

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3786 views
  • 1 like
  • 3 in conversation