Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
458 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

722 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