T-SQL (SS2K8) :: Determine If 3 Most Recent Job Executions Have Failed
Dec 29, 2014
I'm looking for feedback on a query I've devised to return a numeric value if the 3 most recent executions of a job have failed. The purpose of the query is to server as a custom counter alert for Idera SQL DM, and by definition an SQL Script alert must return a numeric value.
My environment is SQL Server 2008. Note that the job name is hard coded.
;WITH CTE_Restore_JobHistory AS
(SELECT h.[job_id]
,j.[name] as JobName
,h.[message]
,h.[run_status]
CREATE TABLE History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)
INSERT INTO History VALUES ('03-23-2015','PT-01','2015-04-22') INSERT INTO History VALUES ('03-30-2015','PT-01','2015-04-20') INSERT INTO History VALUES ('04-06-2015','PT-01','2015-06-30')
[Code] ....
I need an output in the below format. I need the most recent changed value for any given UID. Need to get the below result
create table dbo.customer ( customer_id int identity primary key clustered, customer_name nvarchar(256) not null ) create table dbo.purchase_order ( purchase_order_id int identity primary key clustered customer_id int not null, amount money not null, order_date date not null )
Implement a query for the report that will provide the following information: for each customer output at most 5 different dates which contain abnormally high or low amounts (bigger or less than 3 times SDTDEV from AVG), for each of these dates output minimum and maximum amounts as well.
DelimitedSplit8k and PatternSplitLoop seem to have potential, but I'm just plain stuck on some things:
1. DelimitedSplit8k: the delimiter split the folder paths, but the pattern can be within the strings that result. 2. PatternSplitLoop: I would have to cross apply 16 times and have an awful WHERE clause to determine which of the four strings matched first.
Unless I'm missing something. Short example is below.
WITH testctes (string, pattern) AS ( SELECT 'oh_look_at_this.thing.hishers_stuffmine.craftyours_protein', 'his first' UNION ALL SELECT 'i.am.a._thing.hershis_thingsmine.refrigeratoryours_potato', 'hers first' UNION ALL SELECT 'path_like.things_minehers.some_elsehis_garbageyours_sneakers', 'mine first' UNION ALL SELECT 'more_stuff.yoursminehershis_falafel', 'yours first' ) SELECT string, pattern, ca.item, ca.itemnumber FROM testctes CROSS APPLY [dbo].[PatternSplitLoop] (string, '%his%') ca
I have a table with addresses and activity dates. I need to be able to retrieve the past 3 activity dates and see if the first and last occurred within 15 days. If so, I need to flag them.
Using max date gets me the last date but not the previous two. I was trying to use top 3 in desc order and that didnt seem to work either.
I have several tables a varbinary column in a database. They have names like CSB_BLOB or OBJECT_BLOB. Now I am having intermittent success with getting the data out.
For example this query returns readable text from this data.
0x46726F6D3A20226465616E6E6167726.....etc --data as stored in the column
SELECT CAST(CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM OBJECT_BLOB
However this column has the following query results.
0x0001000000FFFFFFFF01000000000000000C....etc. --data as stored in column
--this query returns empty result
SELECT (CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM CSB_STATUS_LOG
--this query returns no change???
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), CSB_BLOB, 2), 2) FROM CSB_STATUS_LOG 0001000000FFFFFFFF01000000000000000C....etc
Obviously there is a difference between the two but I am not educated enough to interpret this difference. What do I need to learn / read so I can look at the data in one of these BLOB columns and know how to convert it to something meaningful?
Something like:
1. Try to cast as varchar to see if it is text. 2. Turn into a byte array and see if it is a jpg 3. Turn into a byte array and see if it is a pdf 4. Convert it to hex and then cast as varchar 5. etc....
A former employee at my organization created a replication job to back up our database. The developer has since left and his windows account was deleted. I'm now receiving this error:
SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-05 15:40:00 - Message: The job failed. Unable to determine if the owner (username) of job Replication agents checkup has server access (reason: Could not obtain information about Windows NT group/user 'username', error code 0x534. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).
How can I fix this? I looked under the Replication folder but didn't see anything.
I need to get all customer records with the most recent tDate. A customer should never have duplicate tDate records, as only one record per day is allowed per customer, yet there somehow there are duplicates. Given this, I need to get the record with the Max date and Max ID. the ID column is an auto-incrementing Identity column.Below is the code without the Max ID column logic
SELECT tCustID, MAX(tDate) AS tDate--get MAX tDate records FROM table1 GROUP BY tCustID
I have a weird situation. I have migrated some DTS packages from a sql 7.0 server to SQL2005. One of them works as a DTS package but fails under the job sections with an error saying could not connect to the database server. I have no option but to rewrite the whole package in SSIS(which I hate).
Have a table that list item#, date the standard cost went into effect and the standard cost. How do I find the difference in StdCost on the last EffectiveDate and second to last EffectiveDate. 5.59 (01/05/2015) minus 5.81 (09/29/.014) = -.22.
Hi,I'm looking for a way to log exactly which sql queries are executed on a specific asp.net page request. Actually I'm not looking for anything advanced here, just a log which writes all the queries to a text-file would be good enough for me. Does anyone know of a tool for visual studio that could help me with this or is there a way to build a logger like this by hooking into ADO.NET in some way? This would help me out a whole lot now as I with this log could see that my cache logic really is working as it should. thanks!
I have a file-deployed package which I need to call N times at once using DTEXEC. When I attempt to execute N instances of the package, the second instance doesn't execute. I can execute different packages at the same time, but never the same package more than once. Does anyone know how to get this scenario working? Just to clarify a bit more - I can call the package as many times as I like as long as I wait until the previous run of DTEXEC is finished. Please help!
How can I prevent a package, or a section of a package, from being run more than once simultaneously? The package makes use of a staging database table, and if two copies of this package are run at the same time, there will be a race condition and possible corruption of the data in the database table. I have also noticed that the SSIS logging gets messed up and starts overwriting itself if I have more than one of these packages run at the same time.
What I am thinking of should work the same way as a serializable transaction lock on a normal query, where one query has to wait if another query has a lock on the table. I don't want the package to throw an error, I want it to wait until the other one is done.
I have been trying to use transactions in a test SSIS package, but they are not quite doing what I want them to do. I put various SSIS steps in a sequence container and require a serializable transaction. I have also tried putting the transaction at the package level, but that is not preventing simultaneous package runs.
Is there a built-in way to do this that is easy to implement? Otherwise, what I could do is insert a flag into the database indicating that a package is running, then make sure to reset the flag on any error or on completion. It seems like this alternate method could be error-prone though.
I'm looking for a facility similar to NT audit facility except for SQL Server 6.5.
Is there a tool or facility that will track when users log in (using SQL Server security), and either which stored procedures they execute or which tables they select?
basically, is it inefficient to open and close a data connection everytime data needs to be retrieved or is there a way for a user to use the same conenction over multiple pages orfor multiple users to share one global data connection I just wanted to know this before I built a site where data could be constantly being pulled or put into the database.It would be easier to just keep opening and closing the connection since it just means pasting in the small chunk of code I use to do that where I need it. I hear speak of connections sometimes not closing etc and wonder if that is an issue then if you are opening and closing too many of them. I am using SqlConnection SqlCommand and SqlDatareader , my code works, just wondering if my approach lacked scalability before I find out too late and have to rewrite everything . Jim
After fine-tuning my package logging, I built a query against SYSDTSLOG9 that uses the combination of the OnPreExecute events and any subsequent event for each task to build a nice view of a package in progress, including the duration of each event. (Running tasks would have a row with a NULL EndTime.)
When running the package within the Designer, everything works as expected. Events are logged at the task level and everything bubbles-up to the package level.
However, when I run the package from SQL Agent (using the SSIS job step), none of my OnPreExecute or sub-component events are written. That is, I only get package-level OnInformation, OnPostExecute and OnError events.
This means when the job/package is executing, I can't directly see which steps are currently in progress nor can I get a duration for each step once it's completed. This will be very disconcerting for my teammates who will have to help support this at some point.
Any idea how or why there would be a change in behavior between these two execution methods?
Thanks in advance for any help. Until then, banging my head against the wall... Tim
All source and target date fields are defined as data type "smalldatetime". The "select" executes without error though when used with "insert into" it fails with the error:
Msg 295, Level 16, State 3, Line 25: Conversion failed when converting character string to small date-time data type..I am converting from a character string to smalldatetime since the source and target date columns are "smalldatetime". All other columns for the source and target are nvarchar(255). I assume there is an implicit conversion that I don't understand. In a test, I validated that all dates selected evaluate ISDATE() to 1.
USE [SCIR_DataMart_FromProd_06_20_2014] GO IF OBJECT_ID ('[SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]', 'U') IS NOT NULL DROP TABLE [SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]
i have a datetime field in the post tables. I would like to get the records within the latest 7 days. Are there any functions for doing something like this? my current query is something like select * from post where creation_time .... Thank you
Log: Reports_Log LogID | ReportID | AccountID | ReportRunDate ---------------------------------------- 9876 | 5 | 1234 | 9/4/2007 10:35:43 AM 9875 | 5 | 987 | 9/4/2007 10:35:43 AM 9874 | 4 | 1234 | 9/4/2007 9:05:43 AM 9873 | 3 | 1234 | 9/4/2007 9:30:17 AM 9872 | 5 | 1234 | 9/3/2007 10:35:43 AM 9871 | 5 | 987 | 9/3/2007 10:35:43 AM 9870 | 4 | 1234 | 9/3/2007 9:05:43 AM 9869 | 3 | 1234 | 9/3/2007 9:30:17 AM 9868 | 5 | 1234 | 9/2/2007 10:35:43 AM 9867 | 5 | 987 | 9/2/2007 10:35:43 AM 9866 | 5 | 5677 | 9/2/2007 10:35:43 AM 9865 | 4 | 1234 | 9/2/2007 9:05:43 AM 9864 | 3 | 1234 | 9/2/2007 9:30:17 AM
so if i wanted to know what report for reportID 5 hasn't run in the past 2 days, it would be accountID of 5677 (last ran on 9/2/2007 10:35:43 AM)
I am not sure where to even start. I am thinking I can grab the latest report ran for all Accounts in the Reports_AccountID table (create temp table and insert most recent log record into that table), and do a date difference between the date and current datetime and select based on datedifference > 2 ?
or would the best way is to do an inner join between the 2 tables for all reports ran in the past 2 days, and then whatever account is not listed in that query, is a report that has not run (do a subquery?)
just trying to think of the best way to do this. any tips/advice would be appreciated
I have two tables totally unrelated but give the same information, the difference is the duration. I need to create a stored procedure that will give the recent issue dates only, accept if they have already expired. I'm not exactly sure how to do that. We only want the employees to see the current issue date as long as the exclusion has not expired. Can anyone help please
AS SELECT [dbo].[30 Day exclusion].[First Name], [dbo].[30 Day exclusion].[Last Name], [dbo].[30 Day exclusion].[Issue Date], [dbo].[Extended Exclusions].[First Name], [dbo].[Extended Exclusions].[Last Name], [dbo].[Extended Exclusions].[Issue Date] FROM[dbo].[30 Day exclusion] INNER JOIN [dbo].[30 Day exclusion].[id] ON [dbo].[Extended Exclusions].[ID] = [dbo].[30 Day exclusion].[id]
WHERE (@StartIssueDate is null or [Issue Date] >= @StartIssueDate) AND (@EndIssueDate is null or [Issue Date] <= @EndIssueDate) AND (@Enter_LastName is null or [Last Name] = @Enter_LastName) ORDER BY [Last Name]
I have a Customers table and an Orders table. The Customers table has fields... CustomerId, and CustomerName. The Orders table has Fields CustomerId, OrderDate, and ProductId.
I wish to execute a query that will return a single Customer and Order record for a given customer ID that reflects the most recent order. So, a typical join like...
Please concider the line of code below. it doesnt quite work how i want it to. i need it to only pull records where there isnt any activity completed (actualend) after a given period. and only show the most recent activity per regardingobjecid(sales person) however it returns all the records before that period and shows me the most recent one up to that time. Please help.
here is the code
SELECT new_ratingname, regardingobjectidname, actualend, owneridname, createdbyname, activitytypecodename, count(*) AS Total FROM (SELECT filteredcontact.new_ratingname, filteredactivitypointer.regardingobjectidname, filteredactivitypointer.actualend, filteredactivitypointer.Owneridname, filteredactivitypointer.createdbyname, filteredactivitypointer.activitytypecodename, row_number() OVER (Partition BY regardingobjectid ORDER BY actualend DESC) AS recid FROM FilteredActivityPointer INNER JOIN FilteredContact ON FilteredContact.contactid = FilteredActivityPointer.regardingobjectid WHERE new_ratingname NOT IN ('dead', 'archive', 'continous updates') AND filteredactivitypointer.statecodename = 'completed' AND filteredactivitypointer.owneridname IN (@user) AND filteredactivitypointer.createdbyname NOT IN ('melvin felicien', 'suzette collymore', 'gasper ') AND filteredactivitypointer.activitytypecodename IN ('phone call', 'e-mail', 'fax') AND filteredactivitypointer.actualend <= dateadd(d, - CAST(@NeglectedDays AS INT), GetUTCDate())) AS d WHERE recid = 1 GROUP BY d .actualend, d .regardingobjectidname, d .owneridname, d .createdbyname, d .activitytypecodename, new_ratingname ORDER BY d .actualend
Ok, I was able to find the most recent Effective date and it works well. The only problem I am having is with the CarrierName. See there are different Carriers with so then it makes us have Mult effective dates. I need to pull the most recent effective date with carrier. So, I though to max the pcsiNumber2, but that didn’t work. Do you have any other ideas? Let me know.
Select Distinct --PCSNumber, PCSINumber1, max(PCSINumber2) as mostrecentPCS, LastName, FirstName, State, --InsuranceCarrier, CarrierName, MAX(CoverageEffectiveDate)as MostRecentEff, MAX(TerminationDate) as MostRecentTerm, AmtPerClaim, Aggreg
FROM VW_Medical_Malpractice_Project
Where State = 'PA'
Group By PCSINumber1, LastName,FirstName,State,CarrierName,AmtPerClaim,Aggreg
Order By PCSINumber1
Example
000043010003MUSSER WILLIAM American Professional Agency2002-06-01
000043010002MUSSERWILLIAM Columbia Casualty2000-02-20
So, what I need is the most recent effective date which would be 2007-01-01, which I have but what is not letting me do this is the names, the 5ith column. I was trying to use the 2nd column to use the max , but it is not working. What am I doing wrong. I need the whole last row, but that number 0005 can change because there may only be 2 dup records.
hello,I have a table which contains the audit time field. This audit time fieldrecords the current date and time when a record is inserted.I would like to output the most recent top 500 rows.thank you.
If I have tables like Customer (Id, First, Last, Address, etc.), CustomerEvent (EventCode, EventDate, CustomerId), EventTypes(Code, Description) how would I get all of the CustomerIds where the latest CustomerEvent record is a cancel and it happened within the last 60 days?
If I just do a select max(EventDate) and put EventTypes.Code='cancel' in the where clause it picks up records where the customer has done other things since the cancel.
Sorry if I am posting in the wrong place here. I am not a programmer by profession but just muddle through building some reports for my job. The report I am working on at the moment is the most complicated one I have worked on so far. I am working with MS Query (SQL) and trying to limit the rows returned with the most recent date. I have searched online for the answer to this but just cannot seem to get it to work. There can be more than one Production Name per Manuscript Number. Each Production Name has a Production Task Assignment Start Date. I want to display one row per record with the latest (most recent) Production Task Assignment Start Date. I have been trying to use the MAX functionality (which I have seen listed online) but just cannot get it work. Can anyone help? It would be much appreciated! The code looks like this: SELECT SCHEDULE_GROUPS.`Schedule Group Description` AS 'AE', ROLEAUTH_DOC_PEOPLE_ADDR.`Article Type`, ROLEAUTH_DOC_PEOPLE_ADDR.`Manuscript Number` AS 'MS #', DOCUMENT.`Production Notes` AS 'Short title', ROLEAUTH_DOC_PEOPLE_ADDR.`First Author Last Name` AS 'First Author', PRODUCTION_TASKS.`Production Task Name` AS 'Name', "Production Task Assignment Start Date"-"ROLEAUTH_DOC_PEOPLE_ADDR.Final Decision Date" AS 'Days Since', ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.`Last Name of the Editor who made First Decision`, ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.`Decision Term`, ROLEPROD.`Production Task Assignment Start Date` FROM DOCUMENT.tab DOCUMENT, PRODUCTION_TASKS.tab PRODUCTION_TASKS, ROLEAUTH_DOC_PEOPLE_ADDR.tab ROLEAUTH_DOC_PEOPLE_ADDR, ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.tab ROLEEDIT_DOC_PEOPLE_ADDR_AUTH, ROLEPROD.tab ROLEPROD, SCHEDULE_GROUPS.tab SCHEDULE_GROUPS, SCHEDULE_GROUPS_TOC.tab SCHEDULE_GROUPS_TOC WHERE PRODUCTION_TASKS.`Unique Idenitifer` = ROLEPROD.`Production Task ID` AND ROLEPROD.`Document ID` = ROLEAUTH_DOC_PEOPLE_ADDR.`Document ID` AND ROLEAUTH_DOC_PEOPLE_ADDR.`Document ID` = SCHEDULE_GROUPS_TOC.`Document ID` AND SCHEDULE_GROUPS_TOC.`Schedule Group ID` = SCHEDULE_GROUPS.`Unique Identifier` AND ROLEAUTH_DOC_PEOPLE_ADDR.`Document ID` = DOCUMENT.`Unique Document ID` AND ROLEAUTH_DOC_PEOPLE_ADDR.`Document ID` = ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.`Unique Document ID` AND ((SCHEDULE_GROUPS.`Schedule Group Description` Not Like '%2008' And SCHEDULE_GROUPS.`Schedule Group Description` Not In ('Accepted (With SC for assigning)') And SCHEDULE_GROUPS.`Schedule Group Description` Not In ('Comments')) AND (ROLEEDIT_DOC_PEOPLE_ADDR_AUTH.`Decision Term`='Accept'))ORDER BY ROLEAUTH_DOC_PEOPLE_ADDR.`Manuscript Number` Once the records are retrived I am returning the data to Excel. Thanks for your time. Natalie
Every hour we capture some values from our factory (position of pumps, valves, ...) in our sql server 2000 db.
Normally 1 record is added to the db. 00:00:00 01:00:00 02:00:00 ... 21:00:00 22:00:00 23:00:00
All of these values are displayed in an Excel sheet. One sheet contains all the data from one month.
I noticed a problem last week when 2 records were added: first one at 19:00:00 and the second one at 19:00:01
We only want to keep the most recent record (19:00:01) in a situation like this but I can't seem to work out an SQL-statement :o
This is what we have know. It used to work fine untill we had 2 record being added instead of one. SELECT TimeOfCapture, Value1, Value2, Value3 FROM FactoryTable WHERE (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE()))
I need this query to pull just the last scan date, i.e. if a scan ran on 3/7/2012 I need just the records (there may be few or many) that pertain to that date and no other dates. When I run this query I am still getting all of the results (i.e. scan dates of 3/7/2012, 3/5/2012, 3/1/2012, etc)
I just need the most recent date, even if there are many records for that date (i.e. 10 IP addresses that were all scanned on 3/5/2012)
