Search

varbinary(MAX) on linked tables by DDalie

Active

1
0
Sign in
 to vote
Type: Bug
ID: 125236
Opened: 3/24/2006 10:11:24 AM
Access Restriction: Public
1
Workaround(s)
0
User(s) can reproduce this bug
I am contractor doing programming work for the BC Government in Canada. We have started to move from Oracle to SQL 2005 in some of our newest applications. So, our SQL 2005 server software is pretty much new... no more than 2 months old.

We are migrating an application from Access 2003 to VB.Net... however that will occur in phases... on this phase we're making modifications on an access 2003 application so we can move its data to SQL 2005 SQL Server - the application will stay in Access 2003 for a short time though... Here's the problem...

We were initially saving image files (.bmp) using an ADODB.Stream into a linked table field (showing the field as of binary type (510 of size) although on SQL 2005 was an IMAGE data type), and that was just working fine...

However, after finding warnings in different articles of MSDN...
--- e.g.: ---------
Important:
The image data type will be removed in a future version of Microsoft SQL Server. Avoid using this data type in new development work, and plan to modify applications that currently use them. Use the varbinary(max) data type instead.
---
... We decided to apply the recommended change and turned the field into an varbinary(MAX) on SQL 2005. On the linked tables it still shows as a binary data type with 510 of size... however we cannot save the image file any more now that we have done such a change. We are getting the following error information:
---
Error 3163 - The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
---
Note that we are using an ODBC connection with an SQL Native Client Driver just for the sake of security reasons as we work with Windows Authentication, Active directory and all... The file sizes are in the range of 10 to 20 KB.
Details (expand)
Product Language
English
Version
SQL Server 2005 - Standard Edition (32)
Category
SQL Engine
Operating System
Windows Server 2003
Operating System Language
US English
Steps to Reproduce
'On an VBA Accesss Module try to reproduce the following code

'Creating the Stream Object to Read files
Set stm = New ADODB.Stream
stm.Type = adTypeBinary
stm.Open
Dim strDirPath as String: strDirPath = "c:\directoryName\"
Dim strFileName as String: strFileName = "fileName.bmp" 'files are in range of 10-20kb in size
stm.LoadFromFile strDirPath & strFileName

strTrgSQL = "SELECT * FROM tblImageInfo;"
Set rsTrgRS = CurrentDb.OpenRecordset(strTrgSQL)

With rsTrgRS
    'We insert a new image
    .AddNew
    ![ImageBinInfo] = stm.Read 'Inserting the BinaryCode from the image
    ![File_Name] = strFileName
    .Update
End With

rsTrgRS.Close()
rsTrgRS = Nothing
Actual Results
Error 3163 - The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
Please, email me directly to if you have a solution to the problem or should we rollback and use an Image data type????
Expected Results
to save the info in the recommended field
Platform
 
File Attachments
0 attachments
Sign in to post a comment.
Posted by Microsoft on 3/28/2006 at 10:52 AM
We do not have sufficient information to make progress with this issue. When we have more complete information per our previous request we will look at this again.
Posted by Microsoft on 3/24/2006 at 3:00 PM
Please provide futher details of exactly how you are establishing a connection to SQL Server 2005. Note that MSDASQL is not supported with SQL Native Client. See the topic 'Updating an Application to SQL Native Client from MDAC' in Books Online, which states 'SQL Native Client is not supported from deprecated MDAC components, for example, the Microsoft OLE DB provider for ODBC (MSDASQL). ' This means that with ADO you can only use OLE DB to establish a connection to SQL Server 2005 using SQL Native Client.

The topic 'Using ADO with SQL Native Client' explains that when connecting to SQL Server 2005 via OLE DB it is necessary to specify 'DataTypeCompatibility=80' in the connection string.

Could you check if using the SQL Native Client OLE DB provider with 'DataTypeCompatibility=80' in the connection string resolves your problem?

If not, you should be able to resolve the problem by using the MDAC ODBC driver or OLE DB provider. Downlevel clients will see varbinary(max) columns in a manner compatible will image columns.

If you wish you may email me direct, my address is chrlee@microsoft.com

Chris Lee, Program Manager for SQL Native Client
Posted by DDalie on 3/24/2006 at 10:14 AM
We got a our new SQL 2005 Server (99% of our DBs are Oracle)...

We are migrating an application from Access 2003 to VB.Net... however that will occur in phases... on this phase we're making modifications on an access 2003 application so we can move its data to SQL 2005 SQL Server - the application will stay in Access 2003 for a short time though... Here's the problem...

We were initially saving image files (.bmp) using an ADODB.Stream into a linked table field (showing the field as of binary type (510 of size) although on SQL 2005 was an IMAGE data type), and that was just working fine...

However, after finding warnings in different articles of MSDN...
--- e.g.: ---------
Important:
The image data type will be removed in a future version of Microsoft SQL Server. Avoid using this data type in new development work, and plan to modify applications that currently use them. Use the varbinary(max) data type instead.
---
... We decided to apply the recommended change and turned the field into an varbinary(MAX) on SQL 2005. On the linked tables it still shows as a binary data type with 510 of size... however we cannot save the image file any more now that we have done such a change. We are getting the following error information:
---
Error 3163 - The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
---
Note that we are using an ODBC connection with an SQL Native Client Driver just for the sake of security reasons as we work with Windows Authentication, Active directory and all... The file sizes are in the range of 10 to 20 KB.
Sign in to post a workaround.
Posted by Željko Matić on 11/8/2010 at 9:38 AM
I had this problem too.
If you link table with varbinary(max) from Access 2007 using ODBC SQL server native client 10 it will link field correctly as OLE object but in this case you will be able to store data not bigger than 4000 characters in size. But if have data bigger than 4000 from before you will be able to read then without problem. The for writing data bigger then 4000 char will be 3124 - Odbc-data error , ODBC recordset - String data, right truncation
Till finding better solution I'm saving file data in packages in set of records with size less then 4000 and I'm recreting files form this package records one needed.
This is workaround till better solution find.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement