Prev: Error setting Maintenance Plan
Next: Deprecated features/breaking changes in sql server 2008 r2?
From: Mike on 16 Mar 2010 17:34
We are trying to add a linked server on a SQL Server 2005 box. On all of our
other boxes we have a list of providers. But on one particular server there
are none. How do we add providers to the server. I do not want to reinstall
because this is a production server. Any ideas?
From: Erland Sommarskog on 16 Mar 2010 17:53
Mike (Mike(a)discussions.microsoft.com) writes:
> We are trying to add a linked server on a SQL Server 2005 box. On all
> of our other boxes we have a list of providers. But on one particular
> server there are none. How do we add providers to the server. I do not
> want to reinstall because this is a production server. Any ideas?

If you want to add a linked server to another SQL Server instance, and
make no extra thrills, it's very simple:

exec sp_addlinkedserver 'THATSERVEROVERTHERE'

If you need a linked server to another type of data source, you will
also need to specify the provider, which is the third parameter. The
second parameter can usally be left blank (not NULL!). For instance:

exec sp_addlinkedserver 'ORACLESVR', '', 'MSDAORA'

(Although you should probably not use MSDAORA to connect to Oracle, but
Oracle's own provider.)

You can use sp_addlinkedsvrlogin to set up login mapping, and
sp_serveroption to set server options.

Now, if you absolutely refuse to read Books Online, and you really must
point and click, have no fear. Just tune into one of the servers where
you can see the providers, and fill in everything you need. Then
press Script, and the definition will open in a new query window. Then
in that window right-click and select Change Connection from the
Connection submenu. Et voil�!

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Error setting Maintenance Plan
Next: Deprecated features/breaking changes in sql server 2008 r2?