RowNumber Group

I have a report the group is a people name and the detail is their personal
informations. I wuold like insert a row numer in the group, try but in the
list sometime jump a numeber. For example:
...
...
10 John Smith
11 Luis Abrams
13 Mike Logan
...
...
25 Sam Carter
27 Tom Grey
...
how have a solution for my problem?
Thank's
MauroHi,
you can try use running value
=3DRunningValue(Fields!keyfieldinthegroup.Value, CountDistinct,
"groupname")
On Apr 3, 10:22=A0pm, Mauro <Ma...@.discussions.microsoft.com> wrote:
> I have a report the group is a people name and the detail is their persona=l
> informations. I wuold like insert a row numer in the group, try but in the=
> list sometime jump a numeber. For example:
> ...
> ...
> 10 John Smith
> 11 Luis Abrams
> 13 Mike Logan
> ...
> ...
> 25 Sam Carter
> 27 Tom Grey
> ...
> how have a solution for my problem?
> Thank's
> Mauro

RowNumber

I understand that if using this function with "nothing" between parentheses, then the running row total never resets.

However, I am hoping to have the row counter reset when a group value changes. I tried putting both the field and the defined group name in the parentheses both with and without quotes, but I get an error.

What is the correct syntax for accomplishing this?

This may help...

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

cheers,

Andrew

ROWNUM In Oracle

Is there any way to implement ROWNUM in Oracle to select row no.
Is there any way to select the row no when retrieving records as
1 Tv
2 Fridge
3 RadioHi,
There is no concept of Rownum in sql server.
But you could write ur own query to get the serial number.
Use the below script as sample:-
create table item(item_code varchar(05))
go
insert into item values('a1')
insert into item values('a2')
insert into item values('a3')
insert into item values('a4')
go
SELECT (SELECT COUNT(i.item_code)
FROM item i
WHERE i.item_code >= o.item_code ) AS RowID,
item_code
FROM item o
ORDER BY RowID
Thanks
Hari
SQL Server MVP
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:3BDAC82C-4AB5-4631-ABAE-AB693B0E1312@.microsoft.com...
> Is there any way to implement ROWNUM in Oracle to select row no.
> Is there any way to select the row no when retrieving records as
> 1 Tv
> 2 Fridge
> 3 Radio|||Hi
There is not a ROWNUM function in SQLServer 2000 using an identity column is
usually the alternative. If you want to rank your values then you could use
a
construct like:
e.g
SELECT ( SELECT COUNT(*) FROM MyTable T WHERE t.id <= M.id ) AS Rank,
col1, col2
FROM MyTable M
These links may also help.
http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
http://www.microsoft.com/resources/...r />
0761.mspx
http://www.microsoft.com/sql/evalua...pare/oracle.asp
John
"Renjith" wrote:

> Is there any way to implement ROWNUM in Oracle to select row no.
> Is there any way to select the row no when retrieving records as
> 1 Tv
> 2 Fridge
> 3 Radio|||Hi
If it is a big table then the count(*) as inner query will create
performance problem '
"John Bell" wrote:
> Hi
> There is not a ROWNUM function in SQLServer 2000 using an identity column
is
> usually the alternative. If you want to rank your values then you could us
e a
> construct like:
> e.g
> SELECT ( SELECT COUNT(*) FROM MyTable T WHERE t.id <= M.id ) AS Rank,
> col1, col2
> FROM MyTable M
> These links may also help.
> http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
> http://www.microsoft.com/resources/.../>
/c0761.mspx
> http://www.microsoft.com/sql/evalua...pare/oracle.asp
> John
> "Renjith" wrote:
>|||Hi
It may, and indexing would reduce the problem.
You can also do something like:
CREATE TABLE MyTest ( id int not null identity(1,1), val char(1))
INSERT INTO MyTest ( val )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
DELETE FROM MyTest where val = 'C'
SELECT m.id, COUNT(*) as Rank, m.val
FROM MyTest m
JOIN MyTest r ON R.id <= M.id
GROUP BY m.id, M.val
ORDER BY 2
Another alternative would be do deligate the numbering to the client.
John
"Renjith" wrote:
> Hi
> If it is a big table then the count(*) as inner query will create
> performance problem '
> "John Bell" wrote:
>|||Hi
You may want to look at Itzik Ben-Gan's articles in the May 2005 SQL
Server Magazine.
http://www.windowsitpro.com/Article...5828/45828.html
http://www.windowsitpro.com/Article...2302/42302.html
http://www.windowsitpro.com/Article...2646/42646.html
John

Rowlock appears to be locking entire table.

Without going into a lot of history, I am using embedded SQL in a Micro-Focus

COBOL application.

I have a need to be able to select a specific row from my table and lock that row until I release it.

I have successfully used a declare cursor with a rowlock hint that does lock that row, unfortunately

it also locks the entire table.

I know that SQL Server is best utilized by allowing it it to manage locks, but this is a unique situation

and all I want is a clear answer as to how this should work when I have to do it.

here is a sample of what I have tried. I'm hoping someone has successfully done something

like this (maybe not even in cobol).

EXEC SQL DECLARE TECHLK CURSOR FOR
select
TECH_ID
from TECH_REC
with (rowlock,nowait)
where (TECH_ID = :SQL-TECH-ID)
END-EXEC.

EXEC SQL
OPEN TECHLK
END-EXEC.

EXEC SQL
FETCH TECHLK INTO
:SQL-TECH-ID
END-EXEC.

any help would be appreciated.. thx, Ray Dodson

1. How did you identify that entire table is locked?

2. What queries does your application actually sent to the server? You can figure that out using Profile tool.

3. SQL Server maintain record lock granularity untill it has enough resources for that. Otherwise lock granularity escalated to the table level.

|||

This reply asked more questions than it answered.

1. I used the management item in seql server enterprise manager to see the

locking status.

Actually, I found after this post was sent that my keys were not set up correctly (imported

from another database). that appears to be why I was locking the entire table. I have rectified that

and things seem to be working correctly now.

thanks for you time... Ray Dodson

RowLock

I have to lock the row while insertion or at updation when many users
accessthe particular row.i f the first user access the row after at that tim
e
the row should be locked after his updation the row will unlock since many
users not have to updation at the same time.so ihave lock and release the ro
w
in the stored procedureIf I understood your post correctly, you need some sort of check-out
management. This is not provided natively by SQL Server 2000, and can be
achieved in several ways.
If you specify your needs and provide the DDL and some sample data, maybe we
can help you find a solution.
ML|||hi ML,
I will explain in clearly.
By Using frontend(PHP forms)my clients can update a particular row in
particular table at that same time.while updating i have to update th versio
n
number in the table.so i took the maxvalue of that column at that particular
row and update version mumber.while at that time of updating max count two o
r
more clients gets the same value of that the particular column at that same
time.so it will crasing the number.so i have to lock that paricular while on
e
updating if another tries it displays a message some one is updating,so
please wait a moment and try after his updation is over the row lockis
released and another client able to sccess that row and update.
so how can lock that row and release the lock
it is long query,the query depends many so i can't able to send the query
directly
regards,
balakarthik
"ML" wrote:

> If I understood your post correctly, you need some sort of check-out
> management. This is not provided natively by SQL Server 2000, and can be
> achieved in several ways.
> If you specify your needs and provide the DDL and some sample data, maybe
we
> can help you find a solution.
>
> ML|||balakarthik
You have already asked this in .server and already have been given some
references there. Please do not multipost, it make is very confusing for
those people who are trying to help, who don't know what has already been
said.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:E98C44C0-69AC-489A-9B52-D43785C4D684@.microsoft.com...
> hi ML,
> I will explain in clearly.
> By Using frontend(PHP forms)my clients can update a particular row in
> particular table at that same time.while updating i have to update th
> version
> number in the table.so i took the maxvalue of that column at that
> particular
> row and update version mumber.while at that time of updating max count two
> or
> more clients gets the same value of that the particular column at that
> same
> time.so it will crasing the number.so i have to lock that paricular while
> one
> updating if another tries it displays a message some one is updating,so
> please wait a moment and try after his updation is over the row lockis
> released and another client able to sccess that row and update.
>
> so how can lock that row and release the lock
> it is long query,the query depends many so i can't able to send the query
> directly
>
> regards,
> balakarthik
> "ML" wrote:
>|||You need to use SELECT MAX...FROM...WITH(UPDLOCK, HOLDLOCK) to get the
maximum value, and the locks must be held until the INSERT/UPDATE completes.
If you issue INSERT...SELECT MAX...FROM ...WITH(UPDLOCK, HOLDLOCK) then the
INSERT statement provides an implicit transaction. If you issue SELECT
@.localVariable = MAX...FROM...WITH(UPDLOCK, HOLDLOCK) INSERT...VALUES, then
you need to enclose both statements within a transaction. WITH(UPDLOCK,
HOLDLOCK) prevents other transactions from issuing an insert or update that
has a value for the column that is between MAX and infinity until the lock
is released. Both UPDLOCK and HOLDLOCK are required because you need an
update range-lock, not an individual update lock, or a shared range-lock.
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:E98C44C0-69AC-489A-9B52-D43785C4D684@.microsoft.com...
> hi ML,
> I will explain in clearly.
> By Using frontend(PHP forms)my clients can update a particular row in
> particular table at that same time.while updating i have to update th
version
> number in the table.so i took the maxvalue of that column at that
particular
> row and update version mumber.while at that time of updating max count two
or
> more clients gets the same value of that the particular column at that
same
> time.so it will crasing the number.so i have to lock that paricular while
one
> updating if another tries it displays a message some one is updating,so
> please wait a moment and try after his updation is over the row lockis
> released and another client able to sccess that row and update.
>
> so how can lock that row and release the lock
> it is long query,the query depends many so i can't able to send the query
> directly
>
> regards,
> balakarthik
> "ML" wrote:
>
maybe we|||I see.
Let us forget for a minute about rows, columns, tables and databases. Let's
discuss entities.
Let's say your entity is a single document in a drawer. But this is not an
ordinary drawer, since many users can read the same document at any given
moment. However, only one of them should be allowed to change the contents o
f
that document at any given time.
What you need to do is to let other users know that changes to a document
cannot be made if that document is "checked-out".
In a database you need to do something like this:
1) make sure all data access is done through procedures; and
2) make sure procedures cannot make changes to documents that are
checked-out; and
3) make sure the appropriate procedures report check-outs to the database.
One way is to add a column (or two) to the master table (BTW: since I
haven't seen your DDL this is more a guess than a fully valid suggestion):
CheckedOutBy - it holds the username of the person who checked out the
document. When null, the document is not checked out.
(Maybe even CheckedOutSince - it holds the time of the check-out.)
So, in a use case you'd do something like:
when a user gets a document that hasn't been checked-out (CheckOutBy is
null), the reading procedure sets CheckedOutBy to store the name of the
current user; and when a user finishes his changes the procedure then sets
CheckedOutBy back to null;
any user that tries to check out a document that is already checked out by
another user is warned that his changes will not succeed, and the applicatio
n
should prevent any changes to be made.
But, this is just *one* way of doing it.
ML|||Thanks sir i wil try
"ML" wrote:

> I see.
> Let us forget for a minute about rows, columns, tables and databases. Let'
s
> discuss entities.
> Let's say your entity is a single document in a drawer. But this is not an
> ordinary drawer, since many users can read the same document at any given
> moment. However, only one of them should be allowed to change the contents
of
> that document at any given time.
> What you need to do is to let other users know that changes to a document
> cannot be made if that document is "checked-out".
> In a database you need to do something like this:
> 1) make sure all data access is done through procedures; and
> 2) make sure procedures cannot make changes to documents that are
> checked-out; and
> 3) make sure the appropriate procedures report check-outs to the database.
> One way is to add a column (or two) to the master table (BTW: since I
> haven't seen your DDL this is more a guess than a fully valid suggestion):
> CheckedOutBy - it holds the username of the person who checked out the
> document. When null, the document is not checked out.
> (Maybe even CheckedOutSince - it holds the time of the check-out.)
> So, in a use case you'd do something like:
> when a user gets a document that hasn't been checked-out (CheckOutBy is
> null), the reading procedure sets CheckedOutBy to store the name of the
> current user; and when a user finishes his changes the procedure then sets
> CheckedOutBy back to null;
> any user that tries to check out a document that is already checked out by
> another user is warned that his changes will not succeed, and the applicat
ion
> should prevent any changes to be made.
> But, this is just *one* way of doing it.
>
> ML

Row-Limit per Group

I need to sum() the 3 biggest values from a specific field of each group.
Using
SELECT sum(numbers) FROM table GROUP BY field
operates on every row and LIMIT only restricts the final result. What I need is a way to limit the rows per group.select sum(numbers)
from daTable as X
where ( select count(*)
from daTable
where numbers > X.numbers) < 3|||This only gives a single amount, i.e., the sum of the biggest three from the whole table.
To obtain the sums of the three biggest values from each group:SELECT field, sum(numbers)
FROM daTable as X
WHERE (SELECT count(*)
FROM daTable
WHERE field = X.field AND numbers > X.numbers) < 3
GROUP BY field|||well spotted, peter, you are quite right, i misunderstood the question

:)

rowid is there?

Hi experts,
I'm in need to use the rowid of a column.
is there any concept like rowid?

for example, i need the first row of a table.or 5th row of a table.how can i write the query?

thank you verymuch in advance.

Quote:

Originally Posted by vinod

Hi experts,
I'm in need to use the rowid of a column.
is there any concept like rowid?

for example, i need the first row of a table.or 5th row of a table.how can i write the query?

thank you verymuch in advance.

select top 1 * from tablename|||Add a column of int datatype to the existing table. Define identity on that column. Define Identity seed and identity increment as 1,1.
The following is the syntax.

a int identity(1,1). Here a is the column. SQL Server automatically increments the number. Based on that number you can fetch the desired record.|||Using Identity function is really great friend.
I'm trying for my purpose now, if i need some more assistance, i'l come back to you

RowId in SQL server 2000

:confused: Hi Champs,
Is there anyway to get row id of row stored in specific table in sql server 2000 database similar to Oracle?
Thanks in advance,
JaiNope...in 2005 they have it

You could add an identity column to your row

Even in Oracle it's not really an "id" for that row.

The row id is not always assigned to the same row.

It could be different every time

Identity would always be the same.

Why?

RowGuid

Hi

What impact is there in setting a unqiueidentifier primary key to be a row guid?

How does this impact performance?

How does this impact data file size?

Does it impact anything else?

Thanks

Hi,

I guess you don′t want to open up taht religious discussion, right ? There were many, many, many discussions already about this out there which can be find googling / MSNning around:

http://www.google.de/search?hl=de&q=GUID+Primary+Key&meta=

There are really many opinions out there, for me (for my personal opinion) you should avoid them, as long as you don′t really need them, like for distributed applications)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens

Thanks for the response but I wasn't asking if I should use a UNIQUEIDENTIFIER as a primary key.

I was just wondering what the impact was in setting the rowguid flag.

For example...

CREATE TABLE Schema.Table (Id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT PTableId PRIMARY KEY NONCLUSTERED WITH FILLFACTOR=60)

CREATE TABLE Schema.Table (Id UNIQUEIDENTIFIER NOT NULL CONSTRAINT PTableId PRIMARY KEY NONCLUSTERED WITH FILLFACTOR=60)

sql

Rowcounts on all tables in a DB

I need to create a query to get the row counts of all tables in a
particular database. Can somebody point me to a method of doing this?
Regards,
Randy
This will probably work for what you need:
--2005
select object_name(object_id), rows
from sys.partitions
order by 2 desc
--2000
select object_name(id), rowcnt
from sysindexes
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy
|||Hi
--SQL Server 2005
SELECT
t.name,
[RowCount] = SUM
(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
)
FROM
sys.tables t
INNER JOIN sys.partitions p
ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY
t.name;
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy
|||Thank you for all the help. The queries are just what I needed.
Regards,
Randy.
Randy Galliano wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy
|||On Wed, 20 Feb 2008 16:44:12 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Just an FYI that for 2000, the rowcount can very well be off (not reflect reality). In 2005 it is
>likely to reflect reality.
But if you run:
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS
just before you should have accurate numbers for all tables. Or, to
be more precise, each table's count will have been accurate very
recently.
Roy Harvey
Beacon Falls, CT
|||On Feb 20, 5:23Xam, Randy Galliano <r_galli...@.yahoo.com> wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. XCan somebody point me to a method of doing this?
> Regards,
> Randy
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx

Rowcounts on all tables in a DB

I need to create a query to get the row counts of all tables in a
particular database. Can somebody point me to a method of doing this?
Regards,
RandyThis will probably work for what you need:
--2005
select object_name(object_id), rows
from sys.partitions
order by 2 desc
--2000
select object_name(id), rowcnt
from sysindexes
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy|||Hi
--SQL Server 2005
SELECT
t.name,
[RowCount] = SUM
(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
)
FROM
sys.tables t
INNER JOIN sys.partitions p
ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY
t.name;
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy|||Just an FYI that for 2000, the rowcount can very well be off (not reflect reality). In 2005 it is
likely to reflect reality.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:B2937C70-6DD2-4E49-8028-1148FF13E8FF@.microsoft.com...
> This will probably work for what you need:
> --2005
> select object_name(object_id), rows
> from sys.partitions
> order by 2 desc
> --2000
> select object_name(id), rowcnt
> from sysindexes
> order by 2 desc
>
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "Randy Galliano" <r_galliano@.yahoo.com> wrote in message
> news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>>I need to create a query to get the row counts of all tables in a particular database. Can
>>somebody point me to a method of doing this?
>> Regards,
>> Randy
>|||Thank you for all the help. The queries are just what I needed.
Regards,
Randy.
Randy Galliano wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy|||On Wed, 20 Feb 2008 16:44:12 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Just an FYI that for 2000, the rowcount can very well be off (not reflect reality). In 2005 it is
>likely to reflect reality.
But if you run:
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS
just before you should have accurate numbers for all tables. Or, to
be more precise, each table's count will have been accurate very
recently.
Roy Harvey
Beacon Falls, CT|||On Feb 20, 5:23=A0am, Randy Galliano <r_galli...@.yahoo.com> wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. =A0Can somebody point me to a method of doing this?
> Regards,
> Randy
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=
o-count-rows-from-a-table.aspx

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran
"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

You could use dynamic sql (execute a string using EXEC or sp_executesql).|||Thanks

ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

There are several options:

1. Use dynamic SQL to generate the entire SELECT statement

2. Use CASE expression in the ORDER BY clause like:

ORDER BY case @.SortColumn when 1 then col1 end,

case @.SortColumn when 2 then col2 end

3. Use various SELECT statements with UNION operator to perform branching. This is best of both worlds.

There are advantages and disadvantages to these methods. By specifying column(s) directly in ORDER BY clause any covering index can be used whereas with CASE approach you lose that advantage. Dynamic SQL approach needs to be protected against SQL injection, requires additional permissions for caller, you can use execution context in SQL2005 and so on.

|||Thank you so much

ROW_NUMBER() and projected row count.

Is there a way without rerunning the select query to get the "total row count" using

ROW_NUMBER and BETWEEN as such..

SELECT * FROM

(SELECT ROW_NUMBER() OVER(ORDER BY Year DESC, Month DESC, Day DESC) as RowNum,

e.id, e.Title

FROM Events e

) as DerivedTableName

WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1

typically i would build a temp table and return SELECT @.@.ROWCOUNT

? Why not just do: SELECT COUNT(*) FROM Events e -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <jkgreer@.discussions.microsoft.com> wrote in message news:f54208af-7f15-440a-b9fa-41e0f8c59ada@.discussions.microsoft.com... Is there a way without rerunning the select query to get the "total row count" using ROW_NUMBER and BETWEEN as such.. SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Year DESC, Month DESC, Day DESC) as RowNum, e.id, e.Title FROM Events e ) as DerivedTableName WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1 typically i would build a temp table and return SELECT @.@.ROWCOUNT|||

yeah that is pretty much what i'm left with right now but, that was an overly simplified example.

i'm feeding very complex where and order statements into it and if there isn't any overhead in rerunning the query i wouldn't mind.

knowing the virtual count of rows / pages seems to go hand in hand with paging and i wanted to make sure that switching to using ROW_NUMBER() and BETWEEN was a better choice than creating a temp table of ordered indexes and joining off of it just to retrieve the full count of filtered rows.

|||? Yes, I believe that the ROW_NUMBER solution is far superior to the temp table. I also think that running the query twice is not a huge issue if your users page past the first page on most searches. In that case, it means a better user experience. But if that's not the case, I might think twice about taking the full count if I were you. When it comes to paging, it's really a choice of performance vs. a slightly less functional UI -- and in many cases the full count really doesn't add that much anyway. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <jkgreer@.discussions.microsoft.com> wrote in message news:ea3176ea-5d77-4399-a575-31284186b42e_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com yeah that is pretty much what i'm left with right now but, that was an overly simplified example. i'm feeding very complex where and order statements into it and if there isn't any overhead in rerunning the query i wouldn't mind. knowing the virtual count of rows / pages seems to go hand in hand with paging and i wanted to make sure that switching to using ROW_NUMBER() and BETWEEN was a better choice than creating a temp table of ordered indexes and joining off of it just to retrieve the full count of filtered rows.|||

Thanks. that is what i expected i suppose.

I was going for the full featured with this question, but i have no issue letting users fall off the end of a page in places, point well taken.

Row yielded no match during lookup

I have configured a lookup transformation to 'redirect error' all no-matched rows to a text file using the flat file destination.

Now I want to send the same text file as an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Can we use the send email task on eventhandler and invoke the same in case of such error "row yielded no match during lookup" so that we can send the such non matching rows as an email.

Or else any other way to send an email after generating the text file ocntaining the non matching rows.

Please suggest using steps or example

There's likely to be more than one way to do this. Here' one possiblity. After the Dataflow task in the Control Flow, use a sequence container that contains two tasks, a script task that checks for the existence of the error file. Here's the script to check setting a Boolean variable, FileExists, to either two of false.

Dim objFile AsNew FileInfo(Dts.Variables("TempFileName").Value.ToString)

Dts.Variables("FileExists").Value = objFile.Exists

The second task in the container is the Send Mail task which is connected by a Precedence contraint which only executes if the FileExists variable is true.

HTH

|||

Thanks for reply.

Is there any way to invoke the send email task (Assume that the send email task) is present in eventhandler, when we transfer the non matching rows to flat file using flat file destination.

Please suggest.

|||

I'm not sure I understand what you're trying to do. I think you're expecting the OnError event of the DataFlow task to be triggered when a match is not found in the Lookup component. However, if you specify to redirect rows on the Lookup component when no matching row is found, the OnError event of the DataFlow task will not be fired. Am I guessing correctly? If not, which Event (there are several) are you planning on using the SendMail task. OnPostExecute of the DataFlow task? The two tasks used in the Sequence container above could be used in this event to obtain the same result.

|||

thanks for reply.

Your guess is right.

All I need is when lookup transform encounter no match then it should record all such distinct nonmatching rows and then send the same detail to other member using email.

I am new in SSIS, Please suggest me how to do this either by event or any other way

thanks

Row yielded no match during lookup

In SSIS. I am having trouble exporting records
that don't match from a lookup transformation. I get the following
error:

Row yielded no match during lookup.

I would really like to have a list of all records that did not match so
that I could send an email of those missing rows

Please give me solution with example

Thanks

That is happening because your are using the default error configuration of the Lookuptask "Fail Component"; it would fail if a no match occurs. You need to change that to 'Redirect row' and then use the error output of the task to send those rows to whereever you want.

Rafael Salas

|||

Thanks for email

I m new in SSIS, please suggest such error output with example.

|||

leo1 wrote:

Thanks for email

I m new in SSIS, please suggest such error output with example.

Leo,

when you configure a lookup transformation to 'redirect error' all no-matched rows are sent to the error output instead of failing the task (the error you originally received); obviously those error rows will have null in the columns that the lookup transformation added. Then, based in your requirements, you can decide what to do with those errors. e.g. for a data warehouse your may want to replace the nulls by default values and insert them to the destination table; and/or you can decide to send them to an custom error table.

Rafael Salas

|||

thanks for reply.

I want to find out all such distinct rows or lookup id and send an email of all such non-matching items via email to the team.

Can you please suggest me (Steps) or example how to do this.

thanks

|||

Use a Flat File Destination Adapter to push that data into a file. You can then send that file using the Send mail Task.

-jamie

|||

I have got all such rows in the file using the flat file destination in data flow

Kindly let me know how to send an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Should we use the send email task on eventhandler, if yes, how to check for error and invoke send email task.

Kindly suggest possibly by example or steps.

|||I use a lookup often for different purposes.

For example currently i'm using it to pull "Open House" information for Properties.
Only a few of those have open house schedules - so what i do is i have two outs from Lookup - and they both go to Union All transform.

Row yielded no match during lookup

In SSIS. I am having trouble exporting records
that don't match from a lookup transformation. I get the following
error:

Row yielded no match during lookup.

I would really like to have a list of all records that did not match so
that I could send an email of those missing rows

Please give me solution with example

Thanks

That is happening because your are using the default error configuration of the Lookuptask "Fail Component"; it would fail if a no match occurs. You need to change that to 'Redirect row' and then use the error output of the task to send those rows to whereever you want.

Rafael Salas

|||

Thanks for email

I m new in SSIS, please suggest such error output with example.

|||

leo1 wrote:

Thanks for email

I m new in SSIS, please suggest such error output with example.

Leo,

when you configure a lookup transformation to 'redirect error' all no-matched rows are sent to the error output instead of failing the task (the error you originally received); obviously those error rows will have null in the columns that the lookup transformation added. Then, based in your requirements, you can decide what to do with those errors. e.g. for a data warehouse your may want to replace the nulls by default values and insert them to the destination table; and/or you can decide to send them to an custom error table.

Rafael Salas

|||

thanks for reply.

I want to find out all such distinct rows or lookup id and send an email of all such non-matching items via email to the team.

Can you please suggest me (Steps) or example how to do this.

thanks

|||

Use a Flat File Destination Adapter to push that data into a file. You can then send that file using the Send mail Task.

-jamie

|||

I have got all such rows in the file using the flat file destination in data flow

Kindly let me know how to send an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Should we use the send email task on eventhandler, if yes, how to check for error and invoke send email task.

Kindly suggest possibly by example or steps.

|||I use a lookup often for different purposes.

For example currently i'm using it to pull "Open House" information for Properties.
Only a few of those have open house schedules - so what i do is i have two outs from Lookup - and they both go to Union All transform.