SQL Server Connectivity Problem

[chulett]

You don't indicate what hardware / operating system you are using, which can be important in cases like this. Also, in the readme for your server install, is the SQL Server Wire Driver you are trying to use supported on your platform?

[anupam]

You don't indicate what hardware / operating system you are using, which can be important in cases like this. Also, in the readme for your server install, is the SQL Server Wire Driver you are trying to use supported on your platform?

The details are
1. 880R, 4-CPU, 80 GB RAM.Solaris
2. SQL Server Wire Driver VMmsss18.so is supported as it has been provided by Ascential itself in branded_odbc/lib directory. Though this is not mentioned in the readme document.

I want to confirm one more thing. Whenever a new database entry has to be done for Datastage Connectivity. The Entries are done in
1) .odbc.ini
2) uvodbc.config and the copy should be in the project directory also.
3) besides tns names entry.
Please correct me if i am wrong.

[chulett]

Just because you found it on your server doesn't necessarily mean it is supported. I believe you'll find they ship all of them and then (in the documentation) tell you which ones will or will not work per platform. I'm not somewhere where I can verify this at the moment, but there should be an entire section of the readme & Installation Guide dedicated to ODBC, including a listing of what works where.

Your first two 'confirmation' steps are mostly correct and your actual entries in your original post look fine, as best as I can tell. The comment about "a copy should be in the project directory also" is not correct. There is no need for that, the entry in the Engine directory is basically the global entry and will work for all Projects. You would only drop a copy in a specific Project directory if you needed something of the same name but of a different configuration, in other words a project specific over-ride. Perhaps if you removed the entry from the Project it might help?

Sorry there isn't more direct help here. I think it's important to establish whether this particular Wire Driver is supported (i.e. expected to work) on your platform. Do you have support? If so, I'd give them a shout and ask them directly.

[ray.wurlod]

The search precedence for uvodbc.config (on UNIX platforms) is:
1. project directory
2. DSEngine directory
3. /etc directory

On Windows platforms DSN information is obtained directly from the Registry. These days.

[anupam]

Please suggest me how to solve this problem of connecting to SQL Server. I guess i have already given the required details.

[chulett]

Sorry to see this is still a problem for you. I do have to disagree (to a small extent) with your 'required details' comment... if you had, I'm sure someone would have been able to provide a solution for you by now.

DSR.MetaGeta(GET.TABLES)(SQLConnect('RECON','dssuser')): BCI Error:
SQLSTATE=01000,CODE=0,[DataStage][SQL Client][ODBC]

Is that the complete error message? There is no textual part? A quick Google for SQLSTATE shows that SQLSTATE=01000 is a 'General Error' which is kind of a catch-all error, as best as I can tell. And CODE=0 doesn't exactly clarify things either.

Any chance your error message said something like "this server has been disconnected"? If so, check here for a possible solution.

The other apparent message for an error like this is "Data source name not found". Are you sure everything is correct in your ODBC setup? Names match, including case? Some other things to try: the post you linked to in your first post stated that they needed to enter the ADDRESS and DATABASE keywords in UPPER CASE to make things work while yours were in mixed case - have you tried that? I'd also try fully pathing your Driver entry in case using <ODBC_HOME> is a problem.

Hope some of this helps...

[ray.wurlod]

CODE=0 is more usually reported as DBMS Code = 0; it's the error code returned from the database server. However in this error message it appears (because there are no square-bracketed tokens beyond [ODBC] that the connection either failed or was rejected by the ODBC driver manager. It appears, indeed, that no ODBC driver has been loaded.
Can you check whether the data source name (DSN) specified in the job is actually configured on the DataStage server machine (that is, in the .odbc.ini file and in the uvodbc.config file)?

[anupam]

Hello Ray,

I have checked the entry in both these files. Please find the entry for your reference also.

1. Entry in uvodbc.config
<RECON>
DBMSTYPE = ODBC

2. Entry in .odbc.ini

[RECON]
[SQLServer Wire Protocol]
Driver=/u01/app/datastage/product/Ascential/DataStage/branded_odbc/lib/VMmsss18.so
Description=TIBCO 4.10 SQL Server Wire Protocol
Database=RECON
LogonID=dssuser
Password=dssuser123
Address=10.8.51.113,1433
QuotedId=Yes
AnsiNPW=No

Besides also checked the driver in the specified path.

[ray.wurlod]

That looks OK. I presume SQL Server is listening on port 1433?

[anupam]

That looks OK. I presume SQL Server is listening on port 1433?

That's right. SQL Server is listening on port 1433

[ray.wurlod]

I think the time has come for you to do some diagnosis.

Try connecting to the data source with the DS_CONNECT command in your DataStage project.

Within the branded_odbc directory is a READ.ME file that is worth reading through, and a demoodbc executable in branded_odbc/demo (and a readme file that explains how to use it). In demoodbc/bin you will find ivtestlib, which can be used to test whether the driver manager and driver are loaded into memory.

Code: Select all

bin/ivtestlib VMmsss17.so

Finally, there's an ODBC trace utility (lib/odbctrac.so); you can configure tracing and get a detailed picture of what's happening.

[anupam]

[quote="ray.wurlod"]

Code: Select all

bin/ivtestlib VMmsss17.so

1. Tested this output for your reference

ivtestlib VMmsss18.so
Load of VMmsss18.so successful, qehandle is 0xFF280758

2. I do not know how can i use lib/odbctrac.so.
Can you please explain how can i use tracing.