Connect to MS SQL Server through SAS

[tampham92]

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.

[SASKiwi]

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.

[jimbarbour]

---

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

[tampham92]

 I running it on a SAS server.

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

[jimbarbour]

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

[tampham92]

[AlanC]

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 con...:

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;

[tampham92]

what do I need to put for server?

[tampham92]

It said the ODBC engine cannot be found

ERROR: The ODBC engine cannot be found.

[jimbarbour]

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

[tampham92]

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

[jimbarbour]

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

[tampham92]

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

[jimbarbour]

---

@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

[tampham92]

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