Thursday, July 12, 2012 3:59 PM
I have a issue with upsizing MS Access (tried in 2007 and 2010) using Upsizing wizard in MS Access. I have for example Binary(256) type column in Access and it is always converted to binary(1) column in MS SQL server (2008 R2). When i click on Columns Properties on SQL Management Studio it has Length == 1 in General page and Size == 256 in Extended Properties.
When i tried this query in Management Studio
INSERT INTO TestTable (BinaryColumn)
it throws error:
String or binary data would be truncated.
The statement has been terminated.
So, it looks like actual column binary length is only 1 byte and shall have 256.
Does anybody has suggestions how to deal with it?
Friday, July 13, 2012 6:43 AMModerator
Based on my research of this error message, and most of the cases return this error are that the length of the value insert into the table is larger than the the length of the definition in the table.
So just check the data type of BinaryColumn in the TestTable.
TechNet Community Support
Friday, July 13, 2012 8:15 AM
I actually know, that column has size 1 byte. And that is my point. In Access, before conversion to MSSQL it has size 256 bytes. But in MSSQL, after conversion, it has Length = 1. There is another attribute Size in MSSQL management studio, which contains correct value - Size == 256, but it looks like Length value shows actual (and wrong) size of column in MSSQL.
In the other words, I think there is issue in conversion from Access to MS SQL using Upsizing wizard.
Friday, November 23, 2012 3:40 PM
At the end I find and successfully used Microsoft SQL Server Migration Assistant for Access which work great. There is link for download: http://www.microsoft.com/en-us/download/details.aspx?id=28763
- Marked As Answer by Michal Cermak Friday, November 23, 2012 3:41 PM