Help Linking to Remote SQL Server

Last Post 14 Sep 2007 04:30 AM by billthecat0702. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
billthecat0702
New Member
New Member

--
13 Sep 2007 11:15 AM
I am trying to link to a SQL Server machine using sp_addlinkedserver and sp_addlinkedserverlogin. I am doing this sucessfully for two other SQL Server machines currently.

I know the Machine Name and I have double-checked that the account I am using in sp_addlinkedserverlogin has the appropriate access.

The strange this with this one is that the SQL Server instance is actually 'MachineC/BINFORD3000'. that is how it shows up in Enterprise Manager. All of the other SQL Servers that I connect to are just 'MachineA' and 'MachineB' I think the sp_addlinkedserver is choking on the slash, or... I simply do not understand the whole slashy thing.

Can anyone help me out here?

Thanks,

--
The Reportinator
"I'll be back!"
nosepicker
New Member
New Member

--
13 Sep 2007 11:46 AM
For a named instance, a backslash should be used instead of a forward slash. Also, you may have to enclose the server name in brackets ([MachineC\BINFORD3000]) to reference it.
billthecat0702
New Member
New Member

--
14 Sep 2007 04:30 AM
Y'all are awesome! I can't believe I didn't think of that... Yeeks

It took some wrangling, but it is working now... For posterity, here is the solution


--Create a Linked Server to MachineC\BINFORD3000
EXEC sp_addlinkedserver @server = 'BINFORD', @srvproduct = 'SQLServer OLEDB Provider', @provider = 'SQLOLEDB', @datasrc = 'MachineC\BINFORD3000'

-- Add a Login to the Server
EXEC sp_addlinkedsrvlogin 'BINFORD', FALSE, 'sa', 'userlogin', 'password'

-- Make sure the Remote Server can execute Stored Procedures and return the results to you.
Exec sp_serveroption 'BINFORD', 'rpc', TRUE
Exec sp_serveroption 'BINFORD', 'rpc out', TRUE

-- Get the version # of the Power Monitoring Software using SQL Server
Select * From BINFORD.DBName.dbo.version


-- Execute a Stored Procedure - Returns Real Power for all of the SA Breakers for the month of August
EXEC BINFORD.DBName.dbo.uspGetData 'SA%', 'Real Power Total', '08/01/07 00:00:00.000', '08/31/07 23:59:59.997'
You are not authorized to post a reply.

Acceptable Use Policy