April 4, 2011 at 8:43 am
Hi,
I need help. I want to connect to a remote SQL Server. So, can someone exactly describe the T-SQL scripts needed for this?
Thank you!
April 4, 2011 at 9:07 am
First you need to create a linked server. This is easiest to do through the GUI but you can also use sp_addlinkedserver (you may want to check "Data Access" in the Server Options :))
Ex:
EXEC master.dbo.sp_addlinkedserver @server = N'MyServer', @srvproduct=N'SQL Server'
-- Create a login
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'myreader',@rmtpassword='########'
-- All data access
EXEC master.dbo.sp_serveroption @server=N'MyServer', @optname=N'data access', @optvalue=N'true'
GO
Then you need to query the server itself (there are 2 ways):
1. Query the server directly:
SELECT COUNT(1) FROM [MyLinkedServer].MyDatabase.dbo.MyReportTable
2. Use OPENQUERY and allow the originating server to pass the query off to the linked server to parse/execute:
SELECT COUNT(1) FROM OPENQUERY ([MyLinkedServer], 'SELECT 1 FROM MyDatabase.dbo.MyReportTable')
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 4, 2011 at 9:08 am
asterman_3 (4/4/2011)
Hi,I need help. I want to connect to a remote SQL Server. So, can someone exactly describe the T-SQL scripts needed for this?
Thank you!
One method is to create a linked server connection. A linked server is basically a named database object that contains the login credentials for an account on the remote database.
However, avoid using 4 part named selects that join remote tables like below, because that can result pulling table scans across the wire and very poorly performing queries:
select a, b, c
from mytable t1
join linkedservername.somedb.someschema.sometable t2 on t2.a = t1.a
where t2.b = 100;
Instead use the EXEC(..) AT .. syntax to execute a passthrough query and bring the result into a local temporary table, which can then be joined with the local table:
insert into #t2 ( a, b, c)
EXEC('select a, b, c from sometable') at LINKEDSERVERNAME;
select a, b, c
from mytable t1
join #t2 on #t2.a = t1.a
where t2.b = 100;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 4, 2011 at 9:09 am
Thank you for the detailed explanation. It works!
I have another question.
How can I CREATE a PROCEDURE on the remote sql server?
April 4, 2011 at 9:15 am
asterman_3 (4/4/2011)
Thank you for the detailed explanation. It works!I have another question.
How can I CREATE a PROCEDURE on the remote sql server?
You should create a procedure on the remote database using the same process you would use to create a procedure on the local database. If the remote account you're using for the linked server connection has DBO or SYSADMIN privillages, then I guess you could technically use the EXEC() AT command to execute a CREATE PROCEDURE statement on the remote database. However, there must be a DBA owning the remote database. If that's not you, then you'll want to coordinate with them on properly creating the procedure.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 4, 2011 at 9:20 am
Actually, I want to know whether it is possible to create a procedure from T-SQL on remote server, and than execute. Or, to create a SQL Job remotely (on the remote server)?
April 4, 2011 at 9:37 am
creating a procedure or creating a job is just executing a T-SQL batch. It can be more complicated in terms of permissions and you will have to resolve that, but the same techniques apply.
Do you need to do this in T-SQL, or can you just make a remote connection? What is the need to require this in T-SQL?
April 4, 2011 at 9:38 am
asterman_3 (4/4/2011)
Actually, I want to know whether it is possible to create a procedure from T-SQL on remote server, and than execute. Or, to create a SQL Job remotely (on the remote server)?
Yes, using the EXEC(..) AT.. method I described above, you can execute a CREATE PROCEDURE or CREATE JOB statement on the remote database. What you would be doing is executing the statement remotely on the database. This would work only the the DBA who created the remote login account you're using also granted sufficient permission to create objects there. Typically, a DBA will grant an account used for remote linked server connections to have only db_datareader membership or EXEC permission on specific stored procedures. If I were the DBA on that database, I wouldn't want remote users to be using the linked server connection to create objects in the databse I own, so I wouldn't be surprised if your login has only read access.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply