I have a classic client side customization that calls a SQL Stored Procedure in an external database, triggered by button click. This has been working for years but has been timing out in some scenarios lately. It’s somewhat heavy duty and ~30 seconds is a reasonable execution time in my opinion.
I’m of course looking to tune and improve performance on that stored proc, but also looking into increasing the timeout settings. Haven’t done much research on where those settings live / am I playing with fire by trying to adjust them…? Figured I’d start by asking the experts here! Below is the customization code for reference:
To be clear, this proc is running in an external database, the trigger needs to be an Epicor user doing something. How else would you fire the proc if not using code like this?
Wip up a minimal API to call your stored procedure on that machine. I think @SteveFossey did this a bit ago. The nice thing is that you can initiate that with a REST call within a BPM or a Kinetic screen too. This also removes the need of storing the database location and credentials as you can check authorization and point to a different database (test, production or migration) in one place - the API.
What @Mark_Wonsil said! Abstraction is your friend here; entangling your client side customization with a back end SQL call is just a recipe for my secondary DB is down and now I can’t open Customer Entry randomly.
Or I copied Production to Test and forgot to update the secondary database so now I’m updating production records from Test and oops…
But I understand there are needs and we live in a “real world” where best practice isn’t always practical so I’m not gonna give you a hard time about it I am glad it isn’t an stored procedure that generates Invoice Records using SQL and bypassing all BOs… like some others have done
All I did was research, noisily - I never actually did this.
I use powershell to run SQL, can’t remember if I actually used it to trigger SPs anywhere. Currently I have SPs updating external DBs using SQL jobs. I do have a few things like BPMs using streamwrite and text files pretending thereby to be databases…
But honestly more recently I’ve used UD tables, BAQs and REST to call BAQSvc with better results than anything. Plus .NET SQLClient seems to go nuckin futz on me
I’ve shown people how to use it via reflection, but I don’t really like to unless they are trying to
keep some legacy code running while they figure out how to transition to something else.
If it runs under two minutes, you could try setting command.CommandTimeout = 120. But like other have mentioned this is not an ideal code pattern, using web service calls may be safer in the long run
Thanks for the ideas and comments everyone. Just confirming that setting the timeout in code worked splendidly.
I do share @SteveFossey 's sentiment from the other thread that whipping up an API from SQL is a whole lot of (unfamiliar to me) work that adds a bunch of avoidable layers/objects to our code base…, but as always @Mark_Wonsil and @josecgomez 's advice is tremendously appreciated.
And regarding this,
I store all that in User Codes so at least it’s not in code, but it does bother me to store creds there even if the account has minimal permissions and I’m the only one who can see it… My plan for the next iteration of making this better is to move this from client customization to a Function running on the server so I can use Windows authentication more easily instead of SQL login username password…
It appears that you are opening a connection from the client. Depending on number of clients using this function, you might have extra connections to SQL Server. Despite all the claim from Microsoft, database has to manage the connection pools and execution plans. In addition, when you move to cloud, it could become a problem as well.
Any case, as others pointed out, it’s prudent to move it to an API wrapper.
IMO
I am glad it isn’t an stored procedure that generates Invoice Records using SQL and bypassing all BOs… like some others have done 
