Table in use

[spuppett]

I have this rather large conversion process that process over 200,000 records. Everytime I run it I get an error saying that a table is in use. This hasn't happened on the same record twice, but it does happen at least once every time I run it. Does anyone know what would cause this sort of anomaly to crop up?

If you need any more info let me know and I'll post it.

Thanks

 

[mkrausnick]

There could be several causes for this. For example, using SQL Select to create a cursor leaves the table(s) named in the FROM clause open.

If you're getting the error on a USE statement, change the USE syntax to this:

USE mytable IN SELECT('mytable')

If the table is already open, it will be re-opened in the same area without generating an error.

If it's not a USE statement that is failing, maybe post the relevent code, including the line that generates the error.

Mike Krausnick
Dublin, California

 

[spuppett]

Here's the code where it bombs:

if isBlank(myJobClass)
   m.job_class_id = null
else
   SELECT job_cat FROM ..\eclasses WHERE alltrim(upper(myJobClass)) = alltrim(upper(classdesc)) INTO  CURSOR job_category [b]&&dies on this line occasionaly[/b]
  m.job_class_id = val(job_cat)  
endif
if not isBlank(jobDesc1)
		m.people_job_title_id = createGUID()
		m.date_updated = date()
		SELECT category FROM ..\m2_job_titles WHERE alltrim(upper(jobDesc1)) = alltrim(upper(job_titles)) INTO CURSOR job_title
		myCategory = job_title.category
		SELECT title FROM ..\int_job_titles WHERE seq_num = myCategory INTO CURSOR real_job_title
		myJobTitle = real_job_title.title
		IF NOT ISBLANK(myJobTitle)
			SELECT job_title_id FROM ..\convert!companies_job_titles WHERE alltrim(upper(myJobTitle)) = alltrim(upper(job_title)) INTO CURSOR cur_title_id  [b]&&dies on this line occasionaly[/b]
			m.job_title_id = cur_title_id.job_title_id
			INSERT INTO ..\convert!people_job_titles FROM MEMVAR	
		ELSE
			SELECT job_title_id FROM ..\convert!companies_job_titles WHERE alltrim(upper(myJobTitle)) = "OTHER" INTO CURSOR cur_title_id
			m.job_title_id = cur_title_id.job_title_id
			INSERT INTO ..\convert!people_job_titles FROM MEMVAR	
		ENDIF
endif

As you can see, both are selects, so I don't think open tables matter. Aslo it happens so infrequently that even if it was an issue, why does it only do it some of the time. I don't get it.

 

[foxdev]

Are you getting error 3 (file is in use) or error 108 (file is in use by another)? If it is the latter, that is another issue.

--------------
art prints

http://www.artlot.com

 

[lordhawkins]

Wouldn't be better to select the records using the job_title_id field?
I assume that the id's are in a table with less than 1000 records, then write the job title and getting its id shouldn't be a hassle. Then you can use the "id" like a better selection field for the query in the bigger table.

In my experience, SQL is less accurate with alltrim()