locked
Incorrect dynamic sql generated RRS feed

  • Question

  • For the life of me I do not understand why @MiddleName and @Notes were being declared twice. Is this a bug?

    I'm using sync service v1.0.


    //Apply inserts to the server.
    SqlCommand customerInserts = new SqlCommand();
    customerInserts.CommandText =
    @"INSERT INTO Customer (ID, FirstName, LastName,MiddleName, Notes, InsertId, UpdateId)
    VALUES (@ID, @FirstName, @LastName,@MiddleName, @Notes, @sync_client_id, @sync_client_id)
    SET @sync_row_count = @@rowcount";
    customerInserts.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);
    customerInserts.Parameters.Add("@FirstName", SqlDbType.NVarChar);
    customerInserts.Parameters.Add("@LastName", SqlDbType.NVarChar);
    customerInserts.Parameters.Add("@MiddleName", SqlDbType.NVarChar);
    customerInserts.Parameters.Add("@Notes", SqlDbType.NVarChar);
    customerInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
    customerInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
    customerInserts.Connection = serverConn;
    customerSyncAdapter.InsertCommand = customerInserts;


    generates the following sql in the SQL Profiler, thus erroring out my app:

    exec sp_executesql N'INSERT INTO Customer (ID, FirstName, LastName,MiddleName, Notes, InsertId, UpdateId)
    VALUES (@ID, @FirstName, @LastName,@MiddleName, @Notes, @sync_client_id, @sync_client_id)
    SET @sync_row_count = @@rowcount',
    N'
    @ID uniqueidentifier,
    @FirstName nvarchar(3),
    @LastName nvarchar(4),
    @MiddleName nvarchar(1),
    @Notes nvarchar(4),
    @sync_client_id uniqueidentifier,@sync_row_count int,
    @MiddleName nvarchar(4000),
    @Notes nvarchar(4000)',
    @ID='D4424FA6-DF7C-4763-BDAF-6CCBB4A1F79D',
    @FirstName=N'Wei',
    @LastName=N'Liao',
    @MiddleName=N'X',
    @Notes=N'test',
    @sync_client_id='E5B2BEC3-BC6F-4D26-BF44-DEA692A631C2',
    @sync_row_count=0,
    @MiddleName=default,
    @Notes=default

    • Moved by Max Wang_1983 Friday, April 22, 2011 8:47 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, February 1, 2008 4:21 PM

Answers

  • I spent the whole day chasing this down, it turned out to be a typo I made on the name of the command..good grieve..
    Sunday, February 3, 2008 9:25 PM

All replies

  • doesn't seem to be a bug to me. you saw two variables in the profiler due the way sp_executesql works. what error did you get if you simply ran the query ( the one copy/paste from profiler) ?

     

    thanks

    Yunwen

     

    Friday, February 1, 2008 6:14 PM
    Moderator
  • If I run the query it returns:

    Msg 134, Level 15, State 1, Line 8
    The variable name '@MiddleName' has already been declared. Variable names must be unique within a query batch or stored procedure.

    Which is what's expected, if I removed the @MiddleName declaration, it'll say the same thing about @Notes being already declared, if I remove them both it ran fine.

    wliao


    Friday, February 1, 2008 7:19 PM
  • I spent the whole day chasing this down, it turned out to be a typo I made on the name of the command..good grieve..
    Sunday, February 3, 2008 9:25 PM
  • well, glad it was resolved.

     

    Cheers

     

    Yunwen

     

    Wednesday, February 6, 2008 2:42 AM
    Moderator