January 23, 2014 at 9:27 am
Hello,
Our company is migrating a Microsoft Access 2010 backend database to a SQL Server 2008 database. One of the memo fields in the Access backend can store up to 150 Kb of Unicode data. To store this data in SQL server, we found that we can use the following data types:
ntext = (2^30) - 1 = 1,073,741,823 bytes.
nvarchar(max) = (2^31) - 1 = 2,147,483,647 bytes.
Because ntext will be deprecated in future releases of SQL Server, the only good alternative to store an Access memo field in SQL server is to use nvarchar(max), which is what Microsoft recommends for large Unicode texts.
Storing a large amount of text like 150 Kb in an nvarchar(max) field using only SQL server works as expected. However, if Access is used to store the data in a table linked to SQL server, the maximum number of characters allowed is only 4000. We found that this limitation is imposed by the ODBC driver that limits nvarchar(max) to 4000 characters.
The connection string we are currently using to link a table to SQL server is this:
ODBC;DRIVER={SQL Server Native Client 10.0};SERVER= SQLEXPRESS;DATABASE=TestDB;Trusted_Connection=No;UID=uid;PWD=pwd;
Has anyone found a solution for this limitation storing large amounts of data in a Microsoft Access memo field mapped to an nvarchar(max) data field in a SQL Server database?
Thank you,
George
January 23, 2014 at 1:15 pm
Have you tried SSIS instead? You may be able to right click on the database, go to all tasks->import data, and get the data from Access to SQL Server in that way.
January 28, 2014 at 9:16 am
Thank you for replying. We used "Microsoft SQL Server Migration Assistant for Access" and the data was migrated from Access to SQL server with no problems.
When you use the memo fields in SQL server they work fine but when you go back to Access, the ODBC does not let you store more than 4000 characters in the linked memo field.
January 28, 2014 at 10:01 am
If you are building your own conversion process I may have seen something similar when working in vbscript with varchar(max). I was having lots of difficulty working with the new LOB types when I had to pass data between the application and the database. I ended up using a stored procedure and exposing a parameter as a text type and inside the stored procedure I cast it to varchar(max). It had something to do with what vbscript could handle in terms of SQL datatypes.
I also found this link to a msdn blog.
Hope this helps
January 30, 2014 at 9:29 am
SOLVED!
If you create the following table in SQL Server 2008 and link it to Ms Access, the only way to copy-paste a very large amount of text in Access into the memo field is by using code. Opening the linked table in view mode and copy-pasting the text manually causes the error “ODBC call failed. [Microsoft][SQL Server native Client 10.0]String data, right trunctation (#0)”
create table tblMemo (
pkey int not null,
sqlmemo nvarchar(max),
primary key (pkey)
);
We found that the problem is NOT the ODBC but the driver. If you link the table using the {SQL Server} driver instead of the native client version, you will be able to update the memo field in the linked table using code. We used the following ADO code in VB and were able to update the memo field up to 800K, which is much more than what we really need:
Dim adoCnn As adoDB.Connection
Dim adoRst As adoDB.Recordset
Set adoCnn = New adoDB.Connection
adoCnn.Provider = "MSDASQL"
adoCnn.ConnectionString = "DRIVER={SQL Server};SERVER=SQLEXPRESS;DATABASE=MyTestDB;Trusted_Connection=No;UID=myuid;PWD=mypwd;TABLE=dbo.tblMemo;"
adoCnn.Open
Set adoRst = New adoDB.Recordset
adoRst.Open "SELECT * FROM tblMemo", adoCnn, adOpenStatic, adLockOptimistic
adoRst.AddNew
adoRst!pkey = 1
adoRst!sqlmemo = <assign variable here with large amount of text>
adoRst.Update
adoRst.Close
Set adoRst = Nothing
adoCnn.Close
Set adoCnn = Nothing
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply