Common Table Expression Error

I have to use old school ADODB (not ADO.NET) to execute a statement that contains a Common Table Expression. I am using (have to use) the SQLOLEDB provider. T开发者_开发技巧he DML statement works fine when executing from a Windows 7 / Windows Server 2008 client but not from WinXP or Win2K3 server. I have profiled the routine and found that the old OSes send a slightly different SQL statement.

Win7 + 2008 =    exec sp_executesql N'WITH source(Vsl, Cpt, SrcTyp, SrcNum, Opn, JobNum, Qty, Cst, Vry, Reg, Vnt, Sbk) AS ...'

WinXP + Win2K3 = exec sp_executesql N'exec WITH source(Vsl, Cpt, SrcTyp, SrcNum, Opn, JobNum, Qty, Cst, Vry, Reg, Vnt, Sbk) AS ...'

Notice the extra 'exec' slipped into the command text.

It appears as if the verions of SQLOLEDB.1 on the old OSs mis-treats the WITH statement and sees it as needing a prepending 'exec'.

Can anyone shed some light on this. Is there an SQLOLEDB driver update that I can apply to the old OSes? or some other workaround.

---

(FYI, You should really revisit some of your existing questions, as most of them seem to have helpful answers that appear to address the question; your comments even suggest this is so. If they have an answer, please accept it).

If you really need to use a CTE here (meaning you're doing something recursive and aren't just using it for convenience instead of inline-selecting or inline-joining), the simplest and fastest workaround would probably be to include your SQL within your own call to sp_executesql. You'll end up nesting calls to it (which will look silly), but it shouldn't cause any actual problems.

---

Wrapping the query up in an sp_executesql statement works great if you don't have parameters in the query, otherwise the parameters aren't parsed because they're in a quoted string by the time they get to ADO, and this results in a syntax error.

What I did to resolve this was to create a TADOQuery descendant, which overrides the constructor, as follows:

constructor TCPADOQuery.Create(AOwner: TComponent);
begin
  inherited;
  TWideStringList(SQL).OnChange := LocalQueryChanged;
end;

LocalQueryChanged then checks if the query starts with a common table expression, and inserts a dummy declaration at the beginning of the query, which the XP ADO parser does understand. A CTE must be preceded by a semicolon if it is not the first statement in the query, so we have to fix that first:

procedure TCPADOQuery.LocalQueryChanged(Sender: TObject);
var
  a: WideString;
begin
  if not (csLoading in ComponentState) then
    Close;
  a := Trim(SQL.Text);
  if Uppercase(copy(a, 1, 4)) = 'WITH' then a := ';' + a;
  if Uppercase(copy(a, 1, 5)) = ';WITH' then
    a := 'DECLARE @DummyForADO_XP BIT'#13#10 + a;
  CommandText := a;
end;

This has resolved the problem, and has saved me having to rework all of my code where I use both CTEs and parameters.