Prev: how to tutorial for Replication
Next: SQL2005 - Snapshot agent won't start
From: Heather on 10 Jan 2006 13:22
I am working with SQL Server 2005 and having problems with the publisher.
After I installed SQL and setup my database, I setup the server as both the
distributor and the publisher. Then I setup a merge publication that worked
fine with SQL Mobile 2005, I didn't have any problems with synchronization
and everything seemed fine. I deleted that publication and went through the
New Publication Wizard to create a new publication, but at the end I get the
following message:

TITLE: New Publication Wizard
------------------------------
SQL Server could not create publication 'MRSTest'.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid object name 'dbo.sysmergepublications'.
Publication 'MRSTest' does not exist.
Changed database context to 'MRS'. (Microsoft SQL Server, Error: 208)
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server&ProdVer=09.00.
1399&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476

I looked and sure enough all those tables were gone out of my database under
System Tables. I found a stored procedure to recreated all those tables,
and although it did recreate them, it didn't fix the problem. I have trid
to disable Publisher but I get the following error:

TITLE: Publisher Properties
------------------------------
An error occurred while saving publication database.
Do you want to continue saving other changes?
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.
SqlServer.Management.UI.
PubshrPropertiesErrorSR&EvtID=ErrorSavingPubDB&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid object name 'dbo.sysmergesubscriptions'.
Changed database context to 'MRS'. (Microsoft SQL Server, Error: 208)
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server&ProdVer=09.00.
1399&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476

This happened one other time when I deleted a publication and I ended up
reinstalling SQL. This is just a test database so it really isn't a big
deal, it's just time consuming. Could this be happening because I am in
there a lot changing the Publication and Article properties? I will be
moving this to a production server soon and although I doubt I will be
deleting any Publications, just in case I really need to know how to fix it.


Any help would really be appreciated in fixing this on my development server
along with how to prevent it from happening in the future.

Thanks,
Heather
From: Hilary Cotter on 10 Jan 2006 22:03
Can you create a new publication with a different name?

--
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

"Heather B." <hbaker(a)pro-merchnospam.com> wrote in message
news:975c64b3907e4dbb9353419093e55256(a)ureader.com...
>I am working with SQL Server 2005 and having problems with the publisher.
> After I installed SQL and setup my database, I setup the server as both
> the
> distributor and the publisher. Then I setup a merge publication that
> worked
> fine with SQL Mobile 2005, I didn't have any problems with synchronization
> and everything seemed fine. I deleted that publication and went through
> the
> New Publication Wizard to create a new publication, but at the end I get
> the
> following message:
>
> TITLE: New Publication Wizard
> ------------------------------
> SQL Server could not create publication 'MRSTest'.
> ------------------------------
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> ------------------------------
> Invalid object name 'dbo.sysmergepublications'.
> Publication 'MRSTest' does not exist.
> Changed database context to 'MRS'. (Microsoft SQL Server, Error: 208)
> For help, click: http://go.microsoft.com/fwlink?
> ProdName=Microsoft+SQL+Server&ProdVer=09.00.
> 1399&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
>
> I looked and sure enough all those tables were gone out of my database
> under
> System Tables. I found a stored procedure to recreated all those tables,
> and although it did recreate them, it didn't fix the problem. I have trid
> to disable Publisher but I get the following error:
>
> TITLE: Publisher Properties
> ------------------------------
> An error occurred while saving publication database.
> Do you want to continue saving other changes?
> For help, click: http://go.microsoft.com/fwlink?
> ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.
> SqlServer.Management.UI.
> PubshrPropertiesErrorSR&EvtID=ErrorSavingPubDB&LinkId=20476
> ------------------------------
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> ------------------------------
> Invalid object name 'dbo.sysmergesubscriptions'.
> Changed database context to 'MRS'. (Microsoft SQL Server, Error: 208)
> For help, click: http://go.microsoft.com/fwlink?
> ProdName=Microsoft+SQL+Server&ProdVer=09.00.
> 1399&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
>
> This happened one other time when I deleted a publication and I ended up
> reinstalling SQL. This is just a test database so it really isn't a big
> deal, it's just time consuming. Could this be happening because I am in
> there a lot changing the Publication and Article properties? I will be
> moving this to a production server soon and although I doubt I will be
> deleting any Publications, just in case I really need to know how to fix
> it.
>
>
> Any help would really be appreciated in fixing this on my development
> server
> along with how to prevent it from happening in the future.
>
> Thanks,
> Heather


From: Heather on 11 Jan 2006 08:54
No, it doesn't matter what I name it I cannot create another merge
publication. I can create a snapshot publication, but that doesn't really
help.
From: Hilary Cotter on 11 Jan 2006 10:48
You shouldn't have to reinstall. You mention "I found a stored procedure to
recreated all those tables, and although it did recreate them, it didn't fix
the problem"

What was this proc and where did you find it?

--
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

"Heather B." <hbaker(a)pro-merchnospam.com> wrote in message
news:04535b948bf74617a5bdfcf4fba91355(a)ureader.com...
> No, it doesn't matter what I name it I cannot create another merge
> publication. I can create a snapshot publication, but that doesn't really
> help.


From: Heather on 12 Jan 2006 09:43
I found this in a forum somewhere, when I ran it all the tables seemed to be
there, but when I tried to create a new publication I still go an error.
This time it would say something about a field missing, I don't remember
which field but I do remember that I looked in the table and it was there.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER procedure [dbo].[sp_MScreate_mergesystables] as

/* This is to make sure that the varbinary columns do not get padded */

set ANSI_PADDING off

DECLARE @exist bit
DECLARE @validsubs int
select @exist = 1

begin tran
save transaction MScreate_mergesystables

exec dbo.sp_MScheckvalidsystables @validsubs output
if @validsubs = 0
exec dbo.sp_MSdrop_mergesystables

if not exists (select * from sysobjects where name = 'sysmergepublications')

begin

raiserror('Creating table sysmergepublications',0,1)

create table dbo.sysmergepublications
( publisher sysname NOT NULL default @@servername,
publisher_db sysname NOT NULL default db_name(),
name sysname NOT NULL,
description nvarchar(255) NULL,
retention int NULL,
publication_type tinyint NULL,
pubid uniqueidentifier NOT NULL,
designmasterid uniqueidentifier NULL,
parentid uniqueidentifier NULL,
sync_mode tinyint NULL,
allow_push int NULL,
allow_pull int NULL,
allow_anonymous int NULL,
centralized_conflicts int NULL,
status tinyint NULL,
snapshot_ready tinyint NULL,
enabled_for_internet bit NOT NULL default 0,
dynamic_filters bit NOT NULL default 0,
-- portable snapshot support
snapshot_in_defaultfolder bit NOT NULL default
1,
alt_snapshot_folder nvarchar(255) NULL,
-- Pre/post - snapshot commands
pre_snapshot_script nvarchar(255) NULL,
post_snapshot_script nvarchar(255) NULL,
-- Snapshot compression
compress_snapshot bit NOT NULL default
0,
-- Post 7.0 Ftp support
ftp_address sysname NULL,
ftp_port int NOT NULL default
21,
ftp_subdirectory nvarchar(255) NULL,
ftp_login sysname NULL default
N'anonymous',
ftp_password nvarchar(524) NULL,
conflict_retention int NULL,
keep_before_values int NULL default 0,
allow_subscription_copy bit NULL default 0,
allow_synctoalternate bit NULL default 0,
validate_subscriber_info nvarchar(500) NULL,
ad_guidname sysname NULL,
backward_comp_level int not NULL default 10, --7.0 RTM
max_concurrent_merge int not NULL default 0,
max_concurrent_dynamic_snapshots int not NULL default
0
)
if @@ERROR <> 0
goto Error

CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergepublications
ON sysmergepublications(pubid)
if @@ERROR <> 0
goto Error

exec dbo.sp_MS_marksystemobject sysmergepublications
if @@ERROR <> 0
goto Error
-- grant select on sysmergepublications to public
end

if not exists (select * from sysobjects where name = 'MSmerge_errorlineage')

begin
raiserror('Creating table MSmerge_errorlineage',0,1)

create table dbo.MSmerge_errorlineage (
tablenick int NOT NULL,
rowguid uniqueidentifier NOT NULL,
lineage varbinary(255)
)
exec dbo.sp_MS_marksystemobject MSmerge_errorlineage
if @@ERROR <> 0
goto Error
create unique clustered index uc1errorlineage on
MSmerge_errorlineage(tablenick, rowguid)
if @@ERROR <> 0
goto Error

--grant select on MSmerge_errorlineage to public
end

-- this table exists at distribution/db and subscriber databse both
if not exists (select * from sysobjects where name =
'MSrepl_identity_range')
begin
raiserror('Creating table MSrepl_identity_range',0,1)

create table dbo.MSrepl_identity_range (
objid int not NULL primary key,
next_seed bigint NULL, --resource control
pub_range bigint NULL, --publisher range
range bigint NULL, -- set by sp_addmergearticle
max_identity bigint NULL, --resource control
threshold int NULL, --in percentage, set by sp_addmergearticle
current_max bigint NULL --max value for current check constraint,set
by sp_addmergearticle
)
exec dbo.sp_MS_marksystemobject MSrepl_identity_range
if @@ERROR <> 0
goto Error

--grant select on MSrepl_identity_range to public
end

if not exists (select * from sysobjects where name = 'sysmergearticles')
begin

raiserror('Creating table sysmergearticles',0,1)


create table dbo.sysmergearticles (
name sysname NOT NULL,
type tinyint NULL,
objid int NOT NULL,
sync_objid int NOT NULL,
view_type tinyint NULL,
artid uniqueidentifier NOT NULL,
description nvarchar(255) NULL,
pre_creation_command tinyint NULL,
pubid uniqueidentifier NOT NULL,
nickname int NOT NULL,
column_tracking int NOT NULL,
status tinyint NULL,
conflict_table sysname NULL,
creation_script nvarchar(255) NULL,
conflict_script nvarchar(255) NULL,
article_resolver nvarchar(255) NULL,
ins_conflict_proc sysname NULL,
insert_proc sysname NULL,
update_proc sysname NULL,
select_proc sysname NULL,
schema_option binary(8) NULL,
destination_object sysname NOT NULL,
destination_owner sysname NULL,
resolver_clsid nvarchar(50) NULL,
subset_filterclause nvarchar(1000) NULL,
missing_col_count int NULL,
missing_cols varbinary(128) NULL,
excluded_cols varbinary(128) NULL,
excluded_col_count int not NULL default 0,
columns varbinary(128) NULL,
resolver_info nvarchar(255) NULL,
view_sel_proc nvarchar(290) NULL,
gen_cur int NULL,
vertical_partition int not NULL default 0,
identity_support int not NULL default 0,
before_image_objid int NULL,
before_view_objid int NULL,
verify_resolver_signature int NULL,
allow_interactive_resolver bit NOT NULL default 0,
fast_multicol_updateproc bit NOT NULL default 0,
check_permissions int NOT NULL default 0,
maxversion_at_cleanup int NOT NULL default 1,
published_in_tran_pub bit NOT NULL default 0

-- Note: Please update sysmergeextendedarticlesview
whenever
-- there is a schema change in sysmergearticles
)

if @@error<>0
goto Error
else
begin

create unique clustered index uc1sysmergearticles
on sysmergearticles(artid, pubid)
if @@ERROR <> 0
goto Error

if not exists (select * from sysindexes where name =
'nc1sysmergearticles')
begin
create nonclustered index nc1sysmergearticles on
sysmergearticles(nickname)
if @@ERROR <> 0
return 1
end
end
exec dbo.sp_MS_marksystemobject sysmergearticles
if @@ERROR <> 0
goto Error
grant select(nickname,gen_cur,maxversion_at_cleanup) on sysmergearticles
to public

end

if not exists (select * from sysobjects where name =
'sysmergeschemaarticles')
begin

-- The extended merge articles table is for storing
-- schema only article information.
-- The current supported schema only articles are
-- stored procs and views.
raiserror('Creating table sysmergeschemaarticles',0,1)

create table dbo.sysmergeschemaarticles
( name sysname NOT NULL,
type tinyint NULL,
objid int NOT NULL,
artid uniqueidentifier NOT NULL,
description nvarchar(255) NULL,
pre_creation_command tinyint NULL,
pubid uniqueidentifier NOT NULL,
status tinyint NULL,
creation_script nvarchar(255) NULL,
schema_option binary(8) NULL,
destination_object sysname NOT NULL,
destination_owner sysname NULL
-- Note: Please update sysmergeextendedarticlesview
whenever
-- there is a schema change in sysmergeschemaarticles
)

if @@error<>0
goto Error
else
begin
create unique clustered index uc1sysmergeschemaarticles
on sysmergeschemaarticles(artid, pubid)
if @@ERROR <> 0
goto Error
end

exec dbo.sp_MS_marksystemobject sysmergeschemaarticles
if @@ERROR <> 0
goto Error

end

if exists (select * from sysobjects where name =
'sysmergeextendedarticlesview')
begin
drop view dbo.sysmergeextendedarticlesview
end
exec ('create view dbo.sysmergeextendedarticlesview
as
select name, type, objid, sync_objid, view_type, artid,
description, pre_creation_command, pubid,
nickname, column_tracking, status, conflict_table, creation_script,
conflict_script, article_resolver,
ins_conflict_proc, insert_proc, update_proc, select_proc,
schema_option, destination_object,
resolver_clsid, subset_filterclause, missing_col_count, missing_cols,
columns, resolver_info,
view_sel_proc, gen_cur, excluded_cols, excluded_col_count,
vertical_partition, identity_support,
destination_owner, before_image_objid, before_view_objid,
verify_resolver_signature,
allow_interactive_resolver, fast_multicol_updateproc,
check_permissions, maxversion_at_cleanup,
published_in_tran_pub
from sysmergearticles
union all
select name, type, objid, NULL, NULL, artid, description,
pre_creation_command, pubid,
NULL, NULL, status, NULL, creation_script, NULL, NULL,
NULL, NULL, NULL, NULL, schema_option, destination_object,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
destination_owner, NULL, NULL, NULL,
0, 0, 0, NULL, 0
from sysmergeschemaarticles
go')
if @@error <> 0
goto Error

exec dbo.sp_MS_marksystemobject sysmergeextendedarticlesview

if not exists (select * from sysobjects where name =
'sysmergesubscriptions')
begin

raiserror('Creating table sysmergesubscriptions',0,1)


create table dbo.sysmergesubscriptions
(
subid uniqueidentifier NOT NULL,
partnerid uniqueidentifier NOT NULL,
datasource_type int NOT NULL,
datasource_path nvarchar(255) NULL,
srvid int NOT NULL,
db_name sysname NOT NULL
constraint unique_pubsrvdb unique nonclustered (pubid, srvid,
db_name),
pubid uniqueidentifier NULL,
status tinyint NOT NULL,
subscriber_type int NOT NULL,
subscription_type int NOT NULL,
priority real NOT NULL,
sync_type tinyint NOT NULL, -- 1 = automatic 2 = no sync
description nvarchar(255) NULL,
login_name sysname NOT NULL,
last_validated datetime NULL,
subscriber_server sysname NULL,
use_interactive_resolver bit NOT NULL default 0,
publication sysname NULL,
distributor sysname NULL,
validation_level int not NULL default 0,
resync_gen int not NULL default -1,
attempted_validate datetime NULL,
last_sync_date datetime NULL,
last_sync_status int NULL,
last_sync_summary sysname NULL
)

if @@error<>0
goto Error
else
begin
create unique clustered index uc1sysmergesubscriptions
on sysmergesubscriptions (subid)
if @@ERROR<>0
goto Error

create index nc2sysmergesubscriptions on sysmergesubscriptions (srvid,
db_name)
if @@ERROR<>0
goto Error
end
exec dbo.sp_MS_marksystemobject sysmergesubscriptions
if @@ERROR <> 0
goto Error
--grant select on sysmergesubscriptions to public

end

if not exists (select * from sysobjects where name = 'MSmerge_replinfo')
begin
raiserror('Creating table MSmerge_replinfo',0,1)

create table dbo.MSmerge_replinfo
(
repid uniqueidentifier NOT NULL,
replnickname int NOT NULL,
recgen int NULL,
recguid uniqueidentifier NULL,
sentgen int NULL,
sentguid uniqueidentifier NULL,
schemaversion int NULL,
schemaguid uniqueidentifier NULL,
merge_jobid binary(16) NULL,
snapshot_jobid binary(16) NULL
)

if @@ERROR <> 0
goto Error
else
begin
create unique clustered index uc1MSmerge_replinfo
on MSmerge_replinfo (repid)
if @@ERROR <> 0
goto Error
end
exec dbo.sp_MS_marksystemobject MSmerge_replinfo
if @@ERROR <> 0
goto Error
--grant select on MSmerge_replinfo to public

end

if not exists (select * from sysobjects where name = 'MSmerge_tombstone')
begin
raiserror('Creating table MSmerge_tombstone',0,1)

create table dbo.MSmerge_tombstone
(
rowguid uniqueidentifier rowguidcol NOT NULL,
tablenick int NOT NULL,
type tinyint NOT NULL,
lineage varbinary(249) NOT NULL,
generation int NOT NULL,
reason nvarchar(255) NOT NULL,
)

if @@ERROR <> 0
goto Error
else
begin
create unique clustered index uc1MSmerge_tombstone
on MSmerge_tombstone (tablenick DESC, rowguid)
if @@ERROR <> 0 goto Error

create index nc2MSmerge_tombstone
on MSmerge_tombstone (generation)
if @@ERROR <> 0 goto Error
end

exec dbo.sp_MS_marksystemobject MSmerge_tombstone
if @@ERROR <> 0
goto Error
--grant select on MSmerge_tombstone to public

end

if not exists (select * from sysobjects where name = 'MSmerge_contents')
begin
raiserror('Creating table MSmerge_contents',0,1)

create table dbo.MSmerge_contents
(
tablenick int NOT NULL,
rowguid uniqueidentifier rowguidcol NOT NULL,
generation int NOT NULL,
partchangegen int NULL,
joinchangegen int NULL,
lineage varbinary(249) NOT NULL,
colv1 varbinary(2048) NULL,
)

if @@ERROR <> 0
goto Error
else
begin
create unique clustered index uc1SycContents on
MSmerge_contents(tablenick, rowguid)
if @@ERROR <> 0 goto Error
create index nc2MSmerge_contents on MSmerge_contents(generation)
if @@ERROR <> 0 goto Error
create index nc3MSmerge_contents on MSmerge_contents(partchangegen)
if @@ERROR <> 0 goto Error
create index nc4MSmerge_contents on MSmerge_contents(rowguid)
if @@ERROR <> 0 goto Error
end
exec dbo.sp_MS_marksystemobject MSmerge_contents
if @@ERROR <> 0
goto Error
--grant select on MSmerge_contents to public

end

if not exists (select * from sysobjects where name = 'MSmerge_genhistory')
begin
raiserror('Creating table MSmerge_genhistory',0,1)

create table dbo.MSmerge_genhistory
(
guidsrc uniqueidentifier NOT NULL,
guidlocal uniqueidentifier NOT NULL,
pubid uniqueidentifier NULL,
generation int NOT NULL,
art_nick int NULL,
nicknames varbinary(1000) NOT NULL,
coldate datetime NOT NULL
)

if @@ERROR <> 0
goto Error

create clustered index c1MSmerge_genhistory on
MSmerge_genhistory(generation)
if @@ERROR <> 0
goto Error

create unique index unc1MSmerge_genhistory on MSmerge_genhistory(guidsrc,
pubid)
if @@ERROR <> 0
goto Error

create index nc2MSmerge_genhistory on MSmerge_genhistory(guidlocal)
if @@ERROR <> 0
goto Error

CREATE INDEX nc4MSmerge_genhistory ON MSmerge_genhistory(coldate)
if @@ERROR <> 0
goto Error

exec dbo.sp_MS_marksystemobject MSmerge_genhistory
if @@ERROR <> 0
goto Error
--grant select on MSmerge_genhistory to public

end

if not exists (select * from sysobjects where name =
'MSmerge_delete_conflicts')
begin

raiserror('Creating table MSmerge_delete_conflicts',0,1)

create table dbo.MSmerge_delete_conflicts
(
tablenick int NOT NULL,
rowguid uniqueidentifier rowguidcol NOT NULL,
origin_datasource nvarchar(255) NULL,
conflict_type int NULL,
reason_code int NULL,
reason_text nvarchar(720) NULL,
pubid uniqueidentifier NULL,
create_time datetime not null default getdate()
)

if @@ERROR <> 0
goto Error
else
begin
create clustered index uc1MSmerge_delete_conflicts on
MSmerge_delete_conflicts(tablenick, rowguid)
if @@ERROR <> 0
goto Error
end

CREATE UNIQUE NONCLUSTERED INDEX nc1MSmerge_delete_conflicts
ON MSmerge_delete_conflicts(tablenick, rowguid, origin_datasource)
if @@ERROR <> 0
goto Error

exec dbo.sp_MS_marksystemobject MSmerge_delete_conflicts
if @@ERROR <> 0
goto Error
--grant select on MSmerge_delete_conflicts to public

end

if not exists (select * from sysobjects where name = 'sysmergeschemachange')

begin

raiserror('Creating table sysmergeschemachange',0,1)

create table dbo.sysmergeschemachange
(
pubid uniqueidentifier NOT NULL,
artid uniqueidentifier NULL,
schemaversion int NOT NULL,
schemaguid uniqueidentifier NOT NULL,
schematype int NOT NULL,
schematext nvarchar(2000) NOT NULL
)

if @@ERROR <> 0
goto Error
else
begin
create unique clustered index schemachangeversion on
sysmergeschemachange(schemaversion, pubid)
if @@ERROR <> 0
goto Error
end
exec dbo.sp_MS_marksystemobject sysmergeschemachange
if @@ERROR <> 0
goto Error
--grant select on sysmergeschemachange to public

end

if not exists (select * from sysobjects where name =
'sysmergesubsetfilters')
begin
raiserror('Creating table sysmergesubsetfilters',0,1)


create table dbo.sysmergesubsetfilters (
filtername sysname NOT NULL,
join_filterid int identity NOT NULL,
pubid uniqueidentifier NOT NULL,
artid uniqueidentifier NOT NULL,
art_nickname int NOT NULL,
join_articlename sysname NOT NULL,
join_nickname int NOT NULL,
join_unique_key int NOT NULL,
expand_proc sysname NULL,
join_filterclause nvarchar(1000) NULL
)
if @@ERROR <> 0
goto Error

CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergesubsetfilters
ON sysmergesubsetfilters(join_filterid, pubid)
if @@ERROR <> 0
goto Error

exec dbo.sp_MS_marksystemobject sysmergesubsetfilters
if @@ERROR <> 0
goto Error
--grant select on sysmergesubsetfilters to public

end

if @@error <> 0
goto Error

if not exists (select * from sysobjects where name =
'MSdynamicsnapshotviews')
begin
raiserror('Creating table MSdynamicsnapshotviews',0,1)

create table dbo.MSdynamicsnapshotviews (
dynamic_snapshot_view_name sysname primary key
)
if @@ERROR <> 0
goto Error
exec dbo.sp_MS_marksystemobject MSdynamicsnapshotviews
end

if not exists (select * from sysobjects where name =
'MSdynamicsnapshotjobs')
begin
raiserror('Creating table MSdynamicsnapshotjobs',0,1)

create table dbo.MSdynamicsnapshotjobs (
id int identity,
name sysname not null unique,
pubid uniqueidentifier not null,
job_id uniqueidentifier not null,
dynamic_filter_login sysname null,
dynamic_filter_hostname sysname null,
dynamic_snapshot_location nvarchar(255) not null
)
if @@ERROR <> 0
goto Error

exec dbo.sp_MS_marksystemobject MSdynamicsnapshotjobs

create unique clustered index uciMSdynamicsnapshotjobs on
dbo.MSdynamicsnapshotjobs(job_id, pubid)

if @@ERROR <> 0
goto Error


end

if @@error <> 0
goto Error

if not exists (select * from sysobjects where name =
'MSmerge_altsyncpartners')
begin
raiserror('Creating table MSmerge_altsyncpartners',0,1)

create table dbo.MSmerge_altsyncpartners (
subid uniqueidentifier not null,
alternate_subid uniqueidentifier not null,
description nvarchar(255) NULL

)
if @@ERROR <> 0
goto Error

exec dbo.sp_MS_marksystemobject MSmerge_altsyncpartners

create unique clustered index uciMSmerge_altsyncpartners on
dbo.MSmerge_altsyncpartners(subid, alternate_subid)

if @@ERROR <> 0
goto Error


end


if exists (select * from master..sysobjects where type='P' and
name='sp_MScreatedebuginfrastructure')
begin
declare @retval int
exec @retval= dbo.sp_MScreatedebuginfrastructure
if @@ERROR <> 0 or @retval <> 0 goto Error
end

commit transaction
return (0)
Error:
if @@trancount > 0
begin
ROLLBACK TRANSACTION MScreate_mergesystables
COMMIT TRANSACTION
end
RAISERROR (20008, 16, -1)
return (1)
 |  Next  |  Last
Pages: 1 2
Prev: how to tutorial for Replication
Next: SQL2005 - Snapshot agent won't start