[TDP 3.8 & 4.0] Data Import Bug

Toad Data Point
1

Greetings,

It appears that there is a bug with the Data Import Wizard in both TDP 3.8 and TDP 4.0.

Source: Excel 2013 File (.xlsx)

Destination: SQL Server 2008 R2

When attempting to import a file containing a date of ‘0001-01-01’ into a table where the destination column is of the datatype DATETIME2(7), TDP gives the following error message:

“Row 1 failed to insert, error: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.”

It appears that TDP is assuming that the DATETIME2(7) datatype is actually just DATETIME - which does only support a date going back to 1753-01-01.

However, we specifically use DATETIME2(7) on all our date columns, and if I manually insert the row, there is no issue - so it’s definitely a problem with the Import Wizard.

I’ve reproduced the issue on both TDP 3.8 and TDP 4.0.

2

Hello,

I’m not able to reproduce the error in Import Wizard, but I got similar error when I tried to insert such value via data grid (r-click on table -> View Details -> Data). So definitely this is a bug.

But could you please provide me more information to reproduce the problem in Import Wizard? First, I expect you are trying to import the data to already existing table, aren’t you? Can you provide me also structure of the table, for example the create table statement?

You should be also able to see the concrete insert statements in execution trace (Tools -> Execution Trace -> To Output; View -> Output).

Thank you.

Libor

3

Greetings,

Yes, the table already exists and this was a refresh of the data contained in it - normally I’d use the Import Data task within SSMS, but I happen to be running Excel 2013 64-bit and there are known issues with that.

I’ve previously used TDP 3.8 and 4.0 to load data into other tables on this same exact server and database without issue, but those did not have DATETIME2(7) columns.

Below is the DDL to recreate the table layout:

CREATE TABLE [TDP_DATETIME2](

[COMPANY] [int] NOT NULL,

[CODE] varchar NOT NULL,

[STARTDATE] datetime2 NOT NULL,

[ENDDATE] datetime2 NOT NULL,

[GROUPNAME] varchar NOT NULL,

[CATEGORYID] varchar NULL,

[GROUPID] [int] NULL,

CONSTRAINT [PK_TDP_DATETIME2] PRIMARY KEY CLUSTERED

(

[COMPANY] ASC,

[CODE] ASC,

[STARTDATE] ASC,

[ENDDATE] ASC,

[GROUPNAME] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

4

Hello,

I was finally able to reproduce the problem. It seems that the error appears only when you enable “If error occurs, save unimported rows to a discard file (csv)” at the end page of the Import Wizard.

I’ve created TSS-1364 for this bug and TSS-1363 for the similar problem on the table data grid.

Thank you.

Libor

5

That actually matches up with exactly what I was doing - I did have that option enabled.

Just a very odd issue, I ended up having to write a VBA macro to insert the 100k+ rows.