Connect to odbc- how do we know which database it is?

[swathi123]

Hi I am running proc sql step and trying to establish an odbc connection.

Eg:

proc sql;

connect to odbc as abc (dsn=***schema='dbo' uid=&*** pwd=&pwd);

disconnect from abc ;

If I am not told which database it belongs to, how would i know?

Versus:-

connect to db2 as abc (dsn='abc' uid=&***_usr pwd=&***_pwd);

disconnect from ids;

Where we clearly know which database it is.. Is there anyway i can find out which database ..

Pls let me know. Thanks.

[Reeza]

I'm not sure what you mean.

Is that what you see in the log? Or what is in your code?

[swathi123]

That is what i see in the code. Thanks

[SASKiwi]

One way which would make it clearer which database you are using would be to use a fully-defined connection string instead of a DSN. If you are running on Windows SAS using Windows authentification it would look like this:

connect to odbc as abc (noprompt = "server=MyDataServer;DRIVER=SQL Server;Trusted Connection=yes;DATABASE=MyDatabase;");

Note the DRIVER= option tells you the name of the ODBC driver for the database you are using.

Of course you have to know what your connection string is in the first place but you could find that out by looking in the ODBC Administrator tool if running on Windows.

[swathi123]

I am running sas on unix server. Also i do not have all that information available.. So just to be more specific, my question is - with this information- would we know which databse it is?

Thanks

[Reeza]

You don't unless you specify it.

That looks like a log that's been masked to me

OR

Code that is designed to be filled in and run but not saved in the program for security reasons.

[Anotherdream · Accepted solution]

I believe you are using an odbc connection to your database and then calling sql pass through to your aliased DSN.

I believe this because that is EXACTLY what I do.

Please do the following.  Go to run > control pane. > administrative tools > Data Sources (ODBC).. Double click on the shortcut.

Once opened, click on the tab "system dsN" and look for your dsn name specified in your code. Click on it and then click "configure" button... It will launch another tab that shows the "server" and name of the your dsn. Click Next twice and the VERY first button will be "change the default database to"...

That is your default database that your Aliased DSN is pulling from.  I will look for a pdf that explains this in more detail and link it to my message

http://www.depts.ttu.edu/ithelpcentral/solutions/odbcsetup.php

Just as an fyi note to you, it actually doesn't matter what your default database is, as long as you use a database specific name call to your sql tables you can pull from ANY of the databases on your server that your odbc connects through!

let me know if that doesn't answer your question!

[swathi123]

Reeza, I masked the actual info with asterisk. Actually that information has been provided. Also I am quite sure it is not log.

[Reeza]

My misread then.

In that case the dsn= name will be registered in your ODBC connections list. Several people have indicated how to navigate there and find it.

[jakarman]

All depends on the ODBD driver and the remote database, see: http://support.sas.com/techsup/technote/ts685.pdf
There could be a database definition in something like a odbc.ini file or other kind of setting.

A RDBMS usually is having a lot of tables they are organized in database or what is SAS is using as schema-s by that you have the same level as a SAS-library.

What is you question?
Wich database and you have a DBA telling you to use a database name?.

If the DBA is obstructive, does not like SAS usage, you need an SQL developers tool that will show you those names.

-> then use in SAS the schema option. It is the same just an other name.

An auditor that want to verify the right connection?

-> Find the DBA (you have an user and pswd so some DBA must have done that)  make a DB-connection and let the DBA show that connection is coming up.
You can never know what is possibly hacked (man in the middle). You can tell you have connected well when a feed back in an other communication path is done (two factor).