Re: AAArgh! sp_addlinkedserver giving me fits

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

"DWalker" <none@xxxxxxxx> wrote in message
news:uXkDw8BuHHA.4972@xxxxxxxxxxxxxxxxxxxxxxx
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',@srvproduct=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',@srvproduct=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',@provider=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


.

Relevant Pages

  • Re: !!
    ... I installed sql 2005 compact Edition. ... Everytime I try to connect, iot asks me for provider, a server and a database. ... SQL Server 2005 Compact Edition. ...
    (microsoft.public.vb.general.discussion)
  • Connecting to remote SQL database on the sbs does not work but will work on the workstations
    ... Hello we are soon creating a new system that will require our sql ... database on our websites dedicated server. ... An error has occurred while establishing a connection to the server. ... (provider: TCP Provider, error: 0 - A socket operation ...
    (microsoft.public.windows.server.sbs)
  • SIMPLE and wierd SQL Server 2000 login problem
    ... I have setup the following server: ... The SQL Server service ... Added a new local windows group called "ServerA\SQLAdminGroup" ... Run EXEC sp_grantlogin 'ServerA\SQLAdminGroup' [in SQL under SA ...
    (microsoft.public.sqlserver.setup)
  • RE: OLE DB Provider - how tell to not use cursor internally for jo
    ... from server to client. ... me under SQL 2000 error, because DBE OLE provider use cursor for join tables. ... Under SQL 7.0 it run well, the cursor isn't use for join tables. ...
    (microsoft.public.sqlserver.programming)
  • Re: Woher kommt das Timeout?
    ... Im SQL Query Analyzer (SQL Server 2000). ... Also, ich will einen linked server in MS SQL Server 2000, also brauche ich ... OLE DB-Fehlertrace [OLE/DB Provider 'LCPI.IBProvider.2' ...
    (microsoft.public.de.sqlserver)
Loading