AnsweredAssumed Answered

SQL Linked Server Query Problems

Question asked by ff_mac on Mar 4, 2010
Latest reply on Mar 5, 2010 by ff_mac

Title

SQL Linked Server Query Problems

Post

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?

Outcomes