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

[paul_houghton]

Hi @david_fetters,

does this happen when the column is included but left as [NULL]?

My guess is that it requires a primary key table to identify if the record exists, and if it is entered as null the auto-increment IDENTITY would update the [NULL] field?

[david_fetters]

Thanks Paul,

I do get the same error if I include the properly typed Identity column with null values.  I forgot to mention it, but this is Alteryx 11.7, connecting to MS SQL 2016 with the SQL Server 11 driver.

[djoyus1]

I've encountered the same with Alteryx 10.3 through 11.6.  Is there no workaround?

[david_fetters]

No workaround found.  I've put in a request to support for this and will update here when I hear back from them!

[david_fetters · Accepted solution]

Just for anyone following this:

Support has it on their list.  It is a bug and they are aware of it.  No definitive target date for a fix, but it's in the hopper.

[djoyus1]

@AlexT Flagging this per our post-In-DB session talk at Inspire last week!

[djoyus1]

Oops, I meant to flag @ARich! Alex - Per our conversation at Inspire after your In-DB training session.

[AndrewHall209]

Wondering if I could get an update on this bug? Has it been resolved? I am currently on 2018.2, and I am having the same issue with an indb connection to AWS Redshift

[riosjosh]

Wondering if there has been any movement or if there is a work around for this since last year.