Are you trying to use the ODBC connection to get data from FM? If yes, do you have FileMaker Server Advanced?
2) SQL doesn't deal well with "." in the name, from my experience. You might be able to enclose it in single quotes, ie 'Field.Name'. I can't test it right now. But I vaguely remember still having problems until we swapped out the "." for "_".
3) I guess it depends extensively on exact what you are doing. There are a lot of variables. Where is the web server? How are you transferring the data between FM and the SQL server? and a few other questions.
2) dots in field names
This one is easy. The FM ODBC driver does not accept bracketed fieldnames, so just rename the fields in FM.
1) I'm still getting an error when trying to execute a query in ssms for the linked server
OLE DB provider 'MSDASQL" for linked server "fmsample" returned an invalid index definition for table "Companies"
Google turns up nothing so any insight into this would be greatly appreciated.
Perhaps I am overlooking something in the LinkedServer config. Here is what I have for the FM Sample db
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product name: FMSample //can't create the linked server without product name, but it doesn't really seem to be used.
Data Source: FM_Sample //which is the name of the DSN
Security: Made using this security context //acct login set up in FM
3) I am testing all of this on a single machine running winxp x86 with FM 9 Pro Advanced and SqlServer 2008. Our webservers run win2008x64 and dbs are on separate win2008x64 servers running sql server x64. Because FM does not provide 64-bit ODBC drivers even on version 10, it looks like I will need to set up a win2008x86 system in the datacenter running sqlserver x86 in order to link to our FM 9 Advanced Server, then set up another linked server from sql_x64 to sql_x86
I've narrowed the problem down to not being able to read FM text fields from a linked server in SSMS.
works: select [fee paid],[date paid],number from fmsmaple...members
fails: select company,[fee paid],[date paid],number from fmsmaple...members
fails: select company from fmsmaple...members
I can successfully select number and date fields but any text field returns
Error: OLE DB provider "MSDASQL" for linked server "fmsample" returned message "Requested conversion is not supported.".
Cast() doesn't work, probably because the failure is upstream.
I also tried setting a maxLength on the text field in FM but that doesn't work either.
Excel can download from the same DSN without any problem, so this seems to indicate a bug in the DirectData ODBC driver that doesn't work properly with MSDASQL, and FM Tech Support should open an issue with DirectData.
Has anyone ever successfully read FM text fields from a linked server in SSMS?