First of all, this is for Sage 100 ERP (5.00.2.0) and SQL Server 2008 R2
I've been scratching my head. I have a simple code that is triggered at post-write in SO_SalesOrderHeader, i.e. a user hitting "Accept" in Order Entry window to execute.
The code simply creates ADODB connection object, and execute a stored procedure (which pulls data using OpenQuery from MAS and updates the SQL server using Merge statement). Easy peasy. Below is the original code.
Dim ObjConnection
Dim strSalesOrderNo
Dim strSQL
Set objConnection = CreateObject("ADODB.Connection")
strSQL = "EXEC spProc @spOrderNo = '" + strOrderNo + "'"
On Error Resume Next
ObjConnection.Open "DSN=DBname;"
ObjConnection.CommandTimeout = 0
ObjConnection.Execute (strSQL)
ObjConnection.Close
set objConnection = Nothing
While it runs fine, I noticed that this code incurs nearly 1.5 second pause...Normally, clicking Accept button makes everything grayed out instantly and give the control back to user. But it's now noticeably, oddly, long. So I experimented by using 2 separate script files; first one creates a connection object to SQL server and executes the stored proc, another creates Wscript.shell object and triggers the first script file using cscript.exe. Code below is for a trigger, again nothing fancy. I'll omit the execution code since it's pretty much a repeat.
Dim strOrderNo
strFileName = Chr(34) & "\\server\Script_Exec.vbs" & Chr(34)
strOrderNo = ""
retval = oBusobj.getvalue("SalesOrderNo$", strOrderNo)
On Error Resume next
Set WSHShell = CreateObject("WScript.Shell")
WSHShell.Run "cscript " & strFileName & " " & strOrderNo, 0 , false
Set WSHShell = Nothing
On Error Goto 0
Now here are the question.
- Why does this 2-script-file method perform much faster than the original even though the original deals with only one object and the new method has 2 objects?
- Also, can this 2-script-file method in any way cause some of the executed stored proc/statement to be indefinitely running but waiting with OLEDB wait type? (I have 3 sessions that have been on running/waiting status for 3 days now. Killing them only changes the status and keeps them hanging indefinitely)
I hope you can at least give me a hint for the 1st question! Thanks in advance :)
