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
Sybase error
Moderators: chulett, rschirm, roy
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
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
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
The sql is pretty simpleDSguru2B 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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
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.
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.
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 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.
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
I was able to get rid of them by including statement: 'set rowcount 0' , before tuser defined querydsdev750 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.
Thanks
Arif
I ran into same problem... and this helped to solve it in 2 min.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.
Quoted part is ultimate solution for the the original question!
Thanks..