RowNumber Problem in Grouped Data

I have multiple (multilevel) groupings in my RDL (based on line level data
from SQL server). While using the RowNumber("GrouName") I am getting strange
results. For example If that grouping was made out of 3 individual lines from
data set, then I am getting Row number as 3.
What I want is irrespective of the no of lines involved in such aggregation,
I want the rownumbers based on the final aggregated line and not based on the
no of detailed lines involved in such groupings.
Any help will be appreciated!
Thanks,
VisualcppI get this also. With my groupings, RowNumber behaves identically to
CountRows. I have something that looks like:
Group1
Group 2
Details
Group2 and details are initially hidden so I want to alternate row
colors for the group1 header and footer rows. RowNumber("Group1")
returns the number of rows in that group, not the number of the
group. It's identical to CountRows("Group1").
If you figure out the solution to this, please let me know!
On Feb 15, 1:51 am, Visualcpp <Visual...@.discussions.microsoft.com>
wrote:
> I have multiple (multilevel) groupings in my RDL (based on line level data
> from SQL server). While using theRowNumber("GrouName") I am getting strange
> results. For example If that grouping was made out of 3 individual lines from
> data set, then I am getting Row number as 3.
> What I want is irrespective of the no of lines involved in such aggregation,
> I want the rownumbers based on the final aggregated line and not based on the
> no of detailed lines involved in such groupings.
> Any help will be appreciated!
> Thanks,
> Visualcpp|||I don't know if this is the proper way to get around this, but the
following works correctly. If you happened to have blank or null
group headings this method would not work.
=IIF(RunningValue(Fields!name.Value,CountDistinct,nothing) mod 2,
"Transparent", "LightYellow")
On Mar 11, 10:35 am, brandon.rich...@.gmail.com wrote:
> I get this also. With my groupings,RowNumberbehaves identically to
> CountRows. I have something that looks like:
> Group1
> Group 2
> Details
> Group2 and details are initially hidden so I want to alternate row
> colors for the group1 header and footer rows. RowNumber("Group1")
> returns the number of rows in that group, not the number of the
> group. It's identical to CountRows("Group1").
> If you figure out the solution to this, please let me know!
> On Feb 15, 1:51 am, Visualcpp <Visual...@.discussions.microsoft.com>
> wrote:
>
> > I have multiple (multilevel) groupings in my RDL (based on line level data
> > from SQL server). While using theRowNumber("GrouName") I am getting strange
> > results. For example If that grouping was made out of 3 individual lines from
> > data set, then I am getting Row number as 3.
> > What I want is irrespective of the no of lines involved in such aggregation,
> > I want the rownumbers based on the final aggregated line and not based on the
> > no of detailed lines involved in such groupings.
> > Any help will be appreciated!
> > Thanks,
> > Visualcpp- Hide quoted text -
> - Show quoted text -

row-by-row operation without cursor

Hi all,
I have a table with customer data. Ik would like to query this table for duplicate entries, based on certain criteria.
My query to find these records:

SELECT postcode, huisnr, huisnrtv
FROM tblRelatie
GROUP BY postcode, huisnr, huisnrtv
HAVING COUNT(*) > 1

So far so good. What I would like is to show some other data from the duplicate records. Example: suppose the above query
finds 2 records having the same criteria (postcode, huisnr, huisnrtv). How can I select other data from the
same table (e.g. name, date of birth) without using a cursor (is too expensive)? I thought of using a derived query but
I can't figure out how to do it. My desired result looks something like this:

postcode huisnr huisnrtv name date of birth
----- --- ---- ----
1111AA 13 a Smith 12/3/70
1111AA 13 a Clinton 10/2/72
2222BB 22 Bloomberg 1/8/61
2222BB 22 Bloomberg 9/2/79
2222BB 22 Pataki 2/4/71
etc.

Thanks in advance.

Diederikselect *
from tblRelatie r
join
(
SELECT postcode, huisnr, huisnrtv
FROM tblRelatie
GROUP BY postcode, huisnr, huisnrtv
HAVING COUNT(*) > 1
) x on r.postcode=x.postcode and r.huisnr=x.huisnr and r.huisnrtv=x.huisnrtv|||Vezi daca merge asta:

select tblRelatie.* from tblRelatie join
(SELECT postcode, huisnr, huisnrtv
FROM tblRelatie
GROUP BY postcode, huisnr, huisnrtv
HAVING COUNT(*) > 1) T1
on tblRelatie.postcode=T1.postcode and tblRelatie.huisnr=T1.huisnr and tblRelatie.huisnrtv=T1.huisnrtv
order by tblRelatie.postcode, tblRelatie.huisnr, tblRelatie.huisnrtv

ionut calin

Row Size Limitation of Report Model?

Hello,

I am trying to create a Report Model based on Data View that references a SQL View.

I get a an error when building the Report Model based on this view:

An error occurred while executing a command.
Message: Arithmetic overflow error converting expression to data type int.
Command: SELECT COUNT(*) FROM [dbo].[myViewName]
I don't know exactly how many rows are returned from this view, but I do know that it can contain quite a few rows. I tried to find out how many rows were in the view by running a SELECT COUNT() on the view, but I got the following error from SQL Server 2005 query window:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

This appears to be a SQL 2005 issue, but affects me building a Report Model. My question is, is this a SQL Server 2005 issue? Is there a way around it (other than chaning my view to decrease the amount of rows in it)?

BTW...I am running SQL 2005 Developer Edition on a Windows XP (SP2) laptop.

Thanks!!

Brian

Nevermind. It looks like there was an error in the view that I was using.

This does bring up a good question though...Are there limitations to how much data (number of rows) can be used with a Report Model?

Thanks!

Brian|||No, there is technically no limit, although certain expressions like Count(<entity>) could overflow if you have more than 2^31 (~2 billion) rows.|||what can i do if my count(*) exceeds the limit. I have this situation now already.sql

Row Size Limitation of Report Model?

Hello,

I am trying to create a Report Model based on Data View that references a SQL View.

I get a an error when building the Report Model based on this view:

An error occurred while executing a command.
Message: Arithmetic overflow error converting expression to data type int.
Command: SELECT COUNT(*) FROM [dbo].[myViewName]
I don't know exactly how many rows are returned from this view, but I do know that it can contain quite a few rows. I tried to find out how many rows were in the view by running a SELECT COUNT() on the view, but I got the following error from SQL Server 2005 query window:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

This appears to be a SQL 2005 issue, but affects me building a Report Model. My question is, is this a SQL Server 2005 issue? Is there a way around it (other than chaning my view to decrease the amount of rows in it)?

BTW...I am running SQL 2005 Developer Edition on a Windows XP (SP2) laptop.

Thanks!!

Brian

Nevermind. It looks like there was an error in the view that I was using.

This does bring up a good question though...Are there limitations to how much data (number of rows) can be used with a Report Model?

Thanks!

Brian|||No, there is technically no limit, although certain expressions like Count(<entity>) could overflow if you have more than 2^31 (~2 billion) rows.|||what can i do if my count(*) exceeds the limit. I have this situation now already.

Row Count

I am writing a Insert stored procedure that has logic which is going to
do different things based on the results of a query.
The query is going to do a count. If the count is not equal to 0, then
the procedure returns setting a @.msg paramenter and -1 in the return
code.
I can get this working just fine, but when the count > 0, a rowset is
returned and I am not wanting the rowset returned. Is there someway to
structure the procedure such that a rowset is not returned Here is
what I have...
alter proc Proc_InsertCompetativeEvents
@.Name char(50),
@.City char(50),
@.State char(50),
@.StartDt smalldatetime,
@.EndDt smalldatetime,
@.msg nvarchar(220) out
as
declare @.cnt int
select count(CompetitionName)
from dbo.CompetativeEvents
where CompetitionName = @.name
group by CompetitionName
if @.@.ROWCOUNT > 0
begin
set @.msg = 'Duplicate Event Names are not allowed'
return -1
end
insert into dbo.CompetativeEvents
Values (newid(), @.Name,@.City,@.State,@.StartDt, @.EndDt)
set @.cnt = @.@.ROWCOUNT
if @.@.ERROR > 0
begin
set @.msg = 'Unexpected error occurred inserting a record into
CompetativeEvents using the Proc_InsertCompetativeEvents procedure -
Error code: ' + cast(@.@.ERROR as char(4))
return -1
end
if @.cnt = 1
begin
set @.msg=''
return 0
end
set @.msg = 'Beat me what happen'
return -1
Thanks in advance for your assistance!!!!!!!!Use IF EXISTS:
IF EXISTS
(SELECT *
FROM CompetitiveEvents
WHERE competitionname = @.name)
SET @.msg = 'Duplicate Event Names are not allowed'
...
--
David Portas
--
Please reply only to the newsgroup
--|||Jim,
I'm not sure what you want, but could you explain what you are
intending to do with
select count(CompetitionName) ...
if @.@.ROWCOUNT > 0
If I'm not mistaken, @.@.ROWCOUNT will always be greater than zero after a
select count() query.
Your narrative says that you want to compare the count with zero, but
your code does not compare the count with zero. It compares the number
of count(CompetitionName) values returned (the rowcount of the query)
with zero, and that value looks like it will always be 1, since you
restrict to CompetitionName = @.name before grouping.
SK
Jim Heavey wrote:
>I am writing a Insert stored procedure that has logic which is going to
>do different things based on the results of a query.
>The query is going to do a count. If the count is not equal to 0, then
>the procedure returns setting a @.msg paramenter and -1 in the return
>code.
>I can get this working just fine, but when the count > 0, a rowset is
>returned and I am not wanting the rowset returned. Is there someway to
>structure the procedure such that a rowset is not returned Here is
>what I have...
>alter proc Proc_InsertCompetativeEvents
> @.Name char(50),
> @.City char(50),
> @.State char(50),
> @.StartDt smalldatetime,
> @.EndDt smalldatetime,
> @.msg nvarchar(220) out
>as
>declare @.cnt int
>select count(CompetitionName)
> from dbo.CompetativeEvents
> where CompetitionName = @.name
> group by CompetitionName
>if @.@.ROWCOUNT > 0
> begin
> set @.msg = 'Duplicate Event Names are not allowed'
> return -1
> end
>insert into dbo.CompetativeEvents
> Values (newid(), @.Name,@.City,@.State,@.StartDt, @.EndDt)
>set @.cnt = @.@.ROWCOUNT
>if @.@.ERROR > 0
> begin
> set @.msg = 'Unexpected error occurred inserting a record into
>CompetativeEvents using the Proc_InsertCompetativeEvents procedure -
>Error code: ' + cast(@.@.ERROR as char(4))
> return -1
> end
>if @.cnt = 1
> begin
> set @.msg=''
> return 0
> end
>set @.msg = 'Beat me what happen'
>return -1
>
>Thanks in advance for your assistance!!!!!!!!
>|||Jim Heavey (JimHeavey@.nospam.com) writes:
> I can get this working just fine, but when the count > 0, a rowset is
> returned and I am not wanting the rowset returned. Is there someway to
> structure the procedure such that a rowset is not returned Here is
> what I have...
As Steve pointed out, this does not make sense:
> declare @.cnt int
> select count(CompetitionName)
> from dbo.CompetativeEvents
> where CompetitionName = @.name
> group by CompetitionName
> if @.@.ROWCOUNT > 0
What you most likely want is:
SELECT @.cnt = COUNT(*)
FROM CompetativeEvents
WHERE CompetitionName = @.name
IF @.cnt > 0
(Note that I also left out GROUP BY.)
Of course, even better is David's suggestion to use IF EXISTS. Not much
of an issue here, but if there could be 1000 rows, and you are only
interested whether in 0 or not zero, there is no reason to count them
all.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||> If I'm not mistaken, @.@.ROWCOUNT will always be greater than zero after a
> select count() query.
Because the query includes GROUP BY, @.@.ROWCOUNT will return 0 if the @.name
doesn't exist but it will never be more than 1 because of the combination of
GROUP BY and the WHERE clause.
Since the point seems to be to test for the presence or absence of a value I
suggested using EXISTS. Of course, another option is just to do the insert
and trap the error at the client rather than pass a message from the SP.
--
David Portas
--
Please reply only to the newsgroup
--|||David Portas wrote:
>>If I'm not mistaken, @.@.ROWCOUNT will always be greater than zero after a
>>select count() query.
>>
>Because the query includes GROUP BY, @.@.ROWCOUNT will return 0 if the @.name
>doesn't exist but it will never be more than 1 because of the combination of
>GROUP BY and the WHERE clause.
>
Oops - good catch. This is one place where GROUP BY can reduce the
number of rows returned...
SK
>Since the point seems to be to test for the presence or absence of a value I
>suggested using EXISTS. Of course, another option is just to do the insert
>and trap the error at the client rather than pass a message from the SP.
>
>|||Hi
As an aside, the following code will be problematic if an error occurs:
insert into dbo.CompetativeEvents
Values (newid(), @.Name,@.City,@.State,@.StartDt, @.EndDt)
set @.cnt = @.@.ROWCOUNT
if @.@.ERROR > 0
The set resets the @.@.ERROR variable back to 0 so if an error occurs during
the insert, you don't get it.
To resolve this, store the error in a variable and get both values back at
the same time:
SELECT @.cnt = @.@.ROWCOUNT, @.LocalError = @.@.Error
if @.LocalError > 0
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns9465C570A8BFEJimHeaveyhotmailcom@.207.46.248.16...
> I am writing a Insert stored procedure that has logic which is going to
> do different things based on the results of a query.
> The query is going to do a count. If the count is not equal to 0, then
> the procedure returns setting a @.msg paramenter and -1 in the return
> code.
> I can get this working just fine, but when the count > 0, a rowset is
> returned and I am not wanting the rowset returned. Is there someway to
> structure the procedure such that a rowset is not returned Here is
> what I have...
> alter proc Proc_InsertCompetativeEvents
> @.Name char(50),
> @.City char(50),
> @.State char(50),
> @.StartDt smalldatetime,
> @.EndDt smalldatetime,
> @.msg nvarchar(220) out
> as
> declare @.cnt int
> select count(CompetitionName)
> from dbo.CompetativeEvents
> where CompetitionName = @.name
> group by CompetitionName
> if @.@.ROWCOUNT > 0
> begin
> set @.msg = 'Duplicate Event Names are not allowed'
> return -1
> end
> insert into dbo.CompetativeEvents
> Values (newid(), @.Name,@.City,@.State,@.StartDt, @.EndDt)
> set @.cnt = @.@.ROWCOUNT
> if @.@.ERROR > 0
> begin
> set @.msg = 'Unexpected error occurred inserting a record into
> CompetativeEvents using the Proc_InsertCompetativeEvents procedure -
> Error code: ' + cast(@.@.ERROR as char(4))
> return -1
> end
> if @.cnt = 1
> begin
> set @.msg=''
> return 0
> end
> set @.msg = 'Beat me what happen'
> return -1
>
> Thanks in advance for your assistance!!!!!!!!

Row based security

Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:

> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>

Row based security

I have a database system that contains 1 category of data.
There are plans in the future to add other categories of data as well.
Some users of the database can have access to 1 or more of the categories of
data, and other users can only have access to 1 category, and non of the
other categories of data.
I have a solution in place for row level security using stored procedures
and lookup tables to define what data can be viewed by what users.
In the Oracle world, row based security is handled, but in SQL Server there
is no mechanism in place within the security model to accommodate for it.
What are some approaches in SQL Server that others have used to accommodate
for Row level security issues?
Simon WorthCheck this link for an approach on how do implement row level security in SQ
L
Server:
http://vyaskn.tripod.com/ row_level...as
es.htm
-Sue
"Simon Worth" wrote:

> I have a database system that contains 1 category of data.
> There are plans in the future to add other categories of data as well.
> Some users of the database can have access to 1 or more of the categories
of
> data, and other users can only have access to 1 category, and non of the
> other categories of data.
> I have a solution in place for row level security using stored procedures
> and lookup tables to define what data can be viewed by what users.
> In the Oracle world, row based security is handled, but in SQL Server ther
e
> is no mechanism in place within the security model to accommodate for it.
> What are some approaches in SQL Server that others have used to accommodat
e
> for Row level security issues?
> --
> Simon Worth
>
>|||Thanks Sue,
I've read that article before, and that's actually were I developed some
ideas from.
I was just wondering what others have done in the past, or are planning on
implementing in the future.
Just curiosity I guess. Plus, once in awhile you get an answer that really
makes sense that you hadn't thought of before.
Simon Worth
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0D4434BE-321E-4A38-87BF-2D08A238D35F@.microsoft.com...
> Check this link for an approach on how do implement row level security in
SQL[vbcol=seagreen]
> Server:
> http://vyaskn.tripod.com/ row_level...as
es.htm
> -Sue
> "Simon Worth" wrote:
>
categories of[vbcol=seagreen]
procedures[vbcol=seagreen]
there[vbcol=seagreen]
it.[vbcol=seagreen]
accommodate[vbcol=seagreen]

Row based security

Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:

> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>

Row appearance based on field data

I have a simple report that outputs rows of data to a table. The data is real
estate data and shows a bunch of homes in a community. One of the fields is
status. I'd like to have each row in the report with a value of 'Closed' in
the status field to appear with a bold font, while the rest of the rows
appear with a normal font.
Is this possible?
--
ArkayClick on the textbox.
Find teh Font->Weight property in the property sheet.
Set its weight to an expression.
In the expression dialog, enter (replace "myColumn" with your field name)
=IIF(Fields!myColumn.Value = "Closed", "Bold", "Normal")
"Arkay" <Arkay@.discussions.microsoft.com> wrote in message
news:D257735D-CE46-4A17-AD47-94D7CFE5F4C9@.microsoft.com...
>I have a simple report that outputs rows of data to a table. The data is
>real
> estate data and shows a bunch of homes in a community. One of the fields
> is
> status. I'd like to have each row in the report with a value of 'Closed'
> in
> the status field to appear with a bold font, while the rest of the rows
> appear with a normal font.
> Is this possible?
> --
> Arkay

Row and set based ?

Hey
I've been doing for SQL for a while but recently I've been trying to get
some whitepaper for it, I'm now working through some questions I obviously
first I google the questions before I came here to ask.
I ran into the questions.
Give an example of a rowbased statement, and a set based statement. I'm sure
know how to code it but I don't know what it is.
Another question I ran into was "what setting do you ALWAYS disable" in a
live environment. Now I don't know this either... it's not a registered
Microsoft exam so I don't know if the questions are dodgy.
Answers would be appreciated though.
THanksHi Mal
Row based statement:
SELECT * FROM <TABLE> WHERE NEWID() = '<some ID>'
SET based statement
SELECT * FROM <TABLE>
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Mal" wrote:

> Hey
> I've been doing for SQL for a while but recently I've been trying to get
> some whitepaper for it, I'm now working through some questions I obviously
> first I google the questions before I came here to ask.
> I ran into the questions.
> Give an example of a rowbased statement, and a set based statement. I'm su
re
> know how to code it but I don't know what it is.
> Another question I ran into was "what setting do you ALWAYS disable" in a
> live environment. Now I don't know this either... it's not a registered
> Microsoft exam so I don't know if the questions are dodgy.
> Answers would be appreciated though.
> THanks|||When multiple rows are effected by a query, then that query is a set based
query.
if only one row gets effected by a query, then the query is rowbased.
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Mal" wrote:

> Hey
> I've been doing for SQL for a while but recently I've been trying to get
> some whitepaper for it, I'm now working through some questions I obviously
> first I google the questions before I came here to ask.
> I ran into the questions.
> Give an example of a rowbased statement, and a set based statement. I'm su
re
> know how to code it but I don't know what it is.
> Another question I ran into was "what setting do you ALWAYS disable" in a
> live environment. Now I don't know this either... it's not a registered
> Microsoft exam so I don't know if the questions are dodgy.
> Answers would be appreciated though.
> THanks|||Hi Mal
For Row by Row operations look at
http://www.sql-server-performance.com/dp_no_cursors.asp
For things to switch off then looks at Brads article:
http://www.sql-server-performance.c...mance_audit.asp
John
"Mal" wrote:

> Hey
> I've been doing for SQL for a while but recently I've been trying to get
> some whitepaper for it, I'm now working through some questions I obviously
> first I google the questions before I came here to ask.
> I ran into the questions.
> Give an example of a rowbased statement, and a set based statement. I'm su
re
> know how to code it but I don't know what it is.
> Another question I ran into was "what setting do you ALWAYS disable" in a
> live environment. Now I don't know this either... it's not a registered
> Microsoft exam so I don't know if the questions are dodgy.
> Answers would be appreciated though.
> THanks|||Chandra,
That was Terrific :(
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A9EB58C2-F6D2-4244-B324-CF9330F1572A@.microsoft.com...
> Hi Mal
> Row based statement:
> SELECT * FROM <TABLE> WHERE NEWID() = '<some ID>'
>
> SET based statement
> SELECT * FROM <TABLE>
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> ---
>
> "Mal" wrote:
>|||Chandra wrote:
> Hi Mal
> Row based statement:
> SELECT * FROM <TABLE> WHERE NEWID() = '<some ID>'
>
This is not quite correct. SELECT statements generate sets of records (which
is why the term used forthe result of a SELECT statement is "resultset"). A
set can contain a single member.
When we talk about doing a "row by row" operation, we are normally talking
about doing something with a cursor. Looping through a set of rows,
processing one row at a time, is a "row by row" operation. Most experienced
developers frown on row by row processing, due to its usual lack of
performance in comparison to set-based operations.
Here is a set-based operation:
UPDATE <TABLE>
SET <COLUMN> = <value>
WHERE <COLUMN1> = <value1>
Here is the equivalent operation done row by row:
DECLARE @.compare <datatype>
DECLARE cur scroll cursor FOR
SELECT <COLUMN1>, <COLUMN> FROM <TABLE>
FOR UPDATE
open cur
FETCH FIRST FROM cur INTO @.compare
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
if @.@.FETCH_STATUS <> -2
BEGIN
IF @.compare = <value1>
UPDATE <TABLE>
SET <COLUMN> = <value>
WHERE CURRENT OF cur
FETCH NEXT FROM cur INTO @.compare
END
END
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.