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.

SQL Query to Take back up of Database

SQL Query to Take back up of Database 



sp_helpdb reportserver
go
sp_helpdb reportservertempdb

backup database reportserver to disk='D:\scripts\ssrs\reportserver.bak'  with init, stats=1
go
backup database reportservertempdb to disk='D:\scripts\ssrs\reportservertempdb.bak'  with init, stats=1

Useful javascripts for MS CRM

useful javascripts for MS CRM

From this link you can get very Useful javascript to use in CRM.

http://crmdelacreme.blogspot.com/2010/05/replacing-content-of-iframe-if-you.html

Contains topics like:

Replacing the content of an IFRAME

Changing the title of a CRM form

Changing the label of a field at runtime

Changing the colour of a single option in a picklist

Disabling a form at runtime

Automatically changing the value of a text field to capitals

Displaying a picture in a CRM form, using a text field to store the data source

Modifying the color of disabled form fields

and many more......