InDB Write to MSSQL table with Identity column, error

[david_fetters]

Hey friends,

I just started using the InDB tools because they allow me to store the DB connection details as a file (Which enables other users to use these workflows without having to fix the DB connection and save a new copy).  Was going great until I tried to insert new records into a table using the InDB Write tool.

The table is a dimension table with IDENTITY set for its primary key (an auto-incrementing number).  With the normal output tool, when you write to a table that has an Identity column you can just leave that column out of your data and it will insert successfully.

With the InDB tools, it gives me an error even when I don't include the Identity field.  Specifically the error is: “Error running PreSQL on “NoTable”:Microsoft SQL Server Native Client 11.0: An explicit value for the identity column in table ‘dbo.Dim_Groups’ can only be specified when a column list is used and IDENTITY_INSERT is ON.\23000 = 8101.”

This other forum post discussed the issue but has a solution that does not work for this case, and indicates that this issue may still be unresolved after two years.  See: https://community.alteryx.com/t5/Data-Sources/MS-SQL-Identity-Column-using-In-Database-Tools/td-p/78...

Any help would be much appreciated

[Alex_yang]

Has this issue been resovled?

[DaveF]

@Alex_yang 

This is still in the backlog - DE14163 if you need to reference it with Support. There was a workaround posted in another thread you could try:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/MS-SQL-Identity-Column-using-In-Databa...

[troyfurnace]

For the regular (non-inDB) version, an error occurs as well, when you try to send inserts without the identity column (as it should work).

I find that if I include this in the Pre-SQL, it works:

SET NOCOUNT ON