Common table expression error

Question

I need to use an old school ADODB (not ADO.NET) to execute a statement containing a Common Table expression. I use (should use) the SQLOLEDB provider. The DML operator works great when working with a Windows 7 / Windows Server 2008 client, but not from a WinXP or Win2K3 server. I profiled the procedure and found that older OSs 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 ...' 

Note that an additional "exec" has leaked into the command text.

It seems that versions of SQLOLEDB.1 on older operating systems do not correctly handle the WITH statement and see that it needs a preliminary "exec".

Can someone shed some light on this. Is there an SQLOLEDB driver update that I can apply to older OS? or some other workaround.

Answer 1

(FYI, you really should review some of your existing questions, since most of them seem to have useful answers that seem to address the question, your comments even suggest that it is. If they have an answer, please accept it).

If you really need to use CTE here (this means that you are doing something recursive and not just using it for convenience, and not for in-line selection or inline connection), the easiest and fastest solution would probably be to include your SQL in your own call to sp_executesql . You end up putting challenges into it (which would look silly), but this should not cause any real problems.

Answer 2

The flow around the query in the sp_executesql expression works fine if there are no parameters in the query, otherwise the parameters are not parsed, because they fall into the quotation line by the time they get into ADO, and this leads to a syntax error.

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

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

LocalQueryChanged then checks to see if the query starts with a common table expression and inserts a dummy string declaration at the beginning of the query that the XP ADO parser understands. CTE must be preceded by a semicolon if this is not the first statement in the request, so we must fix this 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 solved the problem and saved me from having to recycle all my code, where I use both CTE and parameters.