Wednesday, September 28, 2011 4:16 PMHi,
every time i run following query in SQL Server
insert into test.dbo.Nenmeet SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Documents and Settings\nenmeet.singh\Desktop\PD_data.xlsx;
HDR=yes','Select * from [Collated$]')
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server '(null)' returned truncated data for column '[Microsoft.ACE.OLEDB.12.0].IPC - Current'. The actual data length is 600 and truncated data length is 510
- Edited by Nenmeet Wednesday, September 28, 2011 4:22 PM
Wednesday, September 28, 2011 5:35 PMModerator
Please, could you give more informations about your problem ?
- where are you executing your query ? in SQL Server Management Studio, in an application , in a SSIS package?
- version (2000,2005,2008,2008 R2 ) , edition (MSDE,Express,Standard,Entreprise...) of your SQL Server
- version of your Excel ( i suppose 2007 )
As i have problems with the english language ( it is not my mother tongue ), i am not sure to have understood your length problem.Please, could you tell us what is the length of your column in the Excel file and the length of the related column in your SQL Server table ( for SQL Server , for example nvarchar(480) or varchar(70) or nchar(650) ). what are the values of 600 or 510 ? in the Excel column or in the SQL Server table ?
We are waiting for your feedback to try to help you more efficiently
Have a nice day
Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
Thursday, September 29, 2011 5:20 PM
1. where are you executing your query ?
SQL Server Management Studio
2. version (2000,2005,2008,2008 R2) ?
2008 R2 Express
3. version of your Excel ?
4. length in excel cell ?
between 600 to 10,000
5. column in SQL Server table ?
all the columns are VARCHAR(MAX)
Friday, September 30, 2011 1:00 AMAnswerer
Are you using English data in Excel spreadsheet? Have you tried to use nvarchar(max)?
WDAC Team, Microsoft.
Pak-Ming Cheung - MSFT
Saturday, October 01, 2011 10:31 AM
1. Are you using English data?
2. Have you tried to use nvarchar(max)?
yes, but giving same errors
Saturday, October 01, 2011 12:05 PM
See the last paragaraph in this article - http://www.databasejournal.com/features/mssql/article.php/3875421/SSIS-2008-Import-and-Export-Wizard-and-Excel-based-Data.htm.
If it is supposed to be a scheduled task to import data into a sql server table I would create a SSIS package and configure column length there.
Friday, March 30, 2012 1:16 PM
I know this is an old post, but I just found a great solution, and wanted to share:
Follow the instructions, step by step. It works perfect!!!
Tuesday, April 17, 2012 12:57 PM
hi the below lnk may help you out.
- Proposed As Answer by Papy NormandModerator Monday, April 23, 2012 1:10 PM