How can I change the instance name of a SQL Server from C#

Question

How do I call the Stored Procedure sp_addserver from C# with the local switch?

In SSMS I can call add server like this:

    EXEC sp_addserver 'MYNEWSERVERNAME',local

My relevant code as of now:

cmd.CommandText = "sp_addserver";
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("@server", "MYNEWSERVER"));
cmd.ExecuteNonQuery();

This errors out as sp_addserver is not supported without the local switch.

PhillipH · Accepted Answer · 2018-02-01 21:59:29Z

2

sp_addserver supports the formal parameter called "@local".

sp_addserver [ @server = ] 'server' ,
[ @local = ] 'local'
[ , [ @duplicate_ok = ] 'duplicate_OK' ]

So just pass a parameter called 'local' with the value 'local'

cmd.Parameters.Add(new SqlParameter("@local", "local"));

answered Feb 1, 2018 at 21:59

PhillipH

6,2221 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Neil · Accepted Answer · 2018-02-01 21:59:42Z

1

Create your own SP that adds the extra parameters you need and then call that SP from your code.

answered Feb 1, 2018 at 21:59

Neil

12.1k3 gold badges36 silver badges65 bronze badges

I thought about doing that but I don't know quite how at this point. Something I'll have to learn how to do. Thanks @Neil