SQL server connection problem

babbu9 · Post by **babbu9** » Wed Feb 22, 2006 12:19 pm

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

anntaylor · Post by **anntaylor** » Wed Feb 22, 2006 3:03 pm

You need to create a separate entry in .odbc.ini for each DB you want to connect to and the names should match.

babbu9 · Post by **babbu9** » Wed Feb 22, 2006 4:34 pm

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

anntaylor · Post by **anntaylor** » Thu Feb 23, 2006 7:34 am

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.

gforcegforce · Post by **gforcegforce** » Sat Mar 11, 2006 7:20 pm

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

bmadhav · Post by **bmadhav** » Sat Mar 11, 2006 11:39 pm

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?