Hi,
I am dealing with merge replication.
For identity columns EVEN seed value is set in Publisher database
and ODD seed value is set in Subscriber database(increment value 2) to avoid
insertion conflicts.
For a particular table, usually the records are inserted from
subscriber(through application) and rarely from publisher. During this change
an error 'Primary Violation' occurs.
(1) What is the reason for this?
(2) Is there any way to avoid this?
(3) How can I get the next identity value to be generated?
Thanks,
Soura
Its hard to say. Use the conflict viewer to see if you can figure out where
the two rows are coming from. Partitioning is the way to avoid this, but it
looks like you have done this.
A DBCC Checkident('tablename') will give you the current value, so add the
increment to it to get the next value.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:4208FC88-5A06-46F8-BE66-64EFF7ECDBC4@.microsoft.com...
> Hi,
> I am dealing with merge replication.
> For identity columns EVEN seed value is set in Publisher database
> and ODD seed value is set in Subscriber database(increment value 2) to
> avoid
> insertion conflicts.
> For a particular table, usually the records are inserted from
> subscriber(through application) and rarely from publisher. During this
> change
> an error 'Primary Violation' occurs.
> (1) What is the reason for this?
> (2) Is there any way to avoid this?
> (3) How can I get the next identity value to be generated?
> Thanks,
> Soura
>
>
sql
Showing posts with label value. Show all posts
Showing posts with label value. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
Primary key on Linked server Issue
When I perform a simple query over a linked server that is looking for a
specific value using the primary key, SQL Server performs a constant scan in
stead of a remote query and returns 0 rows. Running the same query locally g
ives the expected result. When I force a data type conversion from integer t
o varchar in the criteria it gives the expected result.
Example problem query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
If i do a select on any other column, then the execution plan shows a remote
query and when I run it the expected rows are returned.
This is only happening on one server, and the only difference I can see is t
hat the database is set up for merge replication.
Any ideas as to why this is happening and how to permanently prevent it?
Thanks
Chris LongstaffAs a follow up to this message and to save anyone else the hassle of finding
the solution ... the issue is merge replication.
Basically the check constraints placed on the table for replication are used
when querying the linked server ... in my case i had a constraint of values
between 810 and 1200 ... when you query the linked server it checks for co
nstraints first and as I was querying for id =400 then it will never find it
as according to the check constraint that value cannot exist within the tab
le...
There are two possible solutions ... force a conversion to bypass the check
constraint ... or use OLEDB for ODBC as the driver as this will not attem
pt to use the check constraint.
Hope this helps anyone else who comes across this!sql
specific value using the primary key, SQL Server performs a constant scan in
stead of a remote query and returns 0 rows. Running the same query locally g
ives the expected result. When I force a data type conversion from integer t
o varchar in the criteria it gives the expected result.
Example problem query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
If i do a select on any other column, then the execution plan shows a remote
query and when I run it the expected rows are returned.
This is only happening on one server, and the only difference I can see is t
hat the database is set up for merge replication.
Any ideas as to why this is happening and how to permanently prevent it?
Thanks
Chris LongstaffAs a follow up to this message and to save anyone else the hassle of finding
the solution ... the issue is merge replication.
Basically the check constraints placed on the table for replication are used
when querying the linked server ... in my case i had a constraint of values
between 810 and 1200 ... when you query the linked server it checks for co
nstraints first and as I was querying for id =400 then it will never find it
as according to the check constraint that value cannot exist within the tab
le...
There are two possible solutions ... force a conversion to bypass the check
constraint ... or use OLEDB for ODBC as the driver as this will not attem
pt to use the check constraint.
Hope this helps anyone else who comes across this!sql
Primary Key falling short
If the identity value falls short you will get an arithmetic overflow error.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:
> Plz tell me what to do if Primary key fall short and execed the max size o
f
> the datatype like integer with Identity increment option.Plz tell me what to do if Primary key fall short and execed the max size of
the datatype like integer with Identity increment option.|||If the identity value falls short you will get an arithmetic overflow error.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:
> Plz tell me what to do if Primary key fall short and execed the max size o
f
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:
> Plz tell me what to do if Primary key fall short and execed the max size o
f
> the datatype like integer with Identity increment option.Plz tell me what to do if Primary key fall short and execed the max size of
the datatype like integer with Identity increment option.|||If the identity value falls short you will get an arithmetic overflow error.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:
> Plz tell me what to do if Primary key fall short and execed the max size o
f
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.
Monday, March 12, 2012
Primary Key
Hi,
I'm looking for a way to make a primary key control in a int column (in othe
r words the app will manage the primary key value).
What's the best way to make that in SQL Server 2005? Most important, there's
a patter that allow many DB systems (like Oracle, DB2,
Firebird...)?
Thanks
MaxIf you're doing it from CLR code, consider using SMO.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Max Andr Bndchen" <nospam@.microsoft.com> wrote in message
news:utIrWfrPGHA.312@.TK2MSFTNGP12.phx.gbl...
Hi,
I'm looking for a way to make a primary key control in a int column (in
other words the app will manage the primary key value).
What's the best way to make that in SQL Server 2005? Most important, there's
a patter that allow many DB systems (like Oracle, DB2,
Firebird...)?
Thanks
Max|||> I'm looking for a way to make a primary key control in a int column (in
> other words the app will manage the primary key value).
If the application will manage (calculate and populate?) the key values,
then all you must do is add the int column and then add the primary key
constraint.
> Most important, there's a patter that allow many DB systems (like Oracle,
> DB2, Firebird...)?
Could you clarify the above question?
"Max Andr Bndchen" <nospam@.microsoft.com> wrote in message
news:utIrWfrPGHA.312@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm looking for a way to make a primary key control in a int column (in
> other words the app will manage the primary key value).
> What's the best way to make that in SQL Server 2005? Most important,
> there's a patter that allow many DB systems (like Oracle, DB2,
> Firebird...)?
> Thanks
> Max
>
I'm looking for a way to make a primary key control in a int column (in othe
r words the app will manage the primary key value).
What's the best way to make that in SQL Server 2005? Most important, there's
a patter that allow many DB systems (like Oracle, DB2,
Firebird...)?
Thanks
MaxIf you're doing it from CLR code, consider using SMO.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Max Andr Bndchen" <nospam@.microsoft.com> wrote in message
news:utIrWfrPGHA.312@.TK2MSFTNGP12.phx.gbl...
Hi,
I'm looking for a way to make a primary key control in a int column (in
other words the app will manage the primary key value).
What's the best way to make that in SQL Server 2005? Most important, there's
a patter that allow many DB systems (like Oracle, DB2,
Firebird...)?
Thanks
Max|||> I'm looking for a way to make a primary key control in a int column (in
> other words the app will manage the primary key value).
If the application will manage (calculate and populate?) the key values,
then all you must do is add the int column and then add the primary key
constraint.
> Most important, there's a patter that allow many DB systems (like Oracle,
> DB2, Firebird...)?
Could you clarify the above question?
"Max Andr Bndchen" <nospam@.microsoft.com> wrote in message
news:utIrWfrPGHA.312@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm looking for a way to make a primary key control in a int column (in
> other words the app will manage the primary key value).
> What's the best way to make that in SQL Server 2005? Most important,
> there's a patter that allow many DB systems (like Oracle, DB2,
> Firebird...)?
> Thanks
> Max
>
Wednesday, March 7, 2012
Previous() function not working with scope parameter.
This works:
=Previous(Fields!Jobs.Value)
The following variants get this error:
"The value expression for the textbox 'textbox17' has an incorrect number of
parameters for the function 'Previous'."
=Previous(Fields!Jobs.Value,"scope")
=Previous(Fields!Jobs.Value,Nothing,"scope",Nothing)
The definition found here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/ht
m/rcr_creating_expressions_v1_61f7.asp?frame=true
Previous(Expression, AggFunction, PreviousScope, AggScope)
Ideas'In this particular case, the documentation is ahead of its time. The MSDN
documentation describes the full implementation of the previous aggregate
function in a future release.
Currently, the previous aggregate only support the first argument.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rick Todd" <rtodd@.spicer.com> wrote in message
news:ePUhUkJhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> This works:
> =Previous(Fields!Jobs.Value)
> The following variants get this error:
> "The value expression for the textbox 'textbox17' has an incorrect number
of
> parameters for the function 'Previous'."
> =Previous(Fields!Jobs.Value,"scope")
> =Previous(Fields!Jobs.Value,Nothing,"scope",Nothing)
> The definition found here
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/ht
> m/rcr_creating_expressions_v1_61f7.asp?frame=true
> Previous(Expression, AggFunction, PreviousScope, AggScope)
> Ideas'
>|||For my situation I did a little workaround by testing the previous rows'
(using previous()) group and comparing the current rows' group and taking
action on the original field I was testing with previous() with a few IIF()
statements. Did the trick...
Thanks
"Rick" wrote:
> Thanks for the quick reply. Are there possibilities of this getting fixed
> with some future service pack?
> "Robert Bruckner [MSFT]" wrote:
> > In this particular case, the documentation is ahead of its time. The MSDN
> > documentation describes the full implementation of the previous aggregate
> > function in a future release.
> > Currently, the previous aggregate only support the first argument.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Rick Todd" <rtodd@.spicer.com> wrote in message
> > news:ePUhUkJhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> > > This works:
> > > =Previous(Fields!Jobs.Value)
> > >
> > > The following variants get this error:
> > >
> > > "The value expression for the textbox 'textbox17' has an incorrect number
> > of
> > > parameters for the function 'Previous'."
> > >
> > > =Previous(Fields!Jobs.Value,"scope")
> > >
> > > =Previous(Fields!Jobs.Value,Nothing,"scope",Nothing)
> > >
> > > The definition found here
> > >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/ht
> > > m/rcr_creating_expressions_v1_61f7.asp?frame=true
> > >
> > > Previous(Expression, AggFunction, PreviousScope, AggScope)
> > >
> > > Ideas'
> > >
> > >
> >
> >
> >
=Previous(Fields!Jobs.Value)
The following variants get this error:
"The value expression for the textbox 'textbox17' has an incorrect number of
parameters for the function 'Previous'."
=Previous(Fields!Jobs.Value,"scope")
=Previous(Fields!Jobs.Value,Nothing,"scope",Nothing)
The definition found here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/ht
m/rcr_creating_expressions_v1_61f7.asp?frame=true
Previous(Expression, AggFunction, PreviousScope, AggScope)
Ideas'In this particular case, the documentation is ahead of its time. The MSDN
documentation describes the full implementation of the previous aggregate
function in a future release.
Currently, the previous aggregate only support the first argument.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rick Todd" <rtodd@.spicer.com> wrote in message
news:ePUhUkJhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> This works:
> =Previous(Fields!Jobs.Value)
> The following variants get this error:
> "The value expression for the textbox 'textbox17' has an incorrect number
of
> parameters for the function 'Previous'."
> =Previous(Fields!Jobs.Value,"scope")
> =Previous(Fields!Jobs.Value,Nothing,"scope",Nothing)
> The definition found here
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/ht
> m/rcr_creating_expressions_v1_61f7.asp?frame=true
> Previous(Expression, AggFunction, PreviousScope, AggScope)
> Ideas'
>|||For my situation I did a little workaround by testing the previous rows'
(using previous()) group and comparing the current rows' group and taking
action on the original field I was testing with previous() with a few IIF()
statements. Did the trick...
Thanks
"Rick" wrote:
> Thanks for the quick reply. Are there possibilities of this getting fixed
> with some future service pack?
> "Robert Bruckner [MSFT]" wrote:
> > In this particular case, the documentation is ahead of its time. The MSDN
> > documentation describes the full implementation of the previous aggregate
> > function in a future release.
> > Currently, the previous aggregate only support the first argument.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Rick Todd" <rtodd@.spicer.com> wrote in message
> > news:ePUhUkJhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> > > This works:
> > > =Previous(Fields!Jobs.Value)
> > >
> > > The following variants get this error:
> > >
> > > "The value expression for the textbox 'textbox17' has an incorrect number
> > of
> > > parameters for the function 'Previous'."
> > >
> > > =Previous(Fields!Jobs.Value,"scope")
> > >
> > > =Previous(Fields!Jobs.Value,Nothing,"scope",Nothing)
> > >
> > > The definition found here
> > >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/ht
> > > m/rcr_creating_expressions_v1_61f7.asp?frame=true
> > >
> > > Previous(Expression, AggFunction, PreviousScope, AggScope)
> > >
> > > Ideas'
> > >
> > >
> >
> >
> >
previous week date parameters
Hi,
Hope someone can help. I am trying to get the default value date parameters
for the previous working week. I then want to use these values while running
the report during the following week until the next Monday. I have almost got
it working with the following:
I put the following code in 'Report Properties>Code':
Public Shared Function GetDate(Byval value as Double)
value=(value)- Weekday(Now)
GetDate=DateAdd("d", value, Now)
End Function
and then the following parameters:
Date from: =Code.GetDate(-5)
Date to: =Code.GetDate(-1)
The only issue i have with this is that it brings in the following:
Date from: 08/11/2004 14:40:54
Date to: 12/11/2004 14:40:54
I don't want it to include the time though. Just the following:
Date from: 08/11/2004 00:00:00
Date to: 12/11/2004 00:00:00
Does anybody know how to get around this?
Any help would be appreciated.
PaulTo convert the date I use .ToShortDateString()
Example:
=Today.AddDays(-1).ToShortDateString()
"pcalv" wrote:
> Hi,
> Hope someone can help. I am trying to get the default value date parameters
> for the previous working week. I then want to use these values while running
> the report during the following week until the next Monday. I have almost got
> it working with the following:
> I put the following code in 'Report Properties>Code':
> Public Shared Function GetDate(Byval value as Double)
> value=(value)- Weekday(Now)
> GetDate=DateAdd("d", value, Now)
> End Function
> and then the following parameters:
> Date from: =Code.GetDate(-5)
> Date to: =Code.GetDate(-1)
> The only issue i have with this is that it brings in the following:
> Date from: 08/11/2004 14:40:54
> Date to: 12/11/2004 14:40:54
> I don't want it to include the time though. Just the following:
> Date from: 08/11/2004 00:00:00
> Date to: 12/11/2004 00:00:00
> Does anybody know how to get around this?
> Any help would be appreciated.
> Paul
>|||Thanks for the reply. Unfortunately this does not work with the custom code i
am using.
Any other help would be appreciated.
Paul
"datobin1" wrote:
> To convert the date I use .ToShortDateString()
> Example:
> =Today.AddDays(-1).ToShortDateString()
>
> "pcalv" wrote:
> > Hi,
> >
> > Hope someone can help. I am trying to get the default value date parameters
> > for the previous working week. I then want to use these values while running
> > the report during the following week until the next Monday. I have almost got
> > it working with the following:
> >
> > I put the following code in 'Report Properties>Code':
> >
> > Public Shared Function GetDate(Byval value as Double)
> > value=(value)- Weekday(Now)
> > GetDate=DateAdd("d", value, Now)
> > End Function
> >
> > and then the following parameters:
> >
> > Date from: =Code.GetDate(-5)
> > Date to: =Code.GetDate(-1)
> >
> > The only issue i have with this is that it brings in the following:
> >
> > Date from: 08/11/2004 14:40:54
> > Date to: 12/11/2004 14:40:54
> >
> > I don't want it to include the time though. Just the following:
> >
> > Date from: 08/11/2004 00:00:00
> > Date to: 12/11/2004 00:00:00
> >
> > Does anybody know how to get around this?
> >
> > Any help would be appreciated.
> >
> > Paul
> >
> >
Hope someone can help. I am trying to get the default value date parameters
for the previous working week. I then want to use these values while running
the report during the following week until the next Monday. I have almost got
it working with the following:
I put the following code in 'Report Properties>Code':
Public Shared Function GetDate(Byval value as Double)
value=(value)- Weekday(Now)
GetDate=DateAdd("d", value, Now)
End Function
and then the following parameters:
Date from: =Code.GetDate(-5)
Date to: =Code.GetDate(-1)
The only issue i have with this is that it brings in the following:
Date from: 08/11/2004 14:40:54
Date to: 12/11/2004 14:40:54
I don't want it to include the time though. Just the following:
Date from: 08/11/2004 00:00:00
Date to: 12/11/2004 00:00:00
Does anybody know how to get around this?
Any help would be appreciated.
PaulTo convert the date I use .ToShortDateString()
Example:
=Today.AddDays(-1).ToShortDateString()
"pcalv" wrote:
> Hi,
> Hope someone can help. I am trying to get the default value date parameters
> for the previous working week. I then want to use these values while running
> the report during the following week until the next Monday. I have almost got
> it working with the following:
> I put the following code in 'Report Properties>Code':
> Public Shared Function GetDate(Byval value as Double)
> value=(value)- Weekday(Now)
> GetDate=DateAdd("d", value, Now)
> End Function
> and then the following parameters:
> Date from: =Code.GetDate(-5)
> Date to: =Code.GetDate(-1)
> The only issue i have with this is that it brings in the following:
> Date from: 08/11/2004 14:40:54
> Date to: 12/11/2004 14:40:54
> I don't want it to include the time though. Just the following:
> Date from: 08/11/2004 00:00:00
> Date to: 12/11/2004 00:00:00
> Does anybody know how to get around this?
> Any help would be appreciated.
> Paul
>|||Thanks for the reply. Unfortunately this does not work with the custom code i
am using.
Any other help would be appreciated.
Paul
"datobin1" wrote:
> To convert the date I use .ToShortDateString()
> Example:
> =Today.AddDays(-1).ToShortDateString()
>
> "pcalv" wrote:
> > Hi,
> >
> > Hope someone can help. I am trying to get the default value date parameters
> > for the previous working week. I then want to use these values while running
> > the report during the following week until the next Monday. I have almost got
> > it working with the following:
> >
> > I put the following code in 'Report Properties>Code':
> >
> > Public Shared Function GetDate(Byval value as Double)
> > value=(value)- Weekday(Now)
> > GetDate=DateAdd("d", value, Now)
> > End Function
> >
> > and then the following parameters:
> >
> > Date from: =Code.GetDate(-5)
> > Date to: =Code.GetDate(-1)
> >
> > The only issue i have with this is that it brings in the following:
> >
> > Date from: 08/11/2004 14:40:54
> > Date to: 12/11/2004 14:40:54
> >
> > I don't want it to include the time though. Just the following:
> >
> > Date from: 08/11/2004 00:00:00
> > Date to: 12/11/2004 00:00:00
> >
> > Does anybody know how to get around this?
> >
> > Any help would be appreciated.
> >
> > Paul
> >
> >
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.
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 Value Calculation driving me nuts
Hey all,
I've posted my question in the OLAP sql group, but so far no one has
responded, so im hoping you guys can help me out...
My problem is that i am trying to create a value added calculation in a
named query. The calculation i am trying looks like this in algebra
form: (current cost-previous cost)-(current profits-previous profits)
Now the previous cost or previous profits data is based on dates found
in another table (called:tbo.Dates) if that makes any difference
Costs & Profits in table called tbo.Values
If more info is needed, please let me know.
Thanks & Cheers,
Can you provide the DDL (Create Table.. etc) and some sample Data and
what the expected results are?
Barry
daveoram24@.hotmail.com wrote:
> Hey all,
> I've posted my question in the OLAP sql group, but so far no one has
> responded, so im hoping you guys can help me out...
> My problem is that i am trying to create a value added calculation in a
> named query. The calculation i am trying looks like this in algebra
> form: (current cost-previous cost)-(current profits-previous profits)
> Now the previous cost or previous profits data is based on dates found
> in another table (called:tbo.Dates) if that makes any difference
> Costs & Profits in table called tbo.Values
> If more info is needed, please let me know.
> Thanks & Cheers,
I've posted my question in the OLAP sql group, but so far no one has
responded, so im hoping you guys can help me out...
My problem is that i am trying to create a value added calculation in a
named query. The calculation i am trying looks like this in algebra
form: (current cost-previous cost)-(current profits-previous profits)
Now the previous cost or previous profits data is based on dates found
in another table (called:tbo.Dates) if that makes any difference
Costs & Profits in table called tbo.Values
If more info is needed, please let me know.
Thanks & Cheers,
Can you provide the DDL (Create Table.. etc) and some sample Data and
what the expected results are?
Barry
daveoram24@.hotmail.com wrote:
> Hey all,
> I've posted my question in the OLAP sql group, but so far no one has
> responded, so im hoping you guys can help me out...
> My problem is that i am trying to create a value added calculation in a
> named query. The calculation i am trying looks like this in algebra
> form: (current cost-previous cost)-(current profits-previous profits)
> Now the previous cost or previous profits data is based on dates found
> in another table (called:tbo.Dates) if that makes any difference
> Costs & Profits in table called tbo.Values
> If more info is needed, please let me know.
> Thanks & Cheers,
Previous Value Calculation driving me nuts
Hey all,
I've posted my question in the OLAP sql group, but so far no one has
responded, so im hoping you guys can help me out...
My problem is that i am trying to create a value added calculation in a
named query. The calculation i am trying looks like this in algebra
form: (current cost-previous cost)-(current profits-previous profits)
Now the previous cost or previous profits data is based on dates found
in another table (called:tbo.Dates) if that makes any difference
Costs & Profits in table called tbo.Values
If more info is needed, please let me know.
Thanks & Cheers,Can you provide the DDL (Create Table.. etc) and some sample Data and
what the expected results are?
Barry
daveoram24@.hotmail.com wrote:
> Hey all,
> I've posted my question in the OLAP sql group, but so far no one has
> responded, so im hoping you guys can help me out...
> My problem is that i am trying to create a value added calculation in a
> named query. The calculation i am trying looks like this in algebra
> form: (current cost-previous cost)-(current profits-previous profits)
> Now the previous cost or previous profits data is based on dates found
> in another table (called:tbo.Dates) if that makes any difference
> Costs & Profits in table called tbo.Values
> If more info is needed, please let me know.
> Thanks & Cheers,
I've posted my question in the OLAP sql group, but so far no one has
responded, so im hoping you guys can help me out...
My problem is that i am trying to create a value added calculation in a
named query. The calculation i am trying looks like this in algebra
form: (current cost-previous cost)-(current profits-previous profits)
Now the previous cost or previous profits data is based on dates found
in another table (called:tbo.Dates) if that makes any difference
Costs & Profits in table called tbo.Values
If more info is needed, please let me know.
Thanks & Cheers,Can you provide the DDL (Create Table.. etc) and some sample Data and
what the expected results are?
Barry
daveoram24@.hotmail.com wrote:
> Hey all,
> I've posted my question in the OLAP sql group, but so far no one has
> responded, so im hoping you guys can help me out...
> My problem is that i am trying to create a value added calculation in a
> named query. The calculation i am trying looks like this in algebra
> form: (current cost-previous cost)-(current profits-previous profits)
> Now the previous cost or previous profits data is based on dates found
> in another table (called:tbo.Dates) if that makes any difference
> Costs & Profits in table called tbo.Values
> If more info is needed, please let me know.
> Thanks & Cheers,
Previous value best method
I'm not really strong in SQL. My goal is to compare the beginning mileage of a vehicle record with it's previous ending mileage reading. I have something that works, but it feels clunky. I wonder if there is a better method, ie a join. Here's what I have:
SELECT A.Trolley_num, A.Date, A.Speedo_start, A.Speedo_end,
(SELECT B.Speedo_end FROM Daily_Trolley AS B
WHERE B.Trolley_num = A.Trolley_num
AND B.Date =
(SELECT Max(Date) FROM Daily_Trolley AS C WHERE C.Trolley_num = A.Trolley_num
And C.Date < '1/23/2005')) AS PrevSpeedoEnd
FROM Daily_Trolley AS A
WHERE A.Date='1/23/2005'
ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.Unfortunately, this is just a clunky thing to do in SQL. You can try this and see if it is any faster. One lest nested subquery...
select Current.Trolley_num,
Current.Date,
Current.Speedo_start,
Current.Speedo_end,
Previous.Speedo_end
from Daily_Trolley Current
inner join --PriorReadings
(select DTA.Trolley_num,
DTA.Date,
Max(DTB.Date) as PreviousDate
from Daily_Trolley DTA
left outer join Daily_Trolley DTB
on DTA.Trolley_num = DTB.Trolley_num
and DTA.Date > DTB.Date
group by DTA.Trolley_num,
DTA.Date) PriorReadings
on Current.Trolley_num = PriorReadings.Trolley_num
and Current.Date = PriorReadings.Date
left outer join Daily_Trolley Previous
on PriorReadings.Trolley_num = Previous.Trolley_num
and PriorReadings.PreviousDate = Previous.Date|||Forgive me if this is a duplicate; I got an error posting a reply and it's not showing up. This is the third try.
Thanks blindman. QA didn't like "Current" as an alias, but it worked fine when I changed that. Both our versions return records so quickly that no time is registered in the execution time window in QA. There are only 10k records in this table though. However, your version lets me calculate the difference; mine wouldn't (not directly anyway).
I may modify both to work against another table with several hundred thousand records, and see how they compare. I suspect yours will be faster due to the join instead of subquery.
Thanks again.
SELECT A.Trolley_num, A.Date, A.Speedo_start, A.Speedo_end,
(SELECT B.Speedo_end FROM Daily_Trolley AS B
WHERE B.Trolley_num = A.Trolley_num
AND B.Date =
(SELECT Max(Date) FROM Daily_Trolley AS C WHERE C.Trolley_num = A.Trolley_num
And C.Date < '1/23/2005')) AS PrevSpeedoEnd
FROM Daily_Trolley AS A
WHERE A.Date='1/23/2005'
ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.Unfortunately, this is just a clunky thing to do in SQL. You can try this and see if it is any faster. One lest nested subquery...
select Current.Trolley_num,
Current.Date,
Current.Speedo_start,
Current.Speedo_end,
Previous.Speedo_end
from Daily_Trolley Current
inner join --PriorReadings
(select DTA.Trolley_num,
DTA.Date,
Max(DTB.Date) as PreviousDate
from Daily_Trolley DTA
left outer join Daily_Trolley DTB
on DTA.Trolley_num = DTB.Trolley_num
and DTA.Date > DTB.Date
group by DTA.Trolley_num,
DTA.Date) PriorReadings
on Current.Trolley_num = PriorReadings.Trolley_num
and Current.Date = PriorReadings.Date
left outer join Daily_Trolley Previous
on PriorReadings.Trolley_num = Previous.Trolley_num
and PriorReadings.PreviousDate = Previous.Date|||Forgive me if this is a duplicate; I got an error posting a reply and it's not showing up. This is the third try.
Thanks blindman. QA didn't like "Current" as an alias, but it worked fine when I changed that. Both our versions return records so quickly that no time is registered in the execution time window in QA. There are only 10k records in this table though. However, your version lets me calculate the difference; mine wouldn't (not directly anyway).
I may modify both to work against another table with several hundred thousand records, and see how they compare. I suspect yours will be faster due to the join instead of subquery.
Thanks again.
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
An = R An-1 + B
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:
An = R An-1 + BThe general solution for which is:
Ak=CRk + B/(1-R)The particular solution for n=0 is
A0=CR0+ B/(1-R)=C+B/(1-R)Solve for C
C=A0-B(1-R)Therefore, the particular solution for the dynamical system is:
Ak=Rk(A0-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
An = R An-1 + B
This is called a First-Order Homogeneous Dynamical Equation
Which is what your request is:
========================
B = expr2 - expr3
expr1n= [(1/30) * expr1n-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
Fk(x) = H(F(x)0 , k)
where k is the iteration you want to calculate and F(x)0 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
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
WITH MyTable AS
(SELECT
*,
Num = ROW_NUMBER() OVER(ORDER BY <YourColumn>)
FROM [dbo].<YourTable>)
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;
Saturday, February 25, 2012
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
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.
preview layout diferent than server layout
i am using reporting serveces :
i created a DataExtension to get a dataset's value in the report as an
xml input file (this works)
1. i am designing the report using this dataset's values and some
calculated expressions like "runningvalue" everything works fine in
the preview button but when the report is called from my c#
application the "runningvalue" is not being calculated while all the
other data is calculated.
2. the data in my report table is in groups were some groups must be
desplayed others hidden ,acccording to the xml values, as well as some
tables must be displayed otheres hidden in the preview button of the
report this works but also when the report is called from my c#
application all the tables and the groups are displaued which MUST NOT
HAPPEN
what can i do to fix this?
pls help
i need it urgently and i have been working on it for like a lot
by the way,
this is how i am calling the render: from my c# application :
proxyParameters[0] = new STB.testlab02.ParameterValue();
proxyParameters[0].Name = "DataSource";
//System.Data.DataSet dsetReport = new System.Data.DataSet();
DataSet dsReportMO = new DataSet();
dsReportMO.ReadXml("c:\\DataExt\\dsReport.xml");//dsReportMO.GetXml();
proxyParameters[0].Value = dsReportMO.GetXml();
// Build device info based on the start page
deviceInfo = String.Format(@."<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",
"emf");
//Exectute the report and get page count.
try
{
// Renders the first page of the report and returns streamIDs for
// subsequent pages
firstPage = repServ.Render(reportPath, format,
null,deviceInfo,proxyParameters,null,null,out encoding,out
mimeType,out reportHistoryParameters, out warnings, out streamIDs);
.
.
.i still cant find a solution pls help!!!!!!!
how can this work
////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
mireille.azar@.ifsal.com (Mireille) wrote in message news:<63c1f241.0408250057.12d85680@.posting.google.com>...
> i am using reporting serveces :
> i created a DataExtension to get a dataset's value in the report as an
> xml input file (this works)
> 1. i am designing the report using this dataset's values and some
> calculated expressions like "runningvalue" everything works fine in
> the preview button but when the report is called from my c#
> application the "runningvalue" is not being calculated while all the
> other data is calculated.
> 2. the data in my report table is in groups were some groups must be
> desplayed others hidden ,acccording to the xml values, as well as some
> tables must be displayed otheres hidden in the preview button of the
> report this works but also when the report is called from my c#
> application all the tables and the groups are displaued which MUST NOT
> HAPPEN
> what can i do to fix this?
> pls help
> i need it urgently and i have been working on it for like a lot
>
> by the way,
>
> this is how i am calling the render: from my c# application :
>
> proxyParameters[0] = new STB.testlab02.ParameterValue();
> proxyParameters[0].Name = "DataSource";
> //System.Data.DataSet dsetReport = new System.Data.DataSet();
> DataSet dsReportMO = new DataSet();
> dsReportMO.ReadXml("c:\\DataExt\\dsReport.xml");//dsReportMO.GetXml();
> proxyParameters[0].Value = dsReportMO.GetXml();
> // Build device info based on the start page
> deviceInfo = String.Format(@."<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",
> "emf");
> //Exectute the report and get page count.
> try
> {
> // Renders the first page of the report and returns streamIDs for
> // subsequent pages
> firstPage = repServ.Render(reportPath, format,
> null,deviceInfo,proxyParameters,null,null,out encoding,out
> mimeType,out reportHistoryParameters, out warnings, out streamIDs);
> .
> .
> .|||i found the solution...
i created a DataExtension to get a dataset's value in the report as an
xml input file (this works)
1. i am designing the report using this dataset's values and some
calculated expressions like "runningvalue" everything works fine in
the preview button but when the report is called from my c#
application the "runningvalue" is not being calculated while all the
other data is calculated.
2. the data in my report table is in groups were some groups must be
desplayed others hidden ,acccording to the xml values, as well as some
tables must be displayed otheres hidden in the preview button of the
report this works but also when the report is called from my c#
application all the tables and the groups are displaued which MUST NOT
HAPPEN
what can i do to fix this?
pls help
i need it urgently and i have been working on it for like a lot
by the way,
this is how i am calling the render: from my c# application :
proxyParameters[0] = new STB.testlab02.ParameterValue();
proxyParameters[0].Name = "DataSource";
//System.Data.DataSet dsetReport = new System.Data.DataSet();
DataSet dsReportMO = new DataSet();
dsReportMO.ReadXml("c:\\DataExt\\dsReport.xml");//dsReportMO.GetXml();
proxyParameters[0].Value = dsReportMO.GetXml();
// Build device info based on the start page
deviceInfo = String.Format(@."<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",
"emf");
//Exectute the report and get page count.
try
{
// Renders the first page of the report and returns streamIDs for
// subsequent pages
firstPage = repServ.Render(reportPath, format,
null,deviceInfo,proxyParameters,null,null,out encoding,out
mimeType,out reportHistoryParameters, out warnings, out streamIDs);
.
.
.i still cant find a solution pls help!!!!!!!
how can this work
////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
mireille.azar@.ifsal.com (Mireille) wrote in message news:<63c1f241.0408250057.12d85680@.posting.google.com>...
> i am using reporting serveces :
> i created a DataExtension to get a dataset's value in the report as an
> xml input file (this works)
> 1. i am designing the report using this dataset's values and some
> calculated expressions like "runningvalue" everything works fine in
> the preview button but when the report is called from my c#
> application the "runningvalue" is not being calculated while all the
> other data is calculated.
> 2. the data in my report table is in groups were some groups must be
> desplayed others hidden ,acccording to the xml values, as well as some
> tables must be displayed otheres hidden in the preview button of the
> report this works but also when the report is called from my c#
> application all the tables and the groups are displaued which MUST NOT
> HAPPEN
> what can i do to fix this?
> pls help
> i need it urgently and i have been working on it for like a lot
>
> by the way,
>
> this is how i am calling the render: from my c# application :
>
> proxyParameters[0] = new STB.testlab02.ParameterValue();
> proxyParameters[0].Name = "DataSource";
> //System.Data.DataSet dsetReport = new System.Data.DataSet();
> DataSet dsReportMO = new DataSet();
> dsReportMO.ReadXml("c:\\DataExt\\dsReport.xml");//dsReportMO.GetXml();
> proxyParameters[0].Value = dsReportMO.GetXml();
> // Build device info based on the start page
> deviceInfo = String.Format(@."<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",
> "emf");
> //Exectute the report and get page count.
> try
> {
> // Renders the first page of the report and returns streamIDs for
> // subsequent pages
> firstPage = repServ.Render(reportPath, format,
> null,deviceInfo,proxyParameters,null,null,out encoding,out
> mimeType,out reportHistoryParameters, out warnings, out streamIDs);
> .
> .
> .|||i found the solution...
Preview is better than web browser in this case
I like the functionality in the preview - that if all the parameters
have a valid value... it starts the query without pressing 'View
Report'.
Whereas in a published report in a browser, I always have to press the
'view report'.
In some cases one is better than the other - specially if the query is
small... but navigation needs to be faster.
I have repeatedly asked this question - without any answers.
But if it works in Preview... why not in the browser...'
Seems like there is setting... but no documentation...
I think we need both the options.Yes, we have struggled to answer this questions ourselves. We keep coming
back to this and will take your comments into consideration for sure. To
explain:
The problem is that on the server, there is really no way to stop the
execution of the query that was spawned by requesting a particular report.
So if you have all the defaults specified, then (for a live report) the
server will immediately kick off a report execution. This will inturn
execute the query. That query could take some time to run. Now, if the
user wants to change the values from default before the report finishes
executing, s/he might just click stop on the browser, change the paramters,
and click view report. Now due to how the browser works, the server
doesn't know the user has changed their minds. To the server it is just
another request. So now you have 2 report executions occuring for the same
user. The orignal request eventually will be killed on the report server
(once we realize the connection is closed/stale) but that still leaves the
long running query on the DB server.
So in effect by automatically running the report with the default parameters
you're increasing load significantly on the server. Now imagine how this
affects the system when you scale to 1000's of users.
In the designer, you don't have the same problem so you're more free to do
increase responsivness.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0407221045.28cb83ac@.posting.google.com...
>I like the functionality in the preview - that if all the parameters
> have a valid value... it starts the query without pressing 'View
> Report'.
> Whereas in a published report in a browser, I always have to press the
> 'view report'.
> In some cases one is better than the other - specially if the query is
> small... but navigation needs to be faster.
> I have repeatedly asked this question - without any answers.
> But if it works in Preview... why not in the browser...'
> Seems like there is setting... but no documentation...
> I think we need both the options.|||I would say - give those controls to the designer.
Let him/her decide -
1. On Default parameters - should execute immediately or not
2. On Change selection - should execute immediately or not
- parameter wise control. I.e. decide whether a particular
parameter should execute the report immediately or not
Provide with a control with each parameter.
Cascading parameters should have that option also - Specially if
selection of Parameter 1 leads to default of Parameter 2.
- In which case - first of all the "dependency" should be explicitly
defined.
- And then - whether to execute the report immediately or not - could
also be a designers choice.
Just some thoughts...
have a valid value... it starts the query without pressing 'View
Report'.
Whereas in a published report in a browser, I always have to press the
'view report'.
In some cases one is better than the other - specially if the query is
small... but navigation needs to be faster.
I have repeatedly asked this question - without any answers.
But if it works in Preview... why not in the browser...'
Seems like there is setting... but no documentation...
I think we need both the options.Yes, we have struggled to answer this questions ourselves. We keep coming
back to this and will take your comments into consideration for sure. To
explain:
The problem is that on the server, there is really no way to stop the
execution of the query that was spawned by requesting a particular report.
So if you have all the defaults specified, then (for a live report) the
server will immediately kick off a report execution. This will inturn
execute the query. That query could take some time to run. Now, if the
user wants to change the values from default before the report finishes
executing, s/he might just click stop on the browser, change the paramters,
and click view report. Now due to how the browser works, the server
doesn't know the user has changed their minds. To the server it is just
another request. So now you have 2 report executions occuring for the same
user. The orignal request eventually will be killed on the report server
(once we realize the connection is closed/stale) but that still leaves the
long running query on the DB server.
So in effect by automatically running the report with the default parameters
you're increasing load significantly on the server. Now imagine how this
affects the system when you scale to 1000's of users.
In the designer, you don't have the same problem so you're more free to do
increase responsivness.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0407221045.28cb83ac@.posting.google.com...
>I like the functionality in the preview - that if all the parameters
> have a valid value... it starts the query without pressing 'View
> Report'.
> Whereas in a published report in a browser, I always have to press the
> 'view report'.
> In some cases one is better than the other - specially if the query is
> small... but navigation needs to be faster.
> I have repeatedly asked this question - without any answers.
> But if it works in Preview... why not in the browser...'
> Seems like there is setting... but no documentation...
> I think we need both the options.|||I would say - give those controls to the designer.
Let him/her decide -
1. On Default parameters - should execute immediately or not
2. On Change selection - should execute immediately or not
- parameter wise control. I.e. decide whether a particular
parameter should execute the report immediately or not
Provide with a control with each parameter.
Cascading parameters should have that option also - Specially if
selection of Parameter 1 leads to default of Parameter 2.
- In which case - first of all the "dependency" should be explicitly
defined.
- And then - whether to execute the report immediately or not - could
also be a designers choice.
Just some thoughts...
Subscribe to:
Posts (Atom)