EOF transactional replication

Hi. I am going out of my mind trying to get transactional
replication working.
Currently the snapshot completes successfully, but I keep
receiving the following error when the initial snapshot
is being applied:
String data, right truncation
(Source: SERVER01 (ODBC); Error number: 22001)
---
String data, right truncation
(Source: ODBC SQL Server Driver(ODBC); Error number:
22001)
---
Unexpected EOF encountered in BCP data-file
(Source: ODBC SQL Server Driver (ODBC); Error number:
S1000)
I have added columns to both the source and destination
to make sure that are sequenced and match perfectly (data
types and null values - everything I can think of)
Using SEM I have been able to use the DTS Export/Import
Wizard to Import into the destination table without any
problems.
Does anybody know what could be causing this error and
how I can fix it.
Also I would really like to see the bcp out snaphot file
to view the contents. How can this been done? Do I need
to add any parameters to the bcp file?
I am at a complete loss... does anyone know how to fix
this, or what could possibly be causing the problem?
Thanks,
Marcy| Hi. I am going out of my mind trying to get transactional
| replication working.
|
| Currently the snapshot completes successfully, but I keep
| receiving the following error when the initial snapshot
| is being applied:
|
| String data, right truncation
| (Source: SERVER01 (ODBC); Error number: 22001)
| ---
| String data, right truncation
| (Source: ODBC SQL Server Driver(ODBC); Error number:
| 22001)
| ---
| Unexpected EOF encountered in BCP data-file
| (Source: ODBC SQL Server Driver (ODBC); Error number:
| S1000)
--
What level of MDAC are you on? Update to the latest MDAC version and see
how you go.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Right now both servers are on:
Windows 2000
SQL Server Standard Edition
SP3 MDAC Version 2.71.9030.9
You sure this could be the problem?
If so, why?
Thanks,
Marcy
>--Original Message--
>| Hi. I am going out of my mind trying to get transactional >| replication working.
>| >| Currently the snapshot completes successfully, but I keep >| receiving the following error when the initial snapshot >| is being applied:
>| >| String data, right truncation
>| (Source: SERVER01 (ODBC); Error number: 22001)
>| ---
>| String data, right truncation
>| (Source: ODBC SQL Server Driver(ODBC); Error number: >| 22001)
>| ---
>| Unexpected EOF encountered in BCP data-file
>| (Source: ODBC SQL Server Driver (ODBC); Error number: >| S1000)
>--
>What level of MDAC are you on? Update to the latest MDAC version and see >how you go.
>Hope this helps,
>--
>Eric C=E1rdenas
>SQL Server support
>.
>|||| Right now both servers are on:
| Windows 2000
| SQL Server Standard Edition
| SP3
| MDAC Version 2.71.9030.9
| You sure this could be the problem?
| If so, why?
| Thanks,
| Marcy
--
Hi Marcy,
As a full time support professional, my job is to make sure that we've
covered all possibilities. I have seen mismatched or outdated MDAC files
causing this error so I pass this info to you.
There is definitely something that's corrupting your data when you do a
synch. Synch is just bcp out and bcp in behind the covers. Do you
experience the same prob when you do the bcp operations manually?
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Might be a stoopid question, but...
Am I suppose to bcp out to a .txt file and then bcp in the .txt file into the table. Or is there a smarter way for me to test the bcp option manually?
Thanks!
Marcy
>--Original Message--
>| Right now both servers are on:
>| Windows 2000
>| SQL Server Standard Edition
>| SP3 >| MDAC Version 2.71.9030.9
>| You sure this could be the problem?
>| If so, why?
>| Thanks,
>| Marcy
>--
>Hi Marcy,
>As a full time support professional, my job is to make sure that we've >covered all possibilities. I have seen mismatched or outdated MDAC files >causing this error so I pass this info to you.
>There is definitely something that's corrupting your data when you do a >synch. Synch is just bcp out and bcp in behind the covers. Do you >experience the same prob when you do the bcp operations manually?
>Hope this helps,
>--
>Eric C=E1rdenas
>SQL Server support
>.
>

Enterprise Publisher to Express Subscribers (2005)

I implemented a merge replication publication on a SQL Server 2005
Enterprise instance on a test server. On my laptop I have VS.NET 2005
installed with SQL Server 2005 Express.

It's my understanding that I can subscribe to a publication with
Express. I also have SQL Server Workgroup installed on my laptop so I'm
able to use Management Studio to control the Express instance.

The first issue I encountered was that you cannot subscribe to a
publication if you are not on the same network as the publishing
instance (it requires the actual server name). And in lieu of this I
tried to implement Web synchronization, but had problems with SSL
(which is my problem since I'm not entirely familiar with how to
implement SSL certs so they don't show the security warning on the
client without purchasing a certificate).

So then I decided to go ahead and create an account on my laptop and
add it to the domain of the test publishing server. I was then able to
subscribe to the published replication.

So then I look at my Express instance and the synchronization never
runs. In the documentation it says that you're supposed to be able to
right-click on the subscription and "View Synchronization" and force it
to run. This is not available in the Express instance, but it is
available in the Workgroup instance.

So I'm looking at cleaning up my web synchronization issues to make
that work, but I see two issues here.

1) Why do I have to specify the "real name" of the publishing database?
Why can't I access it like any other sql server via an IP address? This
seems like a highly restrictive implementation of replication to me.

2) Why do I have to implement SSL for replication? I understand the
need for security, but that should be left to me to implement. I
shouldn't be forced to use SSL as my security layer.

3) Why doesn't Express have "View Synchronization"?

Any help here is greatly appreciated.

David Cornelson

Hi David,
You should be able to use Web synchronization in your case.

Regarding your questions:
1) Not sure what you mean here. You need to have the actual and real publishing database name. Without it, we cannot know which database the subscription is referring to.

2) SSL is required for Web Synchronization. It is for Security Purposes. A security feature cannot be optional.

3) SQL Server Express does not have SQL Server Agent. Hence you do not have 'View Synchronization' (Workgroup has it and hence you see the option). You need to either use RMO or ActiveX sync components or use the Window Sync Manager to synchronizing.|||

re 1) So why can't I direct a subscriber to ipaddress\instanceName? Why does it have to be serverName\instanceName?

2) If all of my replication is done within my own company, say I have Smart Clients that are on different segments of my network, so they can't actually "see" the publishing database, I might want to use web synchronization and _not_ use SSL. You're making a security decision that I don't need. I understand that this should be default behaviour, but I should be able to turn it off. You're actually making my architecture more complex by forcing SSL into my replication strategy.

3) Where can I find information on Windows Sync Manager?

Thanks,

David C.

|||1) Not sure, but looks like there is no option for you to provide an ipaddress of the publisher to connect to. Servername/instance may be the only way.

2) Security is always a big concern and that is the reason for mandating the SSL. If you wish, you could workaround with a test certificate. MSDN has a tool for installing a test certificate.
For IIS6.0: http://www.microsoft.com/downloads/details.aspx?FamilyID=56fc92ee-a71a-4c73-b628-ade629c89499&displaylang=en
For IIS5.0: http://support.microsoft.com/default.aspx?scid=kb;en-us;228984

3) Windows Sync manager comes with Standard windows installation. Books online has more information about synchronizing using Sync Manager. To open Sync Manager follow either of the steps:
a) Windows Explorer-->Tools-->Synchronize
b) Start-->AllPrograms-->Accessories-->Synchronize|||1) Okay

2) Alright

3) It only has a Sql Server 2000 and an IE sync listed. Where's 2005?

Thanks for the help.

David C.|||3) I dont understand what you are saying. Where do you see SQL Server 2000 listed?
Windows sync manager is a windows mechanism for synchronization which can be launched using the 2 methods I described in my previous post. You have to enable the subscription on the Subscriber to be using the Windows Sync Manager. Once you have done that, you will start seeing the subscription in the Sync Manager and you can synchronize the subscription with the publication at the Publisher.

Enterprise Manager reporting wrong server version

I am running MS SQL 2000.

I recently ran a procedure in Query Analyzer from the Master db to
clear out all replication information so I could start/recreate it
again.

After I ran this procedure Enterprise Manager no longer showed the
registered server in the tree. When I tried to re-register it gave me
the following message:

"A connection could not be established to ([Database Name])"

"Reason: [SQL-DMO]Sql Server ([Database Name]) must be upgraded to
version 7.0 or later to be administered by this version of SQL-DMO"

"Please verify that sql is running and check your SQL server
registration properties (by right click on the ([Database Name]) node)
and try again."

I ran the following procedure:

<code>
exec sp_configure N'allow updates', 1
go
reconfigure with override
go

DECLARE @.name varchar(129)
DECLARE @.username varchar(129)
DECLARE @.insname varchar(129)
DECLARE @.delname varchar(129)
DECLARE @.updname varchar(129)
set @.insname=''
set @.updname=''
set @.delname=''

DECLARE list_triggers CURSOR FOR
select distinct replace(artid,'-',''), sysusers.name from
sysmergearticles,sysobjects, sysusers where
sysmergearticles.objid=sysobjects.id
and sysusers.uid=sysobjects.uid

OPEN list_triggers

FETCH NEXT FROM list_triggers INTO @.name, @.username
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping trigger ins_' +@.name
select @.insname='drop trigger ' +@.username+'.ins_'+@.name
exec (@.insname)
PRINT 'dropping trigger upd_' +@.name
select @.updname='drop trigger ' +@.username+'.upd_'+@.name
exec (@.delname)
PRINT 'dropping trigger del_' +@.name
select @.delname='drop trigger ' +@.username+'.del_'+@.name
exec (@.updname)
FETCH NEXT FROM list_triggers INTO @.name, @.username
END

CLOSE list_triggers
DEALLOCATE list_triggers
go

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syspublications]') and OBJECTPROPERTY(id,
N'IsUserTable')
= 1) begin DECLARE @.name varchar(129)
DECLARE list_pubs CURSOR FOR
SELECT name FROM syspublications

OPEN list_pubs

FETCH NEXT FROM list_pubs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping publication ' +@.name
EXEC sp_dropsubscription @.publication=@.name, @.article='all',
@.subscriber
='all'
EXEC sp_droppublication @.name
FETCH NEXT FROM list_pubs INTO @.name
END

CLOSE list_pubs
DEALLOCATE list_pubs
end
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

GO

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_%' or name like 'ms_bi%')

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

GO

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

GO

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

GO

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

GO

Declare @.name nvarchar(200), @.constraint nvarchar(200)
DECLARE list_rowguid_constraints CURSOR FOR
select sysusers.name+'.'+object_name(sysobjects.parent_ob j),
sysobjects.name
from sysobjects, syscolumns,sysusers where sysobjects.type ='d' and
syscolumns.id=sysobjects.parent_obj
and sysusers.uid=sysobjects.uid
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
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_constraints INTO @.name, @.constraint END

CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints

GO

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

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), @.constraint nvarchar(129)
DECLARE list_ms_bidi_tables CURSOR FOR
select sysusers.name+'.'+sysobjects.name from
sysobjects,sysusers where sysobjects.name like 'ms_bi%'
and sysusers.uid=sysobjects.uid
and sysobjects.type='u'

OPEN list_ms_bidi_tables

FETCH NEXT FROM list_ms_bidi_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping ms_bidi ' +@.name
select @.name='drop table ' + rtrim(@.name )
EXEC sp_executesql @.name
FETCH NEXT FROM list_ms_bidi_tables INTO @.name
END

CLOSE list_ms_bidi_tables
DEALLOCATE list_ms_bidi_tables

GO

Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select sysusers.name+'.'+object_name(syscolumns.id) from syscolumns,
sysobjects,sysusers where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysusers.uid=sysobjects.uid
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
go

Declare @.name nvarchar(129)
DECLARE list_views CURSOR FOR

select name From sysobjects where type ='v' and status =-1073741824 and
name
<>'sysmergeextendedarticlesview'

OPEN list_views

FETCH NEXT FROM list_views INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping replication views ' +@.name
select @.name='drop view ' + rtrim(@.name )
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_views INTO @.name
END

CLOSE list_views
DEALLOCATE list_views
go
Declare @.name nvarchar(129)
DECLARE list_procs CURSOR FOR

select name From sysobjects where type ='p' and status = -536870912

OPEN list_procs

FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping replication procedure ' +@.name
select @.name='drop procedure ' + rtrim(@.name )
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END

CLOSE list_procs
DEALLOCATE list_procs

go

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergepublications]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergepublications
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubscriptions
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syssubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM syssubscriptions
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticleupdates]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysarticleupdates
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[systranschemas]') and OBJECTPROPERTY(id,
N'IsUserTable')
= 1)
DELETE FROM systranschemas
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergearticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergearticles
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergeschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergeschemaarticles
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubscriptions
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') =
1)
DELETE FROM sysarticles
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysschemaarticles
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syspublications]') and OBJECTPROPERTY(id,
N'IsUserTable')
= 1)
DELETE FROM syspublications
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergeschemachange]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergeschemachange
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubsetfilters]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubsetfilters
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSdynamicsnapshotjobs]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSdynamicsnapshotjobs
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSdynamicsnapshotviews]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSdynamicsnapshotviews
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_altsyncpartners]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_altsyncpartners
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_contents]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_contents
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_delete_conflicts]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_delete_conflicts
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_errorlineage]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_errorlineage
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_genhistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_genhistory
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_replinfo]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_replinfo
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_tombstone]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_tombstone
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSpub_identity_range]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSpub_identity_range
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSrepl_identity_range]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSrepl_identity_range
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSreplication_subscriptions]') and
OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSreplication_subscriptions
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSsubscription_agents]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSsubscription_agents
GO

if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syssubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
create table syssubscriptions (artid int, srvid smallint, dest_db
sysname,
status tinyint, sync_type tinyint, login_name sysname,
subscription_type
int, distribution_jobid binary, timestamp timestamp,update_mode
tinyint,
loopback_detection tinyint, queued_reinit bit)

CREATE TABLE [dbo].[syspublications] (
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[name] [sysname] NOT NULL ,
[pubid] [int] IDENTITY (1, 1) NOT NULL ,
[repl_freq] [tinyint] NOT NULL ,
[status] [tinyint] NOT NULL ,
[sync_method] [tinyint] NOT NULL ,
[snapshot_jobid] [binary] (16) NULL ,
[independent_agent] [bit] NOT NULL ,
[immediate_sync] [bit] NOT NULL ,
[enabled_for_internet] [bit] NOT NULL ,
[allow_push] [bit] NOT NULL ,
[allow_pull] [bit] NOT NULL ,
[allow_anonymous] [bit] NOT NULL ,
[immediate_sync_ready] [bit] NOT NULL ,
[allow_sync_tran] [bit] NOT NULL ,
[autogen_sync_procs] [bit] NOT NULL ,
[retention] [int] NULL ,
[allow_queued_tran] [bit] NOT NULL ,
[snapshot_in_defaultfolder] [bit] NOT NULL ,
[alt_snapshot_folder] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[pre_snapshot_script] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[post_snapshot_script] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[compress_snapshot] [bit] NOT NULL ,
[ftp_address] [sysname] NULL ,
[ftp_port] [int] NOT NULL ,
[ftp_subdirectory] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[ftp_login] [sysname] NULL ,
[ftp_password] [nvarchar] (524) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[allow_dts] [bit] NOT NULL ,
[allow_subscription_copy] [bit] NOT NULL ,
[centralized_conflicts] [bit] NULL ,
[conflict_retention] [int] NULL ,
[conflict_policy] [int] NULL ,
[queue_type] [int] NULL ,
[ad_guidname] [sysname] NULL ,
[backward_comp_level] [int] NOT NULL
) ON [PRIMARY]
GO
create view sysextendedarticlesview
as
SELECT *
FROM sysarticles
UNION ALL
SELECT artid, NULL, creation_script, NULL, description,
dest_object,
NULL, NULL, NULL, name, objid, pubid, pre_creation_cmd, status, NULL,
type,
NULL,
schema_option, dest_owner
FROM sysschemaarticles
go

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[sysarticles]
GO

CREATE TABLE [dbo].[sysarticles] (
[artid] [int] IDENTITY (1, 1) NOT NULL ,
[columns] [varbinary] (32) NULL ,
[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
,
[del_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[dest_table] [sysname] NOT NULL ,
[filter] [int] NOT NULL ,
[filter_clause] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ins_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [sysname] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[pre_creation_cmd] [tinyint] NOT NULL ,
[status] [tinyint] NOT NULL ,
[sync_objid] [int] NOT NULL ,
[type] [tinyint] NOT NULL ,
[upd_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[schema_option] [binary] (8) NULL ,
[dest_owner] [sysname] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[sysschemaarticles]
GO

CREATE TABLE [dbo].[sysschemaarticles] (
[artid] [int] NOT NULL ,
[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[dest_object] [sysname] NOT NULL ,
[name] [sysname] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[pre_creation_cmd] [tinyint] NOT NULL ,
[status] [int] NOT NULL ,
[type] [tinyint] NOT NULL ,
[schema_option] [binary] (8) NULL ,
[dest_owner] [sysname] NULL
) ON [PRIMARY]
GO

declare @.dbname varchar(130)
select @.dbname ='sp_replicationdboption
'+char(39)+db_name()+char(39)+',''merge publish'',''false'''
exec (@.dbname)
select @.dbname ='sp_replicationdboption
'+char(39)+db_name()+char(39)+',''publish'',''fals e'''
exec (@.dbname)

reconfigure with override
go

select db_name()
</code>

Can any one please help me as this is a production machine and needs
fixing ASAP.

Regards,

BenBenzine (bfausti@.gmail.com) writes:

Quote:

Originally Posted by

I recently ran a procedure in Query Analyzer from the Master db to
clear out all replication information so I could start/recreate it
again.
>
After I ran this procedure Enterprise Manager no longer showed the
registered server in the tree. When I tried to re-register it gave me
the following message:
>
"A connection could not be established to ([Database Name])"
>
"Reason: [SQL-DMO]Sql Server ([Database Name]) must be upgraded to
version 7.0 or later to be administered by this version of SQL-DMO"
>
"Please verify that sql is running and check your SQL server
registration properties (by right click on the ([Database Name]) node)
and try again."
>...
Can any one please help me as this is a production machine and needs
fixing ASAP.

OK, so you've learnt a lesson for the next time: run in test before you
run in production.

You run a script that performs a lot of updates to the system tables,
and in many cases to undocumented columns, and now you wonder why your
server is hosed?

I can't tell if there were was more that was harmful, but this cursor
definitely was:

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

The SELECT hits 30 system procedures on my server, and far from all
are related to replication, for instance sp_updatestats and
sp_updateextendedproperty.

I would recommand that you at first possible maintenance window, detach
all databases and use the rebuildm tool to rebuild the master database.
Or simply reinstall SQL Server. Whatever, don't forget to reapply the
service pack.

If it's difficult to find the time for a reinstall, I suggest that you
open a case with Microsoft. I don't really want to guide you which
scripts to run, as my guidance could be wrong.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your reply.

Unfortunately I didn't have the luxury of a test environment, so I
took a big risk I know. Thankfully we had backups running on Veritas, I
restored to a previous version of the master and msdb databases and
this fixed my problem.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Benzine (bfausti@.gmail.com) writes:

Quote:

Originally Posted by

I recently ran a procedure in Query Analyzer from the Master db to
clear out all replication information so I could start/recreate it
again.

After I ran this procedure Enterprise Manager no longer showed the
registered server in the tree. When I tried to re-register it gave me
the following message:

"A connection could not be established to ([Database Name])"

"Reason: [SQL-DMO]Sql Server ([Database Name]) must be upgraded to
version 7.0 or later to be administered by this version of SQL-DMO"

"Please verify that sql is running and check your SQL server
registration properties (by right click on the ([Database Name]) node)
and try again."
...
Can any one please help me as this is a production machine and needs
fixing ASAP.

>
OK, so you've learnt a lesson for the next time: run in test before you
run in production.
>
You run a script that performs a lot of updates to the system tables,
and in many cases to undocumented columns, and now you wonder why your
server is hosed?
>
I can't tell if there were was more that was harmful, but this cursor
definitely was:
>
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
>
The SELECT hits 30 system procedures on my server, and far from all
are related to replication, for instance sp_updatestats and
sp_updateextendedproperty.
>
I would recommand that you at first possible maintenance window, detach
all databases and use the rebuildm tool to rebuild the master database.
Or simply reinstall SQL Server. Whatever, don't forget to reapply the
service pack.
>
If it's difficult to find the time for a reinstall, I suggest that you
open a case with Microsoft. I don't really want to guide you which
scripts to run, as my guidance could be wrong.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Enterprise Manager Hanging when removing replication

I'm trying to remove replication from my database so that I can run some update scripts on it but SQL Enterprise Manager just keeps hanging on me. I've searched the web an no-one seems to have had this problem.

Does anyone know of this problem or have they experienced it? If so, can you help?

Am I doing something wrong trying to remove the replication (deleting the publication)?

Thanks.

More than likely it is working, it can take some time to delete a publication if there is a lot of history to remove.

If you delete the publication using a store procedure, it won't lock up SQL Enterprise Manager while you do it. If it's a merge publication you do that with this command:

exec sp_dropmergepublication @.publication = <publication name>

|||Drop the subscriptions before you drop the publications. As roamingsim has pointed out it is better to do this through QA as opposed to Enterprise Manager.