I need help with next problem:
Exist table my_rows with next columns:
id - int identity
name - varchar
is_locked - bit
I need create store procedure which will return every time diferent
row for every request from different processes.
the pseudo code for the procedure :
1 select statement is :
select @.id = selct top 1 id from my_rows where is_locked =0
2 update my_rows set is_locked = 1 where id=@.id
3 return @.id
In different words : if in same time I call this procedure from 2
different connections, it will return 2 different record.
I also want that first call to procedure will not generate lock error
for second call in same time(just second call will wait for finish
first , it is ok.)
ThanksHi
You select and update statement should be contained within one transaction
and you can use the UPDLOCK hint on the select statement to stop others
returning that value
CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
SET NOCOUNT ON
BEGIN TRANSACTION
SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
-- Error checking
UPDATE my_rows SET is_locked = 1 WHERE id=@.id
-- Error checking
COMMIT TRANSACTION
RETURN
Alternatively you could use
UPDATE my_rows
SET is_locked = 1
WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
but you would not know the ID that was updated.
Using this type of locking can potentially cause a bottleneck and poor
performance.
John
"is_vlb50@.hotmail.com" wrote:
> I need help with next problem:
> Exist table my_rows with next columns:
> id - int identity
> name - varchar
> is_locked - bit
> I need create store procedure which will return every time diferent
> row for every request from different processes.
> the pseudo code for the procedure :
> 1 select statement is :
> select @.id = selct top 1 id from my_rows where is_locked =0
> 2 update my_rows set is_locked = 1 where id=@.id
> 3 return @.id
> In different words : if in same time I call this procedure from 2
> different connections, it will return 2 different record.
> I also want that first call to procedure will not generate lock error
> for second call in same time(just second call will wait for finish
> first , it is ok.)
> Thanks
>|||On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> You select and update statement should be contained within one transaction
> and you can use the UPDLOCK hint on the select statement to stop others
> returning that value
> CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> SET NOCOUNT ON
> BEGIN TRANSACTION
> SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> -- Error checking
> UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> -- Error checking
> COMMIT TRANSACTION
> RETURN
> Alternatively you could use
> UPDATE my_rows
> SET is_locked = 1
> WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> but you would not know the ID that was updated.
> Using this type of locking can potentially cause a bottleneck and poor
> performance.
> John
>
> "is_vl...@.hotmail.com" wrote:
> > I need help with next problem:
> > Exist table my_rows with next columns:
> > id - int identity
> > name - varchar
> > is_locked - bit
> > I need create store procedure which will return every time diferent
> > row for every request from different processes.
> > the pseudo code for the procedure :
> > 1 select statement is :
> > select @.id = selct top 1 id from my_rows where is_locked =0
> > 2 update my_rows set is_locked = 1 where id=@.id
> > 3 return @.id
> > In different words : if in same time I call this procedure from 2
> > different connections, it will return 2 different record.
> > I also want that first call to procedure will not generate lock error
> > for second call in same time(just second call will wait for finish
> > first , it is ok.)
> > Thanks- Hide quoted text -
> - Show quoted text -
You wroute:
SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
=0 )
-- Error checking
Could you explain what is reason for "Error checking " and what I can
do.
If some body in same time call same procedure , I can get error ?
Thanks|||Hi
Read http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html which gives you a very good
idea of what to do. If you follow Erlands advice you would also have checked
for errors when you commit the transaction and have code to rollback.
John
"is_vlb50@.hotmail.com" wrote:
> On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > You select and update statement should be contained within one transaction
> > and you can use the UPDLOCK hint on the select statement to stop others
> > returning that value
> >
> > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> > SET NOCOUNT ON
> > BEGIN TRANSACTION
> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> > -- Error checking
> > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> > -- Error checking
> > COMMIT TRANSACTION
> > RETURN
> >
> > Alternatively you could use
> >
> > UPDATE my_rows
> > SET is_locked = 1
> > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> >
> > but you would not know the ID that was updated.
> >
> > Using this type of locking can potentially cause a bottleneck and poor
> > performance.
> >
> > John
> >
> >
> >
> > "is_vl...@.hotmail.com" wrote:
> > > I need help with next problem:
> > > Exist table my_rows with next columns:
> > > id - int identity
> > > name - varchar
> > > is_locked - bit
> >
> > > I need create store procedure which will return every time diferent
> > > row for every request from different processes.
> >
> > > the pseudo code for the procedure :
> > > 1 select statement is :
> > > select @.id = selct top 1 id from my_rows where is_locked =0
> > > 2 update my_rows set is_locked = 1 where id=@.id
> > > 3 return @.id
> >
> > > In different words : if in same time I call this procedure from 2
> > > different connections, it will return 2 different record.
> > > I also want that first call to procedure will not generate lock error
> > > for second call in same time(just second call will wait for finish
> > > first , it is ok.)
> > > Thanks- Hide quoted text -
> >
> > - Show quoted text -
> You wroute:
> SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
> =0 )
> -- Error checking
> Could you explain what is reason for "Error checking " and what I can
> do.
> If some body in same time call same procedure , I can get error ?
> Thanks
>
>
>|||On Sep 26, 6:02 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> Readhttp://www.sommarskog.se/error-handling-I.htmlandhttp://www.sommarskog.se/error-handling-II.htmlwhich gives you a very good
> idea of what to do. If you follow Erlands advice you would also have checked
> for errors when you commit the transaction and have code to rollback.
> John
>
> "is_vl...@.hotmail.com" wrote:
> > On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > > Hi
> > > You select and update statement should be contained within one transaction
> > > and you can use the UPDLOCK hint on the select statement to stop others
> > > returning that value
> > > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> > > SET NOCOUNT ON
> > > BEGIN TRANSACTION
> > > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> > > -- Error checking
> > > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> > > -- Error checking
> > > COMMIT TRANSACTION
> > > RETURN
> > > Alternatively you could use
> > > UPDATE my_rows
> > > SET is_locked = 1
> > > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> > > but you would not know the ID that was updated.
> > > Using this type of locking can potentially cause a bottleneck and poor
> > > performance.
> > > John
> > > "is_vl...@.hotmail.com" wrote:
> > > > I need help with next problem:
> > > > Exist table my_rows with next columns:
> > > > id - int identity
> > > > name - varchar
> > > > is_locked - bit
> > > > I need create store procedure which will return every time diferent
> > > > row for every request from different processes.
> > > > the pseudo code for the procedure :
> > > > 1 select statement is :
> > > > select @.id = selct top 1 id from my_rows where is_locked =0
> > > > 2 update my_rows set is_locked = 1 where id=@.id
> > > > 3 return @.id
> > > > In different words : if in same time I call this procedure from 2
> > > > different connections, it will return 2 different record.
> > > > I also want that first call to procedure will not generate lock error
> > > > for second call in same time(just second call will wait for finish
> > > > first , it is ok.)
> > > > Thanks- Hide quoted text -
> > > - Show quoted text -
> > You wroute:
> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
> > =0 )
> > -- Error checking
> > Could you explain what is reason for "Error checking " and what I can
> > do.
> > If some body in same time call same procedure , I can get error ?
> > Thanks- Hide quoted text -
> - Show quoted text -
Thanks,
it was very helpful. One last question:
if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
call in store procedure can raise error because first call in first SP
still running (still locks the record) or it just will receive another
row?
Thanks|||> if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> call in store procedure can raise error because first call in first SP
> still running (still locks the record) or it just will receive another
> row?
TOP 1 can give you *any* row. If the row decided for is locked already by an incompatible lock, then
you will be blocked. If you want some other row, you might wan to check out the READPAST hint.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<is_vlb50@.hotmail.com> wrote in message news:1190831328.803233.199140@.19g2000hsx.googlegroups.com...
> On Sep 26, 6:02 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>> Hi
>> Readhttp://www.sommarskog.se/error-handling-I.htmlandhttp://www.sommarskog.se/error-handling-II.htmlwhich
>> gives you a very good
>> idea of what to do. If you follow Erlands advice you would also have checked
>> for errors when you commit the transaction and have code to rollback.
>> John
>>
>> "is_vl...@.hotmail.com" wrote:
>> > On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
>> > > Hi
>> > > You select and update statement should be contained within one transaction
>> > > and you can use the UPDLOCK hint on the select statement to stop others
>> > > returning that value
>> > > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
>> > > SET NOCOUNT ON
>> > > BEGIN TRANSACTION
>> > > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
>> > > -- Error checking
>> > > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
>> > > -- Error checking
>> > > COMMIT TRANSACTION
>> > > RETURN
>> > > Alternatively you could use
>> > > UPDATE my_rows
>> > > SET is_locked = 1
>> > > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
>> > > but you would not know the ID that was updated.
>> > > Using this type of locking can potentially cause a bottleneck and poor
>> > > performance.
>> > > John
>> > > "is_vl...@.hotmail.com" wrote:
>> > > > I need help with next problem:
>> > > > Exist table my_rows with next columns:
>> > > > id - int identity
>> > > > name - varchar
>> > > > is_locked - bit
>> > > > I need create store procedure which will return every time diferent
>> > > > row for every request from different processes.
>> > > > the pseudo code for the procedure :
>> > > > 1 select statement is :
>> > > > select @.id = selct top 1 id from my_rows where is_locked =0
>> > > > 2 update my_rows set is_locked = 1 where id=@.id
>> > > > 3 return @.id
>> > > > In different words : if in same time I call this procedure from 2
>> > > > different connections, it will return 2 different record.
>> > > > I also want that first call to procedure will not generate lock error
>> > > > for second call in same time(just second call will wait for finish
>> > > > first , it is ok.)
>> > > > Thanks- Hide quoted text -
>> > > - Show quoted text -
>> > You wroute:
>> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
>> > =0 )
>> > -- Error checking
>> > Could you explain what is reason for "Error checking " and what I can
>> > do.
>> > If some body in same time call same procedure , I can get error ?
>> > Thanks- Hide quoted text -
>> - Show quoted text -
> Thanks,
> it was very helpful. One last question:
> if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> call in store procedure can raise error because first call in first SP
> still running (still locks the record) or it just will receive another
> row?
> Thanks
>|||On Sep 26, 8:40 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> > call in store procedure can raise error because first call in first SP
> > still running (still locks the record) or it just will receive another
> > row?
> TOP 1 can give you *any* row. If the row decided for is locked already by an incompatible lock, then
> you will be blocked. If you want some other row, you might wan to check out the READPAST hint.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> <is_vl...@.hotmail.com> wrote in messagenews:1190831328.803233.199140@.19g2000hsx.googlegroups.com...
> > On Sep 26, 6:02 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> >> Hi
> >> Readhttp://www.sommarskog.se/error-handling-I.htmlandhttp://www.sommarsko...
> >> gives you a very good
> >> idea of what to do. If you follow Erlands advice you would also have checked
> >> for errors when you commit the transaction and have code to rollback.
> >> John
> >> "is_vl...@.hotmail.com" wrote:
> >> > On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> >> > > Hi
> >> > > You select and update statement should be contained within one transaction
> >> > > and you can use the UPDLOCK hint on the select statement to stop others
> >> > > returning that value
> >> > > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> >> > > SET NOCOUNT ON
> >> > > BEGIN TRANSACTION
> >> > > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> >> > > -- Error checking
> >> > > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> >> > > -- Error checking
> >> > > COMMIT TRANSACTION
> >> > > RETURN
> >> > > Alternatively you could use
> >> > > UPDATE my_rows
> >> > > SET is_locked = 1
> >> > > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> >> > > but you would not know the ID that was updated.
> >> > > Using this type of locking can potentially cause a bottleneck and poor
> >> > > performance.
> >> > > John
> >> > > "is_vl...@.hotmail.com" wrote:
> >> > > > I need help with next problem:
> >> > > > Exist table my_rows with next columns:
> >> > > > id - int identity
> >> > > > name - varchar
> >> > > > is_locked - bit
> >> > > > I need create store procedure which will return every time diferent
> >> > > > row for every request from different processes.
> >> > > > the pseudo code for the procedure :
> >> > > > 1 select statement is :
> >> > > > select @.id = selct top 1 id from my_rows where is_locked =0
> >> > > > 2 update my_rows set is_locked = 1 where id=@.id
> >> > > > 3 return @.id
> >> > > > In different words : if in same time I call this procedure from 2
> >> > > > different connections, it will return 2 different record.
> >> > > > I also want that first call to procedure will not generate lock error
> >> > > > for second call in same time(just second call will wait for finish
> >> > > > first , it is ok.)
> >> > > > Thanks- Hide quoted text -
> >> > > - Show quoted text -
> >> > You wroute:
> >> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
> >> > =0 )
> >> > -- Error checking
> >> > Could you explain what is reason for "Error checking " and what I can
> >> > do.
> >> > If some body in same time call same procedure , I can get error ?
> >> > Thanks- Hide quoted text -
> >> - Show quoted text -
> > Thanks,
> > it was very helpful. One last question:
> > if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> > call in store procedure can raise error because first call in first SP
> > still running (still locks the record) or it just will receive another
> > row?
> > Thanks- Hide quoted text -
> - Show quoted text -
In this case suggestion of John is not valid, because as I described
at start post,I need solution which will in every call to SP in same
time will return back a different row without any block.
Thanks|||On Wed, 26 Sep 2007 18:50:43 -0000, is_vlb50@.hotmail.com wrote:
>In this case suggestion of John is not valid, because as I described
>at start post,I need solution which will in every call to SP in same
>time will return back a different row without any block.
Hi is_vlb50,
As Tibor already mentioned, the READPAST hint can help you achieve what
you need. You'll find all the details in Books Online.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Sep 27, 12:46 am, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Wed, 26 Sep 2007 18:50:43 -0000, is_vl...@.hotmail.com wrote:
> >In this case suggestion of John is not valid, because as I described
> >at start post,I need solution which will in every call to SP in same
> >time will return back a different row without any block.
> Hi is_vlb50,
> As Tibor already mentioned, the READPAST hint can help you achieve what
> you need. You'll find all the details in Books Online.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
the readpast applied only to update, delete, and writetext records.so
if i use
SELECT @.id = ( SELECT top 1 id FROM my_rows (readpast) WHERE
is_locked
=0 ) it will return same records to both simultanously call.
may be i can use it with UPDLOCK hint?
thanks|||Hi
You would be blocked whilst the second transaction finishes, hence the
possibility of a bottleneck. Why do you need to re-use the ids?
John
"is_vlb50@.hotmail.com" wrote:
> On Sep 26, 8:40 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > > if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> > > call in store procedure can raise error because first call in first SP
> > > still running (still locks the record) or it just will receive another
> > > row?
> >
> > TOP 1 can give you *any* row. If the row decided for is locked already by an incompatible lock, then
> > you will be blocked. If you want some other row, you might wan to check out the READPAST hint.
> >
> > --
> > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> >
> >
> >
> > <is_vl...@.hotmail.com> wrote in messagenews:1190831328.803233.199140@.19g2000hsx.googlegroups.com...
> > > On Sep 26, 6:02 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > >> Hi
> >
> > >> Readhttp://www.sommarskog.se/error-handling-I.htmlandhttp://www.sommarsko...
> > >> gives you a very good
> > >> idea of what to do. If you follow Erlands advice you would also have checked
> > >> for errors when you commit the transaction and have code to rollback.
> >
> > >> John
> >
> > >> "is_vl...@.hotmail.com" wrote:
> > >> > On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > >> > > Hi
> >
> > >> > > You select and update statement should be contained within one transaction
> > >> > > and you can use the UPDLOCK hint on the select statement to stop others
> > >> > > returning that value
> >
> > >> > > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> > >> > > SET NOCOUNT ON
> > >> > > BEGIN TRANSACTION
> > >> > > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> > >> > > -- Error checking
> > >> > > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> > >> > > -- Error checking
> > >> > > COMMIT TRANSACTION
> > >> > > RETURN
> >
> > >> > > Alternatively you could use
> >
> > >> > > UPDATE my_rows
> > >> > > SET is_locked = 1
> > >> > > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> >
> > >> > > but you would not know the ID that was updated.
> >
> > >> > > Using this type of locking can potentially cause a bottleneck and poor
> > >> > > performance.
> >
> > >> > > John
> >
> > >> > > "is_vl...@.hotmail.com" wrote:
> > >> > > > I need help with next problem:
> > >> > > > Exist table my_rows with next columns:
> > >> > > > id - int identity
> > >> > > > name - varchar
> > >> > > > is_locked - bit
> >
> > >> > > > I need create store procedure which will return every time diferent
> > >> > > > row for every request from different processes.
> >
> > >> > > > the pseudo code for the procedure :
> > >> > > > 1 select statement is :
> > >> > > > select @.id = selct top 1 id from my_rows where is_locked =0
> > >> > > > 2 update my_rows set is_locked = 1 where id=@.id
> > >> > > > 3 return @.id
> >
> > >> > > > In different words : if in same time I call this procedure from 2
> > >> > > > different connections, it will return 2 different record.
> > >> > > > I also want that first call to procedure will not generate lock error
> > >> > > > for second call in same time(just second call will wait for finish
> > >> > > > first , it is ok.)
> > >> > > > Thanks- Hide quoted text -
> >
> > >> > > - Show quoted text -
> >
> > >> > You wroute:
> > >> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
> > >> > =0 )
> > >> > -- Error checking
> >
> > >> > Could you explain what is reason for "Error checking " and what I can
> > >> > do.
> > >> > If some body in same time call same procedure , I can get error ?
> > >> > Thanks- Hide quoted text -
> >
> > >> - Show quoted text -
> >
> > > Thanks,
> > > it was very helpful. One last question:
> > > if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> > > call in store procedure can raise error because first call in first SP
> > > still running (still locks the record) or it just will receive another
> > > row?
> > > Thanks- Hide quoted text -
> >
> > - Show quoted text -
> In this case suggestion of John is not valid, because as I described
> at start post,I need solution which will in every call to SP in same
> time will return back a different row without any block.
> Thanks
>|||On Wed, 26 Sep 2007 23:38:57 -0700, is_vlb50@.hotmail.com wrote:
>On Sep 27, 12:46 am, Hugo Kornelis
><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
>> On Wed, 26 Sep 2007 18:50:43 -0000, is_vl...@.hotmail.com wrote:
>> >In this case suggestion of John is not valid, because as I described
>> >at start post,I need solution which will in every call to SP in same
>> >time will return back a different row without any block.
>> Hi is_vlb50,
>> As Tibor already mentioned, the READPAST hint can help you achieve what
>> you need. You'll find all the details in Books Online.
>> --
>> Hugo Kornelis, SQL Server MVP
>> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
>the readpast applied only to update, delete, and writetext records.so
>if i use
>SELECT @.id = ( SELECT top 1 id FROM my_rows (readpast) WHERE
>is_locked
>=0 ) it will return same records to both simultanously call.
>may be i can use it with UPDLOCK hint?
>thanks
>
Hi is_vlb50,
Yes, I thought John Bell already covered that.
First, you do a SELECT with UPDLOCK to make sure an exclusive lock is
acquired right away (to prevent two readers getting the same number),
*and* with READPAST to allow it to skip locked rows.
Then (in the same transaction), you do the update. Since you've already
got an exclusive lock, this will neven block or deadlock.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Sep 28, 10:13 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Wed, 26 Sep 2007 23:38:57 -0700, is_vl...@.hotmail.com wrote:
> >On Sep 27, 12:46 am, Hugo Kornelis
> ><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> >> On Wed, 26 Sep 2007 18:50:43 -0000, is_vl...@.hotmail.com wrote:
> >> >In this case suggestion of John is not valid, because as I described
> >> >at start post,I need solution which will in every call to SP in same
> >> >time will return back a different row without any block.
> >> Hi is_vlb50,
> >> As Tibor already mentioned, the READPAST hint can help you achieve what
> >> you need. You'll find all the details in Books Online.
> >> --
> >> Hugo Kornelis, SQL Server MVP
> >> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
> >the readpast applied only to update, delete, and writetext records.so
> >if i use
> >SELECT @.id = ( SELECT top 1 id FROM my_rows (readpast) WHERE
> >is_locked
> >=0 ) it will return same records to both simultanously call.
> >may be i can use it with UPDLOCK hint?
> >thanks
> Hi is_vlb50,
> Yes, I thought John Bell already covered that.
> First, you do a SELECT with UPDLOCK to make sure an exclusive lock is
> acquired right away (to prevent two readers getting the same number),
> *and* with READPAST to allow it to skip locked rows.
> Then (in the same transaction), you do the update. Since you've already
> got an exclusive lock, this will neven block or deadlock.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> - Show quoted text -
just for confirm final solution:
CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
SET NOCOUNT ON
BEGIN TRANSACTION
SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) (READPAST) WHERE
is_locked =0 )
-- Error checking
UPDATE my_rows SET is_locked = 1 WHERE id=@.id
-- Error checking
COMMIT TRANSACTION
RETURN
Thanks|||Hi
From BOL:
WITH ( < table_hint > [ ,...n ] )
Specifies one or more table hints. For more information about table hints,
see FROM.
John
"is_vlb50@.hotmail.com" wrote:
> On Sep 28, 10:13 pm, Hugo Kornelis
> <h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> > On Wed, 26 Sep 2007 23:38:57 -0700, is_vl...@.hotmail.com wrote:
> > >On Sep 27, 12:46 am, Hugo Kornelis
> > ><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> > >> On Wed, 26 Sep 2007 18:50:43 -0000, is_vl...@.hotmail.com wrote:
> > >> >In this case suggestion of John is not valid, because as I described
> > >> >at start post,I need solution which will in every call to SP in same
> > >> >time will return back a different row without any block.
> >
> > >> Hi is_vlb50,
> >
> > >> As Tibor already mentioned, the READPAST hint can help you achieve what
> > >> you need. You'll find all the details in Books Online.
> >
> > >> --
> > >> Hugo Kornelis, SQL Server MVP
> > >> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
> > >the readpast applied only to update, delete, and writetext records.so
> > >if i use
> > >SELECT @.id = ( SELECT top 1 id FROM my_rows (readpast) WHERE
> > >is_locked
> > >=0 ) it will return same records to both simultanously call.
> > >may be i can use it with UPDLOCK hint?
> > >thanks
> >
> > Hi is_vlb50,
> >
> > Yes, I thought John Bell already covered that.
> >
> > First, you do a SELECT with UPDLOCK to make sure an exclusive lock is
> > acquired right away (to prevent two readers getting the same number),
> > *and* with READPAST to allow it to skip locked rows.
> >
> > Then (in the same transaction), you do the update. Since you've already
> > got an exclusive lock, this will neven block or deadlock.
> >
> > --
> > Hugo Kornelis, SQL Server MVP
> > My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> >
> > - Show quoted text -
> just for confirm final solution:
> CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> SET NOCOUNT ON
> BEGIN TRANSACTION
> SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) (READPAST) WHERE
> is_locked =0 )
> -- Error checking
> UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> -- Error checking
> COMMIT TRANSACTION
> RETURN
> Thanks
>
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Monday, March 26, 2012
Friday, March 9, 2012
Local Temporary Tables
I have created a local temporary table (#Temp) and placed data in it.
When I do:
SELECT * #Temp
The result is a set of rows with no columns.
The SELECT statement is in the same procedure as the creation of #Temp; so I
don't believe this is a scoping issue. Does that make sense?
Thank you in advance,
EricBeringer wrote:
> I have created a local temporary table (#Temp) and placed data in it.
> When I do:
> SELECT * #Temp
> The result is a set of rows with no columns.
> The SELECT statement is in the same procedure as the creation of #Temp; so I
> don't believe this is a scoping issue. Does that make sense?
> Thank you in advance,
> Eric
No, it doesn't make sense. First, your code above is invalid SQL.
Second, you don't provide any DDL or sample data for us to reproduce the
problem.
Zach|||Please be patient with me. I'm learning how to do this stuff by myelf and
just begining! :)
At anyrate here is an example:
ALTER PROCEDURE proc1
AS
SET NOCOUNT ON
CREATE TABLE #TempTable(my_text CHAR(10))
INSERT INTO #TempTable(my_text) VALUES ('test')
INSERT INTO #TempTable(my_text) VALUES ('test2')
INSERT INTO #TempTable(my_text) VALUES ('test3')
SELECT * FROM #TempTable
The result (visually in Access 2002, in datasheet view) after executing the
procedure is simply a row header with three rows and no columns. Of note,
if the SET NOCOUNT ON is commented out there is nothing.
Thanks again,
Eric
"nib" <individual_news@.nibsworld.com> wrote in message
news:2tqnlcF21lmapU2@.uni-berlin.de...
> Beringer wrote:
>> I have created a local temporary table (#Temp) and placed data in it.
>>
>> When I do:
>> SELECT * #Temp
>>
>> The result is a set of rows with no columns.
>>
>> The SELECT statement is in the same procedure as the creation of #Temp;
>> so I don't believe this is a scoping issue. Does that make sense?
>>
>> Thank you in advance,
>> Eric
>>
>>
> No, it doesn't make sense. First, your code above is invalid SQL. Second,
> you don't provide any DDL or sample data for us to reproduce the problem.
> Zach|||I do similar stored procedures like this all the time. Except I create the
SP via Enterprise Manager and Check Syntax etc . Have you tried executing
this from Query Analyzer just to see if it works? Or just create a stored
procedure in Enterprise Manager and EXECUTE it from Query Analyzer...
The column in your example should have one column heading and three rows of
data (if I am reading it correctly).
Is it generating any errors?
Barry
"Beringer" <borden_eric@.invalid.com> wrote in message
news:l1Xdd.56381$kz3.16039@.fed1read02...
> Please be patient with me. I'm learning how to do this stuff by myelf and
> just begining! :)
> At anyrate here is an example:
> ALTER PROCEDURE proc1
> AS
> SET NOCOUNT ON
> CREATE TABLE #TempTable(my_text CHAR(10))
> INSERT INTO #TempTable(my_text) VALUES ('test')
> INSERT INTO #TempTable(my_text) VALUES ('test2')
> INSERT INTO #TempTable(my_text) VALUES ('test3')
> SELECT * FROM #TempTable
> The result (visually in Access 2002, in datasheet view) after executing
> the procedure is simply a row header with three rows and no columns. Of
> note, if the SET NOCOUNT ON is commented out there is nothing.
> Thanks again,
> Eric
> "nib" <individual_news@.nibsworld.com> wrote in message
> news:2tqnlcF21lmapU2@.uni-berlin.de...
>> Beringer wrote:
>>> I have created a local temporary table (#Temp) and placed data in it.
>>>
>>> When I do:
>>> SELECT * #Temp
>>>
>>> The result is a set of rows with no columns.
>>>
>>> The SELECT statement is in the same procedure as the creation of #Temp;
>>> so I don't believe this is a scoping issue. Does that make sense?
>>>
>>> Thank you in advance,
>>> Eric
>>>
>>>
>>
>> No, it doesn't make sense. First, your code above is invalid SQL. Second,
>> you don't provide any DDL or sample data for us to reproduce the problem.
>>
>> Zach
Local temporary table schema
Is there a way to get the schema (columns) of a local temporary table in SQL
Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
multiple connections use the same temp table name, there doesn't seem to be a
way to differentiate between them. Each table name is appended with a bunch
of underscores and a random number (i.e.
"#MyTable__________________________________________________________________________________________________________0000000002B0"),
but that doesn't seem to help much.
Thanks,
Rich WoodThis is a multi-part message in MIME format.
--=_NextPart_000_01D9_01C69935.4E112260
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
It seems odd that you would need to do this, since your code for the =current connection had to know how to create the #Temp Table.
However, if you must, you could use this as a starting point:
SELECT * FROM Tempdb..sysobjects WHERE name LIKE '%#Temp%'
SELECT * FROM Tempdb..syscolumns WHERE id =3D object_ID('tempdb..#Temp')
-- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Rich Wood" <RichWood@.newsgroup.nospam> wrote in message =news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com...
> Is there a way to get the schema (columns) of a local temporary table =in SQL > Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but =if > multiple connections use the same temp table name, there doesn't seem =to be a > way to differentiate between them. Each table name is appended with a =bunch > of underscores and a random number (i.e. > ="#MyTable________________________________________________________________=__________________________________________0000000002B0"), > but that doesn't seem to help much.
> > Thanks,
> > Rich Wood
--=_NextPart_000_01D9_01C69935.4E112260
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It seems odd that you would need to do =this, since your code for the current connection had to know how to create the #Temp =Table.
However, if you must, you could use =this as a starting point:
SELECT *
FROM =Tempdb..sysobjects WHERE name LIKE ='%#Temp%'
SELECT *
FROM Tempdb..syscolumns
WHERE id =3D object_ID('tempdb..#Temp')
-- Arnie Rowland, YACE* "To =be successful, your heart must accompany your knowledge."
*Yet Another Certification =Exam
"Rich Wood" wrote in message news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com...> =Is there a way to get the schema (columns) of a local temporary table in SQL > =Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if > =multiple connections use the same temp table name, there doesn't seem to be a => way to differentiate between them. Each table name is appended with a =bunch > of underscores and a random number (i.e. > "#MyTable________________________________________________________________=__________________________________________0000000002B0"), > but that doesn't seem to help much.> > =Thanks,> > Rich Wood
--=_NextPart_000_01D9_01C69935.4E112260--|||Arnie Rowland wrote:
> It seems odd that you would need to do this, since your code for the curr=ent connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-07=54-45EB-8978-6391D4939CD1@.microsoft.com...
> > Is there a way to get the schema (columns) of a local temporary table i=n SQL
> > Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but =if
> > multiple connections use the same temp table name, there doesn't seem t=o be a
> > way to differentiate between them. Each table name is appended with a b=unch
> > of underscores and a random number (i.e.
> > "#MyTable______________________________________________________________=____________________________________________0000000002B0"),
> > but that doesn't seem to help much.
> >
> > Thanks,
> >
> > Rich Wood
> --=3D_NextPart_000_01D9_01C69935.4E112260
> Content-Type: text/html; charset=3DUtf-8
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 2562
> =EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &
>
>
>
>
>
> It seems odd that you would need to do t=his, since
> your code for the current connection had to know how to create the #Temp
> Table.
>
> However, if you must, you could use this= as a
> starting point:
> SELECT *
> FROM Tempdb..sysobje=cts
>
> WHERE name LIKE '%#Temp%'= face=3D"Courier New" size=3D2>
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> -- Arnie Rowland, YACE* "To =be
> successful, your heart must accompany your knowledge."
>
> *Yet Another Certification Exam
>
>
> "Rich Wood" < href=3D"mailto:RichWood@.newsgroup.nospam"> size=3D2>RichWood@.newsgroup.nospam=> wrote in
> message href=3D"news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com"> size=3D2>news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com face=3DArial size=3D2>...> Is= there a way to
> get the schema (columns) of a local temporary table in SQL > Serve=r 2005?
> TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if > mul=tiple
> connections use the same temp table name, there doesn't seem to be a =>
> way to differentiate between them. Each table name is appended with a bun=ch
> > of underscores and a random number (i.e. >
> "#MyTable________________________________________________________________=__________________________________________0000000002B0"),
> > but that doesn't seem to help much.> > Thanks,=>
> > Rich Wood
> --=3D_NextPart_000_01D9_01C69935.4E112260--
use
sp_help 'tempdb..#temp'
Regards
Amish Shah|||Hi Rich,
Thank you for your posting!
You could use the object_ID function to get the object id of the temporary
table you current user created.
As Arnie mentioned, you could use the following statement to query the
information.
SELECT *
FROM Tempdb..sysobjects
WHERE id = object_ID('tempdb..#TEMP')
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#TEMP')
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||To make a long story short, the local temporary table is created dynamically
with the result of a query -- so even though I create it locally I don't know
the column names at design time.
Thanks for your response -- using the object_id function to query the
tempdb..syscolumns table worked.
Rich Wood
"Arnie Rowland" wrote:
> It seems odd that you would need to do this, since your code for the current connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id = object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com...
> > Is there a way to get the schema (columns) of a local temporary table in SQL
> > Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
> > multiple connections use the same temp table name, there doesn't seem to be a
> > way to differentiate between them. Each table name is appended with a bunch
> > of underscores and a random number (i.e.
> > "#MyTable__________________________________________________________________________________________________________0000000002B0"),
> > but that doesn't seem to help much.
> >
> > Thanks,
> >
> > Rich Wood
Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
multiple connections use the same temp table name, there doesn't seem to be a
way to differentiate between them. Each table name is appended with a bunch
of underscores and a random number (i.e.
"#MyTable__________________________________________________________________________________________________________0000000002B0"),
but that doesn't seem to help much.
Thanks,
Rich WoodThis is a multi-part message in MIME format.
--=_NextPart_000_01D9_01C69935.4E112260
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
It seems odd that you would need to do this, since your code for the =current connection had to know how to create the #Temp Table.
However, if you must, you could use this as a starting point:
SELECT * FROM Tempdb..sysobjects WHERE name LIKE '%#Temp%'
SELECT * FROM Tempdb..syscolumns WHERE id =3D object_ID('tempdb..#Temp')
-- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Rich Wood" <RichWood@.newsgroup.nospam> wrote in message =news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com...
> Is there a way to get the schema (columns) of a local temporary table =in SQL > Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but =if > multiple connections use the same temp table name, there doesn't seem =to be a > way to differentiate between them. Each table name is appended with a =bunch > of underscores and a random number (i.e. > ="#MyTable________________________________________________________________=__________________________________________0000000002B0"), > but that doesn't seem to help much.
> > Thanks,
> > Rich Wood
--=_NextPart_000_01D9_01C69935.4E112260
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It seems odd that you would need to do =this, since your code for the current connection had to know how to create the #Temp =Table.
However, if you must, you could use =this as a starting point:
SELECT *
FROM =Tempdb..sysobjects WHERE name LIKE ='%#Temp%'
SELECT *
FROM Tempdb..syscolumns
WHERE id =3D object_ID('tempdb..#Temp')
-- Arnie Rowland, YACE* "To =be successful, your heart must accompany your knowledge."
*Yet Another Certification =Exam
"Rich Wood"
--=_NextPart_000_01D9_01C69935.4E112260--|||Arnie Rowland wrote:
> It seems odd that you would need to do this, since your code for the curr=ent connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-07=54-45EB-8978-6391D4939CD1@.microsoft.com...
> > Is there a way to get the schema (columns) of a local temporary table i=n SQL
> > Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but =if
> > multiple connections use the same temp table name, there doesn't seem t=o be a
> > way to differentiate between them. Each table name is appended with a b=unch
> > of underscores and a random number (i.e.
> > "#MyTable______________________________________________________________=____________________________________________0000000002B0"),
> > but that doesn't seem to help much.
> >
> > Thanks,
> >
> > Rich Wood
> --=3D_NextPart_000_01D9_01C69935.4E112260
> Content-Type: text/html; charset=3DUtf-8
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 2562
> =EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &
>
>
>
>
>
> It seems odd that you would need to do t=his, since
> your code for the current connection had to know how to create the #Temp
> Table.
>
> However, if you must, you could use this= as a
> starting point:
> SELECT *
> FROM Tempdb..sysobje=cts
>
> WHERE name LIKE '%#Temp%'= face=3D"Courier New" size=3D2>
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> -- Arnie Rowland, YACE* "To =be
> successful, your heart must accompany your knowledge."
>
> *Yet Another Certification Exam
>
>
> "Rich Wood" < href=3D"mailto:RichWood@.newsgroup.nospam"> size=3D2>RichWood@.newsgroup.nospam=> wrote in
> message href=3D"news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com"> size=3D2>news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com face=3DArial size=3D2>...> Is= there a way to
> get the schema (columns) of a local temporary table in SQL > Serve=r 2005?
> TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if > mul=tiple
> connections use the same temp table name, there doesn't seem to be a =>
> way to differentiate between them. Each table name is appended with a bun=ch
> > of underscores and a random number (i.e. >
> "#MyTable________________________________________________________________=__________________________________________0000000002B0"),
> > but that doesn't seem to help much.> > Thanks,=>
> > Rich Wood
> --=3D_NextPart_000_01D9_01C69935.4E112260--
use
sp_help 'tempdb..#temp'
Regards
Amish Shah|||Hi Rich,
Thank you for your posting!
You could use the object_ID function to get the object id of the temporary
table you current user created.
As Arnie mentioned, you could use the following statement to query the
information.
SELECT *
FROM Tempdb..sysobjects
WHERE id = object_ID('tempdb..#TEMP')
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#TEMP')
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||To make a long story short, the local temporary table is created dynamically
with the result of a query -- so even though I create it locally I don't know
the column names at design time.
Thanks for your response -- using the object_id function to query the
tempdb..syscolumns table worked.
Rich Wood
"Arnie Rowland" wrote:
> It seems odd that you would need to do this, since your code for the current connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id = object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com...
> > Is there a way to get the schema (columns) of a local temporary table in SQL
> > Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
> > multiple connections use the same temp table name, there doesn't seem to be a
> > way to differentiate between them. Each table name is appended with a bunch
> > of underscores and a random number (i.e.
> > "#MyTable__________________________________________________________________________________________________________0000000002B0"),
> > but that doesn't seem to help much.
> >
> > Thanks,
> >
> > Rich Wood
Subscribe to:
Posts (Atom)