No login mapping error b/c of Dynamic SQL in PROC

[RSBLaird71]

Not sure if the Subject is really descriptive enough but I tried.

So here is the issue that has completely stumped me...

I am using SQL 2008 and Reporting Services 2008.
The Report is throwing a most confusing error at me:
"Access to the remote server is denied because no login-mapping exists."

I have a report based on a stored procedure that contains Dynamic SQL. Now, like most, I'm not particularly fond of Dynamic SQL, but I just don't know of an alternative method for what I need to do.

Here's a brief explaination of the report and proc, it's confusing so try and stay with me.
PROCEDURE_A runs PROCEDURE_B -- which uses Dynamic SQL to run a list of other procedures, that in turn populate tables that will be utilized for the original PROCEDURE_A to eventually return to the report. Does that make sense?
(The purpose being that the user can be working updating, changing data, then run the report and see immediately the changes they just made.)

This is a snippit of the PROCEDURE where the Dynamic SQL portion is:

DECLARE VR CURSOR STATIC FOR
	SELECT ID, ProgramName FROM MD_ValidationRule WHERE ACTIVE_INDICATOR_LF = 'Y'
	ORDER BY ID
	OPEN VR
	SET @IRC = @@CURSOR_ROWS
	WHILE @ILC <= @IRC
	BEGIN
		FETCH NEXT FROM VR INTO @QID, @SPNAME
		SET @SQL = 'EXEC Site.dbo.' + @SPNAME + ' ' + CHAR(39) + cast(@LID AS VARCHAR(80)) + CHAR(39) +
		', ' + CHAR(39) + CAST(@QID AS VARCHAR(80)) + CHAR(39)
		 EXEC(@SQL)
		/**/
		--PRINT @SQL
		SET @ILC = @ILC + 1
	END
CLOSE VR
DEALLOCATE VR

Anyway, the procedure runs fine and returns the correct data...no errors...in SQL.
However, when I try to run the report with this Dynamic SQL in place, I get that error above...but as soon as I comment out the "EXEC(SQL)" of the procedure and run the procedure and report again, the report has no errors. What is stumping me, is that the Dynamic SQL is not trying to "jump servers" or use a "remote server"...it's all local, so why is it reacting like it's trying to go elsewhere??

I know this is a long explaination...and I truly appreciate anyone taking the time to try to give me a hand with this...I really am at a standstill and have no way around this problem. Thank you so much for your time and any help you can throw my way.

Sincerely,
RSBLaird71

 

[xlbo]

Are any of the SPs that it is calling doing anything on different servers? or even different schemas?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RSBLaird71]

Not that I'm aware, but there is a list of about 30 stored procs that the cursor loops through...and I did not write them, so I guess there is a possibility...I can't believe I didn't think to check each one of them...I will make that check to see and follow up.

Though upon speaking with the person who DID write them, he did not remember any of them needing to do that...I think they are all the same, just being run on each "rule" so I'm afraid that probably won't be it...but one can never tell till you check...so I will, as I said, and follow up later.

Thanks for the thought...It would be great if I can track down something like that, eh? *fingers crossed*

Stay Tuned...

 

[RSBLaird71]

I was wrong...there were more like 70!! *WHEW* And wouldn't you know the 5 that had an OPENSOURCE code that was indeed jumping servers...was on the LAST 5 procedures!

As I said, I did not write the procs, and the person who did, did not indicate that any of them had that type of code involved. So It did not occur to me to go searching THOSE! *smacking forehead*
Thank you so much for the tip...and helping me solve this mystery. Once I made the procedure skip over those that had the OPENSOURCE, the report runs great!!

Sometimes the obvious gets over looked when you've been staring at the problem for hours. *sigh*

Thanks again!
Sincerley,
RSBLaird71

 

[xlbo]

no probs - happy to help

sounds like a nightmare to debug!! Lets just hope it never needs a major overhaul ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question