Issue a SQL query against FMP?

[laker_42]

I am trying to issue the following TSQL command against a FMP database:

Select *

from OpenQuery(HWSWDATABASE, 'select "Servers"."ServerName" from "Servers"')

I set up a FMP DSN, added the DSN as a linked server on our SQL Server 2000 machine. I receive the following error when I issue the query above:

Server: Msg 7341, Level 16, State 2, Line 1

Could not get the current row value of column '[MSDASQL].ServerName' from the OLE DB provider 'MSDASQL'.

[OLE/DB provider returned message: Requested conversion is not supported.]

OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].

I didn't know if I had the correct syntax that FMP wants the SQL in? Anyone done this yet? This would be awesome if I can get it to work. Any help would be appreciated.

Thanks!

John

[SJM]

I don't know what TSQL is, so what follows might be *completely* confused. Given that, it might be easier to debug if you separate expression "OpenQuery(HWSWDATABASE, 'select "Servers"."ServerName" from "Servers"')" from the query. (I have no idea what this expression means, although it appears to give the name of a server, whereas you need the name of the table to follow the 'from' part of the SQL query.)

So rather than:

$query = "Select * from OpenQuery(HWSWDATABASE, 'select "Servers"."ServerName" from "Servers"')"

perform($query)

Try something like:

$table = OpenQuery(HWSWDATABASE, 'select "Servers"."ServerName" from "Servers"')

$query = "Select * from $table"

perform($query)

(that is some made-up language, should be clear enough what the idea is)

Basically if it fails on the first line you'll know something is wrong with that part of the call. If not, you can print out the value of $table to see if it is what you expect, and then at least you'll know more about where it is failing.

[laker_42]

Has anyone done what I orginally posted? I am getting back to this project and still haven't had any success querying a FMP db from SQL Server. I can get a Crystal Report to go against it and it is issuing SQL statements to get the data. So I don't understand why I can't get SQL Server itself to issue SQL statements and return data.

Of course I called FMP tech support and they say they don't support this. BIG SURPRISE! They state in the help & other documentation that you can do this but they won't help you actually do it.

If anyone has any more feedback, I would appreciate it.

Thanks!

John

[laker_42]

We did discover that we can use DTS to access FMP tables directly. We can schedule DTS packages to bring FMP data into SQL tables. This is a step forward then what was available in previous FMP versions. It would still be nice to query a FMP database with Query Analyzer but we will take what we can get.

John