Is it possible to programmatically create a DBLink in SQL server 2005 in C#?

Question

Is it possible to programmatically create a DBLink in SQL server 2005 in C#?

Suppose I have database A and B. I want to create a DBlink in A to connect B. I will capture the B database info from user and create the DBLink in database A. Is this possible in C# .Net version 2.0?

Answer 1

You can add a linked server with sp_addlinkedserver:

EXEC sp_addlinkedserver
    @server = 'OracleHost',
    @srvproduct = 'Oracle',
    @provider = 'MSDAORA',
    @datasrc = 'MyServer'

From C#, you can store this query in a SqlCommand, and call ExecuteNonQuery() to execute it on the database.

Answer 2

What you wanna do is make a Stored Procedure that does this and then call it from C#

Make the following stored procedure:

Create PROCEDURE [dbo].[LinkMyServer]

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

EXEC sp_addlinkedserver @server = N'LinkName',
@srvproduct = N' ',
@provider = N'SQLOLEDB', 
@datasrc = N'some.domain.or.ip.com', 
@catalog = N'database_name'

EXEC sp_addlinkedsrvlogin N'LinkName', false, N'ServerDomain\Administrator', N'user_on_remotedb', N'password_on_remote_db'

END

Now the remote db has been linked to the Local User Administrator.

Now in C# you just make a SqlCommand and and set type to stored procedure and execute a non query :)

ServerDomain\Administrator could also just be a sql user like 'dbo'.

Hope this helped.