I have been working on an application for some time written in VB running
against SQL Server 7 SP1.

Recently I have had to change the connection string used to connect to the
database so that it no longer uses a DSN. After having made this change, I
am experiencing constant blocking problems on the database.

I have managed to isolate the piece of code and the database structure
required to reproduce this problem, and this is all included below. I would
very much appreciate any assistance that anyone might be able to give me!

The cause of the problem appears to be executing INSERT statements when a
have an open recordset. When the INSERT is executed, a second connection
appears to my database, and this connection is blocked by the original
connection. I am totally puzzled as to why this is happening. When I modify
the connection string to use a DSN as before, this problem does not occur
and the code executes without problem.

Can anyone explain what is going on?

To create the structure required to recreate this problem, create a new
database and execute the following in Query Analyser:


CREATE TABLE [dbo].[PlansEvents] (
[Name] [varchar] (64) NOT NULL ,
[Id] [float] NOT NULL ,
[Description] [varchar] (255) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tracks] (
[Ref] [varchar] (64) NOT NULL ,
[Description] [varchar] (255) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TracksEvents] (
[Ref] [varchar] (64) NOT NULL ,
[ID] [float] NOT NULL ,
[Description] [varchar] (255) NULL ,
[Format] [varchar] (64) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tracks] WITH NOCHECK ADD
CONSTRAINT [PK_Tracks] PRIMARY KEY NONCLUSTERED
(
[Ref]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TracksEvents] ADD
CONSTRAINT [TracksTracksEvents] FOREIGN KEY
(
[Ref]
) REFERENCES [dbo].[Tracks] (
[Ref]
)
GO

INSERT INTO [PlansEvents] ([Name], [ID]) VALUES ('Test',1)
INSERT INTO [PlansEvents] ([Name], [ID]) VALUES ('Test',2)
INSERT INTO [PlansEvents] ([Name], [ID]) VALUES ('Test',3)


Once this is done, create a new VB project, reference ADO (I'm using the ADO
2.5 library but I think this occurs with all versions) and paste the
following code into the Form_Load event:


Dim adoConnection As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

Set adoConnection = New ADODB.Connection
adoConnection.CommandTimeout = 10
adoConnection.Open "Provider=SQLOLEDB;Data Source=Victim1;Initial
Catalog=BlockTest;UID=sa;PWD=nothing"

'Begin the transaction
adoConnection.Execute "BEGIN TRANSACTION MyTrans"

'Create a record in the Tracks table
sql = "INSERT INTO [Tracks] (Ref, Description)"
sql = sql & " VALUES ('TestRef', 'Description')"
adoConnection.Execute sql

'Get a list of records in the PlansEvents table
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM [PlansEvents] WHERE Name = 'Test'", _
adoConnection, adOpenStatic, adLockReadOnly

'For each record retrieved, insert a record into TracksEvents
Do Until rs.EOF
sql = "INSERT INTO [TracksEvents] (Ref, ID, Description)"
sql = sql & " VALUES ('TestRef', " & _
rs("ID") & ", '" & rs("Description") & "')"

'The following line fails to execute
adoConnection.Execute sql

rs.MoveNext
Loop

rs.Close
Set rs = Nothing

adoConnection.Execute "COMMIT TRANSACTION MyTrans"

Set adoConnection = Nothing


(You'll obviously need to modify the connection string to refer to the
database and server that you have set up, rather than those shown in the
code above).

When the code is executed on my system, it runs through to the Execute
method call in the loop, and then fails after the 10 second timeout has
elapsed ("Method 'Execute' of object '_Connection' failed"). It I execute an
sp_who call in Query Analyser before the timeout elapses (increase the
timeout period to make this easier), a second connection has appeared at
this point, and is blocked by the first.

Removing the transaction also stops the problem occurring, but I need the
transaction to be in place.

I would very much like to know:

1. Why is this happening? Why has a second connection appeared? What is
causing it to be blocked?

2. Why does this only occur when using a DSN-less connection string? Is
there something special about a DSN that changes the way the database
behaves? How can I make the DSN-less connection behave in the same way as
that provided by the DSN?

Many thanks in advance,

--

Adam.