具有可更新订阅的事务发布 从备份初始化订阅 出错：将截断字符串或二进制数据。

[xxswj]

请大家帮帮忙啊，搞了好多天了，实在没法搞定啊，好难

环境：域管理内的两台服务器 Server2003，SQL2005
服务器Server109 既是 发布也是分发 服务器，数据库用 GZJCS，已经建立好了发布，名称：GZJCS109，允许从备份中初始化订阅，采用了具有可更新订阅的事务发布，然后做了一个完整备份：E:\GZJCS109.Bak
服务器Server105 做订阅服务器，数据库用GZJCS_TEST
两个服务器都已经做好了对方的链接服务器。

然后在发布服务器Server109上执行一下代码，建立订阅
use GZJCS
Declare @Publication As SysName;
Declare @Subscriber as sysName;
Declare @SubscriptionDB as sysname;
Set @Publication=N'GZJCS109';
Set @Subscriber=N'Server105';
set @SubscriptionDB =N'GZJCS_TEST';
exec sp_addsubscription
@publication =@Publication
, @subscriber = @Subscriber
, @destination_db =@SubscriptionDB
, @subscription_type = N'Push'
, @article = N'all'
, @subscriber_type = 0
, @sync_type = N'initialize with backup'
,@BackupDeviceType='disk'
,@BackupDeviceName='E:\GZJCS109.bak'
, @update_mode = N'sync tran'
,@status='active'
结果老是出现一下错误：

消息 8152，级别 16，状态 10，过程 sp_MSget_synctran_commands，第 170 行
将截断字符串或二进制数据。

大家帮我看看是怎么回事啊。。谢谢

[快乐_石头]

字段長度不夠?

[--小F--]

消息 8152，级别 16，状态 10，过程 sp_MSget_synctran_commands，第 170 行
将截断字符串或二进制数据。

看看字符定义的长度是不是不够

[xxswj]

哪个字段啊？

[xxswj]

操作过程没用到具体的表吧

[dawugui]

[Quote=引用 3 楼 xxswj 的回复:]
哪个字段啊？
[/Quote]
过程 sp_MSget_synctran_commands，第 170 行
将截断字符串或二进制数据。


你到这里看看是不是定义了什么变量,长度不够?

[xxswj]

谢谢，提醒了我
GZJCS 数据库里面没有这个sp_MSget_synctran_commands
在Master里面找到了，
170行是 DEALLOCATE #hCsynctran_arts
是临时表操作啊

代码如下：
USE [master]
GO
/****** 对象: StoredProcedure [sys].[sp_MSget_synctran_commands] 脚本日期: 12/07/2009 16:30:56 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [sys].[sp_MSget_synctran_commands]
(
@publication sysname,
@article sysname = 'all',
@command_only bit = 0, -- 0 if snapshot agent, 1 if sp_script_...
@publisher sysname = NULL,
@publisher_db sysname = NULL,
@alter bit = 0 -- if 1 script alter, otherwise script create
) AS
BEGIN
SET NOCOUNT ON
DECLARE @tabid int,
@retcode int,
@distributor sysname,
@pubid int,
@art_name sysname,
@all_article bit,
@is_synctran bit,
@is_queued bit,
@publisher_type sysname,
@has_ts bit,
@has_ident bit

create table #art_commands
(
artid int NOT NULL,
commands nvarchar(max) collate database_default null,
id int identity NOT NULL
)

/*
** Initializations.
*/
select @has_ts = 0, @has_ident = 0

/*
** Security Check.
** We use login_name stored in syssubscriptions to manage security
** Do a relaxed security check here.
*/
exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)

/*
** Parameter Check: @publication
** Check to make sure that the publication exists, that it's not NULL,
** and that it conforms to the rules for identifiers.
*/
IF @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_MSget_synctran_commands')
RETURN (1)
END


-- Get publisher type
EXEC @retcode = sys.sp_MSrepl_getpublisherinfo @publisher = @publisher,
@publisher_type = @publisher_type OUTPUT

IF @retcode <> 0
BEGIN
RETURN (1)
END

SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT

IF @publisher IS NULL
BEGIN
set @publisher = publishingservername()
END

-- validate @publisher_db
IF @publisher_db IS NULL
BEGIN
set @publisher_db = db_name()
END

--
-- Verify publication exists
--
SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)

IF (@pubid IS NULL)
BEGIN
RAISERROR (20026, 11, -1, @publication)
RETURN (1)
END

-- get publication metadata
SELECT @is_synctran = allow_sync_tran,
@is_queued = allow_queued_tran
FROM syspublications
WHERE pubid = @pubid

-- If the publication does not allow sync tran or queued tran return nothing
IF (@is_synctran = 0 AND @is_queued = 0)
RETURN(0)
--
-- get the distributor details for this publisher
--
exec @retcode = sys.sp_MSrepl_getdistributorinfo @distributor = @distributor OUTPUT
if (@@ERROR != 0 OR @retcode != 0 or @distributor IS NULL)
begin
raiserror(14071, 16, -1)
RETURN (1)
end
--
-- validate @article
--
if lower(@article) = 'all'
select @all_article = 1
else
select @all_article = 0
--
-- Process for each article in this metadata
--
DECLARE #hCsynctran_arts CURSOR LOCAL FAST_FORWARD FOR
SELECT art.objid,
art.name
FROM sysarticles art,
syspublications pub
WHERE pub.pubid = @pubid
AND pub.pubid = art.pubid
AND (art.type & 0x1) = 1
AND (art.name = @article OR @all_article = 1)
FOR READ ONLY

OPEN #hCsynctran_arts

FETCH #hCsynctran_arts
INTO @tabid,
@art_name

WHILE (@@fetch_status <> -1)
BEGIN
-- Determine if table has timestamp property
if ObjectProperty(@tabid, 'TableHasTimestamp') = 1
set @has_ts = 1
if ObjectProperty(@tabid, 'TableHasIdentity') = 1
set @has_ident = 1

exec @retcode = sys.sp_MSarticle_synctran_commands
@publication = @publication
,@article = @art_name
,@command_only = 0
,@publisher = @publisher
,@publisher_db = @publisher_db
,@distributor = @distributor
,@has_ts = @has_ts
,@has_ident = @has_ident
-- omit @alter to get the default value, (0 for creation)
if (@@ERROR != 0 OR @retcode != 0)
begin
RETURN (1)
end
--
-- fetch metadata for next article
--
FETCH #hCsynctran_arts INTO @tabid, @art_name
end
CLOSE #hCsynctran_arts
DEALLOCATE #hCsynctran_arts
--
-- Done with article related processing
--
if @command_only = 0
select * from #art_commands order by id
else
select commands from #art_commands order by id
--
-- all done
--
return 0
END

[pbsh]

应该是某列内容超长了。
SQL SERVER就这个错误最郁闷，也不给个明确的提示。

[pbsh]

不了解你的表结构和数据，帖脚本是没用的。

[xxswj]

难道要进入sp_addsubscription 调试吗？

[xxswj]

表结构和数据？很多表和数据啊，至少有几百个表，我几乎清空了所有的数据，还要4G多。。

[xxswj]

看错了，#hCsynctran_arts 原来是游标来的
更不明白了
似乎没地方字段长度不够啊。。

难道会是 FETCH #hCsynctran_arts INTO @tabid, @art_name
这句？