The optimizer, along with a whole of the rest of the engine, was changed between 2000 and 2005. It's not at all surprising that you'll see different execution plans in...

There's not a good source for this information. Experimentation is how I'm getting by.

I can recommend three sources that can help, in addition to posting here (althouth only a...

F'ing lovely. Worked perfect. Thank you.

Now what the heck did you do?:w00t:

I think the frequency is also going to affect this. You would have to check often enough to catch the event as it occurs.

Different people do it different ways. You can control the transaction from the application, but you need to be very cautious about getting extraneous code in the way of the...

Sorry, I only posted part of the XML. It's the XML from an execution plan. Here's a section of the entire XML file (brackets replaced):

[ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.3042.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"]

...

OK. It ran, but it's returning null for all the values and that's wrong. A little more help please?

OK. I think I get why you're using the [1] twice in the first example, but what do the parenthesis do in the second?

Oh, and thanks for the help.

You're stuck. You either need to move to 2005 & use VARCHAR(MAX) or stay in 2000 and use TEXT. There's no in between.

DB2 supports (MAX), but I'm not sure about...

Look into using the NODES method. Here's an example from BOL:

SELECT C.query('.') as result

FROM Production.ProductModel

CROSS APPLY Instructions.nodes('

declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";

/MI:root/MI:Location') as T(C)

WHERE ProductModelID=7

Congratulations. Good work.

Provisional-DBA? But that'd be true & not funny.

Uhm... wanna-be-DBA?

OK, but what did it mean?

You're hitting a variation of parameter sniffing (here's Ken Henderson's description of parameter sniffing). Usually this is a good thing, but sometimes it isn't. Ken shows a method for helping,...

I'd suggest two things, first, get an estimated execution plan with the parameters and compare it to an actual execution plan without. Also, because you're setting your date values to...