I've written a quick report in SSRS that looks at the Transcend deposits and links them back to the Tessitura postings to make it easier for my Finance department to reconcile with the bank. The code runs fine from SSMS (on as a sa equivalent) and from inside Visual Studio. It fails with a "Query execution failed for dataset" when I run it from Tessitura.
How do I grant permissions on the TPAU tables so I can reference them from a Tessitura report?
Thanks.
-steve carlock
Hi Steve
Maybe you could go around the back of that problem by linking the servers, and building views (or whatever) in the Tess database that represent tables in the TPAU db. Then if you re-base your reports on them, that will trick Tess into thinking it's reporting from itself (hopefully - I haven't actually done that.). IN that case, the TPAU end would execute with the security context provided by the link, and the tess end would just need normal execute permissions on your proc.
Ken
Ken,
I found a work-around (or maybe it’s really the best solution, I’m not sure). I turned my query into a stored procedure that I created with an WITH EXECUTE AS ‘DBO’ clause and it seems to be doing the trick. (Our Transcend and Tessitura run on the same database so I didn’t have to do any server linking).
-steve
Information Technology Manager
Santa Barbara Center for the Performing Arts
1330 State Street, Suite 101
Santa Barbara, CA 93101
(805) 899-3000 x 111 (phone)
(805) 899-3081 (fax)
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwainSent: Thursday, April 21, 2011 4:02 AMTo: Steve CarlockSubject: Re: [Tessitura Technical Forum] Reporting from the TPAU database
From: Steve Carlock <bounce-stevecarlock1071@tessituranetwork.com>Sent: 4/20/2011 5:22:48 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Unknown said: I turned my query into a stored procedure that I created with an WITH EXECUTE AS ‘DBO’ clause and it seems to be doing the trick.
We basically use this same technique for cross-db reports here, too; for impresario <- Artifax as well as impresario <- TPAU reports.