Thanks.. this hint was sufficient to get the answer..

agreed with Gail but does it not involves in creating deadlock or blocking other objects?

we call this proc at front end from one of the report and this report used...

Yes we can write but you do you think it would be helpful.Paul's blog suggest that it would not be required.

http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx

thanks for your suggestions..

Thanks for your replies. I used few of your suggestions

1.Applied unique cluster index on temp table on COUNT,MESS columns.

2.Used group by while inserting the data into temp table

3.Remove distinct from...

I can not create cluster because it has duplicate records

It worked. Thanks .. I appreciate your help.

SET @sql='SELECT EVENTUID,EVENTTIMESTAMP,EVENTMAJORTYPE,EVENTMINORTYPE INTO #TEMP

FROM ' + @WgnWCUser + '.EMPLOYEE A WITH(NOLOCK)

WHERE A.'+@TIMESTAMP+' >= ''' +convert(varchar(12), @datFrom, 112) + '''...

Got the solution.. leave it..

solution is like below

WHEN CAST(A.'+@TIMESTAMP+' AS DATE) >= CAST('''+@Wkstr1+''' AS DATE)

Many Thanks Mike.. it would be useful.

I want to set start of week and end of week date in different variable like below..

If i want to do for 12 weeks, for that i need to write...

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near '+'.

I took a variable and stored all dynamic columns on that an used same on pivot query.

declare @sql nvarchar(500)

DECLARE @col NVARCHAR(300)

SELECT @col = ISNULL(@col + ', ',...

Got the solution.. Thanks

Thanks. The problem got resolved. I have created new text box and put on new rectangle and deleted the existing one.

I got same error and tried to reprocess full cube. But the problem is still there