Do some statistics on calling a specific stored function

Dear all,
We would like to keep a counter on how many time a specific stored fuction
is called.
At first, we want to add this counter inside the function but update record
to table is not allow in stored function.
Is there any other method to do so?
IvanYou could run profiler, or you can have audit statements before the function
is called if it is within a proc.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ivan" <ivan@.microsoft.com> wrote in message
news:uF76fgk%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Dear all,
> We would like to keep a counter on how many time a specific stored fuction
> is called.
> At first, we want to add this counter inside the function but update
> record to table is not allow in stored function.
> Is there any other method to do so?
> Ivan
>

Do some statistics on calling a specific stored function

Dear all,
We would like to keep a counter on how many time a specific stored fuction
is called.
At first, we want to add this counter inside the function but update record
to table is not allow in stored function.
Is there any other method to do so?
IvanYou could run profiler, or you can have audit statements before the function
is called if it is within a proc.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ivan" <ivan@.microsoft.com> wrote in message
news:uF76fgk%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Dear all,
> We would like to keep a counter on how many time a specific stored fuction
> is called.
> At first, we want to add this counter inside the function but update
> record to table is not allow in stored function.
> Is there any other method to do so?
> Ivan
>

Do not render report with no data

Hi!
I would like to stop processing of a report that has no data in a specific
dataset. Is this somehow possible?
In Access its possible to not process a report if there is no data behind
it.
Or is the only way to write an application that checks if the data exist and
if not just skips the Render part?
Thanks for any hints!
rgds,
tomOr can I somehow throw an excpetion inside of the report if a specific
dataset has no data?
"Thomas Kern" <tomiknocker@.hotmail.com> wrote in message
news:OUgDeGX$EHA.3372@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I would like to stop processing of a report that has no data in a
> specific dataset. Is this somehow possible?
> In Access its possible to not process a report if there is no data behind
> it.
> Or is the only way to write an application that checks if the data exist
> and if not just skips the Render part?
> Thanks for any hints!
> rgds,
> tom
>|||Thomas Kern wrote:
> Or can I somehow throw an excpetion inside of the report if a specific
> dataset has no data?
You can use the rowcount-property of the dataset and that the
report-visibility or your dataregion or elements to true or false.
regards
Frank
www.xax.de|||Where is the RowCount property of a dataset?
I need to limit mine...
thanks,
trint
Frank Matthiesen wrote:
> Thomas Kern wrote:
> > Or can I somehow throw an excpetion inside of the report if a
specific
> > dataset has no data?
> You can use the rowcount-property of the dataset and that the
> report-visibility or your dataregion or elements to true or false.
> regards
> Frank
> www.xax.de|||Use the CountRows aggregate function. E.g. =CountRows("DatasetName")
See also:
http://msdn.microsoft.com/library/en-us/rscreate/htm/rcr_creating_expressions_v1_0k6r.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"trint" <trinity.smith@.gmail.com> wrote in message
news:1106067185.185429.115960@.z14g2000cwz.googlegroups.com...
> Where is the RowCount property of a dataset?
> I need to limit mine...
> thanks,
> trint
>
> Frank Matthiesen wrote:
> > Thomas Kern wrote:
> > > Or can I somehow throw an excpetion inside of the report if a
> specific
> > > dataset has no data?
> >
> > You can use the rowcount-property of the dataset and that the
> > report-visibility or your dataregion or elements to true or false.
> >
> > regards
> >
> > Frank
> > www.xax.de
>|||how can I set the report visibility to false?
I really want to prevent to report from beeing generated in this case.
thanks.
"Frank Matthiesen" <fm@.xax.de> wrote in message
news:354qr8F4969gvU1@.individual.net...
> Thomas Kern wrote:
>> Or can I somehow throw an excpetion inside of the report if a specific
>> dataset has no data?
> You can use the rowcount-property of the dataset and that the
> report-visibility or your dataregion or elements to true or false.
> regards
> Frank
> www.xax.de
>
>|||I found the following solution but its more database-centric:
-) Check the @.@.rowcount of the query inside the Stored Procedure.
-) If @.@.rowcount = 0, RAISERROR
here we go: this is becomes an exception in the report and it is not
rendered!
tom
"Thomas Kern" <tomiknocker@.hotmail.com> wrote in message
news:us3ocLa$EHA.2984@.TK2MSFTNGP09.phx.gbl...
> how can I set the report visibility to false?
> I really want to prevent to report from beeing generated in this case.
> thanks.
> "Frank Matthiesen" <fm@.xax.de> wrote in message
> news:354qr8F4969gvU1@.individual.net...
>> Thomas Kern wrote:
>> Or can I somehow throw an excpetion inside of the report if a specific
>> dataset has no data?
>> You can use the rowcount-property of the dataset and that the
>> report-visibility or your dataregion or elements to true or false.
>> regards
>> Frank
>> www.xax.de
>>
>

Do I need to examine locking for this?

I'm using SQL Server 2000.
I have a situation where I need to select a variable amount of records
for a specific type_id and status_id and update some fields. Now
multiple users are going to be using this at the same time, and I want
to prevent multiple users from updating the same records.
Here's what I have so far:
CREATE TABLE [dbo].[label] (
[label_id] [int] NOT NULL ,
[label_status_id] [smallint] NOT NULL ,
[label_type_id] [smallint] NOT NULL ,
[master_job_id] [int] NULL,
[label_reprint_index] [int] NULL
) ON [PRIMARY]
CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
(
@.lt_id bigint,
@.mj_id bigint,
@.num_labels bigint
)
AS
declare @.t table
(
label_id int Primary Key
)
set rowcount @.numlabels
insert into @.t
select
label_id
from
label
where
label_type_id = @.lt_id
and label_status_id = 1
set rowcount 0
declare @.count int
set @.count = 0
update
label
set
master_job_id = @.mj_id,
label_status_id = 2,
@.count = label_reprint_index = @.count + 1
where
label_id in (
select label_id
from @.t
)
I have to use a FIFO update for the labels, so do I need to build in
protection to keep multiple users from updating the same records?1) Can't you combine your sproc into a single statement instead of using a
table variable?
2) If you do the above, a simple begin tran/update/error
check/rollback-commit sequence will ensure each record only gets updated by
a single process (which ever fires off first). This could lead to
contention if you are doing large ranges of rows.
3) Timestamping is another mechanism used to ensure rows are not changed
underneath you between your initial grab and the actual update.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
> I'm using SQL Server 2000.
> I have a situation where I need to select a variable amount of records
> for a specific type_id and status_id and update some fields. Now
> multiple users are going to be using this at the same time, and I want
> to prevent multiple users from updating the same records.
> Here's what I have so far:
> CREATE TABLE [dbo].[label] (
> [label_id] [int] NOT NULL ,
> [label_status_id] [smallint] NOT NULL ,
> [label_type_id] [smallint] NOT NULL ,
> [master_job_id] [int] NULL,
> [label_reprint_index] [int] NULL
> ) ON [PRIMARY]
> CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
> (
> @.lt_id bigint,
> @.mj_id bigint,
> @.num_labels bigint
> )
> AS
> declare @.t table
> (
> label_id int Primary Key
> )
> set rowcount @.numlabels
> insert into @.t
> select
> label_id
> from
> label
> where
> label_type_id = @.lt_id
> and label_status_id = 1
> set rowcount 0
>
> declare @.count int
> set @.count = 0
> update
> label
> set
> master_job_id = @.mj_id,
> label_status_id = 2,
> @.count = label_reprint_index = @.count + 1
> where
> label_id in (
> select label_id
> from @.t
> )
> I have to use a FIFO update for the labels, so do I need to build in
> protection to keep multiple users from updating the same records?
>|||Could you plese explain how I would use timestamping? I've looked it
up, but I'm not quite sure how to go about it.
Quote:
2) If you do the above, a simple begin tran/update/error
check/rollback-commit sequence will ensure each record only gets
updated by
a single process (which ever fires off first). This could lead to
contention if you are doing large ranges of rows.
So if I just use the update statement then if multiple users are
updating 300000 records at a time, I may run into the situation where
they are trying to update the same pages right?
On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) Can't you combine your sproc into a single statement instead of using a
> table variable?
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> 3) Timestamping is another mechanism used to ensure rows are not changed
> underneath you between your initial grab and the actual update.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Jason Lepack" <jlep...@.gmail.com> wrote in message
> news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
>
> > I'm using SQL Server 2000.
> > I have a situation where I need to select a variable amount of records
> > for a specific type_id and status_id and update some fields. Now
> > multiple users are going to be using this at the same time, and I want
> > to prevent multiple users from updating the same records.
> > Here's what I have so far:
> > CREATE TABLE [dbo].[label] (
> > [label_id] [int] NOT NULL ,
> > [label_status_id] [smallint] NOT NULL ,
> > [label_type_id] [smallint] NOT NULL ,
> > [master_job_id] [int] NULL,
> > [label_reprint_index] [int] NULL
> > ) ON [PRIMARY]
> > CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
> > (
> > @.lt_id bigint,
> > @.mj_id bigint,
> > @.num_labels bigint
> > )
> > AS
> > declare @.t table
> > (
> > label_id int Primary Key
> > )
> > set rowcount @.numlabels
> > insert into @.t
> > select
> > label_id
> > from
> > label
> > where
> > label_type_id = @.lt_id
> > and label_status_id = 1
> > set rowcount 0
> > declare @.count int
> > set @.count = 0
> > update
> > label
> > set
> > master_job_id = @.mj_id,
> > label_status_id = 2,
> > @.count = label_reprint_index = @.count + 1
> > where
> > label_id in (
> > select label_id
> > from @.t
> > )
> > I have to use a FIFO update for the labels, so do I need to build in
> > protection to keep multiple users from updating the same records... Hide quoted text -
> - Show quoted text -|||I'm slowly renovating this database from using cursors to using set
based logic.
The reason for the table variable is that user parameters
(workstation, username, etc) that are passed to this stored procedure
must be used to update a log table because Windows Domain Security is
not used and if I just used master..sysprocesses.loginname every
transaction would have the username "aspnet"
If I were to begin a transaction and select the records into @.t using
the UPDLOCK hint would that then hold the lock on those records until
the update of the rows was done?
I expect this transaction to take about 1-2 seconds and the amount of
transactions will not be large, so I don't expect too much contention.
Cheers,
Jason Lepack
On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
> > 1) Can't you combine your sproc into a single statement instead of using a
> > table variable?
> > 2) If you do the above, a simple begin tran/update/error
> > check/rollback-commit sequence will ensure each record only gets updated by
> > a single process (which ever fires off first). This could lead to
> > contention if you are doing large ranges of rows.
> > 3) Timestamping is another mechanism used to ensure rows are not changed
> > underneath you between your initial grab and the actual update.
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> > "Jason Lepack" <jlep...@.gmail.com> wrote in message
> >news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
> > > I'm using SQL Server 2000.
> > > I have a situation where I need to select a variable amount of records
> > > for a specific type_id and status_id and update some fields. Now
> > > multiple users are going to be using this at the same time, and I want
> > > to prevent multiple users from updating the same records.
> > > Here's what I have so far:
> > > CREATE TABLE [dbo].[label] (
> > > [label_id] [int] NOT NULL ,
> > > [label_status_id] [smallint] NOT NULL ,
> > > [label_type_id] [smallint] NOT NULL ,
> > > [master_job_id] [int] NULL,
> > > [label_reprint_index] [int] NULL
> > > ) ON [PRIMARY]
> > > CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
> > > (
> > > @.lt_id bigint,
> > > @.mj_id bigint,
> > > @.num_labels bigint
> > > )
> > > AS
> > > declare @.t table
> > > (
> > > label_id int Primary Key
> > > )
> > > set rowcount @.numlabels
> > > insert into @.t
> > > select
> > > label_id
> > > from
> > > label
> > > where
> > > label_type_id = @.lt_id
> > > and label_status_id = 1
> > > set rowcount 0
> > > declare @.count int
> > > set @.count = 0
> > > update
> > > label
> > > set
> > > master_job_id = @.mj_id,
> > > label_status_id = 2,
> > > @.count = label_reprint_index = @.count + 1
> > > where
> > > label_id in (
> > > select label_id
> > > from @.t
> > > )
> > > I have to use a FIFO update for the labels, so do I need to build in
> > > protection to keep multiple users from updating the same records... Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -|||1) Yes, begin tran, selecting records using updlock/holdlock would prevent
other from getting those records for change until after your commit
2) You may be surprised about performance if you have 300K-rows-per-updates
going on.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181574986.867864.46950@.p47g2000hsd.googlegroups.com...
> I'm slowly renovating this database from using cursors to using set
> based logic.
> The reason for the table variable is that user parameters
> (workstation, username, etc) that are passed to this stored procedure
> must be used to update a log table because Windows Domain Security is
> not used and if I just used master..sysprocesses.loginname every
> transaction would have the username "aspnet"
> If I were to begin a transaction and select the records into @.t using
> the UPDLOCK hint would that then hold the lock on those records until
> the update of the rows was done?
> I expect this transaction to take about 1-2 seconds and the amount of
> transactions will not be large, so I don't expect too much contention.
> Cheers,
> Jason Lepack
> On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
>> Could you plese explain how I would use timestamping? I've looked it
>> up, but I'm not quite sure how to go about it.
>> Quote:
>> 2) If you do the above, a simple begin tran/update/error
>> check/rollback-commit sequence will ensure each record only gets
>> updated by
>> a single process (which ever fires off first). This could lead to
>> contention if you are doing large ranges of rows.
>> So if I just use the update statement then if multiple users are
>> updating 300000 records at a time, I may run into the situation where
>> they are trying to update the same pages right?
>> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>>
>> > 1) Can't you combine your sproc into a single statement instead of
>> > using a
>> > table variable?
>> > 2) If you do the above, a simple begin tran/update/error
>> > check/rollback-commit sequence will ensure each record only gets
>> > updated by
>> > a single process (which ever fires off first). This could lead to
>> > contention if you are doing large ranges of rows.
>> > 3) Timestamping is another mechanism used to ensure rows are not
>> > changed
>> > underneath you between your initial grab and the actual update.
>> > --
>> > TheSQLGuru
>> > President
>> > Indicium Resources, Inc.
>> > "Jason Lepack" <jlep...@.gmail.com> wrote in message
>> >news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
>> > > I'm using SQL Server 2000.
>> > > I have a situation where I need to select a variable amount of
>> > > records
>> > > for a specific type_id and status_id and update some fields. Now
>> > > multiple users are going to be using this at the same time, and I
>> > > want
>> > > to prevent multiple users from updating the same records.
>> > > Here's what I have so far:
>> > > CREATE TABLE [dbo].[label] (
>> > > [label_id] [int] NOT NULL ,
>> > > [label_status_id] [smallint] NOT NULL ,
>> > > [label_type_id] [smallint] NOT NULL ,
>> > > [master_job_id] [int] NULL,
>> > > [label_reprint_index] [int] NULL
>> > > ) ON [PRIMARY]
>> > > CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
>> > > (
>> > > @.lt_id bigint,
>> > > @.mj_id bigint,
>> > > @.num_labels bigint
>> > > )
>> > > AS
>> > > declare @.t table
>> > > (
>> > > label_id int Primary Key
>> > > )
>> > > set rowcount @.numlabels
>> > > insert into @.t
>> > > select
>> > > label_id
>> > > from
>> > > label
>> > > where
>> > > label_type_id = @.lt_id
>> > > and label_status_id = 1
>> > > set rowcount 0
>> > > declare @.count int
>> > > set @.count = 0
>> > > update
>> > > label
>> > > set
>> > > master_job_id = @.mj_id,
>> > > label_status_id = 2,
>> > > @.count = label_reprint_index = @.count + 1
>> > > where
>> > > label_id in (
>> > > select label_id
>> > > from @.t
>> > > )
>> > > I have to use a FIFO update for the labels, so do I need to build in
>> > > protection to keep multiple users from updating the same records...
>> > > Hide quoted text -
>> > - Show quoted text -- Hide quoted text -
>> - Show quoted text -
>|||1) Add a timestamp to the table. Then on your grab you can get the
timestamp and do a comparison during the update. This will allow others to
grab the row and update it underneath you, but does allow you to NOT update
it twice if that is the desired intent. Most often used for disconnected
processing of one or a few rows at a time.
2) Yes, single statement activity will immeditately take locks on the
updated rows/pages (or even escalate to a table lock). Indexes will be
locked as well.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181570421.325108.57400@.n4g2000hsb.googlegroups.com...
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
>
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> 1) Can't you combine your sproc into a single statement instead of using
>> a
>> table variable?
>> 2) If you do the above, a simple begin tran/update/error
>> check/rollback-commit sequence will ensure each record only gets updated
>> by
>> a single process (which ever fires off first). This could lead to
>> contention if you are doing large ranges of rows.
>> 3) Timestamping is another mechanism used to ensure rows are not changed
>> underneath you between your initial grab and the actual update.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Jason Lepack" <jlep...@.gmail.com> wrote in message
>> news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
>>
>> > I'm using SQL Server 2000.
>> > I have a situation where I need to select a variable amount of records
>> > for a specific type_id and status_id and update some fields. Now
>> > multiple users are going to be using this at the same time, and I want
>> > to prevent multiple users from updating the same records.
>> > Here's what I have so far:
>> > CREATE TABLE [dbo].[label] (
>> > [label_id] [int] NOT NULL ,
>> > [label_status_id] [smallint] NOT NULL ,
>> > [label_type_id] [smallint] NOT NULL ,
>> > [master_job_id] [int] NULL,
>> > [label_reprint_index] [int] NULL
>> > ) ON [PRIMARY]
>> > CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
>> > (
>> > @.lt_id bigint,
>> > @.mj_id bigint,
>> > @.num_labels bigint
>> > )
>> > AS
>> > declare @.t table
>> > (
>> > label_id int Primary Key
>> > )
>> > set rowcount @.numlabels
>> > insert into @.t
>> > select
>> > label_id
>> > from
>> > label
>> > where
>> > label_type_id = @.lt_id
>> > and label_status_id = 1
>> > set rowcount 0
>> > declare @.count int
>> > set @.count = 0
>> > update
>> > label
>> > set
>> > master_job_id = @.mj_id,
>> > label_status_id = 2,
>> > @.count = label_reprint_index = @.count + 1
>> > where
>> > label_id in (
>> > select label_id
>> > from @.t
>> > )
>> > I have to use a FIFO update for the labels, so do I need to build in
>> > protection to keep multiple users from updating the same records... Hide
>> > quoted text -
>> - Show quoted text -
>

Do I need to examine locking for this?

I'm using SQL Server 2000.
I have a situation where I need to select a variable amount of records
for a specific type_id and status_id and update some fields. Now
multiple users are going to be using this at the same time, and I want
to prevent multiple users from updating the same records.
Here's what I have so far:
CREATE TABLE [dbo].[label] (
[label_id] [int] NOT NULL ,
[label_status_id] [smallint] NOT NULL ,
[label_type_id] [smallint] NOT NULL ,
[master_job_id] [int] NULL,
[label_reprint_index] [int] NULL
) ON [PRIMARY]
CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
(
@.lt_id bigint,
@.mj_id bigint,
@.num_labels bigint
)
AS
declare @.t table
(
label_id int Primary Key
)
set rowcount @.numlabels
insert into @.t
select
label_id
from
label
where
label_type_id = @.lt_id
and label_status_id = 1
set rowcount 0
declare @.count int
set @.count = 0
update
label
set
master_job_id = @.mj_id,
label_status_id = 2,
@.count = label_reprint_index = @.count + 1
where
label_id in (
select label_id
from @.t
)
I have to use a FIFO update for the labels, so do I need to build in
protection to keep multiple users from updating the same records?1) Can't you combine your sproc into a single statement instead of using a
table variable?
2) If you do the above, a simple begin tran/update/error
check/rollback-commit sequence will ensure each record only gets updated by
a single process (which ever fires off first). This could lead to
contention if you are doing large ranges of rows.
3) Timestamping is another mechanism used to ensure rows are not changed
underneath you between your initial grab and the actual update.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
> I'm using SQL Server 2000.
> I have a situation where I need to select a variable amount of records
> for a specific type_id and status_id and update some fields. Now
> multiple users are going to be using this at the same time, and I want
> to prevent multiple users from updating the same records.
> Here's what I have so far:
> CREATE TABLE [dbo].[label] (
> [label_id] [int] NOT NULL ,
> [label_status_id] [smallint] NOT NULL ,
> [label_type_id] [smallint] NOT NULL ,
> [master_job_id] [int] NULL,
> [label_reprint_index] [int] NULL
> ) ON [PRIMARY]
> CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
> (
> @.lt_id bigint,
> @.mj_id bigint,
> @.num_labels bigint
> )
> AS
> declare @.t table
> (
> label_id int Primary Key
> )
> set rowcount @.numlabels
> insert into @.t
> select
> label_id
> from
> label
> where
> label_type_id = @.lt_id
> and label_status_id = 1
> set rowcount 0
>
> declare @.count int
> set @.count = 0
> update
> label
> set
> master_job_id = @.mj_id,
> label_status_id = 2,
> @.count = label_reprint_index = @.count + 1
> where
> label_id in (
> select label_id
> from @.t
> )
> I have to use a FIFO update for the labels, so do I need to build in
> protection to keep multiple users from updating the same records?
>|||Could you plese explain how I would use timestamping? I've looked it
up, but I'm not quite sure how to go about it.
Quote:
2) If you do the above, a simple begin tran/update/error
check/rollback-commit sequence will ensure each record only gets
updated by
a single process (which ever fires off first). This could lead to
contention if you are doing large ranges of rows.
So if I just use the update statement then if multiple users are
updating 300000 records at a time, I may run into the situation where
they are trying to update the same pages right?
On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) Can't you combine your sproc into a single statement instead of using a
> table variable?
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets updated b
y
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> 3) Timestamping is another mechanism used to ensure rows are not changed
> underneath you between your initial grab and the actual update.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Jason Lepack" <jlep...@.gmail.com> wrote in message
> news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||I'm slowly renovating this database from using cursors to using set
based logic.
The reason for the table variable is that user parameters
(workstation, username, etc) that are passed to this stored procedure
must be used to update a log table because Windows Domain Security is
not used and if I just used master..sysprocesses.loginname every
transaction would have the username "aspnet"
If I were to begin a transaction and select the records into @.t using
the UPDLOCK hint would that then hold the lock on those records until
the update of the rows was done?
I expect this transaction to take about 1-2 seconds and the amount of
transactions will not be large, so I don't expect too much contention.
Cheers,
Jason Lepack
On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||1) Yes, begin tran, selecting records using updlock/holdlock would prevent
other from getting those records for change until after your commit
2) You may be surprised about performance if you have 300K-rows-per-updates
going on.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181574986.867864.46950@.p47g2000hsd.googlegroups.com...
> I'm slowly renovating this database from using cursors to using set
> based logic.
> The reason for the table variable is that user parameters
> (workstation, username, etc) that are passed to this stored procedure
> must be used to update a log table because Windows Domain Security is
> not used and if I just used master..sysprocesses.loginname every
> transaction would have the username "aspnet"
> If I were to begin a transaction and select the records into @.t using
> the UPDLOCK hint would that then hold the lock on those records until
> the update of the rows was done?
> I expect this transaction to take about 1-2 seconds and the amount of
> transactions will not be large, so I don't expect too much contention.
> Cheers,
> Jason Lepack
> On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
>|||1) Add a timestamp to the table. Then on your grab you can get the
timestamp and do a comparison during the update. This will allow others to
grab the row and update it underneath you, but does allow you to NOT update
it twice if that is the desired intent. Most often used for disconnected
processing of one or a few rows at a time.
2) Yes, single statement activity will immeditately take locks on the
updated rows/pages (or even escalate to a table lock). Indexes will be
locked as well.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181570421.325108.57400@.n4g2000hsb.googlegroups.com...
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
>
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>

Do I need to examine locking for this?

I'm using SQL Server 2000.
I have a situation where I need to select a variable amount of records
for a specific type_id and status_id and update some fields. Now
multiple users are going to be using this at the same time, and I want
to prevent multiple users from updating the same records.
Here's what I have so far:
CREATE TABLE [dbo].[label] (
[label_id] [int] NOT NULL ,
[label_status_id] [smallint] NOT NULL ,
[label_type_id] [smallint] NOT NULL ,
[master_job_id] [int] NULL,
[label_reprint_index] [int] NULL
) ON [PRIMARY]
CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
(
@.lt_id bigint,
@.mj_id bigint,
@.num_labels bigint
)
AS
declare @.t table
(
label_id int Primary Key
)
set rowcount @.numlabels
insert into @.t
select
label_id
from
label
where
label_type_id = @.lt_id
and label_status_id = 1
set rowcount 0
declare @.count int
set @.count = 0
update
label
set
master_job_id = @.mj_id,
label_status_id = 2,
@.count = label_reprint_index = @.count + 1
where
label_id in (
select label_id
from @.t
)
I have to use a FIFO update for the labels, so do I need to build in
protection to keep multiple users from updating the same records?
1) Can't you combine your sproc into a single statement instead of using a
table variable?
2) If you do the above, a simple begin tran/update/error
check/rollback-commit sequence will ensure each record only gets updated by
a single process (which ever fires off first). This could lead to
contention if you are doing large ranges of rows.
3) Timestamping is another mechanism used to ensure rows are not changed
underneath you between your initial grab and the actual update.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181566796.016917.115820@.w5g2000hsg.googlegro ups.com...
> I'm using SQL Server 2000.
> I have a situation where I need to select a variable amount of records
> for a specific type_id and status_id and update some fields. Now
> multiple users are going to be using this at the same time, and I want
> to prevent multiple users from updating the same records.
> Here's what I have so far:
> CREATE TABLE [dbo].[label] (
> [label_id] [int] NOT NULL ,
> [label_status_id] [smallint] NOT NULL ,
> [label_type_id] [smallint] NOT NULL ,
> [master_job_id] [int] NULL,
> [label_reprint_index] [int] NULL
> ) ON [PRIMARY]
> CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
> (
> @.lt_id bigint,
> @.mj_id bigint,
> @.num_labels bigint
> )
> AS
> declare @.t table
> (
> label_id int Primary Key
> )
> set rowcount @.numlabels
> insert into @.t
> select
> label_id
> from
> label
> where
> label_type_id = @.lt_id
> and label_status_id = 1
> set rowcount 0
>
> declare @.count int
> set @.count = 0
> update
> label
> set
> master_job_id = @.mj_id,
> label_status_id = 2,
> @.count = label_reprint_index = @.count + 1
> where
> label_id in (
> select label_id
> from @.t
> )
> I have to use a FIFO update for the labels, so do I need to build in
> protection to keep multiple users from updating the same records?
>
|||I'm slowly renovating this database from using cursors to using set
based logic.
The reason for the table variable is that user parameters
(workstation, username, etc) that are passed to this stored procedure
must be used to update a log table because Windows Domain Security is
not used and if I just used master..sysprocesses.loginname every
transaction would have the username "aspnet"
If I were to begin a transaction and select the records into @.t using
the UPDLOCK hint would that then hold the lock on those records until
the update of the rows was done?
I expect this transaction to take about 1-2 seconds and the amount of
transactions will not be large, so I don't expect too much contention.
Cheers,
Jason Lepack
On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||1) Yes, begin tran, selecting records using updlock/holdlock would prevent
other from getting those records for change until after your commit
2) You may be surprised about performance if you have 300K-rows-per-updates
going on.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181574986.867864.46950@.p47g2000hsd.googlegro ups.com...
> I'm slowly renovating this database from using cursors to using set
> based logic.
> The reason for the table variable is that user parameters
> (workstation, username, etc) that are passed to this stored procedure
> must be used to update a log table because Windows Domain Security is
> not used and if I just used master..sysprocesses.loginname every
> transaction would have the username "aspnet"
> If I were to begin a transaction and select the records into @.t using
> the UPDLOCK hint would that then hold the lock on those records until
> the update of the rows was done?
> I expect this transaction to take about 1-2 seconds and the amount of
> transactions will not be large, so I don't expect too much contention.
> Cheers,
> Jason Lepack
> On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
>
|||1) Add a timestamp to the table. Then on your grab you can get the
timestamp and do a comparison during the update. This will allow others to
grab the row and update it underneath you, but does allow you to NOT update
it twice if that is the desired intent. Most often used for disconnected
processing of one or a few rows at a time.
2) Yes, single statement activity will immeditately take locks on the
updated rows/pages (or even escalate to a table lock). Indexes will be
locked as well.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181570421.325108.57400@.n4g2000hsb.googlegrou ps.com...
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
>
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>

do i need to deny everything i don't use ?

hi again
i have an account with its password
with specific permissions,
i have to deny, then, the access
to the rest of the objects ?
i.e. systems stored proc, tables , etc ?
thanks
atte,
Hernn Castelo
SGA - UTN - FRBA
Depends what you want.
The user will only have access to those objects it is granted so there is no
need to deny.
But if the user is then added to a role which has other permissions (or they
are given to public) it will gain them.
If this is not what you want then you should deny permissions as well.
I usually only deny dbwriter to read only accounts and leave the rest to
gain from granted permissions
"Hernán Castelo" wrote:

> hi again
> i have an account with its password
> with specific permissions,
> i have to deny, then, the access
> to the rest of the objects ?
> i.e. systems stored proc, tables , etc ?
> thanks
>
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>
>
|||i'm asking because
i entered with a restricted account
and was able to exec SP_HELPTEXT
and i don't wish that
denying dbwriter sounds good,
how can i disable these type of sp's ?
atte,
Hernn Castelo
SGA - UTN - FRBA
"Nigel Rivett" <sqlnr@.hotmail.com> escribi en el mensaje
news:FA87EBFA-AE01-4BDD-8869-E62687DB27FB@.microsoft.com...
> Depends what you want.
> The user will only have access to those objects it is granted so there is
no
> need to deny.
> But if the user is then added to a role which has other permissions (or
they[vbcol=seagreen]
> are given to public) it will gain them.
> If this is not what you want then you should deny permissions as well.
> I usually only deny dbwriter to read only accounts and leave the rest to
> gain from granted permissions
> "Hernn Castelo" wrote:
|||Everyone can see the source code, I'm afraid. Closest you can come is creating the procedures using
the WITH ENCRYPTION option (note however that there exists tools to decrypt...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OO0nNnM0EHA.3416@.TK2MSFTNGP09.phx.gbl...
> i'm asking because
> i entered with a restricted account
> and was able to exec SP_HELPTEXT
> and i don't wish that
> denying dbwriter sounds good,
> how can i disable these type of sp's ?
>
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
> "Nigel Rivett" <sqlnr@.hotmail.com> escribi en el mensaje
> news:FA87EBFA-AE01-4BDD-8869-E62687DB27FB@.microsoft.com...
> no
> they
>

do i need to deny everything i don't use ?

hi again
i have an account with its password
with specific permissions,
i have to deny, then, the access
to the rest of the objects ?
i.e. systems stored proc, tables , etc ?
thanks
atte,
Hernn Castelo
SGA - UTN - FRBADepends what you want.
The user will only have access to those objects it is granted so there is no
need to deny.
But if the user is then added to a role which has other permissions (or they
are given to public) it will gain them.
If this is not what you want then you should deny permissions as well.
I usually only deny dbwriter to read only accounts and leave the rest to
gain from granted permissions
"Hernán Castelo" wrote:

> hi again
> i have an account with its password
> with specific permissions,
> i have to deny, then, the access
> to the rest of the objects ?
> i.e. systems stored proc, tables , etc ?
> thanks
>
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>
>|||i'm asking because
i entered with a restricted account
and was able to exec SP_HELPTEXT
and i don't wish that
denying dbwriter sounds good,
how can i disable these type of sp's ?
atte,
Hernn Castelo
SGA - UTN - FRBA
"Nigel Rivett" <sqlnr@.hotmail.com> escribi en el mensaje
news:FA87EBFA-AE01-4BDD-8869-E62687DB27FB@.microsoft.com...
> Depends what you want.
> The user will only have access to those objects it is granted so there is
no
> need to deny.
> But if the user is then added to a role which has other permissions (or
they[vbcol=seagreen]
> are given to public) it will gain them.
> If this is not what you want then you should deny permissions as well.
> I usually only deny dbwriter to read only accounts and leave the rest to
> gain from granted permissions
> "Hernn Castelo" wrote:
>|||Everyone can see the source code, I'm afraid. Closest you can come is creati
ng the procedures using
the WITH ENCRYPTION option (note however that there exists tools to decrypt.
.).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OO0nNnM0EHA.3416@.TK2MSFTNGP09.phx.gbl...
> i'm asking because
> i entered with a restricted account
> and was able to exec SP_HELPTEXT
> and i don't wish that
> denying dbwriter sounds good,
> how can i disable these type of sp's ?
>
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
> "Nigel Rivett" <sqlnr@.hotmail.com> escribi en el mensaje
> news:FA87EBFA-AE01-4BDD-8869-E62687DB27FB@.microsoft.com...
> no
> they
>

do i need to deny everything i don't use ?

hi again
i have an account with its password
with specific permissions,
i have to deny, then, the access
to the rest of the objects ?
i.e. systems stored proc, tables , etc ?
thanks
--
atte,
Hernán Castelo
SGA - UTN - FRBADepends what you want.
The user will only have access to those objects it is granted so there is no
need to deny.
But if the user is then added to a role which has other permissions (or they
are given to public) it will gain them.
If this is not what you want then you should deny permissions as well.
I usually only deny dbwriter to read only accounts and leave the rest to
gain from granted permissions
"Hernán Castelo" wrote:
> hi again
> i have an account with its password
> with specific permissions,
> i have to deny, then, the access
> to the rest of the objects ?
> i.e. systems stored proc, tables , etc ?
> thanks
>
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>
>|||i'm asking because
i entered with a restricted account
and was able to exec SP_HELPTEXT
and i don't wish that
denying dbwriter sounds good,
how can i disable these type of sp's ?
atte,
Hernán Castelo
SGA - UTN - FRBA
"Nigel Rivett" <sqlnr@.hotmail.com> escribió en el mensaje
news:FA87EBFA-AE01-4BDD-8869-E62687DB27FB@.microsoft.com...
> Depends what you want.
> The user will only have access to those objects it is granted so there is
no
> need to deny.
> But if the user is then added to a role which has other permissions (or
they
> are given to public) it will gain them.
> If this is not what you want then you should deny permissions as well.
> I usually only deny dbwriter to read only accounts and leave the rest to
> gain from granted permissions
> "Hernán Castelo" wrote:
> > hi again
> > i have an account with its password
> > with specific permissions,
> > i have to deny, then, the access
> > to the rest of the objects ?
> > i.e. systems stored proc, tables , etc ?
> >
> > thanks
> >
> >
> > --
> > atte,
> > Hernán Castelo
> > SGA - UTN - FRBA
> >
> >
> >|||Everyone can see the source code, I'm afraid. Closest you can come is creating the procedures using
the WITH ENCRYPTION option (note however that there exists tools to decrypt...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hernán Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OO0nNnM0EHA.3416@.TK2MSFTNGP09.phx.gbl...
> i'm asking because
> i entered with a restricted account
> and was able to exec SP_HELPTEXT
> and i don't wish that
> denying dbwriter sounds good,
> how can i disable these type of sp's ?
>
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
> "Nigel Rivett" <sqlnr@.hotmail.com> escribió en el mensaje
> news:FA87EBFA-AE01-4BDD-8869-E62687DB27FB@.microsoft.com...
> > Depends what you want.
> > The user will only have access to those objects it is granted so there is
> no
> > need to deny.
> > But if the user is then added to a role which has other permissions (or
> they
> > are given to public) it will gain them.
> > If this is not what you want then you should deny permissions as well.
> >
> > I usually only deny dbwriter to read only accounts and leave the rest to
> > gain from granted permissions
> >
> > "Hernán Castelo" wrote:
> >
> > > hi again
> > > i have an account with its password
> > > with specific permissions,
> > > i have to deny, then, the access
> > > to the rest of the objects ?
> > > i.e. systems stored proc, tables , etc ?
> > >
> > > thanks
> > >
> > >
> > > --
> > > atte,
> > > Hernán Castelo
> > > SGA - UTN - FRBA
> > >
> > >
> > >
>

do 2005 backups restore into 2000?

Can sql server 2005 database backups restore to sql server 2000 (assuming no
sql server 2005 specific elements in are the database, such as clr stuff or
new transaction sql features)?
Thank you
No. You would have to export the data from 2005 and import it into 2000
table by table.
Andrew J. Kelly SQL MVP
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:36D3BFC2-32F8-48C4-94FF-BE5E15CF4399@.microsoft.com...
> Can sql server 2005 database backups restore to sql server 2000 (assuming
> no
> sql server 2005 specific elements in are the database, such as clr stuff
> or
> new transaction sql features)?
> Thank you

do 2005 backups restore into 2000?

Can sql server 2005 database backups restore to sql server 2000 (assuming no
sql server 2005 specific elements in are the database, such as clr stuff or
new transaction sql features)?
Thank you"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:36D3BFC2-32F8-48C4-94FF-BE5E15CF4399@.microsoft.com...
> Can sql server 2005 database backups restore to sql server 2000 (assuming
> no
> sql server 2005 specific elements in are the database, such as clr stuff
> or
> new transaction sql features)?
> Thank you
No. Instead you could copy database objects between the serves using SSIS
for example, or scripting and BCP.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||No. You would have to export the data from 2005 and import it into 2000
table by table.
Andrew J. Kelly SQL MVP
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:36D3BFC2-32F8-48C4-94FF-BE5E15CF4399@.microsoft.com...
> Can sql server 2005 database backups restore to sql server 2000 (assuming
> no
> sql server 2005 specific elements in are the database, such as clr stuff
> or
> new transaction sql features)?
> Thank yousql

do 2005 backups restore into 2000?

Can sql server 2005 database backups restore to sql server 2000 (assuming no
sql server 2005 specific elements in are the database, such as clr stuff or
new transaction sql features)?
Thank you"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:36D3BFC2-32F8-48C4-94FF-BE5E15CF4399@.microsoft.com...
> Can sql server 2005 database backups restore to sql server 2000 (assuming
> no
> sql server 2005 specific elements in are the database, such as clr stuff
> or
> new transaction sql features)?
> Thank you
No. Instead you could copy database objects between the serves using SSIS
for example, or scripting and BCP.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||No. You would have to export the data from 2005 and import it into 2000
table by table.
--
Andrew J. Kelly SQL MVP
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:36D3BFC2-32F8-48C4-94FF-BE5E15CF4399@.microsoft.com...
> Can sql server 2005 database backups restore to sql server 2000 (assuming
> no
> sql server 2005 specific elements in are the database, such as clr stuff
> or
> new transaction sql features)?
> Thank you

Dividing the group in sections

I have a group with 3 distinct field values. I'm only interested in
seeing the specific details for one of them and lump the rest into the
"Others" category and viewing the same details but for that category as
a whole.
This is done in Crystal Reports in the Change Group Options dialog. Is
there something comparable to that in SSRS?
Thank you in advance for any assistance.I believe you can do such grouping with Reporting Services, but it may
be better to modify your data source so that you have an additional
field returned, one where the value is either the field value that you
are interested in or the word 'Other' for all other values.
In SQL Server you can do this by including a CASE statement for that
field within the overall SELECT statement.
This may run faster on the server side, as well, and it simplifies
report design.
I try to do as much as possible on the server side, so that report
design and layout are as simple as possible.
tantilis wrote:
> I have a group with 3 distinct field values. I'm only interested in
> seeing the specific details for one of them and lump the rest into the
> "Others" category and viewing the same details but for that category as
> a whole.
> This is done in Crystal Reports in the Change Group Options dialog. Is
> there something comparable to that in SSRS?
> Thank you in advance for any assistance.|||Thank you for your reply and your solution is practical however I don't
have that kind of access to the server. It's a constraint of this
project that I must replicate the Crystal report without manipulating
any stored procedures.
Parker wrote:
> I believe you can do such grouping with Reporting Services, but it may
> be better to modify your data source so that you have an additional
> field returned, one where the value is either the field value that you
> are interested in or the word 'Other' for all other values.
> In SQL Server you can do this by including a CASE statement for that
> field within the overall SELECT statement.
> This may run faster on the server side, as well, and it simplifies
> report design.
> I try to do as much as possible on the server side, so that report
> design and layout are as simple as possible.
> tantilis wrote:
> > I have a group with 3 distinct field values. I'm only interested in
> > seeing the specific details for one of them and lump the rest into the
> > "Others" category and viewing the same details but for that category as
> > a whole.
> >
> > This is done in Crystal Reports in the Change Group Options dialog. Is
> > there something comparable to that in SSRS?
> >
> > Thank you in advance for any assistance.|||i remember a report where i had to break up a group each time the last
two digits of a certain field hit either 49 or 99. the way i wound up
doing it was to add a second expression, in addition to the primary
condition, to the group expression. you might play around with this
possibility?|||I think I may have worked it out doing just that. I've changed my
primary grouping expression to look like this:
=iif(Fields!Some_Value.Value ="SomeString",Fields!Some_Value.Value,nothing)
It seems a bit counter-intuitive but in a dozen tests so far I'm seeing
values I'm expecting to see in the "Others" category.
What the report would do under this condition is first, present the
group of everything for which SomeString was true using SomeString in
the group header. The report would then present the rest of the data
in a single group while displaying only the first value of Some_Value
in alphabetic order. Just used an expression at that point to replace
every Some_Value.Value to "Others" where it wasn't SomeString.
kjward wrote:
> i remember a report where i had to break up a group each time the last
> two digits of a certain field hit either 49 or 99. the way i wound up
> doing it was to add a second expression, in addition to the primary
> condition, to the group expression. you might play around with this
> possibility?

Distributing Reports automatically

Can reports be set up to run and print automatically to specific printers on a nightly basis. What I'm trying to accomplish is, I have 6 users who need to see specific transaction reports for thier department each day. Of course I don't want them to see the other departments reports.. What options do I have....

Thank You!!

Jim - try this link from the forum. I plan to try it this week and it may accomplish what you need.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=568015&SiteID=1