3 Replies Latest reply on Mar 5, 2010 10:54 AM by ff_mac

    SQL Linked Server Query Problems

    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?

        • 1. Re: SQL Linked Server Query Problems
          JoshO.

          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. Re: SQL Linked Server Query Problems
            ff_mac

            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

            • 3. Re: SQL Linked Server Query Problems
              ff_mac

              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?