Vb exe app Trying to run From SQLServer

[PopTrain]

I am trying to run a short VB app from within Sqlserver.

I have tried the xp_cmdshell and it does not work.

If somebody could tell me where to store the VB app and also how I can run it from within sqlserver. this is what I have tried:

exec master..xp_cmdshell 'c:\Directpage.exe'

I am eventually going to try to schedule this so if anyone knows a better way then let me know. I worked with the DTS a little bit but everytime I schedule it does not work, it only works when I execute it manually.

Thanks

Kyle

 

[SQLSister]

your problem is that the SQL Server Agent doen't have the necessary permissions to run the DTS package, so it works when you run it manually based on your permissions, but he agent is using a differnt login and thus has differnt permissions.

 

[PopTrain]

How do I check and verify this???

 

[sunila7]

Hi,

I think ur code is not working b'cos u r trying to run some program which is not there on the server, xp_cmdshell looks for ur program Directpage.exe in the SQL server's C: and as it is not there u get an error message, so either copy it some directory on SQL server or give use network address on ot the machie where the program is there for eg:-

exec master..xp_cmdshell '\\urmachinename\c\Directpagefolder\Directpage.exe'

Sunil

 

[topher0303]

What is the error you are getting?

 

[SemperFiDownUnda]

make sure the user you are logged into has execute permision on xp_cmdshell. If the account isn't a sysadmin account then make sure the SQL Server Agent proxy account has the appropriate rights to execute the application. And lastly make sure the application is there as stated.

I would suggest not putting the application at root c:\ but a directory that the SQL Server Agent proxy account has execute permission to. Letting SQL Server Agent proxy account have access to your whole server is a VERY bad move.

 

[PopTrain]

When I run the code in query analyzer code being:

exec master..xp_cmdshell '\\Housql01\Applic$\Directpage.exe'

I get the following error:
output
------------------------------------------------------------
The name specified is not recognized as an
internal or external command, operable program or batch file.

When I run a stored procedure with the same line of code from query analyzer I get the same error.

Any help would be appreciated.

can someone explain to me where I can check the permissions for the agent like in the previous responses.

Thanks

Kyle

 

[topher0303]

That appears to be an operating system error indicating it could not find the file you are trying to execute. You are using UNC naming for the server. Is this server different from the SQL Server you are running the sproc on?

If so, the user account running the sql server service must have access to this folder and program. If you are running the sql service under a local system account, it is unable to see other servers. You will have to change the account to a domain account that has access to the program.

An alternative would be to place a copy of the exe on the local server.

Chris.

 

[tlbroadbent]

You can open Services from the control panel or administrative tools on the server to see which account is used to start SQL Server and/or SQL Agent. If the accounts are domain accounts, have the network administrator grant appropriate permissions on the remote server to the accounts. If SQL Server or SQL Agent are running under local system accounts you have no choice but copy the executable to the server so it can be executed.

Once you get permissions figured out, you need to make sure teh VB program doesn't prompt for keyboard input or display any messages or screens. If the program does these things, it should not be executed from SQL Server or the SQL Agent.

You won't need to execute a stored procedure or create a DTS package to schedule execution of the program. Create a job that executes a CmdExec job step. The job step will execute the VB program. This eliminates unnecessary complexity. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm