Hidding report before deployment?

Hi,
does it possible to set the "hidden in list view" option at design time of
the report (or any other objects) in Visual studio prior a deployment?
Because I've a lot of reports and images to send to RS, but some of these
are hidden by default. My front end interface hidde these reports to the
user.
Thanks.
Jerome.No, not from report designer. If you use a script to deploy your reports,
you can manipulate these properties.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:%23M4yhsJWEHA.4056@.TK2MSFTNGP11.phx.gbl...
> Hi,
> does it possible to set the "hidden in list view" option at design time of
> the report (or any other objects) in Visual studio prior a deployment?
> Because I've a lot of reports and images to send to RS, but some of these
> are hidden by default. My front end interface hidde these reports to the
> user.
> Thanks.
> Jerome.
>|||So, can I add custom information in the RDL file?
My idea is:
Creating a program (or VS add in) to add some deployment option by report,
then my export application will read each report to retrieve my custom
information to know if the report must be hidden or not.
What do you think about this?
or
have you a sample script to deploy RS repotrs?
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> a écrit dans le
message de news:%23CeCTIKWEHA.4032@.TK2MSFTNGP11.phx.gbl...
> No, not from report designer. If you use a script to deploy your reports,
> you can manipulate these properties.
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
>
> "Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
> news:%23M4yhsJWEHA.4056@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > does it possible to set the "hidden in list view" option at design time
of
> > the report (or any other objects) in Visual studio prior a deployment?
> >
> > Because I've a lot of reports and images to send to RS, but some of
these
> > are hidden by default. My front end interface hidde these reports to the
> > user.
> >
> > Thanks.
> >
> > Jerome.
> >
> >
>|||There is a sample publish script included with the product that you could
use.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23mfVPiYWEHA.1152@.TK2MSFTNGP09.phx.gbl...
> So, can I add custom information in the RDL file?
> My idea is:
> Creating a program (or VS add in) to add some deployment option by report,
> then my export application will read each report to retrieve my custom
> information to know if the report must be hidden or not.
> What do you think about this?
> or
> have you a sample script to deploy RS repotrs?
>
> "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> a écrit dans le
> message de news:%23CeCTIKWEHA.4032@.TK2MSFTNGP11.phx.gbl...
>> No, not from report designer. If you use a script to deploy your
>> reports,
>> you can manipulate these properties.
>> -Lukasz
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>>
>> "Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
>> news:%23M4yhsJWEHA.4056@.TK2MSFTNGP11.phx.gbl...
>> > Hi,
>> >
>> > does it possible to set the "hidden in list view" option at design time
> of
>> > the report (or any other objects) in Visual studio prior a deployment?
>> >
>> > Because I've a lot of reports and images to send to RS, but some of
> these
>> > are hidden by default. My front end interface hidde these reports to
>> > the
>> > user.
>> >
>> > Thanks.
>> >
>> > Jerome.
>> >
>> >
>>
>sql

hidden field populating header/footer trick not working

I'm a bit stumped at this. I have done this before a bunch of times. The difference is that this time I have two rectangles in the footer. For the each, the visibility is set to an expression which checks whether the page is page 1 or 2. If it is 1, the first rectangle is visible, if 2, the second. When I reference the hidden fields on the main area of the report via ReportItems, they come through fine on the first rectangle, but are invisible on the second. I've tried the same exact fields and every time they are visible in rectangle1, and invisible in rectangle2. What is going on here?
Well, just switched the conditional visibility so that rect1 would go on page 2 and rect2 on page one, and guess what? Now these fields on invisible on rect1 and visible on rect2! So it has to do with the page number! Wow, how weird! Anyone see this before and is there any workaround?
|||

Expressions in the page header/footer can only reference report items on the current page. One thing to check is whether the hidden textbox in the report body is repeated on both pages? If the textbox only appears on the first page, expressions in the header/footer for the second page would always get a null value for this textbox.

sql

hidden field populating header/footer trick not working

I'm a bit stumped at this. I have done this before a bunch of times. The difference is that this time I have two rectangles in the footer. For the each, the visibility is set to an expression which checks whether the page is page 1 or 2. If it is 1, the first rectangle is visible, if 2, the second. When I reference the hidden fields on the main area of the report via ReportItems, they come through fine on the first rectangle, but are invisible on the second. I've tried the same exact fields and every time they are visible in rectangle1, and invisible in rectangle2. What is going on here?
Well, just switched the conditional visibility so that rect1 would go on page 2 and rect2 on page one, and guess what? Now these fields on invisible on rect1 and visible on rect2! So it has to do with the page number! Wow, how weird! Anyone see this before and is there any workaround?
|||

Expressions in the page header/footer can only reference report items on the current page. One thing to check is whether the hidden textbox in the report body is repeated on both pages? If the textbox only appears on the first page, expressions in the header/footer for the second page would always get a null value for this textbox.

Hidden columns/blank PDF page(s)

I have been trying for some time to create a columnar report where certain columns can be hidden as requested by the user. I have tried placing the complete column set in the report, and then hiding a subset of columns, but blank pages are produced when rendered to PDF since the report body does not appear to shrink as columns are hidden and justify themselves to the left. I have tried a table, list, and matrix to no avail...

Does anyone know of a workaround, a trick, or even a hack that may accomplish what I am after?

Thanks.

Oh well, guess there is no way to do it. I have seen many other posts of people trying to do the same thing. I wish someone from Microsoft would just say it's impossible so I could forget about trying to come up with a workaround... Doesn't seem like the functionality I am after is asking too much. Actually, I pretty much consider the PDF rendering the blank space to extra pages to be a rendering bug... Seems the renderer could be capable of clipping blank space... My 0.02 rant.|||I'm having the same issue so if anyone, especially someone from MSFT, has any ideas I'd be very eager to hear them.|||

Hello Iago,

I have found a work around for this problem. It’s a bit hacky, but does work.

1.Collapse all table columns to the smallest they can go so width = .125

2.Resize the DESIGN gird to the smallest it can go

3.Set the CanGrow property = FALSE for each cell that has text.This will suppress the vertical size of the text to the row height that you have specified

4.Create a new report in the same project.

5.Create a dummy dataset in the sub report.I usually use: SELECT NULL

6.Pull a table onto the grid and delete all but one cell

7. Set the cell = “”

8.Set the column width to the column width you had in the original report say .625

9.Return to your original report and create an additional header on the report

10. Drag a sub report into each column of the new header cell and set it equal to the new report

11. Run the report.Hidden columns now only take up the .125 space.

Hidden columns/blank PDF page(s)

I have been trying for some time to create a columnar report where certain columns can be hidden as requested by the user. I have tried placing the complete column set in the report, and then hiding a subset of columns, but blank pages are produced when rendered to PDF since the report body does not appear to shrink as columns are hidden and justify themselves to the left. I have tried a table, list, and matrix to no avail...

Does anyone know of a workaround, a trick, or even a hack that may accomplish what I am after?

Thanks.

Oh well, guess there is no way to do it. I have seen many other posts of people trying to do the same thing. I wish someone from Microsoft would just say it's impossible so I could forget about trying to come up with a workaround... Doesn't seem like the functionality I am after is asking too much. Actually, I pretty much consider the PDF rendering the blank space to extra pages to be a rendering bug... Seems the renderer could be capable of clipping blank space... My 0.02 rant.|||I'm having the same issue so if anyone, especially someone from MSFT, has any ideas I'd be very eager to hear them.|||

Hello Iago,

I have found a work around for this problem. It’s a bit hacky, but does work.

1.Collapse all table columns to the smallest they can go so width = .125

2.Resize the DESIGN gird to the smallest it can go

3.Set the CanGrow property = FALSE for each cell that has text.This will suppress the vertical size of the text to the row height that you have specified

4.Create a new report in the same project.

5.Create a dummy dataset in the sub report.I usually use: SELECT NULL

6.Pull a table onto the grid and delete all but one cell

7. Set the cell = “”

8.Set the column width to the column width you had in the original report say .625

9.Return to your original report and create an additional header on the report

10. Drag a sub report into each column of the new header cell and set it equal to the new report

11. Run the report.Hidden columns now only take up the .125 space.

Hidden columns/blank PDF page(s)

I have been trying for some time to create a columnar report where certain columns can be hidden as requested by the user. I have tried placing the complete column set in the report, and then hiding a subset of columns, but blank pages are produced when rendered to PDF since the report body does not appear to shrink as columns are hidden and justify themselves to the left. I have tried a table, list, and matrix to no avail...

Does anyone know of a workaround, a trick, or even a hack that may accomplish what I am after?

Thanks.

Oh well, guess there is no way to do it. I have seen many other posts of people trying to do the same thing. I wish someone from Microsoft would just say it's impossible so I could forget about trying to come up with a workaround... Doesn't seem like the functionality I am after is asking too much. Actually, I pretty much consider the PDF rendering the blank space to extra pages to be a rendering bug... Seems the renderer could be capable of clipping blank space... My 0.02 rant.|||I'm having the same issue so if anyone, especially someone from MSFT, has any ideas I'd be very eager to hear them.|||

Hello Iago,

I have found a work around for this problem. It’s a bit hacky, but does work.

1.Collapse all table columns to the smallest they can go so width = .125

2.Resize the DESIGN gird to the smallest it can go

3.Set the CanGrow property = FALSE for each cell that has text.This will suppress the vertical size of the text to the row height that you have specified

4.Create a new report in the same project.

5.Create a dummy dataset in the sub report.I usually use: SELECT NULL

6.Pull a table onto the grid and delete all but one cell

7. Set the cell = “”

8.Set the column width to the column width you had in the original report say .625

9.Return to your original report and create an additional header on the report

10. Drag a sub report into each column of the new header cell and set it equal to the new report

11. Run the report.Hidden columns now only take up the .125 space.

sql

Hidden columns/blank PDF page(s)

I have been trying for some time to create a columnar report where certain columns can be hidden as requested by the user. I have tried placing the complete column set in the report, and then hiding a subset of columns, but blank pages are produced when rendered to PDF since the report body does not appear to shrink as columns are hidden and justify themselves to the left. I have tried a table, list, and matrix to no avail...

Does anyone know of a workaround, a trick, or even a hack that may accomplish what I am after?

Thanks.

Oh well, guess there is no way to do it. I have seen many other posts of people trying to do the same thing. I wish someone from Microsoft would just say it's impossible so I could forget about trying to come up with a workaround... Doesn't seem like the functionality I am after is asking too much. Actually, I pretty much consider the PDF rendering the blank space to extra pages to be a rendering bug... Seems the renderer could be capable of clipping blank space... My 0.02 rant.|||I'm having the same issue so if anyone, especially someone from MSFT, has any ideas I'd be very eager to hear them.|||

Hello Iago,

I have found a work around for this problem. It’s a bit hacky, but does work.

1.Collapse all table columns to the smallest they can go so width = .125

2.Resize the DESIGN gird to the smallest it can go

3.Set the CanGrow property = FALSE for each cell that has text.This will suppress the vertical size of the text to the row height that you have specified

4.Create a new report in the same project.

5.Create a dummy dataset in the sub report.I usually use: SELECT NULL

6.Pull a table onto the grid and delete all but one cell

7. Set the cell = “”

8.Set the column width to the column width you had in the original report say .625

9.Return to your original report and create an additional header on the report

10. Drag a sub report into each column of the new header cell and set it equal to the new report

11. Run the report.Hidden columns now only take up the .125 space.

hi,need help.....

hi all...
i need help in writing a SQL query to display the date and time of the client last visited.

Quote:

Originally Posted by rocpraveen

hi all...
i need help in writing a SQL query to display the date and time of the client last visited.

Hi rocpraveen,

We are more than happy to help you with your query. But the only problem is we are not good at guessing other people's requirements with absolutely no information to go with. I might decide upon my own set of tables and columns and arrive at a suitable query but I guess it would not serve your purpose. So either you have not given enough information or we are just not good enough.

P.S: It's absolutely irritating when someone assumes that others will guess their requirements.|||Can u please post your table structure n what exactly u want to retrive.

And if possible post how u have tried to solve the problem .

It makes it easier for the experts in the forum in understanding your problem better.

hi Im facing problem in adding values to the database sqlserver2k

hi everyone,

I have a form in in that 3columns and 8rows default.. I have to store values for 8 rows at a time in to database..

I am working with vs2003, asp.net & C# behind the code.. so pls send me a code for implementing this.. asap

vijai

what problem you are facing mention it here. There are lot of scenarious how u want to add the data. Depends on your code and architecture...

|||

Hi Vijai,

Have you created the datatable yet and what about the table's design? Since you haven't told us the details, I just suggest some reference to you.

About how to implement data access in ASP.NET : http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/default.aspx
Something about SQL Basic reference: http://www.w3schools.com/sql/sql_insert.asp
About table creating:http://www.w3schools.com/sql/sql_create.asp or just using the wizard in the tool such like Sql Server enterprise manager.

Thanks.

Hi

Hi,

Somebody can speak (tips) about exam 70-431?

Tips?

Amount Questions?

Time ?

....

If you're looking for brain dumps this is not the site to get those answers. The 70-431 exam is a 200-level "how-to" exam on all the nuts and bolts of SQL Server 2005. There are free eLearning classes available on the Microsoft web site which will help you with the content information for the test, as well as a number of excellent books hitting the market about now.

I believe you'll get about 40 questions on the test, though it may be higher than that, and you'll get about 2 1/2 hours to answer them.

Go to www.microsoft.com/sql and click on Learning and you'll find out all you need to know to prepare for the test.

Good luck.

|||

What is the passing mark/percentage for MCTS?

|||

Hi Gunny,

is 700 scored.

|||

E X A M S P E C S
Exam Number:	70-431
Active / Retired:	Active
Prerequisites:
Exam Format:	Multiple-Choice; Multiple Answer
Num Questions:	55
Time Limit:	90
Cost (USD) $:	$125
Passing Score:	700

taken from cramsession's site: http://www.cramsession.com/certifications/exams/sql-server-2005.asp?exam_id=770

also use this code for 20% of the exam price

Call now to schedule your Microsoft exam call 1-800-247-8731 and use code MSUU5C7E0100 for your 20% discount.

|||

Many thanks WPH and rkohler.

That's 700 out of how many? What is the max possible score?

|||1000 max scored.|||

Thanks WPH.

|||

any one can respon

i have two server one is dpmain controller and second is exchange 2003 server i need to setup sql 2005

which one i choice ?

|||

a.m.m.e.

Your question really belongs in a separate/new thread. This thread is about MCTS.

But to answer your question, I would advise against putting it on the domain controller. The domain controller contains the Active Directory and security is extremely important. Adding any other role such as SQL Server would complicate security configuration for it.

Actually, if possible, I would set up a new dedicated SQL Server - not add it to the Exchange Server 2003. Mixing multiple roles on one server always complicates security. But if you cannot set up a new dedicated server I would add it to the Exchange Server 2003.

Other more experienced people may have different ideas.

|||

Hello everyone,

I would like to let you all know and be aware of the fact that microsoft has changed its exam pattern for

070-431 be very carefull as microsoft has changed the exam pattern and due to which i failed the exam today.

Its now based on simulations questions which comes after the multiple choice questions i got 15 simulations

separately apart from 35 multi choice questions along with exibits. I wasnt aware of this fact and it wasnt

mentioned ánywhere about simulations questions I think no one knows about it I am the first to face this type of

exam.. The exams comes up with 2 sections the first sections was questions that was 35 in 1hr and the next

was simulations that was 15 2hr 15mins so in total it was 195min exam and I got all correct in first section

and the second section was gred out 60% I got 560 out of 700 may not be difficult but very new.

If in case you have come across with any good site with simulations of this exam please forward me to

a_afroze@.yahoo.com

All the best to all of you

Abdul Afroze

|||

Hello everyone,

I would like to let you all know and be aware of the fact that microsoft has changed its exam pattern for

070-431 be very carefull as microsoft has changed the exam pattern and due to which i failed the exam today.

Its now based on simulations questions which comes after the multiple choice questions i got 15 simulations

separately apart from 35 multi choice questions along with exibits. I wasnt aware of this fact and it wasnt

mentioned ánywhere about simulations questions I think no one knows about it I am the first to face this type of

exam.. The exams comes up with 2 sections the first sections was questions that was 35 in 1hr and the next

was simulations that was 15 2hr 15mins so in total it was 195min exam and I got all correct in first section

and the second section was gred out 60% I got 560 out of 700 may not be difficult but very new.

If in case you have come across with any good site with simulations of this exam please forward me to

a_afroze@.yahoo.com

All the best to all of you

Abdul Afroze

|||

Thanks for the information. I'm going to be taking the test soon and was afraid they would do something like this. They did this to me on the 70-228... completely changed the content and format from what my rather expensive test prep materials were based on.

Could you give us a clue as to what the simulations focused on? (ie. TSQL, SSMS, etc.) I can do just about everything you need to do in SSMS and other GUI tools, but I don't have all the full syntaxes for the TSQL methods memorized (ie. create HTTP endpoint for SOAP vs. TCP endpoint for mirroring).

|||

Well, this really isn't a database documentation request, and using a questions-only site really isn't the best way to go. There are reputable firms, such as Transcender, that will help you with testing and so on. The point is that you should try and learn the trade, rather than just memorizing questions. I've posted an article that deals with certifications:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=131

And another here dealing with being a DBA. I think you may find those more useful than looking up test questions on a site.

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=247

These are just my thoughts - I do wish you luck in your studies!

|||

Many thanks, Abdul, for that "heads up".

If you or anyone else here come to know of any test exams based on this new pattern I am sure we all would be eager to know about them as soon as possible.

Regards

hhmmss Time format in SQL Server - How?

Hi !!

I am having difficulties working with SQL Server SmallDataTime...

I use TransactionDate as smalldatetime.. Now for reporting purpose my client needs

date as ccyymmdd format and
time as hhmmss format

I am able to get date in ccyymmdd using
CONVERT(varchar(8), @.fDate, 112) )

How do I get time in hhmmss format .. its little urgent pls help...

I tried using DATEPART(hh, @.fDate) DATEPART(mm, @.fDate) DATEPART(ss, @.fDate)
how do I make one string of hhmmss..

Is there a better way?How about this:

replace(Convert (varchar(8),GetDate(), 108),':','')

Is it going to work for you?
First, you get hh:mm:ss and then you replace : with empty string.|||

select convert(varchar, getdate(), 108)

hth|||Waow.. Replace convert combo worked perfectly fine..

Thx....|||Another problem I am running into is this

if I write SELECT LEN('2000') I get answer = 4

However I write function for this I get wrong answer... I always get 1

Here is my function.. can anyone tell whats wrong?
CREATE FUNCTION fnc_GetLen (@.str nvarchar)
RETURNS nvarchar(10)
AS
BEGIN

DECLARE @.L nvarchar(10)

SET @.L = LEN(@.str)
RETURN (@.L)
END|||Not so sure what the purpose of this function since it does exactly what LEN does.

Anyway, the problem is you did not give a length on the input string. Try change to this:

CREATE FUNCTION fnc_GetLen (@.str nvarchar(500))

|||I just need to find length.. once I find length I do processing inside that function.. like appending 0's or spaces etc..

I will give it a try.. thanks|||also your return type should be int and not nvarchar.

RETURNS int

hth|||Return nvarchar worked perfectly fine. No Problem at all...

I am using padding function and its giving me nightmare when I use blank space (white space) as padding char. Can you see whats wrong?

CREATE FUNCTION fnc_AddPadding ( @.text nvarchar(30), @.padChar char(1), @.maxLen int, @.padType char(1))
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @.resultText nvarchar(50)
SET @.resultText = ''

-- Left Padding
IF @.padType = 'L'
BEGIN
SET @.resultText =RIGHT( REPLICATE ( @.padChar, @.maxLen) + @.text, @.maxLen )
END

-- Right Padding
IF @.padType = 'R'
BEGIN
SET @.resultText = LEFT( @.text + REPLICATE ( @.padChar, @.maxLen) , @.maxLen )
END

-- No Padding
IF @.padType not in ( 'R', 'L')
BEGIN
SET @.resultText = NULL
END

RETURN @.resultText
END

For every other padding char it works ok.. but when I use white space as pad char it is bad...

My Return Type nvarchar(50) or nvarchar(512) and mann..... instead of 5 - 10 white space I end up with 50 or 512 white space...

HelpNeed to convert number to DateTime

Hi! I'm not very technical, so please excuse me if I don't make sense...

I'm pulling Date/Time data from an Oracle database using Crystal 10, however, instead of getting a number in DateTime format, I'm getting a really long number that does not convert when using the DateTime function (I get an error stating that the year is greater than 9999.)

The number is 1123876247 (the actual date should be 8/12/2005 12:50). Sound familiar? Does anyone have any ideas on how to convert this number?

Any help is greatly appreciated!! (I'm about to pull my hair out!)How is 1123876247 related to 8/12/2005 12:50?|||1123876247 is 8/12/2005 represented in seconds since a certain date. Since my last post, I have since found that date to be 01/01/1970. So, the formula is:

DateAdd("s", (11238876247), #1970/01/01#)

This works to convert the seconds into Date/Time format.

HELP: Point in time restore : HOW ?

I have full backup of database at 13:00
and another full backup at 17:00.
I've made backup of transaction log at 17:05
When I try to restore database to state at
15:10 (point in time) , the dialogue in Enterprise Manager
says that only time after 17:05 is valid.
It seems to me that I've done something wrong at 17:05 while taking
trans. log backup.
But, again, if I have full backups at 13:00 and 17:00 restoring
database to point in time at 15:10 should be possible ?!
Any help is appreciated.
Pagus
Hi Pagus,
Point in time recovery can be done with tlogs.... had you taken tlogs after
15:00 full backup?
Thanks
GYK
"Pagus" wrote:

> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>
|||To summarize what u currently have :
[1] First Full Database Backup @. 13:00
[2] Second Full Database Backup @. 17:00
[3] TLog @. 17:05
Trying to Point-In-Time restore to 15:10
Considering that the Point-In-Time restore is before the second DB backup,
the Second DB backup is irrelevent.
There are a few things that u shud check
[1] Was the DB in valid state when you issued the Backup @. 13:00
[2] Check the duration (Period) for the TLog backup. It should have an
end time of 17:05 and start time of what ?
Thanks,
Gopi
"Pagus" wrote:

> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>
|||Pagus <pagus@.writeme.com> wrote in message news:<5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com>. ..
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
Always used transaction logs to do a point in time restore.
Vincento
|||Hi there,
a point in time restore is only possible when using a transaction log
to do the restore. I know it probably doesnt help you but for future
reference here is the way to do it.
first restore the full backup leaving the database in no-recovery mode
then restore the transaction log backup to the point in time that you
want, this time recoverying the database after the restore completes.
Depending upon the size of your database it might be worth looking at
doing a full backup just once per day and several transaction log
backups throughout the day every hour, or couple of hours.

HELP: Point in time restore : HOW ?

I have full backup of database at 13:00
and another full backup at 17:00.

I've made backup of transaction log at 17:05

When I try to restore database to state at
15:10 (point in time) , the dialogue in Enterprise Manager
says that only time after 17:05 is valid.

It seems to me that I've done something wrong at 17:05 while taking
trans. log backup.

But, again, if I have full backups at 13:00 and 17:00 restoring
database to point in time at 15:10 should be possible ?!

Any help is appreciated.

PagusRestore the full backup from 13:00 with NORECOVERY
Restore the tLog backup to 15:10 with Recovery

Should work.

Rick

"Pagus" <pagus@.writeme.com> wrote in message
news:5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
> Restore the full backup from 13:00 with NORECOVERY
> Restore the tLog backup to 15:10 with Recovery

I have not done this, but you may want to restore tlog backup to 15:10 with
a standby file.

I THINK you can then restore in time past 15:10 again w/o having to do the
full restore.

(hmm, I should try this now that I think about... too tired right now
though. :-)

> Should work.
>
> Rick|||> (hmm, I should try this now that I think about... too tired right now
> though. :-)

No need to Greg. I have a demo script just for this. You can restore the same tlog several times,
going forward in time, using STANDBY. I also checked with MS and this is a tested and supported
method. Here are my comments on the topic:
http://www.karaszi.com/SQLServer/in...veral_times.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Mbndd.312510$bp1.26137@.twister.nyroc.rr.com.. .
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
>> Restore the full backup from 13:00 with NORECOVERY
>> Restore the tLog backup to 15:10 with Recovery
> I have not done this, but you may want to restore tlog backup to 15:10 with
> a standby file.
> I THINK you can then restore in time past 15:10 again w/o having to do the
> full restore.
> (hmm, I should try this now that I think about... too tired right now
> though. :-)
>
>>
>> Should work.
>>
>>
>> Rick
>>
>>|||Pagus <pagus@.writeme.com> wrote in message news:<5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com>...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus

Always used transaction logs to do a point in time restore.

Vincento|||Hi there,

a point in time restore is only possible when using a transaction log
to do the restore. I know it probably doesnt help you but for future
reference here is the way to do it.

first restore the full backup leaving the database in no-recovery mode
then restore the transaction log backup to the point in time that you
want, this time recoverying the database after the restore completes.

Depending upon the size of your database it might be worth looking at
doing a full backup just once per day and several transaction log
backups throughout the day every hour, or couple of hours.

HELP: Point in time restore : HOW ?

I have full backup of database at 13:00
and another full backup at 17:00.
I've made backup of transaction log at 17:05
When I try to restore database to state at
15:10 (point in time) , the dialogue in Enterprise Manager
says that only time after 17:05 is valid.
It seems to me that I've done something wrong at 17:05 while taking
trans. log backup.
But, again, if I have full backups at 13:00 and 17:00 restoring
database to point in time at 15:10 should be possible ?!
Any help is appreciated.
PagusRestore the full backup from 13:00 with NORECOVERY
Restore the tLog backup to 15:10 with Recovery
Should work.
Rick
"Pagus" <pagus@.writeme.com> wrote in message
news:5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||Hi Pagus,
Point in time recovery can be done with tlogs.... had you taken tlogs after
15:00 full backup?
Thanks
GYK
"Pagus" wrote:
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
> Restore the full backup from 13:00 with NORECOVERY
> Restore the tLog backup to 15:10 with Recovery
I have not done this, but you may want to restore tlog backup to 15:10 with
a standby file.
I THINK you can then restore in time past 15:10 again w/o having to do the
full restore.
(hmm, I should try this now that I think about... too tired right now
though. :-)
> Should work.
>
> Rick
>|||To summarize what u currently have :
[1] First Full Database Backup @. 13:00
[2] Second Full Database Backup @. 17:00
[3] TLog @. 17:05
Trying to Point-In-Time restore to 15:10
Considering that the Point-In-Time restore is before the second DB backup,
the Second DB backup is irrelevent.
There are a few things that u shud check
[1] Was the DB in valid state when you issued the Backup @. 13:00
[2] Check the duration (Period) for the TLog backup. It should have an
end time of 17:05 and start time of what ?
Thanks,
Gopi
"Pagus" wrote:
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||> (hmm, I should try this now that I think about... too tired right now
> though. :-)
No need to Greg. I have a demo script just for this. You can restore the same tlog several times,
going forward in time, using STANDBY. I also checked with MS and this is a tested and supported
method. Here are my comments on the topic:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Mbndd.312510$bp1.26137@.twister.nyroc.rr.com...
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
>> Restore the full backup from 13:00 with NORECOVERY
>> Restore the tLog backup to 15:10 with Recovery
> I have not done this, but you may want to restore tlog backup to 15:10 with
> a standby file.
> I THINK you can then restore in time past 15:10 again w/o having to do the
> full restore.
> (hmm, I should try this now that I think about... too tired right now
> though. :-)
>
>> Should work.
>>
>> Rick
>>
>|||Pagus <pagus@.writeme.com> wrote in message news:<5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com>...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
Always used transaction logs to do a point in time restore.
Vincento|||Hi there,
a point in time restore is only possible when using a transaction log
to do the restore. I know it probably doesnt help you but for future
reference here is the way to do it.
first restore the full backup leaving the database in no-recovery mode
then restore the transaction log backup to the point in time that you
want, this time recoverying the database after the restore completes.
Depending upon the size of your database it might be worth looking at
doing a full backup just once per day and several transaction log
backups throughout the day every hour, or couple of hours.

HELP: Point in time restore : HOW ?

I have full backup of database at 13:00
and another full backup at 17:00.
I've made backup of transaction log at 17:05
When I try to restore database to state at
15:10 (point in time) , the dialogue in Enterprise Manager
says that only time after 17:05 is valid.
It seems to me that I've done something wrong at 17:05 while taking
trans. log backup.
But, again, if I have full backups at 13:00 and 17:00 restoring
database to point in time at 15:10 should be possible ?!
Any help is appreciated.
PagusHi Pagus,
Point in time recovery can be done with tlogs.... had you taken tlogs after
15:00 full backup?
Thanks
GYK
"Pagus" wrote:

> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||To summarize what u currently have :
[1] First Full Database Backup @. 13:00
[2] Second Full Database Backup @. 17:00
[3] TLog @. 17:05
Trying to Point-In-Time restore to 15:10
Considering that the Point-In-Time restore is before the second DB backup,
the Second DB backup is irrelevent.
There are a few things that u shud check
[1] Was the DB in valid state when you issued the Backup @. 13:00
[2] Check the duration (Period) for the TLog backup. It should have an
end time of 17:05 and start time of what ?
Thanks,
Gopi
"Pagus" wrote:

> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||Pagus <pagus@.writeme.com> wrote in message news:<5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com>
..
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
Always used transaction logs to do a point in time restore.
Vincento|||Hi there,
a point in time restore is only possible when using a transaction log
to do the restore. I know it probably doesnt help you but for future
reference here is the way to do it.
first restore the full backup leaving the database in no-recovery mode
then restore the transaction log backup to the point in time that you
want, this time recoverying the database after the restore completes.
Depending upon the size of your database it might be worth looking at
doing a full backup just once per day and several transaction log
backups throughout the day every hour, or couple of hours.

HELP: Point in time restore

Pagus,
you options are:
(a) Restore full backup 1: 13:00
(b) Restore full backup 2: 17:00
(c) Restore full backup 2 and log 1 17:05
(d) Restore full backup 2 and do a point-in-time restore
of the log to any time between 17:00 and 17:05
To restore to 15:10, you'd need to have backed up a log
between the 2 database backups. Point-in-time is only
possible as part of the log restore, which has a concept
of transactions performed at specific times, while a
database backup only knows when the backup was created.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
Yup, full backup at 17:00 was mistake !
Many thanks Paul,
Pagus
On Wed, 20 Oct 2004 01:52:13 -0700, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:

>Pagus,
>you options are:
>(a) Restore full backup 1: 13:00
>(b) Restore full backup 2: 17:00
>(c) Restore full backup 2 and log 1 17:05
>(d) Restore full backup 2 and do a point-in-time restore
>of the log to any time between 17:00 and 17:05
>To restore to 15:10, you'd need to have backed up a log
>between the 2 database backups. Point-in-time is only
>possible as part of the log restore, which has a concept
>of transactions performed at specific times, while a
>database backup only knows when the backup was created.
>HTH,
>Paul Ibison (SQL Server MVP)
>
>

HELP: Exporting to excel

Hi,
I have been working on this problem for some time now and I am no closer to
a solution/answer.
The trouble is that I have created two reports which are basically tables
which display database data, with the input of several search parameters, but
when they get to a certain size (it appears to be like this) and I try to
export to excel I get this error when trying to open the file:
************
Microsoft Office Excel File Repair Log
Errors were detected in file 'ViewRequests.xls'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible. Excel
attempted to recover your formulas and values, but some data may have been
lost or corrupted.
************
From this error, it seems like an excel problem, but we have tried different
versions and we do have a possible workaround we got from this site:
http://support.microsoft.com/default.aspx?scid=kb en-us 820712
but it is very time consuming and not many users have two versions on their
machine.
A couple of people have told me that it must be something in the report, so
I am wondering if formulas or something else would cause this, or if anyone
as encountered this before, or if anyone can confirm is this is a report or
excel error. It appers to be happening only for larger files, but I am not
sure.
ThanksThis command reaks havick on my emf files.
=System.Math.Floor((RowNumber(table4_Group1)-1)/10)
It makes all of multiple page reports act differently and look
differently.
Trint|||Specer23,
You made no mention of clearing out your user/system temp folders or creating a
new Excel template or being sure that there are no Addins or similar templates
loaded in your Excel.
Nor did you mention the scope of affected users.
These are all cursory steps to solving any MSO product issue and if not done is
a grand waste of time if not done.
You certainly could have a problem w/your report, however once you tip Excel,
you must do a little clean up.
I also would through trial & error, get to the magic number between a rendered
report and Excel or not.
Then I'd look at the raw data near that point and look for things like coma's in
numeric fields, invalid dates etc...
HTH
JeffP....
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:76DEC6BB-B20C-41BE-9CD3-60A1D638120C@.microsoft.com...
> Hi,
> I have been working on this problem for some time now and I am no closer to
> a solution/answer.
> The trouble is that I have created two reports which are basically tables
> which display database data, with the input of several search parameters, but
> when they get to a certain size (it appears to be like this) and I try to
> export to excel I get this error when trying to open the file:
> ************
> Microsoft Office Excel File Repair Log
> Errors were detected in file 'ViewRequests.xls'
> The following is a list of repairs:
> Damage to the file was so extensive that repairs were not possible. Excel
> attempted to recover your formulas and values, but some data may have been
> lost or corrupted.
> ************
> From this error, it seems like an excel problem, but we have tried different
> versions and we do have a possible workaround we got from this site:
> http://support.microsoft.com/default.aspx?scid=kb en-us 820712
> but it is very time consuming and not many users have two versions on their
> machine.
> A couple of people have told me that it must be something in the report, so
> I am wondering if formulas or something else would cause this, or if anyone
> as encountered this before, or if anyone can confirm is this is a report or
> excel error. It appers to be happening only for larger files, but I am not
> sure.
>
> Thanks

HELP..Installed production system slows over time

We have a SQL Server 2000 transactional business production system that is
installed at 20 different locations and has been running very well in all
but 1 location that was installed over the weekend. The hardware setup
"seems" to be rock solid according to the MS SQL Server performance
guidelines.
It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
Windows Server 2003 standard with SP1. The machine is dedicated to SQL
Server, the only other database being a Veritas backup DB. There are 4 gb of
400 mhz RAM.
The Data files are located on a Raid 5 high speed disk array and the
Transaction log is on a Raid 1 mirrored pair. The server is connected to the
network by a Broadcom NetXtreme Gigab: Network controller configured for
100Mb full-duplex link.
Once again let me state that this same system is installed on much less
capable hardware at other locations with up to 30 users accessing it via a
VB Client application running on the workstations. Those other locations are
not experiencing the problem.
Now, the differences...
This location has 4 time as much data as the other locations and 40 client
workstations. One of the tables has 2.2 million records (the Account Detail
table which tracks all activity to Invoices) and the other 13 tables have
record counts that vary but mostly in the 100,000 to 600,000 range. The
system data was converted from a DOS DBF system (on a less capable server)
that held these very same records and was running slowly but did not time
out. We ran multiple reports after the data migration and everything seems
to have come over as expected. The same data migration process has been run
at all the other installations without problem.
Here is an example of what happens. When the Server has been freshly
rebooted a simple query on the largest table takes about 3 seconds. After
the 40 clients run the system for about 2 hours the system gradually slows
to a crawl. When I try to run the same test query it can take up to 3
minutes.
Here is the strange part. Looking at the Task Manager, the Processors are
barely taxed and there is 2GB of free RAM. I don't know how to monitor the
disk activity. This is where it gets stranger. After all users have logged
off, the system STILL runs dog slow using the same test query, BUT after
about 2 hours it is back up to good performance. I am puzzled. It is as if
the Transaction queue is getting backed up, but resolves itself after time.
Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
reboot seems to clear the problem almost immediately.
Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
Thanks for your help...John,
Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
sp_blockcnt.
Also, take a look at:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
HTH
Jerry
"John Kotuby" <jkotuby@.snet.net> wrote in message
news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb
> of 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to
> the network by a Broadcom NetXtreme Gigab: Network controller configured
> for 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations
> are not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account
> Detail table which tracks all activity to Invoices) and the other 13
> tables have record counts that vary but mostly in the 100,000 to 600,000
> range. The system data was converted from a DOS DBF system (on a less
> capable server) that held these very same records and was running slowly
> but did not time out. We ran multiple reports after the data migration and
> everything seems to have come over as expected. The same data migration
> process has been run at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after
> time. Or is it maybe the Disk arrays having trouble synchronizing? But, a
> Server reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh
> in.
> Thanks for your help...
>
>|||John Kotuby wrote:
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb of
> 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to the
> network by a Broadcom NetXtreme Gigab: Network controller configured for
> 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations are
> not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account Detail
> table which tracks all activity to Invoices) and the other 13 tables have
> record counts that vary but mostly in the 100,000 to 600,000 range. The
> system data was converted from a DOS DBF system (on a less capable server)
> that held these very same records and was running slowly but did not time
> out. We ran multiple reports after the data migration and everything seems
> to have come over as expected. The same data migration process has been run
> at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
> Thanks for your help...|||Your problem is an interesting one. A question I would have relating to
it has to do with the outcome of your migration data validation. You
did not elaborate on the process utilized which would lead me to
suspect it may have been quite an abbreviated validation. Can you be
quite certain that stray charactors did not populate some table space
along with valid data? I am currently doing a small bit of research
around validation methodoligy. Would be quite interested in learning
more about the process you empoloyed for this.|||We ran Invoice balance reports for every Company (Division) in the original
DOS system and again after the data migration. All companies balanced before
and after the migration to the penny. The report output was identical for 5
years of data.
<jeff.livingston@.philips.com> wrote in message
news:1129655929.952358.319620@.f14g2000cwb.googlegroups.com...
> Your problem is an interesting one. A question I would have relating to
> it has to do with the outcome of your migration data validation. You
> did not elaborate on the process utilized which would lead me to
> suspect it may have been quite an abbreviated validation. Can you be
> quite certain that stray charactors did not populate some table space
> along with valid data? I am currently doing a small bit of research
> around validation methodoligy. Would be quite interested in learning
> more about the process you empoloyed for this.
>|||Thanks Jerry,
I have been looking at the results of the SPs and finding some strange
things, like a single workstation having 30 SELECT statements running
simultaneously. We are digging deeper.. it's a start.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23EXw0Z$0FHA.1108@.TK2MSFTNGP14.phx.gbl...
> John,
> Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
> sp_blockcnt.
> Also, take a look at:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
> HTH
> Jerry
> "John Kotuby" <jkotuby@.snet.net> wrote in message
> news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
>> We have a SQL Server 2000 transactional business production system that
>> is installed at 20 different locations and has been running very well in
>> all but 1 location that was installed over the weekend. The hardware
>> setup "seems" to be rock solid according to the MS SQL Server performance
>> guidelines.
>> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
>> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
>> Server, the only other database being a Veritas backup DB. There are 4 gb
>> of 400 mhz RAM.
>> The Data files are located on a Raid 5 high speed disk array and the
>> Transaction log is on a Raid 1 mirrored pair. The server is connected to
>> the network by a Broadcom NetXtreme Gigab: Network controller configured
>> for 100Mb full-duplex link.
>> Once again let me state that this same system is installed on much less
>> capable hardware at other locations with up to 30 users accessing it via
>> a VB Client application running on the workstations. Those other
>> locations are not experiencing the problem.
>> Now, the differences...
>> This location has 4 time as much data as the other locations and 40
>> client workstations. One of the tables has 2.2 million records (the
>> Account Detail table which tracks all activity to Invoices) and the other
>> 13 tables have record counts that vary but mostly in the 100,000 to
>> 600,000 range. The system data was converted from a DOS DBF system (on a
>> less capable server) that held these very same records and was running
>> slowly but did not time out. We ran multiple reports after the data
>> migration and everything seems to have come over as expected. The same
>> data migration process has been run at all the other installations
>> without problem.
>> Here is an example of what happens. When the Server has been freshly
>> rebooted a simple query on the largest table takes about 3 seconds. After
>> the 40 clients run the system for about 2 hours the system gradually
>> slows to a crawl. When I try to run the same test query it can take up to
>> 3 minutes.
>> Here is the strange part. Looking at the Task Manager, the Processors are
>> barely taxed and there is 2GB of free RAM. I don't know how to monitor
>> the disk activity. This is where it gets stranger. After all users have
>> logged off, the system STILL runs dog slow using the same test query, BUT
>> after about 2 hours it is back up to good performance. I am puzzled. It
>> is as if the Transaction queue is getting backed up, but resolves itself
>> after time. Or is it maybe the Disk arrays having trouble synchronizing?
>> But, a Server reboot seems to clear the problem almost immediately.
>> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh
>> in.
>> Thanks for your help...
>>
>|||John Kotuby wrote:
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
> Thanks for your help...
Do you update statistics? Auto-update?
There may be contention for tempdb, especially if you have a lot of sorts (order by and group by) in you SQL statements.
Supposedly this happens on servers with multiple processors and lots of memory. Search MS knowledge base for
concurrency and tempdb to see if it fits your situation.
Ed

HELP..Installed production system slows over time

We have a SQL Server 2000 transactional business production system that is
installed at 20 different locations and has been running very well in all
but 1 location that was installed over the weekend. The hardware setup
"seems" to be rock solid according to the MS SQL Server performance
guidelines.
It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
Windows Server 2003 standard with SP1. The machine is dedicated to SQL
Server, the only other database being a Veritas backup DB. There are 4 gb of
400 mhz RAM.
The Data files are located on a Raid 5 high speed disk array and the
Transaction log is on a Raid 1 mirrored pair. The server is connected to the
network by a Broadcom NetXtreme Gigab: Network controller configured for
100Mb full-duplex link.
Once again let me state that this same system is installed on much less
capable hardware at other locations with up to 30 users accessing it via a
VB Client application running on the workstations. Those other locations are
not experiencing the problem.
Now, the differences...
This location has 4 time as much data as the other locations and 40 client
workstations. One of the tables has 2.2 million records (the Account Detail
table which tracks all activity to Invoices) and the other 13 tables have
record counts that vary but mostly in the 100,000 to 600,000 range. The
system data was converted from a DOS DBF system (on a less capable server)
that held these very same records and was running slowly but did not time
out. We ran multiple reports after the data migration and everything seems
to have come over as expected. The same data migration process has been run
at all the other installations without problem.
Here is an example of what happens. When the Server has been freshly
rebooted a simple query on the largest table takes about 3 seconds. After
the 40 clients run the system for about 2 hours the system gradually slows
to a crawl. When I try to run the same test query it can take up to 3
minutes.
Here is the strange part. Looking at the Task Manager, the Processors are
barely taxed and there is 2GB of free RAM. I don't know how to monitor the
disk activity. This is where it gets stranger. After all users have logged
off, the system STILL runs dog slow using the same test query, BUT after
about 2 hours it is back up to good performance. I am puzzled. It is as if
the Transaction queue is getting backed up, but resolves itself after time.
Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
reboot seems to clear the problem almost immediately.
Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
Thanks for your help...John,
Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
sp_blockcnt.
Also, take a look at:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/defaul...kb;en-us;271509
HTH
Jerry
"John Kotuby" <jkotuby@.snet.net> wrote in message
news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb
> of 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to
> the network by a Broadcom NetXtreme Gigab: Network controller configured
> for 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations
> are not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account
> Detail table which tracks all activity to Invoices) and the other 13
> tables have record counts that vary but mostly in the 100,000 to 600,000
> range. The system data was converted from a DOS DBF system (on a less
> capable server) that held these very same records and was running slowly
> but did not time out. We ran multiple reports after the data migration and
> everything seems to have come over as expected. The same data migration
> process has been run at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after
> time. Or is it maybe the Disk arrays having trouble synchronizing? But, a
> Server reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh
> in.
> Thanks for your help...
>
>|||John Kotuby wrote:
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb
of
> 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to t
he
> network by a Broadcom NetXtreme Gigab: Network controller configured for
> 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations a
re
> not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account Detai
l
> table which tracks all activity to Invoices) and the other 13 tables have
> record counts that vary but mostly in the 100,000 to 600,000 range. The
> system data was converted from a DOS DBF system (on a less capable server)
> that held these very same records and was running slowly but did not time
> out. We ran multiple reports after the data migration and everything seems
> to have come over as expected. The same data migration process has been ru
n
> at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time
.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in
.
> Thanks for your help...|||Your problem is an interesting one. A question I would have relating to
it has to do with the outcome of your migration data validation. You
did not elaborate on the process utilized which would lead me to
suspect it may have been quite an abbreviated validation. Can you be
quite certain that stray charactors did not populate some table space
along with valid data? I am currently doing a small bit of research
around validation methodoligy. Would be quite interested in learning
more about the process you empoloyed for this.|||We ran Invoice balance reports for every Company (Division) in the original
DOS system and again after the data migration. All companies balanced before
and after the migration to the penny. The report output was identical for 5
years of data.
<jeff.livingston@.philips.com> wrote in message
news:1129655929.952358.319620@.f14g2000cwb.googlegroups.com...
> Your problem is an interesting one. A question I would have relating to
> it has to do with the outcome of your migration data validation. You
> did not elaborate on the process utilized which would lead me to
> suspect it may have been quite an abbreviated validation. Can you be
> quite certain that stray charactors did not populate some table space
> along with valid data? I am currently doing a small bit of research
> around validation methodoligy. Would be quite interested in learning
> more about the process you empoloyed for this.
>|||Thanks Jerry,
I have been looking at the results of the SPs and finding some strange
things, like a single workstation having 30 SELECT statements running
simultaneously. We are digging deeper.. it's a start.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23EXw0Z$0FHA.1108@.TK2MSFTNGP14.phx.gbl...
> John,
> Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
> sp_blockcnt.
> Also, take a look at:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/defaul...kb;en-us;271509
> HTH
> Jerry
> "John Kotuby" <jkotuby@.snet.net> wrote in message
> news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||John Kotuby wrote:

> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time
.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in
.
> Thanks for your help...
Do you update statistics? Auto-update?
There may be contention for tempdb, especially if you have a lot of sorts (o
rder by and group by) in you SQL statements.
Supposedly this happens on servers with multiple processors and lots of memo
ry. Search MS knowledge base for
concurrency and tempdb to see if it fits your situation.
Ed