Robert

We switched to SQL Server 2000 two years ago for the backend and once we figured out a few things we love it.

1. We started out using remote views, but we had recurring issues with these as table were modified and improved. So we now strictly use SQL Pass Through (SPT). We attempt to keep most of our queries in Stored Procedures so we can execute a line like:

sqlexec(gnConnHandle,[exec dbo.myStoredProcedure ?Parm1,?parm2],[myResultSet])

2. We create a connection to the database via DSNLess String Connection so we don't have to configure/maintain any odbc settings.

gnConnHandle=SQLSTRINGCONNECT(lcDSNless)

3. Like you are product is sold and nationwide, and previously we used Stonefield for updating our foxpro tables and indexs. Now have VPN connections to each client. Each client workstation uses a syslauncher application to check to see if there are any updates on their local server (specified directory I have access to so I can upload application changes) and then pulls them to the workstation, then launches the actual application. For updating the databases, I use a tool called SQL Compare from Red-Gate Software (red-gate.com). They are out of england, but it will compare your master database to any other sql server you have access to and create the modification scripts and run them for you. It wasn't very much money, and for $60 a year you get update and support. I think it was 399 for the actual product. But I can compare and update my clients databases in 5 minutes with it.

4. Customer satisfaction. They know and like that it is a "bigger" database application than foxpro (none of the but isn't foxpro just for desktops? remarks. Previously, we stored the databases and indexes on the server, so as the database/indexs go bigger, the network bandwidth we used was getting bigger and the application kept seeming slower. Now, unless they are running a really big report, it's like the whole thing is on the client, they can't even tell and the network admin guys have been really pleased.


Hope this help and good luck.

Kirk