Execute SQL script step questions

[John.M]

Hello,

I have a couple of questions around trying to get a stored procedure to work using the Execute SQL script step.

1. With that script step you need to specify a DSN and it looks like it's limited to a local user DSN. Is there a way to use the System DSN set up on the server so it doesn't have to be set up on every user computer?

2. Is anybody able to execute a stored procedure just passing parameters with the script step? We haven't had any luck with that or writing data to a SQL table with a stored procedure kicking off once the record is committed. I get an ODBC error with the trigger assigned. Once the trigger is removed, the data can be written to the record without issue.

We are currently setting a flag on the record to be processed with a timer job running on the SQL server querying for the flagged records.

Currently running FMS 13 and FMP 14.

Thanks,

John

[Wim Decorte]

41 minutes ago, John.M said:

1. With that script step you need to specify a DSN and it looks like it's limited to a local user DSN. Is there a way to use the System DSN set up on the server so it doesn't have to be set up on every user computer?

 

No, but if you set it up on the server you can use server-side schedules or Peform-script-on-Server calls to avoid having to replicate the DSNs and install ODBC drivers on the clients.

 

42 minutes ago, John.M said:

2. Is anybody able to execute a stored procedure just passing parameters with the script step? We haven't had any luck with that or writing data to a SQL table with a stored procedure kicking off once the record is committed. I get an ODBC error with the trigger assigned. Once the trigger is removed, the data can be written to the record without issue.

You can't with that script step, it's limited to inserts and updates, not calling stored procedures.

You can use the sqlcmd command line utility to do it though, and use the Send Event script step or use a plugin to execute it.  Downside is that the client needs to have sqlcmd installed, unless you do this through schedules or PSoS on the server.  On server, Send Event is not supported so you'd need a plugin like the free BaseElements plugin to execute sqlcmd.