DSXchange

Hallo all,

I am using ODBC to access sybase database.

It worked well with this statement:
SELECT code_id FROM PDSDB.dbo.tscm_code;

However, I get the error below from this statement:
SELECT code_id FROM tscm_code;

DSBrowser..ODBC_1.DSLink3: DSD.BCIOpenR call to SQLTables failed.
SQLSTATE = HY000, DBMS.CODE = 2762
[DataStage][SQL Client][ODBC][Data Direct][ODBC Sybase Wire Protocol driver][SQL Server]The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.

Is the anything I need to add in the uvconfig file?

Appreciate your help on this problem.

Hi,

The table has to be referenced with DB name & the owner name when the table used in the SQL query in DataStage. This will apply for all Databases.

And it is not required to change anything in UVConfig file.

Hope this would clarify.

Regards
Saravanan

Thanz Elavenil,

The table has to be referenced with DB name & the owner name when the table used in the SQL query in DataStage. This will apply for all Databases.

It does not apply to cases when I access the DB2 database.
Could it be the table permission? Because the datastage user is different from the sybase database user.

elavenil wrote:The table has to be referenced with DB name & the owner name when the table used in the SQL query in DataStage. This will apply for all Databases.

Actually, no - it doesn't. It is definitely a Good Practice and may actually be required in Sybase, for all I know. However, for a query where the owner is not specified, Oracle (for example) will search the current user's schema for that table name. Failing that, it will look for a Synonym (private, then pulic) of that name that will point it to the correct table to use.

A Sybase guru will have to explain why a failed select statement would generate a message that mentions 'create table', unless the "create table if it doesn't already exist" option is checked off.

Christina Lim wrote:the datastage user is different from the sybase database user.

Shouldn't have anything to do with it. Sybase won't really have a clue what your 'DataStage user' is, everything is driven by the userid specified in the ODBC / Sybase OC stage.

I think the aerosol kid is onto something, you may have "create table" turned on somewhere in your sybase stage. The login you are using is defaulting to the tempdb schema within Sybase, when you include the schema name of PDSDB it works, when you leave it out it looks for tempdb.tscm_code. You could leave the schema name within your DS jobs or get the Sybase administrator to make PDSDB the default database for that login.

Actually i faced the same problem eventhough i set the default database name to PDSDB. I still hv the same error. According to the Sybase DBA, this error message is because i use a begin trans and end trans for the sql. Just wonder is there any setting in datastage could treat each sql as a transaction?

You wouldn't have a "create table" possibility if doing a SELECT.

I suspect it's how the Sybase parser is treating a query with an unqualified table name, either checking to see whether it's in the temporary tables space, or preparing the SQL "in case" it's of the form SELECT ... to temptable;

Find out, from your DBA, what error code 2762 means and what the suggested remedy is.

It's interesting that the error message refers to a multi-statement transaction. Is there still an open transaction on the same table? Has an isql user forgotten to type go?