# Add an ACE allowing the service user Start and Stop service rights: $ServiceAcl = Get-ServiceAcl "Standalone SQL Agent" $serviceLogonAccount = $service.StartName # Change to the display name of your SQL Server Express service In order to run it, you need to download the code available at Rohn Edwards’ blog. The following PowerShell script grants the SQL Server service account all the rights it needs.
However, you’ll probably notice that the SQL Server service account does not have sufficient rights to restart the service. You can overcome this limitation using a startup stored procedure that restarts the service:ĮXEC xp_cmdshell 'net stop "Standalone SQL Agent"'ĮXEC xp_cmdshell 'net start "Standalone SQL Agent"'ĮXEC sp_procoption = 'startStandaloneSQLAgent' No connection attempt will be made until Sevice is restarted. In the ERRORLOG file you’ll find a message that resembles to this: Error connecting to SQL Instance. When the service loses the connection to the target SQL Server instance, it won’t restart automatically and it will remain idle until you cycle the service manually. If you don’t have a SQL Server 2008 instance available, you can extract the definition of the stored procedure from the source code at CodePlex. I don’t know why this happens: I copied the definition of the stored procedure from a 2008 instance and it worked fine. In the ERRORLOG file (the one you find in the Standalone SQL Agent directory, not SQL Server’s) you’ll quickly find the reason of the failure: it can’t create the stored procedure sp_help_job_SSA. Easy peasy.Īs far as SQL Server 2012 is concerned, the service fails to start when connected to a 2012 instance. The UAC problem during installation is easy to solve: open an elevated command prompt and run the installer msi.
It’s still a beta version and the project hasn’t been very active lately.However, while I still find it a great piece of software, there are a couple of downsides to take into account: It’s open source and it was started by a person I highly respect.This means that jobs, schedules and the like can be scripted using the same script you would use in the other editions. It uses msdb tables to read job information.My favourite SQL Server Agent replacement to date is Denny Cherry‘s Standalone SQL Agent, for two reasons: This is a known limitation and many people offered alternative solutions to schedule jobs, including windows scheduler, free and commercial third-party applications. As you probably know, SQL Server Express doesn’t ship with SQL Server Agent.
0 kommentar(er)
