Need to cleardown a table due to disk space problems

I need to delete about 3 million rows from a table that is part of a merge
publication. I think that I will have to copy out the rows that I want to
keep into a temp table and truncate the table and then copy the rows back in.
My question is how best to go about this? I think that the best way is to go
into the publication properties and uncheck the table on the articles tab.
Then to carry out the same process of copying out the data to be kept,
truncate the table, then move the rows to be kept back in. Then add the
article back into the publication. In order for the article to replicated
after adding it back in would I have to do a snapshot or would it resume by
itself?
Russell,
this sounds OK. However if the publication already has a subscription, you
won't be able to remove the individual article and you'll have to drop the
entire subscription before proceeding. You could drop the subscription,
remove the rows on publisher and subscriber then do a nosync initialization.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Need to add dup rows to sp

I have a sp that is used for membership cards, there is a column Number_of_Cards. We enter the number of cards a member wants. I need to create duplicate rows in my results based on the number entered in this field for each member. Anyone have any Ideas how to generate dup rows based on this column?

Thanks for any thoughts,Well, I don't like the sounds of it ;-) , but this is how you would do it in your sproc:

DECLARE @.LoopCount int
SELECT @.LoopCount = 1
WHILE @.LoopCount <= @.CardCount
BEGIN
INSERT INTO...
SET @.LoopCount = @.LoopCount + 1
END

Note that Transact-SQL does not have a FOR...NEXT loop construct. You need to make do with a WHILE loop.

Terri|||Thank you, I will give it a try. I know this sounds bad, but it is for a merge to membership cards and I need to do it this way to use Microsoft Word for the process.

Thanks again.|||I have been working with the example and have run into an issue. I thought you might have an idea. I think the problem is that the first loop does the INTO temp_Cards then the next loop fails because the temp_Cards table already exists.

CREATE PROCEDURE dbo.eP_CardProcess2 (@.Start datetime,@.End datetime,@.LoopCount int)
AS SELECT @.LoopCount =1
WHILE @.LoopCount <= (Select dbo.tblPledges.Number_Of_Cards From dbo.tblPledges Where (dbo.tblPledges.StartDate BETWEEN @.Start AND @.End) AND (dbo.tblPledges.Print_Card = 1) AND (dbo.tblPledges.Cards_Printed=0))
BEGIN
SELECT dbo.tblPledges.Number_Of_Cards,dbo.tblPledges.Card_Start_Date,dbo.tblPledges.Card_End_Date,dbo.tblPledges.Alternate_Card_Name,dbo.tblGivers.Email,dbo.tblPledges.RenewalDate INTO #temp_Cards
FROM dbo.tblPledges INNER JOIN dbo.tblClient ON dbo.tblPledges.Client_ID = dbo.tblClient.Client_ID WHERE (dbo.tblPledges.StartDate BETWEEN @.Start AND @.End) AND (dbo.tblPledges.Print_Card = 1) AND (dbo.tblPledges.Cards_Printed=0)
SET @.LoopCount = @.LoopCount + 1
END

sql

Need to add a new column to an existing table with 37M rows

I have been trying a couple of methods to add a column to a table. Well adding the column hasnt been that difficult. The difficult part is when I need to update this newly added column with a value returned from a function. Even 1000 rows takes for ever to update in a transaction. Is there any one who has come across this stituation, please help.

Thanks in Advance.

Quote:

Originally Posted by codezilla

I have been trying a couple of methods to add a column to a table. Well adding the column hasnt been that difficult. The difficult part is when I need to update this newly added column with a value returned from a function. Even 1000 rows takes for ever to update in a transaction. Is there any one who has come across this stituation, please help.

Thanks in Advance.

am not sure there are other ways...maybe you could benchmark your UPDATE vs SELECT ...newfield = udf(para) into ... from...

depending on table that your updating (may have triggers, constraint). the cons of SELECT...INTO is also space on your db.

also, try if you can just use a CALCULATED FIELD. another one is to just use a function outside of db, that is if you don't need to keep this field and will be used primarily for display purposes.

Need the logic to get this done without cursors

Suppose in the table below the left column is called intNumber and right column is called strItem.I want to select the rows where the intNumber number appears for the first time(the ones marked with arrows), how do I do it?

for intNumber = 1 what is the logic to choose x rather than y ?

for intNumber = 2 what is the logic to choose z rather than m ?

|||

dilbert1947:

I want to select the rows where the intNumber number appears for the first time

As khtan is asking, what is the logic behind your requirement? SQL Server has no concept of "first" without some kind of data sorting.

Typically what you are asking for could be accomodated by this sort of query, where the non-unique columns(s) are aggregated in some manner:

SELECT intNumber, MIN(strItem) FROM myTable GROUP BY intNumber ORDER BY intNumber

|||

Are you trying to accomplish this from code on a page, or in your SQL data manager? If you are doing this from a page, you could use a function with logic such as:

dataset1 = SELECT DISTINCT id FROM tableName //populates with 1 entry for each different ID</P><P>while(dataset != null){ //loop until out of ID's</P><P>dataset2 = SELECT TOP 1 * FROM tableName WHERE id = dataset1.value; // grab top record for each distinct ID</P><P>//do something with data</P><P>dataset1.movenext // move to next record</P><P>}

|||

Sorry for the mess. That should read:

dataset1 = SELECT DISTINCT id FROM tableName //populates with 1 entry for each different ID

while(dataset != null){ //loop until out of ID's

dataset2 = SELECT TOP 1 * FROM tableName WHERE id = dataset1.value; // grab top record for each distinct ID

do something with data

dataset1.movenext // move to next record

}

Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in order
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...

> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
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
sql

Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in orde
r
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...

> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
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
--

Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in order
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...
> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
--
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
--

Need sql to return the result of a query as comma seperated values.

Hi,
I need a sql that returns thequery result as comma seperated list of values, instead of several rows. Below is the scenario...
Table Name - Customer
Columns - CustomerID, Join Date
Say below is the data of Customer table ...
CustomerID JoinDate
1 04/01/2005
2 01/03/2003
3 06/02/2004
4 01/05/2002
5 09/07/2005
Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.
Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()
This gives the below result as two rows.
CustomerID
1
5
But i need to get the result as '1,5' (comma seperated list of resulting values).
Any help is highly appreciated
Thanks in Advance
Ramesh

You need declare a variable :
declare @.value nvarchar(200)
Select@.value=case when @.value is null then '' else @.value+',' end+cast(CustomerID as varchar) from Customer where JoinDate between '01/01/2005' and GetDate()
|||Hi,
You can manage your goal by using COALESCE
Please check the following URLs for sample COALESCE usage for returning the column values of a tables in a string seperated by a delimeter character.
http://www.kodyaz.com/ShowPost.aspx?PostID=76
http://www.kodyaz.com/article.aspx?ArticleID=29

As a sample you can run the below code on Northwind database also
DECLARE @.s as nvarchar(4000)
DECLARE @.char as char(1)
SELECT @.char = ','
SELECT @.s = @.char
SELECT @.s = COALESCE(FirstName + @.char + @.s , '') FROM Employees
SELECT SUBSTRING(@.s, 0, LEN(@.s)-1) AS Employees

I hope this helps,
Eralper
|||

Hi All,

I Solved the problem with the below query...

DECLARE @.CustomerIDs VARCHAR(8000)

SELECT @.CustomerIDs = ISNULL(@.CustomerIDs + ',', '') + CAST(CustomerID AS VARCHAR(10))
FROM CUSTOMER
WHERE JoinDate BETWEEN '01/01/2005' and GetDate()

SELECT @.CustomerIDs AS CustomerID

|||It's great! It really worked fine. Thanks a lot...

need some help, making many rows out of one, millions of times

so here's the deal:

i'm getting data in the form of an access db, which may be changed to a txt file due to size. each record has 2 columns at the end, the fields are EffFrom and EffTo, which are of type date and specify the date range for which the rest of the data in the record is valid. Here's the problem, i need to take those ranges and create a row for each day. i.e. if the range is 9/1/2003 to 9/15/2003 i would need 15 rows all with the same data except for a new date field which will replace efffrom and effto. seems like a cursor/loop issue to me, BUT there will eventually be millions of rows that need to be manipulated in this fashion. i started writing a stored procedure that will convert the data, do the necessary lookups [a few of the fields need to be resolved into numerical values before inserting them into the main table], but when i get to the point where i'm pulling the temp table into a cursor then going through row by row and making anywhere from 1 to 365 rows out of each row in the cursor, i'm shaking my head and feeling like there has to be a better way.

Ultimately, i'd like to do it through DTS, but i'm not very crafty with VBScript and opted to go the stored procedure/temp table route.

Here's what the data looks like

location_code1 varchar (will become int through lookup)
location_code2 varchar (will become int through lookup)
deptime varchar (string manipulation being done to add ':')
arrtime varchar (string manipulation being done to add ':')
carriercode varchar (will become int through lookup)
efffrom date
effto date -- described above

does anybody have some quick/dirty code or methods of creating multiple rows from one based on a date range [i know this goes against normalization, but the application requires the data to be this way and it cannot be rewritten]..or some DTS advice?

i'm stumped and in dire need of some inspiration. thanks in advance.See this thread for help on using a table of sequential values to "fill in" dates in a date range:

http://dbforums.com/showthread.php?threadid=914261

Once you create your sequential value table, use it in a query like this:

Select YourFields,
dateadd(dd, SequentialValue, EffFrom) as OnDate
from YourTable, SequentialValues
where SequentialValues.SequentialValue < DateDiff(dd, EffFrom, EffTo)

I didn't check this code for one-off errors or parameter order, but you should be able to get an idea of what you need to do.

Note: this method works well, but if you are going to use it against a table with millions of rows, don't include sequential values in your table greater than the largest datespan you expect, in order to keep the runtime down.

blindman|||awesome...the data is definitely lookin good as far as generating the multiple rows...now to incorporate this into a huge data load.

would you suggest a stored procedure with a variable of type table then a mass insert? or something through DTS? i'll prob try a few different methods and evaluate the speed, but any advice would be appreciated.

thanks!!!!|||I've never liked DTS, and use it mostly for simple data transfers.

A temporary table would probably process fastest, but a permanent table would only need to be created once. Either way probably won't make a large difference.

blindman

Need some help with subtotals in a matrix

Hello all,

This is all being done under SQL2000 and VS2003

I have a matrix report which is showing user information. The Rows are displaying numbers for each user, and the columns show the user info in weekly increments. I have 7 fields of info for each user. My stored procedure already is set up to give me the correct numbers. I dont need to SUM them or anything. Although in the report designer it forced me to SUM them since it was part of an aggregate. This still worked for me anyhow because it was Summing a single value.

However, at the end of the report i want to display totals for all the users combined, per week. So right now the report is showing 21 weeks, so at the end of the report i should have 21 sets of totals.

I right clicked on the users name column and selected subtotal. This gave me some of what i want. But some of the numbers are not correct. Some of the numbers should not just be a simple SUM of the column. Some of the values should be averages etc. I know how to calculate those values myself (its very simple math) but i dont know how to do it using this setup in the report designer. So in the matrix, for each week, how can i calculate the totals for all the users combined and specify the formula used to get the totals for each field?

thanks
I think i might have found a way to fix this. I added a table to the matrix, grouped the records by the weeks, and then displayed what fields i wanted in the table header.

however, i need for all the records returned to be displayed in new columns, not new rows. If i can get it displayed in new colums, it'll look like its just part of the existing matrix.

So instead of new records repeating by adding a new row, i need to know how to get the new records to come up as a new column.

Or is there a better way?

Need some help with dropping certain rows in a sql table ****HELP! ****

Hello,
I need to get some help with my T-SQL statements. Basically, I have
the follwing
data in a SQLServer table and it looks something like this:
Current data:
Vendor Item Frequency Price
AA 101 25 10.50
AA 102 10 20.50
AA 103 2 9.75
AA 104 2 10.99
AA 105 1 10.99
BB 101 25 10.50
BB 102 1020.50
BB 103 29.75
BB 104 210.99
BB 105 221.25
BB 106 121.25
Desired result:
Vendor Item Frequency Price
AA 101 25 10.50
AA 102 10 20.50
AA 104 2 10.99
BB 101 2510.50
BB 102 1020.50
BB 105 221.25
The logic basically says: excludes Frequency <= 1, if the Frequency is
equal, then
choose the item which has the highest price and excludes the others
that have the
same frequency. That is it.
I would like to know the result could be achieved with a single T-SQL
statement?
If yes, please show me how. If not, please show your best
alternative.
Thank you in advance!
Here is one way to accomplish this using a single statement (SQL Server
2005):
CREATE TABLE VendorItems (
vendor CHAR(2),
item INT,
frequency INT,
price DECIMAL(12, 2),
PRIMARY KEY (vendor, item));
INSERT INTO VendorItems VALUES ('AA', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('AA', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('AA', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('AA', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('AA', 105, 1, 10.99);
INSERT INTO VendorItems VALUES ('BB', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('BB', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('BB', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('BB', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('BB', 105, 2, 21.25);
INSERT INTO VendorItems VALUES ('BB', 106, 1, 21.25);
;WITH RankedVendorItems
AS
(SELECT vendor, item, frequency, price,
ROW_NUMBER() OVER(
PARTITION BY vendor, frequency
ORDER BY price DESC) AS seq
FROM VendorItems
WHERE frequency > 1)
SELECT vendor, item, frequency, price
FROM RankedVendorItems
WHERE seq = 1
ORDER BY vendor, item;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Need some help with dropping certain rows in a sql table ****

Hello,
I need to get some help with my T-SQL statements. Basically, I have
the follwing
data in a SQLServer table and it looks something like this:
Current data:
Vendor Item Frequency Price
----
AA 101 25 10.50
AA 102 10 20.50
AA 103 2 9.75
AA 104 2 10.99
AA 105 1 10.99
BB 101 25 10.50
BB 102 10 20.50
BB 103 2 9.75
BB 104 2 10.99
BB 105 2 21.25
BB 106 1 21.25
----
Desired result:
Vendor Item Frequency Price
----
AA 101 25 10.50
AA 102 10 20.50
AA 104 2 10.99
BB 101 25 10.50
BB 102 10 20.50
BB 105 2 21.25
----
The logic basically says: excludes Frequency <= 1, if the Frequency is
equal, then
choose the item which has the highest price and excludes the others
that have the
same frequency. That is it.
I would like to know the result could be achieved with a single T-SQL
statement?
If yes, please show me how. If not, please show your best
alternative.
Thank you in advance!Here is one way to accomplish this using a single statement (SQL Server
2005):
CREATE TABLE VendorItems (
vendor CHAR(2),
item INT,
frequency INT,
price DECIMAL(12, 2),
PRIMARY KEY (vendor, item));
INSERT INTO VendorItems VALUES ('AA', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('AA', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('AA', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('AA', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('AA', 105, 1, 10.99);
INSERT INTO VendorItems VALUES ('BB', 101, 25, 10.50);
INSERT INTO VendorItems VALUES ('BB', 102, 10, 20.50);
INSERT INTO VendorItems VALUES ('BB', 103, 2, 9.75);
INSERT INTO VendorItems VALUES ('BB', 104, 2, 10.99);
INSERT INTO VendorItems VALUES ('BB', 105, 2, 21.25);
INSERT INTO VendorItems VALUES ('BB', 106, 1, 21.25);
;WITH RankedVendorItems
AS
(SELECT vendor, item, frequency, price,
ROW_NUMBER() OVER(
PARTITION BY vendor, frequency
ORDER BY price DESC) AS seq
FROM VendorItems
WHERE frequency > 1)
SELECT vendor, item, frequency, price
FROM RankedVendorItems
WHERE seq = 1
ORDER BY vendor, item;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Need script that can shrink copy of DB to fit on a notebook

I need a script that will take a 40GB 300+ table database and shrink it to the 1st 1000 rows in each table and delete security tables like tblchargecard. Want to get size to about 1gb to fit on a notebook for development. Any suggestions would be appreciated.declare a table variable with two columns, table_name and Table_rowcount.

From a join between sysindexes and sysobjetcs table, get the table names and their respective rowcounts into this table variable.

update the table_rowcount columns with table_rowcount-1000

write a script to automatically generate delete statements for each table, each delete statement being preceded by set rowcount table_rowcount and followed by set rowcount 0 statement.

run this generated script.|||Creative, but I think that will crash if you have relational integrity established, and especially if you are using cascading deletes.

If your database does have cascading deletes, (as it should) then just delete everything but, say, every 10th record, out of the highest level tables in the schema. (You can use something like WHERE Right(PrimaryKey, 1) <> 0 if you have numeric keys, for instance.) Do this in a copy of the database, of course!

As far as "delete security tables like tblchargecard", you'll have to specify those in your script.|||Thanks for the help from both of you. will give this a try.

Need more table rows displayed on page

Right now I have a report with groupings and each full set takes up quite a
bit of room. So right now it only displays 10 sets per page, but I'd like to
increase this to 30. How can I make a table display more information before
making a new page?Change the page size... It is in the report properties menu item...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
news:AF670A51-C85B-43AB-89E6-10323C8D2950@.microsoft.com...
> Right now I have a report with groupings and each full set takes up quite
> a
> bit of room. So right now it only displays 10 sets per page, but I'd like
> to
> increase this to 30. How can I make a table display more information
> before
> making a new page?|||Thanks, ended up being a small fix. I'd rather there be some type of exact
number since now it can differ from page to page since some entries are
larger/smaller, but this works fine.
"Wayne Snyder" wrote:
> Change the page size... It is in the report properties menu item...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
> news:AF670A51-C85B-43AB-89E6-10323C8D2950@.microsoft.com...
> > Right now I have a report with groupings and each full set takes up quite
> > a
> > bit of room. So right now it only displays 10 sets per page, but I'd like
> > to
> > increase this to 30. How can I make a table display more information
> > before
> > making a new page?
>
>|||I have an example on www.msbicentral in the RDL Downloads section which
shows how to set a fixed number of rows/page to display... Its not very
hard...The filename is PagedTableParameters.RDL
Hope this helps
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
news:F9921495-BF23-42A9-ADB6-AB5FB3319188@.microsoft.com...
> Thanks, ended up being a small fix. I'd rather there be some type of exact
> number since now it can differ from page to page since some entries are
> larger/smaller, but this works fine.
> "Wayne Snyder" wrote:
>> Change the page size... It is in the report properties menu item...
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
>> news:AF670A51-C85B-43AB-89E6-10323C8D2950@.microsoft.com...
>> > Right now I have a report with groupings and each full set takes up
>> > quite
>> > a
>> > bit of room. So right now it only displays 10 sets per page, but I'd
>> > like
>> > to
>> > increase this to 30. How can I make a table display more information
>> > before
>> > making a new page?
>>

Need magic trick to set index as PK

Let's say that you have a 10gb table, 23m rows, and somehow it was created
and populated with ten indexes, including one that is clustered and unique,
but none of them seems to be marked as the Primary Key. Is there any magic
trick we can do to get it instantly marked as Primary Key?
This is SQLServer 2005.
What we ... er, that is, my hypothetical friend ... does not want to do, is
to drop the clustered index, rewriting the 10gb table, then define the PK,
rewriting the 10gb table again. I believe I read that SQLServer 2005 has a
new trick that might let it move the clustered index in *one* copy instead of
two, but how about *no* copies instead of one?
It occurs to me as I write this, that dropping all the secondary indexes
might be a good first step.
Thanks for good advice,
Josh
ps - the clustered unique index that is not the PK, is already conveniently
*named* XPKblahblahblah.
pps - will it make any difference to anything that uses that database, that
we do have the unique clustered key on the right fields, but it is not the
Primary Key?
Beleive it or not, if you fully qualify the foreign key constraint, you will
be able to create it against the unique index.
Wierd, but true.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
> Let's say that you have a 10gb table, 23m rows, and somehow it was created
> and populated with ten indexes, including one that is clustered and
> unique,
> but none of them seems to be marked as the Primary Key. Is there any
> magic
> trick we can do to get it instantly marked as Primary Key?
> This is SQLServer 2005.
> What we ... er, that is, my hypothetical friend ... does not want to do,
> is
> to drop the clustered index, rewriting the 10gb table, then define the PK,
> rewriting the 10gb table again. I believe I read that SQLServer 2005 has
> a
> new trick that might let it move the clustered index in *one* copy instead
> of
> two, but how about *no* copies instead of one?
> It occurs to me as I write this, that dropping all the secondary indexes
> might be a good first step.
> Thanks for good advice,
> Josh
> ps - the clustered unique index that is not the PK, is already
> conveniently
> *named* XPKblahblahblah.
> pps - will it make any difference to anything that uses that database,
> that
> we do have the unique clustered key on the right fields, but it is not the
> Primary Key?
|||On Fri, 9 Nov 2007 16:45:05 -0800, "Jay" <nospam@.nospam.org> wrote:

>Beleive it or not, if you fully qualify the foreign key constraint, you will
>be able to create it against the unique index.
>Wierd, but true.
Right, was doing that on SQL7 when replication demanded PK be a GUID.
I'd still like to fiddle the bits so the little gold keys show up on
the index.
(kind of academic now, actually, the guy involved spent the two hours
rewriting the 10gb table, bless the new fast hardware!)
(and actually, with the new SQL2005 features, I guess the trick is you
DON'T first delete the secondary indexes)
J.

>"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
>news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
>

Need magic trick to set index as PK

Let's say that you have a 10gb table, 23m rows, and somehow it was created
and populated with ten indexes, including one that is clustered and unique,
but none of them seems to be marked as the Primary Key. Is there any magic
trick we can do to get it instantly marked as Primary Key?
This is SQLServer 2005.
What we ... er, that is, my hypothetical friend ... does not want to do, is
to drop the clustered index, rewriting the 10gb table, then define the PK,
rewriting the 10gb table again. I believe I read that SQLServer 2005 has a
new trick that might let it move the clustered index in *one* copy instead of
two, but how about *no* copies instead of one'
It occurs to me as I write this, that dropping all the secondary indexes
might be a good first step.
Thanks for good advice,
Josh
ps - the clustered unique index that is not the PK, is already conveniently
*named* XPKblahblahblah.
pps - will it make any difference to anything that uses that database, that
we do have the unique clustered key on the right fields, but it is not the
Primary Key?Beleive it or not, if you fully qualify the foreign key constraint, you will
be able to create it against the unique index.
Wierd, but true.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
> Let's say that you have a 10gb table, 23m rows, and somehow it was created
> and populated with ten indexes, including one that is clustered and
> unique,
> but none of them seems to be marked as the Primary Key. Is there any
> magic
> trick we can do to get it instantly marked as Primary Key?
> This is SQLServer 2005.
> What we ... er, that is, my hypothetical friend ... does not want to do,
> is
> to drop the clustered index, rewriting the 10gb table, then define the PK,
> rewriting the 10gb table again. I believe I read that SQLServer 2005 has
> a
> new trick that might let it move the clustered index in *one* copy instead
> of
> two, but how about *no* copies instead of one'
> It occurs to me as I write this, that dropping all the secondary indexes
> might be a good first step.
> Thanks for good advice,
> Josh
> ps - the clustered unique index that is not the PK, is already
> conveniently
> *named* XPKblahblahblah.
> pps - will it make any difference to anything that uses that database,
> that
> we do have the unique clustered key on the right fields, but it is not the
> Primary Key?|||On Fri, 9 Nov 2007 16:45:05 -0800, "Jay" <nospam@.nospam.org> wrote:
>Beleive it or not, if you fully qualify the foreign key constraint, you will
>be able to create it against the unique index.
>Wierd, but true.
Right, was doing that on SQL7 when replication demanded PK be a GUID.
I'd still like to fiddle the bits so the little gold keys show up on
the index.
(kind of academic now, actually, the guy involved spent the two hours
rewriting the 10gb table, bless the new fast hardware!)
(and actually, with the new SQL2005 features, I guess the trick is you
DON'T first delete the secondary indexes)
J.
>"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
>news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
>> Let's say that you have a 10gb table, 23m rows, and somehow it was created
>> and populated with ten indexes, including one that is clustered and
>> unique,
>> but none of them seems to be marked as the Primary Key. Is there any
>> magic
>> trick we can do to get it instantly marked as Primary Key?
>> This is SQLServer 2005.
>> What we ... er, that is, my hypothetical friend ... does not want to do,
>> is
>> to drop the clustered index, rewriting the 10gb table, then define the PK,
>> rewriting the 10gb table again. I believe I read that SQLServer 2005 has
>> a
>> new trick that might let it move the clustered index in *one* copy instead
>> of
>> two, but how about *no* copies instead of one'
>> It occurs to me as I write this, that dropping all the secondary indexes
>> might be a good first step.
>> Thanks for good advice,
>> Josh
>> ps - the clustered unique index that is not the PK, is already
>> conveniently
>> *named* XPKblahblahblah.
>> pps - will it make any difference to anything that uses that database,
>> that
>> we do have the unique clustered key on the right fields, but it is not the
>> Primary Key?
>

Need magic trick to set index as PK

Let's say that you have a 10gb table, 23m rows, and somehow it was created
and populated with ten indexes, including one that is clustered and unique,
but none of them seems to be marked as the Primary Key. Is there any magic
trick we can do to get it instantly marked as Primary Key?
This is SQLServer 2005.
What we ... er, that is, my hypothetical friend ... does not want to do, is
to drop the clustered index, rewriting the 10gb table, then define the PK,
rewriting the 10gb table again. I believe I read that SQLServer 2005 has a
new trick that might let it move the clustered index in *one* copy instead o
f
two, but how about *no* copies instead of one'
It occurs to me as I write this, that dropping all the secondary indexes
might be a good first step.
Thanks for good advice,
Josh
ps - the clustered unique index that is not the PK, is already conveniently
*named* XPKblahblahblah.
pps - will it make any difference to anything that uses that database, that
we do have the unique clustered key on the right fields, but it is not the
Primary Key?Beleive it or not, if you fully qualify the foreign key constraint, you will
be able to create it against the unique index.
Wierd, but true.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
> Let's say that you have a 10gb table, 23m rows, and somehow it was created
> and populated with ten indexes, including one that is clustered and
> unique,
> but none of them seems to be marked as the Primary Key. Is there any
> magic
> trick we can do to get it instantly marked as Primary Key?
> This is SQLServer 2005.
> What we ... er, that is, my hypothetical friend ... does not want to do,
> is
> to drop the clustered index, rewriting the 10gb table, then define the PK,
> rewriting the 10gb table again. I believe I read that SQLServer 2005 has
> a
> new trick that might let it move the clustered index in *one* copy instead
> of
> two, but how about *no* copies instead of one'
> It occurs to me as I write this, that dropping all the secondary indexes
> might be a good first step.
> Thanks for good advice,
> Josh
> ps - the clustered unique index that is not the PK, is already
> conveniently
> *named* XPKblahblahblah.
> pps - will it make any difference to anything that uses that database,
> that
> we do have the unique clustered key on the right fields, but it is not the
> Primary Key?|||On Fri, 9 Nov 2007 16:45:05 -0800, "Jay" <nospam@.nospam.org> wrote:

>Beleive it or not, if you fully qualify the foreign key constraint, you wil
l
>be able to create it against the unique index.
>Wierd, but true.
Right, was doing that on SQL7 when replication demanded PK be a GUID.
I'd still like to fiddle the bits so the little gold keys show up on
the index.
(kind of academic now, actually, the guy involved spent the two hours
rewriting the 10gb table, bless the new fast hardware!)
(and actually, with the new SQL2005 features, I guess the trick is you
DON'T first delete the secondary indexes)
J.

>"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
>news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
>

Need input...Creating Indexes on 37 million row table...

Hello, all. Looking for suggestions, input,
recommendations, etc. I'm running SQL 2K.
I have a table with 37 million rows that I need to create
about 5 new indexes on. The columns already exist. The
db is used for datawarehousing, and contains static
data...the only update is a monthly insert of about 300K
records. Other than that, users only query it all day.
The db is also running in Simple Recovery mode (no need to
log any transactions).
Realizing this will take a LONG time to run, I'd like to
find out the best/most efficient way/with minimum downtime
to get this done. Luckily, I have the luxury of
restricted access to the db while I perform this, if need
be.
So, I'd like to hear from the gurus on how to do this.
Thanks
RozThere really is no faster way to create an index other than to make sure
your log file (even in simple mode) is on a separate raid array than the
data or tempdb. If you have tempdb on a separate array than the data you
can specify the sort in tempdb option to speed it up some.
Andrew J. Kelly SQL MVP
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2746101c4636e$82f9ae10$a501280a@.phx
.gbl...
> Hello, all. Looking for suggestions, input,
> recommendations, etc. I'm running SQL 2K.
> I have a table with 37 million rows that I need to create
> about 5 new indexes on. The columns already exist. The
> db is used for datawarehousing, and contains static
> data...the only update is a monthly insert of about 300K
> records. Other than that, users only query it all day.
> The db is also running in Simple Recovery mode (no need to
> log any transactions).
> Realizing this will take a LONG time to run, I'd like to
> find out the best/most efficient way/with minimum downtime
> to get this done. Luckily, I have the luxury of
> restricted access to the db while I perform this, if need
> be.
> So, I'd like to hear from the gurus on how to do this.
> Thanks
> Roz

Need input...Creating Indexes on 37 million row table...

Hello, all. Looking for suggestions, input,
recommendations, etc. I'm running SQL 2K.
I have a table with 37 million rows that I need to create
about 5 new indexes on. The columns already exist. The
db is used for datawarehousing, and contains static
data...the only update is a monthly insert of about 300K
records. Other than that, users only query it all day.
The db is also running in Simple Recovery mode (no need to
log any transactions).
Realizing this will take a LONG time to run, I'd like to
find out the best/most efficient way/with minimum downtime
to get this done. Luckily, I have the luxury of
restricted access to the db while I perform this, if need
be.
So, I'd like to hear from the gurus on how to do this.
Thanks
Roz
There really is no faster way to create an index other than to make sure
your log file (even in simple mode) is on a separate raid array than the
data or tempdb. If you have tempdb on a separate array than the data you
can specify the sort in tempdb option to speed it up some.
Andrew J. Kelly SQL MVP
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2746101c4636e$82f9ae10$a501280a@.phx.gbl...
> Hello, all. Looking for suggestions, input,
> recommendations, etc. I'm running SQL 2K.
> I have a table with 37 million rows that I need to create
> about 5 new indexes on. The columns already exist. The
> db is used for datawarehousing, and contains static
> data...the only update is a monthly insert of about 300K
> records. Other than that, users only query it all day.
> The db is also running in Simple Recovery mode (no need to
> log any transactions).
> Realizing this will take a LONG time to run, I'd like to
> find out the best/most efficient way/with minimum downtime
> to get this done. Luckily, I have the luxury of
> restricted access to the db while I perform this, if need
> be.
> So, I'd like to hear from the gurus on how to do this.
> Thanks
> Roz

Need input...Creating Indexes on 37 million row table...

Hello, all. Looking for suggestions, input,
recommendations, etc. I'm running SQL 2K.
I have a table with 37 million rows that I need to create
about 5 new indexes on. The columns already exist. The
db is used for datawarehousing, and contains static
data...the only update is a monthly insert of about 300K
records. Other than that, users only query it all day.
The db is also running in Simple Recovery mode (no need to
log any transactions).
Realizing this will take a LONG time to run, I'd like to
find out the best/most efficient way/with minimum downtime
to get this done. Luckily, I have the luxury of
restricted access to the db while I perform this, if need
be.
So, I'd like to hear from the gurus on how to do this.
Thanks
RozThere really is no faster way to create an index other than to make sure
your log file (even in simple mode) is on a separate raid array than the
data or tempdb. If you have tempdb on a separate array than the data you
can specify the sort in tempdb option to speed it up some.
--
Andrew J. Kelly SQL MVP
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2746101c4636e$82f9ae10$a501280a@.phx.gbl...
> Hello, all. Looking for suggestions, input,
> recommendations, etc. I'm running SQL 2K.
> I have a table with 37 million rows that I need to create
> about 5 new indexes on. The columns already exist. The
> db is used for datawarehousing, and contains static
> data...the only update is a monthly insert of about 300K
> records. Other than that, users only query it all day.
> The db is also running in Simple Recovery mode (no need to
> log any transactions).
> Realizing this will take a LONG time to run, I'd like to
> find out the best/most efficient way/with minimum downtime
> to get this done. Luckily, I have the luxury of
> restricted access to the db while I perform this, if need
> be.
> So, I'd like to hear from the gurus on how to do this.
> Thanks
> Roz