• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

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.
0
Gotwals
Asked:
Gotwals
  • 3
  • 2
1 Solution
 
mlmccCommented:
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
0
 
GotwalsAuthor Commented:
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.
0
 
mlmccCommented:
I agree with your concern and I wasn't suggesting granting system admin permissions but rather some other level that would automatically provide read permissions in new databases.

Rather than using a temporary database could you have a permanent database with tables that you updated with data and when done deleted all the data?

mlmcc
0
 
GotwalsAuthor Commented:
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
0
 
mlmccCommented:
Glad i could help

mlmcc
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now