lock request time out period exceded

Hi,

while my db is executing a store procedure i try to view the current activity in the managent but it returns to me 'Lock request time out period exceded'. It happens until the store procedure is finished. After that everything is ok. However i can see the activity executing sp_who and the db seems to work ok, maybe a little slow.

what does it means??

Sorry for my english.
Thanks. EduardoYou should understand the database engine's locking mechanism. Your stored procedures probably is modifying your data, which means an exclusive lock. If you or another process is trying to view the data, the engine tries to get a shared lock. Since there is already an exclusive lock, the request for getting the shared lock expires, which is shown to you.sql

lock question

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
>

Location of reports?

I cannot find the original copy of a report that is uploaded onto my Report
Server. Where does Reporting Services store the uploaded reports? I need a
physical location so I may copy the .rdl file and edit it.
Is there any other way to copy the report file from inside Reporting
Services?it is in the directory of the report project if you are using VS.NET.
another alternative for the rdl is to select the report properties and
'Update' it (specifying the location of the .rdl - which is what we do for
production here).
Also, you may upload from RM basically anything (it doesn't have to be an
RDL). Let's say for instance that the folder for a department needs a monthly
.XLS for all to work from...
r
"Amon Borland" wrote:
> I cannot find the original copy of a report that is uploaded onto my Report
> Server. Where does Reporting Services store the uploaded reports? I need a
> physical location so I may copy the .rdl file and edit it.
> Is there any other way to copy the report file from inside Reporting
> Services?
>
>|||I was not as complete as I should have been with my answer from post.
if in doubt, look in the ReportServer database (if you used the default name
fom the install) and open the Catalog table. the name and path for the report
are located there.
r
"tutor" wrote:
> it is in the directory of the report project if you are using VS.NET.
> another alternative for the rdl is to select the report properties and
> 'Update' it (specifying the location of the .rdl - which is what we do for
> production here).
> Also, you may upload from RM basically anything (it doesn't have to be an
> RDL). Let's say for instance that the folder for a department needs a monthly
> .XLS for all to work from...
> r
> "Amon Borland" wrote:
> > I cannot find the original copy of a report that is uploaded onto my Report
> > Server. Where does Reporting Services store the uploaded reports? I need a
> > physical location so I may copy the .rdl file and edit it.
> >
> > Is there any other way to copy the report file from inside Reporting
> > Services?
> >
> >
> >|||Amon, the report is stored by RS in the database. It does not exist as a
file. You can use Report Manager to retrieve it (properties of report,
edit).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"tutor" <tutor@.discussions.microsoft.com> wrote in message
news:A431F911-100A-4FCE-852E-D7E2E5CC523E@.microsoft.com...
>I was not as complete as I should have been with my answer from post.
> if in doubt, look in the ReportServer database (if you used the default
> name
> fom the install) and open the Catalog table. the name and path for the
> report
> are located there.
> r
> "tutor" wrote:
>> it is in the directory of the report project if you are using VS.NET.
>> another alternative for the rdl is to select the report properties and
>> 'Update' it (specifying the location of the .rdl - which is what we do
>> for
>> production here).
>> Also, you may upload from RM basically anything (it doesn't have to be an
>> RDL). Let's say for instance that the folder for a department needs a
>> monthly
>> .XLS for all to work from...
>> r
>> "Amon Borland" wrote:
>> > I cannot find the original copy of a report that is uploaded onto my
>> > Report
>> > Server. Where does Reporting Services store the uploaded reports? I
>> > need a
>> > physical location so I may copy the .rdl file and edit it.
>> >
>> > Is there any other way to copy the report file from inside Reporting
>> > Services?
>> >
>> >
>> >sql

Location for audit logs?

Hi,
I saw in this or the SQL Server Security news group that it's recommended to
store auditing logs on an unused disk drive because auditing logs could grow
wildly. But based on this article
http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx,
SQL Server doesn't let you log auditable events to an alternative location.
<QUOTE>After you enable C2 auditing for the default database or for an
instance, the database server will log all activity to the data directory
that you specified during the installation process. (SQL Server doesn't let
you log auditable events to an alternative location.) This directory holds
the databases that SQL Server initially created. This directory is also the
default location for all new databases and their transaction log
files.</QUOTE>
Now I'm confused. I have data/transaction logs on one drive, I'm planning
to add additional disk drive specifically for auditing. Is it possible to
direct auditing logs to the new drive?
Thanks,
BingHi Bing
"bing" wrote:
> Hi,
> I saw in this or the SQL Server Security news group that it's recommended to
> store auditing logs on an unused disk drive because auditing logs could grow
> wildly. But based on this article
> http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx,
> SQL Server doesn't let you log auditable events to an alternative location.
>
> <QUOTE>After you enable C2 auditing for the default database or for an
> instance, the database server will log all activity to the data directory
> that you specified during the installation process. (SQL Server doesn't let
> you log auditable events to an alternative location.) This directory holds
> the databases that SQL Server initially created. This directory is also the
> default location for all new databases and their transaction log
> files.</QUOTE>
> Now I'm confused. I have data/transaction logs on one drive, I'm planning
> to add additional disk drive specifically for auditing. Is it possible to
> direct auditing logs to the new drive?
> Thanks,
> Bing
If you change the default data and log directories in the Database
properties task in Enterprise Manager on the properties page of the instance
(right click) do new audit files get created their (you may need to restart)?
If not you can move the databases away from this location using
sp_detach_db/sp_attach_db or backup/restore see
http://support.microsoft.com/kb/314546. When you do the initial install, the
default location is the system disc e.g. C:\Program Files\Microsoft SQL
Server... this should be changed as filling up the system disc with audit
logs will make the system unusable.
You can move system databases (possibly to different spindles!) as well as
user databases http://support.microsoft.com/kb/224071/ and using different
discs for full text catalogs will also be a way of improving performance
http://support.microsoft.com/kb/240867/ Database data files and log files
should be on separate drive arrays as they perform different types of I/O
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
You should also consider what RAID level and resilience you require for
these disc subsystems.
John

Localizing Labels

Hi,

I am working on localizing SSRS reports and wanted to store the text for the labels in the database for different languages. The Language id would be passed by the user as a parameter. Based on this Language ID, the required data for the labels would be fetched and stored in a dataset in the custom code. This dataset would then be searched for relevant LabelIDs and the label caption in the particular language would be returned. The dataset would be a shared variable so it would get initialized the first time. For initializing the dataset I would need the datasource information, which would either be stored in the Shared Data source or embedded in the Report.

Can I refer to the shared data source being used by the current report in the Custom Code Window ? Or can I retrieve the data source information embedded in the report and use it in the Custom Code? If so, how ?

Thanks In Advance.

Ashish

Hi Guys,

Does any one know as to how can we access the data source connection information from Custom Code ?

I need that information to populate a custom dataset created in the Custom Code.

Please help.

TIA.

Ashish

Local Variable in stored procedure

Hi ,
I need to store a variable which has length more than 8000 bytes..Is there
is a workaround as varchar column takes only 8000 bytes/chracters..Text
cannot be used as local variable..
Pls suggest..
Regards
rectHave you tried creating a temporary table in tempdb, one
column of text ?
Peter
"It's true hard work never killed anybody, but I figure,
why take the chance?"
Ronald Reagan
>--Original Message--
>Hi ,
>I need to store a variable which has length more than
8000 bytes..Is there
>is a workaround as varchar column takes only 8000
bytes/chracters..Text
>cannot be used as local variable..
>Pls suggest..
>Regards
>rect
>.
>

loading XML from SQL Server -- faster

I am using a text column to store XML data on a sql server table.
The data contained in these XML documents maps to a few different
schemas but they share a subset of tags which I use to query with
sp_xml_preparedocument and openxml.
The problem I'm having is that this process is very slow: for ~2500
xml documents, it takes 12 seconds to run.
Is there a way to query these XML documents faster using SQL Server?
Or I will have to reduce my working set of records?
Here is the code I've written:
CREATE PROCEDURE dbo.QueryDocuments
AS
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.namespacedef varchar(512)
declare @.rowpattern varchar(128)
declare @.id uniqueidentifier
declare @.doctype varchar(50)
declare @.docinfo varchar(128)
declare @.docversion varchar(3)
declare @.tstamp datetime
declare @.lastchange varchar(50)
IF OBJECT_ID('tempdb..#rs') IS NOT NULL
DROP TABLE #rs
create table #rs (id uniqueidentifier, doctype
varchar(50), docinfo varchar(128), docversion
varchar(10), tstamp datetime, lastchange varchar(50),
cliente varchar(128), estacao varchar(128), tecnico
varchar(256), inicio datetime)
declare crs cursor for
select Documentos.id,
Documentos.doctype,
DocumentTypes.docinfo,
Documentos.docversion,
Documentos.tstamp,
Documentos.lastchange
from
Documentos
inner join
DocumentTypes on Documentos.doctype = DocumentTypes.doctype and
Documentos.docversion = DocumentTypes.docversion
declare @.cmd varchar(8000)
declare @.xml_0 varchar( 8000 ), @.xml_1 varchar( 8000
)
open crs
fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
@.tstamp, @.lastchange
while @.@.fetch_status = 0
begin
declare @.idoc int
set @.namespacedef = (select
substring(convert(varchar(8000), xmldata),
patindex('%<my:%', xmldata), patindex('%>%',
substring(xmldata, patindex('%<my:%', xmldata),
500))-1) from Documentos where [id] = @.id) +
'/>'
set @.rowpattern = '/' + substring(@.namespacedef, 2,
charindex(' ', @.namespacedef)-1)
select @.xml_0 = replace(substring( xmldata, ( 0*7000
) + 1, 7000 ), char(39),
char(39)+char(39) ),
@.xml_1 = replace(substring( xmldata, ( 1*7000
) + 1, 7000 ), char(39),
char(39)+char(39) )
from Documentos where [id] = @.id
exec ('declare @.idoc int;exec sp_xml_preparedocument @.idoc
OUTPUT, ''' + @.xml_0 + @.xml_1 /*+ @.xml_2 + @.xml_3 + @.xml_4 + @.xml_5*/
+ ''', ''' + @.namespacedef + ''';declare he_cur cursor forselect
@.idoc')
open he_cur
fetch he_cur into @.idoc
deallocate he_cur
insert into #rs
select
@.id,
@.doctype,
@.docinfo,
@.docversion,
@.tstamp,
@.lastchange,
*
from openxml(@.idoc, @.rowpattern, 2)
with (
cliente varchar(128) 'my:Cliente',
estacao varchar(128) 'my:Estacao',
tecnico varchar(256) 'my:Tecnico',
inicio datetime 'my:Inicio'
)
exec sp_xml_removedocument @.idoc
fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
@.tstamp, @.lastchange
end
close crs
deallocate crs
select *
from #rs
order by
year(inicio) asc,
month(inicio) asc,
day(inicio) asc
drop table #rs
GO
Thanks for your help.Moving to SQL Server 2005 could help, since you would not need to do the
string manipulations.
Do you know where you use the time? In the string ops, XML parser or OpenXML
calls?
Also, could you just pass the @.xml_* for the namespace declarations? Or use
a constant namespace declaration?
Best regards
Michael
"mgcm" <miltonmoura@.gmail-dot-com.no-spam.invalid> wrote in message
news:CsWdneQdj-phirveRVn_vA@.giganews.com...
>I am using a text column to store XML data on a sql server table.
> The data contained in these XML documents maps to a few different
> schemas but they share a subset of tags which I use to query with
> sp_xml_preparedocument and openxml.
> The problem I'm having is that this process is very slow: for ~2500
> xml documents, it takes 12 seconds to run.
> Is there a way to query these XML documents faster using SQL Server?
> Or I will have to reduce my working set of records?
> Here is the code I've written:
>
> CREATE PROCEDURE dbo.QueryDocuments
> AS
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.namespacedef varchar(512)
> declare @.rowpattern varchar(128)
> declare @.id uniqueidentifier
> declare @.doctype varchar(50)
> declare @.docinfo varchar(128)
> declare @.docversion varchar(3)
> declare @.tstamp datetime
> declare @.lastchange varchar(50)
> IF OBJECT_ID('tempdb..#rs') IS NOT NULL
> DROP TABLE #rs
> create table #rs (id uniqueidentifier, doctype
> varchar(50), docinfo varchar(128), docversion
> varchar(10), tstamp datetime, lastchange varchar(50),
> cliente varchar(128), estacao varchar(128), tecnico
> varchar(256), inicio datetime)
> declare crs cursor for
> select Documentos.id,
> Documentos.doctype,
> DocumentTypes.docinfo,
> Documentos.docversion,
> Documentos.tstamp,
> Documentos.lastchange
> from
> Documentos
> inner join
> DocumentTypes on Documentos.doctype = DocumentTypes.doctype and
> Documentos.docversion = DocumentTypes.docversion
> declare @.cmd varchar(8000)
> declare @.xml_0 varchar( 8000 ), @.xml_1 varchar( 8000
> )
> open crs
> fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
> @.tstamp, @.lastchange
> while @.@.fetch_status = 0
> begin
> declare @.idoc int
> set @.namespacedef = (select
> substring(convert(varchar(8000), xmldata),
> patindex('%<my:%', xmldata), patindex('%>%',
> substring(xmldata, patindex('%<my:%', xmldata),
> 500))-1) from Documentos where [id] = @.id) +
> '/>'
> set @.rowpattern = '/' + substring(@.namespacedef, 2,
> charindex(' ', @.namespacedef)-1)
> select @.xml_0 = replace(substring( xmldata, ( 0*7000
> ) + 1, 7000 ), char(39),
> char(39)+char(39) ),
> @.xml_1 = replace(substring( xmldata, ( 1*7000
> ) + 1, 7000 ), char(39),
> char(39)+char(39) )
> from Documentos where [id] = @.id
> exec ('declare @.idoc int;exec sp_xml_preparedocument @.idoc
> OUTPUT, ''' + @.xml_0 + @.xml_1 /*+ @.xml_2 + @.xml_3 + @.xml_4 + @.xml_5*/
> + ''', ''' + @.namespacedef + ''';declare he_cur cursor forselect
> @.idoc')
> open he_cur
> fetch he_cur into @.idoc
> deallocate he_cur
> insert into #rs
> select
> @.id,
> @.doctype,
> @.docinfo,
> @.docversion,
> @.tstamp,
> @.lastchange,
> *
> from openxml(@.idoc, @.rowpattern, 2)
> with (
> cliente varchar(128) 'my:Cliente',
> estacao varchar(128) 'my:Estacao',
> tecnico varchar(256) 'my:Tecnico',
> inicio datetime 'my:Inicio'
> )
> exec sp_xml_removedocument @.idoc
> fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
> @.tstamp, @.lastchange
> end
> close crs
> deallocate crs
> select *
> from #rs
> order by
> year(inicio) asc,
> month(inicio) asc,
> day(inicio) asc
> drop table #rs
> GO
> Thanks for your help.
>

loading XML from SQL Server -- faster

I am using a text column to store XML data on a sql server table.
The data contained in these XML documents maps to a few different
schemas but they share a subset of tags which I use to query with
sp_xml_preparedocument and openxml.
The problem I'm having is that this process is very slow: for ~2500
xml documents, it takes 12 seconds to run.
Is there a way to query these XML documents faster using SQL Server?
Or I will have to reduce my working set of records?
Here is the code I've written:
CREATE PROCEDURE dbo.QueryDocuments
AS
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.namespacedef varchar(512)
declare @.rowpattern varchar(128)
declare @.id uniqueidentifier
declare @.doctype varchar(50)
declare @.docinfo varchar(128)
declare @.docversion varchar(3)
declare @.tstamp datetime
declare @.lastchange varchar(50)
IF OBJECT_ID('tempdb..#rs') IS NOT NULL
DROP TABLE #rs
create table #rs (id uniqueidentifier, doctype
varchar(50), docinfo varchar(128), docversion
varchar(10), tstamp datetime, lastchange varchar(50),
cliente varchar(128), estacao varchar(128), tecnico
varchar(256), inicio datetime)
declare crs cursor for
selectDocumentos.id,
Documentos.doctype,
DocumentTypes.docinfo,
Documentos.docversion,
Documentos.tstamp,
Documentos.lastchange
from
Documentos
inner join
DocumentTypes on Documentos.doctype = DocumentTypes.doctype and
Documentos.docversion = DocumentTypes.docversion
declare @.cmd varchar(8000)
declare @.xml_0 varchar( 8000 ), @.xml_1 varchar( 8000
)
open crs
fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
@.tstamp, @.lastchange
while @.@.fetch_status = 0
begin
declare @.idoc int
set @.namespacedef = (select
substring(convert(varchar(8000), xmldata),
patindex('%<my:%', xmldata), patindex('%>%',
substring(xmldata, patindex('%<my:%', xmldata),
500))-1) from Documentos where [id] = @.id) +
'/>'
set @.rowpattern = '/' + substring(@.namespacedef, 2,
charindex(' ', @.namespacedef)-1)
select @.xml_0 = replace(substring( xmldata, ( 0*7000
) + 1, 7000 ), char(39),
char(39)+char(39) ),
@.xml_1 = replace(substring( xmldata, ( 1*7000
) + 1, 7000 ), char(39),
char(39)+char(39) )
from Documentos where [id] = @.id
exec ('declare @.idoc int;exec sp_xml_preparedocument @.idoc
OUTPUT, ''' + @.xml_0 + @.xml_1 /*+ @.xml_2 + @.xml_3 + @.xml_4 + @.xml_5*/
+ ''', ''' + @.namespacedef + ''';declare he_cur cursor forselect
@.idoc')
open he_cur
fetch he_cur into @.idoc
deallocate he_cur
insert into #rs
select
@.id,
@.doctype,
@.docinfo,
@.docversion,
@.tstamp,
@.lastchange,
*
fromopenxml(@.idoc, @.rowpattern, 2)
with (
cliente varchar(128)'my:Cliente',
estacao varchar(128)'my:Estacao',
tecnico varchar(256)'my:Tecnico',
iniciodatetime'my:Inicio'
)
exec sp_xml_removedocument @.idoc
fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
@.tstamp, @.lastchange
end
close crs
deallocate crs
select*
from#rs
order by
year(inicio) asc,
month(inicio) asc,
day(inicio) asc
drop table #rs
GO
Thanks for your help.
Moving to SQL Server 2005 could help, since you would not need to do the
string manipulations.
Do you know where you use the time? In the string ops, XML parser or OpenXML
calls?
Also, could you just pass the @.xml_* for the namespace declarations? Or use
a constant namespace declaration?
Best regards
Michael
"mgcm" <miltonmoura@.gmail-dot-com.no-spam.invalid> wrote in message
news:CsWdneQdj-phirveRVn_vA@.giganews.com...
>I am using a text column to store XML data on a sql server table.
> The data contained in these XML documents maps to a few different
> schemas but they share a subset of tags which I use to query with
> sp_xml_preparedocument and openxml.
> The problem I'm having is that this process is very slow: for ~2500
> xml documents, it takes 12 seconds to run.
> Is there a way to query these XML documents faster using SQL Server?
> Or I will have to reduce my working set of records?
> Here is the code I've written:
>
> CREATE PROCEDURE dbo.QueryDocuments
> AS
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.namespacedef varchar(512)
> declare @.rowpattern varchar(128)
> declare @.id uniqueidentifier
> declare @.doctype varchar(50)
> declare @.docinfo varchar(128)
> declare @.docversion varchar(3)
> declare @.tstamp datetime
> declare @.lastchange varchar(50)
> IF OBJECT_ID('tempdb..#rs') IS NOT NULL
> DROP TABLE #rs
> create table #rs (id uniqueidentifier, doctype
> varchar(50), docinfo varchar(128), docversion
> varchar(10), tstamp datetime, lastchange varchar(50),
> cliente varchar(128), estacao varchar(128), tecnico
> varchar(256), inicio datetime)
> declare crs cursor for
> select Documentos.id,
> Documentos.doctype,
> DocumentTypes.docinfo,
> Documentos.docversion,
> Documentos.tstamp,
> Documentos.lastchange
> from
> Documentos
> inner join
> DocumentTypes on Documentos.doctype = DocumentTypes.doctype and
> Documentos.docversion = DocumentTypes.docversion
> declare @.cmd varchar(8000)
> declare @.xml_0 varchar( 8000 ), @.xml_1 varchar( 8000
> )
> open crs
> fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
> @.tstamp, @.lastchange
> while @.@.fetch_status = 0
> begin
> declare @.idoc int
> set @.namespacedef = (select
> substring(convert(varchar(8000), xmldata),
> patindex('%<my:%', xmldata), patindex('%>%',
> substring(xmldata, patindex('%<my:%', xmldata),
> 500))-1) from Documentos where [id] = @.id) +
> '/>'
> set @.rowpattern = '/' + substring(@.namespacedef, 2,
> charindex(' ', @.namespacedef)-1)
> select @.xml_0 = replace(substring( xmldata, ( 0*7000
> ) + 1, 7000 ), char(39),
> char(39)+char(39) ),
> @.xml_1 = replace(substring( xmldata, ( 1*7000
> ) + 1, 7000 ), char(39),
> char(39)+char(39) )
> from Documentos where [id] = @.id
> exec ('declare @.idoc int;exec sp_xml_preparedocument @.idoc
> OUTPUT, ''' + @.xml_0 + @.xml_1 /*+ @.xml_2 + @.xml_3 + @.xml_4 + @.xml_5*/
> + ''', ''' + @.namespacedef + ''';declare he_cur cursor forselect
> @.idoc')
> open he_cur
> fetch he_cur into @.idoc
> deallocate he_cur
> insert into #rs
> select
> @.id,
> @.doctype,
> @.docinfo,
> @.docversion,
> @.tstamp,
> @.lastchange,
> *
> from openxml(@.idoc, @.rowpattern, 2)
> with (
> cliente varchar(128) 'my:Cliente',
> estacao varchar(128) 'my:Estacao',
> tecnico varchar(256) 'my:Tecnico',
> inicio datetime 'my:Inicio'
> )
> exec sp_xml_removedocument @.idoc
> fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
> @.tstamp, @.lastchange
> end
> close crs
> deallocate crs
> select *
> from #rs
> order by
> year(inicio) asc,
> month(inicio) asc,
> day(inicio) asc
> drop table #rs
> GO
> Thanks for your help.
>