locked
Job problem : The process could not be created for step 1 ... RRS feed

  • Question

  • Hi

    i have some t-sql commands which used xp_cmdShell proc. it works correctly in query window, but when i create a job to run this command periodically and start job to test operation, i'm facing this error :

    Executed as user: WORKGROUP\SERVER-PC$. The process could not be created for step 1 of job 0xEE190A45B7C8764595062A575858EC1E (reason: 2).  The step failed.

    Also i changed all objects to MyDatabase.dbo.MyObject but the problem was not solved.

    where is the problem and how to solve it ?

    I'm using sql server 2008 in workgroup and win 7.


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Saturday, December 3, 2011 8:35 PM

Answers

  • Proxy user account is usually the best practice to dispense sysadmin users from job ownership and assign a different job owner with less privileges according to best Microsoft practices.

    However for that case , we need first to compromise the reasons of this failure :

    So have you assigned sufficient DB privileges for that network Service account WORKGROUP\SERVER-PC$  ( let us assume sysadmin as test) + also sufficient ACL permissions on the relevant  (let us assume local admin as test)).

     

    Let me know if  It worked with you


    Think more deeply of performance terms
    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:33 PM
    Saturday, December 3, 2011 10:30 PM
  • HI Hameed !

    You need to run your job step commad as Proxy Account. Below are the links that describes you why you want to execute is as Proxy User account;

    http://www.databasejournal.com/features/mssql/article.php/3789881/Proxy-Accounts-in-SQL-Server.htm

    http://support.microsoft.com/kb/890775

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:33 PM
    Saturday, December 3, 2011 9:18 PM
    Answerer
  • Here is what I suggest:

    1. Make the script into a stored procedure

    2. Permission the sproc (including for the SQL Agent logon account) and test it

    3. Set it up as a job and try it

    If still fails, add proxy account as suggested above.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Proposed as answer by Hasham NiazEditor Saturday, December 3, 2011 10:45 PM
    • Edited by Kalman Toth Saturday, December 3, 2011 11:10 PM
    • Unproposed as answer by Hamed_1983 Sunday, December 4, 2011 4:55 AM
    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:34 PM
    Saturday, December 3, 2011 10:39 PM

  • An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '1326'.

    Running "NET HELPMSG 1326" from the command prompt tells us: Logon failure: unknown user name or bad password.

    And this is not really surprising, since you say

    EXEC sp_xp_cmdshell_proxy_account 'PowerDomain\PowerUser', '1';

    Is the password for PowerDomain\PowerUser really a single digit? You should supply the Windows password here.

    But I think to sort out this mess, we need to know more about your environment. Are you doing this at home? Or are you in a larger corporation where you have domain and there is an IT group that imposes group policy on your machine?

    You say that you run SQL Server Agent and SQL Server under Local System. This can cause some problems, and it is usually better to use a regular Windows account, preferrably one that is dedicated for the task. (If you use your own account, you get a hassle when you change your password.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:34 PM
    Sunday, December 4, 2011 11:45 AM
  • If you have a workgroup, and you are not part of your domin, I suggest that you do this:

    1) Right-click My Computer and select Manage. Find the Users and Groups node. Add a user sql, and specify a reasonably strong password for this account. Clear the box "must change password on next logon", and check "Password never expires". Do not mark the account as member of Administrators or any other group. Close the diaglog.

    2) Open SQL Server Configuration Manager. Find the node SQL Server Services. Right-click the SQL Server service. Select Properties. It will open on the Log On tab. Change to "This account". Specify sql and the password you selected in the previous step.

    3) Repeat for SQL Server Agent.

    4) Restart the services. (Right-click and select Restart.)

    5) Try running your job.

    For future assistence it would be helpful if you specified which version you have of SQL Server and which Windows version you have.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:34 PM
    Sunday, December 4, 2011 4:14 PM
    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:34 PM
    Sunday, December 4, 2011 6:44 PM
  • So how you have a problems with Windows Task Scheduler? Now, pray tell, that must be beyond the scope for this forum. At least, I don't have a clue. I would suggest Run as Adminstrator, but I seem to recall that I get a UAC prompt when I start Task Scheduler.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:36 PM
    Sunday, December 11, 2011 11:02 PM

All replies

  • Do you use Proxy accoount ? check the permissions of SQL Server account or Proxy account.
    • Proposed as answer by Hasham NiazEditor Saturday, December 3, 2011 8:53 PM
    • Unproposed as answer by Hamed_1983 Saturday, December 3, 2011 8:55 PM
    Saturday, December 3, 2011 8:50 PM
  • Do you use Proxy accoount ? check the permissions of SQL Server account or Proxy account.
    No, What's proxy account ? id on't have any additional security on that machine.
    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Saturday, December 3, 2011 8:54 PM
  • HI Hameed !

    Can you please share your job step code with us.

    Thanks, Hasham

    Hi

    Do you mean my main commands that accomplish my goal ?


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    • Edited by Hamed_1983 Saturday, December 3, 2011 8:56 PM
    Saturday, December 3, 2011 8:56 PM
  • Yeh.. Please post your T-SQL code which you are running under that job step.

    Thanks, Hasham

    Saturday, December 3, 2011 8:59 PM
    Answerer
  • Yeh.. Please post your T-SQL code which you are running under that job step.

    Thanks, Hasham

    It's this :

     

    declare @cntReception int, @sumCash money
    declare @msgReception varchar(50), @msgCash varchar(50), @totalMsg varchar(100)
    declare @today char(10)
    set @today = (select MedicalCenter.dbo.MiladiTOShamsi(getdate()))
    select @cntReception = COUNT(r.ReceptionRowID) from MedicalCenter.dbo.Reception r where r.ReceptionDate = @today
    select @sumCash = SUM(c.Hesab) from MedicalCenter.dbo.Cash c where SubmitDate = @today
    set @msgReception = 'Today Reception is : ' + cast(@cntReception as varchar(10)) + ','
    set @msgCash = ' Today Cash Sum is : ' + cast(@sumCash as varchar(20)) + '.'
    set @totalMsg = '"'+@msgReception + @msgCash+'"'
    exec sp_SendBirthDateMsg 'D:\SmartMMS\Houshmand.WebSms\Houshmand.SmsWebSender','30005937729494','myUser','myPass',@totalMsg,'myMobile'


     


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

    • Edited by Hamed_1983 Saturday, December 3, 2011 9:11 PM
    Saturday, December 3, 2011 9:09 PM
  • HI Hameed !

    You need to run your job step commad as Proxy Account. Below are the links that describes you why you want to execute is as Proxy User account;

    http://www.databasejournal.com/features/mssql/article.php/3789881/Proxy-Accounts-in-SQL-Server.htm

    http://support.microsoft.com/kb/890775

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:33 PM
    Saturday, December 3, 2011 9:18 PM
    Answerer
  • Proxy user account is usually the best practice to dispense sysadmin users from job ownership and assign a different job owner with less privileges according to best Microsoft practices.

    However for that case , we need first to compromise the reasons of this failure :

    So have you assigned sufficient DB privileges for that network Service account WORKGROUP\SERVER-PC$  ( let us assume sysadmin as test) + also sufficient ACL permissions on the relevant  (let us assume local admin as test)).

     

    Let me know if  It worked with you


    Think more deeply of performance terms
    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:33 PM
    Saturday, December 3, 2011 10:30 PM
  • Here is what I suggest:

    1. Make the script into a stored procedure

    2. Permission the sproc (including for the SQL Agent logon account) and test it

    3. Set it up as a job and try it

    If still fails, add proxy account as suggested above.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Proposed as answer by Hasham NiazEditor Saturday, December 3, 2011 10:45 PM
    • Edited by Kalman Toth Saturday, December 3, 2011 11:10 PM
    • Unproposed as answer by Hamed_1983 Sunday, December 4, 2011 4:55 AM
    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:34 PM
    Saturday, December 3, 2011 10:39 PM
  • Here is what I suggest:

    1. Make the script into a stored procedure

    2. Permission the sproc (including for the SQL Agent logon account) and test it

    3. Set it up as a job and try it

    If still fails, add proxy account as suggested above.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

    I don't know how to use these commands, however i login to sql server as 'sa' but i don't know why this job use 'WORKGROUP\SERVER-PC$' account.

    also i've used this command to create proxy command :

    USE master 
    GO
    -- Create a test login called testuser
    CREATE LOGIN testuser WITH PASSWORD='1'
    -- Create a proxy credential for xp_cmdshell.
    EXEC sp_xp_cmdshell_proxy_account 'PowerDomain\PowerUser', '1';
    -- Grant database access to the SQL Server login account that you want to provide access.
    EXEC sp_grantdbaccess 'testuser'
    -- Grant execute permission on xp_cmdshell to the SQL Server login account. 
    GRANT exec ON sys.xp_cmdshell TO [testuser] 
    GO
    


    but i'm facing this error :

    Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1

    An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '1326'.


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Sunday, December 4, 2011 4:58 AM
  • I don't know how to use these commands, however i login to sql server as 'sa' but i don't know why this job use 'WORKGROUP\SERVER-PC$' account.

     WORKGROUP\SERVER-PC$ is likely the SQL Server Agent Logon account. Here is how you make it into a stored procedure:  

    CREATE PROC sprocBirthDayMsg
    AS
    BEGIN
    declare @cntReception int, @sumCash money
    declare @msgReception varchar(50), @msgCash varchar(50), @totalMsg varchar(100)
    declare @today char(10)
    set @today = (select MedicalCenter.dbo.MiladiTOShamsi(getdate()))
    select @cntReception = COUNT(r.ReceptionRowID) from MedicalCenter.dbo.Reception r where r.ReceptionDate = @today
    select @sumCash = SUM(c.Hesab) from MedicalCenter.dbo.Cash c where SubmitDate = @today
    set @msgReception = 'Today Reception is : ' + cast(@cntReception as varchar(10)) + ','
    set @msgCash = ' Today Cash Sum is : ' + cast(@sumCash as varchar(20)) + '.'
    set @totalMsg = '"'+@msgReception + @msgCash+'"'
    exec sp_SendBirthDateMsg 'D:\SmartMMS\Houshmand.WebSms\Houshmand.SmsWebSender','30005937729494','myUser','myPass',@totalMsg,'myMobile' 
    END
    
    Find it in SSMS Object Explorer, right click, properties and permission it.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, December 4, 2011 5:31 AM
  • I don't know how to use these commands, however i login to sql server as 'sa' but i don't know why this job use 'WORKGROUP\SERVER-PC$' account.

     WORKGROUP\SERVER-PC$ is likely the SQL Server Agent Logon account. Here is how you make it into a stored procedure:  

    CREATE PROC sprocBirthDayMsg
    AS
    BEGIN
    declare @cntReception int, @sumCash money
    declare @msgReception varchar(50), @msgCash varchar(50), @totalMsg varchar(100)
    declare @today char(10)
    set @today = (select MedicalCenter.dbo.MiladiTOShamsi(getdate()))
    select @cntReception = COUNT(r.ReceptionRowID) from MedicalCenter.dbo.Reception r where r.ReceptionDate = @today
    select @sumCash = SUM(c.Hesab) from MedicalCenter.dbo.Cash c where SubmitDate = @today
    set @msgReception = 'Today Reception is : ' + cast(@cntReception as varchar(10)) + ','
    set @msgCash = ' Today Cash Sum is : ' + cast(@sumCash as varchar(20)) + '.'
    set @totalMsg = '"'+@msgReception + @msgCash+'"'
    exec sp_SendBirthDateMsg 'D:\SmartMMS\Houshmand.WebSms\Houshmand.SmsWebSender','30005937729494','myUser','myPass',@totalMsg,'myMobile' 
    END
    
    Find it in SSMS Object Explorer, right click, properties and permission it.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

    I don't have this user in my sql server Logins!

    1. i have only these users in my normal sql server logins :

    sa

    SERVER-PC\Ghasedak

    testuser

    2. my sql agent run under 'Local System' account

    3. i've login to my sql server as sa

    Would u plz describe step-by-step ?


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    • Proposed as answer by Vinay Valeti Sunday, December 4, 2011 7:06 AM
    • Unproposed as answer by Vinay Valeti Sunday, December 4, 2011 7:06 AM
    Sunday, December 4, 2011 5:39 AM
  • Hi,

    Can you please run the following two queries and let us know if you find any rows.

    select * from msdb.dbo.sysproxies

    select * from master.sys.credentials

     

    Can you please confirm that SQL Agent Logon account in SQL server Configuration Manager is Local System and it is not WORKGROUP\SERVER-PC$


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    Sunday, December 4, 2011 7:12 AM
  • Hi,

    Can you please run the following two queries and let us know if you find any rows.

    select * from msdb.dbo.sysproxies

    select * from master.sys.credentials

     

    Can you please confirm that SQL Agent Logon account in SQL server Configuration Manager is Local System and it is not WORKGROUP\SERVER-PC$


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer


    Hi

    select * from msdb.dbo.sysproxies

    this query have not any row.

     

    select

     

    * from master.sys.credentials

    also this query have not any row.

     

    My sql server agent run under Local System account. however, i restart my agent service and re run my job, but i'm facing the same problem.


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Sunday, December 4, 2011 8:02 AM

  • An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '1326'.

    Running "NET HELPMSG 1326" from the command prompt tells us: Logon failure: unknown user name or bad password.

    And this is not really surprising, since you say

    EXEC sp_xp_cmdshell_proxy_account 'PowerDomain\PowerUser', '1';

    Is the password for PowerDomain\PowerUser really a single digit? You should supply the Windows password here.

    But I think to sort out this mess, we need to know more about your environment. Are you doing this at home? Or are you in a larger corporation where you have domain and there is an IT group that imposes group policy on your machine?

    You say that you run SQL Server Agent and SQL Server under Local System. This can cause some problems, and it is usually better to use a regular Windows account, preferrably one that is dedicated for the task. (If you use your own account, you get a hassle when you change your password.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:34 PM
    Sunday, December 4, 2011 11:45 AM
  • I don't know what's 'PowerDomain\PowerUser', however i don't have this account in my windows or sql server, in my widnows i have a user named 'Server-PC\Ghasedak' which have administrator privilage and i connect to sql server via 'sa' account.
    I don't have domain (it's workgroup)
    another problem (maybe this clarify my problem) : i can not login to my sql server via windows athentication.

    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Sunday, December 4, 2011 12:01 PM
  • Try to use proxy account , it can be setup under "Server properties">Security ,specify the account that has enough permissions.

    Sunday, December 4, 2011 12:05 PM
  • Try to use proxy account , it can be setup under "Server properties">Security ,specify the account that has enough permissions.

    What i type in Proxy account field ?

    can u plz tell me step-by-step guidance according to my problem ?

    thanks in advance


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Sunday, December 4, 2011 12:10 PM
  • Hi,

    Hameed,Before we can use a proxy account.Can you please do the Quick test.

    Can you please add NT AUTHORITY\SYSTEM as a sysadmin on the SQL Server and try running the Job?


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    Sunday, December 4, 2011 1:06 PM
  • Hi,

    Hameed,Before we can use a proxy account.Can you please do the Quick test.

    Can you please add NT AUTHORITY\SYSTEM as a sysadmin on the SQL Server and try running the Job?


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    NT AUTHORITY\SYSTEM has been add to Sql Server Logins and have 'sysadmin' role.
    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Sunday, December 4, 2011 2:44 PM
  • If you have a workgroup, and you are not part of your domin, I suggest that you do this:

    1) Right-click My Computer and select Manage. Find the Users and Groups node. Add a user sql, and specify a reasonably strong password for this account. Clear the box "must change password on next logon", and check "Password never expires". Do not mark the account as member of Administrators or any other group. Close the diaglog.

    2) Open SQL Server Configuration Manager. Find the node SQL Server Services. Right-click the SQL Server service. Select Properties. It will open on the Log On tab. Change to "This account". Specify sql and the password you selected in the previous step.

    3) Repeat for SQL Server Agent.

    4) Restart the services. (Right-click and select Restart.)

    5) Try running your job.

    For future assistence it would be helpful if you specified which version you have of SQL Server and which Windows version you have.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:34 PM
    Sunday, December 4, 2011 4:14 PM
  • Do you still get the error when the NT AUTHORITY\SYSTEM is sysadmin?
    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    Sunday, December 4, 2011 4:52 PM
  • Hi

    i follow these steps :

    1. The current windows account (Server-PC\Ghasedak) is member of administrators

    2. change sql server agent service account from 'Local System' to 'Server-PC\Ghasedak' and restart sql agent service

    3. Reconnect to sql server and run my job

    but i'm facing the same error for 'Server-PC\Ghasedak' :

    Executed as user: Server-PC\Ghasedak. The process could not be created for step 1 of job 

    Also i'm facing the same problem when NT AUTHORITY\SYSTEM is sysadmin.

    The OS is win 7 32 bit and sql server 2008


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    • Edited by Hamed_1983 Sunday, December 4, 2011 6:03 PM
    Sunday, December 4, 2011 5:50 PM
  • Executed as user: Server-PC\Ghasedak. The process could not be created for step 1 of job 

    And it is still saying (reason: 2)?

    Could you post screen shots of the two property windows for the job step? That is, both General and Advanced?

    What happens if you start SSMS by right-clicking it and select "Run as Administrator?"


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 4, 2011 6:20 PM
  • Start SQL Management studio,connect to your Database serve, in Object explorer right click your server name,then "Properties", then "Security"

    then check "Enable Server proxy account" in "Proxy account" field specify local server or domain accout , type the password for account also in next field.

     

    This are steps to set proxy account.

     

    Sunday, December 4, 2011 6:25 PM
  • Start SQL Management studio,connect to your Database serve, in Object explorer right click your server name,then "Properties", then "Security"

    then check "Enable Server proxy account" in "Proxy account" field specify local server or domain accout , type the password for account also in next field.

     

    This are steps to set proxy account.

     

    I found and do this steps, but i'm facing the same problem!
    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Sunday, December 4, 2011 6:33 PM
    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:34 PM
    Sunday, December 4, 2011 6:44 PM
  • you can see this article

    http://www.databasejournal.com/features/mssql/article.php/3789881/Proxy-Accounts-in-SQL-Server.htm

    Thanks Ivan

    i red this article and test my development machine. i will test out in production and post the result here again.

    thanks for your help.


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Monday, December 5, 2011 12:37 PM
  • Hi again
    i tested almost everything, but doesn't works!
    i'v created a new login named 'testuser', then give sysadmin permisson to it and run this code :
    -- Create a credential containing the domain account PowerDomain\PowerUser and its password
    CREATE CREDENTIAL testuser WITH IDENTITY = N'Server-pc\testuser', SECRET = N'1'
    GO
    USE [msdb]
    GO
    -- Create a new proxy called SSISProxy and assign the PowerUser credentail to it
    EXEC msdb.dbo.sp_add_proxy @proxy_name=N'CmdShellProxy',@credential_name=N'testuser',@enabled=1
    -- Grant SSISProxy access to the "SSIS package execution" subsystem
    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'CmdShellProxy', @subsystem_id=3
    -- Grant the login testUser the permissions to use SSISProxy
    EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'testUser', @proxy_name=N'CmdShellProxy'
    GO
    and then edit my job step to use this proxy account, but i'm facing the same problem (with reason 2)
    also i create new user in windows and set it as member of administrators and then change sql server agent log on account to this new account, then restart agent service and rerun my job, but still i'm facing the same problem.
    can anybody help me ?

    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Thursday, December 8, 2011 5:49 AM
  • So I have a nagging feeling that this business with credentials and proxy user is a false lead, but I like to stress that I don't know for sure.

    I think it would help us a lot, if we could find out this "reason 2" means. Again, I don't know for sure, but my standard assumption when I see an number like that in something which involves OS interaction, is that the answer can be found by running NET HELPMSG on the code from a command line window. Except that 2 is a code that I know by heart: The system cannot find the file specified.

    I have mentioned this previously in the thread, because I have no idea what file that would be. At least not with the T-SQL you posted. And if it really is a T-SQL job.

    That's why I asked you to post screen shots of the Step details, both General and Advanced. Any chance you could do this?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 8, 2011 8:57 AM
  • I think that the only place that can execute external command is  sp_SendBirthDateMsg ,

    what exactly this procedure do ?

     

    try to execute it in SSMS and see more detailed error or post its scripts?

    Friday, December 9, 2011 10:55 AM
  • I think that the only place that can execute external command is  sp_SendBirthDateMsg ,

    what exactly this procedure do ?

     

    try to execute it in SSMS and see more detailed error or post its scripts?

    Hi, Of course!

    Here is sp_SendBirthDateMsg proc commands :

    USE [MedicalCenter]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_SendBirthDateMsg]    Script Date: 12/09/2011 14:48:58 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER proc [dbo].[sp_SendBirthDateMsg](
    @exePath nvarchar(1000),
    @smsNumber varchar(20),
    @user nvarchar(50),
    @pass nvarchar(50),
    @msg nvarchar(500),
    @mobiles varchar(max))
    as
    begin
    declare @cmd nvarchar(1000)
    set @cmd = @exePath+' '+@smsNumber+' '+@user+' '+@pass+' '+@msg+' '+@mobiles
    exec master..xp_cmdshell @cmd
    end
    


    and here is the code which execute from my job step :

    declare @cntReception int, @sumCash money
    declare @msgReception varchar(50), @msgCash varchar(50), @totalMsg varchar(100)
    declare @today char(10)
    set @today = (select MedicalCenter.dbo.MiladiTOShamsi(getdate()))
    select @cntReception = COUNT(r.ReceptionRowID) from MedicalCenter.dbo.Reception r where r.ReceptionDate = @today
    select @sumCash = SUM(c.Hesab) from MedicalCenter.dbo.Cash c where SubmitDate = @today
    set @msgReception = 'Today Reception is : ' + cast(@cntReception as varchar(10)) + ','
    set @msgCash = ' Today Cash Sum is : ' + cast(@sumCash as varchar(20)) + '.'
    set @totalMsg = '"'+@msgReception + @msgCash+'"'
    exec MedicalCenter.dbo.sp_SendBirthDateMsg 'D:\SmartMMS\Houshmand.WebSms\Houshmand.SmsWebSender','mySmsServerNumber','mySmsServerUser','mySmsServerPass',@totalMsg,'mobileNumbers'
    

    this query works correctly in query windows, but i don't know why i'm facing this problem when i run it from job!


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Friday, December 9, 2011 11:24 AM
  • I can see xp_cmdshell ending in tears because it cannot find the file.

    However, this would produce a result set, not an error in SQL, and thus the job would fail with an error.

    But what happens if you rip out all the code, and replace it with "PRINT 12"?

    I mean, we could just as well test that, so we can narrow down the possibilities.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 9, 2011 1:16 PM
  • I can see xp_cmdshell ending in tears because it cannot find the file.

    However, this would produce a result set, not an error in SQL, and thus the job would fail with an error.

    But what happens if you rip out all the code, and replace it with "PRINT 12"?

    I mean, we could just as well test that, so we can narrow down the possibilities.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    For test, i've created new job to execute this command :

    print '12'
    


    and it works correctly.

    Note : before this, i've created a maintenance plan to backup database every night and also it works correctly.


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Friday, December 9, 2011 1:29 PM
  • For test, i've created new job to execute this command :


    No, not a new job. The current job. Replace the code you have now with "PRINT 12" and tell us how it went.

    Even better if you can attach screen shots of the two property pages for the job, but the feature to attach images in the forums may currectly be disabled. (But you could put them elsewhere and post a link.)

    Please bear in mind that in helping you to troubleshoot your problem, you are a vital person. It's only you that can give us the information you need, so it is really important that you follow our instructions.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 10, 2011 9:45 PM
  • For test, i've created new job to execute this command :


    No, not a new job. The current job. Replace the code you have now with "PRINT 12" and tell us how it went.

    Even better if you can attach screen shots of the two property pages for the job, but the feature to attach images in the forums may currectly be disabled. (But you could put them elsewhere and post a link.)

    Please bear in mind that in helping you to troubleshoot your problem, you are a vital person. It's only you that can give us the information you need, so it is really important that you follow our instructions.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Hi

    Do you mean i use my current job (with CmdShellExe susbSystem) and then execute this command ?


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Saturday, December 10, 2011 10:18 PM

  • Do you mean i use my current job (with CmdShellExe susbSystem) and then execute this command ?

    Wait, what did you say now?

    Yes, I meant your current job. But if you say that Type dropdown on the General page for the job reads "Operating System (CmdExec)" we already have the answer. The job type should be "Transact-SQL script (T-SQL)".

    Maybe I'm misunderstanding you, but I've asked you a couple of times to post screen shots. That could help a lot to sort out misunderstandings, so can you pleeeeeease post these screen shots?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 10, 2011 11:14 PM

  • Do you mean i use my current job (with CmdShellExe susbSystem) and then execute this command ?

    Wait, what did you say now?

    Yes, I meant your current job. But if you say that Type dropdown on the General page for the job reads "Operating System (CmdExec)" we already have the answer. The job type should be "Transact-SQL script (T-SQL)".

    Maybe I'm misunderstanding you, but I've asked you a couple of times to post screen shots. That could help a lot to sort out misunderstandings, so can you pleeeeeease post these screen shots?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Hi gain

    tonight, i went to my customer medical center to take screenshot of my job, before that i'm facing a new problem which i think my main problem is related to windows security.

    at present, i solved my problem via windows task scheduler (instead of sql server job), last night i've set enable history for windows task scheduler, tonight i see some history of my task scheduler which i created last night, it display correctly. to get latest history, i've clicked 'Refresh' button, but after multiple time clicking of the 'Refresh' button, i'm facing an error which :

    You do not have permisson to see this .....

    and i could not see my task scheduler's history any more!!! whereas i logged in to windows as 'Server-PC\Ghasedak' which is member of 'Administrators' role.

    what about u ?


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Sunday, December 11, 2011 8:55 PM
  • So how you have a problems with Windows Task Scheduler? Now, pray tell, that must be beyond the scope for this forum. At least, I don't have a clue. I would suggest Run as Adminstrator, but I seem to recall that I get a UAC prompt when I start Task Scheduler.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Friday, December 16, 2011 1:36 PM
    Sunday, December 11, 2011 11:02 PM