10 Replies Latest reply on Aug 14, 2012 3:31 PM by TimDietrich

    Issue with updating a SQL Server ODBC record, cursor problem

    cmsmith.acutec

      Hi - a FileMaker newbie here.

       

      I'm using FileMaker Pro Advanced 12 (on Windows 7 Pro x64) and have added an ODBC connection to my project. I can see all of the tables from my local SQL Server Express install, I can create relationships, I can even add data to them through their generic FileMaker layouts. Something strange happens though after I create a new record and then go back and edit any value in that record and try to create something new. I get this ODBC error:

       

      FileMaker Pro Advanced_2012-08-13_15-56-01.png

      The only thing I can do to get out of this error message is hit 'OK' and then completely delete the record. I have Google the crap out of this and have come up with nothing. It appears that this is an issue within FMP's SQL, but I wanted to bring it to the community to see if anyone has dealt with this.

       

      Any help would be great with this. I want to use FMPA for a nice iPad interface, but I can't even get something simple to work.

        • 1. Re: Issue with updating a SQL Server ODBC record, cursor problem
          beverly

          Please specify the ODBC driver that you used.

           

          Beverly

          • 2. Re: Issue with updating a SQL Server ODBC record, cursor problem
            cmsmith.acutec

            I have tried SQL Server, SQL Server Native Client 10.0, and SQL Server Native Client 11.0.

            • 3. Re: Issue with updating a SQL Server ODBC record, cursor problem
              cmsmith.acutec

              I still can't get this to work. Has anyone had this issue?

               

              As far as I can tell this is FileMaker not handling some sort of logic correctly. I cannot use it at all to access any SQL Server data on my Windows 7 Pro machine.

              • 4. Re: Issue with updating a SQL Server ODBC record, cursor problem
                beverly

                More details please.

                What SQL Server (& version)?

                What ODBC driver (& version)?

                On what OS are you setting the DSN?

                Is it System & not User DSN?

                What system has FM (OS & version)?

                Is ODBC set on FMServer?

                 

                Thanks!

                 

                There is some issue

                 

                -- sent from my iPhone4 --

                Beverly Voth

                --

                • 5. Re: Issue with updating a SQL Server ODBC record, cursor problem
                  cmsmith.acutec

                  I am using the following:

                   

                  • Microsoft SQL Server 2008 Express R2
                  • I have tried the following ODBC drivers:
                    • SQL Server (version 6.01.7601.17514)
                    • SQL Server Native Client 10.0 (version 2009.100.4000.00)
                    • SQL Server Native Client 11.0 (version 2011.110.2100.60)
                  • I am using the DSN on Windows 7 Professional Service Pack 1
                  • Yes, it is a System DSN
                  • The same computer that has the database has FM, so FM is running on Windows 7 Professional Service Pack 1
                  • I had to uninstall FMServer because of it not working correctly with my local install of IIS. As far as I can tell though, I don't have to have FMServer installed to make an ODBC connection work (ESS), correct?

                   

                  Please let me know if you need any other information. Thanks, Beverly!

                  • 6. Re: Issue with updating a SQL Server ODBC record, cursor problem
                    TimDietrich

                    I've never run into this one myself.

                     

                    It looks like SQL Server is what is actually throwing the error - and based on the error message that you're getting, it looks like whatever TSQL it is that FileMaker is sending is being parsed properly, but is causing problem when it is executed.

                     

                    What you might want to do is run Profiler on the SQL Server side, and look to see exactly what it is that FileMaker is sending when the problem occurs. Then take the same TSQL that FM is sending, and then run it yourself to see what happens. It might shed some light on the problem.

                     

                    -- Tim

                    • 7. Re: Issue with updating a SQL Server ODBC record, cursor problem
                      cmsmith.acutec

                      Hey TIm,

                       

                      Did a little tracing and this is what I see.

                       

                      When I update a record that has already been stored in the database and then click out of a text box (or any other type of input), this is the TSQL that is running:

                       

                      DECLARE FMP_CURSOR SCROLL CURSOR FOR SELECT Id,Content,Closed,CreatedDate,UpdatedDate,CreatedBy,JobID,DefectCode_Note FROM "Testing LightSwitch".dbo.Issues WHERE Id=42 FOR UPDATE

                       

                      This line is actually in my trace 4 times...

                       

                      Then this twice:

                       

                      OPEN FMP_CURSOR

                       

                      And then:

                       

                      ROLLBACK TRANSACTION BEGIN TRANSACTION

                       

                      That's it. Does that make any sense at all?

                      • 8. Re: Issue with updating a SQL Server ODBC record, cursor problem
                        cmsmith.acutec

                        When I run the TSQL directly against that database in Management Studio I get the same Error:

                         

                        Msg 16916, Level 16, State 1, Line 0

                        A cursor with the name 'FMP_CURSOR' does not exist.

                         

                        I'm not much of an TSQL kind of guy, but it's my understanding that FileMaker is passing these statements to SQL Server? If so, then are they incorrect?

                        • 9. Re: Issue with updating a SQL Server ODBC record, cursor problem
                          cmsmith.acutec

                          OK, after digging around quite a bit I found an option in the SQL Server database properties call "Default Cursor". It was set to LOCAL by default and after reading some TSQL forums and such some were talking about global cursors. I changed the setting to GLOBAL and now FileMaker is acting correctly...

                           

                          I don't know if this is necessarily a "solution" though because I don't know what the impact is of changing this default. Also, I see nothing anywhere in FileMaker documentation that says I have to change this setting for FM to work correctly. Is this a bug or is this expected to be changed?

                          2 of 2 people found this helpful
                          • 10. Re: Issue with updating a SQL Server ODBC record, cursor problem
                            TimDietrich

                            I think this page helps to shed some light on what you were running into: http://sqlexplore.wordpress.com/2011/02/17/sql-server-2008-cursor-process/  In particular, this:

                            LOCAL: Specifies that the scope of the cursor is local to the program in which the cursor was created.

                            GLOBAL: Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any program by the connection. The cursor is only implicitly deallocated at disconnect.

                            I think what was happening was that FileMaker was assuming that a cursor that it had created would persist longer than it really was. That would explain why you were getting that original "A cursor with the name FMP_CURSOR does not exist." I think FileMaker was referring to it, but it no longer existed, because the cursor's scope was "local." And by setting the defaut cursor type to "Global," the cursor is persisting longer - as long as you have a connection open to SQL Server. Thus, the errors have gone away.

                            It could be that you were seeing this error because of that setting in SQL Server, or because the database server is local to your machine, or maybe both. Regardless, I don't think it is a FIleMaker bug.

                            Also, I don't think that this will have a negative impact on SQL Server, especially with your configuration.

                            Anyway, I'm glad you were able to work around this mess!

                            -- Tim

                            1 of 1 people found this helpful