Modify (alter) fields in sys.servers?

[VicM]

I used sp_addlinkedservers to add another server. Unfortunately I forgot to use the @provstr since the server I added requires SQL Login.

The system will not allow me to delete the record for the linked server I just added. Is there a way to add the @provstr to the existing entry in the sys.server view?

Thanks,
Vic

 

[mrdenny]

SQL won't allow you to edit the DMVs directly. What error does it give you when you try and delete the linked server?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[VicM]

Denny,
Thanks for responding.

Actually I discovered the sp_dropserver stored procedure which removes the server from the sys.server view.

Since the DBSERVER\My_SQL database requires SQL login, I attempted to re-add the linked table with the following script:

sp_addlinkedserver 
@server = 'DBSERVER\My_SQL',
@srvproduct = '',
@provider = 'SQLNCLI',
@datasrc = 'DBSERVER\My_SQL',
@provstr = 'DRIVER={SQL Server};SERVER=DBSERVER\My_SQL;UID=sa;PWD=sapassword;'

which executed successfully.

However, when I attempted to run a script to read data from a table on the DBSERVER\My_SQL (remote) server, I got the following error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'Domain\User'.
OLE DB provider "SQLNCLI" for linked server "DBSERVER\My_SQL" returned message "Invalid connection string attribute".

When I ran the sp_dropserver procedure without the @provider = 'SQLNCLI' line, I got the following message when attempting to read from a table on the remote server:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "" has not been registered.

Any further suggestions?
Vic