Connect to MS SQL Server through SAS

[jimbarbour]

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

[AlanC]

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.

[tampham92]

what do I need to specify for the nonprompt component like server and database and such? Can you please give me an example?

[AlanC]

I put that in my first example. Trick is to make sure you get the ODBC connection string exact. You can get that  using the udl trick. the udl trick, if followed all the way to where the string is stored, will provide the entire connection string for you. Otherwise, copy the driver string from the ODBC wizards in Windows.

[tampham92]

where can I find the ODBC connection string? Sorry this is the first time I try to connect

[tampham92]

Okay I think tried it and it gave me an error "ODBC engine cannot be found"

[AlanC]

You need the ODBC driver install for the db you are using (sql server). If you are using EG via a server, it needs to be installed there, ax others have stated.

[SASKiwi]

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.

[jimbarbour]

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. 😂😉

[tampham92]

This is the output

[tampham92]

So does it means I dont have SAS/ACCESS to ODBC installed?

[SASKiwi]

@tampham92 - Correct. You have a licence for SAS/ACCESS to ODBC but the product is not installed. However you do have SAS/ACCESS to SQL Server installed and licensed so that is the LIBNAME engine you need to try.

Where are you SAS sessions running? Locally on your PC or on a remote SAS server? If you are using a remote SAS server what OS does it run on?

[tampham92]

I run it on a SAS server within EG.

When I tried to run the libname statement, I got this error message

24 libname sqwirdsn sqlsvr dsn=SQL_SAS2 user=xxx password=XXXXXXXXXX schema=dbo;
ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified
ERROR: Error in the LIBNAME statement.

[SASKiwi]

You need to talk to your SAS administrator regarding what database connection settings to use. If your SAS server runs on Unix then data sources need to be defined in an odbc.ini file not the Windows ODBC Administrator and only users with remote login access to your SAS server can do this work.

[jimbarbour]

---

@tampham92 wrote:

This is the output

---

That is a bit odd that @tampham92's SETINIT, see below, mentions ODBC but the product status does not.  @SASKiwi, what do you think?