Changing a linkedserver datasource

Hi all,

I'm trying to change the datasource of a SQL Server LinkedServer using SMO

I've got the relevant linkedserver as an object, changed the DataSource property, and selected it again to confirm the change.
However, the change is lost as soon as the object is destroyed.
Looking at BoL I think I need to be using alter() method of the linkedserver class but I just get an error
Alter failed for LinkedServer '<servername>'.

any ideas how I should be using this class to do what I want?

I can post my script (PowerShell) if it would help.

Thanks, Robin.

Hi Robin,

The DataSource property of an existing LinkedServer cannot be modified. To modify the DataSource, you need to drop and re-create the linked server.

Thanks,

Kuntal

|||Hi Kuntal,
Could you demonstrate how I would do this in code (any language)
I have tried various ways but either manage to build the linkedserver object with the properties of the old server and then get an error when i try to create it, or i can create it but then not set the properties.

Many thanks,

Robin.
|||

Hi Robin,

Here is a small sample which might be of some help.

Server server = new Server("localhost");

LinkedServer lserver = new LinkedServer(server, "testServer");

lserver.DataSource = "Server1";

lserver.ProductName = "SQLNCLI";

lserver.ProviderName = "SQLNCLI";

lserver.Create();

// now you need to change the datasource and keep the rest of properties the same

lserver.DataSource = "Server2";

StringCollection script = lserver.Script();

lserver.Drop();

server.ConnectionContext.ExecuteNonQuery(script);

The above code first creates a linked server and then executes the script to create the same with a different datasource. Now you can create a new linked server by running that script after dropping the original server.

Hope that helps.

Thanks,

Kuntal

|||Many thanks, that's brilliant!

So just to check I'm understanding correctly, another way to express your solution would be, assuming there was a linked server called TESTLS already defined:

Server server = new Server("localhost");

LinkedServer lserver = server.linkedservers.item("TESTLS")

// now you need to change the datasource and keep the rest of properties the same

lserver.DataSource = "Server2";

StringCollection script = lserver.Script();

lserver.Drop();

server.ConnectionContext.ExecuteNonQuery(script);