How to avoid 4000 character field lenght restriction ?

[gransbpa]

Hi there,

I have an SQL Server database, which I want to update from an Access database. One field in this Access database is a memo field, having an allmost unlimited capacity. When I try to update the field in the SQL Server database from this field, the maximum capacity in the SQL Server database is 4000 characters. When I use the varchar datatype, I am not able to change the design field attribute in anything greater than 4000. When I use the ntext datatype, it has a standard length of 16 (?) which can't be changed at all. I definitely need a field length of more than 4000 however.
Does anyone know how to get this done? Any help will be greatly appreciated.

 

[hmckillop]

ntext type is 16bytes, which equates to 2^16, which is signigicantly more than 400 characters,


"I'm living so far beyond my income that we may almost be said to be living apart

 

[gransbpa]

You're absolutely right, but that's the point: I can enter no more than 4000 characters, while I should be able to enter more than 65000. I am under the impression that there is some limitation on the field length in the SQL Server database no matter what data type: whether I use nvarchar or ntext, I can enter only 4000 character and not 1 character more!

 

[gmmastros]

ntext will allow more than 4000 characters. The limitation you are seeing is probably caused by the method in which you are trying to enter the data. If you are using the enterprise manager to do this, you will have problems.

However, if you create an app to enter the data, then you shouldn't have any problems.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gransbpa]

Hi GMMastros,

I am not using the enterprise manager, I am using an SQL statement from within the Access database. This database has a local table containing a temporary value in a memo-field (with more than 4000 characters), and this value is transferred to the SQL server db. The Sql server database is accessed through ODBC by Access; the method I use is INSERT INTO sqlservertable (SELECT * FROM accesstable). But you could very well be right, maybe it is the method, in this case the ODBC methodology, which poses contstraints to the number of characters.

 

[gmmastros]

I would recommend you try using the Import functionality from with the enterprise manager. You may have more luck.

Drill down to your database.
Right click on it. All Tasks -> Import Data...

You'll be able to select the source for the data (including Access databases), select the destination, etc... Let us know how you make out.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gransbpa]

Hi George, thanks for the tip. I tried,and succeeded in making the connection, but got a strange error message: the connection crashed at a certain line, which was exactly the record containing the field with too much data. However, when double clicking the error line for the details, the problem did not seem to be the great amount of data (>4000), but it was a primary key failure: it said something like: could not insert double primary key. However, I am quite certain that the keyfield value for that record was not a duplicate of anything. Is it possible that maybe SQL Server imports it's data in blocks of 4000 characters, and that the second import step caused a PK failure in this particular case?
Anyway, my weekend starts here, so I'll have a few nights to sleep on it. Thanks for all the help!

 

[mrdenny]

The text and ntext fields in SQL Server are only 16 bits becuase they actually hold a binary pointer which directs the SQL Server to the actual data location. Data for text and ntext fields is not actually storred within the record. It's storred in a seperate space within the database file, and the only data kept within the database record is the binary pointer to this location.

If you are getting a primary key error when importing the data into SQL, import the data into a tempory table which has no primary key. Then insert the data from the temp table into the actual table with SQL. This will allow to you get it into SQL, and then work with it. Data is much easier to move around and work with if you only have one platform to deal with.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)