none
I get error String or binary data would be truncated when export data from sql server 2012 to excel 2010 RRS feed

  • Question

  • problem

    I get error String or binary data would be truncated when export data from sql server 2012 to excel 2010

    when export data from sql server 2012 to excel i get error 

    string or binary data would be truncated 

    so How to solve this problem please ?

    Insert Into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=D:\BasicInfo\output\BasicInfo.xlsx;HDR=YES','select * FROM [Sheet2$]')
    Select
    CompanyID ,
    CompanyName,
    DisplayName,
    PartDisplayType  ,
    PartDisplayCompanyName ,
    MainWebSite, 	
    WebsiteStatusName  ,
    CompanyTypeName ,
    From #Temp;

    How to solve this error please ?

    Sunday, November 17, 2019 7:13 PM

Answers

  • thank you for reply

    OK but how to solve this problem

    Due to that cells in excel also have specific data types, you have to do the mapping between SQL Server table and excel.

    If you could compare the operation between yours and the one in my previous reply, I think you could figure out which situation should be considered.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 21, 2019 5:51 AM
    Moderator

All replies

  • Hi engahmedbarbary,

    Thanks for posting here.

    We rarely use OPENROWSET to insert local data to remote data source. It is usually used to get data from remote data source instead.

    For such situation, we could use SQL Server Import and Export Wizard to export data to Excel. Please refer:

    Import and Export Data with the SQL Server Import and Export Wizard

    Get started with this simple example of the Import and Export Wizard

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 18, 2019 1:53 AM
    Moderator
  • thank you for reply

    OK but how to solve this problem

    Monday, November 18, 2019 6:20 AM
  • thank you for reply

    OK but how to solve this problem

    It might be that some SQL column has lengthy data (>255 chars) and excel is considering the data type has 255 by default. 

    You can set TypeGuessRows to 0 at appropriate path in regedit (reference)

    or have some dummy lengthy data in excel which will be overwritten. 


    If the response helped, do "Mark as answer" or upvote it
    - Vaibhav

    Monday, November 18, 2019 6:40 AM
  • thank you for reply

    OK but how to solve this problem

    Due to that cells in excel also have specific data types, you have to do the mapping between SQL Server table and excel.

    If you could compare the operation between yours and the one in my previous reply, I think you could figure out which situation should be considered.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 21, 2019 5:51 AM
    Moderator