August 23, 2019 at 1:43 pm
Hello All,
Iam receiving the below error while exporting data from a database(xyz table) to XL file using Import Export Wizard
TITLE: SQL Server Import and Export Wizard
------------------------------
Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE XYZ (
SYS_KEY Decim..." failed with the following error: "Too many fields defined.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
------------------------------
BUTTONS:
OK
------------------------------
August 24, 2019 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 4, 2022 at 11:00 am
Nothing has changed and its August 2022. Still getting Error 0xc002f210...."Too many fields defined." In my case the table only has only 272 columns and 440 rows.
A sql table with more than 255 columns will not export using MSSQL export wizard to Excel 2016 format. It seems like SSMS 15.0.18x has not caught up yet as the O365 2022 version of Excel handles >255 fields just fine.
In my case the solution was to divide into 2 views, and keep the ID column in the second view just in case of misalignment. Also CSV format is not possible as many of my text fields contain commas.
My Workaround
During the wizard export the SQL Datetime2(7) field was problematic and yielded a result like '2022-04-24 11:08:00.0000000' as it was converted to varchar(29). To resolve this issue before it starts change SQL Datetime2(7) to just DateTime and the final exported result in excel will be "2022-04-17 07:43:01" as it should.
During the export wizard "Review Data Type Mappings" if you instruct it to 'ignore' failed mappings (On Error/On Truncation) and the transfer proceeded fine. During the export wizard "Review Data Type Mappings" if you use the default 'fail' failed mappings (On Error/On Truncation) the process will fail unless SQL Datetime2(7) fields have not been mapped to DateTime in the "Edit Mappings". There is also a risk of truncation which should not be taken for granted letting the wizard 'ignore' failed mappings .
To merge the files in excel it is then simply a matter of copy and paste. All rows should be aligned. Use "Format | Auto Fit Column Width" to check for transfer oddities/errors just in case. They are not hard to spot especially when number and text fields are corrupted. Luckily my data transferred fine using the described method.
September 4, 2022 at 4:30 pm
In my case the solution was to divide into 2 views, and keep the ID column in the second view just in case of misalignment. Also CSV format is not possible as many of my text fields contain commas.
My Workaround
During the wizard export the SQL Datetime2(7) field was problematic and yielded a result like '2022-04-24 11:08:00.0000000' as it was converted to varchar(29). To resolve this issue before it starts change SQL Datetime2(7) to just DateTime and the final exported result in excel will be "2022-04-17 07:43:01" as it should.
FYI - datetime data types include milliseconds which cause issues with Excel displaying the value. The data is correct - but it defaults to a time display instead of date and time. To fix that you could convert to DATETIME2(0) which returns a datetime value without any milliseconds.
CSV format is absolutely possible - all you need to do is make sure you quote the columns that can contain the delimiter using QUOTENAME(column, CHAR(34)). Generally the problem here is embedded carriage returns and/or line feeds - but those can be handled by replacing those characters.
It seems you are exporting from a table directly - instead of doing that, use a query as the source. By using a query you have to ability to cast/convert/replace to update the column data so it exports successfully into the desired format, as well as filtering out any non-relevant data and excluding non-relevant columns.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 22, 2022 at 10:32 pm
Hello Jeffrey
Thankyou for the clarification. I was using TSQL Views as that was my method of reducing the footprint of each exported dataset. The export of the target views worked for the first exports but then failed on my most recent export of data from different tables.
The new error arose with the transferring of data to the O365 2022 files using the Excel 2016 format. The automated mapping of Long Text by the SSIS wizard failed to export field lengths of 512 characters and the 'row transfers' were failing.
To resolve this issue I simply changed the output file type to the old 97-2003 XLS format. The process then worked without error.
September 24, 2022 at 7:52 am
This was removed by the editor as SPAM
October 6, 2022 at 7:23 am
Open the file named in the Source field which appears in the error message transcript. Click on the sheet named in the DataSourceName field in the error message transcript. Check that there are no more than 255 columns in use in this sheet
October 9, 2022 at 8:15 pm
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply