Importing text into SQL Server from FileMaker via ODBC

0
votes

Our client is trying to export their data from their FileMaker 10 database into a MS SQL system via ODBC.

We have the DSNs set up, and can import date and number fields, but text fields fail with:

“requested conversion is not supported” Cannot get the current row value of column “[TQG6]...[Referral].ReferralAccountName” from OLE DB provider for MSDASQL for linked server “TQG6”

We've updated the ODBC drivers to SequeLink 6.0 Also tried with a temporary FileMaker 11 server and related driver.

It looks like there is a problem converting from FileMaker's rather generic 'Text' data type to SQL Server's more specific Char, Varchar or Text fields (and their unicode equivalents, nchar, etc.).

3
sql-serverodbcfilemaker
I've had some good responses giving alternative approaches, which is great. But I would like to know if anyone has got FM ODBC to SQL Server to work. Surely this can't be so fundamentally broken?!? - Paul Hutton

3 Answers

1
votes

I encountered this exact same issue attempting to communicate with a FileMaker 12 server from MS SQL. This version of FileMaker uses a proprietary "FileMaker ODBC" driver. We resolved the error by selecting "Describe text fields as long varchar" in the ODBC configuration.

0
votes

I don't quite understand how you've set up the connection. Technically there are the following options:

  1. Connect to FileMaker via ODBC and import from FileMaker.
  2. Add the MS SQL database to FileMaker as an external data source and then transfer data between tables from within FileMaker. Here you'll import into this external table from an existing internal table.
  3. (For completeness) Have FileMaker to push records one by one using the Execute SQL script step, where FileMaker will connect to MS SQL via ODBC.

It seems that you're using option 1; could you try option 2 instead?

0
votes

Is this a once off or a regular process you need to do? If it is a once off you could just try exporting to Access or even CSV format and importing into SQL Server.