Linked Server to FileMaker

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.

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,

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.

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

Hi jvaldes,

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

Thank you
Yannick

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

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

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

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.

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

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

just unfair...

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

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

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...