use multiple databases in stored procedure 1

[almaragni]

Hello - I am looking for confirmation help on an issue.
I am trying to report from tables out of 2 separate databases ... creating temp table to pull from one - then insert records from another ... but I am getting error 154 - that the USE command cannot be issued in a SP.


Is it as simple as using a explicit reference to a table ... such as "database1"."dbo"."table1" and "database2"."dbo"."table2"?

any help would be appreciated. thanks.

 

[JayKusch]

From there way I am reading your post, it is not the sytax of your code, which looks fine, but it is the implementation of the "USE" command in the store procedure.

remove the USE 'database' reference and re-execute your report.

Byt the way, the code for your cross DB command should look somewhat like this ...

SELECT a.Field1, a.Field2, b.Field1, b.Field3
FROM  Database1.dbo.Table1 as a
JOIN  Datebase2.dbo.Table3 as b
ON (a.SomeID = b.SomeID)

Thanks

J. Kusch

 

[almaragni]

correct thank you.

as it turns out ... that syntax is correct and the USE command is not allowed.

I had a typo in the SP reference which was the root cause.

thank you for your help - your repsonse validated what i was uncertain about and was very helpful.