Thanks Gift Peddie,
I have a VB.net threaded application that I use. However, to verify that this is where the problem lies, I made a simplified test program sem_test.exe which can be run in multiple instances.
Usage: sem_test Semaphore_Name (waits | signals ) --
Semaphore_Name is the name of the semaphore
'waits' is the code where the semaphore named Semaphore_Name is created and it waits for single object
'signals' is the code where we open the semaphore named Semaphore_Name, do work, release it, and close the handle
To test:
1. I open 2 cmd windows so that i can run separate instances.
2. cmd1: sem_test semaphore1 waits
cmd1 output is:
semaphore1: Creating Semaphore semaphore1
Waiting on semaphore semaphore1
3. cmd2: sem_test semaphore1 signals
cmd2 ouput is:
Opening Semaphore semaphore1
Releasing Semaphore semaphore1
Successfully Releasing Semaphore semaphore1 Prev Count = 0
Closing Semaphore semaphore1
4. Now when I do step 3, the output in cmd1 adds:
Captured semaphore semaphore1
So it works if we use native windows cmd prompts OUTSIDE of SQL Server. I can also do the same test INSIDE of SQL Server Management Studio, using 2 query tabs (instead of windows cmd windows), through xp_cmdshell. I can see the same output: queryTab1 output = cmd1 output and queryTab2 output = cmd2 output. So it that works too.
Now I test between the cmd1 window and SQL Server xp_cmdshell
1. cmd1: sem_test semaphore1 waits
cmd1 output is:
semaphore1: Creating Semaphore semaphore1
Waiting on semaphore semaphore1
2. xp_cmdshell: exec xp_cmdshell '{path}\sem_test semaphore1 signals'
SQL output is:
Exiting. Could not open semaphore. Semaphore semaphore1 not defined.
3. cmd1 output has not changed and is still waiting for the signal for semaphore1
So that shows it does not work and that the Named Semaphore is NOT system wide like it should be, as stated on Microsoft's website http://msdn.microsoft.com/en-us/library/z6zx288a.aspx. I've been using this architecture on Win Server 2k3 32bit and every windows os I've had before that with no problems. I'm not sure whether it's a Win Server 2k8 thing or a 64bit thing. However, it does not seem to be a 64bit thing because it works perfectly when both instances are run exclusively INSIDE and exclusively OUTSIDE of SQL Server on the 64bit setup.
I appreciate any ideas. Thank you.