SQL Server error "80040e31": please help me!

I have the following problem in a Win 2000 Server + SQL Server 2000
environment and I hope somewhat can help me to resolve it (after many
days of useless attempts I am desperate).

In my database I have two table:
- master(id, field1, field2, ...)
- detail(id0, id, progr, data, sede, esecutori, brani_autori)
in a master-detail relation with "id" as foreign key.

The fields of the "detail" table are:
- id0: uniqueidentifier, primary key (newid() IsRowGuide=Yes);
- id: uniqueidentifier, foreign key;
- progr: bigint, Identity=Yes;
- data: smalldatetime;
- sede: varchar (100);
- esecutori, brani_autori: text.

In certain situations, in my asp site, I have to make a copy of a record
of "master" with all the linked record of the "detail" table.
The code I've written to realize this task has been tested in many
similar situations and has always worked fine (it is reported on the end
of mail).
With the two table above I have this strange behavior: when I attempt to
do the copy of linked records in the "detail" table (using an "Insert"
query), some records are correctly inserted, whereas for few other
records the Conn.Execute of the "Insert" query don't go and I receive
the message:

Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired

After many attempts I've reached these conclusions:

1.
It isn't the situation described in this faq:
http://www.aspfaq.com/show.asp?id=2287
because also using the IP for the "Data Source" the situation is the
same; on the other hand I have the problem also when there is only one
record to copy in the "detail" table, so the problem is not the duration
of the query.

2.
It isn't due to the contents of the record (at least not directly): if I
substitute the contents of one of this records with simple text the
error persists, if I create manually a new record in "detail" and put in
it the data of the indicted record it is copied normally. So the problem
seems to be the record itself and not its contents.

3.
The insert query work normally if I execute it from the Query Analizer.

4.
The problem seems to be due to the fact that, when I have a recordset
object open on the table and pointed to one of this records, SQL Server
blocks the table and don't permit new insertion; in fact if I execute
the same Insert query out of the code where the recordset object is open
it works.


Finally if the problem is the one of the point 4, I don't know the
reason of this behaviour and how to resolve it.

So, please, help me because it is of great importance for my work!

Many, many thanks
Tonio Tanzi


*** Code of the copy procedure ***
....
old_id_master= 'the id of the master record to copy
new_id_master= 'the id of new master record (copy of the above)

strsql="Select * From detail where id='" & old_id_master & "'"
set rs=Conn.Execute(strsql)
do while not rs.Eof
strsql="Insert Into detail (id, data, sede, esecutori, brani_autori)"
& " Values ('" & new_id_master & "','" & data & "','" & _
sede "','" & esecutori & "','" & brani_autori & "')"
Conn.Execute(strsql)
rs.movenext
loop
rs.close

This code works good for the "good" records, don't words for the "bad"
records, but if I force an insert for a "bad" record before or after the
do while-loop (i.e. when the rs is not pointed on a "bad" record) it works.

Re: SQL Server error "80040e31": please help me!

there is a HINT Command in Trans-SQL that Allows you to tell the RS not
to lock the rows.
select * from detail with (nolock)
i think that would solve the problem.

Tonio Tanzi wrote:
> I have the following problem in a Win 2000 Server + SQL Server 2000
> environment and I hope somewhat can help me to resolve it (after many
> days of useless attempts I am desperate).
>
> In my database I have two table:
> - master(id, field1, field2, ...)
> - detail(id0, id, progr, data, sede, esecutori, brani_autori)
> in a master-detail relation with "id" as foreign key.
>
> The fields of the "detail" table are:
> - id0: uniqueidentifier, primary key (newid() IsRowGuide=Yes);
> - id: uniqueidentifier, foreign key;
> - progr: bigint, Identity=Yes;
> - data: smalldatetime;
> - sede: varchar (100);
> - esecutori, brani_autori: text.
>
> In certain situations, in my asp site, I have to make a copy of a record
> of "master" with all the linked record of the "detail" table.
> The code I've written to realize this task has been tested in many
> similar situations and has always worked fine (it is reported on the end
> of mail).
> With the two table above I have this strange behavior: when I attempt to
> do the copy of linked records in the "detail" table (using an "Insert"
> query), some records are correctly inserted, whereas for few other
> records the Conn.Execute of the "Insert" query don't go and I receive
> the message:
>
> Microsoft OLE DB Provider for SQL Server error '80040e31'
> Timeout expired
>
> After many attempts I've reached these conclusions:
>
> 1.
> It isn't the situation described in this faq:
> http://www.aspfaq.com/show.asp?id=2287
> because also using the IP for the "Data Source" the situation is the
> same; on the other hand I have the problem also when there is only one
> record to copy in the "detail" table, so the problem is not the duration
> of the query.
>
> 2.
> It isn't due to the contents of the record (at least not directly): if I
> substitute the contents of one of this records with simple text the
> error persists, if I create manually a new record in "detail" and put in
> it the data of the indicted record it is copied normally. So the problem
> seems to be the record itself and not its contents.
>
> 3.
> The insert query work normally if I execute it from the Query Analizer.
>
> 4.
> The problem seems to be due to the fact that, when I have a recordset
> object open on the table and pointed to one of this records, SQL Server
> blocks the table and don't permit new insertion; in fact if I execute
> the same Insert query out of the code where the recordset object is open
> it works.
>
>
> Finally if the problem is the one of the point 4, I don't know the
> reason of this behaviour and how to resolve it.
>
> So, please, help me because it is of great importance for my work!
>
> Many, many thanks
> Tonio Tanzi
>
>
> *** Code of the copy procedure ***
> ...
> old_id_master= 'the id of the master record to copy
> new_id_master= 'the id of new master record (copy of the above)
>
> strsql="Select * From detail where id='" & old_id_master & "'"
> set rs=Conn.Execute(strsql)
> do while not rs.Eof
> strsql="Insert Into detail (id, data, sede, esecutori, brani_autori)"
> & " Values ('" & new_id_master & "','" & data & "','" & _
> sede "','" & esecutori & "','" & brani_autori & "')"
> Conn.Execute(strsql)
> rs.movenext
> loop
> rs.close
>
> This code works good for the "good" records, don't words for the "bad"
> records, but if I force an insert for a "bad" record before or after the
> do while-loop (i.e. when the rs is not pointed on a "bad" record) it works.

Re: SQL Server error "80040e31": please help me!

Tonio Tanzi (t.tanzi@alice.it) writes:
> In certain situations, in my asp site, I have to make a copy of a record
> of "master" with all the linked record of the "detail" table.
> The code I've written to realize this task has been tested in many
> similar situations and has always worked fine (it is reported on the end
> of mail).
> With the two table above I have this strange behavior: when I attempt to
> do the copy of linked records in the "detail" table (using an "Insert"
> query), some records are correctly inserted, whereas for few other
> records the Conn.Execute of the "Insert" query don't go and I receive
> the message:
>
> Microsoft OLE DB Provider for SQL Server error '80040e31'
> Timeout expired
>...
>
> *** Code of the copy procedure ***
> ...
> old_id_master= 'the id of the master record to copy
> new_id_master= 'the id of new master record (copy of the above)
>
> strsql="Select * From detail where id='" & old_id_master & "'"
> set rs=Conn.Execute(strsql)
> do while not rs.Eof
> strsql="Insert Into detail (id, data, sede, esecutori, brani_autori)"
> & " Values ('" & new_id_master & "','" & data & "','" & _
> sede "','" & esecutori & "','" & brani_autori & "')"
> Conn.Execute(strsql)
> rs.movenext
> loop
> rs.close

The most likely reason for your problem is that you are not running with
SET NOCOUNT ON, and when you fail to pick up the rowcount, ADO opens a
second connection behind your back, and then you block yourself.

However, the code you have is not very good. There is no reason to run
a loop to get all data up to the client just to shove it back again.
You can copy all in one statement. Furthermore you should learn to
use parameterised commands and stop interpolating parameters directly
into your SQL strings.

cmd.txt = "INSERT detail(id, data, sede, esecutori, brani_autori) " & _
"SELECT ?, data, sede, esecutori, brani_autori " & _
"FROM detail " & _
"WHERE id = ?"
cmd.CreateParameter "@new_master_id", adGUID, adParamInput,, new_id_master
cmd.CreateParameter "@new_master_id", adGUID, adParamInput,, old_id_master
cmd.Execute


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL Server error "80040e31": please help me!

Seribus Dragon ha scritto:
>
>
> there is a HINT Command in Trans-SQL that Allows you to tell the RS not
> to lock the rows.
> select * from detail with (nolock)
> i think that would solve the problem.

Thanks for the solution, however I've solved with this query:

insert into detail (id, data, sede, esecutori, brani_autori)
select new_id_master, data, sede, esecutori, brani_autori
from detail where id= old_id_master

Tonio Tanzi

Re: SQL Server error "80040e31": please help me!

Erland Sommarskog ha scritto:
> The most likely reason for your problem is that you are not running with
> SET NOCOUNT ON, and when you fail to pick up the rowcount, ADO opens a
> second connection behind your back, and then you block yourself.
>
> However, the code you have is not very good. There is no reason to run
> a loop to get all data up to the client just to shove it back again.
> You can copy all in one statement. Furthermore you should learn to
> use parameterised commands and stop interpolating parameters directly
> into your SQL strings.
>
> cmd.txt = "INSERT detail(id, data, sede, esecutori, brani_autori) " & _
> "SELECT ?, data, sede, esecutori, brani_autori " & _
> "FROM detail " & _
> "WHERE id = ?"
> cmd.CreateParameter "@new_master_id", adGUID, adParamInput,, new_id_master
> cmd.CreateParameter "@new_master_id", adGUID, adParamInput,, old_id_master
> cmd.Execute

Thanks for the help and the explanation, I've solved with this query:

insert into detail (id, data, sede, esecutori, brani_autori)
select new_id_master, data, sede, esecutori, brani_autori
from detail where id= old_id_master

it's a not parametrized version of your solution, but I will try also
the parametrized one.

Thanks a lot

Tonio Tanzi