How to ODBC SQL Server with multiple database?

[Haikuo]

Background: I have created ODBC system DSN named , say, SQLPROD, for a SQL server, say, : SQLSERVER\PROD, on which there are multiple database, say SQLA, SQLB and SQLC. On SQL server side, SQLA is set to be the default for my sign-on. Now I use the following statement:

Libname test ODBC datasrc=SQLPROD User=haikuo Password=xxx schema=DBO;

I am able to access all the tables in SQLA.DBO.

Now questions:

1. What options should I use to access other database, such as SQLB and SQLC?

2. What options should I use to access multiple database at the same time, meaning they can share the same libref as long as table name is different?

3. How would I implement this with Management Console when registering tables?

Thank you so much,

Haikuo

[sas_9]

i think we can use different server definition under "server manager" under SASApp for different ODBC conenctions and then create different ODBC library for different ODBC conenction.

lets say if you have SQLA, SQLB and SQLC ODBC data source name - OF COURCE we can create group definition in "user manager" (if we have same userid for all three ODBC data source conenciton but different psw we can mentioned seperately under "account" tab in one group definition as mentioned below). e.g;

user manager:

group: ABC

accounts:

     auth domain: SQLA_auth

                         SQLB_auth

                         SQLC_auth

     userid: same for all 3 - if you have same otherwise you can create different definition for all 3 ODBC data source

     psw: dif. for all 3

server manager:

     you have to create 3 different definition for dw, vw, pw

library manager:

     it will be 3 differnet odbc library, you can create same as dw, vw, and pw

i think this way we can manage the thing.

good lk!

[Haikuo]

Thanks a lot, and I am sure your suggestions will definitely help me out down the road. But let's take one step a time. Right now, I don't even know how to access SQLB and SQLC, the default setting on SQL server side will automatically take me to SQLA. How to let SAS/ACCESS NOT to fall with default path, and go to other database on the server ?

Libname test ODBC datasrc=SQLPROD User=haikuo Password=xxx schema=DBO;

In above statement, SQLPROD is pointing to the server, on which there are multiple database, and I want to access them all, not just the default one for my singon. Is it even possible?

Haikuo

[Reeza]

I don't have the Management Console so not sure what options there are there.

What I have done before is have multiple ODBC connections set up, each pointing to a different database, but that gives you different libnames.

[sas_9 · Accepted solution]

if i understand you correctly, you need to create sepearete data source name to access SQLB and SQLC databases.

go to

control panel >administrative tools>data source (ODBC)>Sstem DSN

under system DSN> clikc on ADD and follow the direction.

- you may need to select drivers for db (check for SQLA - and select same for SQLB and C)

- once you define data source for SQLB and C, create group definition under "user manager"

- then create server definition for SQLB and C by using same data souce name using "server manager"

- and then create ODBC library for SQLB and C using "library manager"

make ODBC libraries as a "predefined" so users don't have to assign each and every time.

[Haikuo]

Thank you, Sandy and Fareeza. That is what I am going to do. Haikuo

[Haikuo]

Update: Have done it successfully! Thank you ALL!!!

[FosterMI]

Hi There - I'm trying to do the same thing - is there a way of doing this outside SAS Management Console?
Many thanks

[SASKiwi]

You can write a LIBNAME statement as shown in this thread.

[FosterMI]

Hi I'm really sorry to sound thick How to I group the SQL data sources in "user magaer" "server manager" libary manager"? Is this something in windows or SAS? Ps I can only access user DSN on ODBC due to IT restrictions could this be hindering my progress? Many thanks!

[SASKiwi]

No need to say sorry - we all learn all the time!

Server Manager allows you to define one SQL Server server connection at a time. Data Library Manager allows you to set up a connection to one SQL Server database at a time - you are automating the creation of a single LIBNAME statement behind the scenes.

So you define a data source for each SQL Server database you want. Then in SQL when you write SQL referring to them, SAS will then process the entire query in SQL Server - in theory. If you want to be sure you can use SQL passthru, but that requires you to write your DSN in code.

Not sure what you mean by "IT restrictions". Pretty much anything you can set up in the ODBC Administrator (we are talking Windows only here) defining your DSN can also be done in Management Console or in SAS SQL CONNECT or LIBNAME statements. IT can't stop this happening via security restrictions, they can only say "you must use the ODBC Administrator DSNs". If that is the case then I don't know why they would do that as avoiding DSNs also avoids having to maintain ODBC Administrator definitions all the time - a major advantage in my view.

[SASKiwi]

For Windows environments there is an alternative to defining ODBC data sources in the ODBC administration tool and that is to use server connections on your LIBNAME statements instead. The following example uses Windows authentification but it can be easily changed to authenticate in SQL Server with a supplied userid and password. The advantage is not having to maintain ODBC source definitions, especially useful for multiple computer and server situations.

libname TEST odbc noprompt = "server=MYSQLServer;DRIVER=SQL Server;Trusted Connection=yes" QUALIFIER = Mydatabase SCHEMA = Myschema;