Incorrect Results -- is a hotfix available?

I have a query that is returning incorrect results. It is reproducible on
SQL 2000 SP4 (version 2040) and SP3a (version 760). It tried on 6 different
machines, all with different operating systems/hardware/etc.
Is there a hotfix available?
The following should not return any rows, but 1 row is returned:
declare @.fg table (FK1 int, FK2 int, FK3 int, FK4 int)
insert into @.fg
select 4, 4, 798, 13734
declare @.dg table (ID int, C1 varchar(20))
insert into @.dg
select 4, 'AAA'
declare @.drg table (C1 varchar(20))
insert into @.drg
select 'AAA'
declare @.fgt table (ID int, C1 char(1))
insert into @.fgt
select 4, 'F'
declare @.dwf table (ID int, FK1 int, FK2 int)
insert into @.dwf
select 798, 4, 46
declare @.f table (ID int)
insert into @.f
select 46
declare @.dwc table (ID int)
insert into @.dwc
select 4
select ID = 1
from @.fg fg
inner join @.dg dg on dg.ID = fg.FK1
inner join @.drg drg on drg.C1 = isnull(dg.C1, '')
inner join @.fgt fgt on fgt.ID = fg.FK2
inner join @.dwf dwf on dwf.ID = fg.FK3
inner join @.dwc dwc on dwc.ID = dwf.FK1
inner join @.f f on f.id = dwf.FK2
left join (select ID = 1, FK1 = 1234) ag on ag.ID = fg.FK4
left join (select ID = 1, C1 = '') dt on dt.ID = ag.FK1
inner join (select C1 = 1) c1 on c1.C1 = 1
inner join (select C2 = 1) c2 on c2.C2 = 1
where
isnull(dt.C1, '') = 'XYZ'This produces 0 rows on SQL Server 2005 (build 1399) and on SQL Server 2000
(build 2151 and build 2162).
You might want to see which (if any) specific hotfix references this issue.
See http://www.aspfaq.com/sql2000builds.asp
#2162 was available publicly until Friday or so, it was pulled due to a
couple of pretty serious issues. I noticed at least one of those issues on
#2151 also, which I am currently running, so please only try to obtain the
absolute highest build number you need, especially if you use a failover
cluster and/or rely on BULK INSERT.
Based on a quick scan, looks like the build # you need is 2145, but I did
not read the KB articles in full.
A
"davedave" <davedave@.discussions.microsoft.com> wrote in message
news:54BCCA28-6F9C-484D-976C-F357D2BF7D07@.microsoft.com...
>I have a query that is returning incorrect results. It is reproducible on
> SQL 2000 SP4 (version 2040) and SP3a (version 760). It tried on 6
> different
> machines, all with different operating systems/hardware/etc.
> Is there a hotfix available?
> The following should not return any rows, but 1 row is returned:
> declare @.fg table (FK1 int, FK2 int, FK3 int, FK4 int)
> insert into @.fg
> select 4, 4, 798, 13734
> declare @.dg table (ID int, C1 varchar(20))
> insert into @.dg
> select 4, 'AAA'
> declare @.drg table (C1 varchar(20))
> insert into @.drg
> select 'AAA'
> declare @.fgt table (ID int, C1 char(1))
> insert into @.fgt
> select 4, 'F'
> declare @.dwf table (ID int, FK1 int, FK2 int)
> insert into @.dwf
> select 798, 4, 46
> declare @.f table (ID int)
> insert into @.f
> select 46
> declare @.dwc table (ID int)
> insert into @.dwc
> select 4
> select ID = 1
> from @.fg fg
> inner join @.dg dg on dg.ID = fg.FK1
> inner join @.drg drg on drg.C1 = isnull(dg.C1, '')
> inner join @.fgt fgt on fgt.ID = fg.FK2
> inner join @.dwf dwf on dwf.ID = fg.FK3
> inner join @.dwc dwc on dwc.ID = dwf.FK1
> inner join @.f f on f.id = dwf.FK2
> left join (select ID = 1, FK1 = 1234) ag on ag.ID = fg.FK4
> left join (select ID = 1, C1 = '') dt on dt.ID = ag.FK1
> inner join (select C1 = 1) c1 on c1.C1 = 1
> inner join (select C2 = 1) c2 on c2.C2 = 1
> where
> isnull(dt.C1, '') = 'XYZ'
>

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1