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.
GotwalsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.