InDB Write to MSSQL table with Identity column, error
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Error Message
- In Database
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Has this issue been resovled?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- « Previous
-
- 1
- 2
- Next »