quering remote database using sql server?
Solution 1
The "nice" format you mention is simply a 4 part object reference.
select * from [server\instance].database.owner.tablename
3 part
select * from database.owner.tablename
2 part
select * from owner.tablename
If you want to dynamically change any of the server, db or schema values then you have one option:
EXEC (@sqlstring)
However, if you only access stored procs remotely...
DECLARE @RemoteSP varchar(500)
SET @RemoteSP = '[server\instance].database2.schema.proc2'
EXEC @RemoteSP @p1, @p2, @p3 OUTPUT
SET @RemoteSP = '[server\instance].database1.schema.proc1'
EXEC @RemoteSP @p4, @p5, @p6 OUTPUT
However, changing the components of the object reference makes no sense arguably: if you know you're going to query a table then just call that table in that database...
Solution 2
you should make a query string and then run it by exec()
function.
getting server name :
SELECT @@SERVERNAME
getting current db name :
SELECT DB_NAME() AS DataBaseName
![Anil Namde](https://i.stack.imgur.com/yiKrN.jpg?s=256&g=1)
Comments
-
Anil Namde over 3 years
I have used sp_addlinkedserver to access the remote machines db now i am writing queries explicitly on database like,
select * from [server\instance].database.owner.tablename
Now with this,
- [Server\instance] : this has to be provided explicitly
- [database] : can we find databases on specified instance using query like ms_ForEachDB ?
- [owner] : Can we find the database owner name using query ?
If these values are found using queries do we need to use EXEC() to execute this or we can still achieve it using nice queries ?
Thanks all,
-
Francesco Mantovani almost 3 yearsDo I need linked server to run such query?