Solved

ODBC error 4000 character limit from Access 2007 to SQL Server 2008 R2 using SQLNCLI or new Microsoft ODBC Driver 11 for SQL Server

Posted on 2014-10-31
11
450 Views
Last Modified: 2014-11-07
I posted a question on another forum, but I'm hoping you will have an answer for me.  Here is the link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f8e049f2-8eb4-4df8-93df-2a90559b3bab/odbc-error-4000-character-limit-from-access-to-sql-server-2008-r2-using-sqlncli-or-new-microsoft?forum=accessdev

I get a 3146 ODBC error when I try to save 4000+ characters from Access to SQL Server 2008 R2 Express to a nvarchar(max) column.  This is using either SQLNCLI 10 or 11 or new Microsoft ODBC Driver 11 for SQL Server.  I have no problem using SQLNCLI saving 4000+ characters to a ntext column.  I can save up to 4000 characters just fine with the nvarchar(max) column, so I do not have the 255 character limitation.  I am using DAO recordsets. I also tried updating the Service Packs, and upgrading to SQL Server 2012 Express and I'm pretty sure I tried SQL Server 2014 Express.
 
The DB Engine error says
Error number: 0
 [Microsoft][ODBC Driver 11 for SQL Server]Connection is busy with results for another command
 then
 Error number: 3146
 ODBC--call failed.

I also tried sp_tableoption N'tblMyTable', 'large value types out of row', 'ON' without success.
If you're looking at the link, Van Dinh's reply 9/23/14 was informative.
TIAFYH
Eunice
0
Comment
Question by:EuniceRH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
11 Comments
 

Author Comment

by:EuniceRH
ID: 40416648
ok this has inspired me to try something else.  By the way it is a 32 bit SQL Server database.  In Microsoft Office Access 2007, I am replacing my references in the VBA, from DAO 3.6 to "Microsoft office 12.0 access database engine Object Library".  We'll see how it goes.
0
 
LVL 57
ID: 40417105
Couple of comments:

1. 3146 is a generic error...there may be additional errors behind it that will help determine the cause.   Make sure your enumerating the errors collection in VBA to get all the errors.

2. Watch out for any fields where your trying to insert a null into a field that does not allow nulls (ie.  bit data type).   This can trigger this error.

3. Take a look at using ADO.    While a latter version of the JET/ACE provider might yield a result over the DAO 3.6, It's still fundamentally "DAO".  

  JET/ACE's page size is 4096 bytes and everything with DAO is structured towards that.  I would hazard a guess that in some way, your bumping into that limit.  

  Using ADO rather than ODBC would be a fundamentally different approach.

Jim.
0
 

Author Comment

by:EuniceRH
ID: 40419724
@Jim
How about the "DB Engine error says
 Error number: 0
  [Microsoft][ODBC Driver 11 for SQL Server]Connection is busy with results for another command"?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:EuniceRH
ID: 40419728
sorry another comment.  If it's DAO, why can I save 4000 + characters using ntext but not nvarchar(max)?
0
 
LVL 57
ID: 40424233
Sorry I lost track of this.   The error message may be a bit of a misdirection.   See the following MSKB article on extending the error handling:

How To Get More Information on the ODBC Call Failed Error
http://support.microsoft.com/kb/161288

to make sure your getting all errors.

Jim.
0
 

Author Comment

by:EuniceRH
ID: 40424336
I did DBEngine error above.  See above where it says
"The DB Engine error says
 Error number: 0
  [Microsoft][ODBC Driver 11 for SQL Server]Connection is busy with results for another command"

With my Tools/References I do not see rdoError.
0
 

Author Comment

by:EuniceRH
ID: 40424347
On another note, I did try ADO and it worked, and it was easier than I thought, because they do have .AddNew and .Update among other methods.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40424422
Has something to do with the page size then.   DAO was really written to deal with JET/ACE fundamentally, so I'm sure many of it's operations revolve around the page size of 4096 bytes.

Glad to to hear that ADO did it for you.

Jim.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40427621
SQL Server page size is 8k, although you don't get to use quite all of it (there's a couple of pointers and a list of record pointers to fit in there somewhere too!). You doubtless realise that 4000+ unicode characters is getting very close to 8K.

Microsoft docs define nvarchar [ ( n | max ) ] as "Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes."

You are providing more than 4000 unicode characters from Access, so it fails (sorry!). As you have discovered, turn it into NTEXT instead.

hth

Mike
0
 

Author Comment

by:EuniceRH
ID: 40428898
Thanks Mike, I have found how to use nvarchar(max) for 4000+ characters: it's using ADO.
0
 

Author Closing Comment

by:EuniceRH
ID: 40428913
regarding accepted solution:  Jim Dettman did earlier provide several options all of which would help in some situations.  The ADO approach was one of them and that was the fix.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how the fundamental information of how to create a table.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question