Database field length problem

[Peter]

Hi everyone
I had an access database running as the source for a website but it
has become too large to run correctly so it has been ported to MS-SQL
the problem is that 4 of the fields were Memo fields in access and as
such are 5000+ characters long each this overflows the allowed size on
the SQL server (8192)

Is there a way round without splitting those 4 fields into seperate
tales?? as this would cause a truly major re-write of the website

Thanks for any help
Further details available if required

[J. Hall]

Re: Database field length problem

If you use VarChar can you not set the max field length to 8000 characters?

I can't believe SQL has such limits, surely there's a way to automatically
use two rows for one record, or does this require additional programming in
ASP?

Cheers, Ash




"Peter" <peter@iib.ws > wrote in message
news:81307dbc.0 406250528.ae1d3 d@posting.googl e.com...[color=blue]
> Hi everyone
> I had an access database running as the source for a website but it
> has become too large to run correctly so it has been ported to MS-SQL
> the problem is that 4 of the fields were Memo fields in access and as
> such are 5000+ characters long each this overflows the allowed size on
> the SQL server (8192)
>
> Is there a way round without splitting those 4 fields into seperate
> tales?? as this would cause a truly major re-write of the website
>
> Thanks for any help
> Further details available if required[/color]

[Peter Winning]

Re: Database field length problem

Hi there
When i try to import a flat text file where i have used the transform
tool to delare field sizes of 6000 for the four fields i get the
following error if they are varchar

cannot create a row of size 8366 which is greater than the allowable
maximum of 8060

If i map them as ntext i dont get an error but the data is truncated

Any ideas
Peter



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: Database field length problem

Peter Winning (peter@iib.ws) writes:[color=blue]
> When i try to import a flat text file where i have used the transform
> tool to delare field sizes of 6000 for the four fields i get the
> following error if they are varchar
>
> cannot create a row of size 8366 which is greater than the allowable
> maximum of 8060
>
> If i map them as ntext i dont get an error but the data is truncated[/color]

ntext is probably the way to go, since that is the only way to have
more than 8060 bytes of data on one row.

Why your ntext data is truncated I don't know, but then again I don't
know how import the data. A CREATE TABLE definition and a sample data
file could help. (You would have to pack the data file into a zip
file, since it surely would be wrecked by news transport, if you
posted it as text.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Peter Winning]

Re: Database field length problem

Hi
I am using the enterprise manager to upload the database and so far all
attempts no matter how the fields are transformed are still failing
I have tried mapping the fields as vchar, nvchar and ntext with the same
results in every case ie failure to complete or data truncation this has
also been attempted on the server itself
The upsizing wizard in access also failed to do the job



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: Database field length problem

Peter Winning (peter@iib.ws) writes:[color=blue]
> I am using the enterprise manager to upload the database and so far all
> attempts no matter how the fields are transformed are still failing
> I have tried mapping the fields as vchar, nvchar and ntext with the same
> results in every case ie failure to complete or data truncation this has
> also been attempted on the server itself
> The upsizing wizard in access also failed to do the job[/color]

I have no idea what Enterprise Manager is up to when it imports data;
I didn't even know that it had a function for it, and even less have I
used it.

My general experience of EM, though, is that it tends to occlude some
syntax in order to be helpful, when things go over its head, it leaves
you alone in the dark.

I would try to import the file with BCP, but since I don't know how your
text file looks like, I cannot suggest the exact command line. I repeat
from my previous posting:

A CREATE TABLE definition and a sample data file could help. (You would
have to pack the data file into a zip file, since it surely would be
wrecked by news transport, if you posted it as text.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Peter Winning]

Re: Database field length problem

Thank you
I have to confess that i am a complete novice with sql what is "BCP" and
where would i find some help in how to use it.
The database is a flat field one with about 30 fields 4 of which are
memo fields with very large amounts of data in each

Regards
Peter



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: Database field length problem

Peter Winning (peter@iib.ws) writes:[color=blue]
> I have to confess that i am a complete novice with sql what is "BCP" and
> where would i find some help in how to use it.[/color]

BCP is a command line tool that permits you to load large amount of
data from files. The files can be text files or binary. BCP is a bit
restricted in that the file has to be fairly square. That is, it not able
to sort out headers, unless you can find a square hole to put them in.

You can read more about BCP in Books Online.

Another alternative is DTS (Data Transformation Service), which is a more
versatile load tool, which I have never used my self though.
[color=blue]
> The database is a flat field one with about 30 fields 4 of which are
> memo fields with very large amounts of data in each[/color]

Well, it is up to you. If you don't want to post a CREATE TABLE statement
for your table and a sample data file, you don't have to. But then you will
have to find out how to load your file with BCP on your own, because I
don't really feel like guessing your table and data.

If you look in the SQL Server Program group, there is "Import and
Export Data". This takes you to the DTS wizard, which may be able to
guide all the way. But as I said, I have not used DTS. Then again,
there are some nice people in microsoft.publi c.sqlserver.dts who might
be able to help you if you go that way. But they, too, might want the
table definition and sample data.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[John Bell]

Re: Database field length problem

Hi

I don't think the DTS Import wizard will truncate the data if you import it
from an Access database! It could be that you are selecting the data in
Query Analyser which has a configurable value (Tools/Options/Results/Maximum
characters per column).

If you have a table such as

CREATE TABLE MyAccessTable ( id int, Memo1 ntext, Memo2 ntext, Memo3 ntext,
Memo4 ntext )

The you can see the number of characters using:
SELECT id,
datalength(memo 1)/2,datalength(me mo2)/2,datalength(me mo3)/2,datalength(me mo4
)/2 FROM MyAccessTable

John

"Peter Winning" <peter@iib.ws > wrote in message
news:40dc5ecd$0 $16435$c397aba@ news.newsgroups .ws...[color=blue]
> Hi there
> When i try to import a flat text file where i have used the transform
> tool to delare field sizes of 6000 for the four fields i get the
> following error if they are varchar
>
> cannot create a row of size 8366 which is greater than the allowable
> maximum of 8060
>
> If i map them as ntext i dont get an error but the data is truncated
>
> Any ideas
> Peter
>
>
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[J. Hall]

Re: Database field length problem

I've just looked at our WebMail application we're using, it uses a SQL
database to store the messages which generally are over 8000 characters - it
uses NTEXT as the datatype, with a length of '16', how on EARTH does that
relate to a VARCHAR field that has to be set to 8000??



"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns9514A53 A31CFFYazorman@ 127.0.0.1...[color=blue]
> Peter Winning (peter@iib.ws) writes:[color=green]
> > When i try to import a flat text file where i have used the transform
> > tool to delare field sizes of 6000 for the four fields i get the
> > following error if they are varchar
> >
> > cannot create a row of size 8366 which is greater than the allowable
> > maximum of 8060
> >
> > If i map them as ntext i dont get an error but the data is truncated[/color]
>
> ntext is probably the way to go, since that is the only way to have
> more than 8060 bytes of data on one row.
>
> Why your ntext data is truncated I don't know, but then again I don't
> know how import the data. A CREATE TABLE definition and a sample data
> file could help. (You would have to pack the data file into a zip
> file, since it surely would be wrecked by news transport, if you
> posted it as text.)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: Database field length problem

J. Hall (remove_this_as h@a-hall.com) writes:[color=blue]
> I've just looked at our WebMail application we're using, it uses a SQL
> database to store the messages which generally are over 8000 characters
> - it uses NTEXT as the datatype, with a length of '16', how on EARTH
> does that relate to a VARCHAR field that has to be set to 8000??[/color]

16 is the length of the pointer that is stored within the row. The data
itself is stored on separate pages. A varchar value on the other hand is
stored within the row, and since a row can not host more than 8060 bytes
of data, there is an upper limit.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[J. Hall]

Re: Database field length problem

Excellent thanks for clearing that up.

Many thanks,




"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns9517834 BC4F9AYazorman@ 127.0.0.1...[color=blue]
> J. Hall (remove_this_as h@a-hall.com) writes:[color=green]
> > I've just looked at our WebMail application we're using, it uses a SQL
> > database to store the messages which generally are over 8000 characters
> > - it uses NTEXT as the datatype, with a length of '16', how on EARTH
> > does that relate to a VARCHAR field that has to be set to 8000??[/color]
>
> 16 is the length of the pointer that is stored within the row. The data
> itself is stored on separate pages. A varchar value on the other hand is
> stored within the row, and since a row can not host more than 8060 bytes
> of data, there is an upper limit.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]