Delay for Conflict Resolution in SS2K5

I have a couple of tables set up with column level tracking and using the "Subscriber Always Wins" conflict resolver.

I am still trying to work out why there is a conflict as the only updates to these rows happen at the subscriber.

What I am finding is that a conflict occurs and there appears to be a delay between the conflict being recognised and then resolved.

The reason I know there is a delay is because this is playing havoc with a data extract that relies on the updates from the subscriber.

For instance, I have a job on which the status is changed. That status change triggers an extract via BizTalk to another system. BizTalk, via an SQL adapter extracts the data from the server and I find that some of it is missing in the extract.

I go and look into the database at the raw data and find that the data is in fact there.

I have certain triggers running which set up export flags in the database to say this data should be exported. This works perfectly when there are no conflicts.

In each case where data has not been extracted, a conflict occurs.

Is there any way to guarantee that the conflict will be resolved at the same time as the synchronisation is complete - i.e. within the same transaction?

I thought replication was supposed to be a single transaction - you get it all or you get nothing.

I'm interested to hear from anyone who has experienced this behaviour.

Thanks

Are you saying that when you run merge agent, there happens a conflict and that conflict is not resolved in the same merge session?

Please explain your scenario more clearly so that we can troubleshoot it.

Also if you could simplify and narrow down the failure, it will be helpful.

|||There is another post on here that details the conflicts we are getting. It drills into some of the details

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

Just to clarify, the subscribers we have are all from SQL Mobile.

When the status data gets synchronised, triggers operate on the update/insert to create a record to indicate that the data should be exported from the system. BizTalk is responsible for extracting this data and looks into SQL every ten seconds for data to extract.

I am getting, on occasion, data that is incomplete. Whenever this occurs, the incomplete data is always from one of the two tables I have that are getting conflicts (see other post for this particular problem). When I go back and look at the data in the database, I see what I would have expected the extract process to get.

This to me indicates that there is a delay in the conflict resolution. (Custom - subscriber always wins).

Let me know what you need if this is not enough.

Deferrend Name Resolution for a field in a Stored Procedure

We are doing an upgrade in about a month and changing the account structure
in one of our tables. I am trying to write a routine that will check to see
if we are using the old format or the new format. So I wrote the following
stored procedure:
CREATE PROCEDURE [dbo].[sp_Account_Info] AS
if exists(select COLUMN_NAME = convert(sysname,name) from syscolumns where
name ='ACTNUMBR_6')
begin
select ACTNUMBR_5,ACTNUMBR_6 from Account_Table
--Do more stuff
end
else
begin
select ACTNUMBR_4 from Account_Table
--Do more stuff
end
In the old format the columns stop at ACTUNUMBR_4, but in the new table
structure(Which has not been implmented yet) we will be adding ACTNUMBR_5
and ACTNUMBR_6. The problem I am having is SQLServer will not let me save
this stored procedure because it says I have an invalid column name. Is
there a way to save the stored procedure even though the new columns do not
yet exist?You could use dynamic SQL (www.sommarskog.se), but perhaps you should consid
er a stable data model
instead, which doesn't require you to add columns over time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Miles C" <mcousens@.clearwater.ca> wrote in message news:eZmKRAHlGHA.408@.TK2MSFTNGP03.phx.g
bl...
> We are doing an upgrade in about a month and changing the account structur
e in one of our tables.
> I am trying to write a routine that will check to see if we are using the
old format or the new
> format. So I wrote the following stored procedure:
> CREATE PROCEDURE [dbo].[sp_Account_Info] AS
> if exists(select COLUMN_NAME = convert(sysname,name) from syscolumns where
name ='ACTNUMBR_6')
> begin
> select ACTNUMBR_5,ACTNUMBR_6 from Account_Table
> --Do more stuff
> end
> else
> begin
> select ACTNUMBR_4 from Account_Table
> --Do more stuff
> end
> In the old format the columns stop at ACTUNUMBR_4, but in the new table st
ructure(Which has not
> been implmented yet) we will be adding ACTNUMBR_5 and ACTNUMBR_6. The pro
blem I am having is
> SQLServer will not let me save this stored procedure because it says I hav
e an invalid column
> name. Is there a way to save the stored procedure even though the new col
umns do not yet exist?
>

Deferred Name Resolution gone wild.

I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
RegardsHello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards

Deferred Name Resolution gone wild.

I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
Regards
Hello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.

> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards

Deferred Name Resolution gone wild.

I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
RegardsHello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.

> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards