asked on

Crystal 9 can't see temporary tables in SQL Server - permissions issue

We are migrating from Crystal 8 to Crystal 9 on a VB6 application. For a number of our reports, we create global temporary tables (##) containing the necessary data. Then we would pass the table name to Crystal for report creation.

In migrating to Crystal 9 - when I pass the new table names to Crystal - Crystal comes back indicating the temp table name could not be found. If I go in as SA afterward and Grant all on the temp table to Public - Crystal will see the table and run normally. I can't figure out a way to create a temp table and grant it Public access so Crystal can see it from within VB.

For some reason when I set the location in the new RDC architecture, Crystal can never see the temp tables. I've put some of our code in below.

-- open the report
Set Report = crxApplication.OpenReport(sRptPath & "PayCodesReport.Rpt", 1)

-- here is where we set the location of the temporary table - and where Crystal bombs out
Report.Database.Tables(1).Location = "tempdb.dbo." & tmpPayCodes

Any solutions would be greatly appreciated. Thanks.

Mike McCracken

Is tempdb a temporary database as well? If not how about granting all users permissions in the database. I am not that familiar with the admin tools but is there a stored procedure that you could run from the application to grant the permissions before calling Crystal?

mlmcc

Gotwals

ASKER

It is a temporary database - but I don't like the idea of granting system admin. permissions to the users for use in tempdb. I am experimenting w a stored procedure to grant permission to temporary tables in tempdb - but I was hoping not to have to do that. I'm wondering if I'm making this more complicated than it needs to be. Thanks for your response.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gotwals

ASKER

Yes - but I'm reluctant to do that for the # of tables involved. In the meantime I have developed a mechanism to now grant permissions in the tempdb on temp tables created. Although it seems a little kludgy to do it, I think I'd rather do that instead of implementing another database. Thanks for the suggestions anyway. - Dennis

Mike McCracken

Glad i could help

mlmcc