topic SAS and Azure SQL DAta warehouse/Database in SAS Programming

SAS and Azure SQL DAta warehouse/Database

shwilliams4 — Fri, 28 Apr 2017 20:15:19 GMT

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;

Re: SAS and Azure SQL DAta warehouse/Database

SASKiwi — Sat, 29 Apr 2017 00:24:31 GMT

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.

Re: SAS and Azure SQL DAta warehouse/Database

shwilliams4 — Mon, 01 May 2017 22:11:04 GMT

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

Re: SAS and Azure SQL DAta warehouse/Database

shwilliams4 — Wed, 03 May 2017 17:26:08 GMT

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.

Re: SAS and Azure SQL DAta warehouse/Database

Jansen — Tue, 02 Oct 2018 05:47:58 GMT

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;