Economics

European recession, should focus on education, focus on innovation, cut on social security, environment conditions and entitlements, need to strike a balance to see growth. Innovation and education to bring is more manufacturing  rather than europe being services, pharamaceutical and financial base.

sql 2008 upgrade - datetime Settings

When installed a new instance of sql server, or upgrade to sql 2008. Check the following.


1. SQL Collation should be the same.

2. The date and time setting should be the same, if it English (United states)

3. In the Regional and Language settings also in the administrative tab  --- click on System locale and change to English United States.

4.

Code to find No of lines in SP

SELECT t.sp_name AS 'Stored Procedure',


SUM(t.lines_of_code) - 1 AS 'No of Lines in code ',

t.type_desc AS 'Object Description'

FROM

(

SELECT o.name AS sp_name,

(LEN(c.text) - LEN(REPLACE(c.text, CHAR(10), ''))) AS lines_of_code,

CASE WHEN o.xtype = 'P' THEN 'Stored Procedure'

WHEN o.xtype IN ('FN', 'IF', 'TF') THEN 'Function'

END AS type_desc

FROM sysobjects o

INNER JOIN syscomments c

ON c.id = o.id

WHERE o.xtype IN ('P', 'FN', 'IF', 'TF')

AND o.category = 0

AND o.name

NOT IN ('fn_diagramobjects', 'sp_alterdiagram',

'sp_creatediagram', 'sp_dropdiagram',

'sp_helpdiagramdefinition', 'sp_helpdiagrams',

'sp_renamediagram', 'sp_upgraddiagrams',

'sysdiagrams')

) t

GROUP BY t.sp_name, t.type_desc

ORDER BY 1

Drop temp table

IF EXISTS


(

SELECT *

FROM tempdb.dbo.sysobjects

WHERE ID = OBJECT_ID(N'tempdb..#temp1')

)

BEGIN

drop table #temp1

END

calculating time difference

--*********************************************************************************


--part 1

--*********************************************************************************

DROP TABLE #temp1

declare @date datetime

set @date = convert(varchar,getdate()-1,101)

SELECT distinct @date Tdate,emp.EmployeeNo,emp.[EmpName],

(select Min([CLKDT]) from [tmsdata].[tmsuser].[tmsclk] where EMPREF COLLATE DATABASE_DEFAULT=[EMP].[TMSNo] COLLATE DATABASE_DEFAULT and datediff(day,CLKDT,@date)=0) ClockIn,

(select Max([CLKDT])from [tmsdata].[tmsuser].[tmsclk] where EMPREF COLLATE DATABASE_DEFAULT=[EMP].[TMSNo] COLLATE DATABASE_DEFAULT and datediff(day,CLKDT,@date)=0 having

Max([CLKDT])<>(select Min([CLKDT]) from [tmsdata].[tmsuser].[tmsclk] where EMPREF COLLATE DATABASE_DEFAULT=[EMP].[TMSNo] COLLATE DATABASE_DEFAULT

and datediff(day,CLKDT,@date)=0)) Clockout,

EMPREF into #temp1

FROM tblEmployee emp

JOIN [tmsdata].[tmsuser].[tmsclk] [CLK] ON [EMP].[TMSNo] COLLATE DATABASE_DEFAULT = [CLK].[EMPREF] COLLATE DATABASE_DEFAULT

WHERE datediff(day,CLKDT,@date)=0 and emp.iscosting = '1' AND emp.active = 1

ORDER BY emp.[EmpName]

--*********************************************************************************

--part2

--*********************************************************************************

DROP TABLE #temp2

SELECT empref, empname, DATEDIFF(mi, ClockIn, Clockout) AS clockedhrs

INTO #temp2 FROM #temp1





--*********************************************************************************

--part3

--*********************************************************************************

DROP TABLE #TEMP3

--declare @date datetime

--set @date = convert(varchar,getdate()-2,101)

SELECT emp.[EmpName] AS empname,TMSNo,

SUM(DATEDIFF(mi, lt.timein, lt.timeout)) AS scannedhrs

INTO #TEMP3

FROM tblLotCardTracking lt JOIN tblEmployee emp ON lt.EmployeeNo = emp.EmployeeNo

INNER JOIN #temp1 t1 ON t1.EMPREF COLLATE DATABASE_DEFAULT = emp.TMSNo

WHERE lt.TimeIn > @date AND lt.TimeIn < @date + 1 AND emp.iscosting = '1' AND emp.active = 1

GROUP BY emp.[EmpName],TMSNo

--*********************************************************************************

--SELECT * FROM #temp1

--SELECT * FROM #temp3

--*********************************************************************************

--part 4

--*********************************************************************************

SELECT

t2.empname,

CONVERT(CHAR(8),DATEADD(MINUTE,SUM(scannedhrs)% 1440, '00:00'), 108) AS scannedhrs,

CONVERT(CHAR(8),DATEADD(MINUTE,clockedhrs% 1440, '00:00'), 108) AS clockedhrs,

scannedhrs,clockedhrs,

CONVERT(NUMERIC(18,2),(CONVERT(NUMERIC(18,2),scannedhrs)/CONVERT (NUMERIC(18,2),clockedhrs) )*100)

FROM #TEMP3 t3 INNER JOIN #temp2 t2 ON t2.EMPREF COLLATE DATABASE_DEFAULT = t3.TMSNo

group BY t2.empname,clockedhrs,scannedhrs

--*********************************************************************************

END

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