Linked Server to FileMaker

yanbes
yanbes used Ask the Experts™
on
Hello everyone,
I was trying to setup my SQL Server 2008 to connect to a FileMaker file (much like linked servers can connect to Excel or Access files) but I am having problems.

I installed the DataDirect SequeLink 5.5 ODBC drivers and this piece seems to be working fine (at least the Test Connection is working).

The linked server is actually working since I see all the tables but I cannot seem to be able to access the records inside it.

When I do a usual "SELECT * FROM [FileMakerLinkedServer]...[TableInFileMaker]" it give me :
OLE DB provider "MSDASQL" for linked server "FileMakerLinkedServer" returned an invalid index definition for table "TableInFileMaker".

My linked server is configured as such:
execute sp_addlinkedserver
@server = 'FileMakerLinkedServer'
@srvproduct = 'FileMaker',
@provider='MSDASQL',
@datasrc='Name Of ODBC Data Source";

execute sp_addlinkedsrvlogin
@rmtsrvname =  'FileMakerLinkedServer' ,
@useself = 'FALSE',
@locallogin = NULL,
@rmtuser = 'admin', @rmtpassword = 'FileMakerAdminPassword'

Is there anyway to make this work?

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
North2AlaskaSenior Engineer

Commented:
I've not connected a SQL Database to Filemaker, just the other way.  But a thought came to mind.  Are you querying the table or a table Occurrence?  I'm thinking it should be the TO.

Author

Commented:
Hi,
Most (if not all) examples detail how to connect to SQL Server from Filemaker but not the other way around. I am not sure (but hoping) that this is at all possible.

How can I tell the difference between a table and the table occurrence? i.e.: how do you write a query to a table occurrence as opposed to the table itself?

Thank you for you help,
North2AlaskaSenior Engineer

Commented:
Again this is purely speculation and I do not have a set up that I can even test this with, but here is my thoughts.

Let's say I create a table name customer and then in the TG I create an occurrence of customer called sales_customers.

Now I write the sql query.  Should it be against the customer table or the sales_customers TO?  

I don't know the answer to that question, but it would be something to experiment with.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
I have updated tables from our ERP application and I am calling out the table name not the occurrence.

Author

Commented:
Hi jvaldes,

Was the ERP using SQL Server? How did you manage to get it working?

Thank you
Yannick

Commented:
I did a connection in between mysql and fm, mysql querying fm. and same as Valdes, using the tables names.
but this is a pretty hairy situation: fm's ODBC implementation is not compliant with anything, actually some say, it does not respect the full standard. so start with a very basic basic table, 2 fields for instance and se what it does.
also there are things like text in number fields which are tolerated in fm and won't be in sql.
the odbc setup is very basic: localhost/2399/fm_file w/o extension

Author

Commented:
Hi everyone,

I tried what you suggested lesouef and it didn't work.
However, as a step forward, I managed to create a small .NET application and configure the data source pointing to the FileMaker's ODBC driver and it worked (other than for two tables for which I got an out of memory error). This is good but I would rather not have to write a .NET application to fetch the data for my SQL Server.

I tried to reuse the same query on my linked server  in SQL Server but it still gave me the same message : "invalid index definition for table".

We are getting there but still no cigar.

Thank you

Commented:
sound like your fm key field is not praised... is a number or text? are we still trying a basic select * from db.table?

Author

Commented:
Ok.
With a little more testing yesterday, I managed to get some of the SQL Statements working on the Linked Server to FileMaker depending on the field I am selecting.
It seems that some fields just don't want to get retrieved and I can't figure out why.
The schema of the table in FileMaker is as defined in the pdf attached.
It appears that if the field I am trying to query is text (like Login ID) I get the "Requested conversion is not supported" error from the DataDirect driver. However, some other fields are fine (like Salary)!

For example, if I do "Select * FROM OPENQUERY(FileMakerLinkedServer, 'SELECT "Employee Name" FROM Payroll'), I get the Error In Result Columns error.
If I do SELECT * FROM OPENQUERY(FileMakerLinkedServer, 'SELECT cast("Employee Name" as nvarchar(100)) FROM Payroll), I get two rows with NULL values for Employee Names.
 
So this is most likely a datatype compatibility/conversion problem but I'm a little lost in what I can do to fix it.
Thanks for your input on this. Much appreciated.




Commented:
text fields is a pb from fm, they are variable and can be up to 64k, which you never do in sql. so you may have to extend your sql field definitions, or limit in fm (which can be done), but once limited, I am not sure these fields limit is seen by the odbc driver, and data properly truncated if needed. as a test make a text field limited to 32char in fm and try to select from that.
select cast is probably not supported either by fm... never checked the datadirect doc.
also media field may be a problem, fm does special stuff on this, they keep more info than a blob field, so compatibility is probably not obvious.
otherwise the dirty way is to export a text file and import it into sql...

Author

Commented:
This file is a replacement file for file I added to the comment I posted earlier (#24889878).
Payroll-Table-Schema.GIF

Commented:
the key contains text and numbers, I doubt sql likes that, also calcs and summaries may be a problem
Commented:
Hello all,

I found the solution finally.
The linked Server to Filemaker works.
My problem was that I kept having the error "Requested conversion is not supported" when I was trying to access any text fields.
It was only a problem of how the fields were configured in FileMaker.
In FileMaker, the text fields need to have the following settings : Always validate, do not allow to override value, and set a maximum number of characters.

For the record, here is a recap of my config.
FileMaker Setting:
- ODBC needs to be enabled in File->Sharing->ODBC
      - Set it to ON
      - Access to all users
- Text fields must be set to at least
      - Always Validate (Allow users to override during data entry must be cleared)
      - Maximum number of characters must be set
- Numeric fields weren't a problem for me.

ODBC setup :
- System DNS
      - Data Source Type : DataDirect Sequelink 5.5
      - Data Source Name, whatever you want, e.g.: OdbcToFileMaker
      - SequeLink Server Host : 127.0.0.1 (if on same computer)
      - SequeLink Server Port : 2399
      - Server Data Source : Hit the "..." button and select from the list.(Filemaker and the file you want to connect to must be loaded to see it in the list)

Linked Server Setup (SQL Server 2008):
- Add Linked Server
      - Linked server : linkedServerName
      - Provider : Microsoft OLE DB Provider for ODBC Drivers
      - Product Name : I put "FileMaker Pro" but I don`t think it matters
      - Data Source : Name of the ODBC Data Source Name as per above, e.g.: OdbcToFileMaker
      - In Security (on the left pane), I selected "Be made using this security context" and then entered admin and the admin password of the FileMaker file. If none is configured, leave it blank and enter admin as the Remote login.

Afterwards when I do something like this in SQL Server, it works:

SELECT *
FROM OPENQUERY([linkedServerName], 'SELECT NumericField, "text field1", "text field2" from TableInFileMaker');

Hope this is helpful to others as well.

Thank you,

Commented:
just unfair...

Commented:
answering to your automated reply:
-pretty confusing when it says:
------------------------
"On 7/30/2009, the question was going to be split for the following reason:
 Solution was provided.

The question was going to be closed as follows:
 Accepted answer: 0 points for yanbes's comment http:/Q_24579914.html#24945487
Assisted answer: 100 points for lesouef's comment http:/Q_24579914.html#24890674"
----------------
- nowhere it says deleting a question means giving those points...
- If a solution was provided as mentionned above, then it should be closed normally, and if the answer is not perfect, then a B or C mark can be given but not a pure deletion
- my rec is 3) Accept one or more Expert posts as the answer
- up to the asker to decide who deserves points, I don't want to pull the blanket for me.
- at least this sentence: "text fields is a pb from fm, they are variable and can be up to 64k, which you never do in sql. so you may have to extend your sql field definitions, or limit in fm (which can be done), " put him on the right track.
- been here for 6 years, and I am pretty fed up of the amount of Q being closed of left over with an acceptable answer
- if deleted, even his post summing up what he's done will not stay here for others

Author

Commented:
Hi lesouef,

Your answer regarding the text field length was part of the answer but not all of it. There was much more to it. In that sense, this is why I wanted to give out 100 points for the time you spent trying to help me. It turned out the solution was much more than that.

As far as deleting is concerned, it comes from a confusion from the way I was able to close the question on the web site. I was not able to close it while giving you points and accepting my own answer as the solution.

Also keep in mind, I could have just decided to not reply at all and leave the question without any comment for a long time. It was for the community's sake (and not my own) that I decided to take the time to formulate a full answer as part of the solution.

With that said, I think the question should be closed normally (as per what you said) and not deleted so the answer should be made available to the EE community.

My appologies for the confusion

Commented:
ok, ok, I don't  care about points, I have too many, and nobody hardly answers my own questions anyway, so they are useless to me.
It's just a matter of principle, there are so many questions left over or deleted, that I get easily fed up by now.
good point you answered...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial