Red Gate forums :: View topic - The system didn’t see the changes.
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 3
SQL Source Control 3 forum

The system didn’t see the changes.

Search in SQL Source Control 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
fadeev_av



Joined: 15 Oct 2013
Posts: 5

PostPosted: Tue Oct 15, 2013 2:04 pm    Post subject: The system didn’t see the changes. Reply with quote

In first data entering in the table (static data) the system can not track the changes.
Turned out that the problem arose because of the presence multi-values (varchar) in the generated script. After manual removal from this files all such lines, everything works fine. Is it a bug? Or explain me how I can fix this situation.
There is an example below:

Code:

INSERT INTO [dbo].[MX_WF_ObjectStructure] ([ID_WF_Object], [ID_Object_Type], [Parent_ID], [NameRU], [NameEN], [Table_Name], [PK_Field], [ID_WF_UsingObject], [Edit_Field], [Field_Type], [FK_Field], [WhereClause], [Field_List], [Value_List], [Get_Method], [Set_Method], [ID_Journal], [JournalPK], [JournalField], [ID_LogicType], [Default_Value], [Edit_Mask], [Visible_Condition], [Enabled_Condition], [Required_Condition], [Dictionary_Condition], [Constraint_Condition], [Constraint_Message], [ID_Sheet], [IsPublished], [OrderNum], [Display_Name], [Display_Rows], [CreateDate], [ID_User], [UpdateDate], [Note], [Filter_Condition], [Use_In_Templates], [Editing_Condition], [Filter_Func], [ID_Form], [FormScript], [ID_WF_ImportObject], [Import_Method], [ShowInFilter], [Colored], [DisplayButton], [ClearIfNotVisible], [UseInGroupUpdate], [Display_Name_ENU], [ColumnNumber]) VALUES (20, 3003, 15, N'Продукт', N'Product', N'TD_Client_Products', N'ID_Client_Product', NULL, N'ID_Product', N'int', NULL, NULL, NULL, NULL, NULL, NULL, 101075, N'ID Продукта', N'Продукт', 4005, NULL, NULL, N'[ТипПродукта] <> ''Пластиковая карта'' or [ПризнакПрорабатываемого] = 1', NULL, N'1', NULL, N'[Продукт] <> 14962805 AND [Продукт] NOT IN
(SELECT
  P1.ID_Product
FROM
  dbo.TD_Products AS P1
  INNER JOIN dbo.TD_Products AS P2
    ON P1.Parent_ID = P2.ID_Product
WHERE
  P2.Parent_ID IN (14947962, 14951205)
)', N'Выбран недопустимый продукт!', 10, 1, 0, N'Продукт', 1, '2007-09-25 11:57:25.967', 100689, '2009-05-27 10:58:31.000', NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL)
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1121
Location: My desk.

PostPosted: Mon Oct 21, 2013 12:56 pm    Post subject: Reply with quote

I'm afraid I'm not really sure what the problem is here. Do you get an error message? Please can you send over a table definition and insert statement so we can replicate the problem?
It might well be a bug with extended character-sets but I've not heard of it happening before so I'd like to see if I can reproduce the problem.
Back to top
View user's profile Send private message
fadeev_av



Joined: 15 Oct 2013
Posts: 5

PostPosted: Tue Nov 05, 2013 2:39 pm    Post subject: Reply with quote

Create table sample:
Code:

CREATE TABLE MX_Developer.dbo.MX_WF_ObjectStructure(
   ID_WF_Object INT NOT NULL,
   ID_Object_Type INT NULL,
   Parent_ID INT NULL,
   NameRU VARCHAR(100) NOT NULL,
   NameEN VARCHAR(100) NOT NULL,
   Table_Name VARCHAR(128) NULL,
   PK_Field VARCHAR(128) NULL,
   ID_WF_UsingObject INT NULL,
   Edit_Field VARCHAR(128) NULL,
   Field_Type VARCHAR(128) NULL,
   FK_Field VARCHAR(128) NULL,
   WhereClause TEXT NULL,
   Field_List TEXT NULL,
   Value_List TEXT NULL,
   Get_Method VARCHAR(500) NULL,
   Set_Method VARCHAR(500) NULL,
   ID_Journal INT NULL,
   JournalPK VARCHAR(128) NULL,
   JournalField VARCHAR(128) NULL,
   ID_LogicType INT NULL,
   Default_Value TEXT NULL,
   Edit_Mask VARCHAR(150) NULL,
   Visible_Condition TEXT NULL,
   Enabled_Condition TEXT NULL,
   Required_Condition TEXT NULL,
   Dictionary_Condition TEXT NULL,
   Constraint_Condition TEXT NULL,
   Constraint_Message TEXT NULL,
   ID_Sheet INT NULL,
   IsPublished BIT NULL,
   OrderNum FLOAT NULL,
   Display_Name VARCHAR(150) NULL,
   Display_Rows INT NULL CONSTRAINT DF_MX_WF_ObjectStructure_Display_Rows DEFAULT (1),
   CreateDate DATETIME NOT NULL CONSTRAINT DF__MX_WF_Obj__Creat__1E56154F DEFAULT (getdate()),
   ID_User INT NOT NULL,
   UpdateDate DATETIME NULL,
   Note TEXT NULL,
   Filter_Condition TEXT NULL,
   Use_In_Templates BIT NULL,
   Editing_Condition TEXT NULL,
   Filter_Func VARCHAR(500) NULL,
   ID_Form INT NULL,
   FormScript VARCHAR(MAX) NULL,
   ID_WF_ImportObject INT NULL,
   Import_Method VARCHAR(500) NULL,
   ShowInFilter BIT NULL,
   Colored BIT NULL,
   DisplayButton BIT NULL,
   ClearIfNotVisible BIT NULL,
   UseInGroupUpdate BIT NULL,
   Display_Name_ENU VARCHAR(150) NULL,
   ColumnNumber INT NULL,
   CONSTRAINT PK__MX_WF_ObjectStru__1F4A3988 PRIMARY KEY (ID_WF_Object)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


Insert stateent:

Code:

INSERT INTO [dbo].[MX_WF_ObjectStructure] ([ID_WF_Object], [ID_Object_Type], [Parent_ID], [NameRU], [NameEN], [Table_Name], [PK_Field], [ID_WF_UsingObject], [Edit_Field], [Field_Type], [FK_Field], [WhereClause], [Field_List], [Value_List], [Get_Method], [Set_Method], [ID_Journal], [JournalPK], [JournalField], [ID_LogicType], [Default_Value], [Edit_Mask], [Visible_Condition], [Enabled_Condition], [Required_Condition], [Dictionary_Condition], [Constraint_Condition], [Constraint_Message], [ID_Sheet], [IsPublished], [OrderNum], [Display_Name], [Display_Rows], [CreateDate], [ID_User], [UpdateDate], [Note], [Filter_Condition], [Use_In_Templates], [Editing_Condition], [Filter_Func], [ID_Form], [FormScript], [ID_WF_ImportObject], [Import_Method], [ShowInFilter], [Colored], [DisplayButton], [ClearIfNotVisible], [UseInGroupUpdate], [Display_Name_ENU], [ColumnNumber]) VALUES (20, 3003, 15, N'Продукт', N'Product', N'TD_Client_Products', N'ID_Client_Product', NULL, N'ID_Product', N'int', NULL, NULL, NULL, NULL, NULL, NULL, 101075, N'ID Продукта', N'Продукт', 4005, NULL, NULL, N'[ТипПродукта] <> ''Пластиковая карта'' or [ПризнакПрорабатываемого] = 1', NULL, N'1', NULL, N'[Продукт] <> 14962805 AND [Продукт] NOT IN
(SELECT
  P1.ID_Product
FROM
  dbo.TD_Products AS P1
  INNER JOIN dbo.TD_Products AS P2
    ON P1.Parent_ID = P2.ID_Product
WHERE
  P2.Parent_ID IN (14947962, 14951205)
)', N'Выбран недопустимый продукт!', 10, 1, 0, N'Продукт', 1, '2007-09-25 11:57:25.967', 100689, '2009-05-27 10:58:31.000', NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL)


I think the problem is in the multi-values​​.
After the changes are committed to SVN, I forcibly removed all the inserts and multi-line error is gone.
I ask help because Unfortunately in our DB many such values​​.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1121
Location: My desk.

PostPosted: Tue Nov 05, 2013 3:37 pm    Post subject: Reply with quote

Thanks

I created the table, and then inserted the data. I was able to add this into SQL Source Control without any problem though.

Do you have trouble when committing? Or do you have problems with existing files .sql from somewhere else?
Back to top
View user's profile Send private message
fadeev_av



Joined: 15 Oct 2013
Posts: 5

PostPosted: Tue Nov 05, 2013 5:21 pm    Post subject: Reply with quote

I have no problems with the first commit. But after the first fix and re-update changes the problem occurs. The system offers me again to fix all of the changes. A list of already fixed hostname is empty ...
Back to top
View user's profile Send private message
fadeev_av



Joined: 15 Oct 2013
Posts: 5

PostPosted: Wed Nov 06, 2013 10:28 am    Post subject: Reply with quote

Also, post the screenshots
This is a working copy and the recorded data in the table:


This is the result of updating the state:
Back to top
View user's profile Send private message
fadeev_av



Joined: 15 Oct 2013
Posts: 5

PostPosted: Fri Nov 08, 2013 8:58 am    Post subject: Reply with quote

Sorry, I had a more detailed analysis of the problem.
The error does not appear if you delete one record. Spread the sample on this post. In the form which it has generated Source Control
Code:

EXEC(N'INSERT INTO [dbo].[MX_WF_ObjectStructure] ([ID_WF_Object], [ID_Object_Type], [Parent_ID], [NameRU], [NameEN], [Table_Name], [PK_Field], [ID_WF_UsingObject], [Edit_Field], [Field_Type], [FK_Field], [WhereClause], [Field_List], [Value_List], [Get_Method], [Set_Method], [ID_Journal], [JournalPK], [JournalField], [ID_LogicType], [Default_Value], [Edit_Mask], [Visible_Condition], [Enabled_Condition], [Required_Condition], [Dictionary_Condition], [Constraint_Condition], [Constraint_Message], [ID_Sheet], [IsPublished], [OrderNum], [Display_Name], [Display_Rows], [CreateDate], [ID_User], [UpdateDate], [Note], [Filter_Condition], [Use_In_Templates], [Editing_Condition], [Filter_Func], [ID_Form], [FormScript], [ID_WF_ImportObject], [Import_Method], [ShowInFilter], [Colored], [DisplayButton], [ClearIfNotVisible], [UseInGroupUpdate], [Display_Name_ENU], [ColumnNumber]) VALUES (16018258, 3005, 14949701, N''Исполнители'', N''Executors'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4009, NULL, NULL, NULL, N''[ТипЗадания] NOT IN (14944020, 14949884)'', N''not ([ЗаданиеШаблон] = 1 and [IDКампании] is null and [IDКлиента] is null and [IDРодительскогоЗадания] is null) and ([СтатусЗадания] <> 14946195 or ([ЗаданиеШаблон] = 1 and [IDКампании] is null))'', NULL, NULL, NULL, 14949702, 1, 4, N''Исполнители'', 1, ''2011-12-08 23:51:16.850'', 100006, ''2012-04-20 11:37:22.000'', NULL, NULL, 0, N''not((([ЗаданиеШаблон] = 0) or
([IDКампании] is not null and [IDКлиента] is null)) and [СтатусЗадания] = 14944034) and not
([Запрет изменения исполнителя] = 1)'', NULL, NULL, cast(N''function Load(ID_Task_History: Integer): TObjectList;
var
  Query: TADOQuery;
  t1, t2, t3, t4, t5, t6: TStringList;
begin
  Result := TObjectList.Create;
  t1 := TStringList.Create;
  Result.Add(t1);
  t2 := TStringList.Create;
  Result.Add(t2);
  t3 := TStringList.Create;
  Result.Add(t3);
  t4 := TStringList.Create;
  Result.Add(t4);
  t5 := TStringList.Create;
  Result.Add(t5);
  t6 := TStringList.Create;
  Result.Add(t6);

  Query := TADOQuery.Create(nil);
  try
    Query.Connection := Application.CashStructure.ADOConnection;
    Query.CommandTimeout := 0;
    Query.SQL.Text :=
      ''''SELECT'''' +
      ''''  ID_Client, '''' +
      ''''  ID_Role, '''' +
      ''''  ID_EmplGroup, '''' +
      ''''  ID_ExtClient, '''' +
      ''''  ID_ExtRole, '''' +
      ''''  ID_ExtEmplGroup '''' +
      ''''FROM '''' +
      ''''  dbo.TD_Empl_In_History '''' +
      ''''WHERE '''' +
      ''''  IsExecutor = 1 AND '''' +
      ''''  ID_Task_History = :ID_Task_History'''';
    Query.Parameters.ParamByName(''''ID_Task_History'''').DataType := ftInteger;
    Query.Parameters.ParamByName(''''ID_Task_History'''').Direction := pdInput;
    Query.Parameters.ParamByName(''''ID_Task_History'''').Value := ID_Task_History;
    Query.Active := True;
    while not Query.EOF do
    begin
      if not Query.FieldByName(''''ID_Client'''').IsNull then
        t1.Add(Query.FieldByName(''''ID_Client'''').AsString)
      else
      if not Query.FieldByName(''''ID_Role'''').IsNull then
        t2.Add(Query.FieldByName(''''ID_Role'''').AsString)
      else
      if not Query.FieldByName(''''ID_EmplGroup'''').IsNull then
        t3.Add(Query.FieldByName(''''ID_EmplGroup'''').AsString)
      else
      if not Query.FieldByName(''''ID_ExtClient'''').IsNull then
        t4.Add(Query.FieldByName(''''ID_ExtClient'''').AsString)
      else
      if not Query.FieldByName(''''ID_ExtRole'''').IsNull then
        t5.Add(Query.FieldByName(''''ID_ExtRole'''').AsString)
      else
      if not Query.FieldByName(''''ID_ExtEmplGroup'''').IsNull then
        t6.Add(Query.FieldByName(''''ID_ExtEmplGroup'''').AsString);
      Query.Next;
    end;
  finally
    Query.Free;
  end;
end;

procedure SaveExecGroup(ID_Task_History: Integer; ExecGroup: TStringList; Field: string);
var
  i: Integer;
  ID_EmplInHistory: Integer;
  s: string;
begin
  for i := 0 to ExecGroup.Count - 1 do
  begin
    ID_EmplInHistory := Application.CashStructure.GetNewID;
    s := ''''INSERT INTO dbo.TD_Empl_In_History (ID_Empl_In_History, IsExecutor, ID_Task_History, '''' +
      Field + '''') VALUES ('''' + IntToStr(ID_EmplInHistory) + '''', 1, '''' + IntToStr(ID_Task_History) + '''', '''' +
      ExecGroup[i] + '''')'''';
    ADOConnectionBatchExecute(s);
  end;
end;

procedure Save(ID_Task_History: Integer; Executors: TObjectList);
var
  i: Integer;
  s: string;
begin
  ADOConnectionBatchExecute(''''DELETE FROM dbo.TD_Empl_In_History WHERE IsExecutor = 1 AND ID_Task_History = '''' + IntToStr(ID_Task_History));
  for i := 0 to 5 do
  begin
    case i of
      0: s := ''''ID_Client'''';
      1: s := ''''ID_Role'''';
      2: s := ''''ID_EmplGroup'''';
      3: s := ''''ID_ExtClient'''';
      4: s := ''''ID_ExtRole'''';
      5: s := ''''ID_ExtEmplGroup'''';
    end;
    SaveExecGroup(ID_Task_History, Executors[i], s);
  end;
end;

function GetExecutorsByIDs(List: TStringList; SQL: string): string;
var
  IDs: string;
  i: Integer;
  Query: TADOQuery;
begin
  Result := '''''''';
  List.Delimiter := '''','''';
  IDs := List.DelimitedText;
  if IDs = '''''''' then
    Exit;
  SQL := AnsiReplaceText(SQL, ''''@ID'''', IDs);
  Query := TADOQuery.Create(nil);
  try
    Query.Connection := Application.CashStructure.ADOConnection;
    Query.CommandTimeout := 0;
    Query.SQL.Text := SQL;
    Query.Active := True;
    while not Query.EOF do
    begin
      if Result <> '''''''' then
        Result := Result + '''', '''';
      Result := Result + Query.Fields[0].AsString;
      Query.Next;
    end;
  finally
    Query.Free;
  end;
end;

function AsString(Executors: TObjectList): string;
var
  i: Integer;
  s: string;
  z: string;
begin
  z '' COLLATE Cyrillic_General_CI_AI as varchar(8000))
+cast(N'':= ''''SELECT ISNULL(Surname, '''''''''''''''') + ISNULL('''''''' '''''''' + SUBSTRING(Name, 1, 1) '''' +
    ''''+ ''''''''.'''''''', '''''''''''''''') + ISNULL('''''''' '''''''' + SUBSTRING(FatherName, 1, 1) + ''''''''.'''''''', '''''''''''''''') FROM TD_Clients_FL WHERE ID_Client IN (@ID)'''';
  Result := GetExecutorsByIDs(Executors[0], z);
  s := GetExecutorsByIDs(Executors[1], ''''SELECT FolderName FROM dbo.MX_Folders_ WHERE ID IN (@ID)'''');
  if s <> '''''''' then
  begin
    if Result <> '''''''' then
      Result := Result + '''', '''';
    Result := Result + s;
  end;

  s := GetExecutorsByIDs(Executors[2], ''''SELECT EmplGroup FROM dbo.TD_EmplGroups WHERE ID_EmplGroup IN (@ID)'''');
  if s <> '''''''' then
  begin
    if Result <> '''''''' then
      Result := Result + '''', '''';
    Result := Result + s;
  end;

  for i := 3 to 5 do
  begin
    s := GetExecutorsByIDs(Executors[i], ''''SELECT Display_Name FROM dbo.MX_WF_ObjectStructure WHERE ID_WF_Object IN (@ID)'''');
    if s <> '''''''' then
    begin
      if Result <> '''''''' then
        Result := Result + '''', '''';
      Result := Result + s;
    end;
  end;
end;

procedure FreeData(Executors: TObjectList);
begin
  FreeAndNil(Executors);
end;

function Edit(Sender: TMXExtPropEditor; Executors: TObjectList): TObjectList;
var
  Form: TForm;
  ID_Client: Integer;
  Read_Only: Boolean;
begin
  Read_Only := Sender.IsPropReadOnly(16018258);
  if Read_Only then
    Exit;
  if not TryStrToInt(Sender.ReplaceParamNamesByValues(''''[IDКлиента]''''), ID_Client) then
    ID_Client := 0;
  Result := Executors;
  Form := CreateFormByID(14962090);
  try
    Form.DCScripter.DispatchMethod(''''EditExecutors'''', [ID_Client, Executors]);
    Result := Executors;
  finally
    Form.Free;
  end;
end;

function AsSQLString(Executors: TObjectList): string;
var
  i, j: Integer;
begin
  Result := '''''''';
  for i := 0 to 5 do
    for j := 0 to Executors[i].Count - 1 do
    begin
      if Result <> '''''''' then
        Result := Result + '''','''';
      Result := Result + Executors[i][j];
    end;
  Result := QuotedStr(Result);
end;'' COLLATE Cyrillic_General_CI_AI as varchar(8000))
, NULL, NULL, 0, 0, 0, 0, 0, NULL, 1)')

I understand that it is clearly very large. Tell me is there any limits on the amount of lines?
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group