rownumbering w tsql lost to ADO loop -

Hello
Thanks to all who have been helping me to fight a battel
with tsql for rownumbering of my tables. I humbly admit
defeat for today. I had to throw the towel in and whipped
up an ADO loop to number some rows in a table. That took
me all of 3 minutes, if that. I wrestled with tsql all
day long. Well, here is one more shot at tsql.
create table t (rownum int, area char(1), yearnum int,
monthnum int, areaNum int, productNum int)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 3)
this yields
rownum area yearnum monum areanum prodnum
NULL A 2003 1 1 1
NULL A 2003 1 1 2
NULL A 2003 1 1 3
NULL B 2003 1 2 1
NULL B 2003 1 2 2
NULL B 2003 1 2 3
NULL C 2003 1 3 1
NULL C 2003 1 3 2
NULL C 2003 1 3 3
NULL A 2003 2 1 1
NULL A 2003 2 1 2
NULL A 2003 2 1 3
NULL B 2003 2 2 1
NULL B 2003 2 2 2
NULL B 2003 2 2 3
NULL C 2003 2 3 1
NULL C 2003 2 3 2
NULL C 2003 2 3 3
I need to number these rows in the order of
yearNum, MonthNum, areaNum, productNum
select * from t order by yearnum, monthnum, areanum,
productnum
how can I number the rownum column starting at 1 using
tsql?
Thanks in advance,
RonDo:
UPDATE t
SET rownum = ( SELECT COUNT(*)
FROM t t1
WHERE CAST( t1.yearnum AS VARCHAR) +
RIGHT( '0' + CAST( t1.monthnum AS VARCHAR), 2 ) +
CAST( t1.areaNum AS VARCHAR) +
CAST( t1.productNum AS VARCHAR) <=
CAST( t.yearnum AS VARCHAR) +
RIGHT( '0' + CAST( t.monthnum AS VARCHAR), 2 ) +
CAST( t.areaNum AS VARCHAR) +
CAST( t.productNum AS VARCHAR) )
WHERE rownum IS NULL ;
Btw, do you really need a reason to have such a column in the table.
Generally, it is a good idea to do such derivations while extracting the
data from the table or to use a view with the ranking expression.
Anith|||thanks for your reply. This worked perfectly.
And to answer your question if I really need this
column? Truthfully, no. I don't really need it. Well,
for my purposes, this column facilitates reporting and ad-
hoc queries for data analysts in my dept at the
workplace. My real problem was that I did not know how
to achieve this with t-sql. I was aware of using the
self join, but I did not know that you had to cast int
values as varchars to accomplish this numerating.
I believe that my exercise for today will be very
valuable in the future.
Thanks for your help,
Ron

>--Original Message--
>Do:
>UPDATE t
> SET rownum = ( SELECT COUNT(*)
> FROM t t1
> WHERE CAST( t1.yearnum AS VARCHAR) +
> RIGHT( '0' + CAST( t1.monthnum
AS VARCHAR), 2 ) +
> CAST( t1.areaNum AS VARCHAR) +
> CAST( t1.productNum AS VARCHAR)
<=
> CAST( t.yearnum AS VARCHAR) +
> RIGHT( '0' + CAST( t.monthnum
AS VARCHAR), 2 ) +
> CAST( t.areaNum AS VARCHAR) +
> CAST( t.productNum AS VARCHAR) )
> WHERE rownum IS NULL ;
>Btw, do you really need a reason to have such a column
in the table.
>Generally, it is a good idea to do such derivations
while extracting the
>data from the table or to use a view with the ranking
expression.
>--
>Anith
>
>.
>|||One more question if I may. I hope this isn't too
ignorant sounding, but...
I noticed a semi colon at the end of your code. Does
Enterprise Manager have a wizard that can generate the t-
sql for something like this? Man, I sure hope so. It
would sure save me a lot of heartache.

>AS VARCHAR), 2 ) +
VARCHAR)
><=
>AS VARCHAR), 2 ) +
VARCHAR) )
>in the table.
>while extracting the
>expression.
>.
>|||>> Does Enterprise Manager have a wizard that can generate the t-sql for
No, they are optional. You need not worry about it.
Btw, for the problem you posted, instead of concatenating the columns you
could do a series of OR-ed correlations like:
WHERE t1.yearnum <= t.yearnum
OR ( t1.yearnum = t.yearnum
AND t1.t1.monthnum <= t.t1.monthnum )
OR ( t1.yearnum = t.yearnum
AND t1.monthnum = t.t1.monthnum
AND t1.areaNum <= t.areaNum )
OR ( t1.yearnum = t.yearnum
AND t1.monthnum = t.t1.monthnum
AND t1.areaNum = t.areaNum
AND t1.productNum <= t.productNum )
Anith|||Thanks again for this explanation. I guess I will just
have to keep experimenting. This other method did work.

>--Original Message--
generate the t-sql for
>No, they are optional. You need not worry about it.
>Btw, for the problem you posted, instead of
concatenating the columns you
>could do a series of OR-ed correlations like:
>WHERE t1.yearnum <= t.yearnum
> OR ( t1.yearnum = t.yearnum
> AND t1.t1.monthnum <= t.t1.monthnum )
> OR ( t1.yearnum = t.yearnum
> AND t1.monthnum = t.t1.monthnum
> AND t1.areaNum <= t.areaNum )
> OR ( t1.yearnum = t.yearnum
> AND t1.monthnum = t.t1.monthnum
> AND t1.areaNum = t.areaNum
> AND t1.productNum <= t.productNum )
>--
>Anith
>
>.
>

Rowguid Column at the end of my Tables

Hello,
There is a "rowguid" column that was added to the end of my tables, and I
have no clue when and why it was added. Actually i had a Merge Replication on
this database and i removed it. I don't know if it was generated by SQL
Server for Merge Replication or something else.
Ant idea ???
Thanks so much
Mathew
Matthew,
this column is essential (actually a column with the rowguid property) to
merge replication. Removing replication will not remove these columns -
you'll need to do it by hand or craft a script. Be sure however that the
developers haven't coded to include this column.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thanks so much for your helps. I know how to remove this column either on
the Enterprise Manager or by scripts using sysobject to get the table name
and remove this column, but is there any easier ways or scripts that you know?
I appreciate so much.
Mathew
"Paul Ibison" wrote:

> Matthew,
> this column is essential (actually a column with the rowguid property) to
> merge replication. Removing replication will not remove these columns -
> you'll need to do it by hand or craft a script. Be sure however that the
> developers haven't coded to include this column.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Rowguid

Rowguid column ( globally unique identifier) was added to all tables in the
database. What may cause that?
Thanks for help in advance."Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:%237NUmMVVEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Rowguid column ( globally unique identifier) was added to all tables in
the
> database. What may cause that?
>
Did you enable SQL Server replication?
Steve|||I inabled replication and it hosed a production db. We are still having tro
uble updating. I disabled and dropped the distribution DB. We then found a
new column created "rowguid" that hosed my db. The latest message is...
Server message:Cannot insert the value NULL into column ", table 'Goldmine_C
ommon_DB.dbo.CONTACT2; column does not allow nul.
I have checked and all tables in our Common DB are correct for "allow nulls"
. I ran a script and the rowguid is no longer associated with the CONTACT2
table or syscolumns.
Please HELP>
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||Hi
You now have one more column than your inserts are expecting. If you have
removed replication, you can remove the column "rowguid".
Thne your code will work again
Regards
Mike
"SqlJunkies User" wrote:

> I inabled replication and it hosed a production db. We are still having t
rouble updating. I disabled and dropped the distribution DB. We then found
a new column created "rowguid" that hosed my db. The latest message is...
> Server message:Cannot insert the value NULL into column ", table 'Goldmine
_Common_DB.dbo.CONTACT2; column does not allow nul.
> I have checked and all tables in our Common DB are correct for "allow null
s". I ran a script and the rowguid is no longer associated with the CONTACT
2 table or syscolumns.
> Please HELP>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine s
upports Post Alerts, Ratings, and Searching.
>

Rowguid

Rowguid column ( globally unique identifier) was added to all tables in the
database. What may cause that?
Thanks for help in advance.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:%237NUmMVVEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Rowguid column ( globally unique identifier) was added to all tables in
the
> database. What may cause that?
>
Did you enable SQL Server replication?
Steve
|||I inabled replication and it hosed a production db. We are still having trouble updating. I disabled and dropped the distribution DB. We then found a new column created "rowguid" that hosed my db. The latest message is...
Server message:Cannot insert the value NULL into column ", table 'Goldmine_Common_DB.dbo.CONTACT2; column does not allow nul.
I have checked and all tables in our Common DB are correct for "allow nulls". I ran a script and the rowguid is no longer associated with the CONTACT2 table or syscolumns.
Please HELP>
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Hi
You now have one more column than your inserts are expecting. If you have
removed replication, you can remove the column "rowguid".
Thne your code will work again
Regards
Mike
"SqlJunkies User" wrote:

> I inabled replication and it hosed a production db. We are still having trouble updating. I disabled and dropped the distribution DB. We then found a new column created "rowguid" that hosed my db. The latest message is...
> Server message:Cannot insert the value NULL into column ", table 'Goldmine_Common_DB.dbo.CONTACT2; column does not allow nul.
> I have checked and all tables in our Common DB are correct for "allow nulls". I ran a script and the rowguid is no longer associated with the CONTACT2 table or syscolumns.
> Please HELP>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>

Rowcounts on all tables in a DB

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

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

Rowcounts on all tables in a DB

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

Rowcount in tables

Can someone throw light on how to get the rowcount of a table that is stored in any system tables? I want to get tablename and rowcount for all user tables in a database in a query. Is there anyway other than count(*)?
Thanks
VinnieDBCC UPDATEUSAGE (0) WITH COUNT_ROWS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_24rp.asp)

SELECT rows, Object_name(id)
FROM dbo.sysindexes
WHERE indid IN (0, 1)If you skip the DBCC, you'll get a SWAG guess, but none too accurate in a busy server.

-PatP|||sysindexes has a rowcount, see BOL for more details on this.

EDIT: sniped again!|||Thanks guys.|||Check for sp_spaceused. It may help you.|||Here's my little addtion to the pile: (built on MSSQL2k)

DECLARE @.MinSize dec(28,2), @.LikeName varchar(45), @.SizeSort bit, @.IncludeLogs bit
--DBCC UPDATEUSAGE(0) WITH COUNT_ROWS

SET @.MinSize = .00
SET @.LikeName = '' --Company%'
SET @.SizeSort = 1
SET @.IncludeLogs = 0

/*
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)

** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
IF @.LikeName = '' SET @.LikeName = '%'

CREATE TABLE #space
(
name varchar(30),
id int,
type char(1),
rows int NULL,
reserved dec(28,2) NULL,
data dec(28,2) NULL,
datapages dec(28,2) NULL,
blob dec(28,2) NULL,
indexp dec(28,2) NULL,
unused dec(28,2) NULL,
rowsize dec(28,2) NULL
)

SET NOCOUNT ON

/************************************************** ***********
** Generate a list of all User and System tables.
** AND
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
INSERT INTO #space (name, id, type, reserved)
SELECT LEFT(obj.name,30), obj.id, obj.type, SUM(reserved)
FROM sysindexes idx, sysobjects obj
WHERE idx.id = obj.id
AND idx.indid IN (0, 1, 255)
AND obj.type in ('S','U')
AND obj.name != 'syslogs'
AND obj.name LIKE @.LikeName
AND (@.IncludeLogs = 1 OR obj.name NOT LIKE '%Log')
GROUP BY obj.name, obj.id, obj.type

/************************************************** ***********
** Initialize these to zero.
*/
UPDATE #space
SET rows = 0, data = 0, blob = 0, indexp = 0, unused = 0

/************************************************** ***********
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
UPDATE #space
SET data = data + ISNULL((
SELECT SUM(idx.dpages)
FROM sysindexes idx
WHERE id = spc.id
AND idx.indid < 2), 0)
FROM #space spc

UPDATE #space
SET blob = blob + ISNULL((
SELECT SUM(idx.used)
FROM sysindexes idx
WHERE id = spc.id
AND idx.indid = 255), 0)
FROM #space spc

/************************************************** ***********
** index: sum(used) where indid in (0, 1, 255) - data space
*/
UPDATE #space
SET indexp = ISNULL((
SELECT SUM(idx.used)
FROM sysindexes idx
WHERE id = spc.id
AND idx.indid IN (0, 1, 255)), 0) - data - blob
FROM #space spc

/************************************************** ***********
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
UPDATE #space
SET unused = reserved - ISNULL((
SELECT SUM(idx.used)
FROM sysindexes idx
WHERE id = spc.id
AND idx.indid IN (0, 1, 255)), 0)
FROM #space spc

/************************************************** ***********
** rows: rows where indid < 2
*/
UPDATE #space
SET rows = idx.rows
FROM #space spc, sysindexes idx
WHERE spc.id = idx.id
AND idx.indid < 2

/************************************************** ***********
** Page Size: for Windows NT
*/
DECLARE @.PageSize int, @.UsablePage int
SELECT @.PageSize = low
FROM master.dbo.spt_values
WHERE number = 1 AND type = 'E'

set @.UsablePage = @.PageSize - 132

/************************************************** ***********
** Compute the results
*/
UPDATE #space SET
reserved = reserved * @.PageSize,
datapages = data,
data = data * @.PageSize,
blob = blob * @.PageSize,
indexp = indexp * @.PageSize,
unused = unused * @.PageSize

UPDATE #space SET
rowsize =
CASE
WHEN rows < 50 THEN -1
ELSE ((data) / rows)
END

UPDATE #space SET
reserved = reserved / 1024.0 / 1024.0,
data = data / 1024.0 / 1024.0,
blob = blob / 1024.0 / 1024.0,
indexp = indexp / 1024.0 / 1024.0,
unused = unused / 1024.0 / 1024.0

/************************************************** ***********
** Finally: output the report header

*/

PRINT GETDATE()
PRINT ''

IF @.MinSize != 0
PRINT 'Tables with a Total space used of ' + LTRIM(STR(@.MinSize, 8,1)) + ' MB or greater in the ' + DB_NAME() + ' database.'
ELSE
PRINT 'All tables in the ' + DB_NAME() + ' database.'

PRINT ''

/************************************************** ***********
** Finally: output the totals
*/

DECLARE @.DataTotal dec(28,2), @.BlobTotal dec(28,2), @.IndexTotal dec(28,2), @.Format varchar(15)

SELECT @.DataTotal = SUM(data),
@.BlobTotal = SUM(blob),
@.IndexTotal = SUM(indexp)
FROM #space

SET @.Format = CONVERT(varchar(15),convert(money,@.DataTotal+@.Blob Total+@.IndexTotal),1)
PRINT 'Space used : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' MB'
SET @.Format = CONVERT(varchar(15),convert(money,@.DataTotal),1)
PRINT 'Space used by table data : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' MB'
SET @.Format = CONVERT(varchar(15),convert(money,@.BlobTotal),1)
PRINT 'Space used by text/image data : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' MB'
SET @.Format = CONVERT(varchar(15),convert(money,@.IndexTotal),1)
PRINT 'Space used by table indexes : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' MB'
SET @.Format = CONVERT(varchar(15),@.PageSize)
PRINT 'Page size : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' Bytes'
SET @.Format = CONVERT(varchar(15),@.UsablePage)
PRINT 'Usable Page size : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' Bytes'
PRINT ''

/************************************************** ***********

** Finally: output the detail
*/

update #space
set rowsize = CASE
WHEN rowsize < 0 THEN 0
WHEN (data) < .1 THEN 0
ELSE rowsize
END

SELECT
TableName = name,
Rows = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,rows),1)),
Total = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,data+blob+indexp),1)),
Data = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,data),1)),
Blob = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,blob),1)),
Indexes = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,indexp),1)),
RowBytes = convert(varchar(11),
CASE
WHEN rowsize = 0 THEN 'n/a'
ELSE CONVERT(varchar(15),convert(money,rowsize),1)
END),
RowsPage = convert(varchar(11),
CASE
WHEN rows = 0 THEN 'n/a'
ELSE CONVERT(varchar(15),convert(money,rows/datapages),1)
END),
Pages = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,datapages),1)),
TotalSize = data+indexp+blob
INTO #report
FROM #space
WHERE data+indexp >= @.MinSize AND type = 'U' and name != 'dtproperties'

UPDATE #report
SET Rows = REPLACE(REPLACE(Rows,'.00',''),'.0',''),
Pages = REPLACE(REPLACE(Pages,'.00',''),'.0','')

UPDATE #report
SET Rows = REPLICATE(' ',11-DATALENGTH(Rows)) + Rows,
Total = REPLICATE(' ',11-DATALENGTH(Total)) + Total,
Data = REPLICATE(' ',11-DATALENGTH(Data)) + Data,
Blob = REPLICATE(' ',11-DATALENGTH(Blob)) + Blob,
Indexes = REPLICATE(' ',11-DATALENGTH(Indexes)) + Indexes,
RowsPage = REPLICATE(' ',11-DATALENGTH(RowsPage)) + RowsPage,
Pages = REPLICATE(' ',11-DATALENGTH(Pages)) + Pages,
RowBytes = REPLICATE(' ',11-DATALENGTH(RowBytes)) + RowBytes

PRINT ' --- Average --- ----- Table Space In MB -----'

IF @.SizeSort = 1
SELECT
TableName,
' Row Size' = RowBytes,
' Rows/Page' = RowsPage,
' Data Pages' = Pages,
' Rows' = Rows,
' Total' = Total,
' Data' = Data,
' Text/Img' = Blob,
' Index' = Indexes
FROM #report
ORDER BY TotalSize DESC
ELSE
SELECT
TableName,
' Row Size' = RowBytes,
' Rows/Page' = RowsPage,
' Data Pages' = Pages,
' Rows' = Rows,
' Total' = Total,
' Data' = Data,
' Text/Img' = Blob,
' Index' = Indexes
FROM #report
ORDER BY TableName

PRINT ''

DROP TABLE #space
DROP TABLE #report|||Here's my little addtion to the pile: (built on MSSQL2k)Great zot there, bubba! I'd consider that a pile all its own, not an addition to an existing pile! Whew, I can't wait to see a major contribution!

-PatP|||I got the following errors when I ran this in Query Analyzer:

Server: Msg 137, Level 15, State 2, Line 172
Must declare the variable '@.Blob'.
Server: Msg 170, Level 15, State 1, Line 201
Line 201: Incorrect syntax near 'ney'.|||i think you might have a simple wrapping issue...@.blob isnt a variable in this script but @.blobtotal is.

testing it...that is exactly what happened...

any tips on posting this without having the format chang on me?|||try the attachment...

for some reason pasting it FROM this forum adds some freaky yeaky spaces.

they dont appear as spaces in the forum window, but they sure as hell get added if you copy/paste it out of here...

...sorry...forum noooob issue.|||Cool that worked. Check this script out that I've been using.sql

rowcount

Hi,

I am using ssis to import .csv files into sql server tables.
How do I get the count of the records imported?
Thanks

SOLVED by using a rowcount component.

|||

Use the RowCount transform.

I dont know what you need, but look around to eventhandlers and global variables.

regards

row-by-row process

Hi,
Please help.

I have 2 tables as followings:

CREATE TABLE [dbo].[Master] (
[masitemno] [char] (10) NOT NULL ,
[masqty] [decimal](10, 3) NOT NULL ,
[masunitcost] [decimal](10, 2) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Transaction] (
[transeqno] [int] NOT NULL ,
[tranitemno] [char] (10) NOT NULL ,
[tranqty] [decimal](10, 3) NOT NULL ,
[tranamount] [decimal](10, 2) NOT NULL ,
[tranunitcost] [decimal](10, 2) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Master] WITH NOCHECK ADD
CONSTRAINT [PK_Master] PRIMARY KEY NONCLUSTERED
(
[masitemno]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Transaction] WITH NOCHECK ADD
CONSTRAINT [PK_Transaction] PRIMARY KEY NONCLUSTERED
(
[transeqno]
) ON [PRIMARY]
GO

Table "Transaction" has about 1,000,000 (one million rows) and Table
"Master" has about 500,000 rows.

I have to update "MASTER" table with "TRANSACTION" table with
row-by-row processing basis sorting by
primary key TRNSEQNO column.

Sometimes TRANSACTION can explicitly SET "MASQTY" and "MASUNITCOST"
columns (TRANUNITCOST<>0) of MASTER
which linked byitemno and after that AMOUNT column of next row of
TRANSACTION will used this
new UNITCOST of MASTER as followed statements.

---------------
declare @.count int, @.max int
set @.count=1
set @.max = (select max(seqno) from transaction(nolock)

while @.count<=@.max
begin
update TRANSACTION
set TRANAMOUNT = TRANQTY * (select MASUNITCOST from MASTER
where MASITEMNO=TRANITEMNO)
where TRANSEQNO = @.count
and TRANUNITCOST = 0

update MASTER
set MASQTY = MASQTY + TRANQTY
from TRANSACTION
where TRANSEQNO = @.count
and TRANUNITCOST = 0
and MASITEMNO=TRANITEMNO

update TRANSACTION
set TRANAMOUNT = TRANQTY * TRANUNITCOST
where TRANSEQNO = @.count
and TRANUNITCOST <> 0

update MASTER
set MASQTY = MASQTY + TRANQTY,
MASUNITCOST = TRANUNITCOST
from TRANSACTION
where TRANSEQNO = @.count
and TRANUNITCOST <> 0
and MASITEMNO=TRANITEMNO

set @.count = @.count +1
end
---------------

The above sample statements take me more than 10 hrs. (I quit before
actually done) with MS SQL SERVER 7.5 SP4.
on WIN2K SERVER (2 XEON PROCESSORS, 1GB MEM.). I tried to use trigger
but result is not correct.

Please advise on shorten running time (in minutes , maybe) and better
performance.

Thank you and appreciate any suggestions

Nipon WongtrakulOn 17 Aug 2004 06:25:30 -0700, Nipon wrote:

>Hi,
> Please help.
> I have 2 tables as followings:
>CREATE TABLE [dbo].[Master] (
>[masitemno] [char] (10) NOT NULL ,
>[masqty] [decimal](10, 3) NOT NULL ,
>[masunitcost] [decimal](10, 2) NOT NULL
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Transaction] (
>[transeqno] [int] NOT NULL ,
>[tranitemno] [char] (10) NOT NULL ,
>[tranqty] [decimal](10, 3) NOT NULL ,
>[tranamount] [decimal](10, 2) NOT NULL ,
>[tranunitcost] [decimal](10, 2) NOT NULL
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Master] WITH NOCHECK ADD
>CONSTRAINT [PK_Master] PRIMARY KEY NONCLUSTERED
>(
>[masitemno]
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Transaction] WITH NOCHECK ADD
>CONSTRAINT [PK_Transaction] PRIMARY KEY NONCLUSTERED
>(
>[transeqno]
>) ON [PRIMARY]
>GO
>Table "Transaction" has about 1,000,000 (one million rows) and Table
>"Master" has about 500,000 rows.
>
>I have to update "MASTER" table with "TRANSACTION" table with
>row-by-row processing basis sorting by
>primary key TRNSEQNO column.
>Sometimes TRANSACTION can explicitly SET "MASQTY" and "MASUNITCOST"
>columns (TRANUNITCOST<>0) of MASTER
>which linked byitemno and after that AMOUNT column of next row of
>TRANSACTION will used this
>new UNITCOST of MASTER as followed statements.
>---------------
> declare @.count int, @.max int
> set @.count=1
> set @.max = (select max(seqno) from transaction(nolock)
>while @.count<=@.max
> begin
> update TRANSACTION
> set TRANAMOUNT = TRANQTY * (select MASUNITCOST from MASTER
> where MASITEMNO=TRANITEMNO)
> where TRANSEQNO = @.count
> and TRANUNITCOST = 0
> update MASTER
> set MASQTY = MASQTY + TRANQTY
> from TRANSACTION
> where TRANSEQNO = @.count
> and TRANUNITCOST = 0
> and MASITEMNO=TRANITEMNO
> update TRANSACTION
> set TRANAMOUNT = TRANQTY * TRANUNITCOST
> where TRANSEQNO = @.count
> and TRANUNITCOST <> 0
> update MASTER
> set MASQTY = MASQTY + TRANQTY,
> MASUNITCOST = TRANUNITCOST
> from TRANSACTION
> where TRANSEQNO = @.count
> and TRANUNITCOST <> 0
> and MASITEMNO=TRANITEMNO
> set @.count = @.count +1
> end
>---------------
>The above sample statements take me more than 10 hrs. (I quit before
>actually done) with MS SQL SERVER 7.5 SP4.
>on WIN2K SERVER (2 XEON PROCESSORS, 1GB MEM.). I tried to use trigger
>but result is not correct.
>Please advise on shorten running time (in minutes , maybe) and better
>performance.
>Thank you and appreciate any suggestions
>Nipon Wongtrakul

Hi Nipon,

Wow. You seem to have gotten yourself in a whole lot of trouble by
choosing this design. I'm trying to figure out what the dependencies in
your situation actually are and how a normalized version of your tables
would look like, but I have to give, due to lack of knowledge of the real
needs of your employer.

I've tried to come up with a set-based approach to what you're doing. You
didn't provide sample data that I could use to test it on, so I'm not sure
if it will really do the same as your procedural code. However, I'm quite
sure that it'll run lots quicker :-)

It might be even more quicker if you make your primary keys clustered.
Another possible improvement is creating an additional (nonunique) index
on transaction.tranitemno, but I'm not sure; your execution plan should
show if it's used or not. If you do, then you might also try if making
that index clustered instead of the primary key is better.

I did test my query to check that it will execute okay, but since I didn't
have sample data, the check was done on empty tables. I had to rename the
table Transaction to Trans, since transaction is a reserved word. If you
change the table names on posting your problem, please do check that the
code still executes okay (there were some other minor issues as well, like
a misspelled column name in the code you supplied).

Anyway, here is the code. Sorry for the lousy formatting; that's my news
software cutting long lines into pieces <g
-- Step 1: Recalculate tranamount.
-- Use qty and cost from transaction;
-- if no cost in transaction, use cost from "last" previous
transaction
-- with cost, or cost from master if no cost exists in previous
transactions.
UPDATE Trans
SET tranamount = tranamount *
CASE
WHEN tranunitcost <> 0 THEN tranunitcost
ELSE COALESCE((SELECT T1.tranunitcost
FROM Trans AS T1
WHERE T1.tranitemno = (SELECT
MAX(T2.tranitemno)
FROM Trans AS T2
WHERE T2.tranitemno =
Trans.tranitemno
AND T2.transeqno <
Trans.transeqno
AND T2.tranunitcost
<> 0)),
(SELECT masunitcost
FROM Master
WHERE Master.masitemno = Trans.tranitemno))
END

-- Step 2: Recalculate masqty and possibly masunitcost.
-- * masqty is simply increased by sum of all tranqty
-- * masunitcost is set to "last" tranunitcost,
-- or left unchanged if no transaction has tranunitcost.
UPDATE Master
SET masqty = masqty + (SELECT SUM(T0.tranqty)
FROM Trans AS T0
WHERE T0.tranitemno = Master.masitemno),
masunitcost = COALESCE((SELECT T1.tranunitcost
FROM Trans AS T1
WHERE T1.tranitemno = (SELECT
MAX(T2.tranitemno)
FROM Trans AS T2
WHERE
T2.tranitemno = Master.masitemno
AND
T2.tranunitcost <> 0)), Master.masunitcost)
FROM Master

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi, Hugo

I test your statements, but the result is still not correct.

INSERT INTO MAS
SELECT 'AAAAA',100.000,10.00
INSERT INTO MAS
SELECT 'BBBBB',200.000,15.00

INSERT INTO TRANS
SELECT 1,'AAAAA',.000,.00,20.00
INSERT INTO TRANS
SELECT 2,'BBBBB',30.000,.00,.00
INSERT INTO TRANS
SELECT 3,'AAAAA',20.000,.00,.00

As you can see the new unitcost of itemno 'AAAAA' must be
MAS.UNITCOST + TRANS.UNITCOST = 30.00 (10.00+20.00) not 20.00
after pass the 1st transaction. So your subquery

COALESCE((SELECT T1.tranunitcost FROM Trans AS T1
WHERE T1.tranitemno =
(SELECT MAX(T2.tranitemno) FROM Trans AS T2
WHERE T2.tranitemno = Trans.tranitemno
AND T2.transeqno < Trans.transeqno
AND T2.tranunitcost <> 0)),
(SELECT masunitcost FROM Mas
WHERE Mas.masitemno = Trans.tranitemno))

will get 20.00 (not 30.00 from MAS.UNITCOST)while in the 3rd transaction.

However, thank you so much for your kindess

Best Regards
Nipon

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<uf34i0liee1jt26l41sg492taiclnmgfft@.4ax.com>...
> On 17 Aug 2004 06:25:30 -0700, Nipon wrote:
> >Hi,
> > Please help.
> > I have 2 tables as followings:
> >CREATE TABLE [dbo].[Master] (
> >[masitemno] [char] (10) NOT NULL ,
> >[masqty] [decimal](10, 3) NOT NULL ,
> >[masunitcost] [decimal](10, 2) NOT NULL
> >) ON [PRIMARY]
> >GO
> >CREATE TABLE [dbo].[Transaction] (
> >[transeqno] [int] NOT NULL ,
> >[tranitemno] [char] (10) NOT NULL ,
> >[tranqty] [decimal](10, 3) NOT NULL ,
> >[tranamount] [decimal](10, 2) NOT NULL ,
> >[tranunitcost] [decimal](10, 2) NOT NULL
> >) ON [PRIMARY]
> >GO
> >ALTER TABLE [dbo].[Master] WITH NOCHECK ADD
> >CONSTRAINT [PK_Master] PRIMARY KEY NONCLUSTERED
> >(
> >[masitemno]
> >) ON [PRIMARY]
> >GO
> >ALTER TABLE [dbo].[Transaction] WITH NOCHECK ADD
> >CONSTRAINT [PK_Transaction] PRIMARY KEY NONCLUSTERED
> >(
> >[transeqno]
> >) ON [PRIMARY]
> >GO
> >Table "Transaction" has about 1,000,000 (one million rows) and Table
> >"Master" has about 500,000 rows.
> >I have to update "MASTER" table with "TRANSACTION" table with
> >row-by-row processing basis sorting by
> >primary key TRNSEQNO column.
> >Sometimes TRANSACTION can explicitly SET "MASQTY" and "MASUNITCOST"
> >columns (TRANUNITCOST<>0) of MASTER
> >which linked byitemno and after that AMOUNT column of next row of
> >TRANSACTION will used this
> >new UNITCOST of MASTER as followed statements.
> >---------------
> > declare @.count int, @.max int
> > set @.count=1
> > set @.max = (select max(seqno) from transaction(nolock)
> >while @.count<=@.max
> > begin
> > update TRANSACTION
> > set TRANAMOUNT = TRANQTY * (select MASUNITCOST from MASTER
> > where MASITEMNO=TRANITEMNO)
> > where TRANSEQNO = @.count
> > and TRANUNITCOST = 0
> > update MASTER
> > set MASQTY = MASQTY + TRANQTY
> > from TRANSACTION
> > where TRANSEQNO = @.count
> > and TRANUNITCOST = 0
> > and MASITEMNO=TRANITEMNO
> > update TRANSACTION
> > set TRANAMOUNT = TRANQTY * TRANUNITCOST
> > where TRANSEQNO = @.count
> > and TRANUNITCOST <> 0
> > update MASTER
> > set MASQTY = MASQTY + TRANQTY,
> > MASUNITCOST = TRANUNITCOST
> > from TRANSACTION
> > where TRANSEQNO = @.count
> > and TRANUNITCOST <> 0
> > and MASITEMNO=TRANITEMNO
> > set @.count = @.count +1
> > end
> >---------------
> >The above sample statements take me more than 10 hrs. (I quit before
> >actually done) with MS SQL SERVER 7.5 SP4.
> >on WIN2K SERVER (2 XEON PROCESSORS, 1GB MEM.). I tried to use trigger
> >but result is not correct.
> >Please advise on shorten running time (in minutes , maybe) and better
> >performance.
> >Thank you and appreciate any suggestions
> >Nipon Wongtrakul
> Hi Nipon,
> Wow. You seem to have gotten yourself in a whole lot of trouble by
> choosing this design. I'm trying to figure out what the dependencies in
> your situation actually are and how a normalized version of your tables
> would look like, but I have to give, due to lack of knowledge of the real
> needs of your employer.
> I've tried to come up with a set-based approach to what you're doing. You
> didn't provide sample data that I could use to test it on, so I'm not sure
> if it will really do the same as your procedural code. However, I'm quite
> sure that it'll run lots quicker :-)
> It might be even more quicker if you make your primary keys clustered.
> Another possible improvement is creating an additional (nonunique) index
> on transaction.tranitemno, but I'm not sure; your execution plan should
> show if it's used or not. If you do, then you might also try if making
> that index clustered instead of the primary key is better.
> I did test my query to check that it will execute okay, but since I didn't
> have sample data, the check was done on empty tables. I had to rename the
> table Transaction to Trans, since transaction is a reserved word. If you
> change the table names on posting your problem, please do check that the
> code still executes okay (there were some other minor issues as well, like
> a misspelled column name in the code you supplied).
> Anyway, here is the code. Sorry for the lousy formatting; that's my news
> software cutting long lines into pieces <g>
> -- Step 1: Recalculate tranamount.
> -- Use qty and cost from transaction;
> -- if no cost in transaction, use cost from "last" previous
> transaction
> -- with cost, or cost from master if no cost exists in previous
> transactions.
> UPDATE Trans
> SET tranamount = tranamount *
> CASE
> WHEN tranunitcost <> 0 THEN tranunitcost
> ELSE COALESCE((SELECT T1.tranunitcost
> FROM Trans AS T1
> WHERE T1.tranitemno = (SELECT
> MAX(T2.tranitemno)
> FROM Trans AS T2
> WHERE T2.tranitemno =
> Trans.tranitemno
> AND T2.transeqno <
> Trans.transeqno
> AND T2.tranunitcost
> <> 0)),
> (SELECT masunitcost
> FROM Master
> WHERE Master.masitemno = Trans.tranitemno))
> END
> -- Step 2: Recalculate masqty and possibly masunitcost.
> -- * masqty is simply increased by sum of all tranqty
> -- * masunitcost is set to "last" tranunitcost,
> -- or left unchanged if no transaction has tranunitcost.
> UPDATE Master
> SET masqty = masqty + (SELECT SUM(T0.tranqty)
> FROM Trans AS T0
> WHERE T0.tranitemno = Master.masitemno),
> masunitcost = COALESCE((SELECT T1.tranunitcost
> FROM Trans AS T1
> WHERE T1.tranitemno = (SELECT
> MAX(T2.tranitemno)
> FROM Trans AS T2
> WHERE
> T2.tranitemno = Master.masitemno
> AND
> T2.tranunitcost <> 0)), Master.masunitcost)
> FROM Master
>
> Best, Hugo|||Hi, Hugo

I test your statements, but the result is still not correct.

INSERT INTO MAS
SELECT 'AAAAA',100.000,10.00
INSERT INTO MAS
SELECT 'BBBBB',200.000,15.00

INSERT INTO TRANS
SELECT 1,'AAAAA',.000,.00,20.00
INSERT INTO TRANS
SELECT 2,'BBBBB',30.000,.00,.00
INSERT INTO TRANS
SELECT 3,'AAAAA',20.000,.00,.00

As you can see the new unitcost of itemno 'AAAAA' must be
MAS.UNITCOST + TRANS.UNITCOST = 30.00 (10.00+20.00) not 20.00
after pass the 1st transaction. So your subquery

COALESCE((SELECT T1.tranunitcost FROM Trans AS T1
WHERE T1.tranitemno =
(SELECT MAX(T2.tranitemno) FROM Trans AS T2
WHERE T2.tranitemno = Trans.tranitemno
AND T2.transeqno < Trans.transeqno
AND T2.tranunitcost <> 0)),
(SELECT masunitcost FROM Mas
WHERE Mas.masitemno = Trans.tranitemno))

will get 20.00 (not 30.00 from MAS.UNITCOST)while in the 3rd transaction.

However, thank you so much for your kindess

Best Regards
Nipon

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<uf34i0liee1jt26l41sg492taiclnmgfft@.4ax.com>...
> On 17 Aug 2004 06:25:30 -0700, Nipon wrote:
> >Hi,
> > Please help.
> > I have 2 tables as followings:
> >CREATE TABLE [dbo].[Master] (
> >[masitemno] [char] (10) NOT NULL ,
> >[masqty] [decimal](10, 3) NOT NULL ,
> >[masunitcost] [decimal](10, 2) NOT NULL
> >) ON [PRIMARY]
> >GO
> >CREATE TABLE [dbo].[Transaction] (
> >[transeqno] [int] NOT NULL ,
> >[tranitemno] [char] (10) NOT NULL ,
> >[tranqty] [decimal](10, 3) NOT NULL ,
> >[tranamount] [decimal](10, 2) NOT NULL ,
> >[tranunitcost] [decimal](10, 2) NOT NULL
> >) ON [PRIMARY]
> >GO
> >ALTER TABLE [dbo].[Master] WITH NOCHECK ADD
> >CONSTRAINT [PK_Master] PRIMARY KEY NONCLUSTERED
> >(
> >[masitemno]
> >) ON [PRIMARY]
> >GO
> >ALTER TABLE [dbo].[Transaction] WITH NOCHECK ADD
> >CONSTRAINT [PK_Transaction] PRIMARY KEY NONCLUSTERED
> >(
> >[transeqno]
> >) ON [PRIMARY]
> >GO
> >Table "Transaction" has about 1,000,000 (one million rows) and Table
> >"Master" has about 500,000 rows.
> >I have to update "MASTER" table with "TRANSACTION" table with
> >row-by-row processing basis sorting by
> >primary key TRNSEQNO column.
> >Sometimes TRANSACTION can explicitly SET "MASQTY" and "MASUNITCOST"
> >columns (TRANUNITCOST<>0) of MASTER
> >which linked byitemno and after that AMOUNT column of next row of
> >TRANSACTION will used this
> >new UNITCOST of MASTER as followed statements.
> >---------------
> > declare @.count int, @.max int
> > set @.count=1
> > set @.max = (select max(seqno) from transaction(nolock)
> >while @.count<=@.max
> > begin
> > update TRANSACTION
> > set TRANAMOUNT = TRANQTY * (select MASUNITCOST from MASTER
> > where MASITEMNO=TRANITEMNO)
> > where TRANSEQNO = @.count
> > and TRANUNITCOST = 0
> > update MASTER
> > set MASQTY = MASQTY + TRANQTY
> > from TRANSACTION
> > where TRANSEQNO = @.count
> > and TRANUNITCOST = 0
> > and MASITEMNO=TRANITEMNO
> > update TRANSACTION
> > set TRANAMOUNT = TRANQTY * TRANUNITCOST
> > where TRANSEQNO = @.count
> > and TRANUNITCOST <> 0
> > update MASTER
> > set MASQTY = MASQTY + TRANQTY,
> > MASUNITCOST = TRANUNITCOST
> > from TRANSACTION
> > where TRANSEQNO = @.count
> > and TRANUNITCOST <> 0
> > and MASITEMNO=TRANITEMNO
> > set @.count = @.count +1
> > end
> >---------------
> >The above sample statements take me more than 10 hrs. (I quit before
> >actually done) with MS SQL SERVER 7.5 SP4.
> >on WIN2K SERVER (2 XEON PROCESSORS, 1GB MEM.). I tried to use trigger
> >but result is not correct.
> >Please advise on shorten running time (in minutes , maybe) and better
> >performance.
> >Thank you and appreciate any suggestions
> >Nipon Wongtrakul
> Hi Nipon,
> Wow. You seem to have gotten yourself in a whole lot of trouble by
> choosing this design. I'm trying to figure out what the dependencies in
> your situation actually are and how a normalized version of your tables
> would look like, but I have to give, due to lack of knowledge of the real
> needs of your employer.
> I've tried to come up with a set-based approach to what you're doing. You
> didn't provide sample data that I could use to test it on, so I'm not sure
> if it will really do the same as your procedural code. However, I'm quite
> sure that it'll run lots quicker :-)
> It might be even more quicker if you make your primary keys clustered.
> Another possible improvement is creating an additional (nonunique) index
> on transaction.tranitemno, but I'm not sure; your execution plan should
> show if it's used or not. If you do, then you might also try if making
> that index clustered instead of the primary key is better.
> I did test my query to check that it will execute okay, but since I didn't
> have sample data, the check was done on empty tables. I had to rename the
> table Transaction to Trans, since transaction is a reserved word. If you
> change the table names on posting your problem, please do check that the
> code still executes okay (there were some other minor issues as well, like
> a misspelled column name in the code you supplied).
> Anyway, here is the code. Sorry for the lousy formatting; that's my news
> software cutting long lines into pieces <g>
> -- Step 1: Recalculate tranamount.
> -- Use qty and cost from transaction;
> -- if no cost in transaction, use cost from "last" previous
> transaction
> -- with cost, or cost from master if no cost exists in previous
> transactions.
> UPDATE Trans
> SET tranamount = tranamount *
> CASE
> WHEN tranunitcost <> 0 THEN tranunitcost
> ELSE COALESCE((SELECT T1.tranunitcost
> FROM Trans AS T1
> WHERE T1.tranitemno = (SELECT
> MAX(T2.tranitemno)
> FROM Trans AS T2
> WHERE T2.tranitemno =
> Trans.tranitemno
> AND T2.transeqno <
> Trans.transeqno
> AND T2.tranunitcost
> <> 0)),
> (SELECT masunitcost
> FROM Master
> WHERE Master.masitemno = Trans.tranitemno))
> END
> -- Step 2: Recalculate masqty and possibly masunitcost.
> -- * masqty is simply increased by sum of all tranqty
> -- * masunitcost is set to "last" tranunitcost,
> -- or left unchanged if no transaction has tranunitcost.
> UPDATE Master
> SET masqty = masqty + (SELECT SUM(T0.tranqty)
> FROM Trans AS T0
> WHERE T0.tranitemno = Master.masitemno),
> masunitcost = COALESCE((SELECT T1.tranunitcost
> FROM Trans AS T1
> WHERE T1.tranitemno = (SELECT
> MAX(T2.tranitemno)
> FROM Trans AS T2
> WHERE
> T2.tranitemno = Master.masitemno
> AND
> T2.tranunitcost <> 0)), Master.masunitcost)
> FROM Master
>
> Best, Hugo|||On 19 Aug 2004 23:46:05 -0700, Nipon wrote:

>Hi, Hugo
> I test your statements, but the result is still not correct.
>INSERT INTO MAS
> SELECT 'AAAAA',100.000,10.00
>INSERT INTO MAS
> SELECT 'BBBBB',200.000,15.00
>INSERT INTO TRANS
> SELECT 1,'AAAAA',.000,.00,20.00
>INSERT INTO TRANS
> SELECT 2,'BBBBB',30.000,.00,.00
>INSERT INTO TRANS
> SELECT 3,'AAAAA',20.000,.00,.00
>
> As you can see the new unitcost of itemno 'AAAAA' must be
> MAS.UNITCOST + TRANS.UNITCOST = 30.00 (10.00+20.00) not 20.00
> after pass the 1st transaction. So your subquery
> COALESCE((SELECT T1.tranunitcost FROM Trans AS T1
> WHERE T1.tranitemno =
> (SELECT MAX(T2.tranitemno) FROM Trans AS T2
> WHERE T2.tranitemno = Trans.tranitemno
> AND T2.transeqno < Trans.transeqno
> AND T2.tranunitcost <> 0)),
> (SELECT masunitcost FROM Mas
> WHERE Mas.masitemno = Trans.tranitemno))
> will get 20.00 (not 30.00 from MAS.UNITCOST)while in the 3rd transaction.
> However, thank you so much for your kindess

Hi Nipon,

I used your sample data to test my queries as well. There were some
corrections I had to make. I first got an error because the subquery
returned two many rows; to solve that, I had to change
WHERE T1.tranitemno = (SELECT MAX(T2.tranitemno)
to
WHERE T1.transeqno = (SELECT MAX(T2.transeqno)
in two places.

After that, I got no error but the tranamount was not calculated; I fixed
that by changing
SET tranamount = tranamount *
to
SET tranamount = tranqty *

The query now runs and returns the same results as the code you posted in
the start of this discussion.

I don't understand that you expect to get 30.00 from Master.unitcost in
the 3rd transaction. Both your code and my code set Master.unitcost for
item to 20.00 and both your and my code use this value of 20.00 to
cancluate the tranamount of the 3rd transaction. So if this is wrong, your
own code is wrong as well.

I can fix this. But before I take the time to change the code, I want a
clear confirmation from you that this is indeed what you want. I can
understand that you want the master QUANTITY to be equal to the sum of all
transaction quantities plus the starting master quantity, but I'd be very
surprised if you really want the master COST to be equal to the starting
cost plus all transaction costs! Setting the master cost equal to the
transaction cost of the last transaction makes a lot more sense (and is
what your row-by-row code actually does!). Of course, thhere are many more
things in your design that surprised me, so it's possible that this is
indeed what you want - but I want an explicit confirmation before I'll
spend time on changing the query.

So to recap: should the value in Master.unitCOST (not quantity!)
be equal to:
a) the tranunitcost of the last individual transaction that has a
tranunitcost not equal to 0, or
b) the sum of the "old" cost PLUS the sum of all tranunitcosts in the
individual transactions.

Let me know. Then, I'll work on your code some more.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi, Hugo

Glad to hear from you so quick.

a should be close to my answer to your question.

The value of MASTER.UNITCOST must be 30.00 , because when
TRANS.UNITCOST <> 0 means that I have to add (TRANS.UNITCOST is signed
and can be < 0) that value to the MASTER.UNITCOST so that the new
MAS.UNITCOST will be 30.00 after the 1st transaction , but the MAS.QTY
will not be increased because of value 0.00 of 1st TRANS.QTY. Then
when reach the 3rd transaction which is 'AAAAA' and the
TRANS.UNITCOST=0, TRANS.AMT will be updated with 20.00 (TRANS.QTY) X
30.00 (new MAS.UNITCOST) and MAS.QTY will be 100.000 (MAS.QTY) + 20.00
(3rd TRANS.QTY) = 120.
But if I have transaction#4 which looks like
'AAAAA',10.00,0.00,-15.00 ,
the MAS.QTY will be 120.000 + 10.00 (4th transaction QTY) and
MAS.UNITCOST will equal 30.00 +(-15) = 15. So, after the 4th
transaction, the next transaction that has itemno='AAAAA' and
UNITCOST=0 will use 15 (MAS.UNITCOST) ... so on

Now I'm trying to re-write my SQL statment by using a new table which
is joined table of MAS and TRANS tables.

Thank you
Best Regards
Nipon

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<i4bbi09sv7udlbb01j2n3dopmu6gu2hlnj@.4ax.com>...
> On 19 Aug 2004 23:46:05 -0700, Nipon wrote:
> >Hi, Hugo
> > I test your statements, but the result is still not correct.
> >INSERT INTO MAS
> > SELECT 'AAAAA',100.000,10.00
> >INSERT INTO MAS
> > SELECT 'BBBBB',200.000,15.00
> >INSERT INTO TRANS
> > SELECT 1,'AAAAA',.000,.00,20.00
> >INSERT INTO TRANS
> > SELECT 2,'BBBBB',30.000,.00,.00
> >INSERT INTO TRANS
> > SELECT 3,'AAAAA',20.000,.00,.00
> > As you can see the new unitcost of itemno 'AAAAA' must be
> > MAS.UNITCOST + TRANS.UNITCOST = 30.00 (10.00+20.00) not 20.00
> > after pass the 1st transaction. So your subquery
> > COALESCE((SELECT T1.tranunitcost FROM Trans AS T1
> > WHERE T1.tranitemno =
> > (SELECT MAX(T2.tranitemno) FROM Trans AS T2
> > WHERE T2.tranitemno = Trans.tranitemno
> > AND T2.transeqno < Trans.transeqno
> > AND T2.tranunitcost <> 0)),
> > (SELECT masunitcost FROM Mas
> > WHERE Mas.masitemno = Trans.tranitemno))
> > will get 20.00 (not 30.00 from MAS.UNITCOST)while in the 3rd transaction.
> > However, thank you so much for your kindess
> Hi Nipon,
> I used your sample data to test my queries as well. There were some
> corrections I had to make. I first got an error because the subquery
> returned two many rows; to solve that, I had to change
> WHERE T1.tranitemno = (SELECT MAX(T2.tranitemno)
> to
> WHERE T1.transeqno = (SELECT MAX(T2.transeqno)
> in two places.
> After that, I got no error but the tranamount was not calculated; I fixed
> that by changing
> SET tranamount = tranamount *
> to
> SET tranamount = tranqty *
> The query now runs and returns the same results as the code you posted in
> the start of this discussion.
> I don't understand that you expect to get 30.00 from Master.unitcost in
> the 3rd transaction. Both your code and my code set Master.unitcost for
> item to 20.00 and both your and my code use this value of 20.00 to
> cancluate the tranamount of the 3rd transaction. So if this is wrong, your
> own code is wrong as well.
> I can fix this. But before I take the time to change the code, I want a
> clear confirmation from you that this is indeed what you want. I can
> understand that you want the master QUANTITY to be equal to the sum of all
> transaction quantities plus the starting master quantity, but I'd be very
> surprised if you really want the master COST to be equal to the starting
> cost plus all transaction costs! Setting the master cost equal to the
> transaction cost of the last transaction makes a lot more sense (and is
> what your row-by-row code actually does!). Of course, thhere are many more
> things in your design that surprised me, so it's possible that this is
> indeed what you want - but I want an explicit confirmation before I'll
> spend time on changing the query.
> So to recap: should the value in Master.unitCOST (not quantity!)
> be equal to:
> a) the tranunitcost of the last individual transaction that has a
> tranunitcost not equal to 0, or
> b) the sum of the "old" cost PLUS the sum of all tranunitcosts in the
> individual transactions.
> Let me know. Then, I'll work on your code some more.
> Best, Hugo

ROW_NUMBER is very slow on large tables

Hi all,
I thought that ROW_NUMBER is designed to handle paging on large tables.
However, when I test it on a table with 1,000,000 records, a simple select
with ROW_NUMBER hangs for more than 10 minutes.
Any idea what can be done?
My table is:
Events (ID, Date, Desc)
My query is:
SELECT * FROM
(
SELECT TOP 100 *, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events
) as SortedEvents
WHERE RowNo > 50 and RowNo < 100You still need to have an index on the column ID. Also maybe you have
other issues such as blocking and the problem is not with the
row_number function.
Adi|||I think your query is wrong.
try this and let me know if it works
SELECT * FROM
(
SELECT TOP 100 *, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events order by id asc
) as SortedEvents
WHERE RowNo > 50 and RowNo < 100|||Thank you, but this query is just as slow as mine.
"Omnibuzz" wrote:

> I think your query is wrong.
> try this and let me know if it works
> SELECT * FROM
> (
> SELECT TOP 100 *, ROW_NUMBER()
> OVER (ORDER BY ID ASC) as RowNo
> FROM Events order by id asc
> ) as SortedEvents
> WHERE RowNo > 50 and RowNo < 100|||then can you tell me if this query is fast? do you have an index on ID?
SELECT * FROM
(
SELECT TOP 100 *
FROM Events order by id asc
) as SortedEvents|||Thank you.
ID has index.
Could you please elaborate on what is the blocking issue?
I'm testing on a standalone, development server.
Nobody else uses it.
"Adi" wrote:

> You still need to have an index on the column ID. Also maybe you have
> other issues such as blocking and the problem is not with the
> row_number function.
> Adi
>|||Anton,
try this:
SELECT Events .* FROM Events join
(
SELECT TOP 100 id, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events
) as SortedEvents
on events.id = SortedEvents.id
WHERE RowNo > 50 and RowNo < 100|||The nesting and the proprietary TOP 100 might be causing problems.
ROW_NUMBER () is new and probalby not well-optimized yet. Keep it
simple
SELECT event_id,
ROW_NUMBER()
OVER (ORDER BY event_id ASC) AS rn
FROM Events
WHERE rn BETWEEN 50 AND 100;|||Nonsense.Several years ago all db vendors acknowledged that all major
db problems had been solved (so they were free to add xml to the engine).
This is just another example of a user underming the operation of the db
by doing something silly and not informing the ng of exactly what it is.
'It's the user stupid' hangs on the wall of all vendors (and in the minds of
most responders:).
On a side note I still do not see any explanation from you from the mind
'set'
to windowing.So yesterdays criminial magically becomes todays most decorated
cop.
Code,code and nothing but code is STILL non-sense.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147365856.484616.283960@.j33g2000cwa.googlegroups.com...
>.
> ROW_NUMBER () is new and probalby not well-optimized yet.
>.|||Steve Dassin wrote:
> Nonsense.Several years ago all db vendors acknowledged that all major
> db problems had been solved
Really? Please amuse me by posting an example of some vendor making
such a claim. :-)
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
--

Row size limitations of SQL Server 2005

I've created a database design and I'm trying to figure out if it's workable or not. I have 2 tables in particular that are sparse (20 columns, but only a few have data - about 100 bytes/row) but will grow very large - to the tune of 700 million rows.

My question is whether or not there is a problem with SQL Server 2005 having 1.5+ billion rows of data even though it will likely only take up 100 gigs or so on disk. Anyone have experience in large numbers of rows like this? We're still doing testing as far determining how slow queries will get.

Thanks,

Craig

That's fine. SQL Server can deal with that many rows of data. I've worked with tables with billions of rows before. Don't forget to plan for your index space as well and any necessary index maintenance.

-Sue

|||

Try the link below for the SQL Server Max CAP.

http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Row number in a select statement

Hi
I am looking for a solution to get an incremental row number along my other
select arguments after joining more than one tables.
Thanks in advance.
Ashokuse pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashok" <akumar@.buildfolio.com> wrote in message
news:uweREUBFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I am looking for a solution to get an incremental row number along my
> other
> select arguments after joining more than one tables.
> Thanks in advance.
> Ashok
>
>|||Why can't you do it client-side? That's probably much more efficient
than attempting it in SQL.
If you really must, the following is one example of a SQL-based method
(from Pubs). You've said that more than one table is involved but it's
hard to give a full answer for that more complex scenario without more
info - like the DDL for the tables involved and some sample data to
work with.
SELECT au_id, au_lname, au_fname,
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS row_num
FROM Authors AS A
David Portas
SQL Server MVP
--

Row Level Security

Posting again in hopes that someone has a solution..

I've set up a sales report that is by territory. Two tables one of which has

sales detail records and another table with Sales Rep info, including territory and

login.. The two tables are joined by state. What I need to be able to do is schedule

this report to run on Reporting services(Already setup) and only allow the reps

to view a snapshot, don't want anyone executing the report again. Additionally,

I need them to only see the territory that they are responsible for. Does anyone

have a solution for this.

Thx again

Hello,

If I'm understanding correctly, you want to create one master snapshot report, then have each rep only access a certain subset of the data in the snapshot. Is this correct?

Since the snapshots are static, you won't be able to limit down the results further after the snapshot is created. I think what you'll need to do is setup multiple subscriptions, one for each rep. You can specify the rep as a parameter to the report. Then you can send the snapshot of the report to the specific rep.

Hope this helps.

Jarret

|||

Thanks for the suggestion. I think that your suggestion will work, but with over 300 reps, I would rather not create that many subscriptions. There has got to be away. Cognos powerplan allows the view to be created upfront and then when A report goes into that view it further filters the results. You would think I should be able to do that. What about not using the snapshot?

Row insert timestamp?

Just a quick question: Does SQL server 2000 store any identity information for tables in terms of the time a row gets inserted or are you forced to track this yourself? Thanks!Track it yourself

USE Northwind
GO

CREATE TABLE myTable99(
Col1 int IDENTITY(1,1) PRIMARY KEY
, Col2 Char(1)
, Col3 datetime DEFAULT getdate()
)
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||But but! I'm lazy... Can't microsoft just do it for me!? ;) Oh well guess I'll just have to add a trigger or something. Thanks!|||Originally posted by blm14_cu
But but! I'm lazy... Can't microsoft just do it for me!? ;) Oh well guess I'll just have to add a trigger or something. Thanks!

Did you try my code?

And lazy is good...|||Here, Here's lazy...

uncomment the EXEC to exeute the statements...

But

BE CAREFUL

You can't easily get rid of a column once you add it....|||ooops..forgot the code

Use Northwind
GO

DECLARE @.SQL varchar(8000)

DECLARE myCursor99 CURSOR
FOR
SELECT 'ALTER TABLE ['+TABLE_NAME
+'] ADD myDateCol datetime DEFAULT GETDATE() NOT NULL'
FROM INFORMATION_SCHEMA.TABLES

OPEN myCursor99

FETCH NEXT INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL
-- EXEC(@.SQL)
FETCH NEXT INTO @.SQL
END

CLOSE myCursor99
DEALLOCATE myCursor99|||Can't easily get rid of a column?

Brett's solution is the simplest if you just want to record the insert date on a record. If you need to monitor updates then you will have to write a trigger. I use the CHECKSUM functions verify dirty data.|||Originally posted by blindman
Can't easily get rid of a column?

Brett's solution is the simplest if you just want to record the insert date on a record. If you need to monitor updates then you will have to write a trigger. I use the CHECKSUM functions verify dirty data.

Yo blind dude...

Want to post how you get rid of a column?|||I use ALTER TABLE DROP COLUMN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) myself. You can do it in Enterprise Mangler too.

-PatP|||Being one of the Lazy DBAs, I do it directly from Enterprise Mangler or have EM generate a script that I can edit.|||What a scrub...there's my DB2 Roots showing..

Thanks guys

SELECT 'ALTER TABLE ['+TABLE_NAME+'] DROP CONSTRAINT ['+TABLE_NAME+'_myDateCol_Default]'
FROM INFORMATION_SCHEMA.TABLES
GO

SELECT 'ALTER TABLE ['+TABLE_NAME+'] DROP COLUMN [myDateCol]'
FROM INFORMATION_SCHEMA.TABLES
GO

Oh and the original code is wrong

Use Northwind
GO

SET NOCOUNT ON

DECLARE @.SQL varchar(8000)

DECLARE myCursor99 CURSOR
FOR
SELECT 'ALTER TABLE ['+TABLE_NAME
+'] ADD myDateCol datetime CONSTRAINT '
+'['+TABLE_NAME+'_myDateCol_Default] DEFAULT GETDATE() NOT NULL'
FROM INFORMATION_SCHEMA.TABLES

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL
-- EXEC(@.SQL)
FETCH NEXT FROM myCursor99 INTO @.SQL
END

CLOSE myCursor99
DEALLOCATE myCursor99

SET NOCOUNT OFF

sheesh...what a scrub....|||Oh dear, Brett...

I guess, on the bright side, your work just got a lot easier.|||Funny thing is, I've never had a need to drop a column....

Anyone?

Usually it's part of massive reengineering effort...

Row height issue on export to Excel

Hi all,
I've been having some problems with a report on export to Excel. I have four tables, contained within a rectangle at the top of the report, followed by a second page of tables dynamically generated by the data being fed into the report.
On export to Excel, the top sections of tables appear to export with a fixed row height that does not show all of the string contained within the cell.
I've had similar issues before with "title" textboxes, but these are usually easily solved by expanding the height of the box within the report. In this case, this hasn't worked!
Any idea?
Regards,

Jon
Hello Jon,

How are the Tables positioned? Are they positioned vertically, or are the tables located beside each other? The only way I was able to reproduce the behavior you describe is to create a cell which contains enough text to cause Excel to reach its row height limit. Excel has a limit of 409.5 for row heights. Are your reports hitting this limit, or do the rows have room to grow, but aren't?

Ian|||Hi Ian,
The tables are positioned both horizontally and vertically as there's four tables in a square formation (imagine one table at each point of the square).
The text certainly doesn't reach the row height within excel. In fact, it only needs around 3 times the amount required by a single row at a font size of 10 pt.
The size of the row within the report designer should be enough on it's own to accomodate the amount of text. And within report manager, the row re-sizes to the correct height required.
Thanks for getting back to me,

Jon
|||

Can you post a screenshot of the problem? I'm having trouble visualizing what the issue is.

Thanks, Geoff

|||

If you want to render the exact same format in Excel than in Preview, it can be challenging.

Excel export is usually that bad that I simply format all reports to be as flat as possible.

I stay away from csv export because it just is not practicable.

I am also used to systematically remove all the "Merge Cells and Wrap Text" options in Excel or format the report to avoid this behavior as much as I can since this is to me a major anoyance and a increase of file size I do not need.

if you need a report formated like this you do not need an Excel output, you need an HTML archive or a PDF.

I always use WEB format for stuff with complex layout I want to preserve. people can still copy paste this from a web page or an email to an Excel if they want.

If you need Excel it is most likely to be for further analytical processing like adding foormulas and building pivot tables, in which case, the merge and wrap stuff is just a waste of time.

Microsoft team, what I am dreaming about is a PLAIN Excel export option without all these bernacles that makes the file bloated, hard to download and open when big and require much work to remove the unwanted formating.

CSV export as it is today is close to be impossible to use, it is too disconnected from the original report.

Philippe

|||Unfortunately I don't have an easy way of linking an image, and no ftp site to upload to (unless there's an easier way that I'm missing?).
However, try and imagine this: You have a cell, populated by a small paragraph of text spanning three lines within report manager. On export to Excel, the cell height in Excel is only large enough to show the first of the three lines of text. It does not seem to grow dynamically to show all the text, without the Excel user expanding the height of the cell manually.
Does that make any more sense?|||

Have you set CanGrow on the text box? If it isn't set, or there are other objects rendered to the same row that do not have CanGrow set, it may not adjust the text box properly when rendering to Excel. If you already have the textbox set to the appropriate height in the RDL, then I'm not sure what is causing the issue and we'll need to investigate further.

Hope that helps!

-Geoff

|||

Hi Philippe, thanks for the feedback.

Wrap text in Excel will not increase the size of the Excel file, as it is only a single boolean flag. Merge cells, however, will increase the size of the file.

The difficulty of using the Excel output for data is something we're very aware of, and we are considering an Excel data export for a future release, as well as improving the CSV output. If your needs can be met by simple data output to Excel, you may want to check out SoftArtisans OfficeWriter, as it will let you define a template in Excel and then drop data into it:

http://officewriter.softartisans.com/officewriter-250.aspx

- Geoff

|||Hi Geoff,
Thanks for getting back to me. Yes, the CanGrow property is set to true. In fact, I tried it with the other option for decreasing contents too, just out of interest. Neither of them seem to affect the export to Excel in this case (although it has on others).
Could it be to do with the fact I have 4 tables within a rectangle?

Jon
|||

If you have other tables at the same horizontal level, or other report items at all (i.e., other items rendered in the same row) they could very well be affecting the autogrow property in Excel.

I would remove all but one table and see if the behavior is still incorrect. Also, try removing the rectangle.

- Geoff

|||I've removed the rectangle, and all surrounding tables and it's still behaving in the same way.
Very odd. Like I've said before, I've had this problem with textboxes, but normally increasing the height in the report designer helps this issue.
By the way. Geoff - any chance you can have a look at this for me? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=497582&SiteID=1
I think it may be a bug, and but i'd appreciate your thoughts. It's causing an issue for myself and one of my colleagues.
Thanks,

Jon

|||Jon, if you could put this into Connect (https://connect.microsoft.com/SQLServer ) then we can start taking a look at it via our regular issue pipeline.

Thanks, Geoff

|||I've now logged this with connect.
Thanks for your help Geoff.

Jon
|||Hi,

I have the same problem. I have one table and the Header Row is set to 0,59062in in height and shows four lines of text 7pt bold.
If I export to PDF everything looks fine but in excel export the row is just one text line high.
Does anoyone know how to solve this?

Thanks,
Mats|||Hello Mats,

In the Excel renderer, if a textbox has CanGrow set to true, along with other criteria, a flag will be set on the textbox's row in the Biff 8 output to indicate that the row's height should be automatically adjusted to fit the text. However, this does not always work for some scenerios, e.g., if the textbox uses a non-default font or the textbox spans more than one cell.

Therefore, if you set CanGrow to false on the textboxes within the row, the Excel renderer will not set this flag, and will use the size specified in the RDL instead.

Ian

Row height issue on export to Excel

Hi all,

I've been having some problems with a report on export to Excel.

I have four tables, contained within a rectangle at the top of the

report, followed by a second page of tables dynamically generated by

the data being fed into the report.

On export to Excel, the top sections of tables appear to export with a

fixed row height that does not show all of the string contained within

the cell.

I've had similar issues before with "title" textboxes, but these are

usually easily solved by expanding the height of the box within the

report. In this case, this hasn't worked!

Any idea?

Regards,
JonHello Jon,

How are the Tables positioned? Are they positioned vertically, or are the tables located beside each other? The only way I was able to reproduce the behavior you describe is to create a cell which contains enough text to cause Excel to reach its row height limit. Excel has a limit of 409.5 for row heights. Are your reports hitting this limit, or do the rows have room to grow, but aren't?

Ian|||Hi Ian,

The tables are positioned both horizontally and vertically as there's

four tables in a square formation (imagine one table at each point of

the square).

The text certainly doesn't reach the row height within excel. In

fact, it only needs around 3 times the amount required by a single row

at a font size of 10 pt.

The size of the row within the report designer should be enough on it's

own to accomodate the amount of text. And within report manager,

the row re-sizes to the correct height required.

Thanks for getting back to me,
Jon|||

Can you post a screenshot of the problem? I'm having trouble visualizing what the issue is.

Thanks, Geoff

|||

If you want to render the exact same format in Excel than in Preview, it can be challenging.

Excel export is usually that bad that I simply format all reports to be as flat as possible.

I stay away from csv export because it just is not practicable.

I am also used to systematically remove all the "Merge Cells and Wrap Text" options in Excel or format the report to avoid this behavior as much as I can since this is to me a major anoyance and a increase of file size I do not need.

if you need a report formated like this you do not need an Excel output, you need an HTML archive or a PDF.

I always use WEB format for stuff with complex layout I want to preserve. people can still copy paste this from a web page or an email to an Excel if they want.

If you need Excel it is most likely to be for further analytical processing like adding foormulas and building pivot tables, in which case, the merge and wrap stuff is just a waste of time.

Microsoft team, what I am dreaming about is a PLAIN Excel export option without all these bernacles that makes the file bloated, hard to download and open when big and require much work to remove the unwanted formating.

CSV export as it is today is close to be impossible to use, it is too disconnected from the original report.

Philippe

|||Unfortunately I don't have an easy way of linking an image, and no ftp

site to upload to (unless there's an easier way that I'm missing?).

However, try and imagine this: You have a cell, populated by a small

paragraph of text spanning three lines within report manager. On export

to Excel, the cell height in Excel is only large enough to show the

first of the three lines of text. It does not seem to grow dynamically

to show all the text, without the Excel user expanding the height of

the cell manually.

Does that make any more sense?|||

Have you set CanGrow on the text box? If it isn't set, or there are other objects rendered to the same row that do not have CanGrow set, it may not adjust the text box properly when rendering to Excel. If you already have the textbox set to the appropriate height in the RDL, then I'm not sure what is causing the issue and we'll need to investigate further.

Hope that helps!

-Geoff

|||

Hi Philippe, thanks for the feedback.

Wrap text in Excel will not increase the size of the Excel file, as it is only a single boolean flag. Merge cells, however, will increase the size of the file.

The difficulty of using the Excel output for data is something we're very aware of, and we are considering an Excel data export for a future release, as well as improving the CSV output. If your needs can be met by simple data output to Excel, you may want to check out SoftArtisans OfficeWriter, as it will let you define a template in Excel and then drop data into it:

http://officewriter.softartisans.com/officewriter-250.aspx

- Geoff

|||Hi Geoff,

Thanks for getting back to me. Yes, the CanGrow property is set

to true. In fact, I tried it with the other option for decreasing

contents too, just out of interest. Neither of them seem to

affect the export to Excel in this case (although it has on others).

Could it be to do with the fact I have 4 tables within a rectangle?
Jon|||

If you have other tables at the same horizontal level, or other report items at all (i.e., other items rendered in the same row) they could very well be affecting the autogrow property in Excel.

I would remove all but one table and see if the behavior is still incorrect. Also, try removing the rectangle.

- Geoff

|||I've removed the rectangle, and all surrounding tables and it's still behaving in the same way.

Very odd. Like I've said before, I've had this problem with

textboxes, but normally increasing the height in the report designer

helps this issue.

By the way. Geoff - any chance you can have a look at this for me?

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

I think it may be a bug, and but i'd appreciate your thoughts. It's causing an issue for myself and one of my colleagues.

Thanks,
Jon|||Jon, if you could put this into Connect (https://connect.microsoft.com/SQLServer ) then we can start taking a look at it via our regular issue pipeline.

Thanks, Geoff

|||I've now logged this with connect.

Thanks for your help Geoff.
Jon|||Hi,

I have the same problem. I have one table and the Header Row is set to 0,59062in in height and shows four lines of text 7pt bold.
If I export to PDF everything looks fine but in excel export the row is just one text line high.
Does anoyone know how to solve this?

Thanks,
Mats|||Hello Mats,

In the Excel renderer, if a textbox has CanGrow set to true, along with other criteria, a flag will be set on the textbox's row in the Biff 8 output to indicate that the row's height should be automatically adjusted to fit the text. However, this does not always work for some scenerios, e.g., if the textbox uses a non-default font or the textbox spans more than one cell.

Therefore, if you set CanGrow to false on the textboxes within the row, the Excel renderer will not set this flag, and will use the size specified in the RDL instead.

Ian

Row height issue on export to Excel

Hi all,
I've been having some problems with a report on export to Excel. I have four tables, contained within a rectangle at the top of the report, followed by a second page of tables dynamically generated by the data being fed into the report.
On export to Excel, the top sections of tables appear to export with a fixed row height that does not show all of the string contained within the cell.
I've had similar issues before with "title" textboxes, but these are usually easily solved by expanding the height of the box within the report. In this case, this hasn't worked!
Any idea?
Regards,

Jon
Hello Jon,

How are the Tables positioned? Are they positioned vertically, or are the tables located beside each other? The only way I was able to reproduce the behavior you describe is to create a cell which contains enough text to cause Excel to reach its row height limit. Excel has a limit of 409.5 for row heights. Are your reports hitting this limit, or do the rows have room to grow, but aren't?

Ian|||Hi Ian,
The tables are positioned both horizontally and vertically as there's four tables in a square formation (imagine one table at each point of the square).
The text certainly doesn't reach the row height within excel. In fact, it only needs around 3 times the amount required by a single row at a font size of 10 pt.
The size of the row within the report designer should be enough on it's own to accomodate the amount of text. And within report manager, the row re-sizes to the correct height required.
Thanks for getting back to me,

Jon
|||

Can you post a screenshot of the problem? I'm having trouble visualizing what the issue is.

Thanks, Geoff

|||

If you want to render the exact same format in Excel than in Preview, it can be challenging.

Excel export is usually that bad that I simply format all reports to be as flat as possible.

I stay away from csv export because it just is not practicable.

I am also used to systematically remove all the "Merge Cells and Wrap Text" options in Excel or format the report to avoid this behavior as much as I can since this is to me a major anoyance and a increase of file size I do not need.

if you need a report formated like this you do not need an Excel output, you need an HTML archive or a PDF.

I always use WEB format for stuff with complex layout I want to preserve. people can still copy paste this from a web page or an email to an Excel if they want.

If you need Excel it is most likely to be for further analytical processing like adding foormulas and building pivot tables, in which case, the merge and wrap stuff is just a waste of time.

Microsoft team, what I am dreaming about is a PLAIN Excel export option without all these bernacles that makes the file bloated, hard to download and open when big and require much work to remove the unwanted formating.

CSV export as it is today is close to be impossible to use, it is too disconnected from the original report.

Philippe

|||Unfortunately I don't have an easy way of linking an image, and no ftp site to upload to (unless there's an easier way that I'm missing?).
However, try and imagine this: You have a cell, populated by a small paragraph of text spanning three lines within report manager. On export to Excel, the cell height in Excel is only large enough to show the first of the three lines of text. It does not seem to grow dynamically to show all the text, without the Excel user expanding the height of the cell manually.
Does that make any more sense?|||

Have you set CanGrow on the text box? If it isn't set, or there are other objects rendered to the same row that do not have CanGrow set, it may not adjust the text box properly when rendering to Excel. If you already have the textbox set to the appropriate height in the RDL, then I'm not sure what is causing the issue and we'll need to investigate further.

Hope that helps!

-Geoff

|||

Hi Philippe, thanks for the feedback.

Wrap text in Excel will not increase the size of the Excel file, as it is only a single boolean flag. Merge cells, however, will increase the size of the file.

The difficulty of using the Excel output for data is something we're very aware of, and we are considering an Excel data export for a future release, as well as improving the CSV output. If your needs can be met by simple data output to Excel, you may want to check out SoftArtisans OfficeWriter, as it will let you define a template in Excel and then drop data into it:

http://officewriter.softartisans.com/officewriter-250.aspx

- Geoff

|||Hi Geoff,
Thanks for getting back to me. Yes, the CanGrow property is set to true. In fact, I tried it with the other option for decreasing contents too, just out of interest. Neither of them seem to affect the export to Excel in this case (although it has on others).
Could it be to do with the fact I have 4 tables within a rectangle?

Jon
|||

If you have other tables at the same horizontal level, or other report items at all (i.e., other items rendered in the same row) they could very well be affecting the autogrow property in Excel.

I would remove all but one table and see if the behavior is still incorrect. Also, try removing the rectangle.

- Geoff

|||I've removed the rectangle, and all surrounding tables and it's still behaving in the same way.
Very odd. Like I've said before, I've had this problem with textboxes, but normally increasing the height in the report designer helps this issue.
By the way. Geoff - any chance you can have a look at this for me? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=497582&SiteID=1
I think it may be a bug, and but i'd appreciate your thoughts. It's causing an issue for myself and one of my colleagues.
Thanks,

Jon

|||Jon, if you could put this into Connect (https://connect.microsoft.com/SQLServer ) then we can start taking a look at it via our regular issue pipeline.

Thanks, Geoff

|||I've now logged this with connect.
Thanks for your help Geoff.

Jon
|||Hi,

I have the same problem. I have one table and the Header Row is set to 0,59062in in height and shows four lines of text 7pt bold.
If I export to PDF everything looks fine but in excel export the row is just one text line high.
Does anoyone know how to solve this?

Thanks,
Mats|||Hello Mats,

In the Excel renderer, if a textbox has CanGrow set to true, along with other criteria, a flag will be set on the textbox's row in the Biff 8 output to indicate that the row's height should be automatically adjusted to fit the text. However, this does not always work for some scenerios, e.g., if the textbox uses a non-default font or the textbox spans more than one cell.

Therefore, if you set CanGrow to false on the textboxes within the row, the Excel renderer will not set this flag, and will use the size specified in the RDL instead.

Ian

Row failed to retrieve on last operation

Hello,

I am getting the following errors when "inserting" data into my tables using Miscrosoft SQL Server Management Studio with a SQL Server 2005 database...

"This row was successfully committed to the database. However, a problem occurred when attempting to retrieve the data back after the commit. Because of this, the displayed data in this row is read-only. To fix this problem, please re-run the query."

In the status bar:

"row failed to retrieve on last operation"

I am hoping I just have some database properties set wrong but have not been able to figure it out.

close cursor on commit is set to False, set it to True, retested, no affect on the error.

other info:

The database is used to supply data to a Microsoft Access ADP application

Are you getting any error number with this?

Anything in the SQL logs?

Does this happen on all inserts or just certain ones?

|||

There is no error number that comes with this error.

There is nothing significant in the logs...

This error occurs when inserting a new record to any table in the database. The record does insert but is not visible until I have followed the instructions of the error msg.

Error Log:

2007-07-04 19:00:42.00 Server (c) 2005 Microsoft Corporation.
2007-07-04 19:00:42.00 Server All rights reserved.
2007-07-04 19:00:42.00 Server Server process ID is xxxx.
2007-07-04 19:00:42.00 Server Authentication mode is xxxxxx.
2007-07-04 19:00:42.00 Server Logging SQL Server messages in file xxxxx.
2007-07-04 19:00:42.00 Server This instance of SQL Server last reported using a process ID of xxxx at 7/4/2007 7:00:00 PM (local) 7/5/2007 2:00:00 AM (UTC). This is an informational message only; no user action is required.
2007-07-04 19:00:42.00 Server Registry startup parameters:
2007-07-04 19:00:42.00 Server xxxxx

2007-07-04 19:00:42.00 Server xxxxxx

2007-07-04 19:00:42.00 Server xxxxxx

2007-07-04 19:00:42.01 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-07-04 19:00:42.01 Server Detected X CPUs. This is an informational message; no user action is required.
2007-07-04 19:00:42.51 Server Using dynamic lock allocation. Initial allocation of xxxx Lock blocks and xxxxx Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-07-04 19:00:42.57 Server Database mirroring has been enabled on this instance of SQL Server.
2007-07-04 19:00:42.57 spidxs Starting up database 'master'.
2007-07-04 19:00:42.95 spidxs Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2007-07-04 19:00:43.01 spidxs SQL Trace ID 1 was started by login "xxxxxx".
2007-07-04 19:00:43.04 spidxs Starting up database 'xxxxxxxxxxx'.
2007-07-04 19:00:43.06 spidxs The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-07-04 19:00:43.20 spidxs Starting up database 'model'.
2007-07-04 19:00:43.21 spidxs Server name is 'xxxxx\MYDB'. This is an informational message only. No user action is required.
2007-07-04 19:00:43.43 spidxs Clearing tempdb database.
2007-07-04 19:00:43.71 spidxs Starting up database 'tempdb'.
2007-07-04 19:00:43.75 spidxs The Service Broker protocol transport is disabled or not configured.
2007-07-04 19:00:43.75 spidxs The Database Mirroring protocol transport is disabled or not configured.
2007-07-04 19:00:43.76 spidxs Service Broker manager has started.
2007-07-04 19:00:44.23 Server A self-generated certificate was successfully loaded for encryption.
2007-07-04 19:00:44.25 Server Server is listening on [ xxxxxx].
2007-07-04 19:00:44.25 Server Server local connection provider is ready to accept connection on [ xxx].
2007-07-04 19:00:44.25 Server Server named pipe provider is ready to accept connection on [xxxx].
2007-07-04 19:00:44.25 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2007-07-04 19:00:44.28 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2007-07-04 19:00:44.43 spidxs Starting up database 'msdb'.

|||

Can you send the DML of the table? And do you have a trigger on the table? I assume you are doing this in the "Open table" tool.

It seems to me that you possibly are changing the key value in a trigger or something and it cannot find the row with the key you edited. I get that error when I edit the following table:

create table fred

(

fredId int primary key,

value varchar(10)

)

go

create trigger fred$insertTrigger

on fred

after insert

as

update fred

set fredId = fredId * 10000

where fredId in (select fredId from inserted)

go

|||There is a constraint for a default in the table and for some reason, when using the open table tool, the default is not being applied automatically and that is what I believe makes the error msg appear.|||That sounds plausible. Can you send the DML for the table? I will test it too.