Stored Procedure Executing In Wrong Database

We ran into an odd issue recently that I'd like to share today.

An ISV reported that in their customer was getting an error when the ISV application attempted to execute a stored procedure.

[SQL Server] Could not find stored procedure DYNAMICS.dbo.SomeStoredProcedure

Easy you say - the proc doesn't exist.  Just create it and you'll be fine.

Good advice and most often the correct solution.  However in this specific situation the problem is that the procedure does exist.  It is in the COMPANY database and not the DYNAMICS database.  Another thing to note that this ISV has many customer installations and this was the first time the issue has revealed itself.  Then the real issue would seem to be the procedure should execute in the company context and instead it is trying to execute in the DYNAMICS database. 

Now why would that be?

The place to start is to understand what determines which database context a stored procedure executes in.

The series of the Dexterity of the stored procedure prototype is typically what determines what database context the procedure executes in.  If the developer sets the series as System, it will execute in the DYNAMICS database.  The series such as Purchasing, Sales, Company, etc will cause the procedure to execute in the company database.

Checking with the ISV, the prototype of the SomeStoredProcedure was set in the Purchasing series. So that is the correct setting for what the ISV requires and is OK.

What next then?

When a stored procedure is to be executed, the Dynamics procedure - SQLScriptPath - is executed by the runtime.  This procedure is what sets the data path output variable in that script to the correct context and what database the Dexterity runtime executes the proc.

It is true as I noted above that the context is typically determined by the series of the prototype procedure - this is likely 99.9% of the time.  However in the Dynamics dictionary, the global variable SQLScriptLocation is used to override this default.  Why would you want to do this?  About the only reason I can think of would that you want to execute a stored procedure in the context of a different company rather than the current company.  For example, the SQL Maintenance window does this to run the smBindTableDefaults proc in the selected company context.  If the value is set, use that value as the database context - otherwise default as previously noted.

Checking with the ISV, they were not setting this variable and weren't even aware of its use.

The last item to check would be a script.log to see if an application was triggering on the SQLScriptPath global procedure.  Because the data path for execution is an out parameter, it is possible that an application could override this path regardless of what the application indended to use.  In this case, no triggers were observed running after SQLScriptPath procedure so this wasn't the problem.

So what does that leave?  Because the series was correct and there were no triggers on the procedure, the only possible way this could be happening was the SQLScriptLocation variable was being set by some application.  That this was happening was confirmed in runtime by using the Debug | Expressions window and checking the value of SQLScriptLocation of globals - the result was DYNAMICS.

The ISV double checked and it wasn't their application.  That being the case, it had to be another application.  To determine which one it was, the only way was to remove the application from the Dynamics.set file one by one and test the value of SQLScriptLocation of globals.  After removing a certain customization, the culprit was determined.

The developer of that application was contacted by the ISV to get a fix (either change the series to System or clear the variable after the stored procedure was called) and the issue was resolved (well after the developer releases a new version with this fixed).

Why did that developer code their application this way?  My guess is that they created the stored procedure and prototype and found that it didn't execute in the DYNAMICS database as expected.  My guess is they searched knowledgebase and found KB 867407 which talks about how to execute stored procedures in a different database.  The KB does say to clear the variable however it isn't strongly worded and doesn't detail the ramifications of not doing this.  We'll have to get it updated to provide a more explicit warning in the KB.

Best Regards,
Patrick Roth
Developer Support