Foregein Key data voilation in merge replication

HI,
The situation is like this, merge replication is setup on server(Publisher),
and 4 clients machines and these are subscribers.
I have two table with primary key and foreign key relationship. Both the
tables have primary key with Uniqueidentifier column. First uniqueidnetifer
column is mapped to second table as Foregein key.
here in some situation inserts into primary key and foregein key tables
happyining in the sequence like insert first in Primary Key table, second in
Foregein Key table.
In some situations the process is reversing, like first inserting foregein
key table then primary key table. With this type of activity i am loosing
most important data in teh foregein key tabel. Suggest me in this how can i
proceed.
Regards
Satish
After you make some data inserts on publisher, merge replication is applying
those changes on subscriber, but these modifications can be applied in
different sequence than you originally perfomed on publisher. The common way
to avoid foreign key conflicts is adding of NOT FOR REPLICATION flag to
foreign key constraints. When this flag is set - foreign key constraint is
not checked for replicated data. I suggest you to check Books Online for
more information.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Satish" <Satish@.discussions.microsoft.com> wrote in message
news:B0F93C30-505A-47FC-B5A4-19ADD5FD94C3@.microsoft.com...
> HI,
> The situation is like this, merge replication is setup on
server(Publisher),
> and 4 clients machines and these are subscribers.
> I have two table with primary key and foreign key relationship. Both the
> tables have primary key with Uniqueidentifier column. First
uniqueidnetifer
> column is mapped to second table as Foregein key.
> here in some situation inserts into primary key and foregein key tables
> happyining in the sequence like insert first in Primary Key table, second
in
> Foregein Key table.
> In some situations the process is reversing, like first inserting foregein
> key table then primary key table. With this type of activity i am loosing
> most important data in teh foregein key tabel. Suggest me in this how can
i
> proceed.
>
> Regards
> Satish
|||Thank you for quick reply. Most of the wesites are telling to create Foreign
Key with NOT FOR REPLICATION option.
I have got one more question that, why merge replication is not inserting
the data in the sequence manner, first Primary key data and then Foreign key
data. Any specific reason in this.
Regards
Satish
"Kestutis Adomavicius" wrote:

> After you make some data inserts on publisher, merge replication is applying
> those changes on subscriber, but these modifications can be applied in
> different sequence than you originally perfomed on publisher. The common way
> to avoid foreign key conflicts is adding of NOT FOR REPLICATION flag to
> foreign key constraints. When this flag is set - foreign key constraint is
> not checked for replicated data. I suggest you to check Books Online for
> more information.
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Satish" <Satish@.discussions.microsoft.com> wrote in message
> news:B0F93C30-505A-47FC-B5A4-19ADD5FD94C3@.microsoft.com...
> server(Publisher),
> uniqueidnetifer
> in
> i
>
>
|||Satish,
This is standard behaviour in SQL Server 2000 and is improved in SQL Server
2005.
Have a look at these articles for more details:
http://support.microsoft.com/default.aspx?scid=kb;[LN];307356
http://support.microsoft.com/kb/308266/EN-US/
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Forcing SQL Express to synchronize

I have a situation where I have SQL Server 2005 as my Publisher/Distributor
and I have it setup to run Push Merge Replication. The subscriber is an
instance of SQL Server Express. Obviously SQL Server Express doesn't have
the means to force synchronization. I do have it setup at the moment to run
Continiously but this will definitely be resource intensive running Merge
Replication on a 1000 or so databases...so I would like to be able to set it
up as Run on Demand and force the synchronization from the application. I
have seen BOL but that RMO code is all in .Net, I am programming in
VB6...and furthermore it looks like I have to use a Pull Subscription in
order to code for RMO...is that correct?
How should I go about setting this up for optimum performance?
Thanks!
RSH
I would advise you to use a pull subscription and use the activeX controls
(you can use the SQL 2005 activeX control with VB6 - use set
test=CreateObject("SQLDistribution.90.SQLDistribut ion")
), or use WSM for that.
You want to use a pull as it has a lower impact on the publisher and you can
run it from the subscriber which is especially important if your publisher
is offline from time to time.
For WSM go to Start, All Programs, Accessories and select Synchronize. Click
on MS SQL Server and select Properties to select your publication and set
subscription properties.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:O5%230v1Q9FHA.3044@.TK2MSFTNGP10.phx.gbl...
>I have a situation where I have SQL Server 2005 as my Publisher/Distributor
>and I have it setup to run Push Merge Replication. The subscriber is an
>instance of SQL Server Express. Obviously SQL Server Express doesn't have
>the means to force synchronization. I do have it setup at the moment to
>run Continiously but this will definitely be resource intensive running
>Merge Replication on a 1000 or so databases...so I would like to be able to
>set it up as Run on Demand and force the synchronization from the
>application. I have seen BOL but that RMO code is all in .Net, I am
>programming in VB6...and furthermore it looks like I have to use a Pull
>Subscription in order to code for RMO...is that correct?
> How should I go about setting this up for optimum performance?
> Thanks!
> RSH
>
|||Hilary,
Thanks for your reply.
Question 1:
I set up a Pull subscription scenerio and on the SQLServer Express instance
when I run WSM I get the following error:
"The Schema script 'Accrual_2.sch' could not be propogated to the
Subscriber."
"The process could not read the file 'C:\Program Files...' due to OS Error
3"
That directory does not exist from the "unc\" part of the path so I am
assuming that a snapshot hasn't been created. If I am correct how do I
generate a snapshot in SQL Express? Or is it created on the SQL Server
instance and copied over?
Question 2:
The VB Code below is my quick attempt at using the Active X control. The
code generates the following error: "The subscription to publication
'TestSub1' has expired or does not exist" which is not right...it does in
fact exist as illustrated in my screenshot...what am I missing here?
Option Explicit
' Add Reference Microsoft SQL Distribution Control 8.0
Private mobjDistr As SQLDISTXLib.SQLDistribution
Private Sub main()
Set mobjDistr = New SQLDISTXLib.SQLDistribution
On Error GoTo Errhandler
With mobjDistr
'Set up the Publisher
.Publisher = "VIRTUAL1"
.PublisherDatabase = "00010101"
.Publication = "TPTestSub1"
.PublisherSecurityMode = NT_AUTHENTICATION
'Set up the Subscriber.
.Subscriber = "PARENTVM\TPSVCCTR01"
.SubscriberDatabase = "00010101"
.SubscriberDatasourceType = SQL_SERVER
.SubscriberSecurityMode = DB_AUTHENTICATION
.SubscriberLogin = "V1"
.SubscriberPassword = "pw"
'Set up the Subscription.
.SubscriptionType = PULL
.SynchronizationType = AUTOMATIC
'Synchronize the data.
.Initialize
.Run
.Terminate
MsgBox "Complete"
End With
Exit Sub
Errhandler:
MsgBox Error
End Sub
Thanks a ton for your help!
RSH
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ecjk%23rR9FHA.1184@.TK2MSFTNGP12.phx.gbl...
>I would advise you to use a pull subscription and use the activeX controls
>(you can use the SQL 2005 activeX control with VB6 - use set
>test=CreateObject("SQLDistribution.90.SQLDistribu tion")
> ), or use WSM for that.
> You want to use a pull as it has a lower impact on the publisher and you
> can run it from the subscriber which is especially important if your
> publisher is offline from time to time.
> For WSM go to Start, All Programs, Accessories and select Synchronize.
> Click on MS SQL Server and select Properties to select your publication
> and set subscription properties.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:O5%230v1Q9FHA.3044@.TK2MSFTNGP10.phx.gbl...
>
|||This almost always means a permissions problem. From what it looks like you
are connecting from your subscriber to c:\Program files\... on your
publisher, or at least it should be your publisher. Make sure when you
configured your distributor that your snapshot location was defined as a
share. If not you can go back and fix it by right clicking on your
replication folder and selecting distributor properties.Select the
Publishers node, and for your publisher select the browse button to the
right of your publisher and in the default snapshot folder make it a share.
For your second question, the reason you most frequently get this message is
because the snapshot has not being generated for this publication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:Olcv3AT9FHA.636@.TK2MSFTNGP10.phx.gbl...
> Hilary,
> Thanks for your reply.
> Question 1:
> I set up a Pull subscription scenerio and on the SQLServer Express
> instance when I run WSM I get the following error:
> "The Schema script 'Accrual_2.sch' could not be propogated to the
> Subscriber."
> "The process could not read the file 'C:\Program Files...' due to OS Error
> 3"
> That directory does not exist from the "unc\" part of the path so I am
> assuming that a snapshot hasn't been created. If I am correct how do I
> generate a snapshot in SQL Express? Or is it created on the SQL Server
> instance and copied over?
>
> Question 2:
>
> The VB Code below is my quick attempt at using the Active X control. The
> code generates the following error: "The subscription to publication
> 'TestSub1' has expired or does not exist" which is not right...it does in
> fact exist as illustrated in my screenshot...what am I missing here?
> Option Explicit
> ' Add Reference Microsoft SQL Distribution Control 8.0
> Private mobjDistr As SQLDISTXLib.SQLDistribution
> Private Sub main()
> Set mobjDistr = New SQLDISTXLib.SQLDistribution
> On Error GoTo Errhandler
> With mobjDistr
> 'Set up the Publisher
> .Publisher = "VIRTUAL1"
> .PublisherDatabase = "00010101"
> .Publication = "TPTestSub1"
> .PublisherSecurityMode = NT_AUTHENTICATION
> 'Set up the Subscriber.
> .Subscriber = "PARENTVM\TPSVCCTR01"
> .SubscriberDatabase = "00010101"
> .SubscriberDatasourceType = SQL_SERVER
> .SubscriberSecurityMode = DB_AUTHENTICATION
> .SubscriberLogin = "V1"
> .SubscriberPassword = "pw"
> 'Set up the Subscription.
> .SubscriptionType = PULL
> .SynchronizationType = AUTOMATIC
> 'Synchronize the data.
> .Initialize
> .Run
> .Terminate
> MsgBox "Complete"
> End With
> Exit Sub
> Errhandler:
> MsgBox Error
> End Sub
> Thanks a ton for your help!
> RSH
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ecjk%23rR9FHA.1184@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks.
How do I generate the snapshot?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:evENuiV9FHA.1188@.TK2MSFTNGP12.phx.gbl...
> This almost always means a permissions problem. From what it looks like
> you are connecting from your subscriber to c:\Program files\... on your
> publisher, or at least it should be your publisher. Make sure when you
> configured your distributor that your snapshot location was defined as a
> share. If not you can go back and fix it by right clicking on your
> replication folder and selecting distributor properties.Select the
> Publishers node, and for your publisher select the browse button to the
> right of your publisher and in the default snapshot folder make it a
> share.
> For your second question, the reason you most frequently get this message
> is because the snapshot has not being generated for this publication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:Olcv3AT9FHA.636@.TK2MSFTNGP10.phx.gbl...
>
|||Hillary,
I set the Snapshot folder up as a Share as you described yesterday. I am no
longer getting the error about:
"The Schema script 'Accrual_2.sch' could not be propogated to the
Subscriber." and I see the files being setup in the share.
I am still getting the pesky error: "The subscription to publication
'TestSub1' has expired or does not exist"
I assume that as part of the setup that somewhere the subscriber is aware of
where to look for the snapshot...I did not see any properties for this when
setting up the subscriber. Also something that might be a bit odd is that I
am generating the snapshot from the Publisher side...is this correct? Or
should I somehow generate it from the subscriber (if so how?) and if that is
the case how do I make the Publisher aware of the fact that it was created?
Thanks alot for your time!
Ron
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eZBocEb9FHA.500@.TK2MSFTNGP15.phx.gbl...
> Thanks.
> How do I generate the snapshot?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:evENuiV9FHA.1188@.TK2MSFTNGP12.phx.gbl...
>

FORCEPLAN causes different results in SQL SERVER 2000

Hello,
Has anyone run into a situation where using SET FORCEPLAN ON changes
the results set of a stored procedure? I've got a large, complicated
procedure that spits out a large hierarchical table of aggregated
values. I noticed that some of the data was coming out incorrectly,
even though the components seem to be correct. In the process of
troubleshooting I tried setting FORCEPLAN to ON, and found that the
procedure started spitting out the correct data.
Unfortunately due to performance issues we can't just leave FORCEPLAN
on, so I need to get to thr root of this problem. I'm also curious as
to why this would happen in the first place. Does anyone have any
ideas? My understanding of FORCEPLAN is that it changes the order of
joins in a query, changing the performance. But it shouldn't change
the data itself, correct?
On a hunch I tried reindexing, thinking that maybe some bad info was
cached, but no luck.
Does anyone know of any instances where FORCEPLAN would change the
results set of a query?
Thanks,
Chris RutledgeWhat is the actual query?
"Chris Rutledge" <csrutledge@.gmail.com> wrote in message
news:1138826752.664351.310480@.g43g2000cwa.googlegroups.com...
> Hello,
> Has anyone run into a situation where using SET FORCEPLAN ON changes
> the results set of a stored procedure? I've got a large, complicated
> procedure that spits out a large hierarchical table of aggregated
> values. I noticed that some of the data was coming out incorrectly,
> even though the components seem to be correct. In the process of
> troubleshooting I tried setting FORCEPLAN to ON, and found that the
> procedure started spitting out the correct data.
> Unfortunately due to performance issues we can't just leave FORCEPLAN
> on, so I need to get to thr root of this problem. I'm also curious as
> to why this would happen in the first place. Does anyone have any
> ideas? My understanding of FORCEPLAN is that it changes the order of
> joins in a query, changing the performance. But it shouldn't change
> the data itself, correct?
> On a hunch I tried reindexing, thinking that maybe some bad info was
> cached, but no luck.
> Does anyone know of any instances where FORCEPLAN would change the
> results set of a query?
> Thanks,
> Chris Rutledge
>|||It's pretty enormous, but I think the pertinent bit is in here:
SELECT SUM(Event0.Qty * EventCmpt.AggSign ) AS SQ ,
NodeSku.ProductStream, NodeSku.ProductDescription, NodeSku.PrdName,
Event0.TimePeriod - @.MaxRel AS TimePeriod
-- Node Table Joins
FROM ( SELECT EventId, TimePeriod, NodeId, EventTypeId, Qty,
TP.PlanId, GroupId, TransId FROM Event
INNER JOIN @.TableEventTimePeriod AS TP
ON TP.EventTimePeriod=TimePeriod
AND TP.PlanId=Event.PlanId
AND IsValidFlag=1 ) AS Event0
INNER JOIN NODESKU
ON Event0.NodeId=NODESKU.NodeId
JOIN ( SELECT EventCmpt, AggSign FROM EventCmpt
WHERE EventId=@.ElementId UNION SELECT @.ElementId, 1 AS
AggSign )
EventCmpt ON Event0.EventTypeId=EventCmpt
GROUP BY Event0.TimePeriod, NodeSku.ProductStream,
NodeSku.ProductDescription, NodeSku.PrdName
WITH ROLLUP
-- multi-component grouping.
HAVING GROUPING (NodeSku.ProductDescription)=GROUPING(NodeSku.PrdName)
AND
GROUPING(Event0.TimePeriod)=0
AND (NodeSku.ProductStream IS NOT NULL OR
GROUPING(NodeSku.ProductStream ) = 1 )
AND (NodeSku.ProductDescription IS NOT NULL OR
GROUPING(NodeSku.ProductDescription ) = 1 )
AND (NodeSku.PrdName IS NOT NULL OR GROUPING(NodeSku.PrdName ) = 1
)
) B
INNER JOIN PlanPeriod ON B.TimePeriod <= PlanPeriod.PeriodId
AND PlanPeriod.CalendarId = ( SELECT CalendarId FROM Plans WHERE
PlanId = @.PlanId )
AND PlanPeriod.PeriodId BETWEEN @.SubRangeMin AND @.SubRangeMax
GROUP BY B.ProductStream, B.ProductDescription, B.PrdName,
PlanPeriod.PeriodId
ORDER BY B.ProductStream, B.ProductDescription, B.PrdName
END
ELSE ...|||I can not say why is this happening without looking at the sample code and
sample data to reproduce the problem in my environment. However, it appears
to me that you may be using ANSI 89 standard in your SQL. Try changing that
to ANSI 92 and see what output are your seeing.
"Chris Rutledge" wrote:

> Hello,
> Has anyone run into a situation where using SET FORCEPLAN ON changes
> the results set of a stored procedure? I've got a large, complicated
> procedure that spits out a large hierarchical table of aggregated
> values. I noticed that some of the data was coming out incorrectly,
> even though the components seem to be correct. In the process of
> troubleshooting I tried setting FORCEPLAN to ON, and found that the
> procedure started spitting out the correct data.
> Unfortunately due to performance issues we can't just leave FORCEPLAN
> on, so I need to get to thr root of this problem. I'm also curious as
> to why this would happen in the first place. Does anyone have any
> ideas? My understanding of FORCEPLAN is that it changes the order of
> joins in a query, changing the performance. But it shouldn't change
> the data itself, correct?
> On a hunch I tried reindexing, thinking that maybe some bad info was
> cached, but no luck.
> Does anyone know of any instances where FORCEPLAN would change the
> results set of a query?
> Thanks,
> Chris Rutledge
>|||Thanks for the reply, Nitin. Which elements appear to be from ANSI 89?
I'm not very familiar with the differences.|||This might be the case if you have the old style outer join in your
query (for example WHERE MyCol *= OtherCol). For an old style inner join
it shouldn't matter. (for example SELECT ... FROM A, B WHERE A.id=B.id)
However, the snippet you posted does not contain such syntax.
Now, the bottom line is that it is a bug. Because with SQL you specify
the result, and this result should be the same (i.e. correct) regardless
of the order in which the steps are executed.
Unfortunately, to analyse the problem, the entire query and execution
plan is necessary, and probably also the DDL and maybe even (some?)
data.
Gert-Jan
Chris Rutledge wrote:
> Thanks for the reply, Nitin. Which elements appear to be from ANSI 89?
> I'm not very familiar with the differences.|||I've had problems where certain indexes have been corrupt, and because
SET FORCEPLAN can cause different indexes to be used, this may explain
why you're getting different resuts.
Are you able to identify the different indexes being used in the two
scenarios and try recreating them?|||Hi folks,
I've done some more experimentation with your guidance and I think I'm
narrowing in on the problem. I tried adding some where statements to
the sp to limit the data i'm getting back. With the new, smaller
dataset it's returning the correct values.
I did a side-by-side comparison of the two versions of the stored
procedure using Beyond Compare. You can take a look yourself here:
http://www.wamsystemsweb.com/SQL/DiffReport.html
(note, the filtered version of the sp is waaaay off to the right)
The differences between the two are highlighted in red.
The filter itself is from lines 357 to 364.
I believe the relevant bits of the execution plan are from 383 to 410
or 504 to 520.
Based on the feedback I've been getting I located the indexes that were
being used by the unfiltered version that weren't being used by the
filtered version. I dropped and recreated those indexes, but I had no
luck.
Now I'm looking at the details of the execution plans for the two. The
filtered version (the one that works) uses more nested loops, while the
unfiltered version is using a lot of hash matches and merge joins.
Thanks much for all your feedback, folks. Any additional hints are
greatly appreciated.
Chris|||Even more to the point, except for the SET FORCEPLAN ON statement early
on, these two stored procedures are identical. Their execution plans
appear to be identical. The sp on the left generates the correct data,
the one on the right does not:
http://www.wamsystemsweb.com/SQL/DiffReport2.html
Chris|||Hmm, never mind. What I actually illustrated in the two above examples
is SHOWPLAN_TEXT doesn't work the way I think it does.

FOR XML RAW & Null Characters Bug

Just curious if anyone else has seen this issue:
We had a situation where the XML being retrieved from an XML Explicit query
was malformed. After looking at the resulting XML from the query being ran
with different sets of paramaters we were able to identify that the building
of the XML was stopping on the same value. Looking further we noticed this
field had a null character (ASCII code of 0).
Here's a simple example showing the issue:
declare @.var varchar(100)
set @.var='Josh' + CHAR(0)
select @.var AS Col
for XML RAW
this results in <row Col="Josh
I couldn't find if this was a documented bug or expected behavior? Does
anyone know of any articles that describe how the null character is handled
in SQL?
Thanks
JoshOn Mon, 30 Jan 2006 13:05:31 -0800, joshb wrote:

>Just curious if anyone else has seen this issue:
>We had a situation where the XML being retrieved from an XML Explicit query
>was malformed. After looking at the resulting XML from the query being ran
>with different sets of paramaters we were able to identify that the buildin
g
>of the XML was stopping on the same value. Looking further we noticed this
>field had a null character (ASCII code of 0).
>Here's a simple example showing the issue:
>declare @.var varchar(100)
>set @.var='Josh' + CHAR(0)
>select @.var AS Col
>for XML RAW
>this results in <row Col="Josh
>I couldn't find if this was a documented bug or expected behavior? Does
>anyone know of any articles that describe how the null character is handled
>in SQL?
Hi Josh,
The problem is that ASCII 0 is used as a string delimiter in many
languages. So the low-level code that sends the results to the screen
sees the ASCII 0 and thinks it's the end of the string.
Try running your code from another client (e.g. osql).
Hugo Kornelis, SQL Server MVP|||Hi, Josh
The problem is fixed in SQL Server 2005, where your example returns
this (in Management Studio or in Query Analyzer):
<row Col="Josh�"/>
However, Management Studio warns that the character with the code 0x0
is illegal in XML documents.
Razvan

For XML Explicit in a stored proc

I searched and searched and didn't see what (i think) i need for a
solution.
Here's my situation. SQL 2k5 Stored Proc with
set @.XMLFile = (
SELECT
1 as Tag,
NULL as Parent,
cr.ID as [Resume!1!ID],
cr.GUID as [Resume!1!GUID],
cr.CandidateResumeTypeID as [Resume!1!typeID],
cr.ResumeName as [Resume!1!Name],
NULL as [resume_content!2!!ELEMENT],
lots and lots more.
ending in FOR XML EXPLICIT)
I get an error with this approach. The Query alone outside a proc and
outside the set blah blah = () returns an XML file. I need a proc to do
the same thing.
Thanks!Hello john.wilker@.gmail.com,

> ending in FOR XML EXPLICIT)
> I get an error with this approach. The Query alone outside a proc and
> outside the set blah blah = () returns an XML file. I need a proc to
> do the same thing.
Its always helpful to know the exact error, etc. but, have you considered
using FOR XML PATH instead? I've successfully used that many times in a stor
ed
procedure exactly this way.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/