Question

Does this statement applied to SQL2K as well ?
Thanks.
...when you use the INSERT...EXEC statement to load a
temporary table, as the stored procedure itself creates
temporary tables, and you end up with blocking locks in
tempdb similar to those described above. The prescribed
workarounds are either "don't do it in the first place",
which is inconvenient if you do not want to mess with
legacy code or code you do not control, or otherwise to
execute the stored procedure as a remote stored
procedure, (i.e. "INSERT #temp EXEC
server.database.owner.proc") which again is not ideal in
all circumstancesLeonid,
Where did you get it from?
Ilya
"Leonid Brehznev" <anonymous@.discussions.microsoft.com> wrote in message
news:2bb601c51e9f$86c92650$a401280a@.phx.gbl...
> Does this statement applied to SQL2K as well ?
> Thanks.
> ...when you use the INSERT...EXEC statement to load a
> temporary table, as the stored procedure itself creates
> temporary tables, and you end up with blocking locks in
> tempdb similar to those described above. The prescribed
> workarounds are either "don't do it in the first place",
> which is inconvenient if you do not want to mess with
> legacy code or code you do not control, or otherwise to
> execute the stored procedure as a remote stored
> procedure, (i.e. "INSERT #temp EXEC
> server.database.owner.proc") which again is not ideal in
> all circumstances|||http://www.databasejournal.com/feat...cle.php/1438061
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:ub2fUV0HFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Leonid,
> Where did you get it from?
> Ilya
> "Leonid Brehznev" <anonymous@.discussions.microsoft.com> wrote in message
> news:2bb601c51e9f$86c92650$a401280a@.phx.gbl...
>|||Hi
I don't think the problem can ever go away totally, but with a better
locking in SQL2000 it is less likely to happen. You will get exclusive key
locks on the system catalogs in tempdb for remaining duration of the
transaction. With previous versions the locking would have been less granula
r.
John
"Leonid Brehznev" wrote:

> Does this statement applied to SQL2K as well ?
> Thanks.
> ...when you use the INSERT...EXEC statement to load a
> temporary table, as the stored procedure itself creates
> temporary tables, and you end up with blocking locks in
> tempdb similar to those described above. The prescribed
> workarounds are either "don't do it in the first place",
> which is inconvenient if you do not want to mess with
> legacy code or code you do not control, or otherwise to
> execute the stored procedure as a remote stored
> procedure, (i.e. "INSERT #temp EXEC
> server.database.owner.proc") which again is not ideal in
> all circumstances
>