September 19, 2011 at 1:58 pm
Hello,
I have a stored procedure that lives in DB1 joins tables from DB1 and DB2. The stored proc works great when I run it in management studio.
However, when I call it from SSRS report it returns blank.
in SSRS I set up a datasource to DB1 (where my stored proc lives) and loaded my dataset.
My guess is that ssrs doesn't like it that i don't have datasource going to DB2? But why should I, since the logic is in stored proc.
Anyone run into this?
September 19, 2011 at 2:28 pm
Does the user that connects your datasource in SSRS have adequate permissions to both databases?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 2:37 pm
This might have to do with cross database ownership chaining and the SSRS login not having permissions, as SQLRNNR mentioned above. I'd usually expect an error in that case, not a blank however. I'd have to goof off and test it to figure out if that's the particular issue, but in the meanwhile you might want to look into the security.
The other option I could see is connecting to one of the db's via a restricted view, where there's a where clause on it such as ColumnH = Original_Login() or somesuch. Try logging into SQL Server as the SSRS server login and then retest the proc output, it'll at least help nail down where the issue is.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply