ODBC Configuration for Filemaker

[twosw33t]

I have a Windows network that has a Filemaker 5 database running on Filemaker Server. I need to be able to query and select data from the filemaker database and then pass the results to a Microsoft SQL 2000 database. This process needs to be automated and runs as a background job. I had envisioned a scheduled VB/Perl script to do this data transformation from filemaker throught to SQL 2000. The script would query filemaker through an ODBC data source and update SQL 2000 through a different ODBC data source. It all seemed reasonably straightforward until I started looking at the requirements to setup an ODBC connection to the required data sources. SQL wasn't a problem however when looking at filemaker requirements the only documentation I could find for ODBC connector required the Filemaker Client application to be running (all the time) with the relevant data files open. This approach just isn't practical or very reliable, our backend systems/servers are often used to provide additional services (not just filemaker) and are often used by other people which often requires other people to login. As a result any open applications such as filemaker would be automatically closed on logoff.

This being the case, does anybody know of a way to setup an ODBC data source to a filemaker 5 database without having the filemaker client running, if there's other third party utilities that can do this I would also like to know about it. If anyone has any other suggestions or approaches on how to extract data from filemaker and pass it to SQL 2000, also more then willing to listen .

Thanks

[Anatoli]

RE: This being the case, does anybody know of a way to setup an ODBC data source to a filemaker 5 database without having the filemaker client running...

Not possible.

[ERlc_the_red]

That is too bad, because I had the same problem. Now we have to find some dedicated machine JUST for the FileMaker application.

[Anatoli]

It doesn't have to be dedicated

[ERlc_the_red]

How can one do this then? It seems the FMPro application has to be open to write to the flat file through ODBC.

Therefore, I set up FileMaker Pro as a SERVICE using the standard exe's (using SVRANY.exe and INSTSVR.exe, and making the proper registry entries), made sure the FileMaker Pro files were shared correctly, started the service, and ran the same piece of code (that successfully inserted to the database WITH the FileMaker Pro application open) and it could not insert the records with just the service started. In fact, using an ADO connection object, there were internal errors (in the Errors collection of the ADODB.Connection object) while opening the connection, both with error number 0. These errors were:

[FileMaker][ODBC FileMaker Pro driver]Optional feature not implemented.

[FileMaker][ODBC FileMaker Pro driver]Driver's SQLSetConnectAttr failed.

Now I KNOW the service properly establishes an instance of FileMaker Pro client in the background since if I have my filemaker service started and I open another connection to the same file through the FileMaker application, it gives me an error message refering to the fact that another connection to file is already there. So why is setting up a service not sufficient? And if I don't even need a service, how can I write to the .fp5 file without messing around with an open FM Pro client application? We don't want some application sitting open on our web server!

[Anatoli]

FMP must be open.

FMP doesn't run happily as service

FMP can run on any machine with NT class OS as minimized application. It doesn't have to be dedicated machine to FM just powerful enough.