Role Nesting

Hi,

I am developing the security in a sql database, and i am doing so in a hierarchical way. In the white paper Row and cell security it says that we must add the child role as a menber of the parent role, but when you are in the role section you can not add roles as menbers of another role, so what i did was give the parent role ownership over the child role, it seemed logical.

So i build a admin
|
boss
|
worker
|
subworker

Nested Role

Now after giving users to those roles i was good to go and try my hierarchy security, i used the view that is in the white paper cell and row security which the code is the following:

SELECT ID, Label
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))

But when i runned this view dont matter which users in which role ist always giving me an output of every single line in the table, the problem seem that he is giving me out the IS_MEMBER(MarkingRoleName) = 1 always as true like the user was in every single role.

What am i doing wrong?

Thanks.

What is MarkingRoleName evaluated to for each user?

Also, ownership is not the same as nesting. To make a role a member of another role, you can use sp_addrolemember.

For example,

sp_addrolemember role1, role2

will make role2 a member of role1.

Thanks
Laurentiu

Role Nesting

Hi,

I am developing the security in a sql database, and i am doing so in a hierarchical way. In the white paper Row and cell security it says that we must add the child role as a menber of the parent role, but when you are in the role section you can not add roles as menbers of another role, so what i did was give the parent role ownership over the child role, it seemed logical.

So i build a admin
|
boss
|
worker
|
subworker

Nested Role

Now after giving users to those roles i was good to go and try my hierarchy security, i used the view that is in the white paper cell and row security which the code is the following:

SELECT ID, Label
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))

But when i runned this view dont matter which users in which role ist always giving me an output of every single line in the table, the problem seem that he is giving me out the IS_MEMBER(MarkingRoleName) = 1 always as true like the user was in every single role.

What am i doing wrong?

Thanks.

What is MarkingRoleName evaluated to for each user?

Also, ownership is not the same as nesting. To make a role a member of another role, you can use sp_addrolemember.

For example,

sp_addrolemember role1, role2

will make role2 a member of role1.

Thanks
Laurentiu

Robust Plan execution error?

I am running a select against a View that exceeds the max allowable row size and added OPTION (ROBUST PLAN) to the select to enable the select to return a result when the row size is a problem. The original view does a number of joins.

One of our users has reported that some data values they expect to see in some of the columns for specific rows are missing or incorrect. Could using the ROBUST PLAN hint cause this to occur?

Thanks,

ChrisRefer to this link (http://www.sql-server-performance.com/hints_query.asp) for tips on ROBUST PLAN and SQLMAG (http://www.sqlmag.com/forums/messageview.cfm?catid=9&threadid=15767) link for references.

Robust Plan

Folks,
I am getting the follwoing error:
"cannot sort a row of size 8192, which is greater than the allowable
maximum of 8094" .
Doing the research around the web, I found a solution which had be
using Option "Robust Plan" as a part of the query.
I am getting another error "Warning: The query processor could not
produce a query plan from the optimizer because the total length of all
the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.
Resubmit your query without the ROBUST PLAN hint."
Am i missing something.
- JessHave you calculated how much bytes the arguments in the GROUP BY and/or
ORDER BY clause need? If it exceeds 8094 bytes, then it will simply not
work, regardless of the ROBUST PLAN option.
When in doubt, then please post the query and relevant DDL.
HTH,
Gert-Jan
Hess wrote:
> Folks,
> I am getting the follwoing error:
> "cannot sort a row of size 8192, which is greater than the allowable
> maximum of 8094" .
> Doing the research around the web, I found a solution which had be
> using Option "Robust Plan" as a part of the query.
> I am getting another error "Warning: The query processor could not
> produce a query plan from the optimizer because the total length of all
> the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.
> Resubmit your query without the ROBUST PLAN hint."
> Am i missing something.
> - Jesssql

Robust Plan

Folks,
I am getting the follwoing error:
"cannot sort a row of size 8192, which is greater than the allowable
maximum of 8094" .
Doing the research around the web, I found a solution which had be
using Option "Robust Plan" as a part of the query.
I am getting another error "Warning: The query processor could not
produce a query plan from the optimizer because the total length of all
the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.
Resubmit your query without the ROBUST PLAN hint."
Am i missing something.
- JessHave you calculated how much bytes the arguments in the GROUP BY and/or
ORDER BY clause need? If it exceeds 8094 bytes, then it will simply not
work, regardless of the ROBUST PLAN option.
When in doubt, then please post the query and relevant DDL.
HTH,
Gert-Jan
Hess wrote:
> Folks,
> I am getting the follwoing error:
> "cannot sort a row of size 8192, which is greater than the allowable
> maximum of 8094" .
> Doing the research around the web, I found a solution which had be
> using Option "Robust Plan" as a part of the query.
> I am getting another error "Warning: The query processor could not
> produce a query plan from the optimizer because the total length of all
> the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.
> Resubmit your query without the ROBUST PLAN hint."
> Am i missing something.
> - Jess

RID

How many bytes is a Row Identifier in a heap?
A RID is 8 bytes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:1A551EB2-2D58-411C-8CF0-D135F0AD6C3D@.microsoft.com...
> How many bytes is a Row Identifier in a heap?
|||Excellent thanks for that :-)
"Dan Guzman" wrote:

> A RID is 8 bytes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:1A551EB2-2D58-411C-8CF0-D135F0AD6C3D@.microsoft.com...
>

RID

How many bytes is a Row Identifier in a heap?A RID is 8 bytes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:1A551EB2-2D58-411C-8CF0-D135F0AD6C3D@.microsoft.com...
> How many bytes is a Row Identifier in a heap?|||Excellent thanks for that :-)
"Dan Guzman" wrote:
> A RID is 8 bytes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:1A551EB2-2D58-411C-8CF0-D135F0AD6C3D@.microsoft.com...
> > How many bytes is a Row Identifier in a heap?
>

RID

How many bytes is a Row Identifier in a heap?A RID is 8 bytes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:1A551EB2-2D58-411C-8CF0-D135F0AD6C3D@.microsoft.com...
> How many bytes is a Row Identifier in a heap?|||Excellent thanks for that :-)
"Dan Guzman" wrote:

> A RID is 8 bytes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:1A551EB2-2D58-411C-8CF0-D135F0AD6C3D@.microsoft.com...
>

Rewriting Insert Statements

Hi Friends,
I have the following set of Insert Statements that calculates sums for various criteria and inserts a row at a time onto my table.
I have a row for every month starting from January with sums for 4 severity levels. So for 12 months that would be 48 Insert Statements and if I want to do this for 4 different types of [EName] that would be 48 * 4 = 192 Insert Statements. Is there a better way to write this. Thanks for your help

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 1)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 2)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 3)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 4)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 1)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 2)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 3)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 4)Maybe something along the lines of

SELECT 'OVERALL' AS [EName], trendmonth AS TrendMonth, SeverityLevel , Sum([Count])
FROM dbo.tbl_Ticket
group by EName, Trendmonth, SeverityLevel|||Works Great!! Thank you very much.

Reverse equivalent to TOP

Is there anyway I can return only the last row of a query, like TOP does for the top most items?

I would like to return something like this:

SELECT BOTTOM 1 Column_C
FROM Table1
WHERE Column_A = something

Any help would be greatly appreciated. Thank you!

gerardkcohen:

Is there anyway I can return only the last row of a query, like TOP does for the top most items?

I would like to return something like this:

SELECT BOTTOM 1 Column_C
FROM Table1
WHERE Column_A = something

Any help would be greatly appreciated. Thank you!

gerardkcohen --

Use a subquery. Get IDs in the order you want using an "order by desc". Get the IDs you want using a "TOP". Get the data you want using a "select * ... where in". And so on.

Here is some sample code.

use northwind
go
--get all the rows, in order, to see what we are working with...
select * from Shippers order by ShipperID asc
/* output...

ShipperID   CompanyName                              Phone                   
---- ------------ --------
1           Speedy Express                           (503) 555-9831
2           United Package                           (503) 555-3199
3           Federal Shipping                         (503) 555-9931
(3 row(s) affected)
*/
--get the top 2 rows
select * from Shippers where ShipperID in (select top 2 ShipperID from Shippers order by ShipperID asc)
/* output...

ShipperID   CompanyName                              Phone                   
---- ------------ --------
1           Speedy Express                           (503) 555-9831
2           United Package                           (503) 555-3199
(2 row(s) affected)
*/
--get the bottom 2 rows
select * from Shippers where ShipperID in (select top 2 ShipperID from Shippers order by ShipperID desc)
/* output...

ShipperID   CompanyName                              Phone                   
---- ------------ --------
3           Federal Shipping                         (503) 555-9931
2           United Package                           (503) 555-3199
(2 row(s) affected)
*/
 

HTH.

Thank you.

-- Mark Kamoski

|||

You can still use TOP 1 to get the bottom 1 by adding ORDER BY Column_C DESC

Like:

SELECT TOP 1 Column_C
FROM Table1
WHERE Column_A = something

ORDER BY Column_C DESC

--edited

|||

Yes remember to changeBOTTOM -> top

SELECTtop 1 Column_C
FROM Table1
WHERE Column_A = something

ORDER BY Column_C DESC

re-use rowguid, replication fails?

My ROWGUID columns are also my primary keys in many cases. I have
some code that deletes and then reinserts a row that has changes
instead of doing an UPDATE. As such, I re-use the same guid in the
ROWGUID column.
I have found that when I do this, the changes to the row do not make
it back up to the master server during a synchronize, as if the
replication does not recognize that the delete and re-insert has been
done on the row. If I do an update on the row, the change is
recognized and sent back to the master server.
Is this an expected behavior? Is it a no-no to delete a row with
ROWGUID x, then re-insert a new row with the same ROWGUID x?
I can probably whip up a step-by-step example that demonstrates this
behavior if desired.
thanks,
matthew tagliaferri
I take it you are using merge replication. Merge replication uses the
rowguid column to track which row has changed. Its probably not a good idea
to modify this row.
Can you check your conflict tables using the conflict viewer to see if any
conflicts are being logged. If not, please post your repo.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"matt tagliaferri" <mtagliaf@.cleindians.com> wrote in message
news:2cc74fa4.0411181102.5406ca87@.posting.google.c om...
> My ROWGUID columns are also my primary keys in many cases. I have
> some code that deletes and then reinserts a row that has changes
> instead of doing an UPDATE. As such, I re-use the same guid in the
> ROWGUID column.
> I have found that when I do this, the changes to the row do not make
> it back up to the master server during a synchronize, as if the
> replication does not recognize that the delete and re-insert has been
> done on the row. If I do an update on the row, the change is
> recognized and sent back to the master server.
> Is this an expected behavior? Is it a no-no to delete a row with
> ROWGUID x, then re-insert a new row with the same ROWGUID x?
> I can probably whip up a step-by-step example that demonstrates this
> behavior if desired.
> thanks,
> matthew tagliaferri
|||No conflicts exist. I can look at the table in question on the "master"
and "child" server, and the data in the child server is newer than the
data in the master server.
I spent a bit of time setting up some replication logging, this was
useful only in the fact that it didn't show any replication activity on
the table in question.
My collegue and I are setting up a reproducible example now, I will post
here when it is complete.
thanks for the reply,
matt tagliaferri
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Returns 0 rows but which part of the where clause caused it.

I currently have a piece of SQL that will return a row if the input is
valid for the process. My application is a warehouse system. I need to
verify that the thing that is picked for the order can really be used
for that order. It is the correct product, it is in the correct status,
in the correct warehouse, ... Well up until now used one SQL statement
to achieve this. The row was returned or not. If not then the thing
being picked could not be used. Now my boss wants me to return why.
Essentially i will have to get the data and process this in code. Does
anyone have a SQL solution to this? Any tricks anyone uses to determine
which part(s) of the where clause (or join) caused the exclusion?
danWhat if more than 1 condition is FALSE?
There's lots of ways you could implement this, but sooner or later you'll
have to test each condition individually to find the cause.
You could test them up front and not run the query if an "error" condition
is found, or you could run the query and only test the conditions if
@.@.ROWCOUNT = 0.
You could indicate the reason by using RAISERROR, or maintain a table of
error codes and return them in the query, or you could ...
"Dan Holmes" wrote:

> I currently have a piece of SQL that will return a row if the input is
> valid for the process. My application is a warehouse system. I need to
> verify that the thing that is picked for the order can really be used
> for that order. It is the correct product, it is in the correct status,
> in the correct warehouse, ... Well up until now used one SQL statement
> to achieve this. The row was returned or not. If not then the thing
> being picked could not be used. Now my boss wants me to return why.
> Essentially i will have to get the data and process this in code. Does
> anyone have a SQL solution to this? Any tricks anyone uses to determine
> which part(s) of the where clause (or join) caused the exclusion?
> dan
>

returning unique data

I have a table with 3 columns: number, name, rowid (and identity column). I
want to return the name of the first row (first meaning lowest rowid) when
there are more than one row with the same number. Given the table looks
like this:
number name rowid
102 bob 1
102 allen 2
104 carl 3
104 mike 4
I want only bob and carl to be returned.
Can anyone point me in the right direction?
Tx for any help.
Bernie YaegerYou're looking for a correlated subquery.
CREATE TABLE #foo
(
rowid INT IDENTITY(1,1),
[name] VARCHAR(12),
[number] INT
)
SET NOCOUNT ON
INSERT #foo([name], [number]) SELECT 'bob', 102
INSERT #foo([name], [number]) SELECT 'allen', 102
INSERT #foo([name], [number]) SELECT 'carl', 104
INSERT #foo([name], [number]) SELECT 'mike', 104
SELECT [name],[number],rowid
FROM #foo f1
WHERE rowid = (SELECT MIN(rowid)
FROM #foo f2
WHERE f2.[number] = f1.[number])
ORDER BY [number]
DROP TABLE #foo
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>I have a table with 3 columns: number, name, rowid (and identity column).
>I want to return the name of the first row (first meaning lowest rowid)
>when there are more than one row with the same number. Given the table
>looks like this:
> number name rowid
> 102 bob 1
> 102 allen 2
> 104 carl 3
> 104 mike 4
> I want only bob and carl to be returned.
> Can anyone point me in the right direction?
> Tx for any help.
> Bernie Yaeger
>|||Bernie
CREATE TABLE #Test
(
[ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
number INT NOT NULL,
[name]CHAR(1)NOT NULL,
rowid INT NOT NULL
)
INSERT INTO #Test(number,[name],rowid) VALUES (102,'A',2)
INSERT INTO #Test (number,[name],rowid)VALUES (102,'B',1)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'C',1)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'D',2)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'A',3)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'B',4)
INSERT INTO #Test(number,[name],rowid) VALUES (102,'T',0)
INSERT INTO #Test(number,[name],rowid) VALUES (104,'a',2)
SELECT [name]FROM #Test
WHERE rowid=(SELECT MIN(rowid) FROM #Test T WHERE T.number=#Test.number)
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>I have a table with 3 columns: number, name, rowid (and identity column).
>I want to return the name of the first row (first meaning lowest rowid)
>when there are more than one row with the same number. Given the table
>looks like this:
> number name rowid
> 102 bob 1
> 102 allen 2
> 104 carl 3
> 104 mike 4
> I want only bob and carl to be returned.
> Can anyone point me in the right direction?
> Tx for any help.
> Bernie Yaeger
>|||Hi Uri,
Tx for your help Uri.
Bernie
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eLVoxKmkFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Bernie
> CREATE TABLE #Test
> (
> [ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
> number INT NOT NULL,
> [name]CHAR(1)NOT NULL,
> rowid INT NOT NULL
> )
> INSERT INTO #Test(number,[name],rowid) VALUES (102,'A',2)
> INSERT INTO #Test (number,[name],rowid)VALUES (102,'B',1)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'C',1)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'D',2)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'A',3)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'B',4)
> INSERT INTO #Test(number,[name],rowid) VALUES (102,'T',0)
> INSERT INTO #Test(number,[name],rowid) VALUES (104,'a',2)
>
> SELECT [name]FROM #Test
> WHERE rowid=(SELECT MIN(rowid) FROM #Test T WHERE T.number=#Test.number)
>
>
> "Bernie Yaeger" <berniey@.optonline.net> wrote in message
> news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>|||Hi Aaron,
Tx so much for your help.
Bernie
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ORACDEmkFHA.708@.TK2MSFTNGP10.phx.gbl...
> You're looking for a correlated subquery.
>
> CREATE TABLE #foo
> (
> rowid INT IDENTITY(1,1),
> [name] VARCHAR(12),
> [number] INT
> )
> SET NOCOUNT ON
> INSERT #foo([name], [number]) SELECT 'bob', 102
> INSERT #foo([name], [number]) SELECT 'allen', 102
> INSERT #foo([name], [number]) SELECT 'carl', 104
> INSERT #foo([name], [number]) SELECT 'mike', 104
> SELECT [name],[number],rowid
> FROM #foo f1
> WHERE rowid = (SELECT MIN(rowid)
> FROM #foo f2
> WHERE f2.[number] = f1.[number])
> ORDER BY [number]
> DROP TABLE #foo
>
>
> "Bernie Yaeger" <berniey@.optonline.net> wrote in message
> news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>

Returning top 2 results from each different data in a row and inserting into new table

Hi All,

I'd much appreciate any help with the following problem. I need a statement, cursor, anything....

I have a table in MS SQL Server containing the following (simplified version of course :)):

PID DATE OTHER INFO
aaa 31/05/2005 blah blah blah
aaa 06/06/2004 moo moo moo
aaa 17/05/2005 baa baa baa baa
bbb 31/04/2004 niegh neigh neigh
bbb 30/04/2004 bleet bleet
ccc 01/05/2005 oink oink
ccc 03/05/2005 cluck cluck
ccc 02/05/2005 meep meep
...etc etc etc

My challenge:

For each unique PID, sorted by date order descending (NOW first ... END DATE bottom), I need to return the top 2 results for each PID and insert them into a NEW table.

For example, using my simplified data above, the results I would exepect in my NEW tablet would be:

PID DATE OTHER INFO
aaa 31/05/2005 blah blah blah
aaa 17/05/2005 baa baa baa baa
bbb 31/04/2004 niegh neigh neigh
bbb 30/04/2004 bleet bleet
ccc 03/05/2005 cluck cluck
ccc 02/05/2005 meep meep

Note: It has returned the two most recent results for each unique PID.

I'm thinking it may need a cursor of some kind, but I just can't get to grips with what needs to be done, lord knows I've tried for the last two days.

Any help sincerely appeciated!

Rob

Hi
I'll try to help you using a cursor but there is a solution without it I don have it now

create table #result(PID char(3) ,Date datetime , otherinfo varchar(50))
declare @.id char(3)
declare c cursor for
select distinct PID from tblInfo
open c
fetch next from c into @.id
while @.@.fetch_Status = 0
begin
insert into #result(pid,date,otherinfo)
select top 2 pid , Date,otherinfo from tblInfo
Where PID=@.id
order by Date desc
fetch next from c into @.id
end
close c
deallocate c
select * from #result

I assumed that your SQL server is not case-sensetive and your table id tblInfo
|||Perfect - thank you so much!|||

Hi Rob,

there is a solution to solve your problem with a view / select statement, too.

select
top2keys.PID,
top2rows.DATE,
top2rows.OTHER_INFO
from
(
select distinct
PID,
(select top 1 top1.DATE from datatable top1 where base.PID = top1.PID order by top1.DATE desc) as top1date,
(select top 1 top2.DATE from (select top 2 tophelp2.DATE from datatable tophelp2 where base.PID = tophelp2.PID order by tophelp2.DATE desc) top2 order by top2.DATE) as top2date
from
datatable base
) top2keys
left join datatable top2rows
on top2keys.PID = top2rows.PID and (top2keys.top1date = top2rows.DATE or top2keys.top2date = top2rows.DATE)
order by
top2keys.PID,
top2rows.DATE
If you have your PID values in a separate table and pid is a unique key, you can avoid the "select distinct ..." with a "select ..." and "datatable" with the table name where PID is unique to achieve better performance. But this wya should outperform the procedure approach more then 10 times, depending on records in the data table.

Regards,
Tom

|||If you are using SQL Server 2005 you may try the following

WITH T1 as (select RANK() OVER(PARTITION BY PID ORDER BY Date ) AS RNK,* from T)
SELECT PID,Date,otherinfo FROM T1 where RNK<=2

returning the primary key of the last rows updated

I would like to return the the Primary Key of the row altered from this query - what changes do I need to make?
UPDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass;

Thanks in advance,
Is the primary key an IDENTITY value? If so then you could do this:

UPDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass;SELECT @.myPrimaryKey = SCOPE_IDENTITY()

|||I am trying to kill 2 birds with uno piedro - I want thisreturn the Primary key ( which is the identity ) when someone islogging in and also update the last time they last logged in to thepresent.
My first quess was to do:

PDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass;SELECT SCOPE_IDENTITY()
But this keeps returning (NULL) - even though the row has been updated.
Thanks in advance

|||Can you post more of your code?
|||

Actually - this is what I was putting in my query analyzer-
UPDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass;
its ok -
I will just work with using this :
UPDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass; SELECT MemberID WHERE UserName=@.user AND Password=@.pass;
I will just dig around some more.

|||Oh gosh, I just realized that you are doing an UPDATE, not an INSERT! I don't know where my head was; I'm sorry.
In that case I would SELECT the MemeberID to be UPDATEd first into avariable, then perform the UPDATE with the WHERE condition being theMemberID you determined.

|||How about using an output parameter and assigning it SCOPE_IDENTITY or @.@.IDENTITY?
I'm not sure if @.@.IDENTITY only gets set to the ID of the latest INSERT or also UPDATE. It would be easy to try of course.

Returning row value before deletion

I want to return a value from a row before i delete it. Is this
possible to do in one operation something like...
SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
RETURN t.columnValCJ wrote:

> I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
Not in one query but why don't you make a stored procedure that returns
the value(s) and deletes the rows?
Kind regards,
Stijn Verrept.|||Thanks Stijn-
This is running inside a stored proc. I have an output param that
needs to return a value from the record being deleted. I didn't want
to run both SELECT and DELETE against the table if there was a way of
capturing the record from the DELETE.
Regards,
-CJ|||in 2005 check out the OUTPUT clause.
William Stacey [MVP]
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133142606.786674.84690@.f14g2000cwb.googlegroups.com...
>I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
>|||Nope. Not in SQL 2000. Be sure to use WITH(UPDLOCK) or WITH(XLOCK) on the
SELECT statement that precedes the DELETE.
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133143713.634416.313700@.o13g2000cwo.googlegroups.com...
> Thanks Stijn-
> This is running inside a stored proc. I have an output param that
> needs to return a value from the record being deleted. I didn't want
> to run both SELECT and DELETE against the table if there was a way of
> capturing the record from the DELETE.
> Regards,
> -CJ
>|||Many thanks!
So UPDLOCK on the SELECT will work if the SELECT and DELETE are wrapped
in a transaction and the DELETE cascades over sevreal tables?|||Brian
>Be sure to use WITH(UPDLOCK)
I think we should add WITH (UPDLOCK,HOLDLOCK) hint , shouldn't we?
I remember our last discussion sometime ago where you explained why we need
to use HOLDLOCK hint in addition to UPDLOCK.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uTj93P88FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Nope. Not in SQL 2000. Be sure to use WITH(UPDLOCK) or WITH(XLOCK) on
> the SELECT statement that precedes the DELETE.
> "CJ" <Charles.Deisler@.gmail.com> wrote in message
> news:1133143713.634416.313700@.o13g2000cwo.googlegroups.com...
>|||Not prior to a DELETE. UPDLOCK is sufficient. UPDLOCK, HOLDLOCK is
necessary if there is a possibility of an INSERT or UPDATE on another
connection that could cause a PRIMARY KEY or UNIQUE constraint violation by
changing the value to one that is about to be INSERTed or UPDATEed.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eTAKC698FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Brian
> I think we should add WITH (UPDLOCK,HOLDLOCK) hint , shouldn't we?
> I remember our last discussion sometime ago where you explained why we
> need to use HOLDLOCK hint in addition to UPDLOCK.
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:uTj93P88FHA.2364@.TK2MSFTNGP12.phx.gbl...
>|||If you have cascading referential actions, then you have to obtain update
locks all affected rows in all affected tables. I don't like cascading
referential actions. I think they are a tool for the lazy or the
incompetent and cause more problems then they're worth. They can cause
locking order confusion and can increase the probability of deadlocks. It
is not possible to determine with certainty which objects will be locked in
which order if cascading referential actions are active. It's better to
write the code manually in the stored procedure that performs the DELETE
(this is the preferred method) or to use a trigger (preferrably an INSTEAD
OF trigger) because then you have complete control over the order in which
locks are obtained, thus eliminating the main cause of deadlocks that can be
avoided. There still exists the possibility of deadlocks, due to threading
or a poorly generated execution plan, but deadlocks caused by accessing
objects in the wrong order can be completely eliminated.
You are correct that the SELECT(s) and the DELETE must be wrapped in a
transaction. In addition, if you don't use WITH(UPDLOCK), two separate
transactions can obtain and hold shared locks on the same row such that
neither can obtain an exclusive lock in order to perform the DELETE, thus
causing a deadlock.
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133153077.282668.76100@.g14g2000cwa.googlegroups.com...
> Many thanks!
> So UPDLOCK on the SELECT will work if the SELECT and DELETE are wrapped
> in a transaction and the DELETE cascades over sevreal tables?
>|||Would the Ouput clause not fit here?USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO
William Stacey [MVP]
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133142606.786674.84690@.f14g2000cwb.googlegroups.com...
>I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
>

returning row from function

Is it possible to do something like this:
First create function(function returns only one row):
CREATE FUNCTION dbo.s_function(@.fID int)
RETURNS @.tbl TABLE (pName varchar(20),quantity decimal(15,5)) AS
BEGIN
INSERT INTO @.tbl
SELECT TOP 1 * from...
WHERE tID=@.fID
RETURN
END
Then use function like this:
select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
If I would need only one value, than there won't be a problem:
I would create a function which returns only one value and use it:
select *,retValue=dbo.s_function(t.tID)
FROM testTable t
Regards,Ssimon,

> Is it possible to do something like this:
This can not be done in SQL Server 2000. In version 2005, you can use "CROSS
APPLY" operator.
Using CROSS APPLY in SQL Server 2005
http://www.sqlteam.com/item.asp?ItemID=21502
AMB
"simon" wrote:

> Is it possible to do something like this:
> First create function(function returns only one row):
> CREATE FUNCTION dbo.s_function(@.fID int)
> RETURNS @.tbl TABLE (pName varchar(20),quantity decimal(15,5)) AS
> BEGIN
> INSERT INTO @.tbl
> SELECT TOP 1 * from...
> WHERE tID=@.fID
> RETURN
> END
>
> Then use function like this:
> select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
> If I would need only one value, than there won't be a problem:
> I would create a function which returns only one value and use it:
> select *,retValue=dbo.s_function(t.tID)
> FROM testTable t
> Regards,S
>
>|||
>select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
Don't believe you can do this in SQL Server 2000. For SQL Server 2005
you can use CROSS APPLY|||simon wrote:
> Is it possible to do something like this:
> First create function(function returns only one row):
> CREATE FUNCTION dbo.s_function(@.fID int)
> RETURNS @.tbl TABLE (pName varchar(20),quantity decimal(15,5)) AS
> BEGIN
> INSERT INTO @.tbl
> SELECT TOP 1 * from...
> WHERE tID=@.fID
> RETURN
> END
>
> Then use function like this:
> select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
> If I would need only one value, than there won't be a problem:
> I would create a function which returns only one value and use it:
> select *,retValue=dbo.s_function(t.tID)
> FROM testTable t
> Regards,S
In SQL Server 2005 you could use the OUTER APPLY operator. In your case
however that seems to be unnecessary. For instance you could perhaps
use a derived table instead:
SELECT T.*, G.*
FROM testTable AS T
JOIN
(SELECT MIN(key_col) AS key_col
FROM /* unspecified */
GROUP BY tID) AS F
ON F.tID = T.tID
JOIN /* unspecified */ AS G
ON F.key_col = G.key_col ;
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
--|||Hi David,
I had very simple question, so I didn't post all code with samples,
otherwise, I agree with you.
I can use derived tables - thank you, but it doesn't work fast enough.
The function is couple of times faster than join with derived table, when
you have a lot of data.
So I usualy use functions, which filters all rows only to those I need (on
the other hand derived table first insert all data into some internal temp
table and than filters it in join - I think)
The problem which I have is, that I can't include in resulting row more than
one data returned from function.
So I use function for each data, when I would like to get more than one
column from function:
SELECT t.*, columnF1=dbo.function1(t.ID),columnF2=dbo.function2(t.ID) from
table t ...
I wonder if it,s possible to include in result set more than one column
returned from function?
my function would return for example 3 data, so 3 columns - only one row if
we speak in table world.
Something like this would work if I had table(with one row) instead of
function:
select t.*,f.* FROM testTable t INNER JOIN testTable1 f ON 1=1
But if I have function,which returns table with one row, won't work.
select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
So, I guess that it's not possible.
Regards,Simon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145282430.488437.92570@.g10g2000cwb.googlegroups.com...
> simon wrote:
> In SQL Server 2005 you could use the OUTER APPLY operator. In your case
> however that seems to be unnecessary. For instance you could perhaps
> use a derived table instead:
> SELECT T.*, G.*
> FROM testTable AS T
> JOIN
> (SELECT MIN(key_col) AS key_col
> FROM /* unspecified */
> GROUP BY tID) AS F
> ON F.tID = T.tID
> JOIN /* unspecified */ AS G
> ON F.key_col = G.key_col ;
> --
> 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
> --
>|||simonZ wrote:
> I can use derived tables - thank you, but it doesn't work fast enough.
> The function is couple of times faster than join with derived table, when
> you have a lot of data.
How did you test that? Do you have a working solution using a function?
I'm actually surprised if a multi-statement table-valued function could
improve on a dervied table query for what you posted.

> SELECT t.*, columnF1=dbo.function1(t.ID),columnF2=dbo.function2(t.ID) from
> table t ...
> I wonder if it,s possible to include in result set more than one column
> returned from function?
Already answered. Not possible in 2000. CROSS APPLY / OUTER APPLY in
2005.
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
--|||I have table with a lot of data. When I need one data, for example, a volume
on some location in warehouse, I have 2 possibilities:
first option with derived table:
SELECT t.*,T1.volume, FROM table t1
INNER JOIN (SELECT wID,volume=sum(w.volume) from .......) as T1 ON
t.wID=T1.wID
WHERE ....
second option with function:
SELECT t.*,volume=dbo.functionV(wID) FROM table t1
and function:
set @.returnValue=(SELECT sum(volume) from ...
WHERE wID=@.wID)
Here, the function filters all rows to the single wID and calculate volume
only for this wID.
In derived table, I get all volumes for all wIDs, and than it's filtered to
my wID with join condition: ON t.wID=T1.wID
That is the reason, that function is much faster than derived table - I
guess.
My examples are more complicated, but it's the same idea. I have many cases,
where I create a function instead of derived table just because of
performance.
Even when I need more than one data (volume, mass,...), which can all be
calculated with single query in derived table, it's faster to create a
function for each column withs it's own query.
Am I doing something wrong?
Another question?
A while ago I posted one example but nobody answer me.
Can you look at it?
Just run the code and you will see, what is the point, otherwise I can
explain to you in more details.
The example works correct but it uses 2 cursors, which has bad performance.
Can you do the query which will return the same results as this example but
without 2 cursors?
http://groups.google.com/group/micr...2252c36ea4152d5
This example is not real one, it's simplified, but it serves as problem
review.
Thanks,
Simon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145358771.297709.215400@.t31g2000cwb.googlegroups.com...
> simonZ wrote:
> How did you test that? Do you have a working solution using a function?
> I'm actually surprised if a multi-statement table-valued function could
> improve on a dervied table query for what you posted.
>
> Already answered. Not possible in 2000. CROSS APPLY / OUTER APPLY in
> 2005.
> --
> 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
> --
>

Returning only the Header Row instead of all the relevant rows.

Hi All:

My below sub in application is returning only the Header Row instead of the relevant rows I guess therez problem in my "str" Syntax. However I fail to understand where exactly is it faultering.

PrivateSub btnSearch_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnSearch.Click

Try

con.Open()

Dim empIDAsString = txtEmpID.Text

Dim FNameAsString = txtFName.Text

Dim LNameAsString = txtLName.Text

Dim strAsString = "Select Emp_ID, Emp_FName,Emp_LName,Emp_Address1,Emp_HNo,Emp_MNo from Emp_Details where Emp_ID='" & empID & " ' Or Emp_LName='" & LName & " ' Or Emp_FName=' " & FName & "'"""Dim da1AsNew SqlDataAdapter(str, con)

da1.Fill(ds, "Emp_Details")

dgEmp.DataSource = ds

dgEmp.DataMember = "Emp_Details"

dgEmp.DataBind()

Finally

con.Close()

EndTry

EndSub

Thanks in Advance for your quick help.

Regards,

Brandy

It looks like you might have one too many extra spaces in your statement. Try this instead:

Dim strAs String =String.Format("SELECT Emp_ID, Emp_FName, Emp_LName, Emp_Address1, Emp_HNo, Emp_MNo FROM Emp_Details WHERE Emp_ID ='{0}' OR Emp_LName = '{1}' OR Emp_FName = '{2}'", empID, LName, FName)

|||

This works... Appreciate your kind help.

Returning only one row from joined table

Hello
Given two tables in a one-to-many relationship, how do I write a query to
return only one row from the table on the "many" side of the relationship?
e.g., in the "pubs" sample database, the following query returns all rows
from the Authors table and all related rows from the TitleAuthor table:
SELECT
A.au_id,
A.au_lname,
A.au_fname,
TA.title_id,
TA.royaltyper
FROM
Authors A
LEFT OUTER JOIN TitleAuthor TA ON TA.au_id = A.au_id
ORDER BY
A.au_id,
TA.title_id
In my case, I want all rows from the Authors table and for related rows in
the TitleAuthor table, only the first row (ordered by title_id).
Thanks!!!
AlexSELECT
A.au_id,
A.au_lname,
A.au_fname,
TA.title_id,
TA.royaltyper
FROM Authors A
LEFT JOIN
(SELECT au_id, MIN(title_id) AS title_id
FROM TitleAuthor
GROUP BY au_id) AS T
ON T.au_id = A.au_id
LEFT JOIN
TitleAuthor TA
ON TA.au_id = T.au_id
AND TA.title_id = T.title_id
ORDER BY
A.au_id,
TA.title_id ;
David Portas
SQL Server MVP
--|||Select TOP1 attribute_list
From table
etc...
Alex wrote:
> Hello
> Given two tables in a one-to-many relationship, how do I write a query to
> return only one row from the table on the "many" side of the relationship?
> e.g., in the "pubs" sample database, the following query returns all rows
> from the Authors table and all related rows from the TitleAuthor table:
> SELECT
> A.au_id,
> A.au_lname,
> A.au_fname,
> TA.title_id,
> TA.royaltyper
> FROM
> Authors A
> LEFT OUTER JOIN TitleAuthor TA ON TA.au_id = A.au_id
> ORDER BY
> A.au_id,
> TA.title_id
> In my case, I want all rows from the Authors table and for related rows in
> the TitleAuthor table, only the first row (ordered by title_id).
> Thanks!!!
> Alex
>

Returning last row only

I have a web application that is data driven from a SQL 2005 database. The application needs to prompt the user to enter some information that will be logged into a SQL table.

It should always be the last row in the table that is being worked on at any one time. Over a period the user will need to enter various fields. Once the data is entered into a field they will not have access to amend it.

Therefore I need to be able to SELECT the last row of a table and present the data to the user with the 'next field' to be edited.

As I'd like to do this as a stored procedure which can be called from an ASP page I wonder if anyoen might be able to help me with some T-SQL code that might achieve it?

Regards

Clive

I will assume that the table has an integer identity primary key. The SQL then becomes very simple:

SELECT A,B,C FROM TABLENAME WHERE ID = (SELECT MAX(Id) FROM TABLENAME)

|||

Yes the ID field is exactly as you say. That works perfectly - many thanks

Clive

|||

Another way to do is:

select top 1 visitid from medications order by visitid desc

It will return the last row only.