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
456 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 58
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 58
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 58

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how the fundamental information of how to create a table.

622 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