topic Re: Inserting into SQL (Auto Identity Columns / Auto Date colums) in SAS Data Management

Inserting into SQL (Auto Identity Columns / Auto Date colums)

mrtball — Mon, 27 Jan 2014 14:46:53 GMT

r SQL Hello,

I'm getting a similar problem to (https://communities.sas.com/thread/12940) attempting to use a table loader in SAS DI 4.4 (9.3) to populate a SQL table with an identity / auto generate ID column.

Initially i was getting the error: Line 650: ERROR: During insert: Data value violated the schema for column Vehicle_ID Data was not set for one or more columns.

However after following the update in the link to include ignore_read_only_columns=yes in the libname - I managed to do this in the advanced library properties > other options within SAS MC 9.3.

After doing this the error changed to the below:

Table Loader

Line
1,328: ERROR: An exception has been encountered. Please contact

technical support and provide them with the following traceback

information: The SAS task name is [APPEND (2)]

Line
1,328: ERROR: Read Access Violation APPEND (2) Exception occurred at

(05B2C17F) Task Traceback Address Frame (DBGHELP API Version 4.0 rev 5)

0000000005B2C17F 0000000009EABC90 sasioole:tkvercn1+0x1B13F

0000000005B802D9 0000000009EABC98 sasxdbi:tkvercn1+0xF299 0000000006C74203

0000000009EABD48 sasyoio:tkvercn1+0x31C3 00000000096A6870 0000000009EAC098

sasqutil:tkvercn1+0x45830 000000000966D65D 0000000009EAF7D0

sasqutil:tkvercn1+0xC61D 000000000967EEC1 0000000009EAFAC0

sasqutil:tkvercn1+0x1DE81 00000000036C059F 0000000009EAFAC8

sasxshel:tkvercn1+0x4F55F 00000000096B2EA8 0000000009EAFBE0

sasqutil:tkvercn1+0x51E68 00000000096614D5 0000000009EAFCB0

sasqutil:tkvercn1+0x495 0000000002128AF7 0000000009EAFCB8

sashost:Main+0xF427 000000000212D12D 0000000009EAFF50 sashost:Main+0x13A5D

0000000076FE652D 0000000009EAFF58 kernel32:BaseThreadInitThunk+0xD

000000007711C541 0000000009EAFF88 ntdll:RtlUserThreadStart+0x21

Line
1,328: ERROR: ROLLBACK issued due to errors for data set

NAMSUM.Vehicle.DATA.

The ID column in question is unmapped.
I also tried to user your code as hand-written code within the table loader (proc append base=target.my_target(drop=SQL_ID) etc..) - this didn't work, and seems to then pass the error onto the next table column (even columns which are not auto generate identity columns)
I've so far struggled to find detail on the pre/post script sequencing steps

Just wondering if you have any ideas of how to correct this?,

Thanks,

Re: Inserting into SQL (Auto Identity Columns / Auto Date colums)

Diane_SAS — Tue, 28 Jan 2014 20:10:43 GMT

Hello! Have you submitted this question to SAS Technical Support? You can copy and paste what you posted into the form available here: Technical Support Form

Re: Inserting into SQL (Auto Identity Columns / Auto Date colums)

LinusH — Tue, 28 Jan 2014 21:44:54 GMT

Posting the part of the log that tries to insert data would help.

I can recall that we had a similar problem. Maybe I remeber wrong, but I think we had to unregister the columns in question from SAS metadata to make it work.

Re: Inserting into SQL (Auto Identity Columns / Auto Date colums)

mrtball — Wed, 29 Jan 2014 09:30:36 GMT

Thank you both for your responses.

Diane - Yes i logged a SAS technical support track yesterday for this.

Linus - I've just looked into deleting the column in metadata with my current setup, and although this hasn't corrected the issue, having deleted the ID column from the metadata within SAS MC, changed the table loader style to replace entire table (as per the section Maintaining Metadata - 'Perform Additional Operations on Column Metadata' in the SAS(R) Data Integration Studio 4.2: User's Guide) - the error has changed to 'ERROR: During insert: Data was not set for one or more columns.'

This i feel is a little more promising and i'm going to continue to look into this error today.

Thanks,
Tom

Re: Inserting into SQL (Auto Identity Columns / Auto Date colums)

mrtball — Wed, 29 Jan 2014 13:41:28 GMT

It looks like this is now working through a combination of revisiting how the SQL table was setup (removing columns that do not allow nulls and not including other auto date columns). The ID column is auto populating and the Date_Inserted date is also auto-populating in the SQL table.

It is also working without removing the ID column from metadata (the column remains in metadata but is not mapped in the table loader)

Using these load settings:

And also including REREAD_EXPOSURE=yes in the libname (as prompted when moving from the 'load technique' tab to the 'mappings' tab in DI):

Thanks,
Tom

Re: Inserting into SQL (Auto Identity Columns / Auto Date colums)

Diane_SAS — Wed, 29 Jan 2014 14:52:08 GMT

That is fantastic! Thanks for posting your resolution!

Re: Inserting into SQL (Auto Identity Columns / Auto Date colums)

mrtball — Thu, 30 Jan 2014 17:00:46 GMT

Also to add to the resolution of this

- I've just identified that within the Library settings - > options > advanced options > output

Setting the 'Whether to use DBMS's bulk load' option to Yes displays this error again.

Many Thanks,

Tom

Re: Inserting into SQL (Auto Identity Columns / Auto Date colums)

anna_holland — Thu, 30 Jan 2014 20:13:11 GMT

mrtball , I'm glad you found the solution to your problem! Thank you for marking the answer as "correct." This allows other community members to benefit from reading your solution.

Thanks,

-Anna-Marie