MsSQL Replication, problem change range, Audit Logout Login

[norwegen60]

Hello

I work since weeks at following problem:

I created a MsSQL replication which works fine until a range_end is reached. Normaly the range_end should automaticly change to next_range_begin. In my application the users get following message:

Fehler beim Einfügen. Es lag ein Konflikt mit einer Einschränkung für die Identitätsbereichsüberprüfung in der YYY-Datenbank in der replizierten Tabelle XXX vor. Falls die Identitätsspalte automatisch von der Replikation verwaltet wird, aktualisieren Sie den Bereich wie folgt: Führen Sie sp_adjustpublisheridentityrange für den Verleger und den Verteilungs-Agent oder den Merge-Agent für den Abonnenten aus.

and in the next replication log you can find

The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit (Quelle: MSSQL_REPL, Fehlernummer: MSSQL_REPL-2147199417)
Hilfe abrufen: http://help/MSSQL_REPL-2147199417
Fehler beim Konvertieren des nvarchar-Datentyps in numeric. (Quelle: MSSQLServer, Fehlernummer: 8114)
Hilfe abrufen: http://help/8114

after starting

Code: Select all

sp_adjustpublisheridentityrange   @publication =  'Repl_FMaQMa_SerNr'

the problem is solved until I reach next time the range_end
Looking to the SQL profiler I found following:
Taking only two queries, one as source and one as target and insert for example 1100 records (range is for testing set to 1000) with

Code: Select all

  with dmSerNr.dbquSerNr do
  begin
    dmSerNr.dbquSerNr_Insert.Open;

    for iAnz := 1 to lAnzInsert do
    begin
      sSerNr := 'F01'+FormatDateTime('HHMMSSZZZ', now);

      dmSerNr.dbquSerNr_Insert.Insert;

      try
        for j := 0 to FieldCount - 1 do
        begin
          sName:=LowerCase(Fields[j].FieldName);
          if sName='sernr' then
            dmSerNr.dbquSerNr_Insert.FieldByName('SerNr').Value := sSerNr
          else if (sName'id') and (sName'rowguid') and (pos('def',sName)1) then
            dmSerNr.dbquSerNr_Insert.FieldByName(sName).Value := Fields[j].Value;
        end;

        dmSerNr.dbquSerNr_Insert.Post;
      except
        dmSerNr.dbquSerNr_Insert.Cancel;
      end;
    end;

all insert commands are behind another. If I start exactly the same code (Datamodule) in my application, between every Insert command I can see Audit Login before and Audit Lookout behind the Insert

Code: Select all

Audit Login
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language Deutsch
set dateformat dmy
set datefirst 1
set transaction isolation level read committed

RPC:Completed
declare @p17 int
set @p17=6471555
exec sp_executesql N'INSERT INTO Ser_Nr
  (SerNr, LiefID, Typ, BauDat, Status, ErstellerID, Dat, Info, ServOrt, ErstUserID, ErstDat, LastUserID, LastDat, Serial)
VALUES
  (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14)
SET @P15 = SCOPE_IDENTITY()',N'@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000),@P4 datetime2(7),@P5 nvarchar(4000),@P6 int,@P7 datetime2(7),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 int,@P11 datetime2(7),@P12 int,@P13 datetime2(7),@P14 nvarchar(4000),@P15 int OUTPUT',N'F01140310564',499,N'jhfjhfhj','2012-01-21 00:00:00',N'S',3,'2012-01-21 00:00:00',NULL,N'F',3,'2012-01-21 14:02:32.4890000',3,'2012-01-21 14:02:39.0390000',N'S',@p17 output
select @p17

Audit Logout

There is no event attached to dmSerNr.dbquSerNr_Insert

Why the Audit Login Logout? I think, that that is the reason, why the range change fails.

My configuration:
Delphi XE Professional
UniDac 4.1.3
MsSQL 2008 R2

Best regards
Gerd

[norwegen60]

I think I have it. After a lot of hours analysis in the database and in my programm I can reproduce the failure.
In that moment where you set

Code: Select all

FetchAll = false

to a TUniQuery which you use later as MasterSource in a other TUniQuery you can observe in the MsSQL profiler that a lot of action start with

Code: Select all

Audit Login
SQL:BatchStarting
SQL:BatchCompleted
RPC:Completed
Audit Logout

With the always repeatable Login Logout you block the change of Ranges in the Replication

I think it`s a bug in UniDac

[AndreyZ]

Hello,

If you set the FetchAll specific option to False, you should keep in mind that execution of such queries blocks the current session. In order to avoid blocking, OLE DB creates additional session that causes the following problems:
- Each additional session runs outside the transaction context, thus the TCustomDAConnection.Commit and TCustomDAConnection.Rollback operations in the main session won't apply changes made in additional sessions. This also concerns changes made by TDataSet.Post;
- No transactions can be started if there are underfetched datasets within the connection;
- Temporary tables created in one session are not accessible from other sessions, therefore simultaneous using of FetchAll = False and temporary tables is impossible;
- When editing compound queries with ORDER BY clause, setting FetchAll = False may lead to deadlock during TDataSet.Post .
To solve the problem, you can either set the FetchAll property to True, or set the TMSConnection.Options.MultipleActiveResultSets property to True. The MultipleActiveResultSets property allows applications to have more than one pending request per connection, and, in particular, to have more than one active default result set per connection. Please note that MultipleActiveResultSets is supported starting with SQL Server 2005 with using SQL Native Client. To use SQL Native Client, you should set the OLEDBProvider specific option to prNativeClient. Here is an example:

Code: Select all

UniConnection.SpecificOptions.Values['OLEDBProvider'] := 'prNativeClient';