Sybase error

mab_arif16 · Post by **mab_arif16** » Fri Jun 09, 2006 11:25 am

Hi
I am sorcing the data from sybase database using the Sybase OCPX stage
and user defined sql.
I got the following warnings
Sybase_OC_0,0: Warning: CopyOf end_st .Sybase_OC_0: Sybase Server warning 2762 (severity 16) from stored procedure 'sp_tables', line 209: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.
Sybase_OC_0,0: Warning: CopyOf end_st .Sybase_OC_0: Sybase Server warning 208 (severity 16) from stored procedure 'sp_tables', line 223: #intermediate_roleset not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
Did anybody came accross such warning.
Any help is appreciated
Thanks
ARif

DSguru2B · Post by **DSguru2B** » Fri Jun 09, 2006 12:42 pm

I have never seen that error message before but i can certainly point you in the right direction. Lets start with analyzing the sql error.
For that click here And try to investigate on the error and see why in your sql giving this kind of error.
I just love google university

mab_arif16 · Post by **mab_arif16** » Fri Jun 09, 2006 2:59 pm

DSguru2B wrote:I have never seen that error message before but i can certainly point you in the right direction. Lets start with analyzing the sql error.
For that click here And try to investigate on the error and see why in your sql giving this kind of error.
I just love google university

The sql is pretty simple
SELECT bintostr(first_ID),first_name,first_state FROM first;
line 209: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.
I dont know where is create in line 209 and where is line 209.
223: #intermediate_roleset not found
What is roleset
Thanks
ARif

ray.wurlod · Post by **ray.wurlod** » Fri Jun 09, 2006 4:56 pm

It seems - without knowing too much about Sybase - that Sybase is trying to create a (temporary?) table in the tempdb database to store the result of your query. And, apparently, such action is not permitted, either because you don't have appropriate privilege to that database, or because it's simply not permitted. Check with your Sybase manuals or DBA (or even Sybase support provider).

Ultramundane · Post by **Ultramundane** » Mon Jun 12, 2006 11:10 am

I never ran into that problem, but I have way too many patches applied to say that it wasn't originally there.

It actually sounds like a teradata problem we had with the product. That is, Ascential was checking for permissions and was not checking for role permissions. Thus, in the end, by trying to check for permissions it actually made the product worse off. We had this permission checking removed for teradata. This may have also removed other permission checking.

#intermediat.... is created by the sybase stored procedure sp_activeroles. It lists what roles a user currently has active. If Ascential is starting a transaction to execute that procedure for permission checking you will get that error.

Ideally Ascential would not check at all for permissions and just return the error message from the database. Which is typically much more descriptive than the errors returned from datastage.

dsdev750 · Post by **dsdev750** » Wed Jun 21, 2006 3:44 pm

I was able to resolve the issue in a couple of jobs.

I was using the SybaseOCPX stage with "Generated SQL query". However, I didn't have the "database owner" with the table name. Thus instead of using "Owner.dbo.TableName" in the "Table Names", I was using "TableName". This was causing the error.

However, there are still a couple of jobs which have the same error. I have "User defined SQL" and can't get rid of these warnings. Please share if you found out a solution.

mab_arif16 · Post by **mab_arif16** » Mon Jul 10, 2006 3:53 pm

dsdev750 wrote:I was able to resolve the issue in a couple of jobs.

I was using the SybaseOCPX stage with "Generated SQL query". However, I didn't have the "database owner" with the table name. Thus instead of using "Owner.dbo.TableName" in the "Table Names", I was using "TableName". This was causing the error.

However, there are still a couple of jobs which have the same error. I have "User defined SQL" and can't get rid of these warnings. Please share if you found out a solution.

I was able to get rid of them by including statement: 'set rowcount 0' , before tuser defined query

Thanks
Arif

kunj201 · Post by **kunj201** » Wed Sep 20, 2006 9:47 am

dsdev750 wrote:I was able to resolve the issue in a couple of jobs.

I was using the SybaseOCPX stage with "Generated SQL query". However, I didn't have the "database owner" with the table name. Thus instead of using "Owner.dbo.TableName" in the "Table Names", I was using "TableName". This was causing the error.

I ran into same problem... and this helped to solve it in 2 min.

Quoted part is ultimate solution for the the original question!

Thanks..