rows column in sysindexes table got overflow

Hi, does anyone have the overflow issue with the column, rows in the
sysindexes table? We have table with over 3 billions records and it throws
error 8115 overflow error when I double click the table, which should return
row counts in the table. There is no issue with all data manipulation on thi
s
table even with count_big. When I checked the sysindexes table for this
table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
always max number of integer (even after new insert).
--
hm100This is because the procedure used by EM to return this information
(sp_MStablespace) is trying to force a bigint into an int variable using the
following code
SELECT @.rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @.id
This is no longer used by management studio in SQL2005
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should
> return
> row counts in the table. There is no issue with all data manipulation on
> this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100|||"double-click the table"... Sounds like some bug in the tool you are using?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should retu
rn
> row counts in the table. There is no issue with all data manipulation on t
his
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100

rows column in sysindexes table got overflow

Hi, does anyone have the overflow issue with the column, rows in the
sysindexes table? We have table with over 3 billions records and it throws
error 8115 overflow error when I double click the table, which should return
row counts in the table. There is no issue with all data manipulation on this
table even with count_big. When I checked the sysindexes table for this
table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
always max number of integer (even after new insert).
--
hm100This is because the procedure used by EM to return this information
(sp_MStablespace) is trying to force a bigint into an int variable using the
following code
SELECT @.rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @.id
This is no longer used by management studio in SQL2005
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should
> return
> row counts in the table. There is no issue with all data manipulation on
> this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100|||"double-click the table"... Sounds like some bug in the tool you are using?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should return
> row counts in the table. There is no issue with all data manipulation on this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100sql

rows become columns help!

I am building a calendar table for the most reason four weeks activitis and I have had a temp table data in table A (See my attached file) and I want to
make it as the format in table B as final. How to convert it? Please help!
Thank you!
Suincut this and paste

This should get you on your way.

More formating needed

select
case when dow = 1 then vactype else null end MON,
case when dow = 2 then vactype else null end TUES,
case when dow = 3 then vactype else null end WED,
case when dow = 4 then vactype else null end THUR,
case when dow = 5 then vactype else null end FRI,
case when dow = 6 then vactype else null end SAT,
case when dow = 7 then vactype else null end SUN,
nameitem,
weeknum
from(
select 'VAC DAY' AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/1/2005' AS FULLWORK,
datepart(dw,'1/1/2005') AS DOW,
1 as weeknum
union
select 'VAC DAY' AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/2/2005' AS FULLWORK,
datepart(dw,'1/2/2005') AS DOW,
2 as weeknum
union
select 'VAC DAY' AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/3/2005' AS FULLWORK,
datepart(dw,'1/3/2005') AS DOW,
2 as weeknum
union
select null AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/4/2005' AS FULLWORK,
datepart(dw,'1/4/2005') AS DOW,
2 as weeknum
union
select null AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/5/2005' AS FULLWORK,
datepart(dw,'1/5/2005') AS DOW,
2 as weeknum) a|||thanks thanks!

Rows as Colums

I know this is possible in DB2 and Oracle, but what about for SQL-server 2005

1) select X number of rows from table1

2) I need colums for each row of table1 in a new table

3) As such, Select (select * from X where x.id = @.ID), a,b,c from table Y where y.Id = @.ID

And I dont want to use IfExists.

Thanks

DK

I don't think it is possible the way your are describing.

You could try the pivot method described in this article:

http://dotnet.sys-con.com/read/45543.htm

rows as columns

is it possible to write a query so that we can have all rows of one column in a single column
TIA
Yes. You could concatenate all the rows in the query as:
SELECT
column1 + column2 + column3
FROM
yourtable
One thing to note here is, since the columns would have different datatypes if you try to concatenate varchar column with int column SQL Server might throw an error. So it is adviced to use CONVERT function to convert all the values into varchar, something like:
SELECT
( CONVERT(varchar(5),intcolumn1) + CONVERT(varchar(10),decimalcolumn2) + regularvarcharcolumn3 )
FROM
yourtable

Rows as Columns

This summary is not available. Please click here to view the post.

Rows and Columns

I have a query that reports all of the data I want. However it creates about 40 columns and 10 rows. I would like to swap the display so that it shows 10 columns and 40 rows, which would cause it to print out more readable. Can it be done?Are you using SQL Server 2005 or SQL Server 2000?|||

Hi,

in SQL Server 200 oyu will have to use appropiate CASE expressions, in SQL Server 2005 you can use the new PIVOT functionality-

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am using SQL 2000 which someone else said needed a CASE statement. What I have seen seems to want to add things. What I have is a list of numbers and then columns that are the results of my Query.

I have columns named Store , Name, Net, Tax, Deposits (and a bunch of others)

I have rows with the store numbers 2, 3, 4, 5 ,6 that I sort by

I would like the columns as rows and the store numbers across the top

Thanks

sql