VFP 7 and Windows 7/8 compatibility issues...

[rlawrence]

Sorry if this is redundant. I discovered that I posted to the wrong forum for this version of Foxpro.

-------------------

O.K. You can tell me I've been living in denial, but I now have a significant problem that I'm hoping some folks here can shed some light on. I've read here and other places that VFP9 is the only version compatible with Windows 7 and 8. I've been looking for ways to get VFP9. There seem to be very few outlets, and Microsoft options are only through MSDN subscriptions--which cost thousand$!

So, I'm wondering if anyone understands the nature of those compatibility issues and whether they can be mitigated.

My application is in VFP 7. I have many users successfully running on Windows 7 & 8. I have run into a few problems that seem to be resolved by setting the compatibility mode to "Windows XP Service Pack 2". I have been around the horn on the "OpLocks" issue and am pretty sure that problem has been nailed. (My own summary of the situation: [URL unfurl="true"]http://www.pubassist.com/articles/OpportunisticLocking.asp[/url].) But something seems to have changed over the last few months and a new crop of problems seems to be coming up in networked environments where traffic is high.

The latest problem that has prompted this posting is that a user is complaining of getting bumped out of the application when they attempt to perform a complex operation and others are connected to the database.

Is there anyone else here that is running VFP 7 on a Windows 7/8 network?

 

[rlawrence]

Here's more information in response to some questions.

______________________________

Hi Dave,

Thanks for your comments. Actually, I don't have a lot of specifics, because I can't reproduce the problems. It may be helpful to know that this is NOT a client-server application. I'm using the multi-user capabilities of Foxpro to access a database on the network from multiple client machines. Right now, I enforce a limit of 6 simultaneous clients. However, when I first introduced this multi-user version, I ran some tests with 10 old machines, pounding the database with data as fast as the keyboard buffer would process. I don't know if the application could pass that test today. It's also worth mentioning that I am well aware of locking issues, and have gone to great pains over the years to optimize locks--generally on records rather than tables. We're not dealing with a Foxpro locking problem here.

Now, there is one problem I can reproduce. It's a report with a very complex SQL SELECT command. The result set is only a few records, but it pulls data from a large database in a complicated way. I'm not looking for help with SQL, but it may be helpful to know that running on Windows 7, without compatibility mode, the query fails with a "syntax error". This does not happen when running on earlier versions of Windows--including Vista. AND when I set the compatibility mode to "Windows XP Service Pack 2", the same report with the same database runs just fine.

The only other detail I have is that when entering customer payments, a whole lot of stuff happens behind the scenes. Lots of potential financial transactions and potentially inventory transactions too. In order to post these transactions, contracts, and account history must be searched as well. For a couple of users who have Windows 7 networks, when several users access the database various failure occur. One user is kicked out of the application when attempting this transaction. Another user finds their index files getting clobbered.

Both of these users have implemented the OpLocks fix I mentioned in the article above. By the way, turning off SMB2 (as recommended in the article) made a WHOLE bunch of problems go away for these same users. I feel like something new in Windows 7 is throttling access to the database.

What I am looking for here are any support stories of problems encountered and solutions that pertain to this scenario. I'm hoping this will provide me (and others) with new ideas for troubleshooting these kinds of issues. I also hope it prolongs the life of VFP applications everywhere!

Any new thoughts?

 

[Olaf Doschke]

First of all VFP98 has the same problems with Opportunistic Locking an Read Ahead Caching as VFP7. The benefits of VFP9 are a bug fixed with Aero style windows, which are a thing of the past with Windows 8. There are some other minor issues and yes, overall VFP9 has the most fixed errors as it's the last version and it has lots of stuff I wouldn't want to miss on comparison with VFP7. In regard to buying it, Pavel Celba in many occasions pointed to the volume licensing program (MOLP), where you need to buy 5 licenses, but not 5 licenses of the same product, eg bundle vfp9 with 4 times the cheapest product (there would be something for 10$), and you still get away cheaper than with a subscription. It's a bit late though, to now look for VFP9, that option may be dropped, if you can't purchase it separate anymore.

In regard of recent new problems with old software tested in time: I never encountered a syntax error, that wasn't a syntax error. If macro substitution is used, it can easily happen if some input is used and not sanitized. This can happen after years if any new user gets a new idea never before tested. And even in other scenarios things can happen, where they never occured, with no involvement of OS changes. I just have to look back a few hours. Today I got a screenshot of an error in MS Word automation happening in a line using the Paste() method to paste RTF I put into the clipbox right beforehand. This provenly has worked for hundreds if not thousands of times and the only reason of it failing is the seldom coincidence something else on the clipbox interfered.

And on the other hand, currently I work a lot with Excel files on Win8 and Excel 2013 and there is a copy&paste issue, too. If I am in between copy and paste, so a range is selected in one excel sheet and I open another Excel file, the file doesn't open up until I click somewhere so no range is selected. Strikes me as an odd clipboard issue. I can't repro this on Win7 and Excel 2010.

Bye, Olaf.

 

[rlawrence]

Hi Olaf,

I have to meet you some day. You have responded to so many of my posts, I feel like we are old friends.

I totally agree with your assessment of the "Syntax Error". In this case, however, I run exactly the same report with exactly the same selection criteria. The SQL command is assembled and submitted to ExecuteSQL() function. The application runs on Windows 7 accessing a database across the network. In the case where the application runs without the use of compatibility mode, I get the syntax error. When I set the compatibility mode and run the same report with the same conditions and selection criteria, I do NOT get the "Syntax Error". Running in compatibility mode fixed the problem for my user as well.

Yes, I know it is late in the game. I have been installing and testing on Windows 7 and 8 and everything I have tested seems to work fine. But some of my users make heavy use of the application in a network environment. That's when most of these problems show up. I don't want to get into a commentary on Microsoft, but suffice it to say that I'm not happy about their handling of Foxpro. I'm cheap to begin with, so investing in a dead product at any price is not something I look forward to.

 

[Olaf Doschke]

As you assemble some SQL, why not store it in some log file or perhaps into a public variable before executing it, and in case of a syntax error (10, 1242 or 2093?) your error handler could copy that SQL into your error log.

In regard to meeting one day: Why not, we might both profit from it.
I don't know how well known it is I live in Germany, Hamburg. As a Lawrence, are you living in Arabia?

Bye, Olaf.

 

[DSummZZZ]

When you say compatibility mode, are you running Windows 64 bit instead of 32? Is there any sort of macro substitution or expansion in your queries? Are there any references to directories?
All those could be mistranslated by the OS as virtual (or even library) paths.
Maybe if you were to redo some of the SQL stuff or the data access stuff to simplify it or trim it down and make it less complex, maybe use several sub-queries or even some SCAN...ENDSCAN scenarios, you would have less processor time and beat on the hard drive less. Windows 7 boxes and OSs just aren't that robust when it comes to file sharing and other 'server' activities. The are file may be somewhat good for file sharing, but that is a whole bunch different than file serving.


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[rlawrence]

One option I haven't had a chance to investigate thoroughly myself is the use of a virtual machine. I know that has been mentioned in other threads on this forum. I made the suggestion to one of my users. Here's the response I just received:

Dear Ron,

The virtual xp machine allows me to work well and speedily; no crashes, purging, reindexing so far. The only downside is that I have to exit it to do other things, since it captures my keyboard, but I can live with doing all my pubassist work at one time. Obviously, the problem was with WIindows 7.

Thanks a bunch.

Well, there's one out. Would still like to better understand what is going on.

 

[rlawrence]

No Olaf, I've never been to the Middle East. I do have relatives in Germany though.

Actually, I have the ability to log the SQL Query. I really don't believe the query itself is the issue since it works everywhere else.

Dave, You ask some interesting questions. I am pretty well convinced that the issue is in the file system. The Windows 7 machine and O/S are 64 bit, while my Foxpro application is obviously 32-bit. "File sharing" vs. "file serving" is interesting to think about, but my head starts to spin when I think about a Foxpro database--for which Foxpro was designed to share simultaneously. Now put those files somewhere on the other side of a network connection... I can see the potential for problems.

On the other hand, this is what operating systems do. And clearly, something has changed--literally within the last few months--that goes beyond the Op Locks problem. Are either of you sharing a VFP database across the network?

 

[DSummZZZ]

Yeah, there is no 64 bit version of VFP. But why I was asking about 64 vs 32 bit is they have different virtual paths, directories, whatever you want to call them. And if you're referencing a path or directory in a query (or in a database for that matter), it could be in a different physical area. For instance "C:\" takes on a whole different meaning in W7 than it did in XP.

Anyway no, I am not sharing VFP databases on a W7 network. I know it can be done, just not reliably. All of our apps are multi-user, but all of the databases, tables, indexes etc., are on servers not workstations.
Server OSs are built differently than workstations. They are designed to have many concurrent connections accessing reading and writing files. Their caching is different, their task switching or time slicing is different, even the backplane is usually different/faster. So yes, this is what operating systems do, and Foxpro was designed to share the files, but is your network hardware and software able to handle the load?

Another thought I have is a little test. If all your data resides on a single workstation, and you run multiple instances of your app on that same workstation without running them in compatibility mode, do they all work?


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[Olaf Doschke]

The bitness of applications and OS doesn't matter, this only matters in DLLs and drivers you may use or not use together, but the file system is still an array of 8bit segments (AKA bytes).
Yes, I still maintain an older (2006) app with DBF/DBC. DBFs are hosted on a Win2003 server instead of setting up the registry that was the choice of that customer to avoid SMB2, anyway we just had the OpLocks problem, but no such syntax problem with the same query.

Would you dare to show the query itself?
And would you please not only visually compare, but really do a diff on the to queries, even if they look the same? BitXOR the strings as in Bitxor(0h+"Hello",0h+"HeIIo")

Bye, Olaf.

 

[Dan Freeman]

running VFP 7 on a Windows 7/8 network?

I've said it before and I'll say it again. Windows 7 and Windows 8 ARE NOT FILE SERVER OPERATING SYSTEMS. If you are using them to build a "network" without a file server you are doing peer-to-peer networking which has, historically, been the least effective way to run a Foxpro application going back to Foxpro DOS.

I currently have applications running in VFP 6,7, and 9 on Windows from XP through 8. They work fine. Of course the files ARE ON FILE SERVERS which, as Dave correctly points out, were built to manage multi-user contention issues.

 

[Mike Gagnon]

Dave Summer

Yeah, there is no 64 bit version of VFP

And here I was using the 64 bit version of Foxpro for 2 years now.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[RobSpencer]

Just to be totally clear here... You're running Windows 7/8 on the client machines where you run your application but what OS is the machine running that stores the database?

I know this is out of left field but how much memory does your client machine have? I found a bug in VFP where it would set the foreground buffers to greater than the memory available to VFP. I now run the following code in my dev environment and at the start of all my programs to detect and limit the settings if they are too big. It only gave us problems when we were running big SQL queries (which may sound familiar - I can't remember what error messages we used to get though).

**********************************************
procedure checkMem ()
**********************************************
* ------------ Limit Memory Usage ------------
local nTotMem, nFGMem, nBGMem

nTotMem= val(sys(1001))
nFGMem= val(sys(3050, 1))
nBGMem= val(sys(3050, 2))

if m.nTotMem < m.nFGMem
	* Adjust the pool sizes
	sys(3050, 1, m.nTotMem / 3)	&& Foreground buffer pool
	sys(3050, 2, min(m.nTotMem / 3, m.nBGMem))	&& Background memory pool
endif
endproc	&& checkMem()

...and finally. You might want to check out the Microsoft MAPS program. At the moment, it is US$475 per year but grants you access to internal use software and limited access to MSDN subscription software. That's where I get almost all the software that I use. Much cheaper than a standard MSDN subscription

Rob Spencer
Caliptor Pty Ltd

http://www.caliptor.com.au/

 

[Olaf Doschke]

I don't know Rob,

but SYS(1001) is not a very good base size.

The help says: In Visual FoxPro, SYS(1001) returns the virtual memory pool size, which is approximately four times the amount of physical memory.

I get 2^31 from it, which is 1 GB. On a PC with 8GB, so the help is wrong, but SYS(1001) also is wrong.

See

http://fox.wikis.com/wc.dll?Wiki~sys3050

DO NOT USE the old 1/3 rule of thumb

Indeed any 32bit process may use up to 2GB, but if queries on large tables are not optimized as expected you still just need a few MB to build the bitmaps rushmore needs.
You should use system API to get correct figures and if you detect more than 2GB you could indeed take 2GB, but you seldom need that kind of RAM for memory objects.
Rather work with a constant of 16MB.

Another quote:

SYS(3050) does not really affect "the memory available to Visual FoxPro", it only affects the memory used for things like caching tables and indexes. (See Christof's explanation -> paragraphs on Memory management)

It also affects the memory used to store rushmore optimization objects. I have witnessed that and reported about it here. If that is exceeded, VFP doesn't take more RAM but swap file. Even that is no reason to be greedy, though. If several foxpro apps run, they should all be satisfied with a few MB each.

Overall memory management won't cause Syntax errors, it may only effect performance.

Bye, Olaf.

 

[rlawrence]

Very interesting discussion. I have two users who are having issues--both running Windows 7 on their client machines. One keeps their data on a Windows Small Business Server (Win 2008?), the other is running a peer-to-peer network.

A quote from Dan:

I've said it before and I'll say it again. Windows 7 and Windows 8 ARE NOT FILE SERVER OPERATING SYSTEMS. If you are using them to build a "network" without a file server you are doing peer-to-peer networking which has, historically, been the least effective way to run a Foxpro application going back to Foxpro DOS.

I have to admit that I don't really appreciate the difference between Windows client and server O/S's. I suspect that what Dan says here is true. I don't understand, however, why the file systems would behave differently in these operating systems. And if it were me offering peer-to-peer networking, and reliability was an issue, I would want to fix it! I am aware of issues with networking hardware having an impact on the reliability of Foxpro; but my goodness, we're talking about issues raised back in the 90's. My sense is that both software and hardware have vastly improved since then. Could this be a contributor on new Windows 7 machines--even in a peer-to-peer network?

Not doubting you, Dan. This is precisely the kind of conversation I was hoping for. Just exploring.

 

[rlawrence]

Also an intriguing point from Rob. Yes, complex SQL queries exacerbate the problem.

Olaf, you asked me to provide the query that was causing a specific problem. I haven't yet captured it at the point of execution, but here is my template. This query is used in a "Monthly Sales by Title" report. It summarizes sales data and inventory by month over the date range selected. It is typical to look at sales over the last year or so, and get a result set of 20 or 30 records (after a WHOLE LOT of crunching, you can tell).

Note that the token, "<<select_str>>" is substituted with user provided selection criteria prior to submitting the query to EXECUTESQL(). In this test, I have used only a date range for testing, so the selection criteria will be empty. The values of m.start_date and m.end_date will be set.

You will also see another token in the FROM clause: "<<Report Title: Title Sales BY Discount|<<Select_str>>>>".
That is a subquery that is executed separately, but the Title Sales by Discount query is every bit as complicated as this one. I'll be glad to share it if you really want to see it, but rest assured that these queries have been working just fine for years. It's not surprising, however, that this might cause a problem if memory management is a weakness.

SELECT 	;
	title_id, ;
	Year(ship_date) * 100 + Month(ship_date) AS Sort2, ;
	Year(ship_date) AS Year, ;
	Month(ship_date) AS Month, ;
	title, ;
	order_key, ;
	order_no, ;
	invoice_no, ;
	order_type, ;
	terms, ;
	oDiscount, ;
	ship_date, ;
	item_no, ;
	order_date, ;
	quantity, ;
	price, ;
	discount, ;
	item_state, ;
	0000000.00 + order_amt AS saleamount, ;
	0000000.00 + IIF(order_type = "C" AND orders.discount + ordritem.discount < 100, order_qty, 0) AS soldqty, ;
	0000000.00 AS  consignqty, ;
	0000000.00 + IIF(orders.discount + ordritem.discount >= 100, order_qty, 0) AS  freeqty, ;
	0000000.00 + IIF(order_type = "R" AND EMPTY(condition), order_qty, 0) AS retgoodqty, ;
	0000000.00 + IIF(order_type = "R" AND condition = "D", order_qty, 0) AS rethurtqty, ;
	0000000.00 AS adjustqty ;
  FROM <<Report Title: Title Sales BY Discount|<<Select_str>>>> ;
UNION ;
SELECT 	;
	title.title_id, ;
	Year(orders.ship_date) * 100 + Month(orders.ship_date) AS Sort2, ;
	Year(orders.ship_date) AS Year, ;
	Month(orders.ship_date) AS Month, ;
	title.title, ;
	orders.order_key, ;
	orders.order_no, ;
	orders.invoice_no, ;
	orders.order_type, ;
	orders.terms, ;
	orders.discount AS oDiscount, ;
	orders.ship_date, ;
	ordritem.item_no, ;
	ordritem.order_date, ;
	ordritem.quantity, ;
	ordritem.price, ;
	ordritem.discount, ;
	ordritem.item_state, ;
	0000000.00 AS saleamount, ;
	0000000.00 AS  soldqty, ;
	0000000.00 + ordritem.quantity AS consignqty, ;
	0000000.00 AS freeqty, ;
	0000000.00 AS retgoodqty, ;
	0000000.00 AS rethurtqty, ;
	0000000.00 AS adjustqty ;
  FROM ordritem ;
    JOIN orders ON orders.order_key = ordritem.order_key ;
    JOIN title ON upper(title.title_id) == upper(ordritem.title_id) ;
  WHERE orders.order_type = "C" ;
	AND orders.terms IN ("Consignment", "Transfer") ;
	AND ordritem.item_no > 0 ;
	AND ordritem.item_no NOT IN (SELECT item_no FROM Sales) ;
	AND ordritem.quantity != 0 ;
  	AND orders.ship_date >= m.start_date ;
  	AND orders.ship_date <= m.end_date ;
  	AND ordritem.item_state NOT IN ("A", "B", "X", "Y") ;
  	<<SELECT_STR>> ;
UNION ;
SELECT 	;
	title.title_id, ;
	Year(orders.ship_date) * 100 + Month(orders.ship_date) AS Sort2, ;
	Year(orders.ship_date) AS Year, ;
	Month(orders.ship_date) AS Month, ;
	title.title, ;
	orders.order_key, ;
	orders.order_no, ;
	orders.invoice_no, ;
	orders.order_type, ;
	orders.terms, ;
	orders.discount AS oDiscount, ;
	orders.ship_date, ;
	ordritem.item_no, ;
	ordritem.order_date, ;
	ordritem.quantity, ;
	ordritem.price, ;
	ordritem.discount, ;
	ordritem.item_state, ;
	0000000.00 AS saleamount, ;
	0000000.00 AS  soldqty, ;
	0000000.00 AS consignqty, ;
	0000000.00 + ordritem.quantity AS freeqty, ;
	0000000.00 AS retgoodqty, ;
	0000000.00 AS rethurtqty, ;
	0000000.00 AS adjustqty ;
  FROM ordritem ;
    JOIN orders ON orders.order_key = ordritem.order_key ;
    JOIN title ON upper(title.title_id) == upper(ordritem.title_id) ;
  WHERE orders.order_type = "C" ;
	AND orders.terms = "Review Copy" ;
	AND ordritem.item_no > 0 ;
	AND ordritem.item_no NOT IN (SELECT item_no FROM Sales) ;
	AND ordritem.quantity != 0 ;
  	AND orders.ship_date >= m.start_date ;
  	AND orders.ship_date <= m.end_date ;
  	AND ordritem.item_state NOT IN ("A", "B", "X", "Y") ;
  	<<SELECT_STR>> ;
UNION ;
SELECT 	;
	title.title_id, ;
	Year(orders.ship_date) * 100 + Month(orders.ship_date) AS Sort2, ;
	Year(orders.ship_date) AS Year, ;
	Month(orders.ship_date) AS Month, ;
	title.title, ;
	orders.order_key, ;
	orders.order_no, ;
	orders.invoice_no, ;
	orders.order_type, ;
	orders.terms, ;
	orders.discount AS oDiscount, ;
	orders.ship_date, ;
	ordritem.item_no, ;
	ordritem.order_date, ;
	ordritem.quantity, ;
	ordritem.price, ;
	ordritem.discount, ;
	ordritem.item_state, ;
	0000000.00 AS saleamount, ;
	0000000.00 AS  soldqty, ;
	0000000.00 AS consignqty, ;
	0000000.00 + ordritem.quantity AS freeqty, ;
	0000000.00 AS retgoodqty, ;
	0000000.00 AS rethurtqty, ;
	0000000.00 AS adjustqty ;
  FROM ordritem ;
    JOIN orders ON orders.order_key = ordritem.order_key ;
    JOIN title ON upper(title.title_id) == upper(ordritem.title_id) ;
  WHERE orders.order_type = "C" ;
	AND ordritem.item_no > 0 ;
	AND ordritem.item_no NOT IN (SELECT item_no FROM Sales) ;
	AND ordritem.quantity != 0 ;
	AND ((orders.discount + ordritem.discount >= 100) OR ordritem.price = 0) ;
  	AND orders.ship_date >= m.start_date ;
  	AND orders.ship_date <= m.end_date ;
  	AND ordritem.item_state NOT IN ("A", "B", "X", "Y") ;
  	<<SELECT_STR>> ;
UNION ;
SELECT 	;
	title.title_id, ;
	Year(ordritem.order_date) * 100 + Month(ordritem.order_date) AS Sort2, ;
	Year(ordritem.order_date) AS Year, ;
	Month(ordritem.order_date) AS Month, ;
	title.title, ;
	0000000000 AS order_key, ;
	0000000000 AS order_no, ;
	0000000000 AS invoice_no, ;
	" " AS order_type, ;
	SPACE(20) AS terms, ;
	000.00 AS oDiscount, ;
	ordritem.order_date AS ship_date, ;
	ordritem.item_no, ;
	ordritem.order_date, ;
	ordritem.quantity, ;
	ordritem.price, ;
	ordritem.discount, ;
	ordritem.item_state, ;
	0000000.00 AS saleamount, ;
	0000000.00 AS  soldqty, ;
	0000000.00 AS  consignqty, ;
	0000000.00 AS  freeqty, ;
	0000000.00 AS retgoodqty, ;
	0000000.00 AS rethurtqty, ;
	0000000.00  + IIF(EMPTY(ordritem.condition), -ordritem.quantity, 0) AS adjustqty ;
FROM ordritem JOIN title ON UPPER(title.title_id) == UPPER(ordritem.title_id) ;
  WHERE ordritem.item_no > 0 ;
    AND ordritem.quantity != 0 ;
  	AND ordritem.order_date >= m.start_date ;
  	AND ordritem.order_date <= m.end_date ;
  	AND ordritem.item_state = "A" ;
  	<<SELECT_STR>> ;
  ORDER BY 1, 2 desc ;
INTO CURSOR monthsales

 

[rlawrence]

Almost missed this...

...and finally. You might want to check out the Microsoft MAPS program.

I'll check it out. Thanks Rob.

And this from Dave...

Is there any sort of macro substitution or expansion in your queries? Are there any references to directories?
All those could be mistranslated by the OS as virtual (or even library) paths.

Obviously, the answer here is, "Yes". All path substitutions use the DBF() function, so they should be physical paths. But, perhaps there is a difference when running in compatibility mode? I'll check that out when I capture the above query in execution.

So, what have we got?
[ol 1]
[li]Possible memory management issues in Windows 7?[/li]
[li]Possible networking issues--particularly in the peer-to-peer network?[/li]
[li]Possible issue with virtual paths in Windows 7?[/li]
[/ol]

My next steps will be to devise some sort of test for each of these.

I don't mean to close the conversation, but I REALLY want to thank you all. This has been very helpful.

 

[Olaf Doschke]

Well, the only way to spot the syntax error would be sing the substitutions in effect.

FROM <<Report Title: Title Sales BY Discount|<<Select_str>>>> ;

If this is a DBF file name, and you have spaces in the path, that would cause a syntax error, obviously.
That wouldn't be OS dependent, though, that would simply be installation and path dependent.

You can't use the normal textmerge funcionailty in this place, as Report Title: Title Sales BY Discount|<<Select_str>> isn't a valid expression, that could be merged in here.
I would really like to see the final statement to spot the syntax error.

Bye, Olaf.

 

[rlawrence]

Actually, it's a bit more complicated than that. I've implemented a multi-tiered query capability. The line you cite is part of that system. Here is the code as it appears just before execution by EXESCRIPT(). (Sorry, I mis-named the execution function earlier.) Also, sorry for the loss of formatting, but thought it best to provide the command as it actually appears at the time of execution.

SELECT  title_id,  Year(ship_date) * 100 + Month(ship_date) AS Sort2,  Year(ship_date) AS Year,  Month(ship_date) AS Month,  title,  order_key,  order_no,  invoice_no,  order_type,  terms,  oDiscount,  ship_date,  item_no,  order_date,  quantity,  price,  discount,  item_state,  0000000.00 + order_amt AS saleamount,  0000000.00 + IIF(order_type = "C" AND orders.discount + ordritem.discount < 100, order_qty, 0) AS soldqty,  0000000.00 AS consignqty,  0000000.00 + IIF(orders.discount + ordritem.discount >= 100, order_qty, 0) AS freeqty,  0000000.00 + IIF(order_type = "R" AND EMPTY(condition), order_qty, 0) AS retgoodqty,  0000000.00 + IIF(order_type = "R" AND condition = "D", order_qty, 0) AS rethurtqty,  0000000.00 AS adjustqty  FROM SALES UNION SELECT  title.title_id,  Year(orders.ship_date) * 100 + Month(orders.ship_date) AS Sort2,  Year(orders.ship_date) AS Year,  Month(orders.ship_date) AS Month,  title.title,  orders.order_key,  orders.order_no,  orders.invoice_no,  orders.order_type,  orders.terms,  orders.discount AS oDiscount,  orders.ship_date,  ordritem.item_no,  ordritem.order_date,  ordritem.quantity,  ordritem.price,  ordritem.discount,  ordritem.item_state,  0000000.00 AS saleamount,  0000000.00 AS soldqty,  0000000.00 + ordritem.quantity AS consignqty,  0000000.00 AS freeqty,  0000000.00 AS retgoodqty,  0000000.00 AS rethurtqty,  0000000.00 AS adjustqty  FROM ordritem   JOIN orders ON orders.order_key = ordritem.order_key   JOIN title ON upper(title.title_id) == upper(ordritem.title_id)  WHERE orders.order_type = "C"  AND orders.terms IN ("Consignment", "Transfer")  AND ordritem.item_no > 0  AND ordritem.item_no NOT IN (SELECT item_no FROM Sales)  AND ordritem.quantity != 0   AND orders.ship_date >= m.start_date   AND orders.ship_date <= m.end_date   AND ordritem.item_state NOT IN ("A", "B", "X", "Y")    UNION SELECT  title.title_id,  Year(orders.ship_date) * 100 + Month(orders.ship_date) AS Sort2,  Year(orders.ship_date) AS Year,  Month(orders.ship_date) AS Month,  title.title,  orders.order_key,  orders.order_no,  orders.invoice_no,  orders.order_type,  orders.terms,  orders.discount AS oDiscount,  orders.ship_date,  ordritem.item_no,  ordritem.order_date,  ordritem.quantity,  ordritem.price,  ordritem.discount,  ordritem.item_state,  0000000.00 AS saleamount,  0000000.00 AS soldqty,  0000000.00 AS consignqty,  0000000.00 + ordritem.quantity AS freeqty,  0000000.00 AS retgoodqty,  0000000.00 AS rethurtqty,  0000000.00 AS adjustqty  FROM ordritem   JOIN orders ON orders.order_key = ordritem.order_key   JOIN title ON upper(title.title_id) == upper(ordritem.title_id)  WHERE orders.order_type = "C"  AND orders.terms = "Review Copy"  AND ordritem.item_no > 0  AND ordritem.item_no NOT IN (SELECT item_no FROM Sales)  AND ordritem.quantity != 0   AND orders.ship_date >= m.start_date   AND orders.ship_date <= m.end_date   AND ordritem.item_state NOT IN ("A", "B", "X", "Y")    UNION SELECT  title.title_id,  Year(orders.ship_date) * 100 + Month(orders.ship_date) AS Sort2,  Year(orders.ship_date) AS Year,  Month(orders.ship_date) AS Month,  title.title,  orders.order_key,  orders.order_no,  orders.invoice_no,  orders.order_type,  orders.terms,  orders.discount AS oDiscount,  orders.ship_date,  ordritem.item_no,  ordritem.order_date,  ordritem.quantity,  ordritem.price,  ordritem.discount,  ordritem.item_state,  0000000.00 AS saleamount,  0000000.00 AS soldqty,  0000000.00 AS consignqty,  0000000.00 + ordritem.quantity AS freeqty,  0000000.00 AS retgoodqty,  0000000.00 AS rethurtqty,  0000000.00 AS adjustqty  FROM ordritem   JOIN orders ON orders.order_key = ordritem.order_key   JOIN title ON upper(title.title_id) == upper(ordritem.title_id)  WHERE orders.order_type = "C"  AND ordritem.item_no > 0  AND ordritem.item_no NOT IN (SELECT item_no FROM Sales)  AND ordritem.quantity != 0  AND ((orders.discount + ordritem.discount >= 100) OR ordritem.price = 0)   AND orders.ship_date >= m.start_date   AND orders.ship_date <= m.end_date   AND ordritem.item_state NOT IN ("A", "B", "X", "Y")    UNION SELECT  title.title_id,  Year(ordritem.order_date) * 100 + Month(ordritem.order_date) AS Sort2,  Year(ordritem.order_date) AS Year,  Month(ordritem.order_date) AS Month,  title.title,  0000000000 AS order_key,  0000000000 AS order_no,  0000000000 AS invoice_no,  " " AS order_type,  SPACE(20) AS terms,  000.00 AS oDiscount,  ordritem.order_date AS ship_date,  ordritem.item_no,  ordritem.order_date,  ordritem.quantity,  ordritem.price,  ordritem.discount,  ordritem.item_state,  0000000.00 AS saleamount,  0000000.00 AS soldqty,  0000000.00 AS consignqty,  0000000.00 AS freeqty,  0000000.00 AS retgoodqty,  0000000.00 AS rethurtqty,  0000000.00 + IIF(EMPTY(ordritem.condition), -ordritem.quantity, 0) AS adjustqty FROM ordritem JOIN title ON UPPER(title.title_id) == UPPER(ordritem.title_id)  WHERE ordritem.item_no > 0   AND ordritem.quantity != 0   AND ordritem.order_date >= m.start_date   AND ordritem.order_date <= m.end_date   AND ordritem.item_state = "A"     ORDER BY 1, 2 desc INTO CURSOR monthsales

Olaf, If you look carefully, you can see that the token you highlighted from my previous template has been changed to "FROM SALES". That's it. "SALES" is a cursor from the lower level query. That query looks like the following at execution...

SELECT     UPPER(LEFT(ordritem.title, 50)) AS Sort1,     ordritem.title_id AS Sort2,     1-(orders.discount+ordritem.discount)/100 AS rate,     IIF(EMPTY(title.isbn), title.isbn13, title.isbn) AS ISBN,     ordritem.quantity AS order_qty,     ordritem.quantity * (ordritem.price - ordritem.deduction) * (1 - orders.discount/100)      AS order_amt,     0000000.00 + SUM(IIF(ISNULL(itemrcpt.receipt_no), 0, itemrcpt.quantity)) AS paid_qty,     0000000.00 + SUM(IIF(ISNULL(itemrcpt.receipt_no), 0, itemrcpt.amount)) AS paid_amt,        orders.order_no,     orders.invoice_no,     orders.order_type,     orders.customerno,     orders.shptocntct,     LEFT(STRTRAN(STRTRAN(orders.shptoaddr, CHR(13), [, ]), CHR(10)), 50) AS shptoaddr,     orders.terms,     orders.discount AS odiscount,     orders.ship_date,     ordritem.*  FROM ordritem   JOIN orders ON orders.order_key = ordritem.order_key   JOIN title ON upper(title.title_id) == upper(ordritem.title_id)   LEFT JOIN itemrcpt ON itemrcpt.item_no = ordritem.item_no   LEFT JOIN receipt ON receipt.receipt_no = itemrcpt.receipt_no  WHERE ordritem.item_no > 0     AND orders.order_type = "C"      AND orders.order_no > 0     AND orders.terms NOT IN ("Consignment", "Pro Forma", "Review Copy", "Transfer")     AND orders.discount + ordritem.discount < 100     AND ordritem.price != 0     AND orders.ship_date >= m.start_date     AND orders.ship_date <= m.end_date     AND ordritem.item_state NOT IN ("A", "B", "X", "Y")       GROUP BY ordritem.item_no UNION SELECT     UPPER(LEFT(ordritem.title, 50)) AS Sort1,     ordritem.title_id AS Sort2,     itemrcpt.amount/itemrcpt.quantity/ordritem.price AS rate,     IIF(EMPTY(title.isbn), title.isbn13, title.isbn) AS ISBN,     SUM(itemrcpt.quantity) AS order_qty,     SUM(itemrcpt.quantity * (ordritem.price - ordritem.deduction) * (1 - orders.discount/100))      AS order_amt,     SUM(itemrcpt.quantity) AS paid_qty,     SUM(itemrcpt.amount) AS paid_amt,     orders.order_no,     orders.invoice_no,     orders.order_type,     orders.customerno,     orders.shptocntct,     LEFT(STRTRAN(STRTRAN(orders.shptoaddr, CHR(13), [, ]), CHR(10)), 50) AS shptoaddr,     orders.terms,     orders.discount AS odiscount,     receipt.recpt_date AS ship_date,     ordritem.*  FROM ordritem   JOIN orders ON orders.order_key = ordritem.order_key   JOIN title ON upper(title.title_id) == upper(ordritem.title_id)   JOIN itemrcpt ON itemrcpt.item_no = ordritem.item_no   JOIN receipt ON receipt.receipt_no = itemrcpt.receipt_no  WHERE ordritem.item_no > 0     AND orders.order_type = "C"      AND orders.order_no > 0     AND orders.terms = "Consignment"     AND ordritem.price != 0     AND orders.ship_date >= m.start_date     AND orders.ship_date <= m.end_date     AND ordritem.item_state NOT IN ("A", "B", "X", "Y")       GROUP BY ordritem.item_no UNION SELECT     UPPER(LEFT(ordritem.title, 50)) AS Sort1,     ordritem.title_id AS Sort2,     1-(orders.discount+ordritem.discount)/100 AS rate,     IIF(EMPTY(title.isbn), title.isbn13, title.isbn) AS ISBN,     IIF(orders.terms="Consignment", 0, -ordritem.quantity) AS order_qty,     IIF(orders.terms="Consignment", 0, -ordritem.quantity) *      (ordritem.price - ordritem.deduction) * (1 - orders.discount/100) AS order_amt,     IIF(orders.terms="Consignment", 0, -ordritem.quantity) AS paid_qty,     IIF(orders.terms="Consignment", 0, -ordritem.quantity) *      (ordritem.price - ordritem.deduction) * (1 - orders.discount/100) AS paid_amt,     orders.order_no,     orders.invoice_no,     orders.order_type,     orders.customerno,     orders.shptocntct,     LEFT(STRTRAN(STRTRAN(orders.shptoaddr, CHR(13), [, ]), CHR(10)), 50) AS shptoaddr,     orders.terms,     orders.discount AS odiscount,     orders.ship_date,     ordritem.*  FROM ordritem   JOIN orders ON orders.order_key = ordritem.order_key   JOIN title ON upper(title.title_id) == upper(ordritem.title_id)  WHERE ordritem.item_no > 0     AND orders.order_type = "R"      AND orders.order_no > 0     AND orders.terms != "Transfer"     AND orders.discount + ordritem.discount < 100     AND ordritem.price != 0     AND orders.ship_date >= m.start_date     AND orders.ship_date <= m.end_date     AND ordritem.item_state NOT IN ("A", "B", "X", "Y")       GROUP BY ordritem.item_no  ORDER BY 1, 2, 3 DESC  INTO CURSOR sales

Suffice it to say that a "sale" is not as simple as it sounds in my area of expertise. This multi-tiered query allows me to define one report, (e.g. the "Title Sales by Discount" report) and use the results in another report (e.g. the "Monthly Sales by Title" report). So, if I decide to alter the lower-level report, the change will automatically be propagated to other reports that use it.

But honestly, these reports have been functional for several years now. The problem I highlighted was to show that a complex but functional report that works under most conditions, now does NOT work under Windows 7 without setting the compatibility mode. I think this also dispels the virtual path theory--except perhaps that the path setting in Foxpro is interpreted differently in Windows 7?

In case it's relevant, here is a copy of my config.fpw file. These settings are typical for an installation of my application.

BLINK=OFF
CATMAN=OFF
CLOCK=STATUS
MEMOWIDTH=75
MVCOUNT=1536
PATH="C:\USERS\PUBLIC\PADATA\AWP"
RESOURCE=FOXUSER
EXCLUSIVE=ON
SYSFORMATS=ON
TMPFILES="Temp"

 

[Olaf Doschke]

If you really find EXESCRIPT in your source code that's your syntax error, there is no such function of VFP, there is EXECSCRIPT. But it only pays for more than a single command, for more than a single sql select query. In your case you would rather &lcCommand than EXECSCRIPT(lcCommmand) to execute the SQL. You could then even benefit from using variables currently in scope, which EXECSCIPT won't support.

There also is a catch with queries from query results. You would need to ensure your initial queries use NOFILTER. But again this is nothing leading to syntax error.

The query you show now creates the SALES cursor. Fine, but that still doesn't give the query causing the syntax error, does it?

Bye, Olaf.