Page 1 of 1

SQL server connection problem

Posted: Wed Feb 22, 2006 12:19 pm
by babbu9
Hi
We have a AIX box with datastage installed on it. We have sqlserver on a windows system and would like to connect to this database to get data.

How will we define the odbc connection to get connected to sql server.
What files need to be edited to get this effect.

My .odbc.ini looks like this

[SQLServer]
Driver=/app/dstage/Ascential/DataStage/branded_odbc/lib/VMmsss20.so
Description=DataDirect SQL Server Wire Protocol
Database=EDENDB
LogonID=cognos
Password=forperot
Address=192.168.200.20,1433
QuotedId=No
AnsiNPW=No


uvodbc.config

<localuv>
DBMSTYPE = UNIVERSE
network = TCP/IP
service = uvserver
host = 127.0.0.1
<dsorcl>
DBMSTYPE = ODBC
<dsodbc>
DBMSTYPE = ODBC
<sqlserver>
DBMSTYPE = ODBC

When I do a view data there is a error saying that
Fatal Error: Could not connect to datasource[DataDirect][ODBC lib] Data source name not found and no default driver specified


Can you please help us troubleshoot this.

Thanks
Bob

Posted: Wed Feb 22, 2006 3:03 pm
by anntaylor
You need to create a separate entry in .odbc.ini for each DB you want to connect to and the names should match.

Posted: Wed Feb 22, 2006 4:34 pm
by babbu9
Thank you for the reply, My odbc.ini has entries for other databases but I have posted only the one for sql server. What are the steps to configure the connection?

Thanks

Posted: Thu Feb 23, 2006 7:34 am
by anntaylor
Based on what you supplied, the names do not match and the error suggests as much.

There is a pdf ..../Ascential/DataStage/branded_odbc/books/books.pdf that can help with this configuration and testing the connection with ../bin/example.

I would also double check the port. It may not be the default 1433.

Posted: Sat Mar 11, 2006 7:20 pm
by gforcegforce
there are two things to ensure..
1-> the sql server should be on the same network XXX.XXX.XXX.YYY
all XXXs should match. OR else try to ping on that port. if it works it should work from Datastage
2-> win and sql server logons are enabled

Posted: Sat Mar 11, 2006 11:39 pm
by bmadhav
Get the port number from the SQL server DBA. The port number u r using seems to be the default dynamically determined port.
Were u able to connect to any other SQL server DB from this AIX node?