September 26, 2013 at 10:17 am
Hello all, I'm out of my depth here. Working for a small company that has two small databases on the same box, a 2008 R2 SQL Server that is virtualized. I can attach to either one and write queries but not write one query against both of them at the same time.
I'm reading lots of information. I suspect adding Linked Servers is what is needed, but still am not sure. Perhaps there is a setting that is prohibiting me from querying (In SSMS) across two databases. If someone can provide some guidance I'd appreciate it.
Thanks all!
September 26, 2013 at 10:48 am
SQLKnitter (9/26/2013)
Hello all, I'm out of my depth here. Working for a small company that has two small databases on the same box, a 2008 R2 SQL Server that is virtualized. I can attach to either one and write queries but not write one query against both of them at the same time.I'm reading lots of information. I suspect adding Linked Servers is what is needed, but still am not sure. Perhaps there is a setting that is prohibiting me from querying (In SSMS) across two databases. If someone can provide some guidance I'd appreciate it.
Thanks all!
Assuming these two databases are on the same instance you can use 3 part naming convention for this.
Select [Columns]
From TableA --on the server you are connected to
join [Database].[SchemaName].[TableName] on ...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2013 at 11:33 am
Thank you for your response. That's what I'm using but it doesn't work. I could do it on the servers at my previous job, but not here. I will double check it and come back with the exact message tomorrow, but wanted to thank you for your prompt response.
September 26, 2013 at 11:35 am
Also, I apologize for posting this in the wrong (2005) forum. Is there a way I can move it to 2008 R2?
September 26, 2013 at 11:57 am
SQLKnitter (9/26/2013)
Thank you for your response. That's what I'm using but it doesn't work. I could do it on the servers at my previous job, but not here. I will double check it and come back with the exact message tomorrow, but wanted to thank you for your prompt response.
Are the two databases on the same instance or does the server have two instances of sql?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2013 at 11:57 am
SQLKnitter (9/26/2013)
Also, I apologize for posting this in the wrong (2005) forum. Is there a way I can move it to 2008 R2?
No biggie. There is no way for us mere mortals to move your thread. You could ask the admin to move it but in all honesty what you are doing is the same either way. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2013 at 12:32 pm
I checked with the company that installed the server. He says " the server has two instances of SQL, one database in each."
I hope that is a good clue for us.
September 26, 2013 at 12:57 pm
SQLKnitter (9/26/2013)
I checked with the company that installed the server. He says " the server has two instances of SQL, one database in each."I hope that is a good clue for us.
With 2 instances - you will need a linked server from at least one of the instances to the other instance. Once you have the linked server, you can then use 4-part naming to access the database in the other instance.
Example:
SELECT ...
FROM localdb.dbo.table t
JOIN server.remotedb.dbo.table t1 on t1.key = t.key
WHERE ...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 26, 2013 at 1:14 pm
That points us in the right direction. Thank you!
We're going to try putting up the linked server using the GUI tomorrow morning.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply