Internet integration: rowguid

Showing posts with label rowguid. Show all posts

Monday, March 12, 2012

Invalid column name 'rowguid'.

Hello ,
I scripted the creation of my Replication ,
Then droped the Replication ,
Added 1 or 2 new Fields in 5 existing tables
and then Recreated the Replication using the scripted File.
The Replication is Created Succesfully but I got an Error when running
the
Snapshot Agent
The Error Is
Invalid column name 'rowguid'.
Can Anyone Help Please
Thanks
Savvas
I set the @.keep_partition_changes = N'false' in the Create
Replication Script
then run snapshot and it worked

invalid column name 'rowguid'

Hi all,

I'm new to replication and database management so I will like some understanding and guiding as this is a new task for me.

I was given a copy of a database with merge replication on it and doing allot of reading and using some scripts I cleaned replication (or so Im thinking) and tryed to use it again on the ms-access front end. When I try to update some data I get "invalid column name 'rowguid'" , but no such column exist in my database as it was dropped.

Any help on this?

thanks George.

You refer to rowguid column either with its real column name or ROWGUIDCOL identifier. ie:

create table myTable ( pkid uniqueidentifier rowguid, othercolumn int )

select pkid, othercolumn from myTable

select ROWGUIDCOL, othercolumn from mytable

|||

When you setup merge replication, it adds a guid column to your table article. You scripts may have dropped the column when you clean up the replication and your front end may still have dependency to that column. Perhap, add a dummy column with same name and type to see if that would get your access front end to work.

Regards,

Gary

|||

As Gary mentioned most likely the column is removed when you cleaned up replication.

It is better to update your front end to not reference this non-existant column.

|||

Thanks for the replies.

The database I was given had a pull subscription on it. With the scripts it was cleared. In my access code using breakpoints I did not find any references to this columns in places where the code steps into.

The code I found and used to clean all replication (indexes, rowguid column etc.) in the database follows:

************************************

UPDATE sysobjects set replinfo=0
GO

DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name
like 'syncobj_%' or name like 'ctsv_%' or name
like 'tsvw_%')

OPEN list_views

FETCH NEXT FROM list_views INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping View ' +@.name
select @.name='drop View ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_views INTO @.name
END

CLOSE list_views
DEALLOCATE list_views

DECLARE @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='p' and (name
like 'sp_ins_%' or name like 'sp_MSdel_%' or name
like 'sp_MSins_%'or name like 'sp_MSupd_%' or name
like 'sp_sel_%' or name like 'sp_upd_%')

OPEN list_procs

FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping procs ' +@.name
select @.name='drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END

CLOSE list_procs
DEALLOCATE list_procs

DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name
like '_onflict%'

OPEN list_conflict_tables

FETCH NEXT FROM list_conflict_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping conflict_tables ' +@.name
select @.name='drop Table ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_conflict_tables INTO @.name
END

CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables

Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_constraints CURSOR FOR
select object_name(sysobjects.parent_obj), sysobjects.name
from sysobjects, syscolumns where sysobjects.type ='d'
and syscolumns.id=sysobjects.parent_obj
and syscolumns.name='rowguid'

OPEN list_rowguid_constraints

FETCH NEXT FROM list_rowguid_constraints INTO @.name,
@.constraint
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid constraints ' +@.name
select @.name='ALTER TABLE ' + rtrim(@.name ) + '
DROP CONSTRAINT ' +@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_constraints INTO
@.name, @.constraint
END

CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints

Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select object_name(id), name from sysindexes where name
like 'index%'

OPEN list_rowguid_indexes

FETCH NEXT FROM list_rowguid_indexes INTO @.name,
@.constraint
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid indexes ' +@.name
select @.name='drop index ' + rtrim(@.name ) + '.'
+@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_indexes INTO @.name,
@.constraint
END

CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes

GO
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select object_name(syscolumns.id) from syscolumns,
sysobjects where syscolumns.name like 'rowguid'
and sysobjects.id=syscolumns.id
and sysobjects.type='u' order by 1

OPEN list_rowguid_columns

FETCH NEXT FROM list_rowguid_columns INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@.name
select @.name='Alter Table ' + rtrim(@.name ) + '
drop column rowguid'
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_columns INTO @.name
END

CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns

DELETE FROM sysmergepublications
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM syssubscriptions
GO
DELETE FROM sysarticleupdates
GO
DELETE FROM systranschemas
GO
DELETE FROM sysmergearticles
GO
DELETE FROM sysmergeschemaarticles
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM sysarticles
GO
DELETE FROM sysschemaarticles
GO
DELETE FROM syspublications
GO
DELETE FROM sysmergeschemachange
GO
DELETE FROM sysmergesubsetfilters
GO
DELETE FROM MSdynamicsnapshotjobs
GO
DELETE FROM MSdynamicsnapshotviews
GO
DELETE FROM MSmerge_altsyncpartners
GO
DELETE FROM MSmerge_contents
GO
DELETE FROM MSmerge_delete_conflicts
GO
DELETE FROM MSmerge_errorlineage
GO
DELETE FROM MSmerge_genhistory
GO
DELETE FROM MSmerge_replinfo
GO
DELETE FROM MSmerge_tombstone
GO
DELETE FROM MSpub_identity_range
GO
DELETE FROM MSrepl_identity_range
GO
DELETE FROM MSreplication_subscriptions
GO
DELETE FROM MSsubscription_agents
GO

****************************************************************************************

DECLARE @.name varchar(129)
DECLARE list_pubs CURSOR FOR
SELECT name FROM sysmergepublications

OPEN list_pubs

FETCH NEXT FROM list_pubs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
/*
sp_dropmergesubscription [ [ @.publication = ] 'publication' ]
[ , [ @.subscriber = ] 'subscriber'
[ , [ @.subscriber_db = ] 'subscriber_db' ]
[ , [ @.subscription_type = ] 'subscription_type' ]
[ , [ @.ignore_distributor = ] ignore_distributor ]
[ , [ @.reserved = ] reserved
*/
PRINT 'dropping publication ' +@.name
EXEC sp_dropmergesubscription @.publication=@.name,
@.subscriber ='all'
EXEC sp_dropmergepublication @.name
FETCH NEXT FROM list_pubs INTO @.name
END

CLOSE list_pubs
DEALLOCATE list_pubs
GO

DECLARE @.name varchar(129)
DECLARE list_replicated_tables CURSOR FOR
SELECT name FROM sysobjects WHERE replinfo <>0
UNION
SELECT name FROM sysmergearticles

OPEN list_replicated_tables

FETCH NEXT FROM list_replicated_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'unmarking replicated table ' +@.name
--select @.name='drop Table ' + @.name
EXEC sp_msunmarkreplinfo @.name
FETCH NEXT FROM list_replicated_tables INTO @.name
END

CLOSE list_replicated_tables
DEALLOCATE list_replicated_tables

UPDATE syscolumns set colstat = colstat & ~4096 WHERE
colstat &4096 <>0
GO
UPDATE sysobjects set replinfo=0
GO

DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name
like 'syncobj_%' or name like 'ctsv_%' or name
like 'tsvw_%')

OPEN list_views

CLOSE list_views
DEALLOCATE list_views

OPEN list_procs

CLOSE list_procs
DEALLOCATE list_procs

DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name
like '_onflict%'

OPEN list_conflict_tables

CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables

UPDATE syscolumns set colstat=2 WHERE name='rowguid'

OPEN list_rowguid_constraints

CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints

Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select object_name(id), name from sysindexes where name
like 'index%'

OPEN list_rowguid_indexes

CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes

GO
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select object_name(syscolumns.id) from syscolumns,
sysobjects where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysobjects.type='u' order by 1

OPEN list_rowguid_columns

CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns

DELETE FROM sysmergepublications
GO
DELETE FROM sysmergesubscriptions
GO

DELETE FROM sysmergearticles
GO
DELETE FROM sysmergeschemaarticles
GO
DELETE FROM sysmergeschemachange
GO

DELETE ...

*************************************************************************

These are the two scripts

I'm willing to repeat this with new directions to clean up the database if someone has an understantable way to do this.

My version:

/*
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38 Copyright (c) 1988-2003
Microsoft Corporation Personal Edition on Windows NT 5.1
(Build 2600: Service Pack 2)
SP4

This 2 scripts were run on my database.

Thanks allot

|||

The table does not have the rowguid column.

Your front end is still thinking that the column exists and is either trying to insert/update/delete or access it. Check your front end code.

For eg: originally table was: Employee (Name varchar(20), id int, rowguid uniqueidentifier rowguidcol).

and your front end was doing: insert into Employee (Name, id, rowguid) values ('Bill', 1, newid())

or doing: select * from Employee

After you removed replication: table is Employee (Name varchar(20), id int)

Now with this schema, if you try to insert or access the missing column, you will get the above error.

|||

The issue has nothing to do with the front end. I replicated the issue as follows:

Take a database replicated with a merge publication and a push subscription, and (with DTS) copy the database to a SQL Server that does not have Replication configured.

Run the script listed in a previous reply to this posting.

Run any valid SQL that alters the data and makes no reference to the rowguid column.

This produces the error in question.

The solution lies in the Triggers. It appears that Replication either adds or modifies triggers that the scripts fail to remove or fix.

I came up with this code to add to the script, but the catch is that if you've got your own triggers that start "ins_", "del_" and "upd_", this will delete them as well. So you may want to change the name of some of your triggers, first, if you use this naming convention:

DECLARE @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='tr' and (name
like 'ins_%' or name
like 'del_%' or name
like 'upd_%')

OPEN list_procs

FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping Triggers ' +@.name
select @.name='drop trigger ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END

CLOSE list_procs
DEALLOCATE list_procs
go

|||So essentially the scripts did not cleanup replication from the database. I believe this is SQL 2000. There is a system stored procedure sp_removedbreplication that will help remove traces of replication from a database. However it is also know that in SQL 2000, this sp may not be completely removing the trace in some cases. It has been improved significantly in SQL 2005. Not that this posting will help resolve the issue, but posting it just for information.|||

Thanks to all.

I managed to go over that problem (it had nothing to do with the front end!) I found some scripts at http://www.replicationanswers.com that were very helpfull.

invalid column name 'rowguid'

Hi all,

I'm new to replication and database management so I will like some understanding and guiding as this is a new task for me.

Any help on this?

thanks George.

You refer to rowguid column either with its real column name or ROWGUIDCOL identifier. ie:

create table myTable ( pkid uniqueidentifier rowguid, othercolumn int )

select pkid, othercolumn from myTable

select ROWGUIDCOL, othercolumn from mytable

|||

Regards,

Gary

|||

As Gary mentioned most likely the column is removed when you cleaned up replication.

It is better to update your front end to not reference this non-existant column.

|||

Thanks for the replies.

The code I found and used to clean all replication (indexes, rowguid column etc.) in the database follows:

************************************

UPDATE sysobjects set replinfo=0
GO

DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name
like 'syncobj_%' or name like 'ctsv_%' or name
like 'tsvw_%')

OPEN list_views

CLOSE list_views
DEALLOCATE list_views

OPEN list_procs

CLOSE list_procs
DEALLOCATE list_procs

DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name
like '_onflict%'

OPEN list_conflict_tables

CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables

OPEN list_rowguid_constraints

CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints

Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select object_name(id), name from sysindexes where name
like 'index%'

OPEN list_rowguid_indexes

CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes

OPEN list_rowguid_columns

CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns

****************************************************************************************

DECLARE @.name varchar(129)
DECLARE list_pubs CURSOR FOR
SELECT name FROM sysmergepublications

OPEN list_pubs

CLOSE list_pubs
DEALLOCATE list_pubs
GO

DECLARE @.name varchar(129)
DECLARE list_replicated_tables CURSOR FOR
SELECT name FROM sysobjects WHERE replinfo <>0
UNION
SELECT name FROM sysmergearticles

OPEN list_replicated_tables

CLOSE list_replicated_tables
DEALLOCATE list_replicated_tables

UPDATE syscolumns set colstat = colstat & ~4096 WHERE
colstat &4096 <>0
GO
UPDATE sysobjects set replinfo=0
GO

DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name
like 'syncobj_%' or name like 'ctsv_%' or name
like 'tsvw_%')

OPEN list_views

CLOSE list_views
DEALLOCATE list_views

OPEN list_procs

CLOSE list_procs
DEALLOCATE list_procs

DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name
like '_onflict%'

OPEN list_conflict_tables

CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables

UPDATE syscolumns set colstat=2 WHERE name='rowguid'

OPEN list_rowguid_constraints

CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints

Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select object_name(id), name from sysindexes where name
like 'index%'

OPEN list_rowguid_indexes

CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes

GO
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select object_name(syscolumns.id) from syscolumns,
sysobjects where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysobjects.type='u' order by 1

OPEN list_rowguid_columns

CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns

DELETE FROM sysmergepublications
GO
DELETE FROM sysmergesubscriptions
GO

DELETE FROM sysmergearticles
GO
DELETE FROM sysmergeschemaarticles
GO
DELETE FROM sysmergeschemachange
GO

DELETE ...

*************************************************************************

These are the two scripts

I'm willing to repeat this with new directions to clean up the database if someone has an understantable way to do this.

My version:

/*
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38 Copyright (c) 1988-2003
Microsoft Corporation Personal Edition on Windows NT 5.1
(Build 2600: Service Pack 2)
SP4

This 2 scripts were run on my database.

Thanks allot

|||

The table does not have the rowguid column.

Your front end is still thinking that the column exists and is either trying to insert/update/delete or access it. Check your front end code.

For eg: originally table was: Employee (Name varchar(20), id int, rowguid uniqueidentifier rowguidcol).

and your front end was doing: insert into Employee (Name, id, rowguid) values ('Bill', 1, newid())

or doing: select * from Employee

After you removed replication: table is Employee (Name varchar(20), id int)

Now with this schema, if you try to insert or access the missing column, you will get the above error.

|||

The issue has nothing to do with the front end. I replicated the issue as follows:

Take a database replicated with a merge publication and a push subscription, and (with DTS) copy the database to a SQL Server that does not have Replication configured.

Run the script listed in a previous reply to this posting.

Run any valid SQL that alters the data and makes no reference to the rowguid column.

This produces the error in question.

The solution lies in the Triggers. It appears that Replication either adds or modifies triggers that the scripts fail to remove or fix.

DECLARE @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='tr' and (name
like 'ins_%' or name
like 'del_%' or name
like 'upd_%')

OPEN list_procs

CLOSE list_procs
DEALLOCATE list_procs
go

Thanks to all.

I managed to go over that problem (it had nothing to do with the front end!) I found some scripts at http://www.replicationanswers.com that were very helpfull.

Invalid column name 'rowguid'

I'm trying to set up a merge publication in MSSQL 2005 SP1.

I have a database which I have successfully made into a merge publication. When I run the snapshot agent, several articles are processed successfully, then I encounter an error with the following: Invalid column name 'rowguid'

Note that none of the tables contain 'rowguid' columns before this process - I let MSSQL automatically create these as needed.

The replication monitor details the following error:

Error messages:

· Message: Invalid column name 'rowguid'.
Command Text: sp_MSpublicationview
Parameters: @.publication = RelMS Publication
Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate)
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.MakePublicationViews()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoRegularMergeSnapshotPreparations()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPreArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615

· Server [XXXX\XXXXXX], Level 16, State 1, Procedure , Line 1
Invalid column name 'rowguid'. (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615

Can anyone shed further light on this?

Thanks.

Further note: I downloaded and applied MSSQL 2005 SP2 just now and started process from scratch (i.e. restored clean database with no replication, then created publication successfully, then ran snapshot agent). Exactly same error was encountered.

Further details that might be relevant:

Publisher and Distributor are set to same. Error persists when compatibility is 2005 or 2000.

Unfortunately the error report doesn't indicate which article was being processed when the error occurred, and the list of successfully processed articles in the log doesn't seem to follow any discernible order.

Any suggestions?

|||

Hi Ramaj;

What are the values for @.keep_partition_changes and @.use_partition_groups parameters in your replication creation script.

|||

Hi Hakan,

@.keep_partition_changes = N'true'

@.use_partition_groups not specified

Thanks

|||

Hi Ramaj;

Could you please try to set @.keep_partition_changes = N'false' then retry to run snapshot.

If the same error occours please try again by adding @.use_partition_groups= N'true' and @.keep_partition_changes = N'false' .

|||

Thanks Hakan, the first step by itself (i.e. @.keep_partition_changes = N'false') did the trick and the snapshot completed successfully. What a strange and misleading error message though - completely had me barking up the wrong tree.

Your help was much appreciated!

|||

I would still be ineterested in finding out what is causing the problem.

Can you run snapshot with higher outputverboseLevel 2. That should tell you which article you have issues with. Then isolating that article in a sepearate publication would help. Also the article schema, publication and article script would really help us troubleshoot the problem.

|||

It's very easy to repro this issue.

Create a one table db with two cols one for filtering.

Below script creates db, table and replication. But the key is to setting keep_partiton_changes = true.

-- CREATE DB
USE [master]
GO
/****** Object: Database [REPLTEST] Script Date: 02/26/2007 10:15:31 ******/
CREATE DATABASE [REPLTEST] ON PRIMARY
( NAME = N'REPLTEST', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\REPLTEST.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'REPLTEST_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\REPLTEST_log.ldf' , SIZE = 3840KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Turkish_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @.dbname=N'REPLTEST', @.new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [REPLTEST].[dbo].[sp_fulltext_database] @.action = 'disable'
end
GO
ALTER DATABASE [REPLTEST] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [REPLTEST] SET ANSI_NULLS OFF
GO
ALTER DATABASE [REPLTEST] SET ANSI_PADDING OFF
GO
ALTER DATABASE [REPLTEST] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [REPLTEST] SET ARITHABORT OFF
GO
ALTER DATABASE [REPLTEST] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [REPLTEST] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [REPLTEST] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [REPLTEST] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [REPLTEST] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [REPLTEST] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [REPLTEST] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [REPLTEST] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [REPLTEST] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [REPLTEST] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [REPLTEST] SET ENABLE_BROKER
GO
ALTER DATABASE [REPLTEST] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [REPLTEST] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [REPLTEST] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [REPLTEST] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [REPLTEST] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [REPLTEST] SET READ_WRITE
GO
ALTER DATABASE [REPLTEST] SET RECOVERY SIMPLE
GO
ALTER DATABASE [REPLTEST] SET MULTI_USER
GO
ALTER DATABASE [REPLTEST] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [REPLTEST] SET DB_CHAINING OFF
GO

-CREATE TABLE
USE REPLTEST
CREATE TABLE [dbo].[TBLTEST](
Angel [nvarchar](50) COLLATE Turkish_CI_AS NULL,
[UID] [nvarchar](10) COLLATE Turkish_CI_AS NULL,
) ON [PRIMARY]

CREATE REPL
-- Enabling the replication database
use master
exec sp_replicationdboption @.dbname = N'REPLTEST', @.optname = N'merge publish', @.value = N'true'
GO

-- Adding the merge publication
use [REPLTEST]
exec sp_addmergepublication @.publication = N'REPLTEST', @.description = N'Merge publication of REPLTEST database from Publisher CCIMOBILE.', @.sync_mode = N'character', @.retention = 14, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.dynamic_filters = N'true', @.conflict_retention = 14, @.keep_partition_changes = N'true', @.allow_synctoalternate = N'false', @.validate_subscriber_info = N'HOST_NAME()', @.max_concurrent_merge = 10, @.max_concurrent_dynamic_snapshots = 0, @.use_partition_groups = N'false', @.publication_compatibility_level = N'90RTM', @.replicate_ddl = 1, @.allow_subscriber_initiated_snapshot = N'true', @.allow_web_synchronization = N'false', @.allow_partition_realignment = N'true', @.retention_period_unit = N'days', @.conflict_logging = N'publisher', @.automatic_reinitialization_policy = 0
GO

exec sp_addpublication_snapshot @.publication = N'REPLTEST', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'sa'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'HQMRKT\SQLServer2005SQLAgentUser$HQMRKT$SQL2005'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'HQMRKT\SQLServer2005MSSQLUser$HQMRKT$SQL2005'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'distributor_admin'
GO

-- Adding the merge articles
use [REPLTEST]
exec sp_addmergearticle @.publication = N'REPLTEST', @.article = N'TBLTEST', @.source_owner = N'dbo', @.source_object = N'TBLTEST', @.type = N'table', @.description = N'', @.creation_script = N'', @.pre_creation_cmd = N'drop', @.schema_option = 0x000000003000CFF1, @.identityrangemanagementoption = N'none', @.destination_owner = N'dbo', @.force_reinit_subscription = 1, @.column_tracking = N'true', @.subset_filterclause = N'UID=HOST_NAME()', @.vertical_partition = N'false', @.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true', @.check_permissions = 0, @.subscriber_upload_options = 2, @.delete_tracking = N'true', @.compensate_for_errors = N'false', @.stream_blob_columns = N'false', @.partition_options = 0
GO

use [REPLTEST]
exec sp_changemergepublication N'REPLTEST', N'status', N'active'
GO

|||

Here is the verbose log of the snapshot agent:

2007-02-26 08:38:58.98
2007-02-26 08:38:58.98 Microsoft (R) SQL Server Snapshot Agent
2007-02-26 08:38:58.98 [Assembly Version = 9.0.242.0, File Version = 9.00.2047.00]
2007-02-26 08:38:58.98 Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
2007-02-26 08:38:58.98 The timestamps prepended to the output lines are expressed in terms of UTC time.
2007-02-26 08:38:58.98 User-specified agent parameter values:
2007-02-26 08:38:58.98 --
2007-02-26 08:38:58.98 -Publisher HQMRKT\SQL2005
2007-02-26 08:38:58.98 -PublisherDB REPLTEST
2007-02-26 08:38:58.98 -Publication REPLTEST
2007-02-26 08:38:58.98 -ReplicationType 2
2007-02-26 08:38:58.98 -Distributor HQMRKT\SQL2005
2007-02-26 08:38:58.98 -DistributorSecurityMode 1
2007-02-26 08:38:58.98 -Output C:\ReplOutput.txt
2007-02-26 08:38:58.98 -OutputVerboseLevel 2
2007-02-26 08:38:58.98 -XJOBID 0xEC583CE49E702944B367AF1E70053178
2007-02-26 08:38:58.98 --
2007-02-26 08:38:58.98 Connecting to Distributor 'HQMRKT\SQL2005'
2007-02-26 08:38:59.20
2007-02-26 08:38:59.20 SQL Command dump
2007-02-26 08:38:59.20 ================
2007-02-26 08:38:59.20 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.20 Database: master
2007-02-26 08:38:59.20 Command Text: sp_helpdistpublisher
2007-02-26 08:38:59.20 Parameters:
2007-02-26 08:38:59.20 @.publisher = HQMRKT\SQL2005
2007-02-26 08:38:59.20
2007-02-26 08:38:59.20 SQL Command dump
2007-02-26 08:38:59.20 ================
2007-02-26 08:38:59.20 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.20 Database: distribution
2007-02-26 08:38:59.20 Command Text: select 'id' = convert(int, srvid) from master..sysservers where upper(srvname) = upper(N'HQMRKT\SQL2005')
2007-02-26 08:38:59.20 Parameters:
2007-02-26 08:38:59.21
2007-02-26 08:38:59.21 SQL Command dump
2007-02-26 08:38:59.21 ================
2007-02-26 08:38:59.21 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.21 Database: distribution
2007-02-26 08:38:59.21 Command Text: sp_MShelp_snapshot_agentid
2007-02-26 08:38:59.21 Parameters:
2007-02-26 08:38:59.21 @.publisher_id = 0
2007-02-26 08:38:59.21 @.publisher_db = REPLTEST
2007-02-26 08:38:59.21 @.publication = REPLTEST
2007-02-26 08:38:59.21 @.job_id = System.Byte[]
2007-02-26 08:38:59.21
2007-02-26 08:38:59.21 SQL Command dump
2007-02-26 08:38:59.21 ================
2007-02-26 08:38:59.21 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.21 Database: distribution
2007-02-26 08:38:59.21 Command Text: sp_MShelp_snapshot_agent
2007-02-26 08:38:59.21 Parameters:
2007-02-26 08:38:59.21 @.agent_id = 1244
2007-02-26 08:38:59.23
2007-02-26 08:38:59.23 SQL Command dump
2007-02-26 08:38:59.23 ================
2007-02-26 08:38:59.23 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.23 Database: distribution
2007-02-26 08:38:59.23 Command Text: sp_MShelp_profile
2007-02-26 08:38:59.23 Parameters:
2007-02-26 08:38:59.23 @.agent_id = 1244
2007-02-26 08:38:59.23 @.agent_type = 1
2007-02-26 08:38:59.23 @.profile_name =
2007-02-26 08:38:59.23 Parameter values obtained from agent profile:
2007-02-26 08:38:59.23
2007-02-26 08:38:59.23 -BcpBatchSize 100000
2007-02-26 08:38:59.23 -HistoryVerboseLevel 2
2007-02-26 08:38:59.23 -LoginTimeout 15
2007-02-26 08:38:59.23 -QueryTimeout 1800
2007-02-26 08:38:59.23
2007-02-26 08:38:59.26 Connecting to Publisher 'HQMRKT\SQL2005'
2007-02-26 08:38:59.29
2007-02-26 08:38:59.29 SQL Command dump
2007-02-26 08:38:59.29 ================
2007-02-26 08:38:59.29 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.29 Database: REPLTEST
2007-02-26 08:38:59.29 Command Text: use master
2007-02-26 08:38:59.29 select isnull(convert(int, value_in_use), 0) from master.sys.configurations where lower(name) = 'smo and dmo xps'
2007-02-26 08:38:59.29 use [REPLTEST]
2007-02-26 08:38:59.29 Parameters:
2007-02-26 08:38:59.29
2007-02-26 08:38:59.29 SQL Command dump
2007-02-26 08:38:59.29 ================
2007-02-26 08:38:59.29 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.29 Database: REPLTEST
2007-02-26 08:38:59.29 Command Text: use [REPLTEST]
2007-02-26 08:38:59.29 select publishingservername(), convert(int, compatibility_level) from sys.databases where name = @.database_name
2007-02-26 08:38:59.29 Parameters:
2007-02-26 08:38:59.29 @.database_name = REPLTEST
2007-02-26 08:38:59.31 Publisher database compatibility level is set to 90.
2007-02-26 08:38:59.31
2007-02-26 08:38:59.31 SQL Command dump
2007-02-26 08:38:59.31 ================
2007-02-26 08:38:59.31 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.31 Database: REPLTEST
2007-02-26 08:38:59.31 Command Text: select convert(int, isnull(is_member('db_owner'),0))
2007-02-26 08:38:59.31 Parameters:
2007-02-26 08:38:59.31
2007-02-26 08:38:59.31 SQL Command dump
2007-02-26 08:38:59.31 ================
2007-02-26 08:38:59.31 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.31 Database: REPLTEST
2007-02-26 08:38:59.31 Command Text:
2007-02-26 08:38:59.31 declare @.retcode int
2007-02-26 08:38:59.31 exec @.retcode = sys.sp_getapplock @.Resource = N'HQMRKT\SQL2005-REPLTEST-REPLTEST-1244',
2007-02-26 08:38:59.31 @.LockMode = N'Exclusive',
2007-02-26 08:38:59.31 @.LockOwner = N'Session',
2007-02-26 08:38:59.31 @.LockTimeout = 0,
2007-02-26 08:38:59.31 @.DbPrincipal = N'db_owner'
2007-02-26 08:38:59.31 if @.retcode < 0 raiserror(21036, 16, -1, 'snapshot')
2007-02-26 08:38:59.31 Parameters:
2007-02-26 08:38:59.31 Retrieving publication and article information from the publisher database 'HQMRKT\SQL2005.REPLTEST'
2007-02-26 08:38:59.31
2007-02-26 08:38:59.31 SQL Command dump
2007-02-26 08:38:59.31 ================
2007-02-26 08:38:59.31 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.31 Database: REPLTEST
2007-02-26 08:38:59.31 Command Text: sp_helpmergepublication
2007-02-26 08:38:59.31 Parameters:
2007-02-26 08:38:59.31 @.publication = REPLTEST
2007-02-26 08:38:59.31 @.reserved = fromSnapshot
2007-02-26 08:38:59.32
2007-02-26 08:38:59.32 SQL Command dump
2007-02-26 08:38:59.32 ================
2007-02-26 08:38:59.32 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.32 Database: REPLTEST
2007-02-26 08:38:59.32 Command Text: sys.sp_MSset_dynamic_filter_options
2007-02-26 08:38:59.32 Parameters:
2007-02-26 08:38:59.32 @.publication = REPLTEST
2007-02-26 08:38:59.32 @.dynamic_filters = True
2007-02-26 08:38:59.39
2007-02-26 08:38:59.39 SQL Command dump
2007-02-26 08:38:59.39 ================
2007-02-26 08:38:59.39 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.39 Database: REPLTEST
2007-02-26 08:38:59.39 Command Text: declare @.ftp_password nvarchar(524) select @.ftp_password = ftp_password from dbo.sysmergepublications where name = N'REPLTEST' and publisher_db = N'REPLTEST' and upper(publisher) = upper(N'HQMRKT\SQL2005') if object_id('sys.fn_repldecryptver4', 'FN') is not null begin select sys.fn_repldecryptver4(@.ftp_password) end else begin select @.ftp_password end
2007-02-26 08:38:59.39 Parameters:
2007-02-26 08:38:59.39
2007-02-26 08:38:59.39 SQL Command dump
2007-02-26 08:38:59.39 ================
2007-02-26 08:38:59.39 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.39 Database: REPLTEST
2007-02-26 08:38:59.39 Command Text: sp_MSestimatemergesnapshotworkload
2007-02-26 08:38:59.39 Parameters:
2007-02-26 08:38:59.39 @.publication = REPLTEST
2007-02-26 08:38:59.40
2007-02-26 08:38:59.40 SQL Command dump
2007-02-26 08:38:59.40 ================
2007-02-26 08:38:59.40 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.40 Database: REPLTEST
2007-02-26 08:38:59.40 Command Text: sp_helpmergearticle
2007-02-26 08:38:59.40 Parameters:
2007-02-26 08:38:59.40 @.publication = REPLTEST
2007-02-26 08:38:59.42
2007-02-26 08:38:59.42 SQL Command dump
2007-02-26 08:38:59.42 ================
2007-02-26 08:38:59.42 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.42 Database: REPLTEST
2007-02-26 08:38:59.42 Command Text: sys.sp_MSget_load_hint
2007-02-26 08:38:59.42 Parameters:
2007-02-26 08:38:59.42 @.qualified_source_object_name = [dbo].[TBLTEST]
2007-02-26 08:38:59.42 @.qualified_sync_object_name = [dbo].[MSmerge_REPLTEST_TBLTEST_VIEW]
2007-02-26 08:38:59.42 @.primary_key_only = False
2007-02-26 08:38:59.42 @.is_vertically_partitioned = False
2007-02-26 08:38:59.42
2007-02-26 08:38:59.42 SQL Command dump
2007-02-26 08:38:59.42 ================
2007-02-26 08:38:59.42 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.42 Database: REPLTEST
2007-02-26 08:38:59.42 Command Text: sp_MSvalidate_wellpartitioned_articles
2007-02-26 08:38:59.42 Parameters:
2007-02-26 08:38:59.42 @.publication = REPLTEST
2007-02-26 08:38:59.42
2007-02-26 08:38:59.42 SQL Command dump
2007-02-26 08:38:59.42 ================
2007-02-26 08:38:59.42 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.42 Database: REPLTEST
2007-02-26 08:38:59.42 Command Text: sp_browsemergesnapshotfolder
2007-02-26 08:38:59.42 Parameters:
2007-02-26 08:38:59.42 @.publication = REPLTEST
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 SQL Command dump
2007-02-26 08:38:59.43 ================
2007-02-26 08:38:59.43 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.43 Database: REPLTEST
2007-02-26 08:38:59.43 Command Text: sys.sp_MSgetmergeadminapplock
2007-02-26 08:38:59.43 Parameters:
2007-02-26 08:38:59.43 @.timeout = -1
2007-02-26 08:38:59.43 @.lockmode = Exclusive
2007-02-26 08:38:59.43 @.lock_acquired = -1
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 SQL Command dump
2007-02-26 08:38:59.43 ================
2007-02-26 08:38:59.43 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.43 Database: REPLTEST
2007-02-26 08:38:59.43 Command Text: sp_MSenumpubreferences
2007-02-26 08:38:59.43 Parameters:
2007-02-26 08:38:59.43 @.publication = REPLTEST
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 SQL Command dump
2007-02-26 08:38:59.43 ================
2007-02-26 08:38:59.43 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.43 Database: REPLTEST
2007-02-26 08:38:59.43 Command Text:
2007-02-26 08:38:59.43 declare @.retcode int,
2007-02-26 08:38:59.43 @.resource sysname,
2007-02-26 08:38:59.43 @.DbPrincipal sysname,
2007-02-26 08:38:59.43 @.pubid uniqueidentifier
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 select @.resource = N'dynpub_' + convert(nvarchar(36), pubid), @.pubid = pubid
2007-02-26 08:38:59.43 from dbo.sysmergepublications
2007-02-26 08:38:59.43 where name = N'REPLTEST'
2007-02-26 08:38:59.43 and upper(publisher) = upper(publishingservername())
2007-02-26 08:38:59.43 and publisher_db = db_name()
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 execute sys.sp_MSrepl_FixPALRole @.pubid = @.pubid, @.role = @.DbPrincipal output
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 execute @.retcode = sys.sp_getapplock @.Resource = @.resource,
2007-02-26 08:38:59.43 @.LockOwner = N'Session',
2007-02-26 08:38:59.43 @.LockMode = N'Exclusive',
2007-02-26 08:38:59.43 @.LockTimeout = 0,
2007-02-26 08:38:59.43 @.DbPrincipal = @.DbPrincipal
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 select 'LockOutcome' = @.retcode, 'LockName' = @.resource, 'DbPrincipal' = @.DbPrincipal
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 Parameters:
2007-02-26 08:38:59.45 [0%] Preparing table '[dbo].[TBLTEST]' for merge replication
2007-02-26 08:38:59.45
2007-02-26 08:38:59.45 SQL Command dump
2007-02-26 08:38:59.45 ================
2007-02-26 08:38:59.45 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.45 Database: REPLTEST
2007-02-26 08:38:59.45 Command Text: SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION
2007-02-26 08:38:59.45 Parameters:
2007-02-26 08:38:59.45
2007-02-26 08:38:59.45 SQL Command dump
2007-02-26 08:38:59.45 ================
2007-02-26 08:38:59.45 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.45 Database: REPLTEST
2007-02-26 08:38:59.45 Command Text: SELECT * FROM [dbo].[TBLTEST] WITH (TABLOCK HOLDLOCK) WHERE 1 = 2
2007-02-26 08:38:59.45 Parameters:
2007-02-26 08:38:59.45
2007-02-26 08:38:59.45 SQL Command dump
2007-02-26 08:38:59.45 ================
2007-02-26 08:38:59.45 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.45 Database: REPLTEST
2007-02-26 08:38:59.45 Command Text: sp_MSprepare_mergearticle
2007-02-26 08:38:59.45 Parameters:
2007-02-26 08:38:59.45 @.source_owner = dbo
2007-02-26 08:38:59.45 @.source_table = TBLTEST
2007-02-26 08:38:59.45 @.publication = REPLTEST
2007-02-26 08:38:59.45 @.qualified_tablename = [dbo].[TBLTEST]
2007-02-26 08:38:59.46
2007-02-26 08:38:59.46 SQL Command dump
2007-02-26 08:38:59.46 ================
2007-02-26 08:38:59.46 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.46 Database: REPLTEST
2007-02-26 08:38:59.46 Command Text: COMMIT TRANSACTION
2007-02-26 08:38:59.46 Parameters:
2007-02-26 08:38:59.53
2007-02-26 08:38:59.53 SQL Command dump
2007-02-26 08:38:59.53 ================
2007-02-26 08:38:59.53 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.53 Database: REPLTEST
2007-02-26 08:38:59.53 Command Text: sp_MSpublicationview
2007-02-26 08:38:59.53 Parameters:
2007-02-26 08:38:59.53 @.publication = REPLTEST
2007-02-26 08:38:59.70
2007-02-26 08:38:59.70 SQL Command dump
2007-02-26 08:38:59.70 ================
2007-02-26 08:38:59.70 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.70 Database: REPLTEST
2007-02-26 08:38:59.70 Command Text: sys.sp_releaseapplock
2007-02-26 08:38:59.70 Parameters:
2007-02-26 08:38:59.70 @.Resource = HQMRKT\SQL2005-REPLTEST-REPLTEST-1244
2007-02-26 08:38:59.70 @.LockOwner = Session
2007-02-26 08:38:59.70 @.DbPrincipal = db_owner
2007-02-26 08:38:59.71 [3%] The replication agent had encountered an exception.
2007-02-26 08:38:59.71 Source: Replication
2007-02-26 08:38:59.71 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-02-26 08:38:59.71 Exception Message: Invalid column name 'rowguid'.
2007-02-26 08:38:59.71 Message Code: 4615
2007-02-26 08:38:59.71
2007-02-26 08:38:59.71 Call Stack:
2007-02-26 08:38:59.71 Microsoft.SqlServer.Replication.ReplicationAgentSqlException: Invalid column name 'rowguid'. > System.Data.SqlClient.SqlException: Invalid column name 'rowguid'.
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
2007-02-26 08:38:59.71 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
2007-02-26 08:38:59.71 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
2007-02-26 08:38:59.71 End of inner exception stack trace
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.MakePublicationViews()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoRegularMergeSnapshotPreparations()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPreArticleFilesGenerationProcessing()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.Run()

invalid column name 'rowguid'

Hi all,

I'm new to replication and database management so I will like some understanding and guiding as this is a new task for me.

Any help on this?

thanks George.

You refer to rowguid column either with its real column name or ROWGUIDCOL identifier. ie:

create table myTable ( pkid uniqueidentifier rowguid, othercolumn int )

select pkid, othercolumn from myTable

select ROWGUIDCOL, othercolumn from mytable

|||

Regards,

Gary

|||

As Gary mentioned most likely the column is removed when you cleaned up replication.

It is better to update your front end to not reference this non-existant column.

|||

Thanks for the replies.

The code I found and used to clean all replication (indexes, rowguid column etc.) in the database follows:

************************************

UPDATE sysobjects set replinfo=0
GO

DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name
like 'syncobj_%' or name like 'ctsv_%' or name
like 'tsvw_%')

OPEN list_views

CLOSE list_views
DEALLOCATE list_views

OPEN list_procs

CLOSE list_procs
DEALLOCATE list_procs

DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name
like '_onflict%'

OPEN list_conflict_tables

CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables

OPEN list_rowguid_constraints

CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints

Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select object_name(id), name from sysindexes where name
like 'index%'

OPEN list_rowguid_indexes

CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes

OPEN list_rowguid_columns

CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns

****************************************************************************************

DECLARE @.name varchar(129)
DECLARE list_pubs CURSOR FOR
SELECT name FROM sysmergepublications

OPEN list_pubs

CLOSE list_pubs
DEALLOCATE list_pubs
GO

DECLARE @.name varchar(129)
DECLARE list_replicated_tables CURSOR FOR
SELECT name FROM sysobjects WHERE replinfo <>0
UNION
SELECT name FROM sysmergearticles

OPEN list_replicated_tables

CLOSE list_replicated_tables
DEALLOCATE list_replicated_tables

UPDATE syscolumns set colstat = colstat & ~4096 WHERE
colstat &4096 <>0
GO
UPDATE sysobjects set replinfo=0
GO

DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name
like 'syncobj_%' or name like 'ctsv_%' or name
like 'tsvw_%')

OPEN list_views

CLOSE list_views
DEALLOCATE list_views

OPEN list_procs

CLOSE list_procs
DEALLOCATE list_procs

DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name
like '_onflict%'

OPEN list_conflict_tables

CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables

UPDATE syscolumns set colstat=2 WHERE name='rowguid'

OPEN list_rowguid_constraints

CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints

Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select object_name(id), name from sysindexes where name
like 'index%'

OPEN list_rowguid_indexes

CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes

GO
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select object_name(syscolumns.id) from syscolumns,
sysobjects where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysobjects.type='u' order by 1

OPEN list_rowguid_columns

CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns

DELETE FROM sysmergepublications
GO
DELETE FROM sysmergesubscriptions
GO

DELETE FROM sysmergearticles
GO
DELETE FROM sysmergeschemaarticles
GO
DELETE FROM sysmergeschemachange
GO

DELETE ...

*************************************************************************

These are the two scripts

I'm willing to repeat this with new directions to clean up the database if someone has an understantable way to do this.

My version:

/*
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38 Copyright (c) 1988-2003
Microsoft Corporation Personal Edition on Windows NT 5.1
(Build 2600: Service Pack 2)
SP4

This 2 scripts were run on my database.

Thanks allot

|||

The table does not have the rowguid column.

Your front end is still thinking that the column exists and is either trying to insert/update/delete or access it. Check your front end code.

For eg: originally table was: Employee (Name varchar(20), id int, rowguid uniqueidentifier rowguidcol).

and your front end was doing: insert into Employee (Name, id, rowguid) values ('Bill', 1, newid())

or doing: select * from Employee

After you removed replication: table is Employee (Name varchar(20), id int)

Now with this schema, if you try to insert or access the missing column, you will get the above error.

|||

The issue has nothing to do with the front end. I replicated the issue as follows:

Take a database replicated with a merge publication and a push subscription, and (with DTS) copy the database to a SQL Server that does not have Replication configured.

Run the script listed in a previous reply to this posting.

Run any valid SQL that alters the data and makes no reference to the rowguid column.

This produces the error in question.

The solution lies in the Triggers. It appears that Replication either adds or modifies triggers that the scripts fail to remove or fix.

DECLARE @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='tr' and (name
like 'ins_%' or name
like 'del_%' or name
like 'upd_%')

OPEN list_procs

CLOSE list_procs
DEALLOCATE list_procs
go

Thanks to all.

I managed to go over that problem (it had nothing to do with the front end!) I found some scripts at http://www.replicationanswers.com that were very helpfull.

invalid column name rowguid

Hi all and I appreciate any help

I recently been assigned to a project that has an ms-access front end and sql server 2000 sp4 as the database engine.
The database was replicated (merge replication) but for some reason they drop replication and when they tried to put it back on they got allot of problems and so they paused it.
My task now is to make it work again. After allot of reading I desided to clean the database and start from the beginning. Using some scripts and instructions on a copy of the database I cleaned all the tables related to merge replication but when I went back to ms-access to update a record I got an error
"invalid column name 'rowguid'" . I checked all my tables in my database but I find not such column as it was dropped using the scripts.
Any ideas that I can use?

Thanks allot,
Georgehi ahain
Some more information
My version is:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38 Copyright (c) 1988-2003
Microsoft Corporation Personal Edition on Windows NT 5.1
(Build 2600: Service Pack 2)
SP4

The database I was given had a pull subscription on it that I removed. There is no table column rowguid in the ms-access code as I debuged and checked and there are no rowguid columns in the tables because they were cleaned.
My quess is that this message is an sql server exception.
If you need more information please ask me as these problem cannot let me move on.
I can try to clean the merge replication again if I' given some understantable guidance.
The database I'm working on has a pull merge subscription on it.
Thanks allot.

Monday, March 12, 2012

Invalid column name 'rowguid'.

invalid column name 'rowguid'

invalid column name 'rowguid'

Invalid column name 'rowguid'

invalid column name 'rowguid'

invalid column name rowguid

Internet integration

Blog Archive

About Me