xp_cmdshell Issue
-
I have an executable that I need to run from a trigger. I'm doing this via xp_cmdshell. The exe is executing, but hangs.
The exe file was built with VB.NET and uses ADO. (ODBC.NET is not an option in this case.) The exe opens connections to SQL Server and another database (Navision) for data manipulation. The exe works fine when executed in the IDE, when executed from a command prompt, and when executed with xp_cmdshell in Query Analyzer. But it hangs when executed from the trigger. I put debug statements in the application and terminated at different places. Everything executed as expected until I tried to open a connection to SQL Server. I got a timeout from this statement. (The connection opens immediately when executed in any mode outside of the trigger.)
A bit more background - the trigger is invoked on Insert. A record is being inserted into the table via a stored procedure. The record insertion is being done inside of a transaction in the stored procedure. (I assumed the the trigger wouldn't fire until the transaction is committed.)
I initially assumed that this was a security context issue. However, SQL Server is running under my account (and I also have local administrator privileges), and the exe is also running under my account. So it wouldn't appear to be a security issue.
Is it possible that the insert trigger is firing prior to the commit tran? If so, might that lead to a conflict? I don't think so, but I'm grasping.
Anyone have any ideas? Any help would be GREATLY appreciated.
I Only Work Here...... -
"Is it possible that the insert trigger is firing prior to the commit tran? "
Yes !
the trigger is launched at command-time ( insert in this case) ! If the trigger fails, the whole transaction fails !
When using xp_cmdshell, it will wait as long as your xp-cmdshell-statement takes !
So when your Navison is down, your sql won't accept anymore inserts !
(because your exe will fail)It is very nice to have all systems hooked up, until there is a "not-normal" situation and one detects all of a sudden, no system at all works because one fails
Consider performing you synchronisation near-time using sql-agent-jobs, ...
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
My 2 cts...
WARNING: We created a trigger a while back that hits an executable. It has been my experience that such a configuration can generate some really wild debugging sessions. Here is a common question, "Why did my query hang?" We had loads of "fun" with database upgrades too...
I don't recommend handicapping database "performance" this way...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply