Jump to content

Execute SQL script step questions


This topic is 2794 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

This topic is 2794 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.