Will be cross-posting here and on FME community. But this may be more of an ESRIish question.
I have an FME process that uses the SPROC's for versioned editing with the .evw views inside an FME SQL Executor...
-- Change to the FME version
EXEC sde.set_current_version 'myFMEversion';
EXEC sde.edit_version 'myFMEversion', 1;
-- DO MY THING HERE
EXEC sde.edit_version 'myFMEversion', 2;
This has been working great in one database and running in FME Server under a service account.
Now, when I try and use it in another database, after creating a 'Public' version called 'myFMEversion', I can run it in FME Desktop successfully where I'm Admin on that DB. But when I run it in server which uses the service account, it fails with this error:
360 | Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a
malformed statement. Query Text `-- Change to the FME version
361 | EXEC dbo.set_current_version 'myFMEversion''. Provider error `(-2147217900)
Version myFMEversion not found.'
362 | A fatal error has occurred. Check the logfile above for details
'Version Not Found'? -- So it looks like the service account hasn't been granted the proper SQL privileges to use the stored procedures in my new DB. But what privileges are needed? Old and new DB's both have the version as 'Public' and owned by 'DBO'.
If you open a support call with the central issue asking what permissions are needed for SDE stored procedures in SQL Server you will get the response you need. FME is a red herring as its only passing the statements through but it will deter Esri owning the issue. Server team at Safe are a good resource too.
Makes a lot of sense. Thanks for the feedback!