Dear everyone.
I am building ETL jobs for a project along with my seniors with SAS Data Integration Studio. Recently, i have updated the entire MS SQL database with new primary key. Before i edit the database, all tables have a column "ID" which is identity column which also remains until now.
Here is the problem, all my Loading jobs are using table loader for Update/Insert. It has always been working fine until the recent MS SQL database change. Now, I am getting this error :
ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert explicit value for identity column in table 'ACTIVITY_HIGHLIGHT' when IDENTITY_INSERT is set to OFF.
Here is the screenshot of the table loader mapping:
ID is not mapped as it is set to identity column in MS SQL.
I have tried to switch the option of SQL Pass thru to on and off and im still getting this same error.
Do take note that I do not map to the ID column in any way.
I do understand that sometimes my information posted is not sufficient enough for your understanding. Hence, I appreciate if you could ask me for more particular info that you need to further analyze.
I would like to thanks everyone here for reading my post and hopefully this problem will be resolved soon.
I think that there have been a couple of threads with similar issues.
As I recall it I have solved this by setting the option IGNORE_READ_ONLY_COLUMNS=YES (works both as a libname and as a data set option).
I think that there have been a couple of threads with similar issues.
As I recall it I have solved this by setting the option IGNORE_READ_ONLY_COLUMNS=YES (works both as a libname and as a data set option).
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.