Sign in
 
Home20132010Other VersionsLibraryForumsGallery
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
TechCenter >
> 
> 
Access to MS SQL transfer Issue

Answered Access to MS SQL transfer Issue

  • Thursday, July 12, 2012 3:59 PM
     
     
    Sign In to Vote
    0

    Hi,

    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)
    VALUES (CONVERT(BINARY,'\0\0'))

    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?

    Thanks.

     

All Replies

  • Friday, July 13, 2012 6:43 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    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.

    HTH!

    Jaynet Zhang

    TechNet Community Support

     
  • Friday, July 13, 2012 8:15 AM
     
     
    Sign In to Vote
    0

    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
     
     Answered
    Sign In to Vote
    0

    Hi Again,

    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

    Thanks.

    • Marked As Answer by Michal Cermak Friday, November 23, 2012 3:41 PM
    •