I was setting up a linked server from my web database to my local database to ease the backup procedure. Currently I have a script which I run to copy all of the database from the web database to my local database. I thought I could simply use sp_addlinkedserver to conect the SQL Servers. My initial attempts to connect the servers was rebuffed:

-- Try 1 (failed)
EXEC sp_addlinkedserver 
     @server        = 'subtext_web'
    ,@srvproduct    = 'SQL Server'
    ,@datasrc       = 'some.sql-web-host.com'
    ,@catalog       = 'some-db'

-- Results in the following error message:
--   Msg 15426, Level 16, State 1, Procedure sp_addlinkedserver, Line 20
--   You must specify a provider name with this set of properties.
GO


-- Try 2 (failed)
EXEC sp_addlinkedserver 
     @server        = 'subtext_web'
    ,@srvproduct    = 'SQL Server'
    ,@provider      = 'SQLNCLI'
    ,@datasrc       = 'some.sql-web-host.com'
    ,@catalog       = 'some-db'

-- Results in the following error message:
--   Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 37
--   You cannot specify a provider or any properties for product 'SQL Server'.
GO


-- Try 3 (succeeded)
EXEC sp_addlinkedserver 
     @server        = 'subtext_web'
    ,@srvproduct    = ''
    ,@provider      = 'SQLNCLI'
    ,@datasrc       = 'some.sql-web-host.com'
    ,@catalog       = 'some-db'

-- Results in the following message:
--   Command(s) completed successfully.
GO

I read the documentation on sp_addlinkedserver and there was a footnote on the use of @srvproduct = 'SQL Server':

This way of setting up a linked server forces the name of the linked server to be the same as the network name of the remote instance of SQL Server. Use data_source to specify the server.

Since I was successful in Try 3, it wasn't a big deal but the error messages in Try 1 and Try 2 seemed to contradict each other since the error message in Try 1 specifically required a provider.