SQL Server connections

krystlecsy · Post by **krystlecsy** » Tue May 31, 2005 5:24 am

Hi all,

I am currently hitting an error in the DataStage Manager > Import > Table Definitions > ODBC Table Definitions >

DSR.MetaGeta(GET.TABLES)(SQLConnect('MYS_SIEBEL_DB','MIFactBase')): BCI Error:
SQLSTATE=IM003,CODE=0,[DataStage][SQL Client]An unknown DBMS type has been specified
SQLSTATE=IM997,CODE=0,[DataStage][SQL Client]An illegal configuration option was found

Below are the .odbc.ini file that is configured:-

[KOREA_DB]
Driver=/u01/app/Ascential/DataStage/branded_odbc/lib/VMmsss19.so
Description=DataDirect SQL Server Wire Protocol
Database=DB_MI_TEST
LogonID=pcakorea
Password=pcakorea
Address=128.231.160.65,1433
QuotedId=Yes
AnsiNPW=Yes

[MYS_SIEBEL_DB]
Driver=/u01/app/Ascential/DataStage/branded_odbc/lib/VMmsss19.so
Description=DataDirect SQL Server Wire Protocol
Database=ESG_MLY_PS01
LogonID=MIFactBase
Password=abcd1234
Address=18.37.20.20,1433
QuotedId=Yes
AnsiNPW=Yes

The connection to the KOREA_DB works perfectly fine. Why does MYS_SIEBEL_DB encounters an error? Please help!!

krystlecsy · Post by **krystlecsy** » Tue May 31, 2005 5:26 am

Forgot to mention that I have edited the uvodbc.config file in the DSEngine folder and also in the Project folders.

<KOREA_DB>
DBMSTYPE = ODBC

<MYS_SIEBEL_DB>
DBMSTYPE = ODBC

roy · Post by **roy** » Tue May 31, 2005 5:57 am

Hi,
if your actually connecting to a SQLServer DB for your siebel despite the driver saying: hey it's not!
check you configured it correctly.
I'm not sure if a class A IP should be an issue.

Is the KOREA_DB connection working properly?

krystlecsy · Post by **krystlecsy** » Tue May 31, 2005 6:31 am

yes, the KOREA_DB is working properly!

ray.wurlod · Post by **ray.wurlod** » Tue May 31, 2005 4:43 pm

It might be the class A IP address. Can you ping 18.37.20.20 from the DataStage server machine? Given that both databases are SQL Server, and that they are identically configured (same driver, same port) it's got to be something about the connectivity. Also check that MYS_SIEBEL_DB really is using port number 1433.

krystlecsy · Post by **krystlecsy** » Tue May 31, 2005 7:08 pm

Yes, I can ping the 18.37.20.20 from the DataStage server and it is the correct port because I tried accessing the SQL Server database through the Query Analyzer (Microsoft SQL Server) and it works perfectly fine.

ray.wurlod · Post by **ray.wurlod** » Tue May 31, 2005 7:37 pm

OK, we've eliminated one possibility (assuming you tested Query Analyzer from the DataStage server machine).

Next is to try to figure out what is meant by "An unknown DBMS type". Can you enable ODBC tracing for this particular DSN, and try once again to connect. You will gather a huge amount of information, so disable tracing as soon as you're done.

krystlecsy · Post by **krystlecsy** » Tue May 31, 2005 8:56 pm

Found the resolution!! In the uvodbc.config file after the DBMSTYPE = ODBC, please type <ENTER> and it works!!

ray.wurlod · Post by **ray.wurlod** » Wed Jun 01, 2005 1:01 am

That, and the absolute requirement to have a space either side of the "=" character, are well-known annoyances. (It's not only in DataStage, however; for example most database's bulk loader control scripts must also have that final line-terminator, as an indicator that a command is ended.) Well done for finding it! Did you - on the point of giving up - re-type the enture uvodbc.config file entry?

krystlecsy · Post by **krystlecsy** » Wed Jun 01, 2005 5:08 am

hahaha....no, i dun think i was that desperate. a friend of mine helped to look and found the problem! :D thanks for the info about the "=" as well. will note it down!