October 8, 2002 at 5:51 pm
Weird problem of the month award candidate.
In calling SQL2000 from asp pages I ran across this, and have reproduced it in a vbscript below. Change the connection string to be a database to which you have access to create a table. The table (LEF) will leave a trail. If you look at the SQL code there, what you would expect is each time it is executed it returns a message box with one row, and show insert one row with one date/time stamp in the table lef.
Run it and you will find six rows. At least I do, on a W2K/SP2 system running SQL2000/SP2 standard on a quad CPU Intel system. Actually two different systems (same configuration), and my own system W2K/SP3 with same SQL and one CPU.
If you change to adOpenForwardOnly it then works fine, and only executes once.
I discovered this on a longer procedure where it was doing a lot of work SIX TIMES and I couldn't figure out why it was so slow. I stared at the rest of the code for ages before realizing it was actually happening inside the one recordset open statement.
I think it's a bug, and a serious one of some sort. I would appreciate it if someone else could confirm it happens for them as well, and that it's not something I'm imagining.
Linwood
PS. Sorry if this wraps, hopefully it's obvious where line breaks are and are not. It shoudl just run as a vbscript from a command prompt, once you change the connect string, on W2k (not sure of other versions).
PPS. I'm not completely sure what MDAC I'm running, whatever comes with .Net now.
adOpenForwardOnly = 0
adOpenStatic=3
adLockReadOnly = 1
set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=dw;Data Source=SRV-DW\SQL2000;"
conn.Open
sql = "if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lef]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " & vbcrlf & _
" CREATE TABLE [lef] ([dt] [datetime]) " & vbcrlf & _
"SELECT 1 as Data1, 2 as Data2 " & vbcrlf & _
"Insert into lef select Getdate()"
set rs = CreateObject("ADODB.Recordset")
rs.Open sql, conn, AdOpenStatic , adLockReadOnly
msgbox "Data1=" & rs.fields("Data1") & ", Data2=" & rs.fields("Data2")
rs.close
conn.close
set rs=nothing
set conn=nothing
Edited by - Ferguson on 10/08/2002 5:52:57 PM
October 8, 2002 at 6:07 pm
I can duplicate it with MDAC 2.6 and 2.7. Running the sql it builds up in QA works fine, you get one row. Each time you run via the code you get 6 rows added, verified with Profiler that it sends 6 batches. Nothing in ADO errors collection. Interesting.
Andy
October 9, 2002 at 4:24 am
I can also confirm this, running SQL Server 2000 Ent Ed SP2, MDAC 2.7 on client. I profiled what was going on and it is the entire statement that is executed several times. If you remove the existance test for the table you will get an error that the table already exists, even if it doesn't exist before you execute your code. The first time the statement executes that table is created and a row inserted, then it (somehow) tries to execute the statement again and the error occurs. You can also see this by the datetimevalues not being exactly the same, if it would have been a single statement the times would be exactly the same since a set-based operation uses a single getdate for all rows of the set.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
October 9, 2002 at 4:31 am
After getting some confirmation I called Microsoft (no point in wasting a support call if I was doing something stupid).
Interestingly in talking with them we tried keyset and dynamic, which behave slightly differently (I think they do it 4 or 5 times, not 6).
If you do not include a SELECT at all, it (correctly) interprets it as not returning a recordset and works (only executes once). I think that's because it never actually opens a recordset since there isn't one returned.
I tried it on SQL7 (I forgot I still had one buried out there) and it worked the same.
They are looking at it, thinking it is a bug. However, this surprises me. Is this an uncommon thing to do? We have quite a bit of web page code where we execute "a" SQL statement to get values, and frequently that statement looks like this:
Select stuff into #x from somewhere
Insert into #x from elsewhere
Do stuff to #x
Select stuff from #x
where we go through multiple steps to yield the final result. A lot of this SQL is built on the fly based on the current need, that's why it isn't a stored procedure. Works fine. However, now I understand why some of these take a lot longer to run than one would expect!
I'm just really surprised, if this is commonly done, no one has stumbled across this? Or is it very uncommon to put multiple SQL statements into a recordset open?
October 9, 2002 at 4:46 am
Not sure on this and am trying to find the details but I believe the changes during the query are causing one of the collections objects to update and force a resync during run. Try this as I don't have a moment right now to check and see what happens.
set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=dw;Data Source=SRV-DW\SQL2000;"
conn.Open
sql = "if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lef]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " & vbcrlf & _
" CREATE TABLE [lef] ([dt] [datetime]) "
conn.Execute(sql)
sql = "SELECT 1 as Data1, 2 as Data2 "
set rs = CreateObject("ADODB.Recordset")
rs.Open sql, conn, AdOpenStatic, adLockReadOnly
conn.Execute("Insert into lef select Getdate()")
msgbox "Data1=" & rs.fields("Data1") & ", Data2=" & rs.fields("Data2")
rs.close
conn.close
set rs=nothing
set conn=nothing
Edited by - antares686 on 10/09/2002 04:46:58 AM
October 9, 2002 at 5:34 pm
I'd guess it is a bit unusual. Typically my recordsets are built from the results of a proc. Possibly the proc builds a temp table, but I wouldnt pass it over as part of the sql. We do use multiple recordsets a lot, either via proc or by passing semi colon delimited selects, both work fine (other than you have to know by ordinal which rs is rs...no way to name them!).
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply