How to use different SQL Server databases in SAS?

[pietinma]

I use different databases in my SAS scripts. In Oracle I can define a database using a schema statement:

libname o1 oracle user=&user pw=&pw path=&path dbprompt=no schema="db1";

libname o2 oracle user=&user pw=&pw path=&path dbprompt=no schema="db2";

How can I do the same in SQL Server? How do I define, that I don't want to use master but db instead? This does not work:

LIBNAME SQL ODBC DSN="DSN" user=&user pw=&pw schema="db";

[Kurt_Bremser]

Define "does not work". There are a gazillion of "don't works", so how should we know which one hits you?

Read the log (Maxim 2), and post it here.

Also do a google search (Maxim 6) for the text of an ERROR or WARNING message, if you get one.

[pietinma · Accepted solution]

Thanks,

You tracked me down. SAS does not give any erros messages. I thought that it should give an error message if my schema database doesn't exist.  I tried this in Oracle:

libname o oracle user=&user pw=&pw path=some_path dbprompt=no schema="not_existing_database";

No error messages in log though there is a typo in the database name. Why? Database queries to "existing_database" don't work, of course.

libname o oracle user=&user pw=&pw path=some_path dbprompt=no schema="existing_database";

No error messages in log. Database queries to "existing_database" work.

In SQL Server the following statement does not point libname SQL to "existing_database" and database queries to "existing_database" don't work. The actual LIBNAME statement does not give any error messages. The connection to the server succeeds, however.

LIBNAME SQL ODBC DSN="server" user=&user pw=&pw schema=existing_database;

35 LIBNAME SQL ODBC DSN="server" user=user pw=pw schema=existing_database;
NOTE: Libref SQL was successfully assigned as follows:
Engine: ODBC
Physical Name: existing_database

I couldn't get schema definition to function.  I just must create a new data source (DSN) for every database by using Microsoft ODBC Administrator.  It is not very handy while then I have many SQL Server drivers for one server connection. I thought that I could create only one data source in ODBC Administrator, define default database and use SAS schema definition to make queries to other databases.

[Reeza]

The idea is right, most likely the syntax is wrong. So you know the dsn works, you've tested that portion already?

I don't think the dsn name needs to be in quotes. This also assumes you're not on a server, otherwise that's a different issue. 

This is my connection string to a DB2 instance, something like this should work for you as well. 

libname home odbc schema="dbo" noprompt="server=SERVERNAME,PORT#;DRIVER=SQL Server;Trusted_Connection=yes;database=databasename";