Help with "ERROR: Read Access Violation In Task [ APPEND (6) ]"

[Majkl]

Hello all,

I am quite new to SAS(started about 1 year ago).I've been only at 1regular SAS training and the rest is somehow "selfstudy".

I was able to understand basic working of things in SAS,also read manuals and some docs on web.BUt that was just to explain,why I cant figure out my problem.

I will try to describe it best way I can.

4 months ago Ive create Library(via 9.2 Management Console) that was OLEDB.It was conected to other server where is running SQL server 2008.

Ive marked that library as pre-assigned.In Properties-Options tab - Advanced Options -Other Options I used this: IGNORE_READ_ONLY_COLUMNS=YES INSERT_SQL=YES DBCOMMIT=0

Then Ive created Export Definition with Output option-Table and linked to the library created before.After some problems with formating and so I made it work.

Till monday this week it seemed to be working perfectly.But then suddenly I wasnt able to inser anything in the table on the other server.

The error I got when I found it isnt working was this: "ERROR: Data source is read only; no updates allowed."

So I decided to create new library(to test it out of anything happend with library).Also,just for sure,created new server.And new export definition.

After that I was still getting error.But it was diferent error: "ERROR:  Read Access Violation In Task [ APPEND (6) ]".

And after 2 days of testing,trying magic with Libname options and so I found out that I am able to insert into that table on the other server only 1 record.

And I dont know why is that possible(before that happend I was able to insert as many records as I wanted).

I should Add that nothing happend with user login on SQL server so the rights for the user are same as before.Only thing I found out is,that SQL server was updated.But as I said nothing happend to login and everything is same as before.

Does anybody know what could be the problem?

Im becoming pretty desperate because this was main way to transfer data directly to that table and I am not that good at SAS Base programing yet so Im not able to do process for it.

Thank you for any comments.

[art297]

I don't know if the following is relevant, but it might be worth looking at: http://support.sas.com/kb/44/180.html

Other than that, I can only think of asking some questions.  e.g.: what changed?  Did you change your password on one of the systems?  Was your SAS version upgraded?  Did anything change on the SQL server side (including things like version, your authorizations, or the file structure itself)?

[Majkl]

Hello,

thanks for answer.I was checking that link before and I think it doesnt have conection to my problem.

I didnt change anything,except Ive set SAS servers to allowe XCMD.I have not changed anything in library definition or in server conetion.

Sas version was not updated,password or anything related to user login was not changed on any of the systems.

On SQL side there was only thing that I know what changed and it was Microsoft Update that included update of SQL.

[ballardw]

I wouldn't be surprised to find that since the SQL server was updated some permissions behave differently or some policy for your login behaves differently with the new software. I ran into oddnes with Access connecting to and MS SQL database that made the fields behave differently. It's probably time to talk with the DB manager about things.

 should Add that nothing happend with user login on SQL server so the rights for the user are same as before.Only thing I found out is,that SQL server was updated.But as I said nothing happend to login and everything is same as before.

[Majkl]

Hi,

yep I think I will have to talk about it with them.

I may also try create whole new login on both sides and try if it has same error.

[Majkl]

Hello again...well...after I wrote replays...I went to SAS CI Studio just try again if anything happend and guess what?

I was able to insert 2 records today.And I have no clue why...nothing happend since yesterday when I wrote this thread

Im realy confused.... why yesterday 1 record with new lib  definition and today 2? I dont wanna add 1 record per each day

[Majkl]

HI again,

so I made a new discoveries.

I created new library with new server conection.

thats the statemant:

LIBNAME dwh OLEDB  IGNORE_READ_ONLY_COLUMNS=YES DBCOMMIT=10000 INSERT_SQL=YES AUTOCOMMIT=YES  PROPERTIES=('Initial Catalog'=DB_CRM)  OLEDB_SERVICES=NO  PROMPT=NO  PROVIDER=SQLOLEDB.1  DATASOURCE="xx.xx.xx.xx"  SCHEMA=dbo  USER=SAS_INT

PASSWORD="xxxx" ;

Then I went to CI studio,created whole new campaigne and I discovered one thing. I was able to insert "some" records.As far as I found out it was some number under 500. I was able to inser 50,100 and also 200 records.But when I rised number to 500 it failed with old error:

ERROR:  Read Access Violation In Task [ APPEND (3) ]

Exception occurred at (0587E522)

Task Traceback

And what I think: it seems to me that the statemant ignores dbcommit setting. Or I have some "instalation" settings that allows me add only some exact number of records to table via OLEDB conection.

Anyone got any idea where could I check those suspections?

Thanks

[Majkl]

Hi all,

so Ive solved problem.

When you have NOT NULL constrains on table you want to fill and trying to fill it with null values it result at that error.

Pity SAS can not tell you what error exactly it is,only that it is Read Access Violation.

And also be carefull when you use Trigers on that table.Similar error is returned when trigger can not run propertly.

With Regards

M.