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

To check for more than one primary key to insert into a table.

For example table p1 and p2 are combined and inserted into table p3

INSERT INTO p1 VALUES (1000)

INSERT INTO p1 VALUES (2000)

INSERT INTO p1 VALUES (3000)

INSERT INTO p2 VALUES (1000,5)

INSERT INTO p2 VALUES (1000,2)

INSERT INTO p2 VALUES (2000,1)

INSERT INTO p2 VALUES (3000,1)

SELECT * FROM p3

INSERT INTO p3

select hdr.ponumber,ord

from p2 Det,

p1 Hdr

where Hdr.PONumber = Det.PONumber

and (det.ponumber not in (select ponumber from p3)

or det.ord not in (select ord from p3))

Converting rows into columns

drop table temp1
select td.salesordernoitem,
case when diskcomp = 'inlet' then material end as I into temp1
from tbltransassemblies ta
inner join tbltransdiskcomp td on td.salesordernoitem = ta.salesordernoitem
where diskcomp in ('inlet')
drop table temp2
select td.salesordernoitem,
case when diskcomp = 'outlet' then material end as O into temp2
from tbltransassemblies ta
inner join tbltransdiskcomp td on td.salesordernoitem = ta.salesordernoitem
where diskcomp in ('outlet')
select t1.salesordernoitem,I,o into temp3
from temp1 t1 inner join temp2 t2 on
t1.salesordernoitem = t2.salesordernoitem

select * from temp3
select * from tbltransdiskcomp where salesordernoitem = '11381068-1'

To calculate the Group total in SSRS

We had a situation to generate a report which is grouped by our job number and to show the jobstatus the number of days the job was in each status.

To do this I had grouped by jobnumber, so the we do not was to repeate the job value each time, it was displayed in the group row. Now to calculate the group total, we do not have a option in SSRS, so we had to create a separate Dataset for the total and show the total.

To calculate No of days in a report

We had a situation to generate a report, where we need to calculate the number of days the order was in each and every department. We are saving this information by jobstatus and job date. To get the number of days the job was in hold in each department we had find the datediff between the first vs second date.

To get the report generate in SSRS we use the funtion under the code area. The code area in SSRS is in Report - Properties --- code

We had used the below code to calculate the No of days, but note this includes the saturday and sunday.
--************************************************************************************
dim gd_Date1 as datetime = "1900-01-01"dim ln_DtDif as integerdim lotno1 as string
public function No_of_days (transdate as datetime,date_received as datetime,lotno as string) as integer
if lotno1 <> lotno Then
ln_DtDif = datediff ("d",date_received,transdate)
else ln_DtDif = datediff ("d",gd_Date1,transdate )end if
gd_Date1 = transdatelotno1 = lotnoreturn ln_DtDif
end function

Change File Name in SSRS

SSRS Report File Name.
There are two options for subscription on the SSRS one for email and one for file share, on the file share you can customize the name of the file, but the physical excel file need to be save on the system, this file will not be automatically emailed to the user. To do this you need to create SSIS job to email the file.

On the other hand the email subscription option does not provide the option to customize the name of the file for eg excel file.

Conclusion
We need to split the report into number of subscriptions needed or use the file share option and create SSIS job to email them.

Reduce MSDB File

To reduce the file size of msdb, one of checks is to check the sysjobhistory in msdb database. This will grow with large number of records. This can be purged using the below syntax

sp_purge_jobhistory { [ @job_name = ] 'job_name' [ @job_id = ] job_id } [ , [ @oldest_date = ] oldest_date ]

USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@job_id = N'93B30316-FF0E-42C7-80C7-7CBAA98817DB'
GO

or

USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@oldest_date = N '2010-07-01'
GO

SELECT COUNT(*) FROM dbo.sysjobhistory
USE msdb
EXEC sp_delete_backuphistory '01/20/98'

USE msdb
select count(*) from backupset with (nolock) where backup_start_date < '2011-01-01'
USE msdb
SELECT [Size in MB] = SUM(IDX.reserved)/128, [Object Name] = OBJ.name FROM msdb.dbo.sysindexes IDX JOIN msdb.dbo.sysobjects OBJ ON OBJ.id = IDX.id WHERE IDX.indid IN (0, 1, 255) GROUP BY IDX.id, OBJ.name ORDER BY 1 DESC