Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

InDB Write to MSSQL table with Identity column, error

david_fetters
11 - Bolide

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

11 REPLIES 11
paul_houghton
12 - Quasar

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
11 - Bolide

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
8 - Asteroid

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

david_fetters
11 - Bolide

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
11 - Bolide

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
8 - Asteroid

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

djoyus1
8 - Asteroid

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

AndrewHall209
5 - Atom

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
7 - Meteor

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

Labels