Identify most recent record in a table

Delete FROM tbl_transfer


DECLARE @qtx_OriginalItemID int, @qtx_ProductCategoryID INT,@qtx_TimeStamp datetime

DECLARE MyCur CURSOR FOR Select

--qtx_TimeStamp,qtx_Responded

qtx_OriginalItemID,qtx_ProductCategoryID,qtx_TimeStamp

from

QuoteTransfer

left JOIN QuoteMaster on qm_QuoteMasterID = qtx_NewQuoteMasterID

left JOIN [QuoteTransferRegionDepartment] on [qtrd_QuoteTransferRegionDepartmentID]=qtx_DepartmentID

--WHERE qtx_OriginalItemID = '1' AND qtx_productcategoryid = '9'

ORDER BY qtx_OriginalItemID,qtx_productcategoryid

ASC


OPEN MyCur

FETCH NEXT FROM MyCur INTO @qtx_OriginalItemID,@qtx_ProductCategoryID,@qtx_TimeStamp

WHILE @@FETCH_Status =0

BEGIN

BEGIN TRAN

SELECT @qtx_OriginalItemID, @qtx_ProductCategoryID

IF EXISTS (SELECT 1 FROM tbl_transfer WHERE ItemID = @qtx_OriginalItemID

AND CategoryID = @qtx_ProductCategoryID)

BEGIN

DELETE FROM tbl_transfer WHERE itemid = @qtx_OriginalItemID

and CategoryID = @qtx_ProductCategoryID

AND qtx_TimeStamp = @qtx_TimeStamp

SELECT @qtx_OriginalItemID,@qtx_ProductCategoryID

END

Else

begin

INSERT INTO tbl_transfer(qtx_TimeStamp,qtx_Responded,Itemid,categoryid)

SELECT qtx_TimeStamp,qtx_Responded,qtx_OriginalItemID,

qtx_ProductCategoryID

from

QuoteTransfer

left JOIN QuoteMaster on qm_QuoteMasterID = qtx_NewQuoteMasterID

left JOIN [QuoteTransferRegionDepartment] on [qtrd_QuoteTransferRegionDepartmentID]=qtx_DepartmentID

where qtx_OriginalItemID= @qtx_OriginalItemID

AND qtx_ProductCategoryID = @qtx_ProductCategoryID

AND qtx_TimeStamp = @qtx_TimeStamp

end

FETCH NEXT FROM MyCur INTO @qtx_OriginalItemID,@qtx_ProductCategoryID,@qtx_TimeStamp

END

CLOSE MyCur

DEALLOCATE MyCur

SELECT * FROM tbl_transfer

To identify sql jobs in sql 2005 server

To identify all the sql jobs running in a sql 2005 server. The query below can be used.


SELECT
CONVERT(DATETIME, RTRIM(jh.run_date)) + (jh.run_time * 9 + jh.run_time % 10000 * 6 + jh.run_time % 100 * 10) / 216e4 AS RunDateTime,
j.name AS JobName,
jh.step_id AS StepID,
SUBSTRING(RIGHT('000000' + CONVERT(varchar(6), jh.run_duration), 6), 1, 2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(varchar(6), jh.run_duration), 6), 3, 2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(varchar(6), jh.run_duration), 6), 5, 2) AS StepRunTime,
jh.step_name AS StepName
FROM msdb..sysjobhistory jh
INNER JOIN msdb..sysjobs j ON jh.job_id = j.job_id
WHERE j.name <> 'Database Mirroring Monitor Job'
ORDER BY jh.run_date, jh.run_time, j.job_id, jh.step_id

To identify the ssrs reports

The below query will provide the path and the name of the reports hosted through sql server reporting services (ssrs). The query needs to be run under repport server database.
select * from catalogue