|
|
Posted by Seribus Dragon on 06/01/07 20:36
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.
Navigation:
[Reply to this message]
|