No HTML page breaks and slow expanding rows

I have a report with a single table, single grouping level, single data set and no sub-reports. It has 3 rows for a grouping header and 3 rows per dataset row of detail. The detail rows are initially hidden and can be expanded by clicking on the header +. Its a fairly standard master-detail report.

Regardless of data size, I get NO page breaks in HTML. I have the Interactive size set to 8.5x11, KeepTogether is set to False, and PageBreakAtEnd is set to False. I would like it to break based on the visible grouping rows.

As it is now, everytime you expand any section, it takes forever to reload for a larger recordset.

I know that "HTML renderer and Preview (which are soft page break renderers) will ignore page breaks of conditionally hidden items and their children.", but how do I get this report to page break? I've seen a lot of posts on this, but none that seem to have an answer.

Anyone? Can I programmatically add the soft page breaks? The report is useless as it is now.|||

Thanks to others, I found a solution. Use the query to calculate the count of headers using the dense_rank() function:

select ...

dense_rank() over ( order by cl.last_name, cl.first_name, cl.client_id ) AS ClientRank

Then add a top level grouping on the table set to "Page Break at End" with the formula:

=Ceiling(Fields!ClientRank.Value/15)

replace 15 with how many group headers you want per page.

-Dave

sql

No Debugging and logging when a single script task is executed

Hi,

I cannot execute a script task in the VBA code window.

I cannot debug or log if I run a single script task from the right click Execute Task .

Every time I have to run the entire package in order to be able to debug.

What am I missing?

appreciate a help.

Gulden

try disabling all the other tasks and/or containers except the script task. then, execute the package.

|||Thank You.. I guess it is the only way..|||

Gulden wrote:

Thank You.. I guess it is the only way..

If memory serves correctly, yes, it IS the only way.

We hope this will change in the future. It certainly SHOULD be changed!

-Jamie

No Cluster to Cluster

We are going through a process of clustering windows 2000 & sql 2000. We
currently are running windows 2000 server & sql 2000 on a single server,
non-clustered of course. We have the procedures down for creating the
windows cluster and then the sql cluster, but have questions as to migrating
the existing sql data from the non-clustered hardware currently in use. Are
there any items to be aware of when moving sql data from a non-clustered
setup to a clustered setup? Does anyone have a recommendation for some
articles on the best practice to move the sql data to the cluster while
retaining logins, security, db maint plans, dts packages, and other sql
items?
TIA!
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
$
$ Nick Maxwell, MCSE 2k, CCNA , A+
$ Systems Engineer
$ Heartland Business Systems
$ http://www.hbs.net
$ nmaxwell@.nospam.hbs.net
$
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
The data/databases don't know or care if they are clustered. So you can
detach them from one machine and attach them to another with no problems.
However, you do need to be aware of these factors in a cluster:
* The databases (user and system) all have to be on a shared drive. If the
drive letter of that drive is different from the drive letter of the
originating instance, you have to take some extra steps which are outlined
in the KB article 314546 HOW TO: Move Databases Between Computers That Are
Running SQL Server http://support.microsoft.com/?id=314546.
* The SQL Server resource must be dependent on any drive with SQL Server
data or backups.
* If you plan to upgrade the existing instance to a clustered instance, you
can do so using setup as long as the data is already moved to a shared
drive. So create the Windows cluster, add a shared drive in its own group
that you plan to use for SQL Server, move all databases to that drive, then
run SQL Enterprise Edition setup to upgrade to a virtual server.
* If the virtual server name is different than the original server name,
run sp_dropserver/sp_addserver (local) to get @.@.servername correct. Note
that this only works when moving databases, not for renaming an existing
virtual instance. To rename an existing virtual instance you have to
reinstall with the correct name to get all the registry keys updated
properly.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Nice to have feature

If you ask ANY developer that has worked with both MySQL and MS SQL, every single one of them will say why hasn't microsoft given MS SQL the capability to do LIMITS as such in MySQL. I'm not impressed by any of the stupid work arounds either... they run heavy and crapout under a large record load.

Of course there are several other attributes that MS SQL has that MySQL can't offer, but again, MySQL is FREE and PAID tools should have all that a free competitor has plus some.

Is there any planned action to remedy this absurd oversight?

Could you please post a suggestion for this on http://connect.microsoft.com/sqlserver? Suggestions (and defect reports) filed on the Connect site go directly into our internal issue tracking system so we don't lose them. Suggestions from customers carry extra weight with the development team when we are prioritizing future work.

I'm afraid I haven't used MySQL, so I have no idea what "LIMIT" does. Is "LIMIT" like the ANSI syntax "SELECT TOP n ..."? If you could explain what you're looking for in terms of functionality, I might be able to help you out.

Thanks,
Steve

|||

Thank you for the url to post this fix suggestion, i will do that.

A limit is also known as a 'Range Result'. A 'must have' for pagination in any sort of program/database development to decrease the need to process, handle and bandwidth requirements to handle unnecessary large datasets. (As produced by 'TOP n' queries) Especially when dealing with hundreds of thousands of records.

Definition: Limit is used to limit your MySQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results. It is phrased as Limit X, Y and included at the end of your query. X is the starting point (remember the first record is 0) and Y is the duration (how many records to display).

SELECT * FROM `your_table` LIMIT 0, 10

This will display the first 10 results from the database.

SELECT * FROM `your_table` LIMIT 5, 5

This will show records 6, 7, 8, 9, and 10

I highly recommend that everyone trying out other SQL servers to not get stuck in the MS paradigm.

Let me know if you have any other questions regarding LIMITS.

|||

you have to roll your own sproc logic to do that in SQL Server.

That would be a very nice to have feature for future releases.

MySQL definately has some nice advantages.

Cheers,

|||

Have you looked at the new ROW_NUMBER syntax in SQL Server 2005?

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;

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

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||true... that is a work around for a STORED PROC. however, i'm not super interested in stored procs and again, it's a work around.|||Its a workaround until you get a straight solution |||

Actually, no... this isn't a stored proc. All of that code can be performed inline as ad-hoc SQL. I believe the term for this is a "Common Table Expression". You can do other cool stuff with it like recursive queries.

WITH common_table_expression (Transact-SQL):
http://msdn2.microsoft.com/en-us/library/ms175972.aspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Newbie: Merge Replication

I have setup merge replication and it appears to be working fine
multiple publishers to a single subscriber. with continuous push
subscription
If the subscribing SQL Server is unavailable for a period of time the
replication monitor shows an error
"could not connect to SERVERNAME"
Ok so fairly easy to understand.
Now when the subscribing server is available again it does not seem to
connect again. I can click the "start synchronizing" option from the
replication monitor, however I would have thought continuous updating would
reconnect when available.
If this is the case,
1. How can I make it connect/start sycnronising again sooner automatically,
without user invervention?
2. Am I able to call the start synchronising feature through commands so
that I can have a force update type feature through code.
Thanks in advance.
Cheers,
Tim
Timmeah,
you could schedule the merge agent to run once a minute rather than
continuously. Alternatively you could force the job steps to run in a loop.
To start the merge agent in TSQL you can use sp_start_job.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)