Please specify the ODBC driver that you used.
I have tried SQL Server, SQL Server Native Client 10.0, and SQL Server Native Client 11.0.
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.
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?
There is some issue
-- sent from my iPhone4 --
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!
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.
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:
ROLLBACK TRANSACTION BEGIN TRANSACTION
That's it. Does that make any sense at all?
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?
2 of 2 people found this helpful
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?
1 of 1 people found this helpful
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!