SQL Linked Server Query Problems
I have set up a linked server in sqlserver 2008 to FM 9 Pro Advanced using datadirect seque ODBC driver that ships with FM.
For testing I am using the Sample db that comes with FM.
The linked server shows up in SQL Server Management Studio and I can see all the tables.
However, when I try a simple query
select * from fmsample...companies
I get OLE DB provider 'MSDASQL" for linked server "fmsample" returned an invalid index definition for table "Companies"
To make sure that the driver is working I was able to successfully get data in excel from this dsn.
Furthermore, I tried accessing the real db that I hope to connect to and ran into parser error problems in excel.
Turns out that some fields have dot in the name, such as "sort.order"
I tried editing the sql in ms query but couldn't find any way to escape the dot in the name, and suspect that I am out of luck.
I can successfully retrieve fields that do not have a dot in the name.
I notice that these simple queries are very slow and am wondering if anyone has successfully set up a linked server and if the performance is enough for data-driving a website.
So there are several questions here.
1) how to do a simple query in ssms of the sample db?
2) any way to get the odbc driver to accept a query for fields that have dot in the name?
3) real-world performance experience with fm and linked servers?