September 5, 2007 at 1:11 pm
Hi all,
Generally speaking, we need to call a Web server runnning on Unix. Due to the fact that SQL Server does not directly communicate with Unix, our developers will create a Java executable residing on Windows which will send some sort of message to Unix box. My task is to call this EXE from SQL Server 2005.
So far I have tried two methods, none of them worked; for testing I used Notepad:
1. xp_cmdshell 'c:\windows\notepad.exe' - it keeps "executing query" for almost 1 hour but the notepad did not actually pop-up neither on server box (I checked it via RDC) nor on my local PC.
2. I created a job with a step of Operating system (CmdExec) type and put just Notepad in Command body. After I manually started it the job history shows that it is Executing, but no Notepad is acually running.
Our ultimate goal is to call Web Server, calling notepad.exe was just an intermediate test.
Does anybody have any suggestions, any other ideas for this ?
Thanks in advance.
September 5, 2007 at 2:34 pm
Instead of calling the notepad.exe directly, can you wrap that command in a .vbs file or .bat file and try calling it and see if that works?
September 5, 2007 at 3:24 pm
In wraped it in .bat and .cmd - result is the same as before. vbs is Visual Basic ? Let me try to wrap it to vbs too, but I really doubt that result will be successful.
September 5, 2007 at 3:29 pm
Earlier in one of my projects I have tried calling external components succesfully using sp_OACreate and exposing its methods to communicate with the component. Please see if it is possible to call the web server in a similar way.
September 5, 2007 at 6:23 pm
What are you trying to do with this call? It's not terminating, because notepad isn't self-terminating. It's probably sitting in some hidden state - waiting for something to do or a command to exit?
communicating with a web server is likely better done using CLR, and possibly something like an HTTPWebRequest object or something similar. From SQL server - this would be a WHOLE lot more stable than some free-standing java or javascript executable. Remember - 2K5 was BUILT to play well with .NET 2.0 in particular - so I'd suggest leveraging that instead. Otherwise it's kind of like buying a BMW for the sole purpose of using the ashtray...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 5, 2007 at 9:54 pm
I would agree with Matt, CLR integration is just the ticket for this type of requirement. As for notepad, check your task list with show processes from all users checked - notepad is running in the context of the sql server account or cmd shell proxy and shouldn't be visible to you anyway unless you're logged in to the machine using the service account, and even then you'd have to have the service running with "allow service to interact with the desktop" which can be set under services.
September 6, 2007 at 8:48 am
I actually expected a reply with CLR, I tried to avoid this because I have no experience with it. We have some people who are familiar with C# or VB.net, but it hardly means that we can create a CLR solution. I think it will require big learning curve.
Let me also try sp_OACreate, and I also got an idea to look into Notification services.
The reason why we need to call web-server is we are building some table triggers in our database which upon their firing don't have to update anything in the tables but rather send a sort of notification to applications running on Unix. Since our developers mentioned "notification" I got an idea to check Notification serveces of SQL Server.
September 6, 2007 at 3:25 pm
Is the executable going to be a GUI app or command line only? Your call to notepad.exe most likely worked but never ended because it is not self terminating. Try testing with a command line executable like dir or copy, etc.
As long as the executable being created for you is command line only then you should not have any problem.
September 7, 2007 at 7:31 am
You can execute a shell command to run an executable from xp_CmdShell, but you cannot run something with a user interface. SQL Server runs as a windows service - which actually has it's own desktop that you cannot see. When an action opens a window, the hidden service desktop will either error, or simply hang waiting for someone to click the X in the corner of a window that does not exist. So, don't do that.
Feel free to create an executable that does something - say creates a text file - and then completely exist the program. If you run this from xp_CmdShell, it will work correctly.
Here are the issues you could have. First, if you have an error that causes some kind of dialog to pop up, it may simply hang your xp_CmdShell command indefinately. You will also get no ffedback from this kind of thing. If your developers are writing a program for this, have them write something designed for the command prompt and you can get feedback from xp_Cmdshell.
However, SQL 2005 CLR integration is designed for this. Go pick up a book on it. It is easy to write the CLR stored procedure and have it call a web service. Then, your developers just need to create a web service that does what they want. It is worth learning to do this in your situation.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply