A recent SQL Server problem I had was to setup a Linked Server from one SQL 2005 server to another, with a couple of specific requirements: The destination server could only be accessed by IP address, but the customer wanted to be able to identify the server by name in SQL code.
This should have been simple, but as I'm blogging about it you may be able to guess that it wasn't quite so straightforward...
It started promisingly. This is how to add a SQL linked server via SQL Management Studio if you can access it by name:
If you want to specify the other parameters, it seemed to make sense to specify 'Other data source', and 'Microsoft OLE DB Provider for SQL Server':
That was easy... till I tried to query the server, which failed with a timeout. Then, when I opended the properties of the Linked Server, the Data Source information had gone:
To get round this, I had to add the linked server via the sp_addlinkedserver system stored procedure, and specify a provider of 'SQLNCLI' (the native SQL client), and not SQLOLEDB (the OleDb provider for SQL Server). The following example shows how to do it.
sp_addlinkedserver
@server = 'REMOTESQL' -- Name used in queries
, @provider = 'SQLNCLI' -- SQL Native Client
, @srvproduct = '' -- Cannot be null, and if 'SQL Server' then you cannot specify the datasrc
, @datasrc = 'SERVER=10.0.0.1' -- IP Address of the server
, @catalog = 'MYDB' -- database name on the server, if required
1 comments:
We really have to figure out how to get your posts on page one of the search engine results. This is great stuff!
Post a Comment