Microsoft Dynamics CRM UK Blog

CRM news and views from Simon Hutson

Quick & Dirty CRM Date Change

Quick & Dirty CRM Date Change

  • Comments 3

Goodbye To Yesterday...

Every so often I find I need to update various date fields in my CRM demo data for a last-minute customer demo. A typical example is when I'm demonstrating case management functionality and I want to update the CreatedOn or ModifiedOn date for all my cases, offset by some random number of days from a particular date. The easiest way to do this is by running a SQL script directly on the CRM database, but enyone who knows me well knows that I have this mental block when it comes to writing T-SQL code. So instead of spending hours trying to figure it out each time I want to make this kind of change, I thought I would post some typical T-SQL code here for me to re-use.

DECLARE @CreatedOn datetime, @IncidentId uniqueidentifier

DECLARE incident_cursor CURSOR FOR
SELECT incidentid
FROM incident

OPEN incident_cursor

FETCH NEXT FROM incident_cursor
INTO @IncidentId

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @CreatedOn = DATEADD(Day, ROUND(RAND()*365,0)-365, GETDATE())

UPDATE incident
SET createdon = @CreatedOn
WHERE incidentid = @IncidentId

FETCH NEXT FROM incident_cursor
INTO @IncidentId

END

CLOSE incident_cursor
DEALLOCATE incident_cursor

Please feel free to use this as you see fit, but remember folks that any kind of direct CRM database modification is totally unsupported and could lead to all kinds of mayhem, premature hair loss and other nasties, so don't try this on your production systems.

This posting is provided "AS IS" with no warranties, and confers no rights.

Laughing Boy

Comments
  • PingBack from http://microsoftnews.askpcdoc.com/microsoft-dynamics-crm-40/quick-dirty-crm-date-change

  • Inspired by Simon Hutson's post and sharing his aversion to T-SQL code, I decided to have a go at

  • We are using MS CRM 3.0...

    We had to add one custom report (made in VS 2003 Business intelligence project).

    We have added via Reports --> New --> then Selected contract.Rdl (it was contract report) --> selected other relevant fields --> pressed Save and Closed.

    Now reports works fine on this server...when we asked other user to check this reports from their system..

    Surprisingly all the reports for other users stopped working...Whenever user clicks on report tab --we get MS error dialog saying --

    "An error has occurred. for more information, contact your system administrator.

    Try Again or Cancel"

    Can anybody help what's going on in CRM 3.0 over here?

    If anybody has got any solution...Please reply me at Mayank.pujara@iris.co.uk. any help will be much appreciated.

    Thanks

    Mayank

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post