August 19, 2009 at 1:02 pm
I need to run a stored procedure from one database (which is working fine), but now I also need it to grab a couple of columns of data from another totally separate database. Is this possible? Or what would be the best way to do this? I need this data to feed into a Report in SSRS.
Thanks
PS I also need the secondary query to filter results (or join) on a value from the primary.
August 19, 2009 at 1:14 pm
Very possible.
Is the separate database on the same server, or a different server?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 19, 2009 at 1:21 pm
Same server - pretty much the same connection, just a different database.
August 19, 2009 at 1:25 pm
Then what you need to do is have a "three-part-name" for the tables you want to query.
If, for example, you have DatabaseA and DatabaseB, and the proc is in DatabaseA, and you want to query Table1 in DatabaseB, looking for ColumnZ, it would look like:
select ColumnZ
from DatabaseB.dbo.Table1;
I'm assuming the table is in schema "dbo" in this case.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 19, 2009 at 5:07 pm
Yes, this does help and I suspected this to be the case. I am not very strong in writing the SQL queries and I really appreciate the help. I am not at work right now, but I will try this first thing in the morning.
August 20, 2009 at 1:08 pm
You're welcome.
We all have to start somewhere.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 22, 2009 at 4:26 pm
Hi, all...
well i was checking the forum and i have another question about this topic, and what's the case if the data base is in other server? thanks in advance
August 22, 2009 at 9:01 pm
Then you want to define a "Linked Server" to that other server and use a four-part name.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 23, 2009 at 5:31 am
This is good information. Thanks
August 23, 2009 at 12:13 pm
Glad I could help...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2009 at 4:46 pm
I just love how guys on this website are willing to help others. Gsquared is obviously a very advanced sql guru but takes the time and patience to answer even the "not so advance" questions posted by people like me. I am sure you guys sleep well at night. Keep it up. You have no idea how many families you are feeding with the help you offer!! Same goes for Lynn Petis, Jeff Moden and many more guys on here (these are just the ones I remember right now)
August 26, 2009 at 1:33 pm
If you must implement a linked server, I highly recommend thoroughly testing your queries first and monitoring performance. With linked servers, you want to be careful with the security, RPC and DCOM (if DCOM comes into play for your scenario). I have seen linked servers that are working fine one day blow up the next and cause max cpu utilization when even a simple proc is run.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply