Possible bug with data import to SQL Azure?

Toad for SQL Server
1

Hi all,

I’m in the process of evaluating Toad for use in our company. One of the things we need to be able to do is import data into SQL Azure from delimited text files.

I am getting an error that I can’t figure out. In our case, we are trying to save into a new table, where the primary key is already represented in one of the columns of the incoming data. I set this column as the primary key in my configuration.

When the template goes to execute, I get an error message. It seems like it’s trying to execute the command “SET IDENTITY_INSERT dbo.XYZ ON”. However, since the table has no identity column, SQL Azure rejects it. Or something like that.

I also tried in “normal” SQL Server database and didn’t have this issue. So, I’m thinking this might be a bug in Toad (i.e. when targeting SQL Azure, it shouldn’t send this command for a table without an identity column)? Or am I doing something wrong?

Event log:

12:25:46 PM Thread (20) Prepare the connection : SET DATEFORMAT YMD ; SET NOCOUNT OFF
12:25:51 PM Thread (20) Identity Insert dbo.XYZ generated an error
Please check your file format settings
Table ‘dbo.XYZ’ does not have the identity property. Cannot perform SET operation.
12:26:01 PM Thread (20) Error importing data, please check file format options: Identity Insert dbo.XYZ generated an error
Please check your file format settings
Table ‘dbo.XYZ’ does not have the identity property. Cannot perform SET operation.

Thanks in advance for any ideas!
-James

2

Hi James,
can you please attach your Import template as well as table ddl and some peace of sample data?

3

Hi Alexander,

Thanks for your quick reply. I’ve attached the data and template. I am using the option to create a new table, so the table is generated by Toad itself; no DDL.

When creating a new table, it seems like you should have the option to add an identity column, or to set a primary key on a different column – in which case it shouldn’t be sending any such commands to the database.

Thanks!
-James
testTemplate.tim (1.2 KB)

4

Hi Alexander,

Thanks for your quick reply. I’ve attached the data and template. I am using the option to create a new table, so the table is generated by Toad itself; no DDL.

When creating a new table, it seems like you should have the option to add an identity column, or to set a primary key on a different column – in which case it shouldn’t be sending any such commands to the database.

Thanks!
-James
XYZ.txt (86 Bytes)

5

James,
thanks for the materials. I also reproduced this on my side. The problem still exist even if I tried to import to existing table with PK defined.
It worked previously and was broken some time ago. I’ve created CR108806 to address this issue.

Thanks for your input.