Create TEMP TABLE :permission denied

[Former Member]

Hi,

I am new to Crystal reports and trying to access the stored procedure from Nettezza via ODBC connection.The stored procedure returns a dataset and contains a temp table in it.When I try to access stored procedure in Crystal I got the error saying :Failed to retrieve data from the database.CREATE TEMP TABLE:permission denied.

I clearly know that I have the access in Nettezza to create a TEMP TABLE and the credentials used are same.

Can someone let me know the work around for this?

Thanks,

Pujitha

[former_member292966]

Hi Pujitha,

This usually means the account Crystal Reports is using to run the Stored Procedure does not have permissions to create the temp table in the procedure.

Make sure you have the proper permissions. Try running the Stored Procedure in the database with the same Crystal Reports account and see if you get the same message.

Brian

[Former Member]

Hi Brian,

When I tried to return 0, instead of Dataset , error vanished. Means, now I am performing select * from TEMP TABLE separately.

But now I am facing another issue:

Current transaction block Aborted. Must issue ROLLBACK to continue.

Could you please suggest!

[former_member292966]

Hi Pujitha,

Do you know what the actual database is? That would help with the error.

The message you are getting indicates there is still an error and the database has cancelled the transaction and allowing a ROLLBACK to allow you to attempt the transaction again.

So it sounds like there is still something wrong with the TEMP table. Google the error message, "Current transaction block Aborted". Hopefully there's a useful suggestion for the database you are using.

I would have to say, this would be something your database administrator would have to look into.

Good luck,

Brian

[Former Member]

Hi Brian,

I am using Nettezza Database.I found the following statement in SAP website:

Crystal Reports does not support SQL that creates temp table in a Command Object, regardless of the data source used.

I am surprised to see this. If this is true there might be some other work around to return a dataset from a procedure.

Do you have any idea on this?

[former_member292966]

Hi Pujitha,

So it's not a Stored Procedure you're calling, its a procedure in a Command Object. Crystal is designed to be read-only. It can't write back to the database, including temp tables.

The temp table should be called inside the Stored Procedure and have the report attach the stores procedure same as you would a table. This way it's all handled on the database side and not on the client.

I haven't worked with Netezza so I'm not sure how the stored procedure would be handled by Crystal. I'm assuming they have their own ODBC driver. I would have you test the ODBC driver in another program like MS Access to make sure the driver is calling the stored procedure properly as well.

Good luck,

Brian