Executing SQL Server stored procedures

[LEL]

I'm looking for a way to execute SQLServer stored procedures without requiring a DSN setup on every client machine. We have about 80 clients, with some Mac, some Windows. The solution I'm building is largely driven via stored procedures within SQLServer, using FileMaker as the interface. Currently I'm using the ExecuteSQL script step to execute the stored procedures, but that will require the extra step of ODBC drivers and a DSN on each machine. Using FM Server Advanced (or other means), is there any way to use the server's DSN to execute the procedures? Any other suggestions?

[Dali]

Without DSN you can't really run a stored procedure via Execute SQL. You could however create a Trigger in your database that will run a stored procedure based on a certain criteria. Trigger is connected to an SQL table so you could setup an UPDATE trigger that will monitor a field in the table for a certain value and then execute the stored procedure. You won't be able to get the results of the stored procedure back to filemaker but you could have the stored procedure write to another table that can be shadowed by filemaker. It could be a lot of work if your entire solutions depends on stored procedures.

[LEL]

I tried that route, and got an interesting ODBC error message regarding "dissallowing triggers that update other files". The error only happens when creating a new record from within FileMaker. The trigger works fine from within the MS Query Analyzer. At this point, I have conceded that we will need to set up DSNs on each client workstation.

[Dali]

Not sure what this means. Can you post the exact message and error code. Might be ODBC settings or security settings of the database. Did you try the test using admin account?