Failed to create snapshot while bulk copy large table with TEXT/IMAGE field

Dear all,

Please help us on the snapshot bulk copy issue with a TEXT
field. The snapshot agent was failed with below message:

The process could not bulk copy into table '"Enclosure"'

We have a big table about 170,000 records that needs to
replicate to other sites. There is a TEXT field in this
table. Here is the table schema..

CREATE TABLE [dbo].[Enclosure] (
[Identifier] [uniqueidentifier] NOT NULL ,
[sCommentId] [varchar] (15) COLLATE
Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[sEncName] [varchar] (50) COLLATE
Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[sUserId] [varchar] (15) COLLATE
Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[dUpdateTime] [datetime] NOT NULL ,
[sContent] [text] COLLATE Chinese_Taiwan_Stroke_CI_AS
NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [EXT_1] TEXTIMAGE_ON [EXT_1]

The "Identifier" field is an auto-generated primary key
(by newid()) of this table. The table is built on SQL
server 2000 SP3 over Windows 2000 sever SP4.

For this problem, I've searched in the news group and
google for the solution. I would summarize them as below:

1. Check disk space:
Our disk space should be enough. We have 3GB in system
partition and 120GB in data partition. All snapshot and
database file are located on the data partition. The data
file of this database that need to replicate is only 170MB.

2. Try BCP.exe command line
We tried to execute
[ bcp "select * from repl_test.dbo.Enclosure" queryout
snapshot.txt ]
in the command line. It can finish successfully but it
showed that it only bulk copy 36739 records out of 170,000
records. We do not have any idea about this result.

3. Make the TEXT field to be non-NULL
We fill an empty character ('') for the NULL value in the
TEXT field but the snapshot still cannot create
successfully.

4. Change configuration "max text repl size (B)" with
sp_configure
We check the maximum length of the TEXT field by [ select
max(datalength(sContent)) from Enclosure ]. Then, we
use "sp_configure" to set the "configure_value" of "max
text repl size (B)" entry to be 2 times of the maximum
length of the TEXT field. However, it shill cannot work!

Above suggestions did not work in our problem. The
snapshot still cannot create successfully. Finally I try
to modify the data type of TEXT field to be VARCHAR(7500)
then it can create snapshot successfully. Although it can
work, it's not really what we need.

I also tried to reduce the row number to see if bulk copy
has row limitation. When the row number become smaller
(about 10,000 records), the snapshot can create
successfully with the TEXT field.

I am not sure it's a bug or limitation of SQL server
replication. Could anybody help us on this urgent issue?
We are exhausted on this problem :~~. We need your advice
about this issue.

Thanks for your help!!

Best regards,
Louis
.Howdy

Check the disk space for the Distribution database - if its small ( monitor space taken for 5000 rows then 10000 rows ) , try moving it to a bigger drive if need be.

You may also want to check the snap shot replication folder location - again it could be a space issue.

See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replplan_1l4e.asp

How to specify alternate snapshot locations (Enterprise Manager):

In SQL Server Enterprise Manager, expand the Replication and Publications directory, select a publication, right-click the publication, and then click Properties.

On the Snapshot Location tab, select Generate snapshots in the following location option, and then type a Universal Naming Convention path or click the browse button (...) and browse for the location where you want to save snapshot files.

Cheers

SG|||HI SG,

Thanks for your answer. I just adjust the file location of "distribution" and snapshot folder to a 120GB free disk partition. The source database is only 170MB. Beside this, the winnt\temp directory has 3GB free in system partition. I think the disk space should be OK.

I just tried the bcp.exe command again. I found it only can bulk copy 36188 records out of original 170000 records. All data length of the TEXT file and total data rows are under snapshot agent's defaults.

bcp command:

bcp repl_test..Enclosure out enclosure.bcp /Usa

Defaults of snapshot agent :

M i c r o s o f t S Q L S e r v e r S n a p s h o t A g e n t 8 . 0 0 . 7 6 0
C o p y r i g h t ( c ) 2 0 0 0 M i c r o s o f t C o r p o r a t i o n
M i c r o s o f t S Q L S e r v e r R e p l i c a t i o n A g e n t : C R M 2 - r e p l _ t e s t - r e p l _ t e s t - 1 1

S e r v e r :

D B M S : M i c r o s o f t S Q L S e r v e r
V e r s i o n : 0 8 . 0 0 . 0 7 6 0
u s e r n a m e : d b o
A P I c o n f o r m a n c e : 2
S Q L c o n f o r m a n c e : 1
t r a n s a c t i o n c a p a b l e : 2
r e a d o n l y : N
i d e n t i f i e r q u o t e c h a r : "
n o n _ n u l l a b l e _ c o l u m n s : 1
o w n e r u s a g e : 3 1
m a x t a b l e n a m e l e n : 1 2 8
m a x c o l u m n n a m e l e n : 1 2 8
n e e d l o n g d a t a l e n : Y
m a x c o l u m n s i n t a b l e : 1 0 2 4
m a x c o l u m n s i n i n d e x : 1 6
m a x c h a r l i t e r a l l e n : 5 2 4 2 8 8
m a x s t a t e m e n t l e n : 5 2 4 2 8 8
m a x r o w s i z e : 5 2 4 2 8 8|||Additional information for the bcp.exe. I think I find the way to solve my problem.

First, I executed

bcp repl_test..Enclosure out enclosure.bcp /Usa

It only can bulk copy 36188 records out of original 170000 records. So I try non-text native bulk copy with /N switch of bcp.exe

bcp repl_test..Enclosure out enclosure.bcp /Usa /N

With non-text native bulk copy, it can copy all 170000 records successfully. Then, I modify snapshot settings in Enterprise manager to use non-text native mode to create snapshot. The snapshop then can finished successfully!! :)

I am not sure if any side effect to use non-text native snapshot creation for TEXT and IMAGE field. I will double check the result and post it for everyone that interested on this issue.

Thanks for your concern and help!!

Louis|||Hello louisyfwang,

With bcp you can set the batch size ( -b option) so that it commits blocks of rows, and breakes the bulk into multiple transaction instead of attempting to transfer all at once. Essence this is mainly done in large size tables.

Hope this helps.

Jose R.

Originally posted by louisyfwang
Additional information for the bcp.exe. I think I find the way to solve my problem.

First, I executed

bcp repl_test..Enclosure out enclosure.bcp /Usa

It only can bulk copy 36188 records out of original 170000 records. So I try non-text native bulk copy with /N switch of bcp.exe

bcp repl_test..Enclosure out enclosure.bcp /Usa /N

With non-text native bulk copy, it can copy all 170000 records successfully. Then, I modify snapshot settings in Enterprise manager to use non-text native mode to create snapshot. The snapshop then can finished successfully!! :)

I am not sure if any side effect to use non-text native snapshot creation for TEXT and IMAGE field. I will double check the result and post it for everyone that interested on this issue.

Thanks for your concern and help!!

Louis|||Hi Jose,

Thanks for your suggestion. I can use the option of "-b" in the bcp.exe command line. Is there any way to specify this options in snapshot agent? I cannot find any "bcp.exe" command in the agent properties.

Best regards,
Louis

Fact Snapshot Table Question

Hello All,

I have a Snapshot Fact table that shows data on a monthly basis. Each person has a row for each month.
But now I am adding bonus information. The problem is, for each month a person may have received more than one bonus amount and type in a month. For example:
ID Date Bonus Bonus Type
1000 1/1/2000 $5,000 Team
1000 1/5/2000 $ 500 GoodJob
1000 1/7/2000 $ 250 Incentive

What is the best way to have multiple bonuses and their amounts in the fact table, (when the fact table currently has a single row per employee, per month)?

Thank you for the help.

-Gumbatman

Assuming that you are using SSAS 2005, Just keep this information in it's own fact table and add that to the cube as another measure group.|||

Darren,

Thank you for the reply, but I am not 100% clear on what you mean (my issue, not yours).

I have my main fact table, that is populated each month, for example:

EmployeeID DateKey JobKey GeographicKey 1 20060101 123 455 2 20060101 3247 9898 3 20060101 9870 2444 1 20060201 987 455 2 20060201 3247 9898 3 20060201 9870 2444

If I now have the bonus information as a separate table, how would I link them in order to add the bonus information as a Measure Group?

Do I need to have the JobKey and GeographicKeys in the Bonus fact table to do my slicing by dimensions (seems redundant)?

Taking a guess, I assume my Bonus fact table should it look something like this:

EmployeeID DateKey BonusAmount BonusKey 1 20060101 500 55 1 20060101 1000 42 1 20060101 250 3 3 20060101 500 55 2 20060201 250 68

My assumption is to link the two fact tables by DateKey and EmployeeID.

Thank you for the help. I've been trying to get my brain around this for a while.

|||

Having different granularities can make things a bit harder to get your head around. If you have a look at the Adventure Works cube you will see that the sales targets measure group is probably similar to bonus fact table we are talking about here.

The answer to your question hinges around how you want to see the data come out like the following, with the bonus essentially repeated for each unrelated attribute (ie Job and Geography).

EmployeeID DateKey JobKey GeographicKey Bonus 1 20060101 123 455 2000 2 20060101 3247 9898 250 3 20060101 9870 2444 500 1 20060201 987 455 2000 2 20060201 3247 9898 250 3 20060201 9870 2444

500

Then simply adding the bonus fact table to the cube and setting up the dimension relationships tab so that only the date and employee dimensions are related to the bonus amounts should be enough. You can also change a setting on the measure group so that if someone drills down by an unrelated dimension then the bonus amount returns null.

Those are the two default options, the only other option I can think of would be to devise an allocation mechanism to divide the bonus between the unrelated attributes like Job and Geography which would involve either extra work in the ETL phase or calculations in the MDX script.

|||

Darren,

Thank you so much for the answers and explanations. I still need to get my head around how the Sales Targets measure group is related, but it certainly seems to be what I was looking for.

I really appreciate you taking the time to answer this for me. You've saved me lots of time and work trying to figure it out on my own (which probably wouldn't have happened).

-Gumbatman

|||

Darren,

I've tried implementing this, but I am not doing something correctly.

I have my Fact table:

RowKey EmployeeKey Date 1 500 20061031 2 600 20061031 3 700 20061031 4 500 20061130 5 600 20061130 6 700 20061130

And my Bonus Fact Table, which references the RowKey:

RowKey BonusAmount Date 1 250 20061031 1 700 20061031 6 1000 20061130 6 2000 20061130

As you can see, Employee 500 got two bonuses in October and Employee 700 got two bonuses in November.

I linked the two Fact tables in the Data Source View (using RowKey) and added a new Measure Group. In the Dimensions tab, I can only connect the Date to the Time Dimension. I don't know how to connect the tables other than that since the Bonus Fact table doesn't have those dimensions.

When I Browse the cube, the Bonus amounts do show over time, but the total populates all the rows. For example,

Period 1 Period 2 Period 3 Midwest 50,000 98,000 45,000 Northeast 50,000 98,000 45,000 Southwest 50,000 98,000 45,000

How do I connect the two fact tables so I can use all the Dimensions that are already connected to the main Fact table? I did try the many-to-many connection but that didn't help either.

Thank you for the help.

-Gumbatman