linked server to another SQL SERVER via an aliased name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kevin - 14 Oct 2005 15:15 GMT

using SqlServer 2K
I want to create a linked server to another SQL SERVER 2K box, but I do not
want to use the actual server name as @server as this is dev and I have no
idea what the final server name will be. I would prefer to use an alias thus
making my SP portable.

per BOL(Transact-SQL Reference - System Store Procedures -
sp_addlinkedserver) I should be able to do this and even copied this from
example A2, somewhat :).

Can I do this, or is BOL a big fat lier?

This
EXEC sp_addlinkedserver
    @server='TP4',
    @srvproduct='SQL Server',
    @provider = 'SQLOLEDB',
    @datasrc = 'LV3KDEV02',
    @catalog = 'TruePayV3_0_Migration'
returns this error
Server: Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
You cannot specify a provider or any properties for product 'SQL Server'.

Reply to this Message

Jasper Smith - 14 Oct 2005 20:03 GMT

If you specify @srvproduct='SQL Server' then this message is correct however
you can still use an alias by setting up a client alias on the server using
the Client Network Utility (Start>Run>cliconfg). You can then use that as
the @server parameter.

Signature

HTH

Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

> using SqlServer 2K
> I want to create a linked server to another SQL SERVER 2K box, but I do
[quoted text clipped - 21 lines]
> 67
> You cannot specify a provider or any properties for product 'SQL Server'.

Reply to this Message