AAArgh! sp_addlinkedserver giving me fits

I seem to be getting conflicting error messages from sp_addlinkedserver.
Here is the problem:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.srv
product=N'SQL Server'
gives me this:
Msg 15426, Level 16, State 1, Procedure sp_addlinkedserver, Line 44
You must specify a provider name with this set of properties.
OK, I'll specify a provider name:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.srv
product=N'SQL Server',
@.provider=N'SQLNCLI'
gives me this:
Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
You cannot specify a provider or any properties for product 'SQL
Server'.
So I *must* specify a provider, and yet I *cannot* specify a provider?
OK, I'll take out the product name:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.pro
vider=N'SQLNCLI'
gives:
Msg 15429, Level 16, State 1, Procedure sp_addlinkedserver, Line 72
'(null)' is an invalid product name.
So null is an invalid product name if the provider is SQLNCLI. (I also
tried SQLOLEDB since the server is SQL 2000.) That goes against the
sp_addlinkedserver doc in BOL, which shows in the third "row" of the
table under "Remarks" that for a specific instance, the product name
doesn't need to be provided.
If the doc is right, then why the error message 15429?
What gives here?
The underlying problem is that I am trying to set up a kind of alias for
a linked server, so that from the production server, I can get some data
from another (linked) production server; from the test server, I can get
the data from the first production server. The test server can't see
the second production server.
Maybe there is a better way to do this... but I don't see it. (I don't
have access to set up a real alias in the client network utility on the
production server.)
The target databases are SQL 2000.
Thanks for any help.
David WalkerDavid,
Here is what I do.
EXEC master.dbo.sp_addlinkedserver @.server = N'RamaLama',
@.srvproduct=N'SrvrName', @.provider=N'SQLOLEDB', @.datasrc=N'SrvrName',
@.catalog=N'databasename'
Note that I have the @.srvproduct and the @.datasrc set to the same value.
Once this resolved my problem, I did not keep fooling with it to see if
there was another way.
RLF
"DWalker" <none@.none.com> wrote in message
news:uXkDw8BuHHA.4972@.TK2MSFTNGP05.phx.gbl...
>I seem to be getting conflicting error messages from sp_addlinkedserver.
> Here is the problem:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.srv
product=N'SQL Server'
> gives me this:
> Msg 15426, Level 16, State 1, Procedure sp_addlinkedserver, Line 44
> You must specify a provider name with this set of properties.
> OK, I'll specify a provider name:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.srv
product=N'SQL Server',
> @.provider=N'SQLNCLI'
> gives me this:
> Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
> You cannot specify a provider or any properties for product 'SQL
> Server'.
> So I *must* specify a provider, and yet I *cannot* specify a provider?
> OK, I'll take out the product name:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.pro
vider=N'SQLNCLI'
> gives:
> Msg 15429, Level 16, State 1, Procedure sp_addlinkedserver, Line 72
> '(null)' is an invalid product name.
> So null is an invalid product name if the provider is SQLNCLI. (I also
> tried SQLOLEDB since the server is SQL 2000.) That goes against the
> sp_addlinkedserver doc in BOL, which shows in the third "row" of the
> table under "Remarks" that for a specific instance, the product name
> doesn't need to be provided.
> If the doc is right, then why the error message 15429?
> What gives here?
> The underlying problem is that I am trying to set up a kind of alias for
> a linked server, so that from the production server, I can get some data
> from another (linked) production server; from the test server, I can get
> the data from the first production server. The test server can't see
> the second production server.
> Maybe there is a better way to do this... but I don't see it. (I don't
> have access to set up a real alias in the client network utility on the
> production server.)
> The target databases are SQL 2000.
> Thanks for any help.
> David Walker|||"Russell Fields" <russellfields@.nomail.com> wrote in news:#dNp7kCuHHA.576
@.TK2MSFTNGP03.phx.gbl:

> David,
> Here is what I do.
> EXEC master.dbo.sp_addlinkedserver @.server = N'RamaLama',
> @.srvproduct=N'SrvrName', @.provider=N'SQLOLEDB', @.datasrc=N'SrvrName',
> @.catalog=N'databasename'
> Note that I have the @.srvproduct and the @.datasrc set to the same value.
> Once this resolved my problem, I did not keep fooling with it to see if
> there was another way.
> RLF
But I need for the data source to have an instance name in one server, and
none in another server. @.srvproduct is supposed to be "SQL Server" (or
"Any" for some provider names).
I'm not sure how you got away with giving a server NAME as the name of the
product.
David|||Hello David,
You may want to try the following statement to see if it works on your side:
EXEC sp_addlinkedserver
@.server='linkedservername',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='servername\instancename'
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||David,
Did Peter Yang's answer resolve things for you? Blank is good, but
@.srvproduct is whatever you say it is.
If, however, you say it is 'SQL Server', you are predefining that the
@.server must be a physical name (or instance name) of the server that you
are trying to reach. Since you want @.server to be an alias (an excellent
idea for deploying from a development to a production environment) then you
will user a provider. Since you are SQL 2000, I used SQLOLEDB.
Out of curiousity, did you try it?
RLF
"DWalker" <none@.none.com> wrote in message
news:%231JXBLPuHHA.2752@.TK2MSFTNGP06.phx.gbl...
> "Russell Fields" <russellfields@.nomail.com> wrote in news:#dNp7kCuHHA.576
> @.TK2MSFTNGP03.phx.gbl:
>
> But I need for the data source to have an instance name in one server, and
> none in another server. @.srvproduct is supposed to be "SQL Server" (or
> "Any" for some provider names).
> I'm not sure how you got away with giving a server NAME as the name of the
> product.
> David
>|||"Russell Fields" <russellfields@.nomail.com> wrote in
news:udbmMvYuHHA.4540@.TK2MSFTNGP05.phx.gbl:

> David,
> Did Peter Yang's answer resolve things for you? Blank is good, but
> @.srvproduct is whatever you say it is.
> If, however, you say it is 'SQL Server', you are predefining that the
> @.server must be a physical name (or instance name) of the server that
> you are trying to reach. Since you want @.server to be an alias (an
> excellent idea for deploying from a development to a production
> environment) then you will user a provider. Since you are SQL 2000, I
> used SQLOLEDB.
> Out of curiousity, did you try it?
> RLF
Sorry, I was out for a while.
@.srvproduct can be anything, but has to be supplied, and if it's "SQL
Server" then it has to be a real name.... hmmmm...
I find that the only way I can connect to a remote SQL 2000 server from
SQL 2005 management studio is to use Peter's example, and supply '' or
'test' or something like that for the server name. If I use 'SQL
Server', then I get a "logon failed for user xyz" (message 18456).
I'll use 'sql' for the product name.
Thanks to you both, including Peter.
David Walker