error while exporting data from sql server to excel

Question

Hi,
I am trying to export data from sql server to excel.

The query that I used is s follows
INSERT INTO OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Folder1\abc.xls','select * from [Sheet1$A1:IV65000]')
select 
column1,column2.... from Table1

I get the following error message while doing so..
String or binary data would be truncated.
The statement has been terminated.

One of the column's holds data more than 225 characters.
What is the way to correct this, i want the whole data of the colum in the excel sheet.

Answer 1

You can split your string in SQL using left, mid, rigth and left you can use concatenate in your spreadsheet. It's the easier way. 

Hope this post helped you

Answer 2

It's probably better to either have Excel to connect to run the query, or have a separate program to talk both to SQL Server and Excel. What is happening now is that SQL Server asks the Jet engine about the data types. But Excel has, in difference to SQL Server, a dynamic idea of what is the data type. So it reports the current values. So it could be that because the current longest value in a column is 50 chars, Excel reports 50 chars, and that's when things starts to go wrong.

Look at the Import/Export wizard.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Erland, I guess in this case the problem is the maximun lenght (excel side). It's common to separate (sql side) and concatenate (excel side) the string. 

Answer 4

Hi Umesh,

May be below links could be helpful to you:

http://support.microsoft.com/kb/189897

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=2

Regards

Avijit

Answer 5

I haven't had great luck with the INSERT INTO OPENROWSET method.  Can you try one of the many samples from one of these 3 links?

http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Excel%20Data%20Export%20to%20SQL%20Server%20Test%20Code 

http://www.mssqltips.com/sqlservertip/1540/insert-update-or-delete-data-in-sql-server-from-excel/

https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc--/

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.