Having Problem While Importing a Text File

[panic attack]

Hello everbody,
Our system is using Sql Server 2000 on Windows XP / Windows 2000
We have a text file needs to be imported into Sql Server 2000 as a
table.
But we are facing a problem which is,
Sql Server claims that it has a character size limit ( which is 8060 )
so it cant procceed the import operation if the text file has a record
bigger then 8060.
The records , in the text file, have a size bigger then 8060. So we
wont be able to import the text file.
On the other hand it is said that Sql Server 2005 can get a record
bigger then 8060 but
again we couldnt be able to perform the task.

As a result, i urgently need to know that how may i import the text
file which has a record bigger then 8060 characters.?
Any help is appreciated
thanks a lot!!

Tunc Ovacik

[Erland Sommarskog]

panic attack (tu*********@gmail.com) writes:

Our system is using Sql Server 2000 on Windows XP / Windows 2000
We have a text file needs to be imported into Sql Server 2000 as a
table.
But we are facing a problem which is,
Sql Server claims that it has a character size limit ( which is 8060 )
so it cant procceed the import operation if the text file has a record
bigger then 8060.
The records , in the text file, have a size bigger then 8060. So we
wont be able to import the text file.
On the other hand it is said that Sql Server 2005 can get a record
bigger then 8060 but
again we couldnt be able to perform the task.

As a result, i urgently need to know that how may i import the text
file which has a record bigger then 8060 characters.?
Any help is appreciated
thanks a lot!!

How do you import the file? BCP, BULK INSERT or DTS?

Could you post the CREATE TABLE statement for the table in question?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[NiTiN]

--SNIP --

The records , in the text file, have a size bigger then 8060. So we
wont be able to import the text file.
On the other hand it is said that Sql Server 2005 can get a record
bigger then 8060 but
again we couldnt be able to perform the task.

As a result, i urgently need to know that how may i import the text
file which has a record bigger then 8060 characters.?
Any help is appreciated

-- SNIP --
Good day,

If you're using a large data-type for a column (such as varchar(max),
nvarchar(max), varbinary(max), text, image, & xml), you can go beyond
the 8060 limit.

Alternatively, if you aren't using large data-types, you can vertically
partition the table so some of the columns would be in one table while
the other set of columns would be in another table.

Hope this helps.

Regards,
N.I.T.I.N.

[panic attack]

Erland Sommarskog wrote:

panic attack (tu*********@gmail.com) writes:

Our system is using Sql Server 2000 on Windows XP / Windows 2000
We have a text file needs to be imported into Sql Server 2000 as a
table.
But we are facing a problem which is,
Sql Server claims that it has a character size limit ( which is 8060 )
so it cant procceed the import operation if the text file has a record
bigger then 8060.
The records , in the text file, have a size bigger then 8060. So we
wont be able to import the text file.
On the other hand it is said that Sql Server 2005 can get a record
bigger then 8060 but
again we couldnt be able to perform the task.

As a result, i urgently need to know that how may i import the text
file which has a record bigger then 8060 characters.?
Any help is appreciated
thanks a lot!!

How do you import the file? BCP, BULK INSERT or DTS?

Could you post the CREATE TABLE statement for the table in question?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

hi again...
thanks for your concern... i really appreciated...
we are importing the text file by using DTS
here is the create table statement used by DTS :

CREATE TABLE [nwind].[dbo].[DDD] (
[Col001] varchar (255) NULL,
[Col002] varchar (255) NULL,
[Col003] varchar (255) NULL,
[Col004] varchar (255) NULL,
[Col005] varchar (255) NULL,
[Col006] varchar (255) NULL,
[Col007] varchar (255) NULL,
[Col008] varchar (255) NULL,
[Col009] varchar (255) NULL,
[Col010] varchar (255) NULL,
[Col011] varchar (255) NULL,
[Col012] varchar (255) NULL,
[Col013] varchar (255) NULL,
[Col014] varchar (255) NULL,
[Col015] varchar (255) NULL,
[Col016] varchar (255) NULL,
[Col017] varchar (255) NULL,
[Col018] varchar (255) NULL,
[Col019] varchar (255) NULL,
[Col020] varchar (255) NULL,
[Col021] varchar (255) NULL,
[Col022] varchar (255) NULL,
[Col023] varchar (255) NULL,
[Col024] varchar (255) NULL,
[Col025] varchar (255) NULL,
[Col026] varchar (255) NULL,
[Col027] varchar (255) NULL,
[Col028] varchar (255) NULL,
[Col029] varchar (255) NULL,
[Col030] varchar (255) NULL,
[Col031] varchar (255) NULL,
[Col032] varchar (255) NULL,
[Col033] varchar (255) NULL,
[Col034] varchar (255) NULL,
[Col035] varchar (255) NULL,
[Col036] varchar (255) NULL,
[Col037] varchar (255) NULL,
[Col038] varchar (255) NULL,
[Col039] varchar (255) NULL,
[Col040] varchar (255) NULL,
[Col041] varchar (255) NULL,
[Col042] varchar (255) NULL,
[Col043] varchar (255) NULL,
[Col044] varchar (255) NULL,
[Col045] varchar (255) NULL,
[Col046] varchar (255) NULL,
[Col047] varchar (255) NULL,
[Col048] varchar (255) NULL,
[Col049] varchar (255) NULL,
[Col050] varchar (255) NULL,
[Col051] varchar (255) NULL,
[Col052] varchar (255) NULL,
[Col053] varchar (255) NULL,
[Col054] varchar (255) NULL,
[Col055] varchar (255) NULL,
[Col056] varchar (255) NULL,
[Col057] varchar (255) NULL,
[Col058] varchar (255) NULL,
[Col059] varchar (255) NULL,
[Col060] varchar (255) NULL,
[Col061] varchar (255) NULL,
[Col062] varchar (255) NULL,
[Col063] varchar (255) NULL,
[Col064] varchar (255) NULL,
[Col065] varchar (255) NULL,
[Col066] varchar (255) NULL,
[Col067] varchar (255) NULL,
[Col068] varchar (255) NULL,
[Col069] varchar (255) NULL,
[Col070] varchar (255) NULL,
[Col071] varchar (255) NULL,
[Col072] varchar (255) NULL,
[Col073] varchar (255) NULL,
[Col074] varchar (255) NULL,
[Col075] varchar (255) NULL,
[Col076] varchar (255) NULL,
[Col077] varchar (255) NULL,
[Col078] varchar (255) NULL,
[Col079] varchar (255) NULL,
[Col080] varchar (255) NULL,
[Col081] varchar (255) NULL,
[Col082] varchar (255) NULL,
[Col083] varchar (255) NULL,
[Col084] varchar (255) NULL,
[Col085] varchar (255) NULL,
[Col086] varchar (255) NULL,
[Col087] varchar (255) NULL,
[Col088] varchar (255) NULL,
[Col089] varchar (255) NULL,
[Col090] varchar (255) NULL,
[Col091] varchar (255) NULL,
[Col092] varchar (255) NULL,
[Col093] varchar (255) NULL,
[Col094] varchar (255) NULL,
[Col095] varchar (255) NULL,
[Col096] varchar (255) NULL,
[Col097] varchar (255) NULL,
[Col098] varchar (255) NULL,
[Col099] varchar (255) NULL,
[Col100] varchar (255) NULL,
[Col101] varchar (255) NULL,
[Col102] varchar (255) NULL,
[Col103] varchar (255) NULL,
[Col104] varchar (255) NULL,
[Col105] varchar (255) NULL,
[Col106] varchar (255) NULL,
[Col107] varchar (255) NULL,
[Col108] varchar (255) NULL,
[Col109] varchar (255) NULL,
[Col110] varchar (255) NULL,
[Col111] varchar (255) NULL,
[Col112] varchar (255) NULL,
[Col113] varchar (255) NULL,
[Col114] varchar (255) NULL,
[Col115] varchar (255) NULL,
[Col116] varchar (255) NULL,
[Col117] varchar (255) NULL,
[Col118] varchar (255) NULL,
[Col119] varchar (255) NULL,
[Col120] varchar (255) NULL,
[Col121] varchar (255) NULL,
[Col122] varchar (255) NULL,
[Col123] varchar (255) NULL,
[Col124] varchar (255) NULL,
[Col125] varchar (255) NULL,
[Col126] varchar (255) NULL,
[Col127] varchar (255) NULL,
[Col128] varchar (255) NULL,
[Col129] varchar (255) NULL,
[Col130] varchar (255) NULL,
[Col131] varchar (255) NULL,
[Col132] varchar (255) NULL,
[Col133] varchar (255) NULL,
[Col134] varchar (255) NULL,
[Col135] varchar (255) NULL,
[Col136] varchar (255) NULL,
[Col137] varchar (255) NULL,
[Col138] varchar (255) NULL,
[Col139] varchar (255) NULL,
[Col140] varchar (255) NULL,
[Col141] varchar (255) NULL,
[Col142] varchar (255) NULL,
[Col143] varchar (255) NULL,
[Col144] varchar (255) NULL,
[Col145] varchar (255) NULL,
[Col146] varchar (255) NULL,
[Col147] varchar (255) NULL,
[Col148] varchar (255) NULL,
[Col149] varchar (255) NULL,
[Col150] varchar (255) NULL,
[Col151] varchar (255) NULL,
[Col152] varchar (255) NULL,
[Col153] varchar (255) NULL,
[Col154] varchar (255) NULL,
[Col155] varchar (255) NULL,
[Col156] varchar (255) NULL,
[Col157] varchar (255) NULL,
[Col158] varchar (255) NULL,
[Col159] varchar (255) NULL,
[Col160] varchar (255) NULL,
[Col161] varchar (255) NULL,
[Col162] varchar (255) NULL,
[Col163] varchar (255) NULL,
[Col164] varchar (255) NULL,
[Col165] varchar (255) NULL,
[Col166] varchar (255) NULL,
[Col167] varchar (255) NULL,
[Col168] varchar (255) NULL,
[Col169] varchar (255) NULL,
[Col170] varchar (255) NULL,
[Col171] varchar (255) NULL,
[Col172] varchar (255) NULL,
[Col173] varchar (255) NULL,
[Col174] varchar (255) NULL,
[Col175] varchar (255) NULL,
[Col176] varchar (255) NULL,
[Col177] varchar (255) NULL,
[Col178] varchar (255) NULL,
[Col179] varchar (255) NULL,
[Col180] varchar (255) NULL,
[Col181] varchar (255) NULL,
[Col182] varchar (255) NULL,
[Col183] varchar (255) NULL,
[Col184] varchar (255) NULL,
[Col185] varchar (255) NULL,
[Col186] varchar (255) NULL,
[Col187] varchar (255) NULL,
[Col188] varchar (255) NULL,
[Col189] varchar (255) NULL,
[Col190] varchar (255) NULL,
[Col191] varchar (255) NULL,
[Col192] varchar (255) NULL,
[Col193] varchar (255) NULL,
[Col194] varchar (255) NULL,
[Col195] varchar (255) NULL,
[Col196] varchar (255) NULL,
[Col197] varchar (255) NULL,
[Col198] varchar (255) NULL,
[Col199] varchar (255) NULL,
[Col200] varchar (255) NULL,
[Col201] varchar (255) NULL,
[Col202] varchar (255) NULL,
[Col203] varchar (255) NULL,
[Col204] varchar (255) NULL,
[Col205] varchar (255) NULL,
[Col206] varchar (255) NULL,
[Col207] varchar (255) NULL,
[Col208] varchar (255) NULL,
[Col209] varchar (255) NULL,
[Col210] varchar (255) NULL,
[Col211] varchar (255) NULL,
[Col212] varchar (255) NULL,
[Col213] varchar (255) NULL,
[Col214] varchar (255) NULL,
[Col215] varchar (255) NULL,
[Col216] varchar (255) NULL,
[Col217] varchar (255) NULL,
[Col218] varchar (255) NULL,
[Col219] varchar (255) NULL,
[Col220] varchar (255) NULL,
[Col221] varchar (255) NULL,
[Col222] varchar (255) NULL,
[Col223] varchar (255) NULL,
[Col224] varchar (255) NULL,
[Col225] varchar (255) NULL,
[Col226] varchar (255) NULL,
[Col227] varchar (255) NULL,
[Col228] varchar (255) NULL,
[Col229] varchar (255) NULL,
[Col230] varchar (255) NULL,
[Col231] varchar (255) NULL,
[Col232] varchar (255) NULL,
[Col233] varchar (255) NULL,
[Col234] varchar (255) NULL,
[Col235] varchar (255) NULL,
[Col236] varchar (255) NULL,
[Col237] varchar (255) NULL,
[Col238] varchar (255) NULL,
[Col239] varchar (255) NULL,
[Col240] varchar (255) NULL,
[Col241] varchar (255) NULL,
[Col242] varchar (255) NULL,
[Col243] varchar (255) NULL,
[Col244] varchar (255) NULL,
[Col245] varchar (255) NULL,
[Col246] varchar (255) NULL,
[Col247] varchar (255) NULL,
[Col248] varchar (255) NULL,
[Col249] varchar (255) NULL,
[Col250] varchar (255) NULL,
[Col251] varchar (255) NULL,
[Col252] varchar (255) NULL,
[Col253] varchar (255) NULL,
[Col254] varchar (255) NULL,
[Col255] varchar (255) NULL,
[Col256] varchar (255) NULL,
[Col257] varchar (255) NULL,
[Col258] varchar (255) NULL,
[Col259] varchar (255) NULL,
[Col260] varchar (255) NULL,
[Col261] varchar (255) NULL,
[Col262] varchar (255) NULL,
[Col263] varchar (255) NULL,
[Col264] varchar (255) NULL,
[Col265] varchar (255) NULL,
[Col266] varchar (255) NULL,
[Col267] varchar (255) NULL,
[Col268] varchar (255) NULL,
[Col269] varchar (255) NULL,
[Col270] varchar (255) NULL,
[Col271] varchar (255) NULL,
[Col272] varchar (255) NULL,
[Col273] varchar (255) NULL,
[Col274] varchar (255) NULL,
[Col275] varchar (255) NULL,
[Col276] varchar (255) NULL,
[Col277] varchar (255) NULL,
[Col278] varchar (255) NULL,
[Col279] varchar (255) NULL,
[Col280] varchar (255) NULL,
[Col281] varchar (255) NULL,
[Col282] varchar (255) NULL,
[Col283] varchar (255) NULL,
[Col284] varchar (255) NULL,
[Col285] varchar (255) NULL,
[Col286] varchar (255) NULL,
[Col287] varchar (255) NULL,
[Col288] varchar (255) NULL,
[Col289] varchar (255) NULL,
[Col290] varchar (255) NULL,
[Col291] varchar (255) NULL,
[Col292] varchar (255) NULL,
[Col293] varchar (255) NULL,
[Col294] varchar (255) NULL,
[Col295] varchar (255) NULL,
[Col296] varchar (255) NULL,
[Col297] varchar (255) NULL,
[Col298] varchar (255) NULL,
[Col299] varchar (255) NULL,
[Col300] varchar (255) NULL,
[Col301] varchar (255) NULL,
[Col302] varchar (255) NULL,
[Col303] varchar (255) NULL,
[Col304] varchar (255) NULL,
[Col305] varchar (255) NULL,
[Col306] varchar (255) NULL,
[Col307] varchar (255) NULL,
[Col308] varchar (255) NULL,
[Col309] varchar (255) NULL,
[Col310] varchar (255) NULL,
[Col311] varchar (255) NULL,
[Col312] varchar (255) NULL,
[Col313] varchar (255) NULL,
[Col314] varchar (255) NULL,
[Col315] varchar (255) NULL,
[Col316] varchar (255) NULL,
[Col317] varchar (255) NULL,
[Col318] varchar (255) NULL,
[Col319] varchar (255) NULL,
[Col320] varchar (255) NULL,
[Col321] varchar (255) NULL,
[Col322] varchar (255) NULL,
[Col323] varchar (255) NULL,
[Col324] varchar (255) NULL,
[Col325] varchar (255) NULL,
[Col326] varchar (255) NULL,
[Col327] varchar (255) NULL,
[Col328] varchar (255) NULL,
[Col329] varchar (255) NULL,
[Col330] varchar (255) NULL,
[Col331] varchar (255) NULL,
[Col332] varchar (255) NULL,
[Col333] varchar (255) NULL,
[Col334] varchar (255) NULL,
[Col335] varchar (255) NULL,
[Col336] varchar (255) NULL,
[Col337] varchar (255) NULL,
[Col338] varchar (255) NULL,
[Col339] varchar (255) NULL,
[Col340] varchar (255) NULL,
[Col341] varchar (255) NULL,
[Col342] varchar (255) NULL,
[Col343] varchar (255) NULL,
[Col344] varchar (255) NULL,
[Col345] varchar (255) NULL,
[Col346] varchar (255) NULL,
[Col347] varchar (255) NULL,
[Col348] varchar (255) NULL,
[Col349] varchar (255) NULL,
[Col350] varchar (255) NULL,
[Col351] varchar (255) NULL,
[Col352] varchar (255) NULL,
[Col353] varchar (255) NULL,
[Col354] varchar (255) NULL,
[Col355] varchar (255) NULL,
[Col356] varchar (255) NULL,
[Col357] varchar (255) NULL,
[Col358] varchar (255) NULL,
[Col359] varchar (255) NULL,
[Col360] varchar (255) NULL,
[Col361] varchar (255) NULL,
[Col362] varchar (255) NULL,
[Col363] varchar (255) NULL,
[Col364] varchar (255) NULL,
[Col365] varchar (255) NULL,
[Col366] varchar (255) NULL,
[Col367] varchar (255) NULL,
[Col368] varchar (255) NULL,
[Col369] varchar (255) NULL,
[Col370] varchar (255) NULL,
[Col371] varchar (255) NULL,
[Col372] varchar (255) NULL,
[Col373] varchar (255) NULL,
[Col374] varchar (255) NULL,
[Col375] varchar (255) NULL,
[Col376] varchar (255) NULL,
[Col377] varchar (255) NULL,
[Col378] varchar (255) NULL,
[Col379] varchar (255) NULL,
[Col380] varchar (255) NULL,
[Col381] varchar (255) NULL,
[Col382] varchar (255) NULL,
[Col383] varchar (255) NULL,
[Col384] varchar (255) NULL,
[Col385] varchar (255) NULL,
[Col386] varchar (255) NULL,
[Col387] varchar (255) NULL,
[Col388] varchar (255) NULL,
[Col389] varchar (255) NULL,
[Col390] varchar (255) NULL,
[Col391] varchar (255) NULL,
[Col392] varchar (255) NULL,
[Col393] varchar (255) NULL,
[Col394] varchar (255) NULL,
[Col395] varchar (255) NULL,
[Col396] varchar (255) NULL,
[Col397] varchar (255) NULL,
[Col398] varchar (255) NULL,
[Col399] varchar (255) NULL,
[Col400] varchar (255) NULL,
[Col401] varchar (255) NULL,
[Col402] varchar (255) NULL,
[Col403] varchar (255) NULL,
[Col404] varchar (255) NULL,
[Col405] varchar (255) NULL,
[Col406] varchar (255) NULL,
[Col407] varchar (255) NULL,
[Col408] varchar (255) NULL,
[Col409] varchar (255) NULL,
[Col410] varchar (255) NULL,
[Col411] varchar (255) NULL,
[Col412] varchar (255) NULL,
[Col413] varchar (255) NULL,
[Col414] varchar (255) NULL,
[Col415] varchar (255) NULL,
[Col416] varchar (255) NULL,
[Col417] varchar (255) NULL,
[Col418] varchar (255) NULL,
[Col419] varchar (255) NULL,
[Col420] varchar (255) NULL,
[Col421] varchar (255) NULL,
[Col422] varchar (255) NULL,
[Col423] varchar (255) NULL,
[Col424] varchar (255) NULL,
[Col425] varchar (255) NULL,
[Col426] varchar (255) NULL,
[Col427] varchar (255) NULL,
[Col428] varchar (255) NULL,
[Col429] varchar (255) NULL,
[Col430] varchar (255) NULL,
[Col431] varchar (255) NULL,
[Col432] varchar (255) NULL,
[Col433] varchar (255) NULL,
[Col434] varchar (255) NULL,
[Col435] varchar (255) NULL,
[Col436] varchar (255) NULL,
[Col437] varchar (255) NULL,
[Col438] varchar (255) NULL,
[Col439] varchar (255) NULL,
[Col440] varchar (255) NULL,
[Col441] varchar (255) NULL,
[Col442] varchar (255) NULL,
[Col443] varchar (255) NULL,
[Col444] varchar (255) NULL,
[Col445] varchar (255) NULL,
[Col446] varchar (255) NULL,
[Col447] varchar (255) NULL,
[Col448] varchar (255) NULL,
[Col449] varchar (255) NULL,
[Col450] varchar (255) NULL,
[Col451] varchar (255) NULL,
[Col452] varchar (255) NULL,
[Col453] varchar (255) NULL,
[Col454] varchar (255) NULL,
[Col455] varchar (255) NULL,
[Col456] varchar (255) NULL,
[Col457] varchar (255) NULL,
[Col458] varchar (255) NULL,
[Col459] varchar (255) NULL,
[Col460] varchar (255) NULL,
[Col461] varchar (255) NULL,
[Col462] varchar (255) NULL,
[Col463] varchar (255) NULL,
[Col464] varchar (255) NULL,
[Col465] varchar (255) NULL,
[Col466] varchar (255) NULL,
[Col467] varchar (255) NULL,
[Col468] varchar (255) NULL,
[Col469] varchar (255) NULL,
[Col470] varchar (255) NULL,
[Col471] varchar (255) NULL,
[Col472] varchar (255) NULL,
[Col473] varchar (255) NULL,
[Col474] varchar (255) NULL,
[Col475] varchar (255) NULL,
[Col476] varchar (255) NULL,
[Col477] varchar (255) NULL,
[Col478] varchar (255) NULL,
[Col479] varchar (255) NULL,
[Col480] varchar (255) NULL,
[Col481] varchar (255) NULL,
[Col482] varchar (255) NULL,
[Col483] varchar (255) NULL,
[Col484] varchar (255) NULL,
[Col485] varchar (255) NULL,
[Col486] varchar (255) NULL,
[Col487] varchar (255) NULL,
[Col488] varchar (255) NULL,
[Col489] varchar (255) NULL,
[Col490] varchar (255) NULL,
[Col491] varchar (255) NULL,
[Col492] varchar (255) NULL,
[Col493] varchar (255) NULL,
[Col494] varchar (255) NULL,
[Col495] varchar (255) NULL,
[Col496] varchar (255) NULL,
[Col497] varchar (255) NULL,
[Col498] varchar (255) NULL,
[Col499] varchar (255) NULL,
[Col500] varchar (255) NULL,
[Col501] varchar (255) NULL,
[Col502] varchar (255) NULL,
[Col503] varchar (255) NULL,
[Col504] varchar (255) NULL,
[Col505] varchar (255) NULL,
[Col506] varchar (255) NULL,
[Col507] varchar (255) NULL,
[Col508] varchar (255) NULL,
[Col509] varchar (255) NULL,
[Col510] varchar (255) NULL,
[Col511] varchar (255) NULL,
[Col512] varchar (255) NULL,
[Col513] varchar (255) NULL,
[Col514] varchar (255) NULL,
[Col515] varchar (255) NULL,
[Col516] varchar (255) NULL,
[Col517] varchar (255) NULL,
[Col518] varchar (255) NULL,
[Col519] varchar (255) NULL,
[Col520] varchar (255) NULL,
[Col521] varchar (255) NULL,
[Col522] varchar (255) NULL,
[Col523] varchar (255) NULL,
[Col524] varchar (255) NULL,
[Col525] varchar (255) NULL,
[Col526] varchar (255) NULL,
[Col527] varchar (255) NULL,
[Col528] varchar (255) NULL,
[Col529] varchar (255) NULL,
[Col530] varchar (255) NULL,
[Col531] varchar (255) NULL,
[Col532] varchar (255) NULL,
[Col533] varchar (255) NULL,
[Col534] varchar (255) NULL,
[Col535] varchar (255) NULL,
[Col536] varchar (255) NULL,
[Col537] varchar (255) NULL,
[Col538] varchar (255) NULL,
[Col539] varchar (255) NULL,
[Col540] varchar (255) NULL,
[Col541] varchar (255) NULL,
[Col542] varchar (255) NULL,
[Col543] varchar (255) NULL
)

and DTS is reporting an error like this :
"
Error at destination for row number 13024. Errors encountered so far
in this task: 1.
The statement has been terminated.
Cannot create a row of size 9997 which is greater than the allowable
maximum of 8060.
"
i also tried "nvarchar" , "ntext" ect. but none of them worked. :((
if you need any further information about the proccess please let me
know
i will respond/answer as soon as possible.
thanks a lot again...

Tunc Ovacik

[Erland Sommarskog]

panic attack (tu*********@gmail.com) writes:

CREATE TABLE [nwind].[dbo].[DDD] (
[Col001] varchar (255) NULL,
[Col002] varchar (255) NULL,
[Col003] varchar (255) NULL,
...
[Col543] varchar (255) NULL
)

The table appears somewhat funny. Does the table really reflect your
business rules? 255 * 543 is 138465 and with a maximum row size of
8060 in SQL Server, this is not like to turn out well.

i also tried "nvarchar" , "ntext" ect. but none of them worked. :((

If you tried 543 ntext columns, I can understand why that fails. A ntext
column has a 16-byte point which is in the the row, and the real data is
elsewhere. 543 * 16 is 8688, so you can't have all those text pointers
on a single row.

Does your input file really have 543 input fields?


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[NiTiN]

panic attack wrote:

Erland Sommarskog wrote:

panic attack (tu*********@gmail.com) writes:

-- SNIP --

CREATE TABLE [nwind].[dbo].[DDD] (
[Col001] varchar (255) NULL,

-- SNIP --

[Col543] varchar (255) NULL
)

-- SNIP --
Hi!

I know I said earlier that you could use long data types, and the table
you're trying to create is in accordance with my statement earlier.
However, I should have added that each column stores a pointer in the
row and the actual data is stored in a different location. If you add
up the sizes of the pointers along with other row data they should be
below the ~8K limit too. I guess I should be more accurate when I say
something in future like those people who speak legal-ese. The DDL
query was really funny to look at, and it's the first time I ever used
the "read more" link on Google Groups.

I would suggest that you partition your tables vertically so you have
some of the columns in one table and the other columns in another table
(...or perhaps more than 2 tables, depending on the sizes... I'm not
really good at the math).

N.I.T.I.N.

PS: I hope I never have to deal with such a monstrosity - a table that
has so many columns. I once had to deal with 36 columns and that was
too much for me as a developer (that was before my days as a DBA). I
split it up into 3 tables though people may say it is less efficient to
have 3 queries instead of one (remember the days when people said you
should use assembly language as the code is smaller & faster?).

PPS: No offence to assembly language developers in the last 'PS'. I
totally respect people who still use assembly, but for me it's just a
little too much source code to think straight - I'd spend a whole hour
doing something that I could do in 15 minutes with VB, Java or C# (when
equipped with the right IDE, of course!).

[panic attack]

ofcourse it has 543 columns :)))
the data includes records for about 6 years, 20 quarters and 60 months
back data and for each period it has 6 parameters and some other
columns info(text).
so if you do the math;
(6 * 6) + ( 20 * 6 ) + ( 60 * 6 ) = 516 columns.

and if you add the other columns the result is 543
as i said i tried various types to get the data in to SQL
nvarchar , varchar , ntext etc.
but none of them worked out.

what do you think Erland? do we have a chance to get over this problem?
or it is not possible to get the data into SQL Server 2000?

NOTE : By the way there is another data that we are importing to Sql
Server 2000.
and it has 124 columns. no problems occur while getting the data into
Sql Server 2000. if we apply the same logic as you did ,
124 * 255 = 31620
so it is also bigger than 8060. but we are doing the operation without
any problems.
it seems that there is a contradiction doesnt it?

what about SQL Server 2005? is there any limitation at sql server 2005
about the row size?

Tunc

Erland Sommarskog wrote:

panic attack (tu*********@gmail.com) writes:

CREATE TABLE [nwind].[dbo].[DDD] (
[Col001] varchar (255) NULL,
[Col002] varchar (255) NULL,
[Col003] varchar (255) NULL,
...
[Col543] varchar (255) NULL
)

The table appears somewhat funny. Does the table really reflect your
business rules? 255 * 543 is 138465 and with a maximum row size of
8060 in SQL Server, this is not like to turn out well.

i also tried "nvarchar" , "ntext" ect. but none of them worked. :((

If you tried 543 ntext columns, I can understand why that fails. A ntext
column has a 16-byte point which is in the the row, and the real data is
elsewhere. 543 * 16 is 8688, so you can't have all those text pointers
on a single row.

Does your input file really have 543 input fields?


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[panic attack]

hi again...
partitioning the table is one of the solvation but for our production
system unfortunately it is not proper for use. :((
cause we have lots of clients and if we partition the tables for our
each client there are gonna be enourmus number of tables, so it is not
possible to deal with those number of tables right now...

hence, we need to get the data at once, in one table.
perhaps we can union some columns into one column. but again there will
be some leck of use of the data while manipulating it.
as you can see it seems not good... :((

i hope that erland may advise another solvation about the problem.
or we may upgrade the database to sql server 2005 if it is gonna help
us getting the data into one table without any problems.

i really appreciated for your help.
thanks a lot.
best regards.

Tunc

NiTiN yazdi:

panic attack wrote:

Erland Sommarskog wrote:

panic attack (tu*********@gmail.com) writes:

-- SNIP --

CREATE TABLE [nwind].[dbo].[DDD] (
[Col001] varchar (255) NULL,

-- SNIP --

[Col543] varchar (255) NULL
)

-- SNIP --
Hi!

I know I said earlier that you could use long data types, and the table
you're trying to create is in accordance with my statement earlier.
However, I should have added that each column stores a pointer in the
row and the actual data is stored in a different location. If you add
up the sizes of the pointers along with other row data they should be
below the ~8K limit too. I guess I should be more accurate when I say
something in future like those people who speak legal-ese. The DDL
query was really funny to look at, and it's the first time I ever used
the "read more" link on Google Groups.

I would suggest that you partition your tables vertically so you have
some of the columns in one table and the other columns in another table
(...or perhaps more than 2 tables, depending on the sizes... I'm not
really good at the math).

N.I.T.I.N.

PS: I hope I never have to deal with such a monstrosity - a table that
has so many columns. I once had to deal with 36 columns and that was
too much for me as a developer (that was before my days as a DBA). I
split it up into 3 tables though people may say it is less efficient to
have 3 queries instead of one (remember the days when people said you
should use assembly language as the code is smaller & faster?).

PPS: No offence to assembly language developers in the last 'PS'. I
totally respect people who still use assembly, but for me it's just a
little too much source code to think straight - I'd spend a whole hour
doing something that I could do in 15 minutes with VB, Java or C# (when
equipped with the right IDE, of course!).

[Erland Sommarskog]

panic attack (tu*********@gmail.com) writes:

ofcourse it has 543 columns :)))
the data includes records for about 6 years, 20 quarters and 60 months
back data and for each period it has 6 parameters and some other
columns info(text).
so if you do the math;
(6 * 6) + ( 20 * 6 ) + ( 60 * 6 ) = 516 columns.

That sounds like 516 rows rows to me. Not 516 columns. At least with a
proper data model. Or this a staging table?

what do you think Erland? do we have a chance to get over this problem?
or it is not possible to get the data into SQL Server 2000?

As NiTiN said, you will have to split the table in two vertically. Note
that it does not have to affect queries, as you can construct views that
combine them. You would then have to use a format file to make it possible
to only selected columns.

NOTE : By the way there is another data that we are importing to Sql
Server 2000.
and it has 124 columns. no problems occur while getting the data into
Sql Server 2000. if we apply the same logic as you did ,
124 * 255 = 31620
so it is also bigger than 8060. but we are doing the operation without
any problems.
it seems that there is a contradiction doesnt it?

No. What matters is the actual row size, not the possible max.

what about SQL Server 2005? is there any limitation at sql server 2005
about the row size?

No. SQL 2005 is yet another option. SQL 2005 permits rows to span multiple
pages.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[panic attack]

thanks for your fast answers.
there is one last thing that i need to ask...!!
now i decided to partition the data vertically
at this point there is one thing i need to ask...

now here is the case :

after partitioning the table it is gonna look like this:

table 1
-------------------------------------------------
column1 column2 ... column250
record1 record2 ... record250
table2
--------------------------------------------------
column1 column2 ... column250
record1 record2 ... record250

at this point i need to combine these tables( mentioned above)
vertically right?

how am i gonna do the combine operation after partitioning the table
into 2 or 3?

i tried to combine them by using "UNION" operator but i guess it works
for combining the tables horizontally.

thanks a lot
best regards.

tunc ovacik

Erland Sommarskog yazdi:

panic attack (tu*********@gmail.com) writes:

ofcourse it has 543 columns :)))
the data includes records for about 6 years, 20 quarters and 60 months
back data and for each period it has 6 parameters and some other
columns info(text).
so if you do the math;
(6 * 6) + ( 20 * 6 ) + ( 60 * 6 ) = 516 columns.

That sounds like 516 rows rows to me. Not 516 columns. At least with a
proper data model. Or this a staging table?

what do you think Erland? do we have a chance to get over this problem?
or it is not possible to get the data into SQL Server 2000?

As NiTiN said, you will have to split the table in two vertically. Note
that it does not have to affect queries, as you can construct views that
combine them. You would then have to use a format file to make it possible
to only selected columns.

NOTE : By the way there is another data that we are importing to Sql
Server 2000.
and it has 124 columns. no problems occur while getting the data into
Sql Server 2000. if we apply the same logic as you did ,
124 * 255 = 31620
so it is also bigger than 8060. but we are doing the operation without
any problems.
it seems that there is a contradiction doesnt it?

No. What matters is the actual row size, not the possible max.

what about SQL Server 2005? is there any limitation at sql server 2005
about the row size?

No. SQL 2005 is yet another option. SQL 2005 permits rows to span multiple
pages.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Roy Harvey]

On 4 Aug 2006 01:17:45 -0700, "panic attack" <tu*********@gmail.com>
wrote:

>partitioning the table is one of the solvation but for our production
system unfortunately it is not proper for use. :((
cause we have lots of clients and if we partition the tables for our
each client there are gonna be enourmus number of tables, so it is not
possible to deal with those number of tables right now...

The idea was not to partition the table by client, but to normalize it
so that the time periods are rows, not columns (for one example.)

My suggestion is to define multiple staging tables, each with a subset
of the columns. All would have to include the key column(s), then
each would include a different part of the rest. One data import for
each table, of course, selective on columns. Then when the data is
in, JOIN on the keys.

Roy Harvey
Beacon Falls, CT

[Erland Sommarskog]

panic attack (tu*********@gmail.com) writes:

thanks for your fast answers.
there is one last thing that i need to ask...!!
now i decided to partition the data vertically
at this point there is one thing i need to ask...

now here is the case :

after partitioning the table it is gonna look like this:

table 1
-------------------------------------------------
column1 column2 ... column250
record1 record2 ... record250
table2
--------------------------------------------------
column1 column2 ... column250
record1 record2 ... record250

at this point i need to combine these tables( mentioned above)
vertically right?

how am i gonna do the combine operation after partitioning the table
into 2 or 3?

Hopefully there is a key in the data you import. Else you are in dire
straits. Say that columns 1 and 2 are the keys. Then you could define
a view as:

CREATE VIEW united AS
SELECT a.col1, a.col2, ... a.col250,
b.col251, ... b.col543
FROM tbl1 a
JOIN tbl2 b ON a.col1 = b.col1
AND a.col2 = b.col2
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx