ODBC Connection to MSSQL

Manfred Hagedorn · Post by **Manfred Hagedorn** » Tue Sep 13, 2011 7:37 am

Hello,

i try to setup a ODBC Connection to a MSSQL-Database

Well, i have searched in this Forum several hours ... tested all hints given ... now i have no further idea ?????

Everythink looks fine !

odbc.ini (part)
---------
[UIP_ALM]
Driver=/datastage/Ascential/DataStage/branded_odbc/lib/VMmsss20.so
Description=DataDirect 5.0 SQL Server Wire Protocol
Database=UIP_ALM
LogonID=bo_dbaccess
Password=aaaaaaaaaa
Address=123.456.789.0\UIP_ALM,1433
QuotedId=No
AnsiNPW=No

==> here i also tested with the server name
==> i also tested without instance

uvodbc.config (part)
----------------
<UIP_ALM>
DBMSTYPE = ODBC

Transformer with an OCBD-Connector gets:
main_program: Fatal Error: Could not connect to datasource[DataDirect][ODBC SQL Server Driver][libssclient20]General network error. Check your network documentation.

Trying to access with a Routine gets:
Status for SQLConnect call is -1, SQLSTATE is 08001, NATCOD is 11, message is [DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][libssclient20]General network error. Check your network documentation.

I can ping the MSSQL-server from the datastage server
I can connect via telnet to MSSQL-server from the datastage server
I can easy setup an ACCESS-ODBC-Connection with the same parameter (so the database is up and running)

I talked with the network-guys ... there is no firewall issue

Does anybody have any further hint, what could i test?

Thanks a lot !!!

Manfred Hagedorn

qt_ky · Post by **qt_ky** » Tue Sep 13, 2011 11:24 am

Have you tried taking out the "\UIP_ALM" string after the IP address? Here is what a working entry looks like (fake IP) from the ".odbc.ini" file:

[ABCMSSQL]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMmsss24.so
Description=DataDirect SQL Server Wire Protocol driver
Database=ABCMSSQL
LogonID=
Password=
Address=12.34.43.21,1433
QuotedId=No
AnsiNPW=No

Manfred Hagedorn · Post by **Manfred Hagedorn** » Tue Sep 13, 2011 11:44 pm

Hello Eric,
yes i also tried without this \UIP_ALM, but it didn't work.
Without this entry the job runs 1 second.
With this entry it runs 10 seconds. So it seems to do something more ...
Also i need this entry when setting up an ODBC connection to Microsoft Access DB.
Therefore i guess, \UIP_ALM is needed.

Best regards
Manfred

Manfred Hagedorn · Post by **Manfred Hagedorn** » Wed Sep 14, 2011 2:33 am

ppid=3031124:pi 3ce008:1 ENTER SQLSetEnvAttr
SQLHENV 0x30194d88
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3
SQLINTEGER 0

ppid=3031124:pi 3ce008:1 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
SQLHENV 0x30194d88
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3
SQLINTEGER 0

ppid=3031124:pi 3ce008:1 ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x30194d88
SQLHANDLE * 0x300cc904

ppid=3031124:pi 3ce008:1 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x30194d88
SQLHANDLE * 0x300cc904 ( 0x301575d8)

ppid=3031124:pi 3ce008:1 ENTER SQLSetConnectOption
HDBC 0x301575d8
UWORD 1041
UDWORD 3552748464

ppid=3031124:pi 3ce008:1 EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0x301575d8
UWORD 1041
UDWORD 3552748464

ppid=3031124:pi 3ce008:1 ENTER SQLSetConnectOption
HDBC 0x301575d8
UWORD 1042
UDWORD 3552748476

ppid=3031124:pi 3ce008:1 EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0x301575d8
UWORD 1042
UDWORD 3552748476

ppid=3031124:pi 3ce008:1 ENTER SQLConnect
HDBC 0x301575d8
UCHAR * 0x300a1f38 [ -3] "UIP_ALM"
SWORD -3
UCHAR * 0x300e02b8 [ -3] "bo_dbaccess"
SWORD -3
UCHAR * 0xf0c34fb0 [ -3] "******"
SWORD -3

ppid=3031124:pi 3ce008:1 EXIT SQLConnect with return code -1 (SQL_ERROR)
HDBC 0x301575d8
UCHAR * 0x300a1f38 [ -3] "UIP_ALM"
SWORD -3
UCHAR * 0x300e02b8 [ -3] "bo_dbaccess"
SWORD -3
UCHAR * 0xf0c34fb0 [ -3] "******"
SWORD -3

ppid=3031124:pi 3ce008:1 ENTER SQLGetDiagRec
SQLSMALLINT 2
SQLHANDLE 0x301575d8
SQLSMALLINT 1
SQLCHAR * 0x2ff1f124 (NYI)
SQLINTEGER * 0x2ff1f120
SQLCHAR * 0x2ff1f140 (NYI)

qt_ky · Post by **qt_ky** » Sun Sep 18, 2011 3:23 pm

The working example I gave is an ODBC to connect to SQL Server, not Access. For SQL Server, I've never had to add anything after the IP address except a comma then the port number for the database, but perhaps your SQL Server database is different.

You could also try removing values from these lines in the entry:

LogonID=
Password=

So far I have not found it necessary to provide values there. Whatever you enter through the GUI or at run time is passed through.

You can also search under the branded_odbc subdirectory for the "example" subdirectory, which contains an "example" program you can run. It prompts for the DSN, ID, and password and tries to connect. Sometimes you can get a useful error by testing the DSN that way directly from the command line.

Double check you have the correct IP and port number an database name.

Also try creating an ODBC DSN from your PC and testing the connection from there to see if it works or you get the same error, etc.

Manfred Hagedorn · Post by **Manfred Hagedorn** » Mon Oct 10, 2011 3:59 am

together with IBM we solved the problem
there was an old/wrong ODBC-driver-version

DSXchange

ODBC Connection to MSSQL

ODBC Connection to MSSQL

ODBC-Trace looks like this:

Problem solved