Print list of tables with Identity row set to NOT FOR REPLICATION

Does anyone know if there's an SQL command i can run that will list
the tables in a database that have an identity column set to NOT FOR
REPLICATION?

Many thanks

Dan Williams."Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
news:2eac5d02.0406030812.2651f9e6@.posting.google.c om...
> Does anyone know if there's an SQL command i can run that will list
> the tables in a database that have an identity column set to NOT FOR
> REPLICATION?
> Many thanks
> Dan Williams.

select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdNotForRepl') =
1

Simon|||Cool. Thanks for that, it worked a treat.

Dan

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<40bf6492$1_1@.news.bluewin.ch>...
> "Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
> news:2eac5d02.0406030812.2651f9e6@.posting.google.c om...
> > Does anyone know if there's an SQL command i can run that will list
> > the tables in a database that have an identity column set to NOT FOR
> > REPLICATION?
> > Many thanks
> > Dan Williams.
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdNotForRepl') =
> 1
> Simon

Print Headers issues in SSRS 2005

I have a report, with 220 records, each row heigth varies. I have set the option to repeat header on every page on. However, when I print the report, the header prints on some pages, and some it does not. For eg, on the 5 page reprot, it prints the header on pg 1, 2 and 3. Then on pg 4 it does not, then again page 5 it prints the header.

Any advise.

The layout of the report has 15 fields. I have narrowed down the issue, to 1 column eg COMMENTS.. This is a comment field(has commnets and description data, ) size of col =2 in. This field is huge, ie has lot of data..So When I Hide this field, then the Report headers prints just fine.

The minute I UNHIDE This field, the print header issues comes back , ie on some pages the header is printed , on some, where the comments are huge, the header does not print.

Pl advise.

Primary key constraint error

We have an SQL Server database that has one damaged data row. The row can't
been updated and if it has been removed and added again with the same primary
key, the row won't still work.
When we try to modify the row, we get on error:
'Another user har modified the contents of this table or view; the database
row you are modifying no longer exists in the database.'
We have installed the SQL Server SP4.
Is there a way fix the table?
BR
Johanna Turku
Hi
Do you modify it by using EM?
"Sebitti" <sebittionline@.companysebitti.fi> wrote in message
news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
> We have an SQL Server database that has one damaged data row. The row
> can't
> been updated and if it has been removed and added again with the same
> primary
> key, the row won't still work.
> When we try to modify the row, we get on error:
> 'Another user har modified the contents of this table or view; the
> database
> row you are modifying no longer exists in the database.'
> We have installed the SQL Server SP4.
> Is there a way fix the table?
> BR
> Johanna Turku
|||Yes, I try to it by using EM, query analyzer and ado. None of them worked.
"Uri Dimant" wrote:

> Hi
> Do you modify it by using EM?
> "Sebitti" <sebittionline@.companysebitti.fi> wrote in message
> news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
>
>

Primary key constraint error

We have an SQL Server database that has one damaged data row. The row can't
been updated and if it has been removed and added again with the same primar
y
key, the row won't still work.
When we try to modify the row, we get on error:
'Another user har modified the contents of this table or view; the database
row you are modifying no longer exists in the database.'
We have installed the SQL Server SP4.
Is there a way fix the table?
BR
Johanna TurkuHi
Do you modify it by using EM?
"Sebitti" <sebittionline@.companysebitti.fi> wrote in message
news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
> We have an SQL Server database that has one damaged data row. The row
> can't
> been updated and if it has been removed and added again with the same
> primary
> key, the row won't still work.
> When we try to modify the row, we get on error:
> 'Another user har modified the contents of this table or view; the
> database
> row you are modifying no longer exists in the database.'
> We have installed the SQL Server SP4.
> Is there a way fix the table?
> BR
> Johanna Turku|||Yes, I try to it by using EM, query analyzer and ado. None of them worked.
"Uri Dimant" wrote:

> Hi
> Do you modify it by using EM?
> "Sebitti" <sebittionline@.companysebitti.fi> wrote in message
> news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
>
>|||I have got same error.
Have you guys been able to fix.

Primary key constraint error

We have an SQL Server database that has one damaged data row. The row can't
been updated and if it has been removed and added again with the same primary
key, the row won't still work.
When we try to modify the row, we get on error:
'Another user har modified the contents of this table or view; the database
row you are modifying no longer exists in the database.'
We have installed the SQL Server SP4.
Is there a way fix the table?
BR
Johanna TurkuHi
Do you modify it by using EM?
"Sebitti" <sebittionline@.companysebitti.fi> wrote in message
news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
> We have an SQL Server database that has one damaged data row. The row
> can't
> been updated and if it has been removed and added again with the same
> primary
> key, the row won't still work.
> When we try to modify the row, we get on error:
> 'Another user har modified the contents of this table or view; the
> database
> row you are modifying no longer exists in the database.'
> We have installed the SQL Server SP4.
> Is there a way fix the table?
> BR
> Johanna Turku|||Yes, I try to it by using EM, query analyzer and ado. None of them worked.
"Uri Dimant" wrote:
> Hi
> Do you modify it by using EM?
> "Sebitti" <sebittionline@.companysebitti.fi> wrote in message
> news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
> > We have an SQL Server database that has one damaged data row. The row
> > can't
> > been updated and if it has been removed and added again with the same
> > primary
> > key, the row won't still work.
> > When we try to modify the row, we get on error:
> > 'Another user har modified the contents of this table or view; the
> > database
> > row you are modifying no longer exists in the database.'
> >
> > We have installed the SQL Server SP4.
> >
> > Is there a way fix the table?
> >
> > BR
> > Johanna Turku
>
>|||I have got same error.
Have you guys been able to fix.

previous value in trigger

Hi,
Can I get previous and current values of row in trigger for update
operation.
Regards,
Shah Adarsh.The previous value is in the deleted table and the current value in the
inserted table.
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140679727.735390.38640@.i39g2000cwa.googlegroups.com...
> Hi,
> Can I get previous and current values of row in trigger for update
> operation.
>
> Regards,
> Shah Adarsh.
>|||The data is stored in the virtual INSERTED table:
Operation --> Virtual Tables
INSERT --> INSERTED
DELETE --> DELETED
UPDATE --> INSERTED,DELETED
HTH, jens Suessmeyer.|||Thanks Quinn. It solved my problem.|||Thanks Jens. It solved my problem.

Previous row calculations using SQL

Hi,
I need help in writing a SQL, where in a calculated column depends on previo
us's row column, for example,
Table: test
Time Pkts Seq_no
--
10:00 20 25
10:01 15 40
10:02 17 57
10:03 10 60
10:04 12 72
Query Result: The output of the query should be
Time Pkts Seq_no drops
---
10:00 20 25 NULL
10:01 15 40 0
10:02 17 57 0
10:03 10 60 7 (=57+10-60)
10:04 12 72 0
Is this kind of calculation possible using SQL. Any help in this regards wil
l be highly appreciated.
Thanks
-MIf is indeed possible to do this in SQL, but you cannot do this with a
calculated column. The rules of calculated columns are that the calculation
is restricted to the current row only.
To perform this calculated you will need to create a column to store the
data. Then use an "instead of" trigger to perform the calculation, and save
the result into the correct row.
There are a couple of warnings with this method.
1. Perform as few inserts and updates as possible. Inserting 1000 rows, one
at a time will be extremely expensive. It is better to perform a single
insert of 1000 rows instead - use temporary tables or another buffering
technique to batch process.
2. You need to make sure that all the nuances of the code are taken into
account. It is vital that the correct calulation is always performed. This
is not trivial, but is possible. I know because I've done it before - but
unfortunatly cannot share the code as it's not my property.
Regards
Colin Dawson
www.cjdawson.com
"mmonis" <mmonis.28cv5s@.mail.codecomments.com> wrote in message
news:mmonis.28cv5s@.mail.codecomments.com...
> Hi,
> I need help in writing a SQL, where in a calculated column depends on
> previous's row column, for example,
> Table: test
> Time Pkts Seq_no
> --
> 10:00 20 25
> 10:01 15 40
> 10:02 17 57
> 10:03 10 60
> 10:04 12 72
>
> Query Result: The output of the query should be
> Time Pkts Seq_no drops
> ---
> 10:00 20 25 NULL
> 10:01 15 40 0
> 10:02 17 57 0
> 10:03 10 60 7 (=57+10-60)
> 10:04 12 72 0
>
> Is this kind of calculation possible using SQL. Any help in this
> regards will be highly appreciated.
> Thanks
> -M
>
> --
> mmonis
> ---
> Posted via http://www.codecomments.com
> ---
>|||Get a copy of SQL-2005 and look at SUM() OVER() function that is new to
Standard SQL. It can get complicated but it is used for running
totals, etc.|||Try this, note that this relies on Time being unique.
If Time isn't unique, you'll need some additional
rules to determine the "previous" row.
SELECT t1.Time,
t1.Pkts,
t1.Seq_no,
t2.Seq_no + t1.Pkts - t1.Seq_no AS drops
FROM test t1
LEFT OUTER JOIN test t2 ON t2.Time<t1.Time
AND t2.Time=(SELECT MAX(t3.Time)
FROM @.test t3
WHERE t3.Time<t1.Time)
ORDER BY t1.Time|||Have you considered doing this at select time instead of storing the value?
You certainly need to take performance into consideration, but it could be
done this way. Note that like the solution posted by MarkC600, this assumes
that time is unique.
Create view Test_vw as
Select a.time
, a.pkts
, (
select sum(b.pkts)
from test b
where b.time = a.time
) as seq_no
from test a
OR (see disclaimer below)
Create view Test_vw as
Select a.time
, a.pkts
, sum(a.pkts) over(order by a.time)
from test a
group by a.time
, a.pkts
Note that the second query syntax is likely wrong, since I don't have a
system to test it on, and put it together based on what I could quickly
gather from the web. I think you can use the functionality in SQL Server
2005, but not on 2000. You can see how it is simpler than the first query,
and the benefit (from a code maintenance perspective) you can get from
learning it if you are on 2005.
"mmonis" <mmonis.28cv5s@.mail.codecomments.com> wrote in message
news:mmonis.28cv5s@.mail.codecomments.com...
> Hi,
> I need help in writing a SQL, where in a calculated column depends on
> previous's row column, for example,
> Table: test
> Time Pkts Seq_no
> --
> 10:00 20 25
> 10:01 15 40
> 10:02 17 57
> 10:03 10 60
> 10:04 12 72
>
> Query Result: The output of the query should be
> Time Pkts Seq_no drops
> ---
> 10:00 20 25 NULL
> 10:01 15 40 0
> 10:02 17 57 0
> 10:03 10 60 7 (=57+10-60)
> 10:04 12 72 0
>
> Is this kind of calculation possible using SQL. Any help in this
> regards will be highly appreciated.
> Thanks
> -M
>
> --
> mmonis
> ---
> Posted via http://www.codecomments.com
> ---
>|||Sorry... I missed the entire second half of your post somehow... I thought
you were asking how to get Seq_no.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23pHFGXBgGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Have you considered doing this at select time instead of storing the
value?
> You certainly need to take performance into consideration, but it could be
> done this way. Note that like the solution posted by MarkC600, this
assumes
> that time is unique.
> Create view Test_vw as
> Select a.time
> , a.pkts
> , (
> select sum(b.pkts)
> from test b
> where b.time = a.time
> ) as seq_no
> from test a
> OR (see disclaimer below)
> Create view Test_vw as
> Select a.time
> , a.pkts
> , sum(a.pkts) over(order by a.time)
> from test a
> group by a.time
> , a.pkts
> Note that the second query syntax is likely wrong, since I don't have a
> system to test it on, and put it together based on what I could quickly
> gather from the web. I think you can use the functionality in SQL Server
> 2005, but not on 2000. You can see how it is simpler than the first
query,
> and the benefit (from a code maintenance perspective) you can get from
> learning it if you are on 2005.
> "mmonis" <mmonis.28cv5s@.mail.codecomments.com> wrote in message
> news:mmonis.28cv5s@.mail.codecomments.com...
>

Previous Row Calculations (sql server 2000)

if anyone know a way that you can look up a value from the previous row in a View to do a calculation on (in sql server 2000)
for example:
expr1=PreviousRow.Expr1/30 + expr2 - expr3 =100

so the next row is

expr1=100/30 + expr2 - expr3 =300

so the third row is

expr1=300/30 + expr2 - expr3 =100
or tell me if it not possible please

and special thanks to Umachandar Jayachandran - MS for help.

Hi,

Im not quite sure if this is possible in a view. But you can do this in a stored proc. You can use a cursor and manually traverse your records...

cheers,

Paul June A. Domag

|||Thank you my frind for your help but I need give me some examples of the crusors to understand that...

if you can.....
|||

Hi,

Here's a sample in transact sql:

-- Declare the variables to store the values returned by FETCH.
DECLARE @.au_lname varchar(40), @.au_fname varchar(20)

DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE 'B%'
ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM authors_cursor
INTO @.au_lname, @.au_fname

-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- here is where you would do your calculations

-- Concatenate and display the current values in the variables.
PRINT 'Author: ' + @.au_fname + ' ' + @.au_lname

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
INTO @.au_lname, @.au_fname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

BTW, this would be placed inside a stored proc...

cheers,

Paul June A. Domag

|||

Bear with me here, as the info is worth it!!!

do you really need a table for this?

Ahhh, memories of my analysis classes!!!

This is what is called a Discrete Dynamic Equation (also called a Difference equation as oppposed to a differential equation) ; specifically, a Discrete first-order Affine Dynamical System

The form is:

A_n = R A_n-1 + B

The general solution for which is:
A_k=CR^k + B/(1-R)

The particular solution for n=0 is

A₀=CR⁰+ B/(1-R)=C+B/(1-R)

Solve for C

C=A₀-B(1-R)

Therefore, the particular solution for the dynamical system is:

A_k=R^k(A₀-B/(1-R))+B/(1-R)
in sql:

Create function AffineDynamic(@.A0 real, @.R decimal(16,16), @.B real, @.k int) returns decimal(32,16)
as
begin
declare @.result decimal(32,16)
if @.k = 0
set @.result = @.A0
else
set @.result Power(@.R,@.k) * (@.A0 - (@.B * Power(1-@.R, -1))) + (@.B * Power(1-@.R, -1))
return @.result
end
usage:


-- a random expr1 for row 1
DECLARE @.initial real
set @.initial = 25

-- the rate: 1/30
DECLARE @.rate decimal(16,16)
set @.rate = 0.03333333

-- a random expr2
DECLARE @.expr2 real
set @.expr2 = 23

-- a random expr3
DECLARE @.expr3 real
set @.expr3 = 7

-- First row
select 0 Iter, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,0) Expr1
union
-- Second row
select 1, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,1)
union
-- Third row
select 2, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,2)
union
-- Fourth row
select 3, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,3)
union
-- Fifth row
select 4, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,4)
union
-- Sixth row
select 5, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,5)
union
-- Sixth row
select 6, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,6)


Yields the following:

Iter Expr1
0 25.0000000000000000
1 16.8333320617675780
2 16.5611095428466800
3 16.5520362854003910
4 16.5517330169677730
5 16.5517234802246090
6 16.5517234802246090

Now, note the affinity towards 16.5517234802246090. This is called a point of stability. Any iteration after 5 for A0=25, R=1/30, B= (23-7) = 16 yeilds this number.

To wit:

select 1000 Iter, dbo.AffineDynamic(25, 0.03333333, 16,1000) Expr1

Yields (as expected):

Iter Expr1
1000 16.5517234802246090

Reference:
Discrete Dynamical Modeling, Sandefur, Oxford University Press, 1993, ISBN 0-19-508438-1

|||

Another plain and practical alternative to cursors, and/or esoteric alternates that concentrate on remembering the previous values by storing them on a temporary table that is later listed as show in sample code in the "PreviousRowCalculation" SP.

create table nums(a int, b int)
insert into nums values (0,1)
insert into nums values (1,2)
insert into nums values (2,3)
insert into nums values (3,4)
insert into nums values (4,5)
go
-- drop procedure PreviousRowCalculation
create procedure PreviousRowCalculation
as
begin
set nocount on
create table #tmp (recno int identity, a int, b int, pv int)
insert into #tmp (a,b,pv)
select a,b,0 from nums order by a
declare @.t int,@.c int
set @.t = (select count(*) from #tmp)
set @.c = 1
while @.c <= @.t
begin
update #tmp
set pv = isnull((select pv from #tmp x where x.recno=@.c-1),0) -- expr1
+ (11 + b) - (10 + a) -- + expr2 - expr3
where recno=@.c
set @.c = @.c + 1
end
select * from #tmp
drop table #tmp
end
go

PreviousRowCalculation
go

|||I want to reiterate that this only works with dynamic equations of the form:
A_n = R A_n-1 + B

This is called a First-Order Homogeneous Dynamical Equation

Which is what your request is:

========================
B = expr2 - expr3
expr1_n= [(1/30) * expr1_n-1] + B
========================

This assumes that expr2 - expr3 are constant expressions and not variable values, such as columns in the table.

There are different solutions for other forms - First-Order Non-Homogenous Dynamical Equations, N-Order Homogeneous/Non-Homogenous Dynamical Equations and Systems of Dynamical Equations, just to name a few.

Google:
Logistic Equation
Markov Chains

The point I am trying to make, if there is a distinct causal relationship between values of sequential iterations of a formula, that is:

F(x)_n = G(F(x)_n-1)

There is often an dual formula H(x , k) such that

F_k(x) = H(F(x)₀ , k)

where k is the iteration you want to calculate and F(x)₀ is the intial value, thereby eliminating the need to iterate all the values.

The trick is being able to recognize the form of the system (is it solvable?) and then applying the proper dual.

Ah, the difference between a degree and a certificate!|||

or, using ed's nums table and exprnext = exprPrev +(11 +b) - (10+a)

select cast(null as int) pv, identity(int) id, * into #temp from nums

update #temp set pv = 0 where id = 1

loophere:
update #temp
set pv = calc
from #temp, ( select top 1 curr.ID, prev.pv + (11 + prev.b) - (10+prev.a) calc
from #temp curr inner join #temp prev on curr.id = prev.ID+1 where curr.pv is null ) thecalc
where #temp.ID = thecalc.ID
if @.@.ROWCOUNT <> 0 goto loophere

select * from #temp
drop table #temp

|||sweet.

Just a last note, on Blair last alternate solution is the need to evaluate the expression for the first row alone (if actually needed) since the loop does require to start in "recno 1" + 1 therefore missing its evaluation at row 1.|||the second line is the initial expr1:

update #temp set pv = 0 where id = 1

Previous Row Calculations

Hi all

I am wondering if anyone know a way that you can look up a value from the previous row to do a calculation on. I have a field that I need to subtract the same field from the previous row to validate. Is this possible in a query?

Thanks
KenzieOk, i just composed this script and i hope it could help you

WITH MyTable AS
(SELECT
*,
Num = ROW_NUMBER() OVER(ORDER BY <YourColumn>)
FROM [dbo].<YourTable>)

SELECT
tb.<YourColumn>,
tb.Num,
prev. <YourColumn>
FROM MyTable tb
LEFT JOIN
(SELECT
<YourColumn>,
Num = (ROW_NUMBER() OVER(ORDER BY <YourColumn>) -1)
FROM [dbo].<YourTable>) prev
ON tb.Num = prev.Num

Mind to change <YourColumn> and <YourTable>
let me know if it works (for me it's working)
|||Slight simplification, you can use only the CTE in the self-join like:

WITH t_seq
AS
(
select ROW_NUMBER() OVER(ORDER BY <column_list>) AS seq
from <table>
)
select ...
from t_seq AS t1
left join t_seq AS t2
on t2.seq = t1.seq -1;

previous row

Hi,
I am trying to do a query where I want to show not only the relevant results
but the previous line as well.
eg.
select *
where msg='crash'
I have a rowno in there but I am pretty new to this stuff so not real sure.
Thanks in advance
TimPerhaps you can give us some DDL and show us what you want to retrieve when
you issue am smaple command
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
news:42916000$0$4656$afc38c87@.news.optusnet.com.au...
> Hi,
> I am trying to do a query where I want to show not only the relevant
> results
> but the previous line as well.
> eg.
> select *
> where msg='crash'
> I have a rowno in there but I am pretty new to this stuff so not real
> sure.
> Thanks in advance
> Tim
>|||ok, as I say I am pretty new to this so excuse any misunderstandings
columns: date msg type detail
rowno
11/5/06 info prog running
1
11/5/06 error prog crash
2
I basically want to be able to show the rowno previous to the prog crash
So I have,
Select *
from TABLENAME
where detail ='prog crash'
but I not sure how to do the next bit to retrieve the previous row.
thanks in advance
Tim
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:elnUUV1XFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Perhaps you can give us some DDL and show us what you want to retrieve
when
> you issue am smaple command
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
> news:42916000$0$4656$afc38c87@.news.optusnet.com.au...
>|||That would be one solution for that:
Select TOP 2 * from TABLENAME
Where Rowno <=
(
Select TOP 1 *
from TABLENAME
where detail ='prog crash'
order by rowno
)
Order by row no desc
I added the order by rowno if the detailcolumn is not precise enough,
perhaps mor ethan one coumn would fit that condition, saythose with 4 and
105, should then the rows 3 AND 104 be returned, or is the detail column
that precise only to return one row at a time ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
news:42916a43$0$10304$afc38c87@.news.optusnet.com.au...
> ok, as I say I am pretty new to this so excuse any misunderstandings
> columns: date msg type detail
> rowno
> 11/5/06 info prog running
> 1
> 11/5/06 error prog crash
> 2
>
> I basically want to be able to show the rowno previous to the prog crash
> So I have,
> Select *
> from TABLENAME
> where detail ='prog crash'
> but I not sure how to do the next bit to retrieve the previous row.
> thanks in advance
> Tim
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:elnUUV1XFHA.1404@.TK2MSFTNGP09.phx.gbl...
> when
>|||yes would return 3 and 104 as well.
that query gives an error
'Only one expression can be specified in the select list when the subquery
is not introduced with EXISTS'
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ecvwdn1XFHA.1384@.TK2MSFTNGP09.phx.gbl...
> That would be one solution for that:
> Select TOP 2 * from TABLENAME
> Where Rowno <=
> (
> Select TOP 1 *
> from TABLENAME
> where detail ='prog crash'
> order by rowno
> )
> Order by row no desc
> I added the order by rowno if the detailcolumn is not precise enough,
> perhaps mor ethan one coumn would fit that condition, saythose with 4 and
> 105, should then the rows 3 AND 104 be returned, or is the detail column
> that precise only to return one row at a time ?
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
> news:42916a43$0$10304$afc38c87@.news.optusnet.com.au...
>|||Ok, I setup a sample with the Northwind database which can be ported easily
for your case, try it and raise a hand if you stuck into it.
Select * from Orders O
Where CustomerID = 'VINET'
UNION
Select * from Orders
Where OrderID in
(
Select OrderID-1 from Orders O
Where CustomerID = 'VINET'
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
news:42917894$0$9263$afc38c87@.news.optusnet.com.au...
> yes would return 3 and 104 as well.
> that query gives an error
> 'Only one expression can be specified in the select list when the subquery
> is not introduced with EXISTS'
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:ecvwdn1XFHA.1384@.TK2MSFTNGP09.phx.gbl...
>|||You missed the most basic idea of the Relational Model. Tables have no
ordering. Rows are not "lines" or "records". Those exists in file
systems. What does this "rowno" mean in your data model, and where
does it occur in the reality you are modeling?
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.|||>> I am pretty new to this so excuse any misunderstandings
Quite often people confuse the two dimensional representation of a table on
some visual medium ( a sheet of paper or a computer screen ) with the rows
sequentially arranged as if they can be navigated through like with a
recordset.
However, one of the basic tenets of relational databases is that, there is
no essential order associated with the rows in a table -- i.e. a row is
identified by <table_name, key_value> each and every value is identified by
the triplet <table_name, column_name, key_value). If the values in your
database cannot be identified this way, as a general rule, your design is
flawed. Therefore asking something about "previous line" does not really
make sense.
As a more specific suggestion, please read www.aspfaq.com/5006. Provide
DDLs, sample data & expected results for others to reproduce your problem
scenario.
Anith

Previous & Next Row DataFlow Transform

I have issue where based up a value in a column i need to do some processing of the previous and current row. The dataflow is also already sorted. I tried creating a Script Data Flow Transformation to do this but it isn't working right and the debugging of it sucks. Would anyone know of the best way to do this? or some helpful pointers? I tried "firing" information to help debug but doesn't help when the error message i get back is a stack overflow message.

An example of what I'm trying to do is process the sorted incoming rows for each person. Each person can have multiple rows. Based upon a "status" column in each row do some different processing on the previous or current row. Some Psuedo code:

if prev.PersonID = current.PersonID if status = 1 change prev.PersonDate to today + 60 days if status = 2 change current.PersonDate to prev.PersonDate change prev.PersonDate to today + 1 day else
send rows to output

Any comments or suggestions or helpful advice/critique would be MUCH appreciated!This might help. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1139922&SiteID=1

Though, if you have a variable number of rows per PersonID, I'm not sure how well you can implement this in SSIS. Jay or some of the other script guys around here might have to chime in.|||

Yeah each person can have 1 or more rows. There isn't a defined set of rows per person. Yeah I looked at the link and that wouldn't work in my situation. Not sure if there would be a good way to do it in straight SQL either. Does anyone have suggestions on how to do it in SQL? If possible? Without cursors?

I think i'm going to play around with SQL for little bit and see if i can't come up with something, however executing a OLE DB Command on each record on 1+ million records is going to slow down the process significantly i suspect. I wonder if there is a batch update way of doing this?

|||I've done this many times. Usually I need to do some type of complex aggregation of multiple rows and only output a single row for a distinct entity. Its a great benefit that your data is already sorted.

I wrote the code below mostly from memory, so there may be some syntax problems, but hopefully you get the idea. You cache each row until you've seen the next one. So you're always writing one row behind the current one. You have to override FinishOutputs so you can write out your last row.

Code Snippet

Public Class ScriptMain
Inherits UserComponent

Private Class BufferClass
'define class members for columns
Public PersonID As Integer
Public Status As Integer
Public PersonDate As DateTime
End Class

Dim PreviousPersonID As Integer = -1

Dim Buffer As BufferClass = Nothing

Public Sub WriteBuffer()
If Not Buffer Is Nothing Then
With NewRecordsBuffer
.AddRow()
' add the persisted values from the class to the output buffer
.PersonID = Buffer.PersonID
.Status = Buffer.Status
.PersonDate = Buffer.PersonDate
End With

Buffer = Nothing

End If
End Sub

Public Overrides Sub FinishOutputs()
'write the previous row
WriteBuffer()
MyBase.FinishOutputs()
End Sub

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

Dim PreviousPersonDate As DateTime

If Row.PersonID = PreviousPersonID Then
'modify previous row
If Row.Status = 1 Then
Buffer.PersonDate = DateAdd("d", DateTime.Today, 60)
ElseIf Row.Status = 2 Then
PreviousPersonDate = Buffer.PersonDate
Buffer.PersonDate = DateAdd("d", DateTime.Today, 1)
End If
End If

'write previous row
WriteBuffer()

'buffer the current row
Buffer = New BufferClass
With Buffer
' fill the class with columns that need to be persisted
.PersonID = Row.PersonID
.Status = Row.Status
If Row.Status = 2 Then
.PersonDate = PreviousPersonDate
Else
.PersonDate = Row.PersonDate
End If
End With

PreviousPersonID = Row.PersonID

End Sub

End Class

|||

Thanks! I'll give this a shot. This is similar to what I was doing except I was getting a stack overflow problem and not sure why. The only difference was that I was buffering all of each persons rows (each person had 1 or more rows with the most being 14), but only changing data in the current and previous rows. When current row was a different person then it would output the all the buffered rows.

Anyways I'll give this a shot and see.

|||JayH- Does this process normally take a lot of time? It is extremely slow?|||

Hmmm I took out the FireInformation method I was using to debug (just one line) and now it is 1000 times faster.

JayH - I was wondering if it would be faster to output each row or to output a the person batch of rows?

|||It works! Thanks JayH! I implemented mine so that it handles batches than just the previous row. As far as performance between each row or a batch I'm not sure, but implementing as a batch is faster than a Merge Join transformation.|||

thames wrote:

It works! Thanks JayH! I implemented mine so that it handles batches than just the previous row. As far as performance between each row or a batch I'm not sure, but implementing as a batch is faster than a Merge Join transformation.

I'm glad you got it going. I doubt you'll find any performance difference between lagging only one row and all the rows for the PersonID, especially since you're only expecting a max of four rows per PersonID. I think the code is probably simpler to only do it for one row, but its just a matter of preference.

Preventing expansion of a cross-tab column

Is it possible to prevent certain users from expanding a column (or
row) in a cross-tab/matrix report? That is, a lower level of
granularity in a report column is too detailed for a certain job role,
and you don't want that person to be able to expand that column out,
but you don't want to write multiple reports to accomplish this task.
Thanks.On May 10, 12:25 pm, kmac2...@.gmail.com wrote:
> Is it possible to prevent certain users from expanding a column (or
> row) in a cross-tab/matrix report? That is, a lower level of
> granularity in a report column is too detailed for a certain job role,
> and you don't want that person to be able to expand that column out,
> but you don't want to write multiple reports to accomplish this task.
> Thanks.
I would suggest setting a hidden parameter for the current user (=User!
UserID.ToString) in the report, then pass this hidden parameter to the
stored procedure/query that is sourcing the report and based on the
user's job role, do not return certain drill-down (etc) data. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||The suggestion helped a bit...I was able to use the user role to set
certain information null, so when the group is expanded in the matrix,
it doesn't do anything but add another header row. However, the +/-
sign is still there, and I'd like to hide it in these situations. I
found the place to work with this information in the Edit Group
properties of the matrix column, but the ToggleItem can't be an
expression. While I could set it to another textbox than the default
and hide -that- textbox (which would hide the +/-) it would remove the
context of the +/- in situations where I need it. I might be missing
something, but I can't figure how to make this work out. Any ideas?