sql local data: bit

Showing posts with label bit. Show all posts

Monday, March 26, 2012

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
>

Lock Problem and application is slowed down

Dear Memebers,

I have a critical problem. I have an application is running on 64 bit
machine. It used to be running on 32 bit machine. That application is
using a Stored Procedure that uses SELECT, INSERT, UPDATE statements.
Whenever this applicataion is being run all the processes are locked
and INSERTING operation becomes cumbersome. We tested again on 32 bit
machine however it happened again. So what might be the problem? Can
somebody help me ? Application creates a lot of processes in a minute
Should SQL Server be caple of recieving these fast inserting processes?
As a DB Admin what should I do to find out whether this problem is
coming from SQL Server or not?

If you respond me ASAP I really appreciate it

Regards

LSlaststubborn (arafatsalih@.gmail.com) writes:

Quote:

Originally Posted by

This question is difficult to answer because of lack of hard information,
and I'm afraid that I will have to ask for clarification.

So there is a stored procedure running. Do I understand that there are
multiple instances of the procedure running? What processes are blocked?
Other processes that are running the same stored procedure? Which operations
are blocked?

Which version of SQL Server do you have?

In general terms, the way to address blocking issues to investigate if
there are any indexes missing. The longer time a query takes to run,
the bigger the risk for blocking. Of course, you also need to know
what is blocked and where in the procedure blocking occurs. I have a
stored procedure that can assist with that, check out
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland ,

Here are the answers of your questions:
-Yes the same SP was kept locing the Database
-The other processes are not the same we have other SELECt or UPDATE or
INSERT processes on our Database
-Our database is MS SQL 20000
Thanks

Erland Sommarskog wrote:

Quote:

Originally Posted by

laststubborn (arafatsalih@.gmail.com) writes:

Quote:

Originally Posted by

>
This question is difficult to answer because of lack of hard information,
and I'm afraid that I will have to ask for clarification.
>
So there is a stored procedure running. Do I understand that there are
multiple instances of the procedure running? What processes are blocked?
Other processes that are running the same stored procedure? Which operations
are blocked?
>
Which version of SQL Server do you have?
>
In general terms, the way to address blocking issues to investigate if
there are any indexes missing. The longer time a query takes to run,
the bigger the risk for blocking. Of course, you also need to know
what is blocked and where in the procedure blocking occurs. I have a
stored procedure that can assist with that, check out
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||laststubborn (arafatsalih@.gmail.com) writes:

Quote:

Originally Posted by

Sorry for the late respond. Eventhough we solved the problem and the
problem was coming from the application, I would like to know my
necessary steps to take the action on SQL Server along with your
suggestions.
>
Here are the answers of your questions:
-Yes the same SP was kept locing the Database
-The other processes are not the same we have other SELECt or UPDATE or
INSERT processes on our Database
-Our database is MS SQL 20000

I'm afraid that I don't have much to add than teh suggestion to use
aba_lockinfo to get an overview of who is locking whom, and from this
try to understand why.

One situation that I should have mentioned is that if your application
has set up a command timeout (which is 30 seconds by default in many
APIs) and cancels the batch after this time, the application should
always submit a

IF @.@.trancount 0 ROLLBACK TRANSACTION

since a timeout expired does not rollback any transactions, and not rolling
back in this situations can lead to locks piling up.

Lock Pages in Memory

My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
Lock Pages in Memory setting is ignored.
We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
configuration). The only other application sharing SQL Server resources is
CLR.
With the Lock Pages in Memory setting ignored, does this mean that the 14GB
dedicated to SQL Server is fair game, to be taken as desired by other
processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Still hoping for an answer.
We had a server running 32bit with 2 GB RAM, converted it to 64bit with 16GB
RAM (14GB Max Server Memory) and in less than a week we are getting the
following error in our error log:
"A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
117484, committed (KB): 11856700, memory utilization: 0%."
We have not made any modifications to MemToLeave, so it is at the default
(256MB?). But from what I understand, 64bit gives you a huge extended memory.
So we have not done anything to MemToLeave.
Why do we have memory issues with 16GB RAM, 64bit, but did not have memory
issues on 2GB with 32 bit?
cbrichards wrote:
>My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
>Lock Pages in Memory setting is ignored.
>We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
>configuration). The only other application sharing SQL Server resources is
>CLR.
>With the Lock Pages in Memory setting ignored, does this mean that the 14GB
>dedicated to SQL Server is fair game, to be taken as desired by other
>processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||Hello there!
Have you read the following document?
http://support.microsoft.com/default.aspx/kb/918483
Ekrem Ã?nsoy
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7925eb5963cda@.uwe...
> My understanding is that running 64 bit SQL Server 2005 Standard Edition,
> the
> Lock Pages in Memory setting is ignored.
> We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server
> Memory
> configuration). The only other application sharing SQL Server resources is
> CLR.
> With the Lock Pages in Memory setting ignored, does this mean that the
> 14GB
> dedicated to SQL Server is fair game, to be taken as desired by other
> processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||I highly recommend you read the KB that was posted by Ekrem. I don't
believe Std Edition supports Lock Pages in Memory but you have to understand
there is a huge difference in how memory is utilized between 32 and 64 bit
and even SQL2000 and 2005. These messages are not related to the MemToLeave
area. That memory is pre-allocated and will not be paged out. But unlike in
32 bit the 64 bit SQL Server can utilize all of the memory for things
normally confined to the 2 or 3GB area in a 32 env and can use considerably
more memory than before. For instance the procedure cache would have been
limited to less than 2GB on your old server. Now it can grow at times to as
much as 75% ( exact amount changes with service packs and current conditions
and this is peak, usually won't be more than 50%) of total available memory.
This memory is all dynamic by default and if windows needs more it can call
for memory from SQL Server which will give you the messages you are seeing.
My guess is that you have a lot of adhoc SQL and the proc cache is huge due
to all the non-reusable plans. And or you have operations other than SQL
Server itself running on the server such as SSIS, CLR, Term Services,
Winzip, Notepad etc.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7929227ce194c@.uwe...
> Still hoping for an answer.
> We had a server running 32bit with 2 GB RAM, converted it to 64bit with
> 16GB
> RAM (14GB Max Server Memory) and in less than a week we are getting the
> following error in our error log:
> "A significant part of sql server process memory has been paged out. This
> may
> result in a performance degradation. Duration: 0 seconds. Working set
> (KB):
> 117484, committed (KB): 11856700, memory utilization: 0%."
> We have not made any modifications to MemToLeave, so it is at the default
> (256MB?). But from what I understand, 64bit gives you a huge extended
> memory.
> So we have not done anything to MemToLeave.
> Why do we have memory issues with 16GB RAM, 64bit, but did not have memory
> issues on 2GB with 32 bit?
> cbrichards wrote:
>>My understanding is that running 64 bit SQL Server 2005 Standard Edition,
>>the
>>Lock Pages in Memory setting is ignored.
>>We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server
>>Memory
>>configuration). The only other application sharing SQL Server resources is
>>CLR.
>>With the Lock Pages in Memory setting ignored, does this mean that the
>>14GB
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via http://www.sqlmonster.com
>|||Well, Hello Ekrem!
I have read that document, and that applies to SQL Server 2005 64 bit
Enterprise Edition. In SQL Server 2005 64 bit Standard Edition (which I noted
in my post, is what we run), that Lock Pages in Memory is ignored.
So, I am still, seeking for answers...
Ekrem Ã?nsoy wrote:
>Hello there!
>Have you read the following document?
>http://support.microsoft.com/default.aspx/kb/918483
>> My understanding is that running 64 bit SQL Server 2005 Standard Edition,
>> the
>[quoted text clipped - 9 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||So now the question is, if I cannot lock pages in memory, then is my only
choice to upgrade to Enterprise edition?
cbrichards wrote:
>Well, Hello Ekrem!
>I have read that document, and that applies to SQL Server 2005 64 bit
>Enterprise Edition. In SQL Server 2005 64 bit Standard Edition (which I noted
>in my post, is what we run), that Lock Pages in Memory is ignored.
>So, I am still, seeking for answers...
>>Hello there!
>[quoted text clipped - 6 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||Thanks Andrew for the information.
From what you wrote, I believe our server ran out of memory due to an export
of data from a 250,000,000 row table. This table holds 90 days worth of
archive data and the "adhoc" query was selecting 1 day to export.
I am still perplexed, because this same archive table was (a week ago) on a
32 bit server with 2 GB RAM, and nothing like this ever happened (running out
of memory). The volume of data, number of stored procedures, user connections,
etc., etc. are all the same as when it was on a 32 bit server. One month ago
I performed this same export when it was 32 bit, and while the CPU escalated,
the server did not crater.
I am having difficulty wrapping my arms around the fact that the server did
not crater under 32 bit and 2 GB RAM, but the same operation cratered 64 bit
with 16 GB RAM.
The following came from running DBCC MemoryStatus:
Procedure Cache Value
-- --
TotalProcs 88
TotalPages 2086
InUsePages 70
Any further insights would be appreciated. We are nervous to move forward
with 64 bit Standard Edition with all these unknowns.
Andrew J. Kelly wrote:
>I highly recommend you read the KB that was posted by Ekrem. I don't
>believe Std Edition supports Lock Pages in Memory but you have to understand
>there is a huge difference in how memory is utilized between 32 and 64 bit
>and even SQL2000 and 2005. These messages are not related to the MemToLeave
>area. That memory is pre-allocated and will not be paged out. But unlike in
>32 bit the 64 bit SQL Server can utilize all of the memory for things
>normally confined to the 2 or 3GB area in a 32 env and can use considerably
>more memory than before. For instance the procedure cache would have been
>limited to less than 2GB on your old server. Now it can grow at times to as
>much as 75% ( exact amount changes with service packs and current conditions
>and this is peak, usually won't be more than 50%) of total available memory.
>This memory is all dynamic by default and if windows needs more it can call
>for memory from SQL Server which will give you the messages you are seeing.
>My guess is that you have a lot of adhoc SQL and the proc cache is huge due
>to all the non-reusable plans. And or you have operations other than SQL
>Server itself running on the server such as SSIS, CLR, Term Services,
>Winzip, Notepad etc.
>> Still hoping for an answer.
>[quoted text clipped - 30 lines]
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||OK now we are getting somewhere. You keep feeding bits and pieces to us but
it would be great to have a more detailed description of the issue and
circumstances. Exactly how are you doing this data export? Is it SSIS? If
so that explains a lot. SSIS is a totally separate process from SQL Server
and it will use it's own memory space just like another app on the server.
On a 32 bit machine it can only use up to 2GB max but probably a lot less.
On the 64 bit it can use all it wants. Since the memory is dynamic it will
compete with SQL Server for sure if run on the same machine. SSIS like to do
the work totally in memory if at all possible and can use a lot more than
you might expect. If you provide more details we can give a more directed or
intelligent answer. Also if you do something like this routinely it sounds
like you may want to partition the data to suite these needs.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79322e221281d@.uwe...
> Thanks Andrew for the information.
> From what you wrote, I believe our server ran out of memory due to an
> export
> of data from a 250,000,000 row table. This table holds 90 days worth of
> archive data and the "adhoc" query was selecting 1 day to export.
> I am still perplexed, because this same archive table was (a week ago) on
> a
> 32 bit server with 2 GB RAM, and nothing like this ever happened (running
> out
> of memory). The volume of data, number of stored procedures, user
> connections,
> etc., etc. are all the same as when it was on a 32 bit server. One month
> ago
> I performed this same export when it was 32 bit, and while the CPU
> escalated,
> the server did not crater.
> I am having difficulty wrapping my arms around the fact that the server
> did
> not crater under 32 bit and 2 GB RAM, but the same operation cratered 64
> bit
> with 16 GB RAM.
> The following came from running DBCC MemoryStatus:
> Procedure Cache Value
> -- --
> TotalProcs 88
> TotalPages 2086
> InUsePages 70
> Any further insights would be appreciated. We are nervous to move forward
> with 64 bit Standard Edition with all these unknowns.
> Andrew J. Kelly wrote:
>>I highly recommend you read the KB that was posted by Ekrem. I don't
>>believe Std Edition supports Lock Pages in Memory but you have to
>>understand
>>there is a huge difference in how memory is utilized between 32 and 64 bit
>>and even SQL2000 and 2005. These messages are not related to the
>>MemToLeave
>>area. That memory is pre-allocated and will not be paged out. But unlike
>>in
>>32 bit the 64 bit SQL Server can utilize all of the memory for things
>>normally confined to the 2 or 3GB area in a 32 env and can use
>>considerably
>>more memory than before. For instance the procedure cache would have been
>>limited to less than 2GB on your old server. Now it can grow at times to
>>as
>>much as 75% ( exact amount changes with service packs and current
>>conditions
>>and this is peak, usually won't be more than 50%) of total available
>>memory.
>>This memory is all dynamic by default and if windows needs more it can
>>call
>>for memory from SQL Server which will give you the messages you are
>>seeing.
>>My guess is that you have a lot of adhoc SQL and the proc cache is huge
>>due
>>to all the non-reusable plans. And or you have operations other than SQL
>>Server itself running on the server such as SSIS, CLR, Term Services,
>>Winzip, Notepad etc.
>> Still hoping for an answer.
>>[quoted text clipped - 30 lines]
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
> --
> Message posted via http://www.sqlmonster.com
>|||> Is Max Server Memory even needed in this case?
Personally, I'd always set Max Server Memory and Min Server Memory on any
serious instance. Why let the SQL Server process engage in expensive back and
forth memory trading with OS?
Linchi
"cbrichards via SQLMonster.com" wrote:
> My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
> Lock Pages in Memory setting is ignored.
> We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
> configuration). The only other application sharing SQL Server resources is
> CLR.
> With the Lock Pages in Memory setting ignored, does this mean that the 14GB
> dedicated to SQL Server is fair game, to be taken as desired by other
> processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Interesting...
I am using SSIS to perform the export.
This adds more questions:
1. You stated, "On a 32 bit machine it can only use up to 2GB max but
probably a lot less." Why only 2GB and probably a lot less?
2. You stated, "On the 64 bit it can use all it wants. Since the memory is
dynamic..." Is this true even when using Enterprise edition when you can lock
pages in memory?
Andrew J. Kelly wrote:
>OK now we are getting somewhere. You keep feeding bits and pieces to us but
>it would be great to have a more detailed description of the issue and
>circumstances. Exactly how are you doing this data export? Is it SSIS? If
>so that explains a lot. SSIS is a totally separate process from SQL Server
>and it will use it's own memory space just like another app on the server.
>On a 32 bit machine it can only use up to 2GB max but probably a lot less.
>On the 64 bit it can use all it wants. Since the memory is dynamic it will
>compete with SQL Server for sure if run on the same machine. SSIS like to do
>the work totally in memory if at all possible and can use a lot more than
>you might expect. If you provide more details we can give a more directed or
>intelligent answer. Also if you do something like this routinely it sounds
>like you may want to partition the data to suite these needs.
>> Thanks Andrew for the information.
>[quoted text clipped - 65 lines]
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||> This adds more questions:
> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
> probably a lot less." Why only 2GB and probably a lot less?
Well if the system only had 2GB you need memory for other things as well.
But in 32 bit OS by default any app can only use up to 2GB of directly
addressable memory. If you had more than 2GB and the OS was capable of using
PAE and the app was AWE aware it may be able to use more than 2GB. But I
don't think SSIS was AWE aware so on 32 bit I believe it was only able to
use 2GB or 3GB if /3gb was set max. I could be wrong there but I am pretty
sure that is correct.
> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
> dynamic..." Is this true even when using Enterprise edition when you can
> lock
> pages in memory?
No. The purpose of Lock Pages is to prevent something else from stealing or
borrowing those pages once they are allocated. You can achieve a similar
functionality as Linchi mentioned by setting the MIN and MAX to the same
value. Then as long as you start SQL Server, use that much memory and the
memory is available it will keep it once it has used it. But now you have
another potentially serious issue and that is with the memory that is left.
If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the OS
and anything else you run. If you attempt to run that SSIS package again in
this mode you will surely starve the OS of memory and the machine and
everything on it will not be happy. This is one of the main reasons why it
is recommended you run larger SSIS packages on a separate machine.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79367cc8cda07@.uwe...
> Interesting...
> I am using SSIS to perform the export.
> This adds more questions:
> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
> probably a lot less." Why only 2GB and probably a lot less?
> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
> dynamic..." Is this true even when using Enterprise edition when you can
> lock
> pages in memory?
> Andrew J. Kelly wrote:
>>OK now we are getting somewhere. You keep feeding bits and pieces to us
>>but
>>it would be great to have a more detailed description of the issue and
>>circumstances. Exactly how are you doing this data export? Is it SSIS?
>>If
>>so that explains a lot. SSIS is a totally separate process from SQL Server
>>and it will use it's own memory space just like another app on the server.
>>On a 32 bit machine it can only use up to 2GB max but probably a lot less.
>>On the 64 bit it can use all it wants. Since the memory is dynamic it will
>>compete with SQL Server for sure if run on the same machine. SSIS like to
>>do
>>the work totally in memory if at all possible and can use a lot more than
>>you might expect. If you provide more details we can give a more directed
>>or
>>intelligent answer. Also if you do something like this routinely it
>>sounds
>>like you may want to partition the data to suite these needs.
>> Thanks Andrew for the information.
>>[quoted text clipped - 65 lines]
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||> 1. So, in my case, if SSIS were to be run on a separate machine, would it
> not
> equally starve the Memory on the separate machine?
If those are the only two processes on the machine they would live much
better together than with SQL Server and lock pages.
> 2. When running this SSIS export on the same machine, (loosely speaking)
> does
> SSIS first consume the 14GB, then go after the 2GB of the OS?
I don't know the allocations work at that level but I would say there are
factors that would make it a DEPENDS type of answer.
> 3. At what point does the 8TB of virtual address space get used.
Virtual address space is always used but if you mean when does it start
swapping to disk? Then generally when all the physical memory is depleted.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:793df7e614c62@.uwe...
> This raises a few more questions:
> 1. So, in my case, if SSIS were to be run on a separate machine, would it
> not
> equally starve the Memory on the separate machine?
> 2. When running this SSIS export on the same machine, (loosely speaking)
> does
> SSIS first consume the 14GB, then go after the 2GB of the OS?
> 3. At what point does the 8TB of virtual address space get used.
> Andrew J. Kelly wrote:
>> This adds more questions:
>> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
>> probably a lot less." Why only 2GB and probably a lot less?
>>Well if the system only had 2GB you need memory for other things as well.
>>But in 32 bit OS by default any app can only use up to 2GB of directly
>>addressable memory. If you had more than 2GB and the OS was capable of
>>using
>>PAE and the app was AWE aware it may be able to use more than 2GB. But I
>>don't think SSIS was AWE aware so on 32 bit I believe it was only able to
>>use 2GB or 3GB if /3gb was set max. I could be wrong there but I am
>>pretty
>>sure that is correct.
>> 2. You stated, "On the 64 bit it can use all it wants. Since the memory
>> is
>> dynamic..." Is this true even when using Enterprise edition when you can
>> lock
>> pages in memory?
>>No. The purpose of Lock Pages is to prevent something else from stealing
>>or
>>borrowing those pages once they are allocated. You can achieve a similar
>>functionality as Linchi mentioned by setting the MIN and MAX to the same
>>value. Then as long as you start SQL Server, use that much memory and the
>>memory is available it will keep it once it has used it. But now you have
>>another potentially serious issue and that is with the memory that is
>>left.
>>If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the
>>OS
>>and anything else you run. If you attempt to run that SSIS package again
>>in
>>this mode you will surely starve the OS of memory and the machine and
>>everything on it will not be happy. This is one of the main reasons why
>>it
>>is recommended you run larger SSIS packages on a separate machine.
>> Interesting...
>>[quoted text clipped - 32 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
> --
> Message posted via http://www.sqlmonster.com
>|||This raises a few more questions:
1. So, in my case, if SSIS were to be run on a separate machine, would it not
equally starve the Memory on the separate machine?
2. When running this SSIS export on the same machine, (loosely speaking) does
SSIS first consume the 14GB, then go after the 2GB of the OS?
3. At what point does the 8TB of virtual address space get used.
Andrew J. Kelly wrote:
>> This adds more questions:
>> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
>> probably a lot less." Why only 2GB and probably a lot less?
>Well if the system only had 2GB you need memory for other things as well.
>But in 32 bit OS by default any app can only use up to 2GB of directly
>addressable memory. If you had more than 2GB and the OS was capable of using
>PAE and the app was AWE aware it may be able to use more than 2GB. But I
>don't think SSIS was AWE aware so on 32 bit I believe it was only able to
>use 2GB or 3GB if /3gb was set max. I could be wrong there but I am pretty
>sure that is correct.
>> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
>> dynamic..." Is this true even when using Enterprise edition when you can
>> lock
>> pages in memory?
>No. The purpose of Lock Pages is to prevent something else from stealing or
>borrowing those pages once they are allocated. You can achieve a similar
>functionality as Linchi mentioned by setting the MIN and MAX to the same
>value. Then as long as you start SQL Server, use that much memory and the
>memory is available it will keep it once it has used it. But now you have
>another potentially serious issue and that is with the memory that is left.
>If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the OS
>and anything else you run. If you attempt to run that SSIS package again in
>this mode you will surely starve the OS of memory and the machine and
>everything on it will not be happy. This is one of the main reasons why it
>is recommended you run larger SSIS packages on a separate machine.
>> Interesting...
>[quoted text clipped - 32 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
--
Message posted via http://www.sqlmonster.com|||Thanks for all the info, Andrew.
Hopefully, (for your sake) this is my last set of questions. If I set my
Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS, then,
if I execute an expensive adhoc query directly on the server from Management
Studio, and that query consumes the 14GB, is that query able to consume any
of the OS memory?
On the flip side, if my Max Server Memory is set to 14GB and Min Server
Memory is left at its default (0), then with this configuration, the same
adhoc query is executed above, does that mean the query could consume both
the 14GB as well as the 2GB OS?
Andrew J. Kelly wrote:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would it
>> not
>> equally starve the Memory on the separate machine?
>If those are the only two processes on the machine they would live much
>better together than with SQL Server and lock pages.
>> 2. When running this SSIS export on the same machine, (loosely speaking)
>> does
>> SSIS first consume the 14GB, then go after the 2GB of the OS?
>I don't know the allocations work at that level but I would say there are
>factors that would make it a DEPENDS type of answer.
>> 3. At what point does the 8TB of virtual address space get used.
>Virtual address space is always used but if you mean when does it start
>swapping to disk? Then generally when all the physical memory is depleted.
>> This raises a few more questions:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would it
>[quoted text clipped - 49 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||> Hopefully, (for your sake) this is my last set of questions. If I set my
> Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS,
> then,
> if I execute an expensive adhoc query directly on the server from
> Management
> Studio, and that query consumes the 14GB, is that query able to consume
> any
> of the OS memory?
Well the portion of memory that used to be called MemTo Leave still comes
into play as a section of memory pre-reserved at startup and is not part of
the buffer pool. In 2000 that defaulted to about 384MB (128M for the worker
threads). In 2005 the allocations are a little more complicated but lets
just say 384MB comes right off the top. The MAX Memory is for the buffer
pool only. So you can theoriticallyuse 14GB for bufferpool and 384MB for
Contiguous memory. That will leave about 1.7GB for the OS. Having said that
none of the 1.7GB will be used for a query in SQL Server under the
conditions you outlined.
> On the flip side, if my Max Server Memory is set to 14GB and Min Server
> Memory is left at its default (0), then with this configuration, the same
> adhoc query is executed above, does that mean the query could consume both
> the 14GB as well as the 2GB OS?
No, this still follows the exact same rules. The MIN just states that after
SQL Server grabs that memory it will not give it back to the OS.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:793ea42168c08@.uwe...
> Thanks for all the info, Andrew.
> Hopefully, (for your sake) this is my last set of questions. If I set my
> Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS,
> then,
> if I execute an expensive adhoc query directly on the server from
> Management
> Studio, and that query consumes the 14GB, is that query able to consume
> any
> of the OS memory?
> On the flip side, if my Max Server Memory is set to 14GB and Min Server
> Memory is left at its default (0), then with this configuration, the same
> adhoc query is executed above, does that mean the query could consume both
> the 14GB as well as the 2GB OS?
> Andrew J. Kelly wrote:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would
>> it
>> not
>> equally starve the Memory on the separate machine?
>>If those are the only two processes on the machine they would live much
>>better together than with SQL Server and lock pages.
>> 2. When running this SSIS export on the same machine, (loosely speaking)
>> does
>> SSIS first consume the 14GB, then go after the 2GB of the OS?
>>I don't know the allocations work at that level but I would say there are
>>factors that would make it a DEPENDS type of answer.
>> 3. At what point does the 8TB of virtual address space get used.
>>Virtual address space is always used but if you mean when does it start
>>swapping to disk? Then generally when all the physical memory is
>>depleted.
>> This raises a few more questions:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would
>> it
>>[quoted text clipped - 49 lines]
>>>>processes whenever needed? Is Max Server Memory even needed in
>>>>this
>>>>case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Thanks for the advice Linchi.
I am trying to understand the difference between the ability to lock pages in
memory on 64 bit Enterprise Edition and setting the Min/Max Server Memory
setting the same on a 64 bit Standard Edition.
We are running 64 bit Standard Edition, with 16 GB RAM, and have the Min
Server Memory set to zero and Max Server Memory set to 14336.
We are still experiencing a shortage of memory and I believe it is either a
separate application, such as Reporting Services or SSIS that is depleting
our memory.
So, it this were an Enterprise addition box (instead of Standard Edition),
and I had the ability to Lock Pages in Memory, how would this change my
situation?
Or, with my current Standard Edition situation, how would setting the Min and
Max Server Memory to the same amount change my situation?
Linchi Shea wrote:
>> Is Max Server Memory even needed in this case?
>Personally, I'd always set Max Server Memory and Min Server Memory on any
>serious instance. Why let the SQL Server process engage in expensive back and
>forth memory trading with OS?
>Linchi
>> My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
>> Lock Pages in Memory setting is ignored.
>[quoted text clipped - 6 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1sql

Monday, March 19, 2012

LocalReport.Render problem

At the moment my Intranet contains a few reports which are all shown using one reportviewer. I use this bit of code to switch one RDLC for another

===

ReportViewer1.LocalReport.DataSources.Clear();

ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("dsstrafstudie_DataTable1","ObjectDataSource2"));

ReportViewer1.LocalReport.ReportPath = "rptstrafstudie.rdlc";

ObjectDataSource2.FilterExpression = "datum = '" + Request.QueryString["datum"]+ "'";

===

It works just fine ! Now I Want to skip the reportviewer VIEW and just let the people download EXCEL or PDF export. So I add this bit of code

===

Warning[] warnings;

string[] streamids;

string mimeType;

string encoding;

string extension;

byte[] bytes = ReportViewer1.LocalReport.Render("Excel", null, out mimeType, out encoding, out extension, out streamids, out warnings);

FileStream fs = new FileStream(@."c:\output.xls", FileMode.Create);

fs.Write(bytes, 0, bytes.Length);

fs.Close();

===

But I keep getting the error "A data source instance has not been supplied for the data source "dsstrafstudie_DataTable1". Alltough I DO supply a DS instance only a few rules above ? What on earth am I doing wrong?

Thx in advance,

Puitje

Hi Puitje,

Did you find a solution for this error? I am experiencing the exact problem.

Thanks

|||Instead of using "ReportViewer1.LocalReport" create a new instance of a LocalReport and use that one.|||

I use a new variable LocalReport rp1, but error doesn't change ...

If i didin't use a datasource for the report the code works correctly.

Someone have any idea ?

Thanks

|||Did you add the datasource when you used Render?|||

Yes cause i copy the same code that visualize the Report correctly and i put it in a new function.

The function who render at video works correctly, the function who render manually give me the exception.

|||Are you sure that the objectdatasource you provided is correctly populated? Maybe you lost some event that populates it?|||I

I post the code :

these two function are called on Page_Load event ( the same on Page_PreRender).

The first function works correctly and display on my page the report with correct data.

The second function generate an exception on Render call.

He didn't find "DataforReport" istance.

The rdlc is a converted rdl.

FIRST

virtual public Boolean VisualizzaReport(ref Hashtable par)
{
ReportViewer rp1;
ReportDataSource ds1;
SqlDataSource sql1;
Database dbRep;
ControlParameter cp1;
Label l1;

// Definisce l'SqlDataSource per poi definire il ReportDataSource in funzione di questo
sql1 = new SqlDataSource();
sql1.ID = ReportPage.PageContent.SqlReportSource.ToString();
dbRep = new Database(this.NomeDatabase, this.NomeSP, ref sql1, 0);
sql1.SelectParameters.Clear();

foreach (DictionaryEntry de in par)
{
l1 = new Label();
l1.ID = "Label_"+de.Key.ToString();
l1.Text=de.Value.ToString();
l1.Visible = false;
this.content.Controls.Add(l1);

cp1 = new ControlParameter();
cp1.ControlID = l1.ID = "Label_" + de.Key.ToString();
cp1.Name = de.Key.ToString();
cp1.PropertyName = "Text";
cp1.Type = Type.GetTypeCode(de.Value.GetType());
// TypeCode.Int32;
sql1.SelectParameters.Add(cp1);
}
this.content.Controls.Add(sql1);
// Definisce il reportDataSource
ds1 = new ReportDataSource();
ds1.Name = "DataforReport";
ds1.DataSourceId = ReportPage.PageContent.SqlReportSource.ToString();

// Definisce il Report
rp1 = new ReportViewer();
rp1.ID = ReportPage.PageContent.ReportLocale.ToString();
rp1.ShowToolBar = false;
rp1.Width = new Unit(100, UnitType.Percentage); ;
rp1.Height = new Unit(100, UnitType.Percentage); ;
//rp1.ID = ReportPage.PageContent.ReportLocale.ToString();
rp1.LocalReport.DisplayName = this.NomeReport;
rp1.LocalReport.ReportPath = this.NomefileReport;
rp1.LocalReport.DataSources.Clear();
rp1.LocalReport.DataSources.Add(ds1);
rp1.LocalReport.Refresh();
this.content.Controls.Add(rp1);

return true;
}

SECOND

virtual public Boolean RenderReport(ref Hashtable par)
{
ReportViewer rp1;
ReportDataSource ds1;
SqlDataSource sql1;
Database dbRep;
ControlParameter cp1;
Label l1;

sql1 = new SqlDataSource();
sql1.ID = ReportPage.PageContent.SqlReportSource.ToString();
dbRep = new Database(this.NomeDatabase, this.NomeSP, ref sql1, 0);
sql1.SelectParameters.Clear();

foreach (DictionaryEntry de in par)
{
l1 = new Label();
l1.ID = "Label_" + de.Key.ToString();
l1.Text = de.Value.ToString();
l1.Visible = false;
this.content.Controls.Add(l1);

cp1 = new ControlParameter();
cp1.ControlID = l1.ID = "Label_" + de.Key.ToString();
cp1.Name = de.Key.ToString();
cp1.PropertyName = "Text";
cp1.Type = Type.GetTypeCode(de.Value.GetType());
// TypeCode.Int32;
sql1.SelectParameters.Add(cp1);
}
this.content.Controls.Add(sql1);
// Definisce il reportDataSource
ds1 = new ReportDataSource();
ds1.Name = "DataforReport" ;
ds1.DataSourceId = ReportPage.PageContent.SqlReportSource.ToString();

ReportViewer rp2;
rp2 = new ReportViewer();
rp2.LocalReport.ReportPath = "Fattura.rdlc";
rp2.LocalReport.DataSources.Clear();
rp2.LocalReport.DataSources.Add(ds1);
string deviceInfo =

"<DeviceInfo>" +

" <OutputFormat>PDF</OutputFormat>" +

" <PageWidth>8.5in</PageWidth>" +

" <PageHeight>11in</PageHeight>" +

" <MarginTop>0.5in</MarginTop>" +

" <MarginLeft>1in</MarginLeft>" +

" <MarginRight>1in</MarginRight>" +

" <MarginBottom>0.5in</MarginBottom>" +

"</DeviceInfo>";
byte[] data =rp2.LocalReport.Render("PDF", deviceInfo, out mimeType,out encoding, out extension, out streamids, out warnings);
FileStream fs = new FileStream(@."c:\prova.pdf", FileMode.Create);
fs.Write(data, 0, data.Length);
fs.Close();
//determine if format is rendered to the web or a file.

return true;
}

|||Why do you define 2 datasources? When do you add data to ds1?|||I think the refresh method in the first code triggers the datasource updating...

LocalReport.Render does not return

Sorry for the cross-post, but I'm not sure what the best place to post this
is, and I'm getting a bit desparate.
Part of our product uses the SQL Server Reporting engine to create reports
in Excel and PDF format. To do this I am using the LocalReport object the
Microsoft.ReportViewer.WinForms namespace. This is all running in a
web-based service running in IIS.
In development and UAT this has worked flawlessly and had been very powerful
and convenient. Having just put some of our systems live, we are suddenly
running into problems that I am unable to work out how to resolve.
Everything works fine at first, but after the service has been running for
between 20 and 40 minutes, suddenly the report generation stops working.
I've added debug logging to the application, and can see that the last
statement that gets executed is a call to the LocalReport.Render method.
This never returns.
If we recycle the application pool, the reports start working again -- for
another 20 to 40 minutes. Then they stop, just as before.
Setting the application pools to automatically recycle every 20 minutes
seems like a very bad solution to this. I would really like to understand
why it is that the system is locking up. But without being able to step into
the source code for the LocalReport object, I'm completely at a loss as to
how I can resolve this.
Does anyone have any suggestions as to what I could try?
We're using VS2005 and ASP.NET v2.0, running in IIS6 on Windows Server 2003.
My thanks in advance,
--
(O)enoneHave you tried using the WebForms version? Usually when there are a WinForms
and WebForms versions of the same item it's due to the fact that the
WinForms version will not run in the ASP.Net threading model, or uses
services that are not applicable in that environment.
--
Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199...2006
"Oenone" <oenone@.nowhere.com> wrote in message
news:eLhRw6PVHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Sorry for the cross-post, but I'm not sure what the best place to post
> this is, and I'm getting a bit desparate.
> Part of our product uses the SQL Server Reporting engine to create reports
> in Excel and PDF format. To do this I am using the LocalReport object the
> Microsoft.ReportViewer.WinForms namespace. This is all running in a
> web-based service running in IIS.
> In development and UAT this has worked flawlessly and had been very
> powerful and convenient. Having just put some of our systems live, we are
> suddenly running into problems that I am unable to work out how to
> resolve.
> Everything works fine at first, but after the service has been running for
> between 20 and 40 minutes, suddenly the report generation stops working.
> I've added debug logging to the application, and can see that the last
> statement that gets executed is a call to the LocalReport.Render method.
> This never returns.
> If we recycle the application pool, the reports start working again -- for
> another 20 to 40 minutes. Then they stop, just as before.
> Setting the application pools to automatically recycle every 20 minutes
> seems like a very bad solution to this. I would really like to understand
> why it is that the system is locking up. But without being able to step
> into the source code for the LocalReport object, I'm completely at a loss
> as to how I can resolve this.
> Does anyone have any suggestions as to what I could try?
> We're using VS2005 and ASP.NET v2.0, running in IIS6 on Windows Server
> 2003.
> My thanks in advance,
> --
> (O)enone
>|||Mark Fitzpatrick wrote:
> Have you tried using the WebForms version?
Hmm, interesting, no I haven't.
The application in question can potentially be driven from both a web
service and also a Windows Forms application (it's implemented in a separate
DLL that can be used in either environment). I initially wrote it all in the
WinForms environment, and hence by the time I got to running it from the web
I'd forgotten all about the existence of the WebForms version.
As we're only using it to programmatically generate the report content
(we're not using the report viewer control at all), I just assumed that the
WinForms version would work correctly in both environments.
Anyway, I've rebuilt the application using the WebForms version of the DLL
(which was nice and easy), and have deployed that to our productions server,
now I just need to wait and see if it makes a difference. I'll post back
with the results.
Thanks for giving me something else to try! :)
--
(O)enone|||Oenone wrote:
>> Have you tried using the WebForms version?
> Hmm, interesting, no I haven't.
Sadly this has exactly the same problem as the WinForms version. After
working fine for a little while, it ultimately stops returning each time it
is called.
Any other suggestions?
--
(O)enone

Wednesday, March 7, 2012

Local SQL Service Not Starting but SQL Server is running?

Hello there,

This is a bit of strange one.
My local version of SQL Server fails to start when I boot yet if I
start Enterprise Manager its started and working fine. ISQL or Query
Analyzer is also not working.

If I try and register the server by another SQL Server is not there. Me
thinks that the named pipes might be a bit broken but would appreciate
any advise from people.

Thanks

GintersCouple of thoughts. One the service is not starting until you start EM and
click on the server? By default when a server is registered in EM it sets
the checkbox for automatically start. Right click the server in EM, choose
Edit server registration and look at the bottom checkbox.

Another is only shared memory is working and you are trying to connect from
isql (osql) and QA remotely? In this case check the SQL server error log
for what protocols SQL started and verify that on the client that these
protocols are activated in the client network utility.

If this is a default installation then shared memory, named pipes, and tcp
are all active. Is the MSSQLServer service set to start automatically?

"Ginters" <jpmcginty@.talk21.com> wrote in message
news:1108124975.337210.24500@.f14g2000cwb.googlegro ups.com...
> Hello there,
> This is a bit of strange one.
> My local version of SQL Server fails to start when I boot yet if I
> start Enterprise Manager its started and working fine. ISQL or Query
> Analyzer is also not working.
> If I try and register the server by another SQL Server is not there. Me
> thinks that the named pipes might be a bit broken but would appreciate
> any advise from people.
> Thanks
> Ginters|||Ginters (jpmcginty@.talk21.com) writes:
> This is a bit of strange one.
> My local version of SQL Server fails to start when I boot yet

Do you get any error message?

Do you have the service set to start automatically?

> if I start Enterprise Manager its started and working fine. ISQL or Query
> Analyzer is also not working.

Even if SQL Server is running?

Which operating system and which version of SQL Server are you using?

> If I try and register the server by another SQL Server is not there. Me
> thinks that the named pipes might be a bit broken but would appreciate
> any advise from people.

Which protcols have you enabled in the Client Network Utility? Is
shared memory on?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

local SQL Server 2005 Express and Host with SQL Server 2000

I'm creating an app and have SQL Server 2005 Express installed. Most of the hosts now offering SQL Server 2005 are a bit pricey compared to those only offering 2000. Will I have any difficulty uploading my site and running the db on SQL 2000?
Thanks in advance.I've been searching these forums but all people having about the same question, all have no replies in the thread.

I'd love some feedback on this issue.
I'm quite new to asp.net.

I've got vs installed with sql 2005 express. but I also installed sql 2000 since my host is still using this one.
how can I port the db ? can I use the advanced controls (like login, membership, roles) if using a sql 2000 db ?

thank you|||ok I found some more replies in other threads.

basically I think my best approach should/could be:

-develop the website directly with ms sql 2000 as my database installed locally.
I guess the advanced features related to security, users, roles aren't configurable through the new admin panel of asp.net (related to the aspnetdb.* )
but If I understand correctly I could use sql 2005 for that and then run the tool inside the .NET 2.0 folder to port them into a mssql 2000 database. am I correct ?

any recommendations are welcome ;)

thanks|||

Because it depends on what you are doing. Yes, you can use sql 2000. Yes, you can move the data. No, sql 2000 can't do everything sql 2005 can, so if you write custom stored procedures, views, etc then you'll have to change them if they don't work on sql 2000.

The aspnetdb works fine on sql 2000, as does the sql providers that shipped with vs 2005. The problem is that it varies from hosting provider to hosting provider on how you get access to the sql box, and therefore you can't really give any instructions on "This is how you move your tables, views, stored procedures, indexes, triggers, and data easily".

Monday, February 20, 2012

Local - Printing margins are off by ~1/8 inch

I am printing my reports from my local report viewr, and the margins are off by just a little bit. It looks like printing shifts the contents of the report 1/8 inch to the right, borking the margins.

However, when I print the report I get it shifted to the right by 1/8 inch. This leaves me with a 7/8" margin on the left, and a 5/8" margin on the right.

Setting the margins to Left: 0.625, Right: 0.875 (5/8&7/8) actually causes it to print centered, so I know it is paying attention. When I do this, the print layout mode shows the contents shifted to the left 1/8", as expected.

I assume that people print using this control, or at least it was tested, so has anyone else seen this or is it a confirmed bug?

//5ive

Check which version of SSRS you are running. I would highly suggest visting this page http://www.sqlservercentral.com/columnists/sjones/2960.asp and reading the information. It helped me a lot as I had also been frequently dealing with this printing bug. Good luck!

Local - Printing margins are off by ~1/8 inch

I am printing my reports from my local report viewr, and the margins are off by just a little bit. It looks like printing shifts the contents of the report 1/8 inch to the right, borking the margins.

However, when I print the report I get it shifted to the right by 1/8 inch. This leaves me with a 7/8" margin on the left, and a 5/8" margin on the right.

I assume that people print using this control, or at least it was tested, so has anyone else seen this or is it a confirmed bug?

//5ive

Loads of System Generated Procedures In publisher

Dear Friends,
I have a database which is a published for 3 subscriber with bit of
variations. now while checking i found that there are 8870 system generated
procedures in the database with name.
ap_sel_0394BA4C0A144B4DB88E9E00ED0446B3_pal or similar way.
please suggest do i need the same.
Can i clean this? How?
Best regards
Sharad
Dear Paul,
Thanks. You have guide me several times i salute you for the knowledge you
have.
now with the query you have given i got the results of 56 rows but there are
8000+ procedure do i need to delete the rest.
Thanks and best regards
Sharad.
"Sharad2005" wrote:

> Dear Friends,
> I have a database which is a published for 3 subscriber with bit of
> variations. now while checking i found that there are 8870 system generated
> procedures in the database with name.
> ap_sel_0394BA4C0A144B4DB88E9E00ED0446B3_pal or similar way.
> please suggest do i need the same.
> Can i clean this? How?
> Best regards
> Sharad

Monday, March 26, 2012

lock question

Lock Problem and application is slowed down

Lock Pages in Memory

Monday, March 19, 2012

LocalReport.Render problem

LocalReport.Render does not return

Wednesday, March 7, 2012

Local SQL Service Not Starting but SQL Server is running?

local SQL Server 2005 Express and Host with SQL Server 2000

Monday, February 20, 2012

Local - Printing margins are off by ~1/8 inch

Local - Printing margins are off by ~1/8 inch

Loads of System Generated Procedures In publisher

sql local data

Blog Archive

About Me