Tag Info

New answers tagged

2

It would help to know which version of SQL Server you are using. You may get some information from the history of the job step itself. In SSMS, under SQL Server Agent, find the job. Right click the job and choose View History. Each entry in the Log File Viewer is one job run, so expand a failed run by clicking the [+] sign next to it. Highlight the ...


1

The other alternative would be to use operating system command and give powershell.exe c:/path/script.ps1 as command and save your script in script.ps1. This will ensure using OS powershell instead of using provider.


0

Starting an Agent job from a user database is common, and the best way to accomplish it is to use code signing. Books Online describes the concept here [http://msdn.microsoft.com/en-us/library/ms188304.aspx]. In your application database: Create a certificate Use ADD SIGNATURE to sign the stored procedure you wish to call sp_start_job Use ALTER ...


0

Your code for the query is using two servers, identified by IP address as shown below. Where is the server [123.456.7.890]? And where is the server [098.765.4.321]? My guess is that [098.765.4.321] is the [RemoteServer] and [123.456.7.890] is the [LocalServer]. First of all, the slowness of the code is not SQL Agent's fault. Code run from SQL Agent is ...


0

If you are using Windows Authentication in the linked server setup it will not work using the SQL Server agent even if you have Kerberos correctly setup. You can configure the linked server to use SQL authentication for those connections but I would recommend to create a SSIS package for this as the authentication will be easier to configure. Can you ...


1

I know this is an older post, and I have personally had the same problem. Managing multiple SQL agent jobs across multiple instances, so I decided to create SQL Agent Insight, found here > http://brentec.ca. It is an evolving product and will get out of beta in the near future and it is continually being updated and suggestions are welcome. This is a project ...


0

I've discovered what the issue was. 2 of the parameters were connection strings and if a connection string contained an Application Name then the value of the preceding parameter was not being stored. This worked fine from my machine so I'm assuming a difference in the version of SSMS client is to blame. For the time being I have simply removed the ...


1

You can write this more tersely (e.g. without any variables at all), but I thought logically breaking it up might be more helpful. DECLARE @Today DATE = SYSDATETIME(); DECLARE @FirstDayNextMonth DATE = DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(@Today), @Today)); DECLARE @LastWorkDayThisMonth DATE; SELECT @LastWorkDayThisMonth = MAX(CalendarDate) FROM ...


0

Well, many thanks James, Mark, Aaron and especially David Benoit. I read the Joe Sack article you mentioned and it led me to Benjamin Nevarez's article - http://www.benjaminnevarez.com/2011/05/optimizer-statistics-on-linked-servers/. which then pointed out the BOL - Guidelines for Using Distributed Queries. The stored procedure had 3 remote queries. The ...


0

Many of the plan options in Profiler are estimated plans, so you will have to use the correct one to get the actual plan. Since you are using SQL Sentry (great product by the way :) ), you should be able to get the plan for the statement run from the Top SQL tab if you check shortly after the job was run. Regardless, IF at all possible, I recommend that ...


1

One risk mitigation is to use a signed stored procedure. Here's an example of this in action. In summary, you package all the sensitive action (table access etc.) inside a stored procedure. You create a certificate in SQL server to sign that SP. You create a new login who's only ability is to execute the SP and you send the password to this low-privilege ...


0

do a manual full backup and then it will run. Why is this? This is because any database you create with full recovery model is always in pseudo simple recovery mode until you take a full database backup and this is where a log backup chain can be establish successfully. You can easily check if a database is in pseudo simple recovery mode using DBCC ...



Top 50 recent answers are included