More Charts.......

I know, I know there are several problems with charts already out
there. I have read them all.... 4 times. I have a report that has a
page header made up of simple labels and the body of the report is made
up of a chart. I am performing the following.
result = rs.Render(reportPath, format, historyID, devinfo, parameters,
Nothing, showHide, Nothing, mimeType, Nothing, Nothing, streamIDs)
For Each streamID In streamIDs
result1 = rs.RenderStream(reportPath, format, _
streamID, Nothing, devinfo, parameters, Nothing, Nothing)
***This is written to the location of the report not neccessarily
***the application. I have tried writing to the application home
***also with no success
Dim stream As System.IO.FileStream = _
System.IO.File.OpenWrite
("C:\Inetpub\wwwroot\ReportsServices\eRisc\" & streamID)
stream.Write(result1, 0, CInt(result1.Length))
stream.Close()
Next
Response.Clear()
***Now how do I display this to the page? I have tried a couple
options.
***Option 1 - This displays the header and a bunch of binary ***output
to the bottom
Response.Write(New
System.Text.UTF8Encoding().GetString(result).Replace("{SessionId}",
rs.SessionHeaderValue.SessionId))
Response.ContentType = "image/JPEG"
Response.BinaryWrite(result1)
***Option 2 - This also displays the header and a bunch of binary
***output to the bottom
Response.ContentType = "image/JPEG"
Response.BinaryWrite(result)
Response.BinaryWrite(result1)
***Option 3 - This displays the header and just a red X for the image.
Response.BinaryWrite(result)I should have added option #4
***Option 4 - This also displays the chart only
Response.ContentType = "image/JPEG"
Response.BinaryWrite(result1)|||First of all, have you considered using URL access for your reports? That
would be the easiest solution.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"UW_Hoops" <kdschmidt@.charter.net> wrote in message
news:1103144483.726468.325490@.f14g2000cwb.googlegroups.com...
>I know, I know there are several problems with charts already out
> there. I have read them all.... 4 times. I have a report that has a
> page header made up of simple labels and the body of the report is made
> up of a chart. I am performing the following.
> result = rs.Render(reportPath, format, historyID, devinfo, parameters,
> Nothing, showHide, Nothing, mimeType, Nothing, Nothing, streamIDs)
> For Each streamID In streamIDs
> result1 = rs.RenderStream(reportPath, format, _
> streamID, Nothing, devinfo, parameters, Nothing, Nothing)
> ***This is written to the location of the report not neccessarily
> ***the application. I have tried writing to the application home
> ***also with no success
> Dim stream As System.IO.FileStream = _
> System.IO.File.OpenWrite
> ("C:\Inetpub\wwwroot\ReportsServices\eRisc\" & streamID)
> stream.Write(result1, 0, CInt(result1.Length))
> stream.Close()
> Next
>
> Response.Clear()
> ***Now how do I display this to the page? I have tried a couple
> options.
> ***Option 1 - This displays the header and a bunch of binary ***output
> to the bottom
> Response.Write(New
> System.Text.UTF8Encoding().GetString(result).Replace("{SessionId}",
> rs.SessionHeaderValue.SessionId))
> Response.ContentType = "image/JPEG"
> Response.BinaryWrite(result1)
> ***Option 2 - This also displays the header and a bunch of binary
> ***output to the bottom
> Response.ContentType = "image/JPEG"
> Response.BinaryWrite(result)
> Response.BinaryWrite(result1)
> ***Option 3 - This displays the header and just a red X for the image.
> Response.BinaryWrite(result)
>

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

Monitor Database Growth

At the present moment, is there any software in-place to give us the ability
to perform database growth monitoring, database sizes. charts etc.
thanks
Farrukh"FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
news:3E4CD58C-736D-4F35-A574-1FE52DEBB25D@.microsoft.com...
> At the present moment, is there any software in-place to give us the
> ability
> to perform database growth monitoring, database sizes. charts etc.
> thanks
> Farrukh
The performance monitor in Windows will have SQL Server specific objects and
counters in it. Take a look at these counters.
Rick Sawtell|||Hi
Take a look into
dbname.sys.database_files system table
"FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
news:3E4CD58C-736D-4F35-A574-1FE52DEBB25D@.microsoft.com...
> At the present moment, is there any software in-place to give us the
> ability
> to perform database growth monitoring, database sizes. charts etc.
> thanks
> Farrukh

Monitor Data Access Speeds & Time-Outs

I've been asked to produce charts that show data access & time-out
information for an instance of SQL Server.
I've very little experience with SQL Server.
My background is MI - so only know Analysis Services part of SQL
Server.
It would seem the previous person manually went through error logs
generated & rekeyed appropriate data into Excel to produce charts.
How can I produce the following charts automatically?
1. Data Access - ave. access time per process by day
2. SQL Errors - number of by day
3. Time-outs - number by hour
Or anyway of achieving effectively the same thing without any manual
involvement.
Many thanks.
We probably need more information on what you are trying to collect but here
is a start.
Setup a SQL trace. It will need to run 24hours a day writing to a file(s).
Have it stop and write out every so often 5 mins, 4 hours (depends on system
load and requirements). Start the next trace. Read the trace into a table.
Delete old trace files.
1. Data Access - Assuming you mean how long did each query take to be
serviced. Add RPC:Completed and SQL:Batch Completeed to trace events.
Select the avg(duration) of these events over the time period you need.
2. SQL Errors - Add the specific SQL errors you are looking for in the
trace. Select the cout of them over the time period.
3. Timeouts. Not sure where you are getting timeout information now.
There's lock timeout, remote query time, or connection timeout in SQL
Agent...Need more info.
<duvinrouge@.servihoo.com> wrote in message
news:1129629233.565048.215040@.g44g2000cwa.googlegr oups.com...
> I've been asked to produce charts that show data access & time-out
> information for an instance of SQL Server.
> I've very little experience with SQL Server.
> My background is MI - so only know Analysis Services part of SQL
> Server.
> It would seem the previous person manually went through error logs
> generated & rekeyed appropriate data into Excel to produce charts.
> How can I produce the following charts automatically?
> 1. Data Access - ave. access time per process by day
> 2. SQL Errors - number of by day
> 3. Time-outs - number by hour
> Or anyway of achieving effectively the same thing without any manual
> involvement.
> Many thanks.
>

Monitor Data Access Speeds & Time-Outs

I've been asked to produce charts that show data access & time-out
information for an instance of SQL Server.
I've very little experience with SQL Server.
My background is MI - so only know Analysis Services part of SQL
Server.
It would seem the previous person manually went through error logs
generated & rekeyed appropriate data into Excel to produce charts.
How can I produce the following charts automatically?
1. Data Access - ave. access time per process by day
2. SQL Errors - number of by day
3. Time-outs - number by hour
Or anyway of achieving effectively the same thing without any manual
involvement.
Many thanks.We probably need more information on what you are trying to collect but here
is a start.
Setup a SQL trace. It will need to run 24hours a day writing to a file(s).
Have it stop and write out every so often 5 mins, 4 hours (depends on system
load and requirements). Start the next trace. Read the trace into a table.
Delete old trace files.
1. Data Access - Assuming you mean how long did each query take to be
serviced. Add RPC:Completed and SQL:Batch Completeed to trace events.
Select the avg(duration) of these events over the time period you need.
2. SQL Errors - Add the specific SQL errors you are looking for in the
trace. Select the cout of them over the time period.
3. Timeouts. Not sure where you are getting timeout information now.
There's lock timeout, remote query time, or connection timeout in SQL
Agent...Need more info.
<duvinrouge@.servihoo.com> wrote in message
news:1129629233.565048.215040@.g44g2000cwa.googlegroups.com...
> I've been asked to produce charts that show data access & time-out
> information for an instance of SQL Server.
> I've very little experience with SQL Server.
> My background is MI - so only know Analysis Services part of SQL
> Server.
> It would seem the previous person manually went through error logs
> generated & rekeyed appropriate data into Excel to produce charts.
> How can I produce the following charts automatically?
> 1. Data Access - ave. access time per process by day
> 2. SQL Errors - number of by day
> 3. Time-outs - number by hour
> Or anyway of achieving effectively the same thing without any manual
> involvement.
> Many thanks.
>

Monitor Data Access Speeds & Time-Outs

I've been asked to produce charts that show data access & time-out
information for an instance of SQL Server.
I've very little experience with SQL Server.
My background is MI - so only know Analysis Services part of SQL
Server.
It would seem the previous person manually went through error logs
generated & rekeyed appropriate data into Excel to produce charts.
How can I produce the following charts automatically?
1. Data Access - ave. access time per process by day
2. SQL Errors - number of by day
3. Time-outs - number by hour
Or anyway of achieving effectively the same thing without any manual
involvement.
Many thanks.We probably need more information on what you are trying to collect but here
is a start.
Setup a SQL trace. It will need to run 24hours a day writing to a file(s).
Have it stop and write out every so often 5 mins, 4 hours (depends on system
load and requirements). Start the next trace. Read the trace into a table.
Delete old trace files.
1. Data Access - Assuming you mean how long did each query take to be
serviced. Add RPC:Completed and SQL:Batch Completeed to trace events.
Select the avg(duration) of these events over the time period you need.
2. SQL Errors - Add the specific SQL errors you are looking for in the
trace. Select the cout of them over the time period.
3. Timeouts. Not sure where you are getting timeout information now.
There's lock timeout, remote query time, or connection timeout in SQL
Agent...Need more info.
<duvinrouge@.servihoo.com> wrote in message
news:1129629233.565048.215040@.g44g2000cwa.googlegroups.com...
> I've been asked to produce charts that show data access & time-out
> information for an instance of SQL Server.
> I've very little experience with SQL Server.
> My background is MI - so only know Analysis Services part of SQL
> Server.
> It would seem the previous person manually went through error logs
> generated & rekeyed appropriate data into Excel to produce charts.
> How can I produce the following charts automatically?
> 1. Data Access - ave. access time per process by day
> 2. SQL Errors - number of by day
> 3. Time-outs - number by hour
> Or anyway of achieving effectively the same thing without any manual
> involvement.
> Many thanks.
>