6 Replies Latest reply on Oct 12, 2012 10:31 AM by dreed

    Trouble with Execute SQL script step and SQL Server

    dreed

      I've been using filemaker as an alternate admin interface to our LIMS database which is on SQL Server 2008. It's handy for quick searches and edits, and I'd like to be able to use it to delete certain records in the database rather than using the SQL Server Management Studio. ESS works well for most searching, quick editing, and reporting tasks but for some reason I cannot delete records via this interface. I get no error when I try to do this and it appears from Filemaker that the record is gone. But if I update the found set or search for that record again, either in Filemaker or SSMS, it is still there. I further attempted to use the Execute SQL script step to delete a specific record in the database using syntax like the following, (gleaned from inspection of the Import from ODBC dialog which I successfully used to import records from the same DB)

       

      DELETE FROM "RequestDocuments"

      WHERE "RequestDocuments"."RequestDocumentUid" = '99A02BA0-E3D4-4C9E-AFF7-E7092A12F54A'

       

      When I try to run a script that has this SQL in it, I get a dialog that simply says "ODBC Error" with no error code or further explanation.

       

      FYI, the syntax used in SSMS is as follows and my privileges allow me to delete there:

       

      DELETE FROM [Ironwood_LIMS].[dbo].[RequestDocuments]

      WHERE RequestDocumentUid = '99A02BA0-E3D4-4C9E-AFF7-E7092A12F54A'

      GO

       

      So two questions:

       

      1. Why can't ESS delete records in this table? (wondering if it has something to do with triggers that set off record creation in the audit database)

      2. Why can't Execute SQL operate on the database, when Importing from the same DSN works fine? (I've also tried UPDATE queries via Execute SQL with no success, but updating records via ESS works fine)

       

      Thanks,

       

      Dana

        • 1. Re: Trouble with Execute SQL script step and SQL Server
          beverly

          Are you using the same DataSoure Name (via ODBC controlpanel/manager) for ESS and the Execute SQL script step? Do you know what permissions have been set with that DS? Is it SELECT only?

           

          Beverly

          • 2. Re: Trouble with Execute SQL script step and SQL Server
            dreed

            Thanks for the reply Beverly,

             

            I am using the same DSN for ESS, Import From ODBC and Execute SQL. perspectives.  I can't see anywhere in the Microsoft ODBC Administrator to set permissions for the data source.  I just set it to use integrated windows authentication.  In SSMS, the Security for the DB in question shows my domain account to have db_datareader, db_datawriter, and db_ddladmin rights. 

            Maybe there's separate permissions for what ODBC clients can do?  In any case, it's not only SELECT, since via ESS, I can update records.  I can't UPDATE via Execute SQL however, I get the same "ODBC Error".

             

            Dana

            • 3. Re: Trouble with Execute SQL script step and SQL Server
              TimDietrich

              Dana --

               

              Try changing the way that you're sending the DELETE request. For example, instead of this...

               

              DELETE FROM [Ironwood_LIMS].[dbo].[RequestDocuments]

                    WHERE RequestDocumentUid = '99A02BA0-E3D4-4C9E-AFF7-E7092A12F54A'

               

              ... do this...

               

              DELETE FROM [RequestDocuments]

                    WHERE RequestDocumentUid = '99A02BA0-E3D4-4C9E-AFF7-E7092A12F54A'

               

              ... and see what happens.

               

              -- Tim

              • 4. Re: Trouble with Execute SQL script step and SQL Server
                dreed

                Hi Tim,

                 

                I've a number of different combinations of syntax.  Sometimes, if I mess it up completely (like in this example where I put a random character in the middle of the query) I get an error message about the syntax

                error.bmp

                 

                But if I attempt the query as you suggest, either with or without the brackets and with or without double quotes, I get this unhelpful message.

                 

                error2.bmp

                • 5. Re: Trouble with Execute SQL script step and SQL Server
                  LSNOVER

                  Not sure if it may be an applicable issue with Windows, but I recall either Jonathan Monroe or one of the Engineers from FMI saying NOT to use the same ODBC connection for ESS for other SQL mechanisms.  So, I would attempt to setup a specific ODBC connection for use by the Execute SQL Script set.

                   

                   

                  Next, use some other Query tool and login with the exact same credentials you are employing in Filemaker.  See if you can build and execute your SQL successfully there.  You will probably get more detailed error information, and many tools have much better mechanisms for assembling the SQL strings than Filemaker.   Once you have good working syntax, move it over to Filemaker and try again. 

                  • 6. Re: Trouble with Execute SQL script step and SQL Server
                    dreed

                    Hi Everyone,

                     

                    After having some time to muck around, I found that I could delete records using ESS or Execute SQL with no error if there were no triggers set for auditing changes on that table, but for tables employing auditing, I would not be able to delete records via ODBC, only if I used SQL Server Management Studio.

                     

                    The workaround that I found to work was if I used a Stored Procedure to do the deletion.  Then I can delete records in the audited tables using Filemaker's Execute SQL script step to call the stored procedure, passing the recordUid as a parameter.

                     

                    Thanks everyone for the help with this issue.

                     

                    FYI, here is an example of the syntax I got to work

                     

                    "{CALL Ironwood_RequestDocuments_Delete('"&RequestDocuments::RequestDocumentUid&"')}"

                     

                    Where Ironwood_RequestDocuments_Delete is the Stored Procedure, hard coded in the calculation

                     

                    and

                     

                    RequestDocuments::RequestDocumentsUid is the Uid values of the current record I'm viewing in the ESS view.  I'll probably change that to a script parameter so I can pass values from portal records, since this is a child table.

                     

                    so the following is passed, for example:

                     

                    {CALL Ironwood_RequestDocuments_Delete('1B39AB97-0341-4FF1-9C1D-4B5BF39835EC')}

                     

                    and finally, the code of the sProc:

                     

                     

                     

                     

                     

                     

                     

                     

                     

                    USE [Ironwood_LIMS]
                    GO
                    /****** Object:  StoredProcedure [dbo].[Ironwood_RequestDocuments_Delete]    Script Date: 10/12/2012 12:30:05 ******/
                    SET ANSI_NULLS ON
                    GO
                    SET QUOTED_IDENTIFIER ON
                    GO
                    -- =============================================
                    -- Author:  xxxxxxxxxxxxx
                    -- Create date: 12/07/2010
                    -- Description: Delete RequestAssignments Records
                    -- =============================================
                    ALTER PROCEDURE [dbo].[Ironwood_RequestDocuments_Delete]
                    @RequestDocumentUid uniqueidentifier
                    AS

                    BEGIN
                    -- SET NOCOUNT ON added to prevent extra result sets from
                    -- interfering with SELECT statements.
                    SET NOCOUNT ON;

                    DELETE FROM dbo.RequestDocuments
                      WHERE RequestDocumentUid = @RequestDocumentUid
                    END