Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
SqlSyncScopeDeprovisioning Script Generation Truncating Stored Procedure Names RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I've looked around for a solution to this for a while now but have been unable to find one.

    I'm using Sync Framework 2.1 to bidirectionally sync multiple clients against one master db. After generating a second scope with SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create), the stored procedures are added for the select on each table with a guid tagged onto the end of the procedure name. Some of these table names are long so the procedure name can exceed 100 characters eg.

    ParameterTrackingSetpointBatchRevisionInformationNotes_selectchanges_1e239d0c-bb61-4798-a19b-42f18d76fe3c

    the whole system works and syncs properly but when i attempt to deprovision a scope that references one of these long procedure names i get an exception indicating that there is an unclosed quotation mark.

    I took a look at the script being generated via the ScriptDeprovisionScope() method and it appears that the procedure name is being truncated to a maximum of 100 characters including quote/square bracket.

    The offending line from the script is

    DROP PROCEDURE [ParameterTrackingSetpointBatchRevisionInformationNotes_selectchanges_1e239d0c-bb61-4798-a19b-42f18d; 

    the procedure names in the [scope_config] table are correct, the only time this appears to be an issue is during deprovisioning. Besides altering my schema, is there a workaround for this?

    I'm using SQL 2008 Express R2 if that's relevant.

    Friday, January 11, 2013 9:41 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    I don't think there's  a workaround within the framework for this.

    if am not mistaken, there is a separate problem as well when you have long column names and if you happen to have columns that are almost the same, you will end up with a duplicate column name because they're getting truncated.

    you can script the deprovisioning and alter the scripts yourself or programmatically, but that still requires looking up the full name of the stored proc.

    • Marked as answer by Darcy_B Monday, January 14, 2013 4:27 PM
    Monday, January 14, 2013 12:58 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    I don't think there's  a workaround within the framework for this.

    if am not mistaken, there is a separate problem as well when you have long column names and if you happen to have columns that are almost the same, you will end up with a duplicate column name because they're getting truncated.

    you can script the deprovisioning and alter the scripts yourself or programmatically, but that still requires looking up the full name of the stored proc.

    • Marked as answer by Darcy_B Monday, January 14, 2013 4:27 PM
    Monday, January 14, 2013 12:58 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    It does seem odd that the provision script is generated without issue but fails during the deprovision.

    I decided to generate the script, detect any truncation by pulling in the scope configuration. I then replace all offending entries with the full name listed in the xml configuration. It's not pretty but it works and will not be an issue if this ever gets fixed.

    Monday, January 14, 2013 4:30 PM