MULTIPLE SQL SERVER DATABASES ODBC.INI SETTING

[ejazsalim]

Is it possible to access multiple SQL Server Databases running on the same server with a generic .odbc.ini setting.

My requirements does not allow me to create new entry into the .ODBC.INI file for every database in the server.

Here is a working .ODBC.INI file that has 2 entries going into the same SQL Server but different databases.

[BISOPINFSPH]
Driver=/opt/is/IBM/InformationServer/Server/branded_odbc/lib/VMmsss23.so
Description=DataDirect SQL Server Wire Protocol driver
Database=bisopinfsph
LogonID=uid
Password=pwd
Address=wassql001v,1433
QuotedId=No
AnsiNPW=No

[BISOPLOAN]
Driver=/opt/is/IBM/InformationServer/Server/branded_odbc/lib/VMmsss23.so
Description=DataDirect SQL Server Wire Protocol driver
Database=HABITAT03262010
LogonID=uid
Password=pwd
Address=wassql001v,1433
QuotedId=No


------ I tried doing ------
---------------------------
[WASSQL001V]
Driver=/opt/is/IBM/InformationServer/Server/branded_odbc/lib/VMmsss23.so
Description=DataDirect SQL Server Wire Protocol driver
Database=db
LogonID=uid
Password=pwd
Address=wassql001v,1433
QuotedId=No

I want to pass the DATABASE NAME as a parameter into the ODBC stage in paralell job.

Any advice would help

[ray.wurlod]

My requirements does not allow me to create new entry into the .ODBC.INI file for every database in the server.

Tough. There's no other way to do it.

The DSN can be a job parameter.

[ejazsalim]

Isnt there a way to pass the DSN name and database name as a string parameter to the ODBC Stage ???

Like
DSNNAME@DBNAME

[ray.wurlod]

Of course you can pass any string into the job. But how are you planning to use it? The ODBC stages require the DSN to be separate.

[ejazsalim]

I am at the end of my wits. The only way that I can think of is to change the .odbc.ini file at run-time (I personally dont like that idea as it might lead to more complications with multiple instances and other applications that might be using the same datastage project)

Will ask IBM and see if they have a better answer

Thanks

[ray.wurlod]

They won't, they don't, there isn't one.

[chulett]

My requirements does not allow me to create new entry into the .ODBC.INI file for every database in the server.

Tough. There's no other way to do it.

Ray, I'm a little disappointed - where is your famous cry of 'Resist stupid requirements!'?

[ejazsalim]

A non datastage person (EX SQL Server DBA) provided the solution.

We created one SQLSERVER connection in the .odbc.ini file and the jobs connect using that DSN and in the BEFORE SQL of the ODBC stage we are switching the database using the SQL command

USE <SQL Server DB NAME>

** and prefix the table name with databasename like
wassql001v.dbo.table_name