Error when running custom Member Directory

BillG49 · Post by **BillG49** » Mon Oct 04, 2004 5:32 pm

We are getting ready to rerun our in-house Member Directory, last run in 2002. When we try to run the report now, we get the error message "SQL Select statement that is being executed by Query Maker has an error in it".

I notice that there are multiple iterations of the record filter and output fields. If I go in and delete all of the duplicates, I can run the report - ONE TIME. the next time I try to run it, all the duplicate fields are back!

This problem exists on the main church computer as well as my personal computer, where I have the backup files. We are running v8.5. Was there someting in the upgrades since 2002 that could have caused this problem? If so, is it fixable? Or do I just need to start recreating this monster report?

Thanks in advance for any assistance.[/img]

Zoe · Post by **Zoe** » Tue Oct 05, 2004 11:07 am

You may want to try creating a copy of the custom report and try running the copy. Also, try deleting the entire filter, and recreating the filter.

BillG49 · Post by **BillG49** » Tue Oct 05, 2004 12:09 pm

I have done what you suggest. The copy has the same problem. And the filter may or may not be the problem. It does not show duplicate entries.

I would be happy to send you a screen shot that might illustrate the problem a little better. Or is there a way to post a jpg file on the forum?

Thanks for the reply.

Zoe · Post by **Zoe** » Wed Oct 06, 2004 11:17 am

I have seen that error message before, and usually one of the two suggestions will fix it. What is the filter you are using?

BillG49 · Post by **BillG49** » Wed Oct 06, 2004 11:42 am

I guess I can't post or send a screen shot?

Record Filter:
Mailing Category exactly matches 1 Member or
Mailing Category exactly matches 3 Friend of Congregation and
Publish Address is true

Post by **Zaphod** » Thu Oct 07, 2004 9:49 am

to post a screenshot, you'd have to have webspace of your own, upload the file there, and then include the link like such:

Code: Select all

[img]http://www.domain.com/path/to/image.jpg[/img]

It would then show up inline in your post.

I think there are services on the web that you can use specifically for this purpose like photobucket or the like. This place looks cool: http://www.imageshack.us/

BillG49 · Post by **BillG49** » Thu Oct 07, 2004 9:57 am

Thanks for that website - I'll have to bookmark it.

Here's the error message. It changes a little each time it's run.

Post by **Zaphod** » Thu Oct 07, 2004 10:13 am

The only thing I see as a potential issue is you need brackets around your OR condition.

(Mailing Category exactly matches 1 Member OR
Mailing Category exactly matches 3 Friend of Congregation) AND
Publish Address is true

This can be changed in the edit record filter screen.

Post by **Jeff** » Thu Oct 07, 2004 10:26 am

Their are some internal numbers that keep track of separate reports. It looks like those internal numbers have gotten off so you have more than one report using the same key. You will need to contact support so we can reset those numbers and then fix those reports. Worse case, you might have to send in a Custom Reports backup so we can fix it.

The technical aspects: The qm_id file has gotten off so it is assigning a query_id that is already in use. This is causing the duplicate fields and also doing weird things when you try to delete the duplicates.

BillG49 · Post by **BillG49** » Thu Oct 07, 2004 11:17 am

Jeff,

Thanks for the clue. I am running a backup copy of the software on my personal computer, and I happen to have some old backups on my usb drive. I restored a backup from August, and the report runs fine!

The problem appears to have started when we made a copy of the "Directory October 2002" report and named it "Directory October 2004".

Do you think if I delete the copy, the original report will run? I can't try it here at the office (since I just overwrote the problem by restoring an old backup), but I will stop by the church at lunch time and give it a shot. I can always just modify the 2002 report - I was just trying to be careful by making a copy to modify and leaving the original.

edit - I just recreated the problem by making a copy of the report. I got an error saying the report could not be created, then when I clicked on the OK button, PowerChurch shut down. When I reopened PC, both reports are there, but they both have the original error message (see screen shot above) when I try to "Run Query". I deleted the copy, but the original report appears to have had all the formatting deleted as well, so that doesn't work.

I could delete both reports and restore the original Custom Report from an old backup, but how do I identify it?

Thanks!

Post by **Jeff** » Thu Oct 07, 2004 2:00 pm

It will be best if you call in to support because there is going to be many steps to solving this.

The problem is the qm_id table has somehow got back to an old value. We need to figure out what the highest value should be and manually update the table with the correct value. To do this type the following command from the database manager:

SELECT MAX(RIGHT(f_query_id,7)) FROM qm_list <Enter>

Make a note of the number returned.

The type the following commands.
Use QM_Id <enter>
Browse <Enter>

The qm_id table should only have one record. Update the Id_Value field to a number that is greater than the highest number returned from the select statement. Also make sure the f_query_id field ends with this same number.

I think this should solve the problem. Do try this on a backup before doing this on a production system. Normally we just have people send in their data so we can verify this is what the problem is and make sure that this does fix the problem.

BillG49 · Post by **BillG49** » Thu Oct 07, 2004 3:36 pm

Jeff,

"SELECT MAX(RIGHT(f_query_id,7)) FROM qm_list <Enter>
Make a note of the number returned"

0000012

"The qm_id table should only have one record. Update the Id_Value field to a number that is greater than the highest number returned from the select statement. Also make sure the f_query_id field ends with this same number."

was: 0000002, 0003045, ME0000003
changed to:0000014, n/c, ME0000014

(I think those are the correct number of "0's")

At any rate, I can now copy the report and modify everything. Will this work on a computer that has 2 corrupted reports, and fix them both (the situation at church)?

Post by **Jeff** » Thu Oct 07, 2004 3:48 pm

Yes, you should be able to do the same kind of fix at the church. Make sure you make a backup before you start doing the changes just in case something bad happens.

BillG49 · Post by **BillG49** » Fri Oct 08, 2004 11:12 am

Jeff,

I just tried the fix at church, and it didn't work.

There are two corrupted reports, and neither will work. When I reset the values in the QM Id record to "14" and then try to run either report, I get the same error message. When I then go back and look at the table, the ID_Value is still at "14", but the F_query_id has been reset to "03".

Can we not just delete both of the reports, fix the table, and restore a good version of the report from an old backup?

Thanks for your help.

Post by **Jeff** » Fri Oct 08, 2004 2:42 pm

I think that will work. I would export the reports from your home system where they are working. To export choose export custom reports from the utilities menu. Delete the reports at the church and double check the qm_id file to make sure the pointers are okay. You will need to rerun the select statement at the church because the highest number may be different than the dataset you have at home. Then import the reports from home.

Without being able to look at the data, I am not really sure why the original procedure did not work.

User Support Forum

Error when running custom Member Directory

Error when running custom Member Directory

OK - that was easy

Bingo!

It works!