Question about Reports....

Hi all... I am doing Report in Business Intelligence Project. If I preview the report, why is it the data is shown only in 2 pages. It is actually 4 pages if I print it... what should I do to show the 4 pages report if I preview it? Please I need guidance....

thanks.

-Ron-

It is not possible to control the number of pages in any format, either html preview or pdf.

Shyam

|||

On the Layout tab of your report, under the Layout section on the Properties menu, try setting your InteractiveSize to 8.5in, 11in.

|||

triguns wrote:

Hi all... I am doing Report in Business Intelligence Project. If I preview the report, why is it the data is shown only in 2 pages. It is actually 4 pages if I print it... what should I do to show the 4 pages report if I preview it? Please I need guidance....

thanks.

-Ron-

I'm brand new to MS BI and have a similar problem but the even number pages don't have any detail on them.... just header/footer stuff. I've got interactive and page size both set to 8.5X11. Any thoughts?

Thanks!

Leif|||

Do you have any explicit page breaks set?

Another thing that can affect paging is having a control too close to the border of the page. Try leaving a quarter inch or eighth of an inch between the control and the margin.

|||

It is because the data from one page is getting spilled to second page. This happens if the report width in your designer + (left margin+right margin specified in Report Properties -> Layout tab) exceeds the page width that you specify in Report Properties -> Layout tab. For example, if your report is 8 inches wide in your designer and in report properties, you have specified page width=8.5 inch and left margin=1inch, right margin=1inch, data from your pages will spill to next pages because 8 inch (designer report width) + 1 inch (left margin) + inch (right amrgin) > 8.5 inch (page width).

Shyam

question about order in which queries fire in trigger

I ran into this trigger in a project. Is this tigger firing Delete from
webproducts join Deleted... first? and then Delete from webproducts join
Inserted... second? Or is the ...join Inserted firing first and then the
...deleted?
The table SubDetail gets updated on some column, any column. No deletes
involved. What would constitute deleted if it isn't Delete from webproducts
join Inserted?
----
CREATE trigger t_For_Update_WebProducts on dbo.SubDetail
for update
delete WebProducts
from WebProducts wp
inner join Deleted d
on (d.RecordID = wp.RecordID )
delete WebProducts
from WebProducts wp
inner join Inserted i
on (i.RecordID = wp.RecordID)
return
---
Thanks,
RichOn Fri, 12 May 2006 15:17:01 -0700, Rich wrote:

>I ran into this trigger in a project. Is this tigger firing Delete from
>webproducts join Deleted... first? and then Delete from webproducts join
>Inserted... second? Or is the ...join Inserted firing first and then the
>...deleted?
>The table SubDetail gets updated on some column, any column. No deletes
>involved. What would constitute deleted if it isn't Delete from webproduct
s
>join Inserted?
(snip code)
Hi Rich,
I'm not sure if I understand all your questions.
However:
1. Statements in a trigger are executed sequentially, top-down (unless
you explicitly change order of execution with GO TO, WHILE, IF or other
control-flow statements).
2. In an UPDATE trigger, the deleted pseudo-table holds the before
update image of all affected rows and the inserted pseudo-table holds
the after update image of all affected rows.
Hugo Kornelis, SQL Server MVP|||Rich (Rich@.discussions.microsoft.com) writes:
> I ran into this trigger in a project. Is this tigger firing Delete from
> webproducts join Deleted... first? and then Delete from webproducts
> join Inserted... second?
Yes, statements are executed sequentially.

> The table SubDetail gets updated on some column, any column. No deletes
> involved. What would constitute deleted if it isn't Delete from >
> webproducts join Inserted?
In an UPDATE trigger, both the "inserted" and "deleted" tables are
popoulated. "inserted" holds the after-image data, and "deleted" is
before-image. That is how the row looked like before the update.

> CREATE trigger t_For_Update_WebProducts on dbo.SubDetail
> for update
> delete WebProducts
> from WebProducts wp
> inner join Deleted d
> on (d.RecordID = wp.RecordID )
> delete WebProducts
> from WebProducts wp
> inner join Inserted i
> on (i.RecordID = wp.RecordID)
Seems funny to run two deletes like that...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you all for your replies. Actually, I left out other stuff from that
trigger which I did understand. However, the explanations provided here did
enlighten me about one thing that I was not aware of, the image of the table
before the action - "Deleted".
I actually do need to capture the before image, and I had no idea how to
describe that. Now I understand. Thank you all for explaining this to me.
Now I understand.
It doesn't solve my immediate problem, but now I think I can figure it out.
Incase anyone cares, my problem is that I am updating this table from a
client app using com ADO and want to retrieve the records affected count. I
f
...deleted... comes after ...inserted... in the trigger, I can retrieve th
e
counts of records affected. But if ...deleted... comes before ...inserted..
.
then
cmd.Execute j, , adExecuteNoRecords
is not returning the count of records affected. But at least now I can
tweak the trigger a little since I have a better idea what is going on.
Thanks again all,
Rich
"Rich" wrote:

> I ran into this trigger in a project. Is this tigger firing Delete from
> webproducts join Deleted... first? and then Delete from webproducts join
> Inserted... second? Or is the ...join Inserted firing first and then th
e
> ...deleted?
> The table SubDetail gets updated on some column, any column. No deletes
> involved. What would constitute deleted if it isn't Delete from webproduc
ts
> join Inserted?
> ----
--
> CREATE trigger t_For_Update_WebProducts on dbo.SubDetail
> for update
> delete WebProducts
> from WebProducts wp
> inner join Deleted d
> on (d.RecordID = wp.RecordID )
> delete WebProducts
> from WebProducts wp
> inner join Inserted i
> on (i.RecordID = wp.RecordID)
> return
> ---
> Thanks,
> Rich|||Let me clarify a few things here.. Rich.. if you don't mind.
Take your case
delete WebProducts
from WebProducts wp
inner join Deleted d
on (d.RecordID = wp.RecordID )
delete WebProducts
from WebProducts wp
inner join Inserted i
on (i.RecordID = wp.RecordID)
Here first you are joining with deleted table on recordID and then the same
with inserted. And as Hugo and Erland have mentioned, Deleted has the old
image and Inserted has the new image. So this delete with join from deleted
is actually redundant.
There are two cases. One where the update is happening on the column
"RecordID".
In that case you will not be having any row in the table with the value of
"RecordID" that you have in deleted. So the first delete will not delete any
rows (Though I made an assumption here, that RecordID is the PKey :)
And if RecordID is not being updated, then the first delete will delete the
rows and the second delete will try to delete the same rows which will not
happen, so you will be getting rowcount = 0.
What I don't understand is your requirement. Do you want to delete the
updated rows from the table'|||On Fri, 12 May 2006 20:23:01 -0700, Omnibuzz wrote:
(snip)
> So this delete with join from deleted
>is actually redundant.
Hi Omnibuzz,
Good catch!

>What I don't understand is your requirement. Do you want to delete the
>updated rows from the table'
Same here. I hope that Rich will return and explain his requirements
some more.
Hugo Kornelis, SQL Server MVP|||examnotes <Rich@.discussions.microsoft.com> writes:
>Thank you all for your replies. Actually, I left out other stuff from that
>trigger which I did understand. However, the explanations provided here di
d
>enlighten me about one thing that I was not aware of, the image of the tabl
e
>before the action - "Deleted".
>I actually do need to capture the before image, and I had no idea how to
>describe that. Now I understand. Thank you all for explaining this to me.
>Now I understand.
>It doesn't solve my immediate problem, but now I think I can figure it out.
>Incase anyone cares, my problem is that I am updating this table from a
>client app using com ADO and want to retrieve the records affected count.
If
>...deleted... comes after ...inserted... in the trigger, I can retrieve th
e
>counts of records affected. But if ...deleted... comes before ...inserted.
.
>then
>cmd.Execute j, , adExecuteNoRecords
> is not returning the count of records affected. But at least now I can
>tweak the trigger a little since I have a better idea what is going on.
I think you need to skip adExecuteNoRecords and instead loop over
:NextRecordset. Each UPDATE and DELETE causes a rowcount. Obviously,
you want the first: the number of rows inserted. You must loop over
.NextRecordset to get all rowcounts, even if you don't need them.
--
Erland Sommarskog, Stockholm, esquel@.sommarskog.se

Question about move large amount of data from database to database

guys,

I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).

My question is if there is some other way I should do to speed up the
action, I am thinking about use bcp to copy those records to datafile
and then use bcp to insert it into SQL Server table. Is this the right
way to do it or should I consider other solution (then, what is the
solution.)

Thanks a lot!On Apr 23, 2:23 pm, Lee <lee.jenkins...@.gmail.comwrote:

Quote:

Originally Posted by

guys,
>
I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).
>
My question is if there is some other way I should do to speed up the
action, I am thinking about use bcp to copy those records to datafile
and then use bcp to insert it into SQL Server table. Is this the right
way to do it or should I consider other solution (then, what is the
solution.)
>
Thanks a lot!

Use a Select Into statement and make sure the destination db is set to
a simple recovery model.|||Yes, BCP will be a good option for fast data transfer. All of the BULK
operations (BULK INSERT, SELECT INTO, BCP) are minimally logged when a non
FULL recovery model is set.

Another issue could be the purging of the archived records from your main
table. If you have it as a single DELETE and it takes long time to complete,
then you can break it into smaller DELETE chunks.

If you have SQL Server 2005 Enterprise Edition, an interesting alternative
is to use partitioned tables. Specifically range partitions based on date
ranges (in your case could be weekly) can help with archiving. Take a look
at the following article (in particular the section about Range Partitions):
http://msdn2.microsoft.com/en-us/library/ms345146.aspx
HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Lee (lee.jenkins.ca@.gmail.com) writes:

Quote:

Originally Posted by

I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).

It shouldn't take 1-2 hours to move 100.000 rows. It sounds like the
process is not well implemented, or that there are indexes missing. Yes,
you can gain speed by using BCP, but you also add complexity to the
solution that I can't really see should be needed with the volumes you
indicate?

Would it be possible for you to post the definition of the tables, including
indexes and the stored procedure?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Plamen, Thanks a lot. I will try it and let you know the result.
Thanks again!

On Apr 23, 2:08 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Yes, BCP will be a good option for fast data transfer. All of the BULK
operations (BULK INSERT, SELECT INTO, BCP) are minimally logged when a non
FULL recovery model is set.
>
Another issue could be the purging of the archived records from your main
table. If you have it as a single DELETE and it takes long time to complete,
then you can break it into smaller DELETE chunks.
>
If you have SQL Server 2005 Enterprise Edition, an interesting alternative
is to use partitioned tables. Specifically range partitions based on date
ranges (in your case could be weekly) can help with archiving. Take a look
at the following article (in particular the section about Range Partitions):http://msdn2.microsoft.com/en-us/library/ms345146.aspx
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

|||Erland, Thanks a lot for the reply, also forgot to say thanks to Brad,
Here is the table:

CREATE TABLE [dbo].[tbl_record](
[record_id] [int] IDENTITY(1,1) NOT NULL,
[record_CC_id] [int] NOT NULL,
[record_content] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF_tbl_record_record_content] DEFAULT (''),
[record_date] [datetime] NOT NULL CONSTRAINT
[DF_tbl_record_record_date] DEFAULT (getdate()),
[record_ip] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF_tbl_record_record_ip] DEFAULT (''),
[record_active] [bit] NOT NULL CONSTRAINT
[DF_tbl_record_record_archive] DEFAULT (1),
CONSTRAINT [PK_tbl_record] PRIMARY KEY CLUSTERED
(
[record_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON
[PRIMARY]
) ON [PRIMARY]

And The stored procedure is here:

ALTER PROCEDURE [dbo].[ArchiveRecords]
(
@.ddate datetime
)
AS
BEGIN TRAN
SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive ON;
INSERT INTO record_archive.dbo.tbl_record_archive
(
record_id,
record_CC_id,
record_content,
record_date,
record_ip,
record_active
)
SELECT
record_id,
record_CC_id,
record_content,
record_date,
record_ip,
record_active
FROM tbl_record WHERE record_date <= @.ddate;
DELETE FROM tbl_record WHERE record_date <= @.ddate;
SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive OFF;
IF @.@.ERROR = 0 BEGIN COMMIT TRAN END ELSE BEGIN ROLLBACK TRAN END

On Apr 23, 2:31 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Lee (lee.jenkins...@.gmail.com) writes:

Quote:

Originally Posted by

I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).

>
It shouldn't take 1-2 hours to move 100.000 rows. It sounds like the
process is not well implemented, or that there are indexes missing. Yes,
you can gain speed by using BCP, but you also add complexity to the
solution that I can't really see should be needed with the volumes you
indicate?
>
Would it be possible for you to post the definition of the tables, including
indexes and the stored procedure?
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||Should I remove the clusterd index on the record_id field and create
nonclustered index on this field and create a clustered index on
record_date field since in my query, I always select a range of data
by date.

On Apr 23, 3:16 pm, Lee <lee.jenkins...@.gmail.comwrote:

Quote:

Originally Posted by

Erland, Thanks a lot for the reply, also forgot to say thanks to Brad,
Here is the table:
>
CREATE TABLE [dbo].[tbl_record](
[record_id] [int] IDENTITY(1,1) NOT NULL,
[record_CC_id] [int] NOT NULL,
[record_content] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF_tbl_record_record_content] DEFAULT (''),
[record_date] [datetime] NOT NULL CONSTRAINT
[DF_tbl_record_record_date] DEFAULT (getdate()),
[record_ip] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF_tbl_record_record_ip] DEFAULT (''),
[record_active] [bit] NOT NULL CONSTRAINT
[DF_tbl_record_record_archive] DEFAULT (1),
CONSTRAINT [PK_tbl_record] PRIMARY KEY CLUSTERED
(
[record_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON
[PRIMARY]
) ON [PRIMARY]
>
And The stored procedure is here:
>
ALTER PROCEDURE [dbo].[ArchiveRecords]
(
@.ddate datetime
)
AS
BEGIN TRAN
SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive ON;
INSERT INTO record_archive.dbo.tbl_record_archive
(
record_id,
record_CC_id,
record_content,
record_date,
record_ip,
record_active
)
SELECT
record_id,
record_CC_id,
record_content,
record_date,
record_ip,
record_active
FROM tbl_record WHERE record_date <= @.ddate;
DELETE FROM tbl_record WHERE record_date <= @.ddate;
SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive OFF;
IF @.@.ERROR = 0 BEGIN COMMIT TRAN END ELSE BEGIN ROLLBACK TRAN END
>
On Apr 23, 2:31 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
>
>

Quote:

Originally Posted by

Lee (lee.jenkins...@.gmail.com) writes:

Quote:

Originally Posted by

I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).

>

Quote:

Originally Posted by

It shouldn't take 1-2 hours to move 100.000 rows. It sounds like the
process is not well implemented, or that there are indexes missing. Yes,
you can gain speed by using BCP, but you also add complexity to the
solution that I can't really see should be needed with the volumes you
indicate?

>

Quote:

Originally Posted by

Would it be possible for you to post the definition of the tables, including
indexes and the stored procedure?

>

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

>

Quote:

Originally Posted by

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

>
- Show quoted text -

|||Brad, Thanks for the reply, my situation is the target table already
have lots of records and I will just append the data to that table.

On Apr 23, 1:58 pm, Brad <Brad.Marsh...@.Teksouth.comwrote:

Quote:

Originally Posted by

On Apr 23, 2:23 pm, Lee <lee.jenkins...@.gmail.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

guys,

>

Quote:

Originally Posted by

I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).

>

Quote:

Originally Posted by

My question is if there is some other way I should do to speed up the
action, I am thinking about use bcp to copy those records to datafile
and then use bcp to insert it into SQL Server table. Is this the right
way to do it or should I consider other solution (then, what is the
solution.)

>

Quote:

Originally Posted by

Thanks a lot!

>
Use a Select Into statement and make sure the destination db is set to
a simple recovery model.- Hide quoted text -
>
- Show quoted text -

|||Lee (lee.jenkins.ca@.gmail.com) writes:

Quote:

Originally Posted by

Should I remove the clusterd index on the record_id field and create
nonclustered index on this field and create a clustered index on
record_date field since in my query, I always select a range of data
by date.

Yes, that was precisely my reaction when I saw the table. Make the primary
key on record_id non-clustered, and add a clustered index on the date
column. I would guess you should do this on the archive table as well.

Also, I don't see the point with having the IDENTITY property on the
archive table. Just make it a normal column, and you don't need that
SET IDENTITY_INSERT. Not that it affects performance, but it looks cleaner.
However to change this, you would need rename the existing table, create
it a new and copy over. There is no ALTER syntax for changing the
IDENTITY property.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Question about installation. Please help. ASAP Urgent~!

My computer install SQL server express, when i try to develop my project, I found out there are some tools missing from this edition. So i try to upgrade to enterprise edition. I setup the enterprise edition, it saying i already have exist copy of SQL server. It does not allow to upgrade. So i try to uninstall the SQL server express and install the enterprise edition. After i install the enterprise edition, it missing all the server service. In Surface Area Conforuration missing all the service. That's mean the program installed, but the service part is missing. Is there any way to get back those service?. I already try to install those package, no matter how i try to install those service package, it keep saying i didn't install any of them. Please help.

Hi jeffery,

Probably you forgot to check the services while setting up SQL Server Enterprise Edition. You should reinstall SQL Server to make those services available.

Ekrem ?nsoy
MCP, MCDST, MCDBA, MCAD.Net, MCSD.Net, MCSA, MCSE|||What is your operating system? SQL Server 2005 requires server operating systems like Windows 2003 Enterpise Edition with SP1.|||My system is XP professional. the SQL 05 express working perfectly... all i need is local SQL server. After uninstall the 05 express, all the service is missing. i already select all the service when installing SQL 05 enterprise, but it still missing all the service. Even going into surface area config. Still saying all the service is missing. Anyone can help me?

Question about installation. Please help. ASAP

My computer install SQL server express, when i try to develop my project, I found out there are some tools missing from this edition. So i try to upgrade to enterprise edition. I setup the enterprise edition, it saying i already have exist copy of SQL server. It does not allow to upgrade. So i try to uninstall the SQL server express and install the enterprise edition. After i install the enterprise edition, it missing all the server service. That's mean the program installed, but the service part is missing. Is there any way to get back those service?. Please help.

Moving to setup.

Mike

Question about database size and performance...

I have a project where I will be loading ~ 242 Million Rows of data to a
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
RogRoger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
2061.mspx" target="_blank">http://www.microsoft.com/resources/...r />
2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>

Question about database size and performance...

I have a project where I will be loading ~ 242 Million Rows of data to a
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
RogRoger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>

Question about database size and performance...

I have a project where I will be loading ~ 242 Million Rows of data to a
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
Rog
Roger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.
|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
http://www.microsoft.com/resources/d...rt5/c2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>
|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>

Question about Aggragation in Measure Group?

Hello,

I am working on a project using SQL Server BI Developement Studio 2005. While designing a cube, I would like create a Measure Group that shows me a count of students grouped by department or school. However, I am not able to see an aggragate function under Measure Group's properties that would take care of this? For a while I was thinking about using "ByAccount" function but my dimension doesn't have anything called Account. My dimension has following columns: studentid, term, majorid, [dept id], [dept name], [school id], [school name]. Is there a way to get a count of students grouped by dept or school under a Measure Group?

Thanks,

Below, you can see functions that I am able to see when designing Meaure Groups.

Sum

Count

Min

Max

DistinctCount

None

ByAccount

AverageOfChildren

FirstChild

LastChild

FirstNonEmpty

LastNonEmpty

Hi,

I suspect that you might didn't understand the concept of OLAP yet (sorry if I'm wrong with that assumption).

You first have to think about your data... what are facts, what are dimensions. Facts are the base of your measures. Dimensions are your grouping creterias.

In your case the fact table and the dimension table might be the same. You define a "count" measure on any of your fields.

You also define one or more dimensions with your grouping creterias (department, school or whatever you want)...

When you open your cube you will see the count of records you processed. You then can drilldown on each dimension you defined to group your count...

Sorry, this is only a very brief hint how you might start. I really can't give you a complete training on OLAP in this forum...

|||

Thank you Thomas. I am new to Olap and still struggling a little bit to get the concepts right. My understading of a Fact table was that it was the only place where you have columns that can be summed, addded ,aggragated and so on. However, it looks like it is not the case. Do you know where I could learn more about BI in SQL 2005? Is there a website or something with some info?

|||

Take a look for Mosha & Teo Lachev on Google.

Mosha has some good resources listed on his web site. Teo has a great book out.

Question about Aggragation in Measure Group?

Hello,

I am working on a project using SQL Server BI Developement Studio 2005. While designing a cube, I would like create a Measure Group that shows me a count of students grouped by department or school. However, I am not able to see an aggragate function under Measure Group's properties that would take care of this? For a while I was thinking about using "ByAccount" function but my dimension doesn't have anything called Account. My dimension has following columns: studentid, term, majorid, [dept id], [dept name], [school id], [school name]. Is there a way to get a count of students grouped by dept or school under a Measure Group?

Thanks,

Below, you can see functions that I am able to see when designing Meaure Groups.

Sum

Count

Min

Max

DistinctCount

None

ByAccount

AverageOfChildren

FirstChild

LastChild

FirstNonEmpty

LastNonEmpty

Hi,

I suspect that you might didn't understand the concept of OLAP yet (sorry if I'm wrong with that assumption).

You first have to think about your data... what are facts, what are dimensions. Facts are the base of your measures. Dimensions are your grouping creterias.

In your case the fact table and the dimension table might be the same. You define a "count" measure on any of your fields.

You also define one or more dimensions with your grouping creterias (department, school or whatever you want)...

When you open your cube you will see the count of records you processed. You then can drilldown on each dimension you defined to group your count...

Sorry, this is only a very brief hint how you might start. I really can't give you a complete training on OLAP in this forum...

|||

Thank you Thomas. I am new to Olap and still struggling a little bit to get the concepts right. My understading of a Fact table was that it was the only place where you have columns that can be summed, addded ,aggragated and so on. However, it looks like it is not the case. Do you know where I could learn more about BI in SQL 2005? Is there a website or something with some info?

|||

Take a look for Mosha & Teo Lachev on Google.

Mosha has some good resources listed on his web site. Teo has a great book out.

Question

Hello every body.

I have a problem with SQL Server. I have two computers (XP and Vista) I am developing my project sometimes on XP and sometimes on the Vista one.

Everything was fine until when I started working on login part of website. The XP is totally fine but when I run my website on my Vista to see and test, this error show up:

So I did a little search and aparently this is what I should do to fix this problem:

sp_configure 'user instances enabled','1'

RECONFIGURE;

GO

I used this code in SQL server Express and pressed Execute, but this was the error:

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 2
You do not have permission to run the RECONFIGURE statement.

Now I have no idea how to fix it any further.

I appreciate your help.

Thank you.

Try to login with admin or sa account which has admin prievelage on Sql Server ...

|||

You might try logging in as the Adminstrator on the XP machine before running your recongifuration script.

|||

Thank you very much, I tried it as Admin but still the same exact error is there.

Stiletto you said log in on XP as admin but my problem is not on XP its on Vista.

I actually tried the same thing before (I loged in as Admin) but now this is the second time and still the error is the same.

Do I need to uninstall the SQL server and install it back ? What do you think friends ?

|||

Check the permission with the sa or admin acount of sql provide it full permissions and try again ... uninstalling is not a solution i think so ..

|||

Do I need to attach my website's database before running that SQL code ?

And where do i have to check the permission ?

|||

Check the Permissions in the Sql Management Studio for the User ...

|||

Hi,

I am kind of new with this. Where am I suppose to do this (Checking user permissions). I mean which part of managment studio ?

What would generally cause this in the first place ? how come my XP is fine but Vista turned up to be like this ?

|||

I think you XP and Vista has different Sql Server installed. You need to goto open Sql Server Management Studio . Under the security node under the login check for the particular account. Click on the account you want to give permission .. check the server roles. You can enabled more roles.