ERROR: Read Access Violation In Task (SQL)
in [SAS]
|
Prev: advice about SAS coding level displayed on the video
Next: Windows search facility does not find a word or phrase in .SAS
From: Tom Abernathy on 5 Mar 2010 09:45 This looks to be an ODBC issue. Can you access this data using ODBC from applications other than SAS? Take SQL out of the loop and just access the data with a DATA step. data Synopsis_TransHist ; set a1.FHPR_PR_TransHistory ; run; I doubt it will help, but it might be that SQL is too smart and is trying to push functionality like the ORDER BY operation into the database. On Mar 4, 12:00 pm, dgcarr...(a)MAGELLANHEALTH.COM (David Carroll) wrote: > Last week we started getting an error with a SAS program which had been > working fine for many months. The code of that program is unchanged as is > the format of the database table it is reading. The only thing that has > changed (that I'm aware of) is that the number of rows in that table > continues to grow (it is now about 66,000.) > I am including a bit of the SAS log when we got this error. Does anyone > know what might explain this error? Would there be some option which > might avoid it? As a work-around, instead of the PROC SQL which pulls the > entire table (and now gets the error), I used two PROC SQLs each with a > subsetting WHERE to pull about half the records each time. (These two > PROC SQLs run fine - no errors.) I then use a data step to put the two > pieces together. > Thanks in advance for any help, > David Carroll > > Here's the log snippet: > > 683 /* set up libname to link to prod synopsis with odbc */ > 684 > 685 libname a1 odbc DBMAX_TEXT=6000 dsn=remedy_prod uid=XXXXXX > pwd=XXXXXXX USE_ODBC_CL=YES; > > NOTE: Libref A1 was successfully assigned as follows: > Engine: ODBC > Physical Name: remedy_prod > 686 > 687 /* set up file hook and write work dataset out to permanent SAS > dataset */ > 688 > 689 libname synop "\\Client\C$\"; > NOTE: Libref SYNOP was successfully assigned as follows: > Engine: V9 > Physical Name: \\Client\C$ > 690 > 691 /* Set up work dataset from Synopsis Trans. History table */ > 692 > 693 proc sql; > 694 create table Synopsis_TransHist as > 695 (SELECT * > 696 FROM a1.FHPR_PR_TransHistory a > 697 ) > 698 order by a.Review_ID; > ERROR: Read Access Violation In Task [ SQL ) > Exception occurred at (067733B4) > Task Traceback > Address Frame (DBGHELP API Version 4.0 rev 5) > 067733B4 06A1E134 arodbc45:SQLGetFunctions+0x396A > 0678837D 06A1E1E0 arodbc45:SQLGetData+0x149F > 06786E76 06A1E240 arodbc45:SQLFetch+0xAAC > 06483FF2 0000012C ODBC32:SQLFetch+0x15F
From: Michael Raithel on 5 Mar 2010 12:39
Dear SAS-L-ers, David Carroll posted, in part, the following: <<David's entire original posting can be found beneath the Sig line>> > Last week we started getting an error with a SAS program which had been > working fine for many months. The code of that program is unchanged as > is > the format of the database table it is reading. The only thing that > has > changed (that I'm aware of) is that the number of rows in that table > continues to grow (it is now about 66,000.) > I am including a bit of the SAS log when we got this error. Does > anyone > know what might explain this error? Would there be some option which > might avoid it? As a work-around, instead of the PROC SQL which pulls > the > entire table (and now gets the error), I used two PROC SQLs each with a > subsetting WHERE to pull about half the records each time. (These two > PROC SQLs run fine - no errors.) I then use a data step to put the two > pieces together. David, I think that the root of your problem is likely the size of your output data set in relation to the size of the media you are attempting to write it to. Said in a less obtuse way: I bet that you are running out of space when extracting the entire table. The message snippet that you posted is consistent with messages I have experienced in such situations, as is the success of your workaround. So, what to do? What to do? Well, you have a valid, though likely clumsy, workaround that you could stick with. Or, maybe you could make sure that the directory you are writing to has more than enough space to accept your large output file. Yea, I would try the latter, and see if this irksome error message fades into the dust of time. David, best of luck in all your SAS endeavors! I hope that this suggestion proves helpful now, and in the future! Of course, all of these opinions and insights are my own, and do not reflect those of my organization or my associates. All SAS code and/or methodologies specified in this posting are for illustrative purposes only and no warranty is stated or implied as to their accuracy or applicability. People deciding to use information in this posting do so at their own risk. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Michael A. Raithel "The man who wrote the book on performance" E-mail: MichaelRaithel(a)westat.com Author: Tuning SAS Applications in the MVS Environment Author: Tuning SAS Applications in the OS/390 and z/OS Environments, Second Edition http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=58172 Author: The Complete Guide to SAS Indexes http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=60409 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ I can stand brute force, but brute reason is quite unbearable. There is something unfair about its use. It is hitting below the intellect. - Oscar Wilde +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ <<David's entire original posting>> > Last week we started getting an error with a SAS program which had been > working fine for many months. The code of that program is unchanged as > is > the format of the database table it is reading. The only thing that > has > changed (that I'm aware of) is that the number of rows in that table > continues to grow (it is now about 66,000.) > I am including a bit of the SAS log when we got this error. Does > anyone > know what might explain this error? Would there be some option which > might avoid it? As a work-around, instead of the PROC SQL which pulls > the > entire table (and now gets the error), I used two PROC SQLs each with a > subsetting WHERE to pull about half the records each time. (These two > PROC SQLs run fine - no errors.) I then use a data step to put the two > pieces together. > Thanks in advance for any help, > David Carroll > > Here's the log snippet: > > 683 /* set up libname to link to prod synopsis with odbc */ > 684 > 685 libname a1 odbc DBMAX_TEXT=6000 dsn=remedy_prod uid=XXXXXX > pwd=XXXXXXX USE_ODBC_CL=YES; > > NOTE: Libref A1 was successfully assigned as follows: > Engine: ODBC > Physical Name: remedy_prod > 686 > 687 /* set up file hook and write work dataset out to permanent SAS > dataset */ > 688 > 689 libname synop "\\Client\C$\"; > NOTE: Libref SYNOP was successfully assigned as follows: > Engine: V9 > Physical Name: \\Client\C$ > 690 > 691 /* Set up work dataset from Synopsis Trans. History table */ > 692 > 693 proc sql; > 694 create table Synopsis_TransHist as > 695 (SELECT * > 696 FROM a1.FHPR_PR_TransHistory a > 697 ) > 698 order by a.Review_ID; > ERROR: Read Access Violation In Task [ SQL ) > Exception occurred at (067733B4) > Task Traceback > Address Frame (DBGHELP API Version 4.0 rev 5) > 067733B4 06A1E134 arodbc45:SQLGetFunctions+0x396A > 0678837D 06A1E1E0 arodbc45:SQLGetData+0x149F > 06786E76 06A1E240 arodbc45:SQLFetch+0xAAC > 06483FF2 0000012C ODBC32:SQLFetch+0x15F |