Thursday, March 29, 2012
Backing Up SQL Server
Tuesday, March 20, 2012
Backing Up
is restored I think it is a good idea to back up the full text catalog. What
is the best method for doing do ?
GMG,
Assuming you're using either SQL Server 7.0 or SQL Server 2000, you should
review KB article: 240867 (Q240867) "INF: How to Move, Copy, and Backup
Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867
If you're using SQL Server 2005 (codename Yukon), the FT Catalog can be
backed up and restored with the database!
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"GMG" <nospam@.nospam.com> wrote in message
news:uDysw71tFHA.908@.tk2msftngp13.phx.gbl...
> As it can take a long time to create the full text index once the database
> is restored I think it is a good idea to back up the full text catalog.
> What
> is the best method for doing do ?
>
sql
Back, restore and Verification
verifiying my backup.Each time i backup, and want to test
my backup or view the contents,i do not see file in the
backup folder.Pls helpI do not know why this post is in the .Warehouse NG but
If you are backing up a database then you use the BACKUP DATABASE
command. For this you will specify a filename or devicename.
You can then after you know this use RESTORE VERIFYONLY on that file or
device.
How are you backing up your databases?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||In article <2932901c465b7$60ec5ca0$a601280a@.phx.gbl>, Ayo wrote:
> Hi guys, i am new to Sql and seems to be having problems
> verifiying my backup.Each time i backup, and want to test
> my backup or view the contents,i do not see file in the
> backup folder.Pls help
>
The ONLY true way to test your backup is to RESTORE it.
Thanks Steve
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Back, restore and Verification
verifiying my backup.Each time i backup, and want to test
my backup or view the contents,i do not see file in the
backup folder.Pls help
I do not know why this post is in the .Warehouse NG but
If you are backing up a database then you use the BACKUP DATABASE
command. For this you will specify a filename or devicename.
You can then after you know this use RESTORE VERIFYONLY on that file or
device.
How are you backing up your databases?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
|||In article <2932901c465b7$60ec5ca0$a601280a@.phx.gbl>, Ayo wrote:
> Hi guys, i am new to Sql and seems to be having problems
> verifiying my backup.Each time i backup, and want to test
> my backup or view the contents,i do not see file in the
> backup folder.Pls help
>
The ONLY true way to test your backup is to RESTORE it.
Thanks Steve
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Monday, March 19, 2012
Back up
I am able to get back up VIA Database>All Tasks>Backup Database but I cannot
schedule it for particular time.
May be I am missing something but I tried all options.
Is there any other thing to do.
RegardsHi,
There are 2 options
1. Use Maintenance plan to backup the database. It has the scheduling option
2. Write ur own script and schedule it using SQL agent -- Jobs inside
Enterprise manager
1. Maintenance Plan
1. Open Enterprise maneger, Expand the Management options and select
"database Maintenance Plans"
2. Right click and select new maintenance plan
3. Click Next and select the database u need to backup and click next till
the "Database Backup Plan " screen comes
4. There you can schedule the time for backup and click next
5. In the backup disk directory , you can specify the backup folder and
click next and specify the number of backup copies to keep
6. COntinue the steps (it is prestty easy to understand)
Maintenance plans use SQL agent to schedule so ensure that SQL Agent service
run all the time in the SQL server Machine.
2. SQL Agent schedule
1. In enterprise manager -- select SQL agent - Jobs
2. Right click jobs and create new job
3. Give a name and go to Steps tab and give the TSQL command to backup
(Backup database <dbname> to disk='c:\backup\dbname.bak' with init)
4. Go to schedule tab and schedule atime for backup
Ensure that SQL Agent service run all the time in the SQL server Machine.
Thanks
Hari
MCDBA
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:#O27QolEEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi all
>
> I am able to get back up VIA Database>All Tasks>Backup Database but I
cannot
> schedule it for particular time.
> May be I am missing something but I tried all options.
> Is there any other thing to do.
>
> Regards
>|||Thanks Hari
> Ensure that SQL Agent service run all the time in the SQL server Machine
This is the main thing I was missing after running it everything is OK
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23XobD4lEEHA.3344@.tk2msftngp13.phx.gbl...
> Hi,
> There are 2 options
> 1. Use Maintenance plan to backup the database. It has the scheduling
option
> 2. Write ur own script and schedule it using SQL agent -- Jobs inside
> Enterprise manager
> 1. Maintenance Plan
> 1. Open Enterprise maneger, Expand the Management options and select
> "database Maintenance Plans"
> 2. Right click and select new maintenance plan
> 3. Click Next and select the database u need to backup and click next till
> the "Database Backup Plan " screen comes
> 4. There you can schedule the time for backup and click next
> 5. In the backup disk directory , you can specify the backup folder and
> click next and specify the number of backup copies to keep
> 6. COntinue the steps (it is prestty easy to understand)
> Maintenance plans use SQL agent to schedule so ensure that SQL Agent
service
> run all the time in the SQL server Machine.
> 2. SQL Agent schedule
> 1. In enterprise manager -- select SQL agent - Jobs
> 2. Right click jobs and create new job
> 3. Give a name and go to Steps tab and give the TSQL command to backup
> (Backup database <dbname> to disk='c:\backup\dbname.bak' with init)
> 4. Go to schedule tab and schedule atime for backup
> Ensure that SQL Agent service run all the time in the SQL server Machine.
> Thanks
> Hari
> MCDBA
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:#O27QolEEHA.624@.TK2MSFTNGP10.phx.gbl...
> cannot
>
Wednesday, March 7, 2012
awe enabled but still has dynamic memory set
The dedicated s2k machine has 8GB of memory. AWE has been enabled, and max server memory set to 5120 (this is a clustered instance and the second machine only has 6 Gb. More is on order). When viewing properties for the instance it shows memory as being dynamic with max server memory = 5120.
Should the memory be set to something other than dynamically managed?
I confirmed the settings via sp_configure and AWE is enabled and memory is 5120... And perf mon shows target memory as around 5120 so it looks like things are set correctly. But the dynamically configure sql memory is still selected.
So is that an issue?
Thanks for any input.
MikeMike,
Set the memory to fixed when you use AWE. This way SQL won't have extend the memory whenever it needs it. It will save you some overhead.|||I was plan on making the change but was looking for some doco from Bill 1st.
Thanks for the response!!
Mike
Saturday, February 25, 2012
AVs, Fatal Exceptions 17066, 17052, etc., etc.
Sql Server 2000 SP2 - 8.00.534
Win2k
About 1400 databases.
After a while, sometimes 24 hours sometimes 2 hours sql server terminates with errors like the following (in order of occurrence).
17066 :
SQL Server Assertion: File: <S:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
18052 :
Error: 3624, Severity: 20, State: 1.
17066 :
SQL Server Assertion: File: <S:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I've searched this forum and looked here - http://support.microsoft.com/common/canned.aspx?R=d&H=Microsoft%20SQL%20Server%202000%20Post-SP2%20Hotfixes&LL=&Sz=kbSQLServ2000presp3fix&Fr=&DU=&SD=GN&LN=EN-US&CND=1&VR=&CAT=&VRL=&SG=&MaxResults=200
but cannot find a fix.
I can provide the latest *.dmp file if you need it.
Anyone have any ideas?
--
PadraicRE: Originally posted by Podge
Q1 About 1400 databases.
Q2 <S:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
Q3 Anyone have any ideas?
--
Padraic
A1 That is quite a few databases (1400 are they all quite large)?
A2 Are you running internally created XP_ stored procedures at the time, (what is the purpose of the record.inl?), if so what do they do?
A3 Not really, it is interesting though. Some MS info does exist re. some bugfixes related to Error: 3624: http://support.microsoft.com/default.aspx?scid=kb;EN-US;274266|||Thanks for the reply. I really appreciate any help I'm getting.
1. Most databases are 1mb - 40mb, 10 are above 40mb and 2 are above 100mb.
2. The databases contain only default sp's. All db's are interacting only with asp pages (which don't call sp's). Don't know what "XP_ stored procedures" are.
3. From that page "This problem was first corrected in SQL Server 2000 Service Pack 1." I have Sql Server 2000 SP2 (which should include the fix ?)|||RE: Thanks for the reply. I really appreciate any help I'm getting.
1. Most databases are 1mb - 40mb, 10 are above 40mb and 2 are above 100mb.
2. The databases contain only default sp's. All db's are interacting only with asp pages (which don't call sp's). Don't know what "XP_ stored procedures" are.
3. From that page "This problem was first corrected in SQL Server 2000 Service Pack 1." I have Sql Server 2000 SP2 (which should include the fix ?)
1. Are they all very active? Is your server ever maxed out with respect to any resources (CPU, IO, etc.); if so is it busy at the time of the incidents?
2. XP_ = extended stored procedures.
If you have not developed any internally, and no outside contractor has developed any for you, and you have not now or ever had a non MS record.inl file in use, i.e.(S:\sql\ntdbms\storeng\drs\include\record.inl) ; Then this is most likely a MS bug.
==> In that case you should call MS support (ask for a refund as this is a MS Sql Server bug). <==
3. They may have fixed some issues (but not all, say your particular issue, for example?), or the MS fix may have created other issues?|||1. Some are very active, some are not at all. Since the errors started occurring I restricted use to about 80 db's instead of all 1400. Server is under no stress cpu wise but memory use keeps going up even though SQL server is set (not dynamic controlling memory).
2. I will as soon as I can find a number.
3. I believe there are a few more fixes available, I'm going to install them now. Cumulative right?
I have at the moment 8.0.0.584 (sp2.01)
Is this the latest?
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316426|||1. ...no stress cpu wise but memory use keeps going up even though SQL server is set (not dynamic controlling memory).
2. I will as soon as I can find a number.
3. I believe there are a few more fixes available, I'm going to install them now. Cumulative right? I have at the moment 8.0.0.584 (sp2.01) Is this the latest? http://support.microsoft.com/default.aspx?scid=kb;EN-US;316426
1. Memory use keeps going up? (You don't mean as in a memory leak?)
2. {One last thought before calling MS PSS; you haven't experimented with enabling any unusual trace flags?} If not, support is available at $245 per incident:
Support for Phone Numbers
Small or Home Business (800) 936-4900
Developer (800) 936-5800
IT Professional (800) 936-4900
Partner (resellers or consultants) (888) 456-5570
Original Equipment Manufacturer (800) 936-2197
System Builder (888) 456-5570
Certified Partner (888) 677-9444
TDD/TTY (800) 892-5234
3. My most up to date installs are at:
8.00.679 <==> http://support.microsoft.com/default.aspx?scid=kb;en-us;316333 (Hotfixes often take more work to install, and undo)|||I have seen this occur when an index gets hosed. Are you using any clustered indexes ? If so, drop and recreate them. If that does not work, drop and recreate other indexes. I know you probably have quite a few but this is a necessary step.
Yes, please post your sql dump.|||RE: I have seen this occur when an index gets hosed. Are you using any clustered indexes ? If so, drop and recreate them. If that does not work, drop and recreate other indexes. I know you probably have quite a few but this is a necessary step.
Yes, please post your sql dump.
S1 You might want to run dbcc checkdb to confirm / assess any corruption (and get some idea of how extensive it might be), also PSS might want to have you do some other things as well.
RE: [I have seen this occur when an index gets hosed. ]
Q1 Is "index gets hosed" a cause, or an effect (or is / was that not clear)?
Q2 Do you happen to know the significance of record.inl, it looks to me like the Failed Assertion is comming straight out of MS source code (since it wasn't written by / for Podge)?|||DBA - Good idea about running dbcc checkdb. This might reveal even more information about whether or not sp2 somehow "modified" sp1. One of the fixes for sp1 relates directly to dbcc checktable (which checkdb uses). One word of caution - Only run dbcc checkdb when the usage of resources is at their lowest - also do not run backups while you do this and understand that this uses tempdb and will grow (there are more caveats - check out bol to see all of them and some handy options).|||Lots of clustered indexes in every db. I'll try to drop and recreate them.
This will take a while.
This is the latest error which resulted in downtime. No other severe errors occurred.
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I installed the hotfixes (the ones you mentioned were the ones I meant to post). Sql is now 8.00.679. It still crashed :(
I have run DBCC CHECKDB for the three largest db's. Anyone know how I can run it for all db's via a script?
Thanks for your continued help.|||Latest dmp file - 148kb
http://64.66.186.41/dmp.html|||Ran checkdb on all databases using this code
declare @.Sql varchar(1000)
, @.DbName varchar(100)
declare DbName cursor fast_forward for
select name from master.dbo.sysdatabases order by name
open DbName
fetch DbName into @.DbName
while @.@.fetch_status = 0
begin
set @.Sql = 'dbcc checkdb(''' + @.DbName + ''') with no_infomsgs'
print @.Sql
--exec (@.Sql)
fetch DbName into @.DbName
end
deallocate DbName
No errors.|||Interesting.
Q1 Is that the first time you have seen the AV you mentioned:
Error: 0, Severity: 19, State: 0 SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Q2 Have you gotten any feedback from PSS on your service request? Has MS indicated that this is possibly a known bug, etc.?
Q3 I think it is important to try to determine if the indexes that are becomming corrupt is a cause, or an effect; as well as the significance of the record.inl file. Again, I suspect the Failed Assertion came straight out of MS source code.
Q4 Assuming periodic data / index checking / maintenance, etc., have you previously detected / repaired occasional index, linkage / or any other corruption issues? If so, is there any noticible pattern to it?|||Getting the 17052 error described in this thread, followed by SQL agent failing to complete jobs. The run through all steps, but fail to terminate and notify operators. Status shows 'Performing Completion Actions' pretty much for ever. Stop the agent but it usually won't start again and have to reboot. MSDB did show corruption on one occasion, and I was hopeful that fixing that would solve the problem. Unfortunately not. I'm looking for ideas too. SQL 2000 8.00.608 .
Friday, February 24, 2012
avoid repeating "if...end" in a stored procedure
Is there any command that could do that?
thanks
[code]
CREATE PROCEDURE sa_default
@.etriduser int= null,
@.locator int = null,
@.choix int = null,
@.login varchar(50) = null
AS
if @.choix=0
begin
........
end
if @.choix=1
begin
.........
end
if @.choix=2
begin
.........
end
if @.choix=3
begin
......
end
RETURN
GO
[/code]What is it you are trying to accomplish in your condition?|||I am selection a list of project which change depending on the level of right of the user
|||You can use a CASE statement in some situations, but it seems like you want to execute code within your BEGIN... ENDs. It won't work in those cases. I don't believe there is anything like Javascript's switch you can use to execute code like that. Depending on what you're trying to do, dynamic SQL might be an answer. Really though, more information is needed on what will happen based on each IF to really give a good answer.|||A CASE statement may work when you just need to change the WHERE clause, but it looks like you change the query as well. It looks like IF times 4 is the way to go.|||I second trying the CASE, but if you can't make it work then I'd make turn those inner batches into their own procs you'll prb' get better performance cause you won't suffer from the selective query plan issues of using IFs.|||Thanks for your comments.
CREATE PROCEDURE sa_default@.etriduser int= null,
@.locator int = null,
@.choix int = null,
@.login varchar(50) = nullAS
if @.choix=0
begin
SELECT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service, Tproject.location, Tproject.ponumber, Tproject.state, Tcompany.company, Tproject.datestart,
ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0 )) /
(SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1
FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany
WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) and Tproject.locator=isnull(@.locator,locator) ORDER BY idproject
end
if @.choix=1
begin
SELECT DISTINCT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service,Tproject.location, Tproject.ponumber, Tproject.state,
Tcompany.company, Tproject.datestart, ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0)) /
(SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1
FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject
INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany
INNER JOIN Tuserproject ON Tproject.idproject = Tuserproject.etridproject
WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) and (Tuserproject.etridperson=@.etriduser or Tproject.locator=@.locator) ORDER BY idproject
end
if @.choix=2
begin
SELECT DISTINCT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service,Tproject.location, Tproject.ponumber, Tproject.state,
Tcompany.company, Tproject.datestart, ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0)) /
(SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1,Tuserproject.mc, Tuserproject.type, Tuserproject.wechart
FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject
INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany INNER JOIN Tuserproject ON Tproject.idproject = Tuserproject.etridproject
WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) and Tuserproject.etridperson=@.etriduser ORDER BY idproject
endif @.choix=3
begin
SELECT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service, Tproject.location, Tproject.ponumber, Tproject.state, Tcompany.company, Tproject.datestart,
ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0)) /
(SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1, Tuserproject.mc, Tuserproject.type, Tuserproject.wechart
FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject
INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany INNER JOIN Tuserproject ON Tproject.idproject = Tuserproject.etridproject
WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) AND
(Tuserproject.etridperson IN (SELECT idperson FROM Tperson WHERE login LIKE @.login))
ORDER BY Tproject.idproject
endRETURN
GO
Can you tell me more about your idea about "turn those inner batches into their own procs "
I never hear about that, do you have any sample or link for documentation?
Thanks|||There's no great mystery to it. Problem 1: One of the publicised problems with stored procedure plans is they can get "confused" by IF statements. I believe the problem is because it originally stores only one plan that represents only one path through the code. So the other IF conditions may not be ready to run. I've not tested it this myself but sounds plausable.
Problem 2: The more complicated the plan, the more chance the query optimiser has of making the wrong choices. I've certainly seen this. The more conditions in the proc seems to increase the likelyhood of the query plan having performance issues. Not suprising really because there is a slider between compiling optimal code and compiling the code quickly. So the longer it takes to compile the code the more likely the optimiser will throw its hands in the air.
Both of these problems can be helped by converting some of the query into other stored procedures. These have their own plans so the optimiser can, typically, do a better job.|||I agree with that post 100% and it's true that 4 significantly different queries will have a very good chance of not being run optimally. Create 4 procs and within your application code, decide which one to call based on what that parameter is at run time. The 4 procs will run more efficiently.
Sunday, February 19, 2012
Avg, Min & Max of two date fields in a Table
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 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 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 time difference
---StartDate ------------ StopDate --
______________________________________________
01/05/2005 7:30 AM -------- 03/05/2005 10:00 AM
13/05/2005 2:30 PM -------- 01/08/2005 8:00 PM
_____________________________________________
How do I find out the avg time difference between StopDate and StartDate? I triedAVG(StopDate - StartDate ),but it's giving me the following error message.
"The average aggregate operation cannot take a smalldatetime data type as an argument."
Thanks for any reply.
polash26 wrote:
I triedAVG(StopDate - StartDate ),but it's giving me error message. Thanks for any reply.
The key to solving any programming problem is to read the error messageand understand what it is telling you. When you seek help fromother programmers, the very first thing you are going to be asked is"what exactly does the error message say?"
In your case, I am assuming you are receiving this error message:
The average aggregate operation cannot take a datetime data type as an argument.Try using the DATEDIFF function, which will return you an integer datatype. You CAN use the AVG function on an integer data type.
This, for example, will give you the average number of hours between the myDateTime column and the current system date:
SELECT AVG(DATEDIFF(hh,myDateTime, GETDATE())) FROM test
avg of timestamp
I have a timestamp column with format 00:00:00, and I want to get the
average time of all rows for that column.
When I try it, I get this error:
The average aggregate operation cannot take a timestamp data type as an
argument.
Thanks!
A timestamp is a point in time. So averaging is impossible.
This means that you would have to define the averaging formula yourself.
Let's assume all your datetime columns have the date 1900-01-01. In that
case you might use the formula:
SELECT
DATEADD(second,AVG(DATEDIFF(second,'19000101',MyDa teColumn)),'19000101')
FROM MyTable
HTH,
Gert-Jan
chrismtoth@.gmail.com wrote:
> Is it possible to use AVG with timestamp?
> I have a timestamp column with format 00:00:00, and I want to get the
> average time of all rows for that column.
> When I try it, I get this error:
> The average aggregate operation cannot take a timestamp data type as an
> argument.
> Thanks!
avg of timestamp
I have a timestamp column with format 00:00:00, and I want to get the
average time of all rows for that column.
When I try it, I get this error:
The average aggregate operation cannot take a timestamp data type as an
argument.
Thanks!A timestamp is a point in time. So averaging is impossible.
This means that you would have to define the averaging formula yourself.
Let's assume all your datetime columns have the date 1900-01-01. In that
case you might use the formula:
SELECT
DATEADD(second,AVG(DATEDIFF(second,'19000101',MyDateColumn)),'19000101')
FROM MyTable
HTH,
Gert-Jan
chrismtoth@.gmail.com wrote:
> Is it possible to use AVG with timestamp?
> I have a timestamp column with format 00:00:00, and I want to get the
> average time of all rows for that column.
> When I try it, I get this error:
> The average aggregate operation cannot take a timestamp data type as an
> argument.
> Thanks!
avg of timestamp
I have a timestamp column with format 00:00:00, and I want to get the
average time of all rows for that column.
When I try it, I get this error:
The average aggregate operation cannot take a timestamp data type as an
argument.
Thanks!A timestamp is a point in time. So averaging is impossible.
This means that you would have to define the averaging formula yourself.
Let's assume all your datetime columns have the date 1900-01-01. In that
case you might use the formula:
SELECT
DATEADD(second,AVG(DATEDIFF(second,'1900
0101',MyDateColumn)),'19000101')
FROM MyTable
HTH,
Gert-Jan
chrismtoth@.gmail.com wrote:
> Is it possible to use AVG with timestamp?
> I have a timestamp column with format 00:00:00, and I want to get the
> average time of all rows for that column.
> When I try it, I get this error:
> The average aggregate operation cannot take a timestamp data type as an
> argument.
> Thanks!
Avg function
=Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
Fields!TODO_ACTL_END_DT.Value ))/60
My problem is in figuring out how to truncate decimal places for example the
report returns 36.2795358649783 when all I want is 36.
If any one has any ideas that would be greatly appreciated.
--
kmatth007use format(Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> Fields!TODO_ACTL_END_DT.Value ))/60,"0")
"kmatth007" wrote:
> The following function returns an average in minutes between to dates.
> =Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> Fields!TODO_ACTL_END_DT.Value ))/60
> My problem is in figuring out how to truncate decimal places for example the
> report returns 36.2795358649783 when all I want is 36.
> If any one has any ideas that would be greatly appreciated.
> --
> kmatth007|||Thanks! This worked.
kmatth007
"ש×?×?×?" wrote:
> use format(Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> > Fields!TODO_ACTL_END_DT.Value ))/60,"0")
> "kmatth007" wrote:
> > The following function returns an average in minutes between to dates.
> >
> > =Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> > Fields!TODO_ACTL_END_DT.Value ))/60
> >
> > My problem is in figuring out how to truncate decimal places for example the
> > report returns 36.2795358649783 when all I want is 36.
> >
> > If any one has any ideas that would be greatly appreciated.
> >
> > --
> > kmatth007
Avg Date Diff?
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?
> >
> >
Thursday, February 16, 2012
averageofChildren vs avg()/count()
hi,
which is better to use: averageofchildren or avg()/count() calculated measure, if i don't want to calculate average over time.
i have read in the "Analysis Services Performance Guide" that is better to avoid using semi-additive functions to improve performance.
Special aggregate functions require unique performance-tuning techniques. They
include DistinctCount and a collection of semiadditive aggregate functions. The
semiadditive functions include: FirstChild, LastChild, FirstNonEmpty,
LastNonEmpty, ByAccount, and AverageOfChildren.
thanks
Christina
Probably the normal average (sum/count) is the better choice if you won't consider time. AverageOfChildren requires a time dimension and Enterprise Edition.|||i have both time dimension and enterprise edition.
but my question is, i have lots of measures in which i will be using the average. i would like to know if i use averageofchildren is convenient or better use the other way, in terms of performance.
thanks
|||Now I am not answering your question again, but.. since the average of sum/count will be a calculated member, it will take zero processing time for the calculation, but the sum and count will take processing time (both probably much faster than AverageOfChildren). However, it will take time to calculate the value when a user executes the query. AvaregeOfChildren is used for measures and therefore will take some time to process, but then zero extra time for queries (if aggregated).
How they compare (process sum+count + calculate average vs just process) I don't know, but you can probably test this on your specific system. In general using measures is better than calculated members (faster for end users, but as said, that depends on how long it takes to process the measure vs calculate the member).
Cheers
Thanks HappyCow.
i think i got the point. I'll go for averageofChildren and test accordingly. if i find it slow, will try the other way.
Hope you remain always happy..but not a cow.
Cheers
Average Turnaround Time - Datediff() Help
The database that I am working with lists a trip each time a unit is dispatched to a destination, and then another trip is created for the units return. In the example below I am trying to calculate the number of days that it takes for a unit to return to Vancouver by calculating the difference between the departure date from Vancouver and the arrival date back into Vancouver. I then need to calculate the average number of days that it takes for a unit to return from a trip. See sample data below.
UNIT -- TRIP -- START LOCATION --START DATE--FIN LOCATION--FIN DATE
================================================== =======
U12 --001 -- VANCOUVER ---FEB 10 -- ONTARIO -- FEB 15
U10 --002 -- VANCOUVER ---FEB 13 -- ONTARIO -- FEB 18
U12 --003 -- ONTARIO ----MARCH 13 -- VANCOUVER -- MARCH 18
U10 --004 -- ONTARIO ----MARCH 1 -- VANCOUVER --MARCH 6
Unit U12 took 36 days to return back to Vancouver
Unit U10 took 21 days to return back to Vancouver
Therefore based on the two trips it takes an average of aproximately 28.5 days for a unit to return from trips destined to Ontario.try this, not a greate SQL though :)
select Route, avg(DaysTaken) as AvgDaysTaken
from (
select A.Unit,(select top 1 Start_Location from MyTable where MyTable.Unit=A.Unit order by Trip)+ '-'+(select top 1 Fin_Location from MyTable where MyTable.Unit=A.Unit order by Trip)+'-'+(select top 1 Start_Location from MyTable where MyTable.Unit=A.Unit order by Trip) as Route, cast(A.DaysTaken as decimal(9,2)) as DaysTaken
from (
select Unit,datediff(d,min(Start_date),max(fin_date)) DaysTaken from MyTable group by unit
) A
) B
group by Route
Average Times
following expression to calculate the time
=IIF(RowNumber("DailyInspectCount") > 1,-1 *
(DateDiff(DateInterval.Minute,Fields!InspectedByDate.Value,Previous(Fields!InspectedByDate.Value))),0)
& " min."
This gives me the number of minutes between each event just fine. Now I
want to get an overall average for the whole row. When I use the AVG
function I get the error "Aggregate functions cannot be nested inside other
aggregate functions". Does anyone know a way to get the values from this
expression to generate the average? Thanks.
JohnCorrection,
I want to get the average for the whole column not the row.
"John Wright" <riley_wright@.hotmail.com> wrote in message
news:uVA4BwvpIHA.4716@.TK2MSFTNGP06.phx.gbl...
>I have a report that displays the average time between events. I use the
>following expression to calculate the time
> =IIF(RowNumber("DailyInspectCount") > 1,-1 *
> (DateDiff(DateInterval.Minute,Fields!InspectedByDate.Value,Previous(Fields!InspectedByDate.Value))),0)
> & " min."
>
> This gives me the number of minutes between each event just fine. Now I
> want to get an overall average for the whole row. When I use the AVG
> function I get the error "Aggregate functions cannot be nested inside
> other aggregate functions". Does anyone know a way to get the values from
> this expression to generate the average? Thanks.
>
> John
>