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
431 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
  • 7
  • 3
11 Comments
 

Author Comment

by:EuniceRH
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
@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
 

Author Comment

by:EuniceRH
Comment Utility
sorry another comment.  If it's DAO, why can I save 4000 + characters using ntext but not nvarchar(max)?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:EuniceRH
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Mike, I have found how to use nvarchar(max) for 4000+ characters: it's using ADO.
0
 

Author Closing Comment

by:EuniceRH
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now