Re: Lookup executes 3 time upon Recordset.Save

Finally found the problem after wasting around 5 hours. The problem occurs
because the recordset is using a server side cursor, I have switched to a
client side cursor and the SQL is now only executed once.

The performance hit of using a client side cursor is much less than
executing my code three times (the code below is simplified fro
demonstration purposes, my actual SQL creates a temp table, selects data
into from a join of around ten tables, paginates and returns the result,
drops the temp table, and finally stores some usage stats).

I have learnt from this problem that one must be VERY careful to check
performance issues when using Recordset.Save

Julian


"Julian Madle" <julian@xxxxxxxxxxxxxxxx> wrote in message
news:uBArxraJGHA.2064@xxxxxxxxxxxxxxxxxxxxxxx
> Getting really strange problem using Recordset.Save: my SQL command gets
> executed three times (tested on SQL Server 2000 and 2005 using both
> sqloledb
> and {SQL Server} drivers):
>
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open("Driver={SQL Server}; Server=localhost; Database=DBname; User
> ID=Username; Password='password'")
> Set RS = objConn.Execute("SET NOCOUNT ON; SELECT ID FROM Products; INSERT
> INTO DebugLog (Message) VALUES ('Done'); SET NOCOUNT OFF")
> RS.Save "c:\output.xml", 1
> Set RS = Nothing
> objConn.Close
> Set objConn = Nothing
>
> When executed (once) there will be three 'Done' messages in the table
> named
> DebugLog. This does not occur when the (exetremly simplified) SQL is
> executed directly inside SQL Server, or when RS.Save is not called. This
> looks like a bug in ADO to me.
>
> Can anybody confirm this behaviour and more importantly suggest a
> solution/workaround?
>
>
> Thanks
> Julian
>


.

Relevant Pages