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: uniqueidentifie r, primary key (newid() IsRowGuide=Yes) ;
- id: uniqueidentifie r, 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:
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(st rsql)
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.
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: uniqueidentifie r, primary key (newid() IsRowGuide=Yes) ;
- id: uniqueidentifie r, 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:
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(st rsql)
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.
Comment