Re: help ! merge replication blob data

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Sep 20, 1:06 pm, RLU <R...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi Hilary,

Thank you for your reply.

There is 16GB RAM in this Server currently.

I set the @stream_blob_columns='ture' When I added merge article
(Documentest). According to Microsoft document, after set this parameter to
ture, the synchronization will not run out memory if the blob data is large.

Regards
RLU



"Hilary Cotter" wrote:
This could be a limitation of available memory on your publisher? How much
RAM is on it?

--
RelevantNoise.com - dedicated to mining blogs for business intelligence.

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
"rlu" <jlin168...@xxxxxxxx> wrote in message
news:1190137550.723825.183520@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi all,

I got error from the merge replication when I tried to synchronization
article with blob data (varbinary(max)). The error said, The merge
process could not replicate one or more INSERT statements to the
'Subscriber', A stored procedure failed to execute.

I tried to use profiler, but I didn't get related info. Does anyone
can help to figure out the problem?

The detail info of my merge replication is:

SQL Server 2005 enterprise edition sp2
windows 2003 x64
publication and subscription on the same machine.
articles: 1 (Documentest with varbinary(max) column, mediaItem)
merge, pull replication

After initialization, I loaded image file to the table to the
subscription or publication. if the file size less than 495 MB, the
synchronization is fine. But If I load image file (for example, file
size bigger than 495 MB), the synchronization failuer. The following
is the output log got from the merge agent log file:

-------------------------

2007-09-17 20:33:18.890 Connecting to Subscriber 'MYserver'
2007-09-17 20:33:18.890 Connecting to OLE DB Subscriber at datasource:
'MYserver', location: '', catalog: 'Multimedia_subscription',
providerstring: '' using provider 'SQLNCLI'
2007-09-17 20:33:18.890 OLE DB Subscriber: MYserver
DBMS: Microsoft SQL Server
Version: 09.00.3159
catalog name: Multimedia_subscription
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-09-17 20:33:18.890 OLE DB Subscriber 'MYserver': set nocount on
declare @dbname sysname select @dbname = db_name() declare @collation
nvarchar(255) select @collation = convert(nvarchar(255),
databasepropertyex(@dbname, N'COLLATION')) select
collationproperty(@collation, N'CODEPAGE') as 'CodePage',
collationproperty(@collation, N'LCID') as 'LCID',
collationproperty(@collation, N'COMPARISONSTYLE') as
'ComparisonStyle',cast(case when convert (int,databasepropertyex
(@dbname,'comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as
DB_CaseSensitive,cast(case when convert (int,serverproperty
('comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as
Server_CaseSensitive set nocount off
2007-09-17 20:33:18.890 OLE DB Subscriber 'MYserver': {?=call
sp_helpsubscription_properties (N'MYserver', N'PUB_Multimedia',
N'Multimedia')}
2007-09-17 20:33:18.890 Distributor security mode: 0, login name:
subscriberUser, password: ********.
2007-09-17 20:33:18.890 OLE DB Subscriber 'MYserver': {?=call
sp_helpsubscription_properties (N'MYserver', N'PUB_Multimedia',
N'Multimedia')}
2007-09-17 20:33:18.890 Publisher security mode: 0, login name:
subscriberUser, password: ********.
2007-09-17 20:33:18.890 Percent Complete: 0
2007-09-17 20:33:18.890 Connecting to Distributor 'MYserver'
2007-09-17 20:33:18.890 Connecting to OLE DB Distributor at
datasource: 'MYserver', location: '', catalog: '', providerstring: ''
using provider 'SQLNCLI'
2007-09-17 20:33:18.906 OLE DB Distributor: MYserver
DBMS: Microsoft SQL Server
Version: 09.00.3159
catalog name:
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-09-17 20:33:18.906 OLE DB Distributor 'MYserver': {call
sp_MSgetversion }
2007-09-17 20:33:18.906 OLE DB Distributor 'MYserver': {call
sp_helpdistpublisher (N'MYserver') }
2007-09-17 20:33:19.078 OLE DB Distributor 'MYserver': select
datasource, srvid from master..sysservers where upper(srvname) =
upper(N'MYserver')
2007-09-17 20:33:19.078 OLE DB Distributor 'MYserver': {call
sp_MShelp_merge_agentid
(0,N'PUB_Multimedia',N'Multimedia',null,N'Multimedia_subscription',
90,N'MYserver')}
2007-09-17 20:33:19.078 OLE DB Subscriber 'MYserver': {call
sys.sp_MSadd_merge_history90
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,­?,?,?,?,?,?,?,?)}
2007-09-17 20:33:19.093 OLE DB Distributor 'MYserver': {call
sp_MShelp_profile (1, 4, N'')}
2007-09-17 20:33:19.093 Percent Complete: 0
2007-09-17 20:33:19.093 OLE DB Subscriber 'MYserver': {call
sys.sp_MSadd_merge_history90
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,­?,?,?,?,?,?,?,?)}
2007-09-17 20:33:19.093 Initializing
2007-09-17 20:33:19.109 OLE DB Distributor 'MYserver': {call
sys.sp_MSadd_merge_history90
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,­?,?,?,?,?,?,?,?)}
2007-09-17 20:33:19.125 Connecting to OLE DB Publisher at datasource:
'MYserver', location: '', catalog: 'PUB_Multimedia', providerstring:
'' using provider 'SQLNCLI'
2007-09-17 20:33:19.125 OLE DB Publisher: MYserver
DBMS: Microsoft SQL Server
Version: 09.00.3159
catalog name: PUB_Multimedia
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-09-17 20:33:19.125 Percent Complete: 0
2007-09-17 20:33:19.125 OLE DB Publisher 'MYserver': set nocount on
declare @dbname sysname select @dbname = db_name() declare @collation
nvarchar(255) select @collation = convert(nvarchar(255),
databasepropertyex(@dbname, N'COLLATION')) select
collationproperty(@collation, N'CODEPAGE') as 'CodePage',
collationproperty(@collation, N'LCID') as 'LCID',
collationproperty(@collation, N'COMPARISONSTYLE') as
'ComparisonStyle',cast(case when convert (int,databasepropertyex
(@dbname,'comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as
DB_CaseSensitive,cast(case when convert (int,serverproperty
('comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as
Server_CaseSensitive set nocount off
2007-09-17 20:33:19.125 Connecting to Publisher 'MYserver'
2007-09-17 20:33:19.125 OLE DB Distributor 'MYserver': {call
sys.sp_MSadd_merge_history90
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,­?,?,?,?,?,?,?,?)}
2007-09-17 20:33:19.140 Connecting to OLE DB Publisher at datasource:
'MYserver', location: '', catalog: 'PUB_Multimedia', providerstring:
'' using provider 'SQLNCLI'
2007-09-17 20:33:19.140 OLE DB Publisher: MYserver
DBMS: Microsoft SQL Server
Version: 09.00.3159
catalog name: PUB_Multimedia
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-09-17 20:33:19.140 OLE DB Subscriber 'MYserver': {call
sys.sp_MSadd_merge_history90
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,­?,?,?,?,?,?,?,?)}
2007-09-17 20:33:19.156 OLE DB Subscriber 'MYserver': {call
sys.sp_MSadd_merge_history90
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,­?,?,?,?,?,?,?,?)}
2007-09-17 20:33:19.156 Percent Complete: 0
2007-09-17 20:33:19.156 Retrieving publication information
2007-09-17 20:33:19.156 OLE DB Distributor 'MYserver': {call
sys.sp_MSadd_merge_history90
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,­?,?,?,?,?,?,?,?)}
2007-09-17 20:33:19.171 Percent Complete: 0
2007-09-17 20:33:19.187 Retrieving subscription information.
2007-09-17 20:33:19.187 OLE DB Distributor 'MYserver': {call
sys.sp_MSadd_merge_history90
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,­?,?,?,?,?,?,?,?)}
2007-09-17 20:33:19.265 OLE DB Subscriber 'MYserver': {call
sys.sp_MSadd_merge_history90
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,­?,?,?,?,?,?,?,?)}
2007-09-17 20:33:19.328 Percent Complete: 0
2007-09-17 20:33:19.343 OLE DB Subscriber 'MYserver': {call
sys.sp_MSadd_merge_history90

...

read more »- Hide quoted text -

- Show quoted text -

Hi,

Is that means, 16GB RAM is not enough for synchronization a varbinary
column with 494MB image loaded, even set @stream_blob_columns='ture'?

Does there any other setting need configure in sql server 2005 or
server side?

Thanks

RLU

.

Relevant Pages