Backing up a restoring or standby database

Hi,
I would like to backup a secondary log shippping database (in restoring or
standby mode). I need to use the backup as an up to date test database on the
same server but don't have the bandwidth to copy the primary backups over.
I've tried the backup command which results in an error saying that the
restore sequence must be completed first.
I also tried detaching the database with a view to copying the files but
when attempting to attach it again sql server produced another restore
sequence error.
Anyone know if it is possible to do a backup of a log shipping secondary, by
foul means or fair?
Cheers,
Kev.
You'd have to use recovery to be able to do the backup but this'd invalidate
the log-shipping chain. for a test database you might roll your own 'database
shipping' solution which is something a few people on this newsgroup seem to
be using...
HTH,
Paul Ibison
|||Okay, thanks.
Kev.
"Paul Ibison" wrote:

> You'd have to use recovery to be able to do the backup but this'd invalidate
> the log-shipping chain. for a test database you might roll your own 'database
> shipping' solution which is something a few people on this newsgroup seem to
> be using...
> HTH,
> Paul Ibison
>

Avoiding NULLS how ?

Two examples where we use NULL fields, how to avoid them ?
For a datetime field, date's which are still unknown, for
example an appointment in the future or the ending of
a event still going on, were the date still
has to be set or the date when somebody died.
(And what to do with a birthdate, which is not completely
know, for example jan 1958 or born in 1958, sorry side track).
A integer field, for example a count field were there is no
actual count at the moment. (count can be positive and negative).
Offcourse the data is used by different applications and systems,
also by MIS/MSS/DSS/Olap/Datamining.
Ben BrugmanHi Ben,
What's the reason for avoiding NULL's, seem like a logical values to me in
these cases?
HTH
Karl Gram
http://www.gramonline.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:#88hzTODEHA.3016@.TK2MSFTNGP11.phx.gbl...
> Two examples where we use NULL fields, how to avoid them ?
> For a datetime field, date's which are still unknown, for
> example an appointment in the future or the ending of
> a event still going on, were the date still
> has to be set or the date when somebody died.
> (And what to do with a birthdate, which is not completely
> know, for example jan 1958 or born in 1958, sorry side track).
> A integer field, for example a count field were there is no
> actual count at the moment. (count can be positive and negative).
> Offcourse the data is used by different applications and systems,
> also by MIS/MSS/DSS/Olap/Datamining.
> Ben Brugman
>|||On Thu, 18 Mar 2004 13:31:32 +0100, ben brugman wrote:

>Two examples where we use NULL fields, how to avoid them ?
>For a datetime field, date's which are still unknown, for
>example an appointment in the future or the ending of
>a event still going on, were the date still
>has to be set or the date when somebody died.
>(And what to do with a birthdate, which is not completely
>know, for example jan 1958 or born in 1958, sorry side track).
>A integer field, for example a count field were there is no
>actual count at the moment. (count can be positive and negative).
>Offcourse the data is used by different applications and systems,
>also by MIS/MSS/DSS/Olap/Datamining.
>Ben Brugman
I know many people advise against using NULLs. I don't agree with
them. If a programmer doesn't know how to code proper SQL statements
with NULLable columns, don't forbid NULLs but fire the programmer and
hire a more capable replacement.
In the examples you provided (date unknown / no count present), NULL
is an excellent (the best, IMnotsoHO) solution.
Icomplete dates are another matter. If you foresee incomplete dates,
you'll have to store the parts of the date individually. So the
combination day/month/year would be NULL/NULL/1958 for someone born in
1958, or NULL/1/1958 for someone born in jan 1958. However, this will
require lots of extra work if you also have to do date calculations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

i need to get the average, min & max time between the referral and assessment dates where the assessment date falls within a given period (dteFromDate & dteToDate) and where assessment date is not null

but im a little bit stuck on how to do it. i know about the MIN & MAX functions but they only work one field at a time. do i need to do a cursor to get all the date differences then work off these for MIN, MAX & AVG?

any help would be greatly appreciated.

Cheers,
Craig

You take min, max, avg of a expression. An expression can be a column or anything else that qualifies as an expression.
select min(A_DATE - D_REF) from T_EPISODE
where A_DATE between dteFromDate and dteToDate|||

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

To get the average for a period in time I used this query
select avg(nScore) as 'Daily Average', dtDate
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'
group by dtDate
order by dtDate

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

i not joining both function for avg() and max(). but i just use avg() to select the data i need. then i use the sort and filter in the object(table, chart, text box and so on). to select the top N record or bottom N record then i can get the maximun and minimun record already.

regards

terence chua

Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

i need to get the average, min & max time between the referral and assessment dates where the assessment date falls within a given period (dteFromDate & dteToDate) and where assessment date is not null

but im a little bit stuck on how to do it. i know about the MIN & MAX functions but they only work one field at a time. do i need to do a cursor to get all the date differences then work off these for MIN, MAX & AVG?

any help would be greatly appreciated.

Cheers,
Craig

You take min, max, avg of a expression. An expression can be a column or anything else that qualifies as an expression.
select min(A_DATE - D_REF) from T_EPISODE
where A_DATE between dteFromDate and dteToDate

|||

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

To get the average for a period in time I used this query
select avg(nScore) as 'Daily Average', dtDate
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'
group by dtDate
order by dtDate

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

i not joining both function for avg() and max(). but i just use avg() to select the data i need. then i use the sort and filter in the object(table, chart, text box and so on). to select the top N record or bottom N record then i can get the maximun and minimun record already.

regards

terence chua

Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

i need to get the average, min & max time between the referral and assessment dates where the assessment date falls within a given period (dteFromDate & dteToDate) and where assessment date is not null

but im a little bit stuck on how to do it. i know about the MIN & MAX functions but they only work one field at a time. do i need to do a cursor to get all the date differences then work off these for MIN, MAX & AVG?

any help would be greatly appreciated.

Cheers,
Craig

You take min, max, avg of a expression. An expression can be a column or anything else that qualifies as an expression.
select min(A_DATE - D_REF) from T_EPISODE
where A_DATE between dteFromDate and dteToDate|||

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

To get the average for a period in time I used this query
select avg(nScore) as 'Daily Average', dtDate
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'
group by dtDate
order by dtDate

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

i not joining both function for avg() and max(). but i just use avg() to select the data i need. then i use the sort and filter in the object(table, chart, text box and so on). to select the top N record or bottom N record then i can get the maximun and minimun record already.

regards

terence chua

Avg Date Diff?

Has anyone had any luck getting an Avg on Date Diff? Here's what I'm
trying to do:
Figure out the average time it takes from ringtime (datetime) to
starttime(datetime).
Any ideas?Does this work for you?
SELECT SUM(DATEDIFF(SECOND, RingTime, StartTime))/COUNT(*) FROM myTable
"naomimsm@.gmail.com" wrote:
> Has anyone had any luck getting an Avg on Date Diff? Here's what I'm
> trying to do:
> Figure out the average time it takes from ringtime (datetime) to
> starttime(datetime).
> Any ideas?
>|||Sorry, I should have been clearer: I'm trying to define a formula in
Report Builder and I can't get it to give me an average--very
frustrating! Thanks!
Matt wrote:
> Does this work for you?
> SELECT SUM(DATEDIFF(SECOND, RingTime, StartTime))/COUNT(*) FROM myTable
> "naomimsm@.gmail.com" wrote:
> > Has anyone had any luck getting an Avg on Date Diff? Here's what I'm
> > trying to do:
> >
> > Figure out the average time it takes from ringtime (datetime) to
> > starttime(datetime).
> >
> > Any ideas?
> >
> >

average date of birth

Hi, folks.
I have a table with a column Date_of_birth.
I want to evaluate average date_of_birth by grouping on depts.
AVG function doesn't seem to work with datetime col. Plz help.Hi, folks.
I have a table with a column Date_of_birth.
I want to evaluate average date_of_birth by grouping on depts.
AVG function doesn't seem to work with datetime col. Plz help.

r u looking for average of age ??
coz avg of date of birth doesn't make any sense to me.|||yes, i want to evaluate average age on the basis of date of brith stored with in the column|||datediff ?|||AVG(datediff(yy,date_of_birth,getdate()))
this would work, i guess.
thanx|||How's about:

USE Northwind
GO

CREATE TABLE myTable99(Col1 datetime)
GO

INSERT INTO myTable99(Col1)
SELECT '10/24/1960' UNION ALL SELECT '10/24/1970' UNION ALL SELECT '10/24/1980'

SELECT CONVERT(datetime,AVG(CONVERT(float,Col1))) FROM myTable99
GO

DROP TABLE myTable99
GO|||Hi Brett, good morning.
That was what i've been lookin for.
How do we get the date back after it's converted into float.
SELECT convert(datetime,38174.0)
--------
2004-07-08 00:00:00.000
(1 row(s) affected)

Howdy!|||Here is a quick one:

************************************************** ****
SET NOCOUNT ON
/* Here is your table */
create table #working (Date_of_birth datetime)
insert into #working (Date_of_birth) VALUES ('1/1/1920')
insert into #working (Date_of_birth) VALUES ('1/6/1930')
insert into #working (Date_of_birth) VALUES ('1/17/1940')
DECLARE @.days_old int

/* Average days old */
SELECT @.days_old = AVG(datediff(dd, Date_of_birth, GETDATE()))
FROM #working

/* back it out now for average birth date, odd as that is :P */
SELECT dateadd(dd, -@.days_old, GETDATE())

drop table #working
SET NOCOUNT OFF
************************************************** ****

Hope it helps|||Aren't you concerned that cobalt sound a lot like COBOL?

Autoslicing not working

I have around 36 partitions in the 2005 cube. Each partition is roughly 20M fact rows.

The partitions are differentiated by a date member dimension.

When I run a query which selects by a distinct date, SSAS tries to scan all partitions (for inclusions?). This introduces a huge performance impact. It takes many seconds to run a single query.

I have aggregations designed specifically for relevant dimensions. The Profiler shows that SSAS reads from aggregations. It does not read from facts.

The storage mode is MOLAP.

Do I need to use the partition slice property ? I am seeing on forums from time to time that this attribute is not used in MOLAP.

I just need to make sure that my queries are fast.

I don't see where it could hurt to set this property, though BOL indicates this is only used by SSAS for ROLAP partitions. I thought I had heard that with MOLOP and/or HOLAP, that SSAS maintained an internal set of information regarding the contents of a slice that would handle this function for you. Maybe someone else out there could chime in on this one.

Regarding the query you specify above, are you specifying a date member from the specific cube dimension used to partition your cube? You probably are but it wasn't explicitly stated. Date dimensions are often used in a role-playing capacity which introduces some confusion.

Thanks,
Bryan

|||

can anyone clarify if the Slice property on Partition is used in MOLAP or not?

I checked info....xml files for my slicing dimension min/max values and they seem correct, however with explicit queries, SSAS still reads a bunch of partitions...

autoregression Trees

hi,

I am using Time series alogorrithm.I just wants to know about the autoregression tree.I am having data like

Studid Date Perf

001 01/01/2007 90

001 02/01/2007 95

001 03/01/2007 89

002 01/01/2007 79

002 02/01/2007 90

002 03/01/2007 95

Like that. when I use my Model Viewer --> Descision Tree --> It shows like

Perf = 90.0084 + 1.02 * Perf(-2) + 0.25 * Perf(-2).

What is this value and how its getting calculated?

Basically, the formula tells that the forcasted Perf value is determined by using a regression formula that uses, as regressors, the value Perf had two time slices ago.

If your time key were a date, then Today's Perf depends linearly on Perf two days ago.

More details on the autoregressive tree can be found in the paper on "Autoregressive tree models for Time Series Analysis" by C. Meek, D.M Chickering and David Heckerman, available here: http://research.microsoft.com/~dmax/publications/dmart-final.pdf

'Autorefreshing' parameters gives errors

hi all,
I have this strange problem with my parameters in a report.
There are 4 parameters (2 date and 2 comboboxes) which need te be filled
in in order to get the report.
I've managed to get this to work on my test-machine,but once I deploy the code on another server,I get errors filling in the parameters:
When filling in the first date, I get an error:
The value provided for the report parameter 'PER2' is not valid for its type. (rsReportParameterTypeMismatch)
(where PER2 is my second date)
It seems the page reload itself each time I fill in a parameter, and getting this error because it can't be left empty...
So when I push back in my browser, the first date is still filled in, filling in the second date lets me select the 2 other values from the dropdown and then generating my report.
Anyone any ideas what could cause this?
Since the exact same report runs without a problem on my machine, I don't really think it's a code problem,
Thx,
Hans
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=ec520230db804271906faa5728e90e6d
*****************************************"Hans Cryspin via SQLMonster.com" wrote:
> There are 4 parameters (2 date and 2 comboboxes) which need te be filled
> in in order to get the report.
> I've managed to get this to work on my test-machine,but once I deploy the
> code on another server,I get errors filling in the parameters:
> Since the exact same report runs without a problem on my machine, I don't
> really think it's a code problem,
Could it be a problem with the regional settings on your test machine and
the other server? Check that the regional settings are the same and that if
you're testing with different Internet Explorer installation that they both
have set the same default language. (Tools->Internet Options->Languages)