March 12, 2019 at 3:27 pm
Error: ERROR MESSAGE: String or binary data would be truncated.
if value in sql table is <= 255 the INSERT to excel from sql works just fine
How / where to remove limitation?
--insert records into $Sheet1 of Template
SET @SqlCmd =
'INSERT OPENDATASOURCE
( ''Microsoft.ACE.OLEDB.12.0'',''Data Source=' + @ServerShare + '\' + @ExportFileName + ';Extended Properties=Excel 12.0'')...[Sheet1$]
SELECT
UPS_Account_Number,
UPS_Tracking_Number,
UPS_Pick_Up_Date,
Original_Invoice_Number,
Purchase_Order_Number,
materialdescription_ups,
Merchandise_Quantity,
Package_Weight,
LBS_or_KGS,
Replacement_Cost,
REPLACE(Shipping_Charges,''n/a'','''') AS Shipping_Charges,
Total_Claim,
Consignee_Contact_Name,
Consignee_Contact_Number,
Customer_contacted,
Replacement_been_shipped,
Replacement_UPS_Tracking_Number
FROM
BusOps_Transportation.stg.tracer_ups_extract_ct
WHERE claim_type in (''CCNR'',''LATE'') and len(materialdescription_ups)<256';
EXECUTE sp_executesql @SqlCmd;
--Quote me
March 13, 2019 at 7:25 am
polkadot - Tuesday, March 12, 2019 3:27 PMError: ERROR MESSAGE: String or binary data would be truncated.
if value in sql table is <= 255 the INSERT to excel from sql works just fine
How / where to remove limitation?
--insert records into $Sheet1 of Template
SET @SqlCmd =
'INSERT OPENDATASOURCE
( ''Microsoft.ACE.OLEDB.12.0'',''Data Source=' + @ServerShare + '\' + @ExportFileName + ';Extended Properties=Excel 12.0'')...[Sheet1$]
SELECT
UPS_Account_Number,
UPS_Tracking_Number,
UPS_Pick_Up_Date,
Original_Invoice_Number,
Purchase_Order_Number,
materialdescription_ups,
Merchandise_Quantity,
Package_Weight,
LBS_or_KGS,
Replacement_Cost,
REPLACE(Shipping_Charges,''n/a'','''') AS Shipping_Charges,
Total_Claim,
Consignee_Contact_Name,
Consignee_Contact_Number,
Customer_contacted,
Replacement_been_shipped,
Replacement_UPS_Tracking_Number
FROM
BusOps_Transportation.stg.tracer_ups_extract_ct
WHERE claim_type in (''CCNR'',''LATE'') and len(materialdescription_ups)<256';EXECUTE sp_executesql @SqlCmd;
It's just not physically possible to populate a column that way with more than 255 characters. Not as a table-based INSERT, anyway. You would need procedural code to do that, manipulating the Excel Object Model. VBA would be a candidate.
Alternatively, guarantee that the column with the problem has no more than 255 characters in it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 13, 2019 at 10:24 am
Trim it within the SELECT statement:
--insert records into $Sheet1 of Template
SET @SqlCmd =
'INSERT OPENDATASOURCE
( ''Microsoft.ACE.OLEDB.12.0'',''Data Source=' + @ServerShare + '\' + @ExportFileName + ';Extended Properties=Excel 12.0'')...[Sheet1$]
SELECT
UPS_Account_Number,
UPS_Tracking_Number,
UPS_Pick_Up_Date,
Original_Invoice_Number,
Purchase_Order_Number,
LEFT(materialdescription_ups, 255) AS materialdescription_ups,
Merchandise_Quantity,
Package_Weight,
LBS_or_KGS,
Replacement_Cost,
REPLACE(Shipping_Charges,''n/a'','''') AS Shipping_Charges,
Total_Claim,
LEFT(Consignee_Contact_Name, 255) AS Consignee_Contact_Name,
Consignee_Contact_Number,
Customer_contacted,
Replacement_been_shipped,
Replacement_UPS_Tracking_Number
FROM
BusOps_Transportation.stg.tracer_ups_extract_ct
WHERE claim_type in (''CCNR'',''LATE'')';
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 13, 2019 at 10:46 am
In this case TRIM won't be acceptable. I have also tried ORDER BY len(materialdescription_ups) DESC, to make sure that Excel knows to set the size of the column to >255 in the first 10 rows.
Seems crazy to have all this functionality halted merely due to size of field.
How does SSRS get around this issue when export to Excel option is used? This Export to Excel is to replace the SSRS report, and SSRS has no issue exporting to excel > 255 char length.
Googled: Before you can successfully save strings longer than 255 characters to an Excel column, the driver must recognize the data type of the destination column as memo and not string.
Googled: The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR). Integration Services maps the Excel data types as follows:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms137643(v=sql.100)
Or could answer be to increase the TypeGuessRow in the registery, as given here? https://stackoverflow.com/questions/24865183/data-truncated-after-255-bytes-while-using-microsoft-ace-oledb-12-0-provider
--Quote me
March 13, 2019 at 3:34 pm
polkadot - Wednesday, March 13, 2019 10:46 AMIn this case TRIM won't be acceptable. I have also tried ORDER BY len(materialdescription_ups) DESC, to make sure that Excel knows to set the size of the column to >255 in the first 10 rows.Seems crazy to have all this functionality halted merely due to size of field.
How does SSRS get around this issue when export to Excel option is used? This Export to Excel is to replace the SSRS report, and SSRS has no issue exporting to excel > 255 char length.
Googled: Before you can successfully save strings longer than 255 characters to an Excel column, the driver must recognize the data type of the destination column as memo and not string.
Googled: The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR). Integration Services maps the Excel data types as follows:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms137643(v=sql.100)
Or could answer be to increase the TypeGuessRow in the registery, as given here? https://stackoverflow.com/questions/24865183/data-truncated-after-255-bytes-while-using-microsoft-ace-oledb-12-0-provider
I didn't mean use a "TRIM" function, I meant use LEFT / SUBSTRING, as I showed in the code. By "trim" I meant "remove excessive bytes".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 13, 2019 at 3:47 pm
this is one of the issues with using the ACE driver - and many more issues can be found if you processing same file layout with changing contents.
In this particular case try and cast the offending field (materialdescription_ups) as varchar(max) which is what corresponds to the Memo data type.
May or not work.
March 13, 2019 at 6:51 pm
cast (materialdescription_ups as varchar(max)) didn't alleviate. Also tried changing General to TEXT datatype in excel column.
is there another provider other than "ACE" that will work??
any experience with increase the TypeGuessRow in the registery, as given here? https://stackoverflow.com/questions/24865183/data-truncated-after-255-bytes-while-using-microsoft-ace-oledb-12-0-provider
I have to keep it within SQL because I don't know another programming language.
--Quote me
March 13, 2019 at 8:40 pm
polkadot - Wednesday, March 13, 2019 6:51 PMcast (materialdescription_ups as varchar(max)) didn't alleviate. Also tried changing General to TEXT datatype in excel column.is there another provider other than "ACE" that will work??
any experience with increase the TypeGuessRow in the registery, as given here? https://stackoverflow.com/questions/24865183/data-truncated-after-255-bytes-while-using-microsoft-ace-oledb-12-0-provider
I have to keep it within SQL because I don't know another programming language.
with all examples out there with SSIS + C# you should be able to pick it up and do at least the basic stuff - and you would bypass most of the errors that arise from using Excel files.
As far as I remember the typeguessrow only affects reading. But you can try having the template with 1 of the first 8 rows populated with more than 256 chars to see if it works
As for other provides - CozyRock and possibly others may work - but its paid software which may not be an option for you... and they are SSIS components.
Providers that you can use from SQL I'm not aware of any other than this one and Jet (which has the same issues)
March 13, 2019 at 11:03 pm
I am fine with SSIS since this is just another application I have already experience using (just don't have a licence for it at my current job place). So SSIS can handle this insert to Excel (and overcome the 255 char limit)?
--Quote me
March 15, 2019 at 12:48 am
it's not working in SSIS. I have posted in Integration Services forum. Same issue with 255 limitation.
--Quote me
March 15, 2019 at 2:42 am
polkadot - Friday, March 15, 2019 12:48 AMit's not working in SSIS. I have posted in Integration Services forum. Same issue with 255 limitation.
it is not the tool but the driver - I was very clear on that aspect.
you either use one of the available tools from third parties (like CozyRoc) or you do it in a C# script using OpenXml
March 15, 2019 at 7:09 am
polkadot - Friday, March 15, 2019 12:48 AMit's not working in SSIS. I have posted in Integration Services forum. Same issue with 255 limitation.
As I mentioned to begin with, the standard SQL toolset isn't going to be able to do this. You are going to have to do this in a procedural language with the ability to manipulate the Excel Object Model. As Excel itself is expertly capable of this, you could place the VBA code needed into the spreadsheet itself, and it can run a query against SQL Server and then populate the spreadsheet row by row, and within a row, column by column. I'm just not aware of any way around the 255 character limit with the standard tools. Although I'm not sure I'd blame Microsoft for this. Using a spreadsheet to display that much data in one column is unquestionably in the bad idea category 99.9% of the time. Web app is one thing, Excel, a whole different kettle of fish. Just because a tool is familiar to users, doesn't mean using it instead of a better alternative is a good idea. The number of times I've seen that kind of justification boggles the mind.... I'm often reminded by such occurrences of the doubters of Charles Babbage, who asked "Pray, Mr. Babbage, should the wrong figures be entered into the machine, would the right answers still come out?" His reply reflects my perspective 100% - "I cannot understand the confusion of ideas that would lead to such a question."
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 15, 2019 at 11:16 am
i don't understand why it is bad. only you're saying 'Using a spreadsheet to display that much data in one column is unquestionably in the bad idea category 99.9% of the time'
it's been done that way for years where I am working, (SSRS exports to Excel > 255 char limit) and customer uses, and it is not problem. Why do you think it is 'bad'??
--Quote me
March 15, 2019 at 3:13 pm
polkadot - Wednesday, March 13, 2019 6:51 PMcast (materialdescription_ups as varchar(max)) didn't alleviate. Also tried changing General to TEXT datatype in excel column.is there another provider other than "ACE" that will work??
any experience with increase the TypeGuessRow in the registery, as given here? https://stackoverflow.com/questions/24865183/data-truncated-after-255-bytes-while-using-microsoft-ace-oledb-12-0-provider
I have to keep it within SQL because I don't know another programming language.
I've had success with it before. Read through the following as you can make changes when using the ACE instead of the Jet:
Setting TypeGuessRows for excel ACE Driver
Sue
March 15, 2019 at 3:19 pm
Sue_H - Friday, March 15, 2019 3:13 PMpolkadot - Wednesday, March 13, 2019 6:51 PMcast (materialdescription_ups as varchar(max)) didn't alleviate. Also tried changing General to TEXT datatype in excel column.is there another provider other than "ACE" that will work??
any experience with increase the TypeGuessRow in the registery, as given here? https://stackoverflow.com/questions/24865183/data-truncated-after-255-bytes-while-using-microsoft-ace-oledb-12-0-provider
I have to keep it within SQL because I don't know another programming language.I've had success with it before. Read through the following as you can make changes when using the ACE instead of the Jet:
Setting TypeGuessRows for excel ACE DriverSue
Sue.. the OP is using the ACE driver - but not reading but writing to Excel where this setting may not have any effect.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply