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 11 8620
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" <pe***@iib.ws> wrote in message
news:81************************@posting.google.com ... 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
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!
Peter Winning (pe***@iib.ws) writes: 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
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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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!
Peter Winning (pe***@iib.ws) writes: 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
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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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!
Peter Winning (pe***@iib.ws) writes: 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.
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.
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
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.public.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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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(memo1)/2,datalength(memo2)/2,datalength(memo3)/2,datalength(memo4
)/2 FROM MyAccessTable
John
"Peter Winning" <pe***@iib.ws> wrote in message
news:40**********************@news.newsgroups.ws.. . 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!
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" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1... Peter Winning (pe***@iib.ws) writes: 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
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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
J. Hall (re*************@a-hall.com) writes: 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??
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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Excellent thanks for clearing that up.
Many thanks,
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1... J. Hall (re*************@a-hall.com) writes: 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??
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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Tommy Christian |
last post by:
Hi!
Anyone who knows about saving serialized data to database,
coz I have a problem with that. If I just serialize my session data and
then deserialize it, it works. But when I save it...
|
by: Mark |
last post by:
How can i get the maximum allowed length for a field in a SQL database?
ie the value defined when creating a text field in a table, in a SQL database.
As i want to set the max length of a text...
|
by: Tony Lugg |
last post by:
I have an application with a document management form. When users add
documents to the form, I call the API function SHGetFileInfo to get
the associated large and small icons for the file. These...
|
by: Tony Lugg |
last post by:
I have an application with a document management form. When users add
documents to the form, I call the API function SHGetFileInfo to get
the associated large and small icons for the file. These...
|
by: Alan |
last post by:
Hi,
I'm converting a database application from Access 97 to C#/SQL Server. Old
database contains some images in OLE fields. I've figured out that there's
OLE header preceeding actual image data...
|
by: MP |
last post by:
Greets,
context: vb6/ado/.mdb/jet 4.0 (no access)/sql
beginning learner, first database, planning stages
(I think the underlying question here is whether to normalize or not to
normalize this...
|
by: conspiracy cam |
last post by:
Hi.
Trying to create a program which splits up a large amount of text into
small pieces, puts these pieces into a database, allowing for later
reuse.
The problem is since each text file is a...
|
by: SSG |
last post by:
In my database table , I have field to store the text,..
I am tryinf to find the length of the filed in ASP like below
response.write len(rs("cname1"))
It is displaying the value , when the...
|
by: TechnoAtif |
last post by:
<?php
include "dbconnect.php";
include "commonFunc.php"; ?>
<!---------------------------------->
<table width="80%" border="1" cellpadding="2" cellspacing="0">
<tr >
<td...
|
by: sialater |
last post by:
Hello,
I realise there are a lot of topics related to this problem but many of what I have found has run cold or unresolved. What I have is an addressbook clone where there are groups which have...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |