ODBC and MS SQL

[zinkoproducts]

We use Microsoft SQL as a repository for all our data. In my FmP 6 application I used the ODBC connection in FmP to push and pull data from FmP to MS SQL. All worked fine.

With version 8, I was hoping to do the pushing and pulling from the MS SQL side. Among other things MS SQL can run scheduled events; I was hoping that it could run out to FmP and get data in the wee hours when I wasn't looking!

MS SQL uses what it calls a Linked server to permanently connect to external data sources - other MS SQL servers, Access, ODBC, etc. This works fine.

I can even set up a linked server for FmP that actually shows me the FmP tables. But I cannot for the life of me do any kind of sql command against that linked server.

In MS SQL a select statement can have up to four parts to a table definition:

linkedservername.database.owner.table

So a sql select would be

Select field1, field2 from

myfmpodbcconnection.database.dbo.stuffTable

Life is grand.

BUT I cannot find out what the database name is supposed to be in FmP.

There are sql commands for doing so and they come back and say that the "catalog" name is not published (i.e. it can't find the db name).

Has anybody been able to read FmP directly from MS SQL? I know the ODBC connection works because I can read stuff in Access and Excel.

Help,

zinko

[Wim Decorte]

I think you need to ask that question in the DataDirect forums since they make the drivers. Seems to me this is something that is either supported on the driver side or not...

[Ben Goldstein]

At one of our clients we pull a over a million records each from FileMaker 8 server to SQL Server 2000 (takes about an hour). Instead of using SQL Servers "Linked Server" function you can just use SQL Servers Data Transformation Services (DTS), or SQL Server Integration Services if using SQL 2005. Have found SQL Servers "linked" server function to be fairly unreliable with Data Directs ODBC driver for FileMaker 8..

Ben