January 4, 2009 at 7:42 am
I'm having problems creating a linked server on my local SQL 2005 to a remote shared SQL Server. I need to be able to read and copy data to the remote SQL Server from an SP on my local SQL.
E.g. SELECT * FROM [LinkedServer].[dbName].[dbo].[tableName]
The access details for the remote SQL Server are:
remoteServerIP
dbName
remoteUserName
remotePassword
I think the following SPs are needed but I know they are not quite right as the remoteServerIP is not being specified.
EXEC sp_addlinkedserver
'LinkedServer',
N'SQL Server'
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'LinkedServer',
@useself = 'false',
@rmtuser = 'remoteUserName',
@rmtpassword = 'remotePassword'
Any help would be much appreciated.
Thanks
January 4, 2009 at 8:35 am
Can you provide the error that you are getting? It would be helpful when anyone tries to find the error. In any case I think you should also configure your linked server to allow data access. You can do it with sp_serveroption stored procedure (more details can be found in BOL).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 5, 2009 at 4:47 am
Thanks for your help Adi. It gave me a little more info to work with and I now have a solution.
EXEC sp_addlinkedserver
@server = N'linkedServer',
@srvproduct=N'19.12.xx.xx',
@provider=N'SQLOLEDB',
@datasrc=N'19.12.xx.xx',
@catalog=N'catalogName'
EXEC sp_serveroption
@server=N'linkedServer',
@optname=N'data access',
@optvalue=N'true'
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'linkedServer',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'remoteUserName',
@rmtpassword = N'remotePassword'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply