Money Data Type: following

Showing posts with label following. Show all posts

Wednesday, March 28, 2012

Monday, March 26, 2012

Month-Wise Report.

Hi All,
I have a table with the following structure
EMP_ID VARCHAR(5)
START_DATE DATETIME
END_DATE DATETIME
NO_OF_DAYS INT
This table tracks the leave taken by employees.
Say, I have a record like this
emp_id start_date end_date no_of_days
---
10005 Jan 26, 2005 Feb 02, 2005 8 (total
number of days on leave inclusive of start_date and end_date)
I want a report like this
10005 Jan - 6 Days Feb - 2 Days
Is there any way I can split a date range like this using SQL?
Thanks in advance...
Raju JosephTry this and let me know if it does what you require:
-- BEGIN SCRIPT
declare @.tbl table
(emp_id varchar(5)
, start_date datetime
, end_date datetime
, no_of_days int
)
insert into @.tbl
values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
insert into @.tbl
values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
select emp_id
, substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
day(start_date)+1) vacation
, substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
day(end_date))
, no_of_days
from @.tbl
where month(start_date)< month(end_date)
-- END SCRIPT
"Raju Joseph" wrote:

> Hi All,
> I have a table with the following structure
> EMP_ID VARCHAR(5)
> START_DATE DATETIME
> END_DATE DATETIME
> NO_OF_DAYS INT
> This table tracks the leave taken by employees.
> Say, I have a record like this
> emp_id start_date end_date no_of_day
s
> ---
> 10005 Jan 26, 2005 Feb 02, 2005 8 (total
> number of days on leave inclusive of start_date and end_date)
> I want a report like this
> 10005 Jan - 6 Days Feb - 2 Days
> Is there any way I can split a date range like this using SQL?
> Thanks in advance...
> Raju Joseph
>
>|||That's what I need.
Thanks a lot Valdez
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:0F266F38-6CCA-4654-9CB4-1E28C75D653E@.microsoft.com...
> Try this and let me know if it does what you require:
> -- BEGIN SCRIPT
> declare @.tbl table
> (emp_id varchar(5)
> , start_date datetime
> , end_date datetime
> , no_of_days int
> )
> insert into @.tbl
> values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
> insert into @.tbl
> values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
> select emp_id
> , substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
> day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
> day(start_date)+1) vacation
> , substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
> day(end_date))
> , no_of_days
> from @.tbl
> where month(start_date)< month(end_date)
> -- END SCRIPT
> "Raju Joseph" wrote:
>|||Hi Valdez,
Is there any way I can make the sql more generic
ie. if an employee has been on leave for say more than 2 months (Jun 23,
2005 to Aug 5, 2005), I would like all the 3 months to show up. Can I do
this using just a single sql?
Thanks in advance
Raju Joseph
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:0F266F38-6CCA-4654-9CB4-1E28C75D653E@.microsoft.com...
> Try this and let me know if it does what you require:
> -- BEGIN SCRIPT
> declare @.tbl table
> (emp_id varchar(5)
> , start_date datetime
> , end_date datetime
> , no_of_days int
> )
> insert into @.tbl
> values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
> insert into @.tbl
> values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
> select emp_id
> , substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
> day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
> day(start_date)+1) vacation
> , substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
> day(end_date))
> , no_of_days
> from @.tbl
> where month(start_date)< month(end_date)
> -- END SCRIPT
> "Raju Joseph" wrote:
>|||Hi There,
You can have one calendar table which will store all the dates from say
'1-jan-2000' to '31-dec-2009'
then your query would become as easy as this
Select Month(caldate),Year(caldate),count(*) Days From Calendar C ,
(Select * from yourtable where empid=@.empid) EMP Where caldate between
EMP.startdate and
emp.enddate group by Year(caldate),Month(caldate)
With Warm regards
Jatinder Singh|||Sorry to ask again.
Any way to display the result like this
emp_id Jan Feb Mar Apr
.... Dec
----
--
101 2 1
102 3
.
.
.
Thanks in advance...
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:0F266F38-6CCA-4654-9CB4-1E28C75D653E@.microsoft.com...
> Try this and let me know if it does what you require:
> -- BEGIN SCRIPT
> declare @.tbl table
> (emp_id varchar(5)
> , start_date datetime
> , end_date datetime
> , no_of_days int
> )
> insert into @.tbl
> values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
> insert into @.tbl
> values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
> select emp_id
> , substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
> day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
> day(start_date)+1) vacation
> , substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
> day(end_date))
> , no_of_days
> from @.tbl
> where month(start_date)< month(end_date)
> -- END SCRIPT
> "Raju Joseph" wrote:
>|||Hi there,
I am bit worried abut the leave extending from one year to another like
'31-Dec' to '4-Jan' .How would you like to show this '
If it is OK to ignore Year then You can try this .
Select empid,
Count(Case When Month(caldate) = 1 Then 1 else null ENd ) Jan,
Count(Case When Month(caldate) = 2 Then 1 else null ENd ) Feb,... And
SoON
>From Emp,calendar where caldate between startdate and enddate
Group by empid;
With Warm regards
Jatinder Singh|||Thanks a lot Jatinder and Valdez...
Coola Boola...
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1144239010.621694.145890@.u72g2000cwu.googlegroups.com...
> Hi there,
> I am bit worried abut the leave extending from one year to another like
> '31-Dec' to '4-Jan' .How would you like to show this '
> If it is OK to ignore Year then You can try this .
> Select empid,
> Count(Case When Month(caldate) = 1 Then 1 else null ENd ) Jan,
> Count(Case When Month(caldate) = 2 Then 1 else null ENd ) Feb,... And
> SoON
> Group by empid;
> With Warm regards
> Jatinder Singh
>

Friday, March 23, 2012

Monthly Reports - Date Format

I have a report that charts our weekly sales and monthly sales. I use the
following SQL statement to reflect 7 days or 30 days, which works but not
properly. I'd like for my report to run from Sunday to Sunday for the 7-day
report, and from the 1st of the month to the end of the month, regardless of
the number of days in the month. How do I modfify my statement to do this?
Here's my SQL statement for the 30-day or monthly report:
SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
(*) AS TOT
FROM Sysadm.receipt
WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
-30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
101), 101))
GROUP BY receipt_date, receipt_no, stamp
ORDER BY receipt_date
--
EnchantnetI used the following statement to run a report for the previous week, from
Saturday to Friday:
CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
101), 101) AS StartDt,
CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
101), 101) AS EndDt
You could probably use a variation of this for your monthly report. I hope
this helps!
"Enchantnet" wrote:
> I have a report that charts our weekly sales and monthly sales. I use the
> following SQL statement to reflect 7 days or 30 days, which works but not
> properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> report, and from the 1st of the month to the end of the month, regardless of
> the number of days in the month. How do I modfify my statement to do this?
> Here's my SQL statement for the 30-day or monthly report:
> SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> (*) AS TOT
> FROM Sysadm.receipt
> WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> 101), 101))
> GROUP BY receipt_date, receipt_no, stamp
> ORDER BY receipt_date
> --
> Enchantnet|||Hey DAW,
Thanks for the code. However, I'm a newbie at this sort of stuff and I get
the following error when I entered your code: "ADO error: Incorrect syntax
near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
could not be completed." Any idea? Do I need to include or define the
StartDt and EndDt elsewhere?
--
Enchantnet
"daw" wrote:
> I used the following statement to run a report for the previous week, from
> Saturday to Friday:
> CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> 101), 101) AS StartDt,
> CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> 101), 101) AS EndDt
> You could probably use a variation of this for your monthly report. I hope
> this helps!
> "Enchantnet" wrote:
> > I have a report that charts our weekly sales and monthly sales. I use the
> > following SQL statement to reflect 7 days or 30 days, which works but not
> > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > report, and from the 1st of the month to the end of the month, regardless of
> > the number of days in the month. How do I modfify my statement to do this?
> > Here's my SQL statement for the 30-day or monthly report:
> >
> > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > (*) AS TOT
> > FROM Sysadm.receipt
> > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > 101), 101))
> > GROUP BY receipt_date, receipt_no, stamp
> > ORDER BY receipt_date
> > --
> > Enchantnet|||I'm not sure what that particular error means, but your statement should look
something like:
select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
from ....
"Enchantnet" wrote:
> Hey DAW,
> Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> the following error when I entered your code: "ADO error: Incorrect syntax
> near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> could not be completed." Any idea? Do I need to include or define the
> StartDt and EndDt elsewhere?
> --
> Enchantnet
>
> "daw" wrote:
> > I used the following statement to run a report for the previous week, from
> > Saturday to Friday:
> > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > 101), 101) AS StartDt,
> > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > 101), 101) AS EndDt
> >
> > You could probably use a variation of this for your monthly report. I hope
> > this helps!
> >
> > "Enchantnet" wrote:
> >
> > > I have a report that charts our weekly sales and monthly sales. I use the
> > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > report, and from the 1st of the month to the end of the month, regardless of
> > > the number of days in the month. How do I modfify my statement to do this?
> > > Here's my SQL statement for the 30-day or monthly report:
> > >
> > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > (*) AS TOT
> > > FROM Sysadm.receipt
> > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > 101), 101))
> > > GROUP BY receipt_date, receipt_no, stamp
> > > ORDER BY receipt_date
> > > --
> > > Enchantnet|||Sorry, I had that wrong as far as what you need. Try this:
select ....
from...
where date between CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 -
DATEPART(dw, GETDATE()), 101), 101) AND CONVERT(datetime, CONVERT(nvarchar,
GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101)
"daw" wrote:
> I'm not sure what that particular error means, but your statement should look
> something like:
> select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
> GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
> GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
> from ....
> "Enchantnet" wrote:
> > Hey DAW,
> >
> > Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> > the following error when I entered your code: "ADO error: Incorrect syntax
> > near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> > could not be completed." Any idea? Do I need to include or define the
> > StartDt and EndDt elsewhere?
> > --
> > Enchantnet
> >
> >
> > "daw" wrote:
> >
> > > I used the following statement to run a report for the previous week, from
> > > Saturday to Friday:
> > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > > 101), 101) AS StartDt,
> > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > > 101), 101) AS EndDt
> > >
> > > You could probably use a variation of this for your monthly report. I hope
> > > this helps!
> > >
> > > "Enchantnet" wrote:
> > >
> > > > I have a report that charts our weekly sales and monthly sales. I use the
> > > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > > report, and from the 1st of the month to the end of the month, regardless of
> > > > the number of days in the month. How do I modfify my statement to do this?
> > > > Here's my SQL statement for the 30-day or monthly report:
> > > >
> > > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > > (*) AS TOT
> > > > FROM Sysadm.receipt
> > > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > > 101), 101))
> > > > GROUP BY receipt_date, receipt_no, stamp
> > > > ORDER BY receipt_date
> > > > --
> > > > Enchantnet|||Perfect! Thank u, Thank u, Thank u!!!!!
--
Enchantnet
"daw" wrote:
> Sorry, I had that wrong as far as what you need. Try this:
> select ....
> from...
> where date between CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 -
> DATEPART(dw, GETDATE()), 101), 101) AND CONVERT(datetime, CONVERT(nvarchar,
> GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101)
> "daw" wrote:
> > I'm not sure what that particular error means, but your statement should look
> > something like:
> >
> > select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
> > GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
> > GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
> > from ....
> >
> > "Enchantnet" wrote:
> >
> > > Hey DAW,
> > >
> > > Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> > > the following error when I entered your code: "ADO error: Incorrect syntax
> > > near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> > > could not be completed." Any idea? Do I need to include or define the
> > > StartDt and EndDt elsewhere?
> > > --
> > > Enchantnet
> > >
> > >
> > > "daw" wrote:
> > >
> > > > I used the following statement to run a report for the previous week, from
> > > > Saturday to Friday:
> > > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > > > 101), 101) AS StartDt,
> > > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > > > 101), 101) AS EndDt
> > > >
> > > > You could probably use a variation of this for your monthly report. I hope
> > > > this helps!
> > > >
> > > > "Enchantnet" wrote:
> > > >
> > > > > I have a report that charts our weekly sales and monthly sales. I use the
> > > > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > > > report, and from the 1st of the month to the end of the month, regardless of
> > > > > the number of days in the month. How do I modfify my statement to do this?
> > > > > Here's my SQL statement for the 30-day or monthly report:
> > > > >
> > > > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > > > (*) AS TOT
> > > > > FROM Sysadm.receipt
> > > > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > > > 101), 101))
> > > > > GROUP BY receipt_date, receipt_no, stamp
> > > > > ORDER BY receipt_date
> > > > > --
> > > > > Enchantnet

Month Name rather than Month Integer

Hi,
I am using the following query
SELECT FirstName AS FirstName, LastName AS LastName, COUNT(*) AS
NoOfOrders,
MONTH(CreatedDate) AS Order_Month
FROM Customer_Info
I want the Order_Month field to conain month name & not integers... Is there
a way that I can do this in this query itself ? & how?
pmudYes, use DateName() function, as in:
Select DateName(month, getdate())
Just take left( , 3) of that to get abbreviation
Select Left(DateName(month, getdate()),3)
"pmud" wrote:

> Hi,
> I am using the following query
> SELECT FirstName AS FirstName, LastName AS LastName, COUNT(*) AS
> NoOfOrders,
> MONTH(CreatedDate) AS Order_Month
> FROM Customer_Info
> I want the Order_Month field to conain month name & not integers... Is the
re
> a way that I can do this in this query itself ? & how?
> --
> pmud|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Try:
print convert(char(3),getdate(),9)
To get the 1st 3 chars of the month name.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjYmYYechKqOuFEgEQI6cQCaAqePHwzETSIU
H+001wpBY6rP0TsAoIc8
h0Bj/kYzw1k3NP+lO7DvySQ2
=JHMo
--END PGP SIGNATURE--
pmud wrote:
> Hi,
> I am using the following query
> SELECT FirstName AS FirstName, LastName AS LastName, COUNT(*) AS
> NoOfOrders,
> MONTH(CreatedDate) AS Order_Month
> FROM Customer_Info
> I want the Order_Month field to conain month name & not integers... Is the
re
> a way that I can do this in this query itself ? & how?
>|||Thanks CBretana, That helped.. :))
"CBretana" wrote:
> Yes, use DateName() function, as in:
> Select DateName(month, getdate())
> Just take left( , 3) of that to get abbreviation
> Select Left(DateName(month, getdate()),3)
>
> "pmud" wrote:
>

month name from the sql query

Hi all

i want result as month name from my query

i tried with the following query but it give result as month number
like (8)

select (month(getdate())) as expr

i want result as month name (Augest)..

give me the proper query...

from Sachinsachin shah (sachin28880@.gmail.com) writes:

Quote:

Originally Posted by

i want result as month name from my query
>
i tried with the following query but it give result as month number
like (8)
>
select (month(getdate())) as expr
>
i want result as month name (Augest)..
>
give me the proper query...

Look at the datename() function in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Aug 16, 7:48 am, sachin shah <sachin28...@.gmail.comwrote:

Quote:

Originally Posted by

Hi all
>
i want result as month name from my query
>
i tried with the following query but it give result as month number
like (8)
>
select (month(getdate())) as expr
>
i want result as month name (Augest)..
>
give me the proper query...
>
from Sachin

Try SELECT DATENAME(MONTH,GETDATE())sql

Friday, March 9, 2012

Monitoring / Logging Suggestion

Hi Everyone,

I'm looking for some suggestions on how I can go about the following task. I have an application which connects to our SQL2000 servers. Ocasionally some of the db connections take a long time to run. What I'd like to do is enable some sort of logging which would provide me with the following pieces of information... where the request originated from, length of time servicing the request, some sort of info on the nature of the request (search, insert, update, sproc, etc...), time of the request.

I'd then like to cross reference these logs with my application logs to isolate which requests are taking to long to service. Hopefully this will help us as we attempt to eliminate the issue.

Thanks for any suggetions you might have!!!! much appreciated!You can use the Profiler which is a standard tool that comes with SQL Server. Several templates are already defined to create a trace (such as for performance measuring) or you can create your own. See Books Online for more info about the parameters and settings.|||Thanks for the suggestion Johan. Taking a look now!|||I attached an MSAccess database which may or may not be helpful for you. It monitors SQL Server sessions. It's written in MSAccess XP.

Wednesday, March 7, 2012

Monitor i/o, cpu etc. without traces

Any suggestions on how I can monitor the following without using traces? I am a dba/developer working as a developer on a contract, and I'm supposed to be tuning. However, I can't run traces. I've got my own procs that monitor locking, etc. But I would like to get at least i/o and cpu throughout the day. It would also be nice to get the query executed. Basically, the type of stuff you'd normally use traces for.

I know about @.@.cpu, @.@.io etc., but these are basically useless (no?) since they only record since the server was started. There is a stored proc but it only monitors these things since the last time it was run.

Does anyone know how I could utilize the above? I tried to write a script but I couldn't get it to work. :(

I realize that in general this is a ridiculous request, but I thought I would ask anyway.

Hello, Rottengeek.

Are you using SQL Server 2005? If so, you might want to use some of the new DMVs.

sys.dm_exec_query_stats gives you I/O and execution statistics for each plan. You can use the columns in this view to relate the plan handle and statement handle back to the actual SQL text. You'll find that the DMV has lots of neat data; each of the statistics carries a total, min and max value for the plan since it was last compiled.

If you checkout the dm_exec_sql_text() function in books online, it shows how to get the statement text given a sql_handle (one of the columns in dm_exec_query_stats) and has a couple of neat sample queries.

I hope that's enough to get you going in the right direction. If you have follow-up questions, please let me know.

.B ekiM

|||

Below are some of the system functions/virtual tables that you can use in SQL Server 2000:

fn_virtualfilestats - To get I/O counters per database/file

sysprocesses

sysperfinfo - Perfmon counters

But whether you can access these or not depends on your permission levels. You can also take a look at the links below for other pointers:

http://support.microsoft.com/kb/298475/

http://support.microsoft.com/kb/243588/

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

|||

I'm on SQL Server 2000.

This was VERY helpful.

Unfortunately, I can't query sysperfinfo, however, I may be able to get a workaround by submitting a script to the DBA...who knows. At any rate, as a consultant (who is normally a DBA) these will come in VERY HANDY. One of the links instructs you how to create sp_blocker_pss80, which I will modify but will be much better than running a trace.

I'm typically pretty familiar with the system tables, but sysperfinfo I did not know about.

Thanks! If anyone else has some more ideas, I'd love to hear them...

Monday, February 20, 2012

MOM Subscription errors

I have a few MOM reports that give the following error when I try to
set up a new subscription. I have previously been able to subscribe to
the reports, but now we get this error.
"There is an error in XML document (1,50623)"
I have seen couple of other posts on this, but never an answer or
solution. I can open and run the report in Visual Studio, but even if I
republish them to another folder they still fail. I can see the error
in the logs telling me I have an illegal character, but other than
validating the error, the log does not provide useful info.
THis is happening on more than one MOM report, but only on MOM reports.
Anyone know what is happening'?Had to contact MS. Of course there is a bug that requires a HOT FIX
that needs to be run on the MOM server. Turns out somehow there are
non-printable characters being populated in the data the reports are
using for paramaters to the reports.
The RS log files shows that there is an illegal character but does not
tell you where it is. I finally stumbled upon the illegal character
when looking at the drop down list boxes on the reports. They are of
course non-printable characters so you will not see them in the results
of a SQL select statement.
Here is the article that outlines the problem.
http://support.microsoft.com/?id=915785#XSLTH3120121123120121120120
Article ID : 915785