Using SSMS to export SQL Server table to Excel. Getting Error 0x002f210

Question

Hello,

I've been trying to export data from an SQL server to and excel file. The SQL table isn't complex at all, just names, date of birth, sections, etc. The date of birth field is important because, I have many entries where the date of birth (date of birth field is datetime2(7) datatype) is before 1900. I've tried two different ways.

Using Excel and 'Get Data' option. I connect to my SQL Server, select the db and then table. Data transfer goes through just fine, however any date of birth before 1900 is showing up as negative values. I've tried formatting the column to text, then pulling data from the sql db (this didn't work). I've tried using the Date() function (when I do this, the year of birth is way too big. I.E I have someone born on 11/14/1864. Using the =Date(1864,11,14) + cell, I'm getting a value of 11/14/3729. I've also tried formatting using custom field (mm/dd/yyyy) this didn't work either.

My second approach was to try exporting my table using SSMS to my excel file. Going through the import/export wizard I am getting an error:

TITLE: SQL Server Import and Export Wizard

Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE cem (

OBJECTID ..." failed with the following error: "The Microsoft Access database engine could not find the object 'cem'. Make sure the object exists and that you spell its name and the path name correctly. If 'cem' is not a local object, check your network connection or contact the server administrator.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am using a sql server native client 11.0 as my data source. Using an excel as my destination (version 2016), copy data from one or more tables or views. When I am at 'Review Data Type Mapping' section of import/export wizard, I notice there is a conflict (exclamation point on 3 of my columns, all dealing with dates). I'm seeing source type for Date of Birth being datetime2 datatype and the destination source being varchar for the excel file. When I go back a step and look at the column mappings, I'm seeing Date of Birth data type set as varchar. It also looks like another table is being created?

I am not a DBA, so I'm not sure what else I can do to try and fix these issues. Is there something I do in the edit column mappings to change the datetime2 datatype to varchar?

Answer 1

I think Get Data is a lot better option than the Export Wizard. I tried both to test your case, and the Export Wizard is really painful! The error you get suggests that you are doing something wrong, which I also did, before I was able to get the export to start. And then it failed on a date in the 18th century.

I'm an SQL Server person, and not an Excel guy, but my conclusion is that Excel's lower boundary for dates is 1899-12-30.

You can still load the data with Get Data, but you need to change your query, so that you have something like: convert(char(10), DOB, 121). That is, you need to export it as string. 121 will give you the format YYYY-MM-DD. If you want a different format, check the topic for CAST and CONVERT for the available options.