Create linked server with instance name that contains special characters using c#

Question

I have a stored procedure that creates a new linked server.

The problem is that my InstanceName is "my-pc" (contains "-"), and therefore the LinkedServer is not generated correctly. My guess is that there is a problem in the way I pass the parameter from my c# code, but I couldn't find a solution for that.

My procedure is:

CREATE PROCEDURE 
    [test].[createlinkedserver] 
    (
         @InstanceName  AS SYSNAME
    )
AS

BEGIN

    EXECUTE sys.sp_addlinkedserver
        @server     = N'Server' ,
        @srvproduct = N'' ,
        @provider   = N'SQLNCLI' ,
        @datasrc    = [@InstanceName] ,
        @location   = NULL ,
        @provstr    = NULL ,
        @catalog    = NULL



    EXECUTE master.dbo.sp_serveroption
        @server     = N'Server' ,
        @optname    = N'rpc out' ,
        @optvalue   = N'true'



    EXECUTE master.dbo.sp_serveroption
        @server     = N'Server' ,
        @optname    = N'remote proc transaction promotion' ,
        @optvalue   = N'true'


END

and I call the stored procedure from c#:

    DbCommand Command = CovertixDB.Database.GetStoredProcCommand("test.createlinkedserver");
    CovertixDB.Database.AddInParameter(Command, "InstanceName", DbType.String,  instanceName);
    CovertixDB.Database.ExecuteNonQuery(Command);

What error message do you get? Have you double checked to make sure the value of the variable is correct before adding it as a parameter? I would recommend using SQL Profiler to see the text the server is receiving. — Abraham, Feb 19 '13 at 08:58
@Abraham - I don't get an error. The linked server is added, but the catalogs folder is empty (means it is incorrect). If I execute the command from the sql UI, and send [my-pc] as parameter, it works. If I do the same from my c# code and send "[" + InstanceName + "]", it doesn't work (therefore I tried to add [] around [@InstanceName]. — Inbal, Feb 19 '13 at 09:41

score 0 · Answer 1 · answered Feb 19 '13 at 09:11

0

This won't work ! @datasrc = [@InstanceName] Keep it as other parameters:

@datasrc = @InstanceName

Instead change your C# code, just like below:

DbCommand Command = CovertixDB.Database.GetStoredProcCommand("test.createlinkedserver");
CovertixDB.Database.AddInParameter(Command, "InstanceName", DbType.String,  "[" + instanceName + "]");
CovertixDB.Database.ExecuteNonQuery(Command);

answered Feb 19 '13 at 09:11

Karthik D V

906
1
11
19

Thanks for your response. I already tried, but it doesn't work. The linked server is created, but it is not good (the catalogs folder is empty). – Inbal Feb 19 '13 at 09:40

score 0 · Answer 2 · answered Feb 21 '13 at 02:51

You need to know exactly what is being passed across to SQL from the c# application. To go further in your attempt to move the escaping into SQL, try this:

DECLARE @EscapedInstanceName nvarchar(128)
SET @EscapedInstanceName = '[' + @InstanceName + ']'

EXECUTE sys.sp_addlinkedserver
    @server     = @EscapedInstanceName ,
    @srvproduct = N'' ,
    @provider   = N'SQLNCLI' ,
    @datasrc    = @EscapedInstanceName ,
    @location   = NULL ,
    @provstr    = NULL ,
    @catalog    = NULL

Then take a look at the resulting server name. That will tell you what string is going into the DataSource.

score 0 · Answer 3 · answered Feb 21 '13 at 14:39

I changed my c# to:

  DbCommand Command = CovertixDB.Database.GetStoredProcCommand("test.createlinkedserver");
    CovertixDB.Database.AddInParameter(Command, "InstanceName", DbType.AnsiString,  instanceName);
    CovertixDB.Database.ExecuteNonQuery(Command);

I changed row 2 from DbType.String to DbType.AnsiString.

and then it worked.

Actually, I don't really understand why, because c# DbType.String fits to NCHAR, NVARCHAR, while DbType.AnsiString fits CHAR, VARCHAR.

I would expect it to work with DbType.String...

Does anyone has any explanation for this?

Create linked server with instance name that contains special characters using c#

3 Answers3