Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
465 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
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

926 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