Rowcounts on all tables in a DB

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

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

Rowcounts on all tables in a DB

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

Row wise operation in MSSQL Server

How can I create a row wise trigger in MSSQL Server .

CREATE TRIGGER trigName ON tableName for
INSERT , UPDATE , DELETE
AS ...

For a multiple delete , I got only one trigger invocation .
But I need individual trigger calls for each row ...
How can I do this in t-sql ?
Is there any usage like FOR EACH ROW in Oracle ?
Is it possible through INSTEAD OF TRIGGER ?
Please help !!!!!!!!Yes, you could put a cursor in the trigger that operates on each row of the INSERTED table. But do yourself a favor and don't do it. Find a set-based solution using the INSERTED table instead.

Please describe what you are trying to do and why you think you need a row-wise operator (cursor). These are generally only needed by dynamic sql procedures or processes where the results of the operation on one record affects the results of the operation on the next record.|||AND...I'll add...anything that would require a cursor should not be done in a trigger...sql

Row values to string

Hi,
How can I create a string containing all values from a row in a
table(SELECT).
sample:
ID | NAME
--
1 | John -> "1,John"
2 | Alexander -> "2,Alexander"
Thank you,
Roby Eisenbraun MartinsConvert all the column values to a character compatible type and use the
concatenation operator like:
SELECT CAST( id AS VARCHAR ) + ',' +
CAST( name AS VARCHAR ( 30 ) ) + ',' +
..
FROM tbl ;
Anith|||What do you want to do with the string? You can use DTS to export a table or
view to a comma delimited text file.
"Roby Eisenbraun Martins" <RobyEisenbraunMartins@.discussions.microsoft.com>
wrote in message news:BDF4B30A-70E8-4D3C-8D35-7C095D2C167F@.microsoft.com...
> Hi,
> How can I create a string containing all values from a row in a
> table(SELECT).
> sample:
> ID | NAME
> --
> 1 | John -> "1,John"
> 2 | Alexander -> "2,Alexander"
> Thank you,
> Roby Eisenbraun Martins
>sql

Row triggers equivalent in sql server 2000

Hello Guys!
i have been working with oracle with quite a time. No i migrated to sql
server 2000 and i want to create a trigger on a table.
the trigger function has to update the Modification field to getdate()
whenever a row is being updated.
i tried lots of things

if anyone can help i would appreciate a lot!

RegardsFad,

This should work for you:

create trigger MyTrigger on MyTable for insert, update
as
begin
update MyTable
set Modification = getdate()
from MyTable m
inner join inserted i on m.MyTableID = i.MyTableID
end

-- Bill

"Fad" <fadyay@.gmail.comwrote in message
news:1169163810.969473.16880@.m58g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Hello Guys!
i have been working with oracle with quite a time. No i migrated to sql
server 2000 and i want to create a trigger on a table.
the trigger function has to update the Modification field to getdate()
whenever a row is being updated.
i tried lots of things
>
if anyone can help i would appreciate a lot!
>
Regards
>

|||Thanks AlterEgo for the help!
now i get the functionality of inserted and deleted tablessql

Row Size Limitation of Report Model?

Hello,

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

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

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

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

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

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

Thanks!!

Brian

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

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

Thanks!

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

Row Size Limitation of Report Model?

Hello,

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

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

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

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

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

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

Thanks!!

Brian

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

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

Thanks!

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

Row size in SQL2K

I was trying to create the table below with Query Analyzer and got this warning:
Warning: The table 'PREPARACAO_ATIVIDADE' has been created but its maximum row size (16432) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Is there a way to increase the default maximum row size of 8060 in SQL server 2000?
Thanks for your help,
Andr=E9 Andraus Filho
CREATE TABLE [dbo].[PREPARACAO_ATIVIDADE] (
[COD_PREPARACAO] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[CODIGO_USUARIO] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[COD_TIP_OBJ] [decimal](18, 0) NOT NULL ,
[COD_UA] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[COD_FP] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[COD_ATIVIDADE] [decimal](5, 0) NOT NULL ,
[COD_SUBNIVEL_ATIV] [decimal](5, 0) NOT NULL ,
[COD_PLANO] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[DATINI_ABRAG] [datetime] NOT NULL ,
[DATFIM_ABRAG] [datetime] NOT NULL ,
[CUSTO_PREV_TRANSP] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[DATINI_PREP_ATIV] [datetime] NULL ,
[DATINI_PREVISAO] [datetime] NULL ,
[CUSTO_PREV_DIARIA] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[COMENTARIO_DIRETOR] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[DATFIM_PREVISAO] [datetime] NULL ,
[CENTRO_RESPONSAVEL] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[SIT_DIR_CENTRO] [decimal](1, 0) NULL ,
[JUSTIF_PONTO] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[SIT_DCI_G] [decimal](1, 0) NULL ,
[COD_CENTRO_RESP_ATIV] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[COD_CONSIG] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[OUTROS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ) ON [PRIMARY]
GOYou can't make it larger than 8060 bytes but you can change some of the
varchar(8000)'t to Text datatypes.
--
Andrew J. Kelly
SQL Server MVP
"André Andraus Filho" <aafandraus@.fazenda.sp.gov.br> wrote in message
news:074f01c3b057$0dd5bae0$a001280a@.phx.gbl...
I was trying to create the table below with Query Analyzer
and got this warning:
Warning: The table 'PREPARACAO_ATIVIDADE' has been created
but its maximum row size (16432) exceeds the maximum
number of bytes per row (8060). INSERT or UPDATE of a row
in this table will fail if the resulting row length
exceeds 8060 bytes.
Is there a way to increase the default maximum row size
of 8060 in SQL server 2000?
Thanks for your help,
André Andraus Filho
CREATE TABLE [dbo].[PREPARACAO_ATIVIDADE] (
[COD_PREPARACAO] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[CODIGO_USUARIO] [varchar] (11) COLLATE
SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[COD_TIP_OBJ] [decimal](18, 0) NOT NULL ,
[COD_UA] [varchar] (6) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[COD_FP] [varchar] (17) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[COD_ATIVIDADE] [decimal](5, 0) NOT NULL ,
[COD_SUBNIVEL_ATIV] [decimal](5, 0) NOT NULL ,
[COD_PLANO] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[DATINI_ABRAG] [datetime] NOT NULL ,
[DATFIM_ABRAG] [datetime] NOT NULL ,
[CUSTO_PREV_TRANSP] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[DATINI_PREP_ATIV] [datetime] NULL ,
[DATINI_PREVISAO] [datetime] NULL ,
[CUSTO_PREV_DIARIA] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[COMENTARIO_DIRETOR] [varchar] (8000) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[DATFIM_PREVISAO] [datetime] NULL ,
[CENTRO_RESPONSAVEL] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[SIT_DIR_CENTRO] [decimal](1, 0) NULL ,
[JUSTIF_PONTO] [varchar] (8000) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[SIT_DCI_G] [decimal](1, 0) NULL ,
[COD_CENTRO_RESP_ATIV] [varchar] (6) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[COD_CONSIG] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[OUTROS] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL
) ON [PRIMARY]
GO|||Telling you to change the datatype in order to avoid the error may be a
short term fix but not a complete answer. I would contact them again and
find out when they intend to provide a hot fix to really solve your problem.
--
Andrew J. Kelly
SQL Server MVP
"André A. Filho" <aafandraus@.fazenda.sp.gov.br> wrote in message
news:03e701c3b281$45c3efe0$a001280a@.phx.gbl...
I had already done the change of all varchar(8000) to text
datatype , but it doesn´t resolve , because with this , I
got an Access Violation error when trying to update a text
column inside a stored procedure.
I´ve contacted Microsoft and they told me to change the
datatype in order to solve this AV error.
The columns that are set to varchar(8000) are reports
entered by the user , about requests and contracts.
They are free text with at least 4200 bytes.
Do you have any idea on how to solve it ?
Thanks in advance,
André
>--Original Message--
>You can't make it larger than 8060 bytes but you can
change some of the
>varchar(8000)'t to Text datatypes.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"André Andraus Filho" <aafandraus@.fazenda.sp.gov.br>
wrote in message
>news:074f01c3b057$0dd5bae0$a001280a@.phx.gbl...
>I was trying to create the table below with Query Analyzer
>and got this warning:
>Warning: The table 'PREPARACAO_ATIVIDADE' has been created
>but its maximum row size (16432) exceeds the maximum
>number of bytes per row (8060). INSERT or UPDATE of a row
>in this table will fail if the resulting row length
>exceeds 8060 bytes.
>Is there a way to increase the default maximum row size
>of 8060 in SQL server 2000?
>Thanks for your help,
>André Andraus Filho
>
>CREATE TABLE [dbo].[PREPARACAO_ATIVIDADE] (
>[COD_PREPARACAO] [varchar] (9) COLLATE
>SQL_Latin1_General_CP1_CI_AI NOT NULL ,
>[CODIGO_USUARIO] [varchar] (11) COLLATE
>SQL_Latin1_General_CP1_CI_AI NOT NULL ,
>[COD_TIP_OBJ] [decimal](18, 0) NOT NULL ,
>[COD_UA] [varchar] (6) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[COD_FP] [varchar] (17) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[COD_ATIVIDADE] [decimal](5, 0) NOT NULL ,
>[COD_SUBNIVEL_ATIV] [decimal](5, 0) NOT NULL ,
>[COD_PLANO] [varchar] (5) COLLATE
>SQL_Latin1_General_CP1_CI_AI NOT NULL ,
>[DATINI_ABRAG] [datetime] NOT NULL ,
>[DATFIM_ABRAG] [datetime] NOT NULL ,
>[CUSTO_PREV_TRANSP] [varchar] (20) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[DATINI_PREP_ATIV] [datetime] NULL ,
>[DATINI_PREVISAO] [datetime] NULL ,
>[CUSTO_PREV_DIARIA] [varchar] (20) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[COMENTARIO_DIRETOR] [varchar] (8000) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[DATFIM_PREVISAO] [datetime] NULL ,
>[CENTRO_RESPONSAVEL] [varchar] (100) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[SIT_DIR_CENTRO] [decimal](1, 0) NULL ,
>[JUSTIF_PONTO] [varchar] (8000) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[SIT_DCI_G] [decimal](1, 0) NULL ,
>[COD_CENTRO_RESP_ATIV] [varchar] (6) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[COD_CONSIG] [varchar] (20) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL ,
>[OUTROS] [varchar] (100) COLLATE
>SQL_Latin1_General_CP1_CI_AI NULL
>) ON [PRIMARY]
>GO
>
>
>
>.
>

Row numbering unpredictable

Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/Assuming CustomerName is unique:
select
(select count (*)
from #tmp t1
where t1.CustomerName <= t2.CustomerName) as rownum
, *
from
#tmp t2
order by
t2.CustomerName
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/|||you could create the table first with an ID column, then insert into
it. I suspect (though have no evidence) that the select into #tmp with
an id column created then is having issues with the order by|||(cseemeuk@.googlemail.com) writes:
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
people that it's guaranteed to work. Whether that really is true, I'm not
completely convinced of, but fairly. In any case, SELECT INTO is *not*
guaranteed to work that way, so stay away from it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks - works perfectly. The INTO was the problem - appears to be no
guaranteed order to the IDENTITY(bigint, 1,1)
All sorted
Cheers,
Chris Smith
http://www.cswd.co.uk/|||The order is not guaranteed when you use SELECT INTO.
See
http://support.microsoft.com/defaul...kb;en-us;273586
For a list of paging options see
http://www.aspfaq.com/show.asp?id=2120
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
> Hi,
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
> (in ADO :-P)
> This is SQL 2000 SP3 (pah!)
> Cheers,
> Chris Smith
> http://www.cswd.co.uk/
>|||One would think this type of thing,so common and important,
would have a kb or something written by MS.Are you aware of any
link?If none exists I would ask you to kindly request something in
'writing'.Key points of an enterprise database should not be rattling
around just in someone head! :)
Clarity,clarity and nothing but clarity.
Regards from:
www.rac4sql.net
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A28BF07D031Yazorman@.127.0.0.1...
> (cseemeuk@.googlemail.com) writes:
> Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
> BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
> people that it's guaranteed to work. Whether that really is true, I'm not
> completely convinced of, but fairly. In any case, SELECT INTO is *not*
> guaranteed to work that way, so stay away from it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Steve Dassin wrote:
> One would think this type of thing,so common and important,
> would have a kb or something written by MS.Are you aware of any
> link?If none exists I would ask you to kindly request something in
> 'writing'.Key points of an enterprise database should not be rattling
> around just in someone head! :)
> Clarity,clarity and nothing but clarity.
http://support.microsoft.com/defaul...kb;en-us;273586
Do not assume that article means that all INSERTs will always cause
IDENTITY to be generated in a predetermined order. There are at least
some situations where that doesn't work - whether by design or a bug I
can't say.
Perhaps the safest course is to assume that you cannot control the
IDENTITY sequence with ORDER BY. In my view the wisest and most logical
solution is to use other methods like the ROW_NUMBER function for
example.
I can think of at least two good reasons for not using IDENTITY the way
proposed by the KB. Firstly IDENTITY is normally intended as an
arbitrary surrogate key - using the values in any "meaningful" way is a
compromise you don't need and is something it just isn't designed for.
Secondly, this supposed behaviour of an "ordered" INSERT looks contrary
to the set-based nature of an INSERT statement. Whether or not it works
today, it seems undesirable to assume that it should always work that
way in future. One would hope and expect that the engine could optimise
out any redundant sorting in INSERT...SELECT queries. That seems to be
what happens in some cases today and maybe it will happen more often in
future versions due to improvements in the optimiser. Just some things
to bear in mind.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Anyone got any ideas on how to solve that other than client side paging <
<
The basic principle of a tiered architecture is that display is done in
the front end adn NEVER in the database. Why are you sing violating
40 years of Software Engineering?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144803667.948395.290650@.i40g2000cwc.googlegroups.com...
<<
> The basic principle of a tiered architecture is that display is done in
> the front end adn NEVER in the database. Why are you sing violating
> 40 years of Software Engineering?
>
Forty years of a life sentence is enough.Time to let the innocent free.
Convicted on trumped up,unsubstantiated and false charges.In other words,
NONSENSE.
The thread:
Monday, April 10, 2006 9:48 PM
microsoft.public.sqlserver.programming
Re: Membership Timeline Spanning
contains a response that further clarifies things:
"Itzik Ben-Gan" writes
>.
>In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
>option). It is really brilliant, and I wonder if the designers of the
>feature themselves knew how profound it is. I believe this option to be the
>bridge between cursors and sets; sort of the holy grail of SQL. :-)
To quote Bob Dylan:
'I would not feel so alone if everyone where getting stoned':)
Yes I agree with you in principal.The 'real' paradign shift has
little to do with the clr and everything to do with exploding
the perverted myth of the exclusivity of'set based' constructs.
The idea one can legitimately think in terms of rows without being
labelled an sql Jodus has arrived.But calling this windowing a
'profound' kind of insight and bestowing on the designers the aura
of 'brilliance' would be a mistake.It is at best an example of
'better late than never'.Calling this state of affairs profound
would surely overshadow the accountability that the commericial
database world should be held to.The fact that this mindset change
has taken almost 30 years should be seen as appalling.Neo-cons of
the industry had hijacked sense with sql creationism and marketing.
WMD was replaced with client/server and a tiered approach.A theory
was misapplied to a retrival mechanism and unapplied to a design
mechanism.An approach that vendors marketted that allowed them to
hide both their intellectual and creative shortcomings.Their db
failures made for the 'client'.And now the clr in the db has replaced
the client.And of course the dreaded cursor.This demanded regime
change and the field was bankrupted for 30 years.For this we are to
praise Ceasar?I think not.
It is interesting to look at the fanfare that vendors are using
to usher in this new paradign.In their documentation Oracle refers
to their analytic functions in windows as an example of
'data densification'.This phrase is supposed to illustrate the
flip side of the Group By.It was obviously borrowed from the idea
of pacification,right out of the Pentagon.This is the best they could
come up with?Any army of engineers berefit of language and concepts.
Not to be out done,MS in its highly touted BOL offers the next best
thing - absolutely Nothing!No explanations,no history no seqways.
The functions are thrown around like so much spaghetti on a wall.
If you write about concepts someone may quote you.MS needn't worry
now.Least I be accused of favortism,IBM was too busy pleasing its
shareholders to write anything intelligible.
Finally,to your point about MS leaving out a large chunk of analytic
material this was obviously not an oversight but just insurance
that anything done with sql-99 could most definitly be easily ported
to the competition.Less is more.Please!If they weren't sure of
what they were doing they could have at least looked at Oracle
which is probably about 8 years ahead.Or even looked at RAC to see what
you and I are really talking about :)
Interested readers maybe surprised that many of the ideas in sql
analytics can be found in the SAS (Statistical Analysis System) Data
Step...introduced about 20 years ago!Many of the Oracle extensions
(First/Last) can also be found here.MySql allows mixing of variables
and columns in a SELECT.Most of the analytics can be easily simulated
in a single SELECT.And of course little RAC, way ahead of its time:)
Some musing from:
www.rac4sql.net

Row Lock On Update Statement

What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,
You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>

Row Lock On Update Statement

What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>

Row Lock On Update Statement

What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>

row limit in MSSQL

There is at present (as we accidently found) limitation of MSSQL to return per row maximally 8060 bytes. Message like this comes: "Cannot create a row of size 8279 which is greater than the allowable maximum of 8060".
My questions are :
- Is there any way how to pass it? If I split into more tables (as I have it now) and ask for result where these tables are connected over any ID the result is the same. If I use stored procedures it seems to be ok. Any other idea?
- Will be this ok in SQL server 2005?There is no way to overcome this limit in SQL 2000 other than to split the one table into multiple tables (as it seems you have already done).

Another workaround might be to take some of your longer varchar fields and convert them into text; however, you will lose some functionality (searching and indexing) if you choose this option.

Regards,

hmscott

There is at present (as we accidently found) limitation of MSSQL to return per row maximally 8060 bytes. Message like this comes: "Cannot create a row of size 8279 which is greater than the allowable maximum of 8060".
My questions are :
- Is there any way how to pass it? If I split into more tables (as I have it now) and ask for result where these tables are connected over any ID the result is the same. If I use stored procedures it seems to be ok. Any other idea?
- Will be this ok in SQL server 2005?|||Actually, the 8060 byte limit is for storing rows in a table. Result set rows can be many times larger than 8060 bytes.

-PatP|||Actually, the 8060 byte limit is for storing rows in a table. Result set rows can be many times larger than 8060 bytes.

-PatP
I seems to be different for me. I have row size in table roughly 5k but I need to connect more tables. Than (during SELECT command) this error comes.|||Does someone know if this will be fixed in MS SQL 2005 ?|||Try this one on for size, it generates lots of 16 Kb+ rows in the result set.
CREATE TABLE Sladky0 (
pk INT IDENTITY PRIMARY KEY (pk)
, junque00 CHAR(255) NOT NULL DEFAULT '00'
, junque01 CHAR(255) NOT NULL DEFAULT '01'
, junque02 CHAR(255) NOT NULL DEFAULT '02'
, junque03 CHAR(255) NOT NULL DEFAULT '03'
, junque04 CHAR(255) NOT NULL DEFAULT '04'
, junque05 CHAR(255) NOT NULL DEFAULT '05'
, junque06 CHAR(255) NOT NULL DEFAULT '06'
, junque07 CHAR(255) NOT NULL DEFAULT '07'
, junque08 CHAR(255) NOT NULL DEFAULT '08'
, junque09 CHAR(255) NOT NULL DEFAULT '09'
, junque0a CHAR(255) NOT NULL DEFAULT '0a'
, junque0b CHAR(255) NOT NULL DEFAULT '0b'
, junque0c CHAR(255) NOT NULL DEFAULT '0c'
, junque0d CHAR(255) NOT NULL DEFAULT '0d'
, junque0e CHAR(255) NOT NULL DEFAULT '0e'
, junque0f CHAR(255) NOT NULL DEFAULT '0f'
)

CREATE TABLE Sladky1 (
pk INT NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
, junque10 CHAR(255) NOT NULL DEFAULT '10'
, junque11 CHAR(255) NOT NULL DEFAULT '11'
, junque12 CHAR(255) NOT NULL DEFAULT '12'
, junque13 CHAR(255) NOT NULL DEFAULT '13'
, junque14 CHAR(255) NOT NULL DEFAULT '14'
, junque15 CHAR(255) NOT NULL DEFAULT '15'
, junque16 CHAR(255) NOT NULL DEFAULT '16'
, junque17 CHAR(255) NOT NULL DEFAULT '17'
, junque18 CHAR(255) NOT NULL DEFAULT '18'
, junque19 CHAR(255) NOT NULL DEFAULT '19'
, junque1a CHAR(255) NOT NULL DEFAULT '1a'
, junque1b CHAR(255) NOT NULL DEFAULT '1b'
, junque1c CHAR(255) NOT NULL DEFAULT '1c'
, junque1d CHAR(255) NOT NULL DEFAULT '1d'
, junque1e CHAR(255) NOT NULL DEFAULT '1e'
, junque1f CHAR(255) NOT NULL DEFAULT '1f'
)

CREATE TABLE Sladky2 (
pk INT NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
, junque20 CHAR(255) NOT NULL DEFAULT '20'
, junque21 CHAR(255) NOT NULL DEFAULT '21'
, junque22 CHAR(255) NOT NULL DEFAULT '22'
, junque23 CHAR(255) NOT NULL DEFAULT '23'
, junque24 CHAR(255) NOT NULL DEFAULT '24'
, junque25 CHAR(255) NOT NULL DEFAULT '25'
, junque26 CHAR(255) NOT NULL DEFAULT '26'
, junque27 CHAR(255) NOT NULL DEFAULT '27'
, junque28 CHAR(255) NOT NULL DEFAULT '28'
, junque29 CHAR(255) NOT NULL DEFAULT '29'
, junque2a CHAR(255) NOT NULL DEFAULT '2a'
, junque2b CHAR(255) NOT NULL DEFAULT '2b'
, junque2c CHAR(255) NOT NULL DEFAULT '2c'
, junque2d CHAR(255) NOT NULL DEFAULT '2d'
, junque2e CHAR(255) NOT NULL DEFAULT '2e'
, junque2f CHAR(255) NOT NULL DEFAULT '2f'
)

CREATE TABLE Sladky3 (
pk INT NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
, junque30 CHAR(255) NOT NULL DEFAULT '30'
, junque31 CHAR(255) NOT NULL DEFAULT '31'
, junque32 CHAR(255) NOT NULL DEFAULT '32'
, junque33 CHAR(255) NOT NULL DEFAULT '33'
, junque34 CHAR(255) NOT NULL DEFAULT '34'
, junque35 CHAR(255) NOT NULL DEFAULT '35'
, junque36 CHAR(255) NOT NULL DEFAULT '36'
, junque37 CHAR(255) NOT NULL DEFAULT '37'
, junque38 CHAR(255) NOT NULL DEFAULT '38'
, junque39 CHAR(255) NOT NULL DEFAULT '39'
, junque3a CHAR(255) NOT NULL DEFAULT '3a'
, junque3b CHAR(255) NOT NULL DEFAULT '3b'
, junque3c CHAR(255) NOT NULL DEFAULT '3c'
, junque3d CHAR(255) NOT NULL DEFAULT '3d'
, junque3e CHAR(255) NOT NULL DEFAULT '3e'
, junque3f CHAR(255) NOT NULL DEFAULT '3f'
)

DECLARE
@.loop INT
, @.pk INT

SET @.loop = 10

WHILE 0 < @.loop
BEGIN
INSERT INTO Sladky0 (junque00) VALUES (DEFAULT)
SET @.pk = @.@.identity
INSERT INTO Sladky1 (pk) VALUES (@.pk)
INSERT INTO Sladky2 (pk) VALUES (@.pk)
INSERT INTO Sladky3 (pk) VALUES (@.pk)
SELECT @.loop = @.loop - 1
END

SELECT *
FROM Sladky0
JOIN Sladky1 ON (Sladky1.pk = Sladky0.pk)
JOIN Sladky2 ON (Sladky2.pk = Sladky0.pk)
JOIN Sladky3 ON (Sladky3.pk = Sladky0.pk)-PatP|||ok , thanks|||I had a bit different SQL command in my application. I have join done over where condition and used DISTINCT in command.
But I found if I use DISTICNT keyword before that this error comes even with your offered SQL command with JOINs. So it seems to me different with DISTINCT or not - but why? So I cannot use keyword distinct for bigger result over tables - I guess.

(
SELECT DISTINCT *
FROM Sladky0
JOIN Sladky1 ON (Sladky1.pk = Sladky0.pk)
JOIN Sladky2 ON (Sladky2.pk = Sladky0.pk)
JOIN Sladky3 ON (Sladky3.pk = Sladky0.pk)
) doesn't work|||Avoid using DISTINCT this way (it implicity creates a table), use a GROUP BY instead.

-PatP|||Does it mean that it is better to mention all columns in GROUP BY statement than use DISCTINCT expression? So I have very long statements. To mention only some of them is not possible I think.|||Listing every returned column in a GROUP BY is the only was I know to avoid creating the work table that is created by DISTINCT.

-PatP|||I have tryied it - it really works fine. Thanks|||I know that it is something of a pain to have to deal with the problem by changing your code, but the designers made some trade off decisions in the query engine that work very well for 99.9% of the queries, but clobber queries like yours. It is annoying to have your code be the "lucky" one that needs to be changed, but at least the change is better than waiting for another database upgrade (at least in most cases)!

-PatP|||I agree with you. I am glad that I have solution for now for MSSQL server.
In our application I try to have as much as possible common code for MySQL (ODBC, native), MSSQL (ODBC), ORACLE(ODBC, native),... but you can imagine a lot of different parts.|||Yukon allows rows to span across multiple pages.

Row ID trigger

Anyone knows how to make a good trigger to create a row id on a table every time a new row is inserted? This is to maintain row id sequence by not setting the row id field as an auto identity which may be in broken sequence after a while.
Thanks.just use something like "select max(rowID)+1" in the update trigger. Although you'll have to careful of your isolation level.|||I tried what you said in INSERT trigger and it did'nt work.
Why?

Thanks.|||What's to know about isolation level?
Thanks.|||If you have a low isolation level then, potentially, two inserts would get the same result for select max()

What "doesn't work"?|||How can the low isolation level be taken care of?

Sorry, I tested the trigger in enterprise manager and at first didn't see the rowid appear until I refreshed the table, which made me believe the trigger wasn't working.

Thanks.|||"which may be in broken sequence after a while. "

Why do you think so, IMHO, it cannot be broken. If you don't like identity you can use
so-called "table with current id's for all tables" and update it through sps or udfs|||You need to have a quick read of the isolation levels. Basically the lower the level the less database locks you have - although that means you have more concurrency. The higher the level the more locks you have so the "safer" code like select max() becomes - but your concurrency drops. You can influence the isolation level using commands such as "SET ISOLATION LEVEL" or by using query hints. If you're using the select max() technique then I'd suggest you look at the UPDLOCK hint.|||Could you explain how to implement a UDF on the rowid field to update it at each insertion? I tried and failed to implement a UDF on the field but succeeded with a trigger.
Thanks.|||>> Could you explain how to implement a UDF on the rowid field to update it at each insertion? I tried and failed to implement a UDF on the field but succeeded with a trigger.

How are you trying to use a UDF? They're just a way of batching SQL, they still need to be called...like a stored procedure. So you'd still need to put them in a trigger.

>> how can they get out of sequence
Easily! Insert, rollback, Insert. Where can I buy a MCSDBA for $2 please?

Row group 'footers' in matrices

I am trying to create a report that will append a calculated row of
percentages to the end of a group of test results. The results are
grouped by 'Category A' and have different subcategories. But
overall, I want to generate percentages for each column in 'Category
A'. The columns of Category A consist of Pass, Fail, and In
Progress. I want to be able to have a percentage of all the passes in
category A, but still have that distinction between a subcategory in
category A.
I have designed the matrix to look like such:
Static
Column
Category A | Sub Category
and I want it to produce a result like such:
Pass Fail In Progress
Category A Sub Category 1
1 3 0
Sub Category 2
3 0 1
Percentage
50% 37.5% 12.5%
Category B Sub Category 1
1 3 0
Sub Category 2
3 0 1
Percentage
50% 37.5% 12.5%
It is easy to format within Crystal Reports, but I have been mashing
my brain all day and I haven't found how to do it within Reporting
Services. HELP.Here are the layouts again. I didn't realize that it was going to get
ruined when it posts.
.Static Column
Category A | SubCategory
.................................P .F .IP
Category A Sub1 3 1 0
..................Sub2 1 2 1
..................Percentage 50% 37.5% 12.5%
Category B Sub1 3 1 0
..................Sub2 1 2 1
..................Percentage 50% 37.5% 12.5%

Row by Row Processing

I am trying to create a procedure within SQL Server 2000 that will update a
table for viewing over our intranet(nightly process). The final output will
show current balances within accounts.
The way the logic works for calculating the accounts is that the accounts
are allocated by years.
Example:
2001 ACCOUNT1
2002 ACCOUNT1
2001 ACCOUNT2
2003 ACCOUNT2
etc.....
Expenses occur over multiple years and must be applied to the earliest
year/account that exists. If the balance of the year/account is zero, then
look at the next year (if it exists) to apply the expense (expenses could be
split between 2 years).
What I have in the SQL server is a table with the accounts and balances, and
a seperate view with the expenditures. I want to take the view and "walk"
through record by record updating the table with current balances. I have
never done this before in SQL server and wonder if it can be done. We would
like to do it in the SQL Server because then the SQL server handles all the
data updating, etc. on it's own and we don't have to worry about an external
process to update this table.
I'm looking for an example, or guidance on what is the best way to perform
this task.
Thanks.You should go for a rowbased solution. YOu didnt post soe DDL, so we
cansee wheter the new data will be stored in existing tables (then you
should use an update) or in a new table (Then you should use ainsert or
select into). perhaps you can give some more information about that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lyners" <Lyners@.discussions.microsoft.com> schrieb im Newsbeitrag
news:742DBEF6-85F1-47B8-8F6C-3F8B668ECDE9@.microsoft.com...
>I am trying to create a procedure within SQL Server 2000 that will update a
> table for viewing over our intranet(nightly process). The final output
> will
> show current balances within accounts.
> The way the logic works for calculating the accounts is that the accounts
> are allocated by years.
> Example:
> 2001 ACCOUNT1
> 2002 ACCOUNT1
> 2001 ACCOUNT2
> 2003 ACCOUNT2
> etc.....
> Expenses occur over multiple years and must be applied to the earliest
> year/account that exists. If the balance of the year/account is zero, then
> look at the next year (if it exists) to apply the expense (expenses could
> be
> split between 2 years).
> What I have in the SQL server is a table with the accounts and balances,
> and
> a seperate view with the expenditures. I want to take the view and "walk"
> through record by record updating the table with current balances. I have
> never done this before in SQL server and wonder if it can be done. We
> would
> like to do it in the SQL Server because then the SQL server handles all
> the
> data updating, etc. on it's own and we don't have to worry about an
> external
> process to update this table.
> I'm looking for an example, or guidance on what is the best way to perform
> this task.
> Thanks.
>|||hi
just look at CURSORs in SQL Server Books Online. U can traverse row-by-row
was this the one u are looking for?
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Lyners" wrote:

> I am trying to create a procedure within SQL Server 2000 that will update
a
> table for viewing over our intranet(nightly process). The final output wil
l
> show current balances within accounts.
> The way the logic works for calculating the accounts is that the accounts
> are allocated by years.
> Example:
> 2001 ACCOUNT1
> 2002 ACCOUNT1
> 2001 ACCOUNT2
> 2003 ACCOUNT2
> etc.....
> Expenses occur over multiple years and must be applied to the earliest
> year/account that exists. If the balance of the year/account is zero, then
> look at the next year (if it exists) to apply the expense (expenses could
be
> split between 2 years).
> What I have in the SQL server is a table with the accounts and balances, a
nd
> a seperate view with the expenditures. I want to take the view and "walk"
> through record by record updating the table with current balances. I have
> never done this before in SQL server and wonder if it can be done. We woul
d
> like to do it in the SQL Server because then the SQL server handles all th
e
> data updating, etc. on it's own and we don't have to worry about an extern
al
> process to update this table.
> I'm looking for an example, or guidance on what is the best way to perform
> this task.
> Thanks.
>|||@.OG: But rather using cursor you should always prefer using rowbased
statements. In common you can say that cursor are slower than rowbased
statements.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chandra" <chandra@.discussions.microsoft.com> schrieb im Newsbeitrag
news:C9B5793C-2D15-48DA-96DC-8CB91DFD4338@.microsoft.com...
> hi
> just look at CURSORs in SQL Server Books Online. U can traverse row-by-row
> was this the one u are looking for?
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Lyners" wrote:
>|||You could use a cursor for this, but cursors on large rowsets can be slow.
It sounds like what you are trying to do could be better implemented by
joining to a sub-query.
"Lyners" <Lyners@.discussions.microsoft.com> wrote in message
news:742DBEF6-85F1-47B8-8F6C-3F8B668ECDE9@.microsoft.com...
> I am trying to create a procedure within SQL Server 2000 that will update
a
> table for viewing over our intranet(nightly process). The final output
will
> show current balances within accounts.
> The way the logic works for calculating the accounts is that the accounts
> are allocated by years.
> Example:
> 2001 ACCOUNT1
> 2002 ACCOUNT1
> 2001 ACCOUNT2
> 2003 ACCOUNT2
> etc.....
> Expenses occur over multiple years and must be applied to the earliest
> year/account that exists. If the balance of the year/account is zero, then
> look at the next year (if it exists) to apply the expense (expenses could
be
> split between 2 years).
> What I have in the SQL server is a table with the accounts and balances,
and
> a seperate view with the expenditures. I want to take the view and "walk"
> through record by record updating the table with current balances. I have
> never done this before in SQL server and wonder if it can be done. We
would
> like to do it in the SQL Server because then the SQL server handles all
the
> data updating, etc. on it's own and we don't have to worry about an
external
> process to update this table.
> I'm looking for an example, or guidance on what is the best way to perform
> this task.
> Thanks.
>|||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.|||The best way to get help with your problem is to post DDL, sample data
and required results. See:
http://www.aspfaq.com/etiquett=ADe.asp?id=3D5006
I doubt that row-by-row processing is the best solution. Pobably you
can do this with an UPDATE or SELECT statement.
--=20
David Portas=20
SQL Server MVP=20
--|||Thanks Jens,
for the update. I was suposed to mention that but clicked send before
mentioning that. I normally suggest people not to use cursors as they consum
e
a lotof time and memory.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Jens Sü?meyer" wrote:

> @.OG: But rather using cursor you should always prefer using rowbased
> statements. In common you can say that cursor are slower than rowbased
> statements.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Chandra" <chandra@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:C9B5793C-2D15-48DA-96DC-8CB91DFD4338@.microsoft.com...
>
>|||It's OK to admit that we all use cursors on occasion. They can be convenient
so long as the rowset is small. A lot of the system stored procedures in
MASTER are implemented using cursors, so we can't avoid them.
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:1101D390-347A-45C3-AA4B-78577F8A4080@.microsoft.com...
> Thanks Jens,
> for the update. I was suposed to mention that but clicked send before
> mentioning that. I normally suggest people not to use cursors as they
consume
> a lotof time and memory.
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Jens Smeyer" wrote:
>
row-by-row
update
output
accounts
earliest
could
balances,
"walk"
have
all|||I got it working... kind of. My problem is the select at the end. it appears
that the @.vchNextExpenseFiscalYearID are being reset. I set the
@.vchNextExpenseFiscalYearID by a select statement, but there is another quer
y
with the same field name (FiscalYear) that pulls into another varable. Does
SQL Server 2000 set a varable so that it is static so it is set to the year
(i.e. 2003), or is my varable (@.vchNextExpenseFiscalYearID) dynamic and set
to the field FiscalYear, so it changes with the field?
Please note that I did not use Cursors or Fetch next. I didn't use cursors
because of the resource use, and I didn't use Fetch Next because I did not
realize there was such a command until I was close to the end.
here is my code:
CREATE PROCEDURE dbo.loadunspentbonds AS
delete from unspentbonds
insert into unspentbonds(fiscalyear, project, subproject, bondamount,
UnspentBond) select fiscalyear, project, subproject, bondamount, bondamount
from vwUnspentBondsbudgetprior2005
insert into unspentbonds(fiscalyear, project, subproject, bondamount,
UnspentBond) select fiscalyear, project, subproject, bondamount, bondamount
from vwUnspentBondsbudgetafter2004
/* Update the Unspent Bond table with Expenditures and the current bond amou
nt
** We use a row by row processing to achieve our results because expenditure
s
** go back to the first year of the bonds
*/
SET NOCOUNT ON
-- declare all variables!
DECLARE @.iReturnCode int,
@.vchNextProjectID nvarchar(5),
@.vchNextSubProjectID nvarchar(2),
@.vchNextFiscalYearID nvarchar(4),
@.vchCurrentProjectID nvarchar(5),
@.vchCurrentSubProjectID nvarchar(2),
@.vchCurrentFiscalYearID nvarchar(4),
@.iExpenseLoopControl int,
@.vchNextExpenseProjectID nvarchar(5),
@.vchNextExpenseSubProjectID nvarchar(2),
@.vchNextExpenseFiscalYearID nvarchar(4),
@.vchCurrentExpenseProjectID nvarchar(5),
@.vchCurrentExpenseSubProjectID nvarchar(2),
@.vchCurrentExpenseFiscalYearID nvarchar(4),
@.fltGLExpendedAmount float,
@.fltUnspentBondAmount float
-- Initialize variables
SELECT @.iExpenseLoopControl = 1
SELECT TOP 1 @.vchNextExpenseProjectID = Project,
@.vchNextExpenseSubProjectID = SubProject,
@.vchNextExpenseFiscalYearID = FiscalYear
FROM [CapitalFinance].[dbo]. [vwUnspentBondsDailyExpendituresAfter200
4]
-- Make sure the table has data
if isnull(@.vchNextExpenseProjectID,'') = ''
BEGIN
RETURN
END
-- Retrieve the first Unspent Bond Row
SELECT TOP 1 @.vchCurrentExpenseProjectID = Project,
@.vchCurrentExpenseSubProjectID = SubProject,
@.vchCurrentExpenseFiscalYearID =
FiscalYear,
@.fltGLExpendedAmount = GLExpended
FROM [CapitalFinance].[dbo]. [vwUnspentBondsDailyExpendituresAfter200
4]
WHERE Project = @.vchNextExpenseProjectID and
SubProject = @.vchNextExpenseSubProjectID and
FiscalYear = @.vchNextExpenseFiscalYearID
WHILE @.iExpenseLoopControl = 1
BEGIN
-- Begin the nested(inner) loop.
-- Get the first Unspent Bond for the current Expense Record
SELECT @.vchNextProjectID = Project,
@.vchNextSubProjectID = SubProject,
@.vchNextFiscalYearID = FiscalYear
FROM [CapitalfINANCE].[dbo].[UnspentBonds]
WHERE Project = @.vchCurrentExpenseProjectID and
SubProject = @.vchCurrentExpenseSubProjectID
--make sure that the Unspent Bond exists
if isnull(@.vchNextProjectID,"") <> ""
BEGIN
WHILE @.vchNextProjectID = @.vchCurrentExpenseProjectID and
@.vchNextSubProjectID = @.vchCurrentExpenseSubProjectID
BEGIN
-- Get the first Unspent Bond for the current Expense Record
SELECT @.vchCurrentProjectID = Project,
@.vchCurrentSubProjectID = SubProject,
@.vchCurrentFiscalYearID = FiscalYear,
@.fltUnspentBondAmount = UnspentBond
FROM [CapitalfINANCE].[dbo].[UnspentBonds]
WHERE Project = @.vchNextProjectID and
SubProject = @.vchNextSubProjectID and
FiscalYear = @.vchNextFiscalYearID
IF @.fltGLExpendedAmount < @.fltUnspentBondAmount
BEGIN
UPDATE [CapitalfINANCE].[dbo].[UnspentBonds]
SET unspentBond = @.fltUnspentBondAmount - @.fltGLExpendedAmount,
ExpenditureAmount = ExpenditureAmount +
@.fltGLExpendedAmount
WHERE Project = @.vchCurrentProjectID and
SubProject = @.vchCurrentSubProjectID and
FiscalYear = @.vchCurrentFiscalYearID
SELECT @.fltGLExpendedAmount = 0
END
ELSE
BEGIN
SELECT @.fltGLExpendedAmount = @.fltGLExpendedAmount -
@.fltUnspentBondAmount
UPDATE [CapitalfINANCE].[dbo].[UnspentBonds]
SET unspentBond = 0,
ExpenditureAmount = ExpenditureAmount + @.fltUnspentBondAmount
WHERE Project = @.vchCurrentProjectID and
SubProject = @.vchCurrentSubProjectID and
FiscalYear = @.vchCurrentFiscalYearID
END
SELECT @.vchNextProjectID = Project,
@.vchNextSubProjectID = SubProject,
@.vchNextFiscalYearID = FiscalYear
FROM [CapitalfINANCE].[dbo].[UnspentBonds]
WHERE Project + SubProject + FiscalYear > @.vchNextProjectID +
@.vchNextSubProjectID + @.vchNextFiscalYearID
END
END
SELECT TOP 1 @.vchNextExpenseProjectID = Project,
@.vchNextExpenseSubProjectID = SubProject,
@.vchNextExpenseFiscalYearID = FiscalYear
FROM [CapitalFinance].[dbo]. [vwUnspentBondsDailyExpendituresAfter200
4]
WHERE Project + SubProject + FiscalYear >
@.vchCurrentExpenseProjectID + @.vchCurrentExpenseSubProjectID +
@.vchCurrentExpenseFiscalYearID
BEGIN
-- Make sure the table has data
if isnull(@.vchnextExpenseProjectID,"") = ""
BEGIN
BREAK
END
SELECT @.vchCurrentExpenseProjectID = Project,
@.vchCurrentExpenseSubProjectID = SubProject,
@.vchCurrentExpenseFiscalYearID = FiscalYear,
@.fltGLExpendedAmount = GLExpended
FROM [CapitalFinance].[dbo]. [vwUnspentBondsDailyExpendituresAfter200
4]
WHERE Project = @.vchNextExpenseProjectID and
SubProject = @.vchNextExpenseSubProjectID and
FiscalYear = @.vchNextExpenseFiscalYearID
END
RETURN
GO
"Lyners" wrote:

> I am trying to create a procedure within SQL Server 2000 that will update
a
> table for viewing over our intranet(nightly process). The final output wil
l
> show current balances within accounts.
> The way the logic works for calculating the accounts is that the accounts
> are allocated by years.
> Example:
> 2001 ACCOUNT1
> 2002 ACCOUNT1
> 2001 ACCOUNT2
> 2003 ACCOUNT2
> etc.....
> Expenses occur over multiple years and must be applied to the earliest
> year/account that exists. If the balance of the year/account is zero, then
> look at the next year (if it exists) to apply the expense (expenses could
be
> split between 2 years).
> What I have in the SQL server is a table with the accounts and balances, a
nd
> a seperate view with the expenditures. I want to take the view and "walk"
> through record by record updating the table with current balances. I have
> never done this before in SQL server and wonder if it can be done. We woul
d
> like to do it in the SQL Server because then the SQL server handles all th
e
> data updating, etc. on it's own and we don't have to worry about an extern
al
> process to update this table.
> I'm looking for an example, or guidance on what is the best way to perform
> this task.
> Thanks.
>

Rounding Calculated Member

I have the following calculated member that is being used to create Offline/Local cubes.

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage]

AS Case

When IsEmpty( [Measures].[Event Count] )

Then 0

Else ((

[EE Event Template Type Dim].[Event Types].CurrentMember,

[Measures].[Event Count]) /

( [EE Event Template Type Dim].[Event Types].[(All)].[All],

[Measures].[Event Count]

)*100)

End,

FORMAT_STRING = "###.##%",

BACK_COLOR = 12632256 /*Silver*/ ,

FORE_COLOR = 16744576 /*R=128, G=128, B=255*/ ,

VISIBLE = 1 ;

I'm looking for a way to round the result. The FORMAT_STRING functionality does not carry over when I create the local cube so I end up with values like 28.90909097.

Any suggestions would be appreciated.

Tristan

Tristan,

Have you tried the "format" function?

Format(

Case

When IsEmpty( [Measures].[Event Count] )

Then 0

... rest of case statement from original calculation

,"###.##%")

|||That did the trick! Much Thanks!

Rounding and Grouping

Perhaps someone can settle an arguement for me ?

I have a set of data that I need to group together. SQL Script below.

CREATE TABLE [dbo].[CommTransactions] (
[ID] [id_type] NOT NULL ,
[TransactionID] [id_type] NULL ,
[ClientID] [id_type] NULL ,
[AccountCode] [varchar] (10) NULL ,
[Amount] [float] NULL ,
[CreateDateTime] [datetime] NULL

For the records I want to group the following applies.

The ID is unique and distinct.
The TransactionId is the same.
The ClientId is the same.
The AccountCode is different.
The Amount will be the same.
The CreateDateTime field is different by a few milliseconds.

I want to create a single line showing two account codes in different
fields. i.e. Staff and Manager (where their ID is the account code).

These can be entered in any order in the table mentioned.

The problem I have is I need to link two records together (that's the
problem in it's most simplistic terms). However, there may be
additional records with the same TransactionId, ClientId, AccountCode
and Amount, but happened at a slightly different time. It could be
done on the same day.

Now, the arguement is that we can group using the CreateDateTime
field. I argue that we can't as it will show down to the millisecond
and any rounding will not always allow for a match. If we added the
matching records once per day, then I can extract the date and group
on it, but if more than one group is added per day, then this would
cause the logic to fail.

So, are there any reliable methods for grouping date/time fields
reliably if there is a small difference (I suspect not)?

Is there anything I have missed ?

Any help or suggestions would be appreciated.

Thanks

RyanRyan,
Forgive me if I am not understanding the question correctly.
But I think the answer is that you don't have to group on a field; you
can group on an expression in most cases.
In this case, you can probably group by
convert(varchar,CreateDateTime,101), which is the date portion of
CreateDateTime.
I hate to suggest this because the performance will probably be terrible
unless your WHERE clause if very specific, but it may be the quick fix you
are looking for.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"Ryan" <ryanofford@.hotmail.com> wrote in message
news:7802b79d.0402020100.41141655@.posting.google.c om...
> Perhaps someone can settle an arguement for me ?
> I have a set of data that I need to group together. SQL Script below.
> CREATE TABLE [dbo].[CommTransactions] (
> [ID] [id_type] NOT NULL ,
> [TransactionID] [id_type] NULL ,
> [ClientID] [id_type] NULL ,
> [AccountCode] [varchar] (10) NULL ,
> [Amount] [float] NULL ,
> [CreateDateTime] [datetime] NULL
> For the records I want to group the following applies.
> The ID is unique and distinct.
> The TransactionId is the same.
> The ClientId is the same.
> The AccountCode is different.
> The Amount will be the same.
> The CreateDateTime field is different by a few milliseconds.
> I want to create a single line showing two account codes in different
> fields. i.e. Staff and Manager (where their ID is the account code).
> These can be entered in any order in the table mentioned.
> The problem I have is I need to link two records together (that's the
> problem in it's most simplistic terms). However, there may be
> additional records with the same TransactionId, ClientId, AccountCode
> and Amount, but happened at a slightly different time. It could be
> done on the same day.
> Now, the arguement is that we can group using the CreateDateTime
> field. I argue that we can't as it will show down to the millisecond
> and any rounding will not always allow for a match. If we added the
> matching records once per day, then I can extract the date and group
> on it, but if more than one group is added per day, then this would
> cause the logic to fail.
> So, are there any reliable methods for grouping date/time fields
> reliably if there is a small difference (I suspect not)?
> Is there anything I have missed ?
> Any help or suggestions would be appreciated.
> Thanks
> Ryan|||Ryan (ryanofford@.hotmail.com) writes:
> Now, the arguement is that we can group using the CreateDateTime
> field. I argue that we can't as it will show down to the millisecond
> and any rounding will not always allow for a match. If we added the
> matching records once per day, then I can extract the date and group
> on it, but if more than one group is added per day, then this would
> cause the logic to fail.
> So, are there any reliable methods for grouping date/time fields
> reliably if there is a small difference (I suspect not)?

I'm not sure that I follow, but it sounds to me more like a business
problem.

You can group by the hour for instance:

SELECT yadadada, d, COUNT(*)
FROM (SELECT yadayada,
d = convert(char(8), CreateDateTime, 112) +
convert(char(5), CreateDateTime, 108)
FROM ...) AS a
GROUP BY yadayada, d

Of course, is a group is inserted so that some rows are inserted before
one o'clock, and others after you lose. Likewise, if two groups are
inserted the same hour.

A more complicated scheme may be devised where you compute the time
between two inserted rows, and if the difference is > some value,
those are two groups.

But you probably get a lot more robust application, by introducing a
marker which is unique for every batch you insert. This could still
be a datetime value, you just need to make sure that all rows in the
same batch gets the the same value.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yep, pretty much as I suspected. Unfortunately this table is one
supplied by another company so I can't change it as easily as I want
without affecting their app. Our users expectation differs from what
this package does hence the problem.

I want the other company to change this slightly and there will be a
cost (fair enough), only problem is our company doesn't want to pay
for it. So, I'm trying to provide them with everything to prove they
either pay for the change or accept it won't work. They would rather
my team spend several days (at God knows what cost) examining
something I know won't work instead of paying for a days worth of
development.

Daft.

As you have guessed, I'm trying to steer them down the route of a
marker that I can group on.

Thanks for the help.

Ryan

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94843E9F3EA3Yazorman@.127.0.0.1>...
> Ryan (ryanofford@.hotmail.com) writes:
> > Now, the arguement is that we can group using the CreateDateTime
> > field. I argue that we can't as it will show down to the millisecond
> > and any rounding will not always allow for a match. If we added the
> > matching records once per day, then I can extract the date and group
> > on it, but if more than one group is added per day, then this would
> > cause the logic to fail.
> > So, are there any reliable methods for grouping date/time fields
> > reliably if there is a small difference (I suspect not)?
> I'm not sure that I follow, but it sounds to me more like a business
> problem.
> You can group by the hour for instance:
> SELECT yadadada, d, COUNT(*)
> FROM (SELECT yadayada,
> d = convert(char(8), CreateDateTime, 112) +
> convert(char(5), CreateDateTime, 108)
> FROM ...) AS a
> GROUP BY yadayada, d
> Of course, is a group is inserted so that some rows are inserted before
> one o'clock, and others after you lose. Likewise, if two groups are
> inserted the same hour.
> A more complicated scheme may be devised where you compute the time
> between two inserted rows, and if the difference is > some value,
> those are two groups.
> But you probably get a lot more robust application, by introducing a
> marker which is unique for every batch you insert. This could still
> be a datetime value, you just need to make sure that all rows in the
> same batch gets the the same value.|||I have another thought that is worth a go. A slightly unusual approach
I must admit, but I think it may work.

I can establish the initial line that I want and take the
CreateDateTime from that. If I then add 1 minute to give me a start
time. Then subtract 1 minute to give me an end time, I can create a
table which holds the various ID fields, the accountcode I need and
the start and end times of a group.

I then use another query to pull out the second accountcode I want and
use a left join to the table I created previously, joining where the
createdatetime is between the start and end date. I add the
accountcode from the first table as a new field on the end of the
results of this query.

It means that the system will have a 2 minute window to commit the
transactions. Normally this is a few seconds, but I can adjust my
window.

I'll have to do some work checking where this can fail though, but
it's worth a little time doing this.

Feel free to pull this apart so I can check how well it will work.

Round date on a chart

Hello!
I'm collecting performance counters from few servers.
They are collecting every 10 minutes. After that i need to create
day-report.But when i put "processor use" on Y and time on X, on X i've got
a forest of small dates.
I need only fuul hours (8, 10, 12 etc). How can i do this?
I;m thinking about round it, but what syntax is for it?
And where to type it?
LeszekUse Date.Parse
Andy Potter|||I think that there is a problem with only showing that dates.
When i put (under chart) a Computername, then i have information about
processor for each computer (labels with computername exists).
But when i put date there, i see only my text (without dates).
Where can be a problem?
Type conversion?
I just need to display hours...
Uzytkownik "Potter" <drewpotter@.gmail.com> napisal w wiadomosci
news:1137425277.468665.280170@.g14g2000cwa.googlegroups.com...
> Use Date.Parse
> Andy Potter
>|||I'm not really following you. You're concatenating text and a datetime
value? Have you tried doing a .ToString on your date value?
Andy Potter|||If I get you right, you have too many datas for a day and you only want to
"print" a label in the chart every hour, not every 10 minutes. I had a
similar situation way back, and the date/time labels at the bottom of the
charts got very small and unreadable.
My first try was to show only every 6th label by leaving 5 blank and only
show 1, but Reporting Services fully drawn the blank labels over my real ones
which were unreadable as well. Finally I create the average of the 6 values
on the SQL server and used this aggregate for displaying (To catch suspicious
values I also added a min/max value as well).
The SQL looked like:
select min(Value) as minValue, max(Value) as maxValue, avg(Value) as avgValue
, min ( cast(floor(cast(tmDate as float) * 24) / 24 as smalldatetime) ) as
tmDate
from
(
select Value, tmDate from tabMeasures where tmDate between @.tmStart and
@.tmEnd
)
group by datepart(hour, tmDate)
To hide the seconds in the chart I used to modify the label field from
=Fields!tmDate.Value
to
=Fields.tmDate.Value.ToShortTimeString()
"Leszek" wrote:
> Hello!
> I'm collecting performance counters from few servers.
> They are collecting every 10 minutes. After that i need to create
> day-report.But when i put "processor use" on Y and time on X, on X i've got
> a forest of small dates.
> I need only fuul hours (8, 10, 12 etc). How can i do this?
> I;m thinking about round it, but what syntax is for it?
> And where to type it?
> Leszek
>
>|||Now i have different problem:
When i see my report, i see only one date. Other are like 38748, 37568 etc.
It looks like converted date to other type of values?
Sometimes i see two dates and other date values are 38732, 38768 :/
Where could be a problem?
And my other problem - how can i dynamically generate report, where i will
get as much charts, as i have comupters in my database.column?
U¿ytkownik "GreyMana" <GreyMana@.discussions.microsoft.com> napisa³ w
wiadomo¶ci news:D4821781-CE04-4520-9070-4CA661F877AE@.microsoft.com...
> If I get you right, you have too many datas for a day and you only want to
> "print" a label in the chart every hour, not every 10 minutes. I had a
> similar situation way back, and the date/time labels at the bottom of the
> charts got very small and unreadable.
> My first try was to show only every 6th label by leaving 5 blank and only
> show 1, but Reporting Services fully drawn the blank labels over my real
> ones
> which were unreadable as well. Finally I create the average of the 6
> values
> on the SQL server and used this aggregate for displaying (To catch
> suspicious
> values I also added a min/max value as well).
> The SQL looked like:
> select min(Value) as minValue, max(Value) as maxValue, avg(Value) as
> avgValue
> , min ( cast(floor(cast(tmDate as float) * 24) / 24 as smalldatetime) ) as
> tmDate
> from
> (
> select Value, tmDate from tabMeasures where tmDate between @.tmStart and
> @.tmEnd
> )
> group by datepart(hour, tmDate)
> To hide the seconds in the chart I used to modify the label field from
> =Fields!tmDate.Value
> to
> =Fields.tmDate.Value.ToShortTimeString()
>
> "Leszek" wrote:
>> Hello!
>> I'm collecting performance counters from few servers.
>> They are collecting every 10 minutes. After that i need to create
>> day-report.But when i put "processor use" on Y and time on X, on X i've
>> got
>> a forest of small dates.
>> I need only fuul hours (8, 10, 12 etc). How can i do this?
>> I;m thinking about round it, but what syntax is for it?
>> And where to type it?
>> Leszek
>>

Rotate report 180 degrees

Is it possible to rotate a report 180 degrees? I need to create a report
that prints on a form and the report need to be rotated 180 degrees for it
to work.
Thanks.On Jun 15, 2:45 pm, "Tim Kelley" <tkel...@.company.com> wrote:
> Is it possible to rotate a report 180 degrees? I need to create a report
> that prints on a form and the report need to be rotated 180 degrees for it
> to work.
> Thanks.
As far as I know, there is not. Sorry that I could not be of greater
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Jun 15, 3:45 pm, "Tim Kelley" <tkel...@.company.com> wrote:
> Is it possible to rotate a report 180 degrees? I need to create a report
> that prints on a form and the report need to be rotated 180 degrees for it
> to work.
> Thanks.
I have had success in reproducing an entire form in the report
designer (with boxes, labels, and images), so that we did not need to
print to special paper stock.
If you need to print to specialized forms, I think what you are asking
is possible, but will require post-processing work that will take some
serious investigation and development work.
Two possible approaches -
1) Render the reports in Image format, then call an image processing
program to 'flip' and print the image. Obviously, this will be easier
if you are doing a one-page report, but it sounds like you might be,
from your description. You might be able to do this with native VB.NET
or C# capabilities, or with an imaging add-in.
Note that you can save your rendered report files to some kind of
output folder, and use that as an input folder for the post processing
operations.
If this is an infrequent need, you could manually flip the output
files in Paint. In any case there would be some trial and error to get
the registration correct.
2) Render the report to PDF (Acrobat), and then find an extension or
component that will let you flip the PDF pages. If you have the full
Acrobat application (not just the reader), you may be able to do this
with Acrobat. The advantage here would be smaller output files and
better output rendering.|||Isn't 180 degrees equivalent to printing it upside down?
Do you mean 90 degrees?
If so, surely you can simply change the PageSize in the report
properties from 8.5x11 to 11x8.5 and it will rotate it from portrait
to landscape...
You also need to change the InteractiveSize to correspond to the size
of the actual report width and then put 8.5 as the height.