topic Re: Connect to MS SQL Server through SAS in SAS Enterprise Guide

Connect to MS SQL Server through SAS

tampham92 — Tue, 03 Aug 2021 20:41:04 GMT

Hello, I am trying to connect to MS SQL Server data table through SAS and I ran into this issue and not sure what it means.

I ran the following code and got issue

proc sql;
connect to SQLSVR as mydb (datasrc="SQL_SAS" user="XXX" password="XXX");
select * from connection to mydb
(select * from DATA_TABLE);
quit;
run;

23 GOPTIONS ACCESSIBLE;

24 proc sql;
25 connect to SQLSVR as mydb (datasrc="SQL_SAS" user="XXXXXX" password=XXXXXXXXXX);
ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
26 select * from connection to mydb
27 (select * from DATA_TABLE);
ERROR: The MYDB engine cannot be found.
ERROR: A Connection to the mydb DBMS is not currently supported, or is not installed at your site.
28 quit;

I already added that SQL_SAS datasource into the ODBC Datasouce Administrator but it still did not work.

Thank you so much for any help.

Re: Connect to MS SQL Server through SAS

SASKiwi — Tue, 03 Aug 2021 21:02:14 GMT

Where is your SAS program running? Locally on your PC or on a remote SAS server? If you are using EG as your SAS interface and you are using a connection profile then your program is running on a remote SAS server. This is where your data source needs to be defined.

Re: Connect to MS SQL Server through SAS

jimbarbour — Wed, 04 Aug 2021 17:05:30 GMT

@tampham92 wrote:

I already added that SQL_SAS datasource into the ODBC Datasouce Administrator but it still did not work.

If you're using ODBC, you need to say "Connect to ODBC..." not "SQLSVR".

Try the below code (fix the password and such first of course).

Jim

proc sql;
	connect to ODBC as mydb (datasrc="SQL_SAS" user="XXX" password="XXX");
	select * from connection to mydb
	(select * from DATA_TABLE);
	quit;
run;

Re: Connect to MS SQL Server through SAS

tampham92 — Wed, 04 Aug 2021 18:47:28 GMT

I running it on a SAS server.

I got this error when I used "connect to ODBC"

Re: Connect to MS SQL Server through SAS

jimbarbour — Wed, 04 Aug 2021 19:13:16 GMT

Hmmm. That's odd. Would you please run some code for me? This will help narrow down what the problem might be. Run the following:

PROC  SETINIT;
RUN;

You should get output (among other things) in the log that looks like this:

---Base SAS Software
---SAS/STAT
---SAS/CONNECT
---SAS/ACCESS Interface to PC Files
---SAS/ACCESS Interface to ODBC
---SAS Workspace Server for Local Access

I'm looking for SAS/ACCESS Interface to ODBC. Before we go too much farther, we should make sure that is installed on your server.

Jim

Re: Connect to MS SQL Server through SAS

tampham92 — Wed, 04 Aug 2021 19:34:07 GMT

Re: Connect to MS SQL Server through SAS

AlanC — Wed, 04 Aug 2021 19:54:19 GMT

Rather than using the built-in variables, try a direct connection string. This keeps you from having to put the name in place. Also, try the udl file trick (search or just look here: Dan Swearingen's Tech Stuff Blog » Blog Archive » Using a UDL file to generate and test database connection strings (polyweb.com):

libname SQLREF ODBC NOPROMPT="server=Server01.mycompany.com;driver=ODBC Driver 17 for SQL Server;Trusted_Connection=yes;Database=MY_DB;"
    STRINGDATES=NO IGNORE_READ_ONLY_COLUMNS=YES SCHEMA=DBO BULKLOAD=YES;

proc sql;
   SELECT DISTINCT SampleMode
   FROM SQLRef.Sales2016
   ;
quit;

Re: Connect to MS SQL Server through SAS

tampham92 — Wed, 04 Aug 2021 19:55:43 GMT

what do I need to put for server?

Re: Connect to MS SQL Server through SAS

jimbarbour — Wed, 04 Aug 2021 19:56:49 GMT

OK, great. So, you've definitely got ODBC capability in your SAS environment.

QUESTIONS:

1. Could it be a timing issue? When did you set up the ODBC DSN? Have you tried opening a fresh SAS session and trying your code there? I doubt that's the issue, but maybe.

2. What are your driver options? I see "SQL Server" in your ODBC set up. I have "SQL Server Native Client 11.0" in mine. Are there any other driver options available to you?

3. Is there anyone else in your shop that has the connection working? Perhaps you could ask them about what their settings are for the DSN that they set up in the ODBC Data Source Administrator.

Jim

Re: Connect to MS SQL Server through SAS

tampham92 — Wed, 04 Aug 2021 19:58:59 GMT

QUESTIONS:

1. Could it be a timing issue? When did you set up the ODBC DSN? Have you tried opening a fresh SAS session and trying your code there? I doubt that's the issue, but maybe.
- I set it up yesterday and I expect it should work right away

2. What are your driver options? I see "SQL Server" in your ODBC set up. I have "SQL Server Native Client 11.0" in mine. Are there any other driver options available to you?
- I have ODBC, SQL Server and SQL Server Native Client 11.0. Which one should I use preferably?

3. Is there anyone else in your shop that has the connection working? Perhaps you could ask them about what their settings are for the DSN that they set up in the ODBC Data Source Administrator
- Not at the moment

Re: Connect to MS SQL Server through SAS

tampham92 — Wed, 04 Aug 2021 20:00:39 GMT

It said the ODBC engine cannot be found

ERROR: The ODBC engine cannot be found.

Re: Connect to MS SQL Server through SAS

jimbarbour — Wed, 04 Aug 2021 20:05:59 GMT

Try setting up a new DSN but this time use "SQL Server Native Client 11.0" and then try a version of your SAS code that uses the new DSN.

QUESTION: Is your SAS environment running with an 32 bit or 64 bit OS? Would you run this bit of code and post the log, please?

%PUT	NOTE:  &=SYSHOSTINFOLONG;

Jim

Re: Connect to MS SQL Server through SAS

tampham92 — Wed, 04 Aug 2021 20:15:52 GMT

I got this error

libname sqwirdsn sqlsvr dsn=SQL_SAS2 user=myuser password='Start123' schema=mypw;

ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified

Re: Connect to MS SQL Server through SAS

jimbarbour — Wed, 04 Aug 2021 20:24:23 GMT

@tampham92 wrote:
I got this error

libname sqwirdsn sqlsvr dsn=SQL_SAS2 user=myuser password='Start123' schema=mypw;

ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified

Actually, I think that's progress. 🙂

Try changing "sqlsvr" to ODBC.

libname sqwirdsn ~~sqlsvr~~ ODBC dsn=SQL_SAS2 user=myuser password='Start123' schema=mypw;

Jim

Re: Connect to MS SQL Server through SAS

tampham92 — Wed, 04 Aug 2021 20:30:16 GMT

Now I got
ERROR: The ODBC engine cannot be found.
Do you think I need the SAS Foundation and ODBC drivers installed based on what I researched

Re: Connect to MS SQL Server through SAS

jimbarbour — Wed, 04 Aug 2021 20:32:48 GMT

I would try that. SAS is not finding the DSN/driver for whatever reason, so, yes, that would be the next area to look in. Since the driver you're using is in the list of available drivers, I'm assuming that the driver is installed, but maybe not. Where's @SASKiwi when you need him? 🙂

Jim

Re: Connect to MS SQL Server through SAS

SASKiwi — Wed, 04 Aug 2021 20:38:31 GMT

Run this to confirm if you have SAS/ACCESS to ODBC installed or not and post the output:

proc product_status;
run;

The evidence you have supplied indicates you are licensed for it, but the errors you are getting suggest you either do not have it installed or it is not configured correctly.

Re: Connect to MS SQL Server through SAS

jimbarbour — Wed, 04 Aug 2021 20:41:17 GMT

Good ideas from @SASKiwi*

Additional question: Is your SAS environment running with an 32 bit or 64 bit OS? Would you run this bit of code and post the log, please?

%PUT	NOTE:  &=SYSHOSTINFOLONG;

Jim

*It took you six entire minutes to respond to my post. You're slipping I see. Why, I bet Superman would never have let this occur. Tsk, tsk. 😂😉

Re: Connect to MS SQL Server through SAS

AlanC — Wed, 04 Aug 2021 20:41:45 GMT

If you use that UDL trick I mentioned, on the server, or go into the ODBC drivers and look, you can get the ODBC driver version that is on your system. The UDL trick combined with a far easier way to connect using the ODBC NOPROMPT option will help you find the issue.

Re: Connect to MS SQL Server through SAS

tampham92 — Wed, 04 Aug 2021 20:42:57 GMT

This is the output