Date format SQL Server

Date format SQL Server

select CONVERT(VARCHAR(22),GETDATE(),103) + ' ' + CONVERT(VARCHAR(8),GETDATE(),114)

Send Multiple reports in One Email

Send Multiple reports in One Email


Create one new report and include all other reports in that as Sub report and then schedule this report to send by email.

Format Date and year like CDR201106

Format Date and year like CDR201106

Format the date and year like CDR201106 by taking current month and year. By using Left, Len command of sql server.

DECLARE @VAR varchar(50)

SELECT  @VAR = 'CDR' + convert(varchar(4),YEAR(getdate())) + left('000', 2- len(month(getdate()))) + CONVERT(varchar(2),month(getdate()))

Passing and taking out values from Open Query

Passing and taking out values from Open Query

DECLARE @TSQL nvarchar(1000), @VAR varchar(50)


--Formate Date and year like CDR201106

SELECT  @VAR = 'CDR' + convert(varchar(4),YEAR(getdate())) + left('000', 2- len(month(getdate()))) + CONVERT(varchar(2),month(getdate()))

SELECT  @TSQL = 'SELECT @RepCount= [rows] FROM OPENQUERY([LEB-ODSREP01],''SET FMTONLY OFF EXECUTE DEUView.dbo.SP_SPACEUSED ''''' + @VAR + ''''''')'

EXEC  sp_executesql @TSQL, N'@RepCount int out' , @RepCount out

select @ODSCount,@RepCount

OpenQuery to execute query on other linked server

use the following OpenQuery to execute query on other linked server:

select * FROM
OPENQUERY([LEB-ODSREP01],'SET FMTONLY OFF EXECUTE DEUView.dbo.SP_SPACEUSED [CDR201105]')

without using SET FMTONLY OFF, it will give following error:
The OLE DB provider "SQLNCLI10" for linked server "LEB-ODSREP01" supplied inconsistent metadata for a column. The name was changed at execution time.

SET FMTONLY OFF

SET FMTONLY OFF

If it is ON, will not return any data only the column names.

For example:
The following command will not return any row/data.


SET FMTONLY ON
select * from T_repstatus

Following will return the rows with column names:


SET FMTONLY OFF
select * from T_repstatus

Delete old cases from CRM 3.0

Delete old cases from CRM 3.0

Use the following query to delete old cases from CRM 3.0.

Set the deletion code value to 2, MS CRM deletion service will automatically delete that records and child records when it will run (usually 2 hours).


Update IncidentBase set deletionStateCode=2
where datediff(d,'2009-06-30',ModifiedOn) < 0

Recycle Work process in IIS on daily basis

Recycle Work process in IIS on daily basis to Increase Performance of IIS

We need to Recycle work process in IIS on daily basis to remove any stuck process/thread from IIS. This will help in increasing the process of IIS.

Please see the below screen shot to know how to do it.