ODBC connection for SQL

[Recep]

Hello all,

We recently changed our PCs in my organization and now I'm having trouble connecting to our SQL server which was not an issue before the change. Before I used to use to create the libname as follow:

libname bctr odbc datasrc=SPAPPBCTS001 schema=bctr;run;

This code now gives me the following error:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source
name not found and no default driver specified
ERROR: Error in the LIBNAME statement.

Based on a quick Google search I found the following code to set up the initial connection:

Libname BCTR ODBC NoPrompt="Driver=SQL Server; Server=10.251.185.25; Database=EDW; schema=bctr"; (I changed the parameters to fit into my environment).

Even though the code above creates the libname BCTR it does not make a connection to the server hence the libname is empty.

I would appreciate any suggestion.

Thanks a lot in advance!

Recep

PS: I already have the SQL server driver file (SQLSRV32.DLL) in my new machine.

[SASKiwi]

Are you trying to connect from SAS on your PC? If so what is the bitness of your SAS installation? The bitness must align with your ODBC driver bitness. 64-bit SAS requires a 64-bit ODBC driver. Go into the Windows ODBC Administrator and confirm if the ODBC driver is showing in the list of available drivers and to confirm the actual driver name.

[ocasaenva]

Hey all, I am provisioning a SAS server for my company and SAS's handling of ODBC connections is fighting with me. I can connect to and query an ODBC connected database with isql, and I can add the same ODBC connection as a Library in SAS Studio, but when attempting to query the Library I get the following error:

<LIBNAME>.<TABLENAME>.DATA File does not exist

SAS seems to be appending .DATA and thinking am querying a file, rather than an ODBC connection. I can post the actual PROC PRINT statement when I am back at my desktop, but it's the statement pulled right from the SAS ODBC docs.

I feel like I am missing something fundamental here but no amount of Googling has gotten me anywhere.

[ballardw]

Do you get that error in the Log? If so, then you might share the Log with the code and all the messages generated.

Not an expert in ODBC in all its flavors by any means but showing the way that you connect SAS to the ODBC will help.

Also, so names used in external data bases may not be valid for SAS to use in some forms. How you "query a Library" might require additional options for the connection.

The about about .DATA is basically telling you SAS attempted to use it as a data set as opposed to data view, catalog or other type of object. It is not indicating that name of the object had .DATA as part of the name.

[SASKiwi]

@ocasaenva  - Welcome to the SAS Community. The first thing to learn when posting a problem that involves SAS errors or warnings is to post the complete SAS log of the code and the notes, warnings and errors that follow. Without that we have no accurate evidence of what's going on. I sometimes feel when I respond to posts that half of them are "post your COMPLETE SAS log". Look at other posts and you will see what I mean. Posting just error messages is another favourite habit. Useful, but it is way better if you include the code that caused it! Rant over.

Regarding your post, it looks like the ODBC connection is working but not necessarily how you expect. I await your log with interest.

[Ksharp]

Firstly of all, make sure connection string "SPAPPBCTS001 " is working at your SQL client side . Once you can connect to SQL by "SPAPPBCTS001 " ,you can use this LIBNAME.

[Sajid01]

@Recep 
On the new desktops odbc connection to sql server need to be created.
The libname statements will work once that is done.
Has this been done?

@ocasaenva 
You need to configure sasenv_local.

In both the above cases the best person to approach is your local SAS Administrator.
They are aware of the local environment and can resolve the issue.