Its tricky but if you look at Bobs post above on calculated columns ...

you want your dynamic SQL to dynamically create a the final column as

( [Date1] + [Date2]...

In my experience CLR functions are faster than the equivalent SQL - However I've only used CLR functions for very complex queries that lend themselves to procedural processeing rather than...

Hmm, no clue from the error message then

but if its the XML PATH part thats causing the problem you could replace it with the old simple SQL 2000 way...

There's no set 'best way'.

Try the join, examine your query's execution plan - try your correlated subquery - and compare the execution plan.

Pick the best plan. Note sometimes...

I tried what you did before and got loads of errors - must have got a typo somewhere. :blush:

For totals - do you mean a separate total line at the...

russ.thomas (5/13/2009)

---

I'm using Crystal 2008, SQL Server 2008 and Visual Studio 2008.

Here is the exact error message I get when I run the crystal report while debugging within VS. ...

For Bob's version, I guess you'd want to pre-sum the data before it gets to the dynamic query

SELECT SUM(Amount) AS Amount, Name, ID, JobID, Date

INTO #Sample2

FROM #Sample

GROUP BY Name, ID,...

Hi martin

This is quite a long post, as the code is in twice - first the complete code: - its broken down after, clause by clause. I hope I...

Hi Flo,

No misunderstanding, I was looking for a built-in approach, but have learned that T-SQL doesn't have one, so I finished my dynamic SQL. It seemed natural to post...

Brilliant feature 😀

Can you update the IFCode Shortcuts, and IFCode popup window http://www.sqlservercentral.com/Forums/Skins/Classic/Dialogs/InsertIFCode.aspx?ControlID=txtPos

thx

Tom

Hi Flo,

It was quite simple to get the dynamic version working - so I didn't need to venture into SSAS or Excel.

CREATE TABLE #TMP1 (

ADate datetime,

Amount float,

Name varchar(30) );

INSERT...

I have been working on a very similar problem, and was about to post when I noticed martin's problem was virtually the same,

I noticed Flo has hard-coded the dates...

Is there anything in the Windows event log?

I had one recently - the eventlog said something like deadlock query sp_xxx selected for termination

Just a thought, check the database compatibility level

I know a similar error to this happens in SQL2005 if you try to diagram a level 80 (SQL2000) database.

I don't have 2008...

could it be permissions?

Perhaps you created the proc in your user, and its not accessible to the user agent for scheduled jobs?

Or maybe you just need to decorate the proc...