June 9, 2016 at 8:52 am
Hi,
I'm supporting and application that dispatching tickets for cinema and i detect some diferences in the internal procedures on sql merge replication when apply the change between publication table and suscriptor table.
I have some triggers apply in merge replication reception table. This trigger are for inserted so, the problem its the replication procedure change the order of the records between the tables.
On original table we have an specific order for each ticket and line associated to ticket id, but in destination table this insertion order change and triggering process split the information wrong aleatory depens merge insert the values collected.
We have and original table with 3 fields id's for the tickets
Merge original table suscriptor:
Ticket nº, line nº, order, xxx...n
1 / 1 / 1
1 / 2 / 2
1 / 3 / 3
2 / 1 / 1
2 / 2 / 2
2 / 3 / 3
3 / 1 / 1
3 / 2 / 2
3 / 3 / 3
...The same values inserted in merge publication table receptor detects this disorder in the insertion records one by one line.
Merge destination table publisher on trigger process detect this on insert the records by replication:
Ticket nº line nº order xxx...n
1 1 1
1 3 2
1 2 3
2 2 1
2 1 2
2 3 3
3 3 1
3 2 2
3 1 3
The order in the reception table from the source not the same in destination table that the orinal insertion for the application.
I have thi problem affecting in the data integration any could help me to prevent this problem please?.
The version sql server its 2000 sp4 some old for now.
I read something related article with the parameter @processing_order in sysmergearticles but this its only available from sql 2008 in advance and not resolve my problem because only apply to articles order not order into one article defined.
https://technet.microsoft.com/en-us/library/ms174329(v=sql.105).aspx
Thanks for your time and help.
June 9, 2016 at 9:07 am
you need to have an explicit ORDER BY in your query. otherwise, it's coincidence or a crapshoot.
SQL server does not have a concept of order in a table.
SQL will build a plan it thinks is the most efficient to get the data,and is free to order it in any way it sees as efficient,so your select/insert will be in that potentially random order, unless you add an order by clause.
once inserted in another table, it's the same thing, if you need the data ordered, there's no guarantee you get it in any specific order without an ORDER BY clause.
Lowell
June 9, 2016 at 9:42 am
Perfect, that was my mistake, ordering by rowguid field the insertion not its in order from applicacion so the replication make the change was receive in disorder.
I change it and now all its correct. Thanks for your help sir.
June 15, 2016 at 4:30 am
After try the insert in order the merge process continuouse disordering the insertion on destionation table.
Any idea what make this if the insert order in publication table are now correct.
Thanks for your help.
June 15, 2016 at 5:40 am
As Lowell has already mentioned, your question does not make sense. A table being an UNORDERED set is a fundamental part of relational theory.
The only guaranteed way to order a query is to use the ORDER BY clause.
June 15, 2016 at 6:11 am
Thanks for your comments and all of this its correct, but the insert its in order now (verified), but merge rowguid assignation provided for merge replication column its aleatory in the assignation by the merge replication process,
This rowguid its the used by merge replication process to make the insert on destination merge replication table, so how we could change this "order by" in the internal rowguid assignament in merge replication.
Dateop field shows the time of insertion in tables but rowguid assignement its aleatory in this insert order.
Thanks for your help sirs.
June 15, 2016 at 6:22 am
The GUID just needs to be unique, why do you want to order by it?
Looking at your last query does:
ORDER BY alber_no, linea_no;
do what you want?
June 15, 2016 at 6:22 am
you keep missing the point about how tables do not have a natural or default order in SQL server.
they are treated as unordered rows of data.
all the rows are in the table, due to the replication process.
if you want to see the data in the replicated table, in a specific order, the query from that table must have an order by clause.
there is no default order by in sql server.
lots of things can affect the order of the data without an order by clause:
load on the server, reading pages from disk to memory, new indexes, statistics, parallelism, and more that i could think of if i tried.
Lowell
June 15, 2016 at 8:22 am
Hi men, see the dateop field with the order insertion time, just its order by albar_no, linea_no fields.
Rowguid its inserted for each record by sql over the replication merge source table cfalbara_2way to control the the merge replication process as unique identifier between source merge replication table and destination table.
This order its followed for the the replication for the insert records on destination table. This order rowguid its the used by replication finally to insert the records and not the same inserted in table for application order albar_no, linea_no fields on table cfalbara_2way.
The table cfalbara its used for the application and cfalbara_2way its the mirror table used for merge replication to transfer data. You can see the insert time in dateop field its in order according order by application insert but not the same the rowguid insered for automation sql replication.
Do you know if we could implement some steps in configuration to control this point and mark the records for replication order in the same order that insert process?.
This its important because on destination table for merge replication i have some other triggering process to control some other operations and this replication insert records its important to be insertend in the same order the application inserted.
The xxxx_reg tables its only registry tables the operation inserts/updates/deletes to show you the timming on each insert.
In the attachement one case correct with the same configuration that the other one.
I apreciate all your help in the review and commends. Thanks to all for it.
June 16, 2016 at 10:12 am
Finally i found the problem but not the solution for sql2000 versions.
Its in the definition field for rowguidcol in sql2000 this unique assignament its aleatory not in the same way that insertions.
In sql 2000 the field rowguidcol its declare like this "newid()"
...but in versions 2005 and later the field rowguidcol its declare with this other function: newsequentialid() (....to better....)
Try this (over sql 2005 or later vers.):
DECLARE @t TABLE (
GuidCol UNIQUEIDENTIFIER DEFAULT newid() ROWGUIDCOL,
IDENTITYCL INT IDENTITY(1,1),
data VARCHAR(60) )
INSERT INTO @t (data) SELECT 'test'
INSERT INTO @t (data) SELECT 'test1'
INSERT INTO @t (data) SELECT 'test2'
INSERT INTO @t (data) SELECT 'test3'
INSERT INTO @t (data) SELECT 'test4'
SELECT $rowguid,$IDENTITY FROM @t order by GuidCol
DECLARE @t2 TABLE (
GuidCol UNIQUEIDENTIFIER DEFAULT newsequentialid() ROWGUIDCOL,
IDENTITYCL INT IDENTITY(1,1),
data VARCHAR(60) )
INSERT INTO @t2 (data) SELECT 'test'
INSERT INTO @t2 (data) SELECT 'test1'
INSERT INTO @t2 (data) SELECT 'test2'
INSERT INTO @t2 (data) SELECT 'test3'
INSERT INTO @t2 (data) SELECT 'test4'
SELECT $rowguid,$IDENTITY FROM @t2 order by GuidCol
you can see the generation rowguid field used by merge its according with the insertion not aleatory.
The problem its to solve this in sql2000 maybe some other similar func newid().
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply