Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SLM09
Starting Member
31 Posts |
 Posted - 2009-09-30 : 13:32:49
|
| Simple question- is it possible to change the server you are connected to within SQL code? I have tried using "USE" with the full server/DB path, but SQL server 2005 didn't like that to much.. I haven't had much luck searching for an answer, so sorry if this is brain dead.I am hoping to find something like-USE server/DB name='xxx' pw='xxx'Select ...USE server2/DB2 name='xxx' pw='xxx'Select .......Thanks in advance for any help. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-09-30 : 13:37:05
|
| If the server is linked, you can reference it with a 4 part naming conventionselect stuff from [ServerName].[databasename].[owner].tableJimEveryday I learn something that somebody else already knew |
 |
|
|
SLM09
Starting Member
31 Posts |
Posted - 2009-09-30 : 13:55:26
|
| Forgive my ignorance, but by "linked" do you mean simply connected to the DB, or is there some additional setting for linking the servers? The goal here is to be able to have queries written and rather than manually changing the server for each one, being able to execute the query one after another and just let SQL Server do all the jumping around. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-09-30 : 14:21:13
|
| You actually add the linked server to the one you're operating from, in SSMS under Server Objects\Linked Servers. Can you post a sample of the actual code?JimEveryday I learn something that somebody else already knew |
|
|
|
SLM09
Starting Member
31 Posts |
Posted - 2009-09-30 : 14:41:06
|
| At the moment, this is more of an open-ended issue rather than a specific one. I want to know how to do it, but don't have anything specific atm that "needs" it. The example I gave above is basically the concept. One query page that can be run and switches servers for each query when needed.I looked into the server link options, and looks like I don't have access as just a user (hopefully will change in the future). For now, I appreciate the info pointing me in the right direction. Gonna' guess there aren't any ways to do what I want from a user standing eh? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-09-30 : 14:46:34
|
| Nope. You'll have to talk to your sa about addding linked servers. The only way I can see doing it from a suer standpoint is by having a front-end that can decide which connection string to use.JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
Changing Server within SQL code