hot backup: reports

Showing posts with label reports. Show all posts

Wednesday, March 28, 2012

How can I change the reports wizard templates?

I can easily create reports using the wizard, but I have to spend a lot of
time changing them.
Is there a way to change the templates the wizard uses?No. But you can create your own basic report as a template and change the
items needed by hand instead of using the wizard.
--
| From: "geri" <dsfds>
| Subject: How can I change the reports wizard templates?
| Date: Thu, 6 Jan 2005 12:21:10 +0200
| Lines: 5
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <OT9Urm98EHA.3828@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: dbk-exc.dubek.co.il 62.219.254.181
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38816
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I can easily create reports using the wizard, but I have to spend a lot of
| time changing them.
| Is there a way to change the templates the wizard uses?
|
|
||||This is a multi-part message in MIME format.
--=_NextPart_000_002E_01C50EA5.CCFB9680
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Then can you explain what the RS BOL topic "Creating a Report Using =Report Wizard" means by "Report Designer provides four style templates: =Bold, Casual, Corporate, and Compact. You can alter existing templates =or add new ones by editing the StyleTemplates.xml file in the =\80\Tools\Report Designer\Business Intelligence Wizards\Reports\Styles =folder in the Microsoft SQL Server program folder. This folder is =located on the computer on which Report Designer is installed. ", Please =? (Bold, italics, mine...)
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message =news:7BlC$jF9EHA.764@.cpmsftngxa10.phx.gbl...
> No. But you can create your own basic report as a template and change =the > items needed by hand instead of using the wizard.
> --
> | From: "geri" <dsfds>
> | Subject: How can I change the reports wizard templates?
> | Date: Thu, 6 Jan 2005 12:21:10 +0200
> | Lines: 5
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> | Message-ID: <OT9Urm98EHA.3828@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: dbk-exc.dubek.co.il 62.219.254.181
> | Path: > =cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNG=P09
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl =microsoft.public.sqlserver.reportingsvcs:38816
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > | I can easily create reports using the wizard, but I have to spend a =lot of
> | time changing them.
> | Is there a way to change the templates the wizard uses?
> | > | > | >
--=_NextPart_000_002E_01C50EA5.CCFB9680
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Then can you explain what the RS BOL =topic "Creating a Report Using Report Wizard" means by "Report Designer =provides four style templates: Bold, Casual, Corporate, and Compact. You =can alter existing templates or add new ones by editing the StyleTemplates.xml =file in the \80\Tools\Report Designer\Business Intelligence Wizards\Reports\Styles =folder in the Microsoft SQL Server program folder. This folder is =located on the computer on which Report Designer is installed. ", Please ? =(Bold, italics, mine...)
""Brad Syputa - MS"" wrote in message news:7BlC$jF9EHA.764@.cpmsftngxa10.phx.gbl...> =No. But you can create your own basic report as a template and change the > items =needed by hand instead of using the wizard.> =--> | From: "geri" > | Subject: How can I change the =reports wizard templates?> | Date: Thu, 6 Jan 2005 12:21:10 +0200> =| Lines: 5> | X-Priority: 3> | X-MSMail-Priority: =Normal> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437> | =X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441> | Message-ID: > | Newsgroups: microsoft.public.sqlserver.reportingsvcs> | NNTP-Posting-Host: dbk-exc.dubek.co.il 62.219.254.181> | Path: > cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNG=P09> phx.gbl> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38816> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs> | > | I can =easily create reports using the wizard, but I have to spend a lot of> | =time changing them.> | Is there a way to change the templates the =wizard uses?> | > | > | >

--=_NextPart_000_002E_01C50EA5.CCFB9680--|||This is a multi-part message in MIME format.
--=_NextPart_000_0090_01C50EB7.5E3039B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You CAN change the templates, but the BOL topic is deficient...
1.. CREATE A BACKUP of the StyleTemplates.xml file in the folder cited =in BOL (see post below).
2.. Open the file in a text editor (your favorite or XML editor...)
3.. Locate the "StyleTemplate" you want to change, e.g. "Compact"
4.. Make sure you understand what each element does. Refer to the BOL =Index for detailed info, e.g., "BorderWidth element" will give you blurb =on that element as used in rdl's AND the Template.
5.. Color values have NO spaces between words, e.g. Steel Blue is =coded SteelBlue.
6.. Font names CAN include spaces, e.g. <FontFamily>Arial =Black</FontFamily>
7.. DO NOT include a <TextAlign> pair in the "Table Header" Style - VS =barfs! (It seems to want to apply the alignment of the cell to the =corresponding table header cell - DUH!) A new post to be seen by MS =will hopefully get this recognized as a bug (if it hasn't already =been...).
8.. Despite setting a default font within the "Table" style, if you =expect it to be used when you add a Group, dream on...
9.. Save the edited XML file.
10.. NOW COPY THE SUCKER TO YOUR "LANGUAGE" FOLDER UNDER THE ..\Styles =FOLDER... e.g., for English, copy it to ..\Styles\EN\
11.. REBOOT (emotive term!) Visual Studio - it appears to cache the =Style file's XML the first time you use the "New Report Wizard".
Experiment with the Style File and you may be able to control things =like the page size, grid size, etc., but I've yet to pluck up that =degree of courage...
Hope this helps...
"SS_Newbie" <SS_Newbie@.community.nospam> wrote in message =news:uxuddjuDFHA.328@.tk2msftngp13.phx.gbl...
Then can you explain what the RS BOL topic "Creating a Report Using =Report Wizard" means by "Report Designer provides four style templates: =Bold, Casual, Corporate, and Compact. You can alter existing templates =or add new ones by editing the StyleTemplates.xml file in the =\80\Tools\Report Designer\Business Intelligence Wizards\Reports\Styles =folder in the Microsoft SQL Server program folder. This folder is =located on the computer on which Report Designer is installed. ", Please =? (Bold, italics, mine...)
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message =news:7BlC$jF9EHA.764@.cpmsftngxa10.phx.gbl...
> No. But you can create your own basic report as a template and =change the > items needed by hand instead of using the wizard.
> --
> | From: "geri" <dsfds>
> | Subject: How can I change the reports wizard templates?
> | Date: Thu, 6 Jan 2005 12:21:10 +0200
> | Lines: 5
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> | Message-ID: <OT9Urm98EHA.3828@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: dbk-exc.dubek.co.il 62.219.254.181
> | Path: > =cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNG=P09
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl =microsoft.public.sqlserver.reportingsvcs:38816
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > | I can easily create reports using the wizard, but I have to spend =a lot of
> | time changing them.
> | Is there a way to change the templates the wizard uses?
> | > | > | >
--=_NextPart_000_0090_01C50EB7.5E3039B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

You CAN change the templates, but the =BOL topic is deficient...
CREATE A BACKUP of the =StyleTemplates.xml file in the folder cited in BOL (see post below).
Open the file in a text editor (your =favorite or XML editor...)
Locate the "StyleTemplate" you =want to change, e.g. "Compact"
Make sure you understand what each =element does. Refer to the BOL Index for detailed info, e.g., "BorderWidth element" =will give you blurb on that element as used in rdl's AND the =Template.
Color values have NO spaces between =words, e.g. Steel Blue is coded SteelBlue.
Font names CAN include spaces, e.g. Arial Black
DO NOT include a =pair in the "Table Header" Style - VS barfs! (It seems to want to apply the =alignment of the cell to the corresponding table header cell - DUH!) A =new post to be seen by MS will hopefully get this recognized as a bug (if it =hasn't already been...).
Despite setting a default font within =the "Table" style, if you expect it to be used when you add a Group, dream on...
Save the edited XML file.
NOW COPY THE SUCKER TO YOUR "LANGUAGE" =FOLDER UNDER THE ..\Styles FOLDER... e.g., for English, copy it to ..\Styles\EN\
REBOOT (emotive term!) Visual Studio - =it appears to cache the Style file's XML the first time you use the "New =Report Wizard".
Experiment with the Style File and you =may be able to control things like the page size, grid size, etc., but I've yet to =pluck up that degree of courage...
Hope this helps...
"SS_Newbie" wrote in message news:uxuddjuDFHA.328@.t=k2msftngp13.phx.gbl...
Then can you explain what the RS BOL =topic "Creating a Report Using Report Wizard" means by "Report Designer =provides four style templates: Bold, Casual, Corporate, and Compact. =You can alter existing templates or add new ones by editing the =StyleTemplates.xml file in the \80\Tools\Report Designer\Business Intelligence Wizards\Reports\Styles folder in the Microsoft SQL Server program folder. This folder is located on the computer on which =Report Designer is installed. ", Please ? (Bold, italics, mine...)

""Brad Syputa - MS"" wrote in message news:7BlC$jF9EHA.764@.cpmsftngxa10.phx.gbl...> No. But you =can create your own basic report as a template and change the > items =needed by hand instead of using the wizard.> --> =| From: "geri" > | Subject: How can I change the reports =wizard templates?> | Date: Thu, 6 Jan 2005 12:21:10 +0200> | =Lines: 5> | X-Priority: 3> | X-MSMail-Priority: Normal> =| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437> | =X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441> | Message-ID: > | Newsgroups: microsoft.public.sqlserver.reportingsvcs> | NNTP-Posting-Host: dbk-exc.dubek.co.il 62.219.254.181> | Path: > =cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNG=P09> phx.gbl> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38816> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs> | > | I can =easily create reports using the wizard, but I have to spend a lot of> =| time changing them.> | Is there a way to change the templates the =wizard uses?> | > | > | >

--=_NextPart_000_0090_01C50EB7.5E3039B0--

Wednesday, March 21, 2012

How can I alter the parameters pane design?

Hi all,
I was wondering if it's possible to somehow change the parameters pane
design. My problem is that some of the reports we write are in right-
to-left languages (hebrew, arabic). We can change the whole report so
it's right-to-left, but the parameters stick to the left.
Is there any way I can change this?
Thanks in advance,
R. GreenI don't believe there is a good way to do it.
you can write your own parameter controls and just interface RS with
SOAP calls.
people who need to use asp 1.1(no report viewer control) have had to
do this for instance.|||Hi Tom,
Thanks for your prompt answer.
I didn't think there's a way to do it too, until I saw Reporting
Services 2000 and the parameters *were* right-to-left when viewing a
hebrew report.
Perhaps I can somehow use CSS to change the parameters pane design?
On Jul 3, 10:16 pm, tomk...@.gmail.com wrote:
> I don't believe there is a good way to do it.
> you can write your own parameter controls and just interface RS with
> SOAP calls.
> people who need to use asp 1.1(no report viewer control) have had to
> do this for instance.sql

Friday, March 9, 2012

How accurate is GetDate() ?

According to MS, GetLocalTime() (in C++) is only accurate to approx a
second,
even though it reports milliseconds, and calling it twice and computing
the
interval can on occasion lead to a negative interval.

Is T-SQL's GetDate() more accurate than that, or at least
non-decreasing?

Thanks,
JimOn 26 Sep 2006 11:28:07 -0700, jim_geissman@.countrywide.com wrote:

Quote:

Originally Posted by

>According to MS, GetLocalTime() (in C++) is only accurate to approx a
>second,
>even though it reports milliseconds, and calling it twice and computing
>the
>interval can on occasion lead to a negative interval.
>
>Is T-SQL's GetDate() more accurate than that, or at least
>non-decreasing?

Hi Jim,

Getdate() (or, to use the ANSI standard name, CURRENT_TIMESTAMP) is
accurate to 1/300th of a second. I've never seen it decrease. Apart from
that, it is as accurate as the system clock of the server - if an admin
sets the system clock three hours back, SQL Server will report from the
past.

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:

Quote:

Originally Posted by

>
Getdate() (or, to use the ANSI standard name, CURRENT_TIMESTAMP) is
accurate to 1/300th of a second. I've never seen it decrease. Apart from
that, it is as accurate as the system clock of the server - if an admin
sets the system clock three hours back, SQL Server will report from the
past.
>

Just to split hairs over this a little: The maximum precision of the
value returned by GETDATE() is 1/300th of a second. That doesn't
necessarily mean it is *accurate* to within 1/300th of a second
compared to some other time measurement such as the system clock.
However, like Hugo, I have never heard of any issue where serial
executions of GETDATE() went negative. It's not difficult to verify
that the time returned by GETDATE() is within a fraction of a second of
the system clock value returned by other means.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||>Is T-SQL's GetDate() more accurate than that, or at least non-decreasing? <<

Yes; read the other answers. The real answer is that the FIPS-127
tests require five or more decimal places of seconds in a timestamp for
Federal use. Maybe when we get to 64 bit hardware and a better
internal representation ..|||Thanks, fellows.

By way of experiment, I wrote a loop that wrote 100,001 GetDate()s to a
table,
with an identity column to record the sequence. That took ~2 sec on
this
server. Then I examined the 100,000 intervals. Their distribution:

Interval (sec) N
0 99855
..13 44
..16 100
..30 1

I conclude that on this particular SQL2000 server, the clock usually
ticks every
160 ms (but not always).

I did this a few times and never saw a negative interval, although
that's not proof
it could never happen.

Thanks again
Jim

Wednesday, March 7, 2012

How about a reporting product that...Prints reports?

I waited to see if sp2 would address this issue; it does not.
Don't get me wrong, I like RS in general, but, where is the ability to print?
This seems, well, fundamental in a reporting tool. Can you name any other
serious reporting or BI product that doesn't let you output to paper? I
can't and I've been around the block a time or two.
Yes, I know sp2 adds client-side printing and that's nice, but it misses the
point.
How is one supposed to have the boss's report waiting for him in the printer
in the morning using RS? There are still some people that want reports on
paper.
You should see the looks on people's faces when, in the course of telling
them why we should consider replacing our current reporting tool with RS,
that RS does not have the ability to automatically print a report. It's a
pretty awkward feeling.
Come on, Microsoft. You can do better than this.In the samples folder that installs with the product, you'll find a printer
delivery extension. (complete with source code if you care to see how it is
done)
When you install that extension, you'll be able to use the subscription
mechanism to automatically send a report to the printer so it is sitting
there waiting for your boss in the morning as requested.
Regards,
--
Rob Labbé, MCP, MCAD, MCSD, MCT
Lead Architect/Trainer
Fidelis
Blog: http://spaces.msn.com/members/roblabbe
"dj" <dj@.discussions.microsoft.com> wrote in message
news:62871B7A-70B5-48A3-9BF0-FE4372235F27@.microsoft.com...
>I waited to see if sp2 would address this issue; it does not.
> Don't get me wrong, I like RS in general, but, where is the ability to
> print?
> This seems, well, fundamental in a reporting tool. Can you name any other
> serious reporting or BI product that doesn't let you output to paper? I
> can't and I've been around the block a time or two.
> Yes, I know sp2 adds client-side printing and that's nice, but it misses
> the
> point.
> How is one supposed to have the boss's report waiting for him in the
> printer
> in the morning using RS? There are still some people that want reports on
> paper.
> You should see the looks on people's faces when, in the course of telling
> them why we should consider replacing our current reporting tool with RS,
> that RS does not have the ability to automatically print a report. It's a
> pretty awkward feeling.
> Come on, Microsoft. You can do better than this.
>|||While we would love to add every feature in the product, we simply don't
have resources to do so. So we have to pick and choose. It turns out every
person that you ask considers a different feature "absolutely critical".
Some of these "must haves" that we have added for SQL 2005 include
multi-valued parameters, a date picker, and an end-user report designer.
Other "must haves" that we didn't have time to do include rich text, style
templates, and Word output (to name a few).
Are you saying that you would rather have server-side printing instead of
client printing? If so, you have different priorities than the set of
customers we talked to (which is understandable) before we decided to add
client print. Anyway, if enough people really think this is important, it
can be easily written using the extensibility mechanism in RS. Or, you can
wait until we get a chance to build it in.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"dj" <dj@.discussions.microsoft.com> wrote in message
news:62871B7A-70B5-48A3-9BF0-FE4372235F27@.microsoft.com...
>I waited to see if sp2 would address this issue; it does not.
> Don't get me wrong, I like RS in general, but, where is the ability to
> print?
> This seems, well, fundamental in a reporting tool. Can you name any other
> serious reporting or BI product that doesn't let you output to paper? I
> can't and I've been around the block a time or two.
> Yes, I know sp2 adds client-side printing and that's nice, but it misses
> the
> point.
> How is one supposed to have the boss's report waiting for him in the
> printer
> in the morning using RS? There are still some people that want reports on
> paper.
> You should see the looks on people's faces when, in the course of telling
> them why we should consider replacing our current reporting tool with RS,
> that RS does not have the ability to automatically print a report. It's a
> pretty awkward feeling.
> Come on, Microsoft. You can do better than this.
>|||Brian -
I probably could have expressed my frustration somewhat better.
You and your team deserve a lot of credit for delivering a solid, useful
reporting tool.
And you are right on target that not everyone has the same needs or
priorities.
As you can tell, server-side printing is a major priority for us. Trying to
move to
RS from Access and CR-based reporting is a challenge we are currently
working through. Converting and losing a capability we previously had makes
things more of a challenge.
Not to labor the point, but I can't help but think that when most managers,
executives, etc. think of the term 'report', they think of a piece of paper.
Perhaps I'm wrong, but judging from the response I've seen personally, this
would appear to be the case.
Certainly, we have many reporting scenarios, as most shops do, and printing
does
not figure in all, or even most of them. It's just frustrating to not be
able to
deliver on all your client's requests. Hmmm, does that sound familiar?
So, let me try this again:
Brian, if you're not already doing so, please consider adding server-side
printing
to the next release of Reporting Services.
Best regards,
Dexter Jones, MCSD
"Brian Welcker [MSFT]" wrote:
> While we would love to add every feature in the product, we simply don't
> have resources to do so. So we have to pick and choose. It turns out every
> person that you ask considers a different feature "absolutely critical".
> Some of these "must haves" that we have added for SQL 2005 include
> multi-valued parameters, a date picker, and an end-user report designer.
> Other "must haves" that we didn't have time to do include rich text, style
> templates, and Word output (to name a few).
> Are you saying that you would rather have server-side printing instead of
> client printing? If so, you have different priorities than the set of
> customers we talked to (which is understandable) before we decided to add
> client print. Anyway, if enough people really think this is important, it
> can be easily written using the extensibility mechanism in RS. Or, you can
> wait until we get a chance to build it in.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "dj" <dj@.discussions.microsoft.com> wrote in message
> news:62871B7A-70B5-48A3-9BF0-FE4372235F27@.microsoft.com...
> >I waited to see if sp2 would address this issue; it does not.
> >
> > Don't get me wrong, I like RS in general, but, where is the ability to
> > print?
> > This seems, well, fundamental in a reporting tool. Can you name any other
> > serious reporting or BI product that doesn't let you output to paper? I
> > can't and I've been around the block a time or two.
> >
> > Yes, I know sp2 adds client-side printing and that's nice, but it misses
> > the
> > point.
> > How is one supposed to have the boss's report waiting for him in the
> > printer
> > in the morning using RS? There are still some people that want reports on
> > paper.
> >
> > You should see the looks on people's faces when, in the course of telling
> > them why we should consider replacing our current reporting tool with RS,
> > that RS does not have the ability to automatically print a report. It's a
> > pretty awkward feeling.
> >
> > Come on, Microsoft. You can do better than this.
> >
>
>|||I certainly appreciate that this is an important feature for many people. I
cringe every time I look at the features that we end up having to cut to
meet our schedule. One of the ways that we try to mitigate the fact that we
can't get everything in is by providing extensibility interfaces that people
can use to add functionality we didn't have time to do. Printer delivery is
one of these that is possible to add. It is not going to make it into SQL
2005 as we are already code complete with the product and are in bug fix
mode. It is very high on the list for the post 2005 release.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"dj" <dj@.discussions.microsoft.com> wrote in message
news:1F422F95-A85D-4C66-BC2D-DB65563D5113@.microsoft.com...
> Brian -
> I probably could have expressed my frustration somewhat better.
> You and your team deserve a lot of credit for delivering a solid, useful
> reporting tool.
> And you are right on target that not everyone has the same needs or
> priorities.
> As you can tell, server-side printing is a major priority for us. Trying
> to
> move to
> RS from Access and CR-based reporting is a challenge we are currently
> working through. Converting and losing a capability we previously had
> makes
> things more of a challenge.
> Not to labor the point, but I can't help but think that when most
> managers,
> executives, etc. think of the term 'report', they think of a piece of
> paper.
> Perhaps I'm wrong, but judging from the response I've seen personally,
> this
> would appear to be the case.
> Certainly, we have many reporting scenarios, as most shops do, and
> printing
> does
> not figure in all, or even most of them. It's just frustrating to not be
> able to
> deliver on all your client's requests. Hmmm, does that sound familiar?
> So, let me try this again:
> Brian, if you're not already doing so, please consider adding server-side
> printing
> to the next release of Reporting Services.
> Best regards,
> Dexter Jones, MCSD
>
> "Brian Welcker [MSFT]" wrote:
>> While we would love to add every feature in the product, we simply don't
>> have resources to do so. So we have to pick and choose. It turns out
>> every
>> person that you ask considers a different feature "absolutely critical".
>> Some of these "must haves" that we have added for SQL 2005 include
>> multi-valued parameters, a date picker, and an end-user report designer.
>> Other "must haves" that we didn't have time to do include rich text,
>> style
>> templates, and Word output (to name a few).
>> Are you saying that you would rather have server-side printing instead of
>> client printing? If so, you have different priorities than the set of
>> customers we talked to (which is understandable) before we decided to add
>> client print. Anyway, if enough people really think this is important, it
>> can be easily written using the extensibility mechanism in RS. Or, you
>> can
>> wait until we get a chance to build it in.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "dj" <dj@.discussions.microsoft.com> wrote in message
>> news:62871B7A-70B5-48A3-9BF0-FE4372235F27@.microsoft.com...
>> >I waited to see if sp2 would address this issue; it does not.
>> >
>> > Don't get me wrong, I like RS in general, but, where is the ability to
>> > print?
>> > This seems, well, fundamental in a reporting tool. Can you name any
>> > other
>> > serious reporting or BI product that doesn't let you output to paper?
>> > I
>> > can't and I've been around the block a time or two.
>> >
>> > Yes, I know sp2 adds client-side printing and that's nice, but it
>> > misses
>> > the
>> > point.
>> > How is one supposed to have the boss's report waiting for him in the
>> > printer
>> > in the morning using RS? There are still some people that want reports
>> > on
>> > paper.
>> >
>> > You should see the looks on people's faces when, in the course of
>> > telling
>> > them why we should consider replacing our current reporting tool with
>> > RS,
>> > that RS does not have the ability to automatically print a report.
>> > It's a
>> > pretty awkward feeling.
>> >
>> > Come on, Microsoft. You can do better than this.
>> >
>>

Hourly Scheduling

I have reports that I need to run every hour during the "scheduled" working
day.
Meaning between 8am and 8pm. The only way that I can see to do that is to
create one entry for each hour that I want it to run, meaning 12 scheduled
tasks for each report that I want to run in this manner ...
That could become a maintainence nightmare... Anyone got a better way to do
the same thing?
Thanks,
GaryCan't you create a SQL Server Agent Job Which occurs every 1 hour starting at
8AM and ending at 8PM?
HTH
"Gary Stewart" wrote:
> I have reports that I need to run every hour during the "scheduled" working
> day.
> Meaning between 8am and 8pm. The only way that I can see to do that is to
> create one entry for each hour that I want it to run, meaning 12 scheduled
> tasks for each report that I want to run in this manner ...
> That could become a maintainence nightmare... Anyone got a better way to do
> the same thing?
> Thanks,
> Gary
>
>|||You may create a subcscription for that report and choose hourly
schedule.
Gary Stewart wrote:
> I have reports that I need to run every hour during the "scheduled" working
> day.
> Meaning between 8am and 8pm. The only way that I can see to do that is to
> create one entry for each hour that I want it to run, meaning 12 scheduled
> tasks for each report that I want to run in this manner ...
> That could become a maintainence nightmare... Anyone got a better way to do
> the same thing?
> Thanks,
> Gary

Hourly batch process locking my database

Hello everyone,

I have around 20 reports in an ASP web-application which connects to a
SQL Server 2000 dB, executes stored procedures based on input
parameters and returns the data in a nice tabular format.

The data which is used in these reports actually originates from a 3rd
party accounting application called Exchequer. I have written a VB
application (I call it the extractor) which extracts data from
Exchequer and dumps the same into the SQL Server dB every hour. The
running time for the extractor is an average of 10 minutes. During
these 10 minutes, while the extractor seems to run happily, my ASP
web-application which queries the same dB that the extractor
application is updating becomes dead slow.

Is there anyway I can get the extractor to be nice to SQL Server and
not take up all its resources so that the ASP web-application users do
not have to contend with a very very slow application during those
times?

I am using a DSN to connect to the dB from the server that runs the
web-application and well as the other server which runs extractor.
Connection pooling has been enabled on both (using the ODBC
Administrator). The Detach Database dialog gives me a list of open
connections to the dB. I have been monitoring the same and I have
noted 10-15 open connections at most times, even during the execution
of extractor.

All connection objects in the ASP as well as VB applications are
closed and then set to nothing.

This system has been in use from 2002. My Data file has grown to 450MB
and my Transaction Log is close to 2GB. Can the Transaction Log be a
problem. For some reason, the size of the Transaction Log does not go
down even after a complete dB backup is done. Once a complete dB
backup is done, doesn't the Transaction Log lose its significance and
can be actually deleted? Anyway this is another post I'm doing today
to the group.

In the extractor program,
1) I create a temporary table
2) I create an empty recordset out of the table
3) I loop through the Exchequer records using Exchequer's APIs, adding
records into the recordset of the temporary table as I go along.
4) I do an UpdateBatch of the Recordset intermitently
5) I open an SQL Transaction
6) I delete all records from the main table
7) I run a INSERT INTO main_table SELECT * FROM #temp_table
8) I commit the transaction

I hope that the information is sufficient

Thanks
SamSam (simantahandique@.indiatimes.com) writes:
> Is there anyway I can get the extractor to be nice to SQL Server and
> not take up all its resources so that the ASP web-application users do
> not have to contend with a very very slow application during those
> times?

There probably is, but it's not that there is a low-priority setting.
You simply will have to analyse in what way the extractor is slowing
the rest down. It could be blocking, it could be that the extractor
consumes a lot of CPU. Indeed during these steps:

> 5) I open an SQL Transaction
> 6) I delete all records from the main table
> 7) I run a INSERT INTO main_table SELECT * FROM #temp_table
> 8) I commit the transaction

The other processes are likely to be blocked, as the main table is
being reloaded.

I don't think you said how much data that is loaded each time, but it is
not impossible that here are possibilities for improvements. In step 6,
you could use TRUNCATE TABLE to empty the table, this is faster than using
the DELETE statement. TRUNCATE TABLE will not work if the table has
foreign keys. Also, the extractor must be running as dbo to have permission.

Then again, does data really change that wildly? Maybe you are better off
loading deltas only?

Also: you load the data through a recordset. You are likely to get better
performance with bulk-load operations.

> This system has been in use from 2002. My Data file has grown to 450MB
> and my Transaction Log is close to 2GB. Can the Transaction Log be a
> problem. For some reason, the size of the Transaction Log does not go
> down even after a complete dB backup is done. Once a complete dB
> backup is done, doesn't the Transaction Log lose its significance and
> can be actually deleted?

Well, someone has to make that decision, and SQL Server is not doing it
behind your back. You need to backup the transaction log as well to
keep it down in size. The transaction log is kept so that you can back
it up and make an up-to-the point recovery.

Judging from the description of the database, it sounds that you might
be interested in setting the database in simple recovery. This means
that you cannot perform up-to-the-point recovery, only restore from
the most recent backup. The flip side is that SQL Server will regularly
truncate the transaction log from all committed transaction.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you for your post. A couple of points.

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns952F720586F5AYazorman@.127.0.0.1>...
> Then again, does data really change that wildly? Maybe you are better off
> loading deltas only?
I run the extractor every hour to synchronize data from the past 7
days from Exchequer. This is very much the requirement, so I do not
have any choice there. This results on an average 10,000 records to be
moved into the main_table from the #temp_table, which is when the
dead-lock happens. Does this information help you in any way to
suggest some improvements?

I was thinking of running two parallel dBs in some sort of a
round-robin load balanced setup. (I'm sure the "round-robin load
balanced" term is completely incorrect to describe the setup. But I am
using it for lack of a better phrase to describe my plan) In a typical
scenario, the Extractor will run on dB1. The Intranet web-application
will be connected to dB2. Once the extractor completes its execution,
it can alter the connection string in the conf file for my Intranet
web-application so that the Intranet connects to the updated dB1
henceforth. The next time the extractor executes, it will update dB2
instead of dB1 and switch the Intranet back to dB2 after its
execution, and so on. The only problem is that the dB has around 20
tables out of which only 6 are being updated by Extractor. How do I
synchronize the other 14 tables between dB1 and db2? Does SQL Server
provide any facility for that?

> Also: you load the data through a recordset. You are likely to get better
> performance with bulk-load operations.
What are these bulk-load operations? Are they a group of SQL Commands?
If you can point me to the right place in the SQL Server help file, I
can read up from there.

> Judging from the description of the database, it sounds that you might
> be interested in setting the database in simple recovery. This means
> that you cannot perform up-to-the-point recovery, only restore from
> the most recent backup. The flip side is that SQL Server will regularly
> truncate the transaction log from all committed transaction.
I just enquired with the administration team. They are using a
third-party software called Backup Exec from Veritas for all backup
activities. The backup software uses some thing it calls as SQL Server
agents to backup the dB. I'm not sure if these SQL Server agents would
cause the transaction log to get truncated once they are done with the
backups.
If I use a simple recovery model, is the transaction log table still
used by the dB? Would SQL statements which have a BEGIN TRANS use the
Transaction Log? Once the transaction is committed in this case, does
it automatically truncate the Transaction Log and thus keep its file
size in check?
These questions may been answered in the previous post. I'm sorry if
I'm repeating the same question. But I could not understand the last
few sentences completely. Especially, when it was mentioned thus: "The
flip side is that ...". Why do you say it is a "flip" side?

Thanks and regards,
Sam|||Just a suggestion for you. It sounds similar to something we do in
here, so it may be worth a thought.

We have to extract a large number of records from several tables. From
this we need to perform some complex calculations and play around with
data formatting to get the data into a required output which is a
single table. We need to do this every 10 minutes with no longer than
3 mins taken up for the processing. Locking users is a major concern,
as previous methods used directly on the 'live' tables caused us
problems.

What we found worked best for us was to have a database in which we
could store the structure of the tables we were copying (specifically
just the fields we needed). Using a DTS package and SP's, we extract
the data from the master db into CSV files (very quick to do this -
for 1/2 million records between all of the tables, this takes about 1
min to export and import) and re-import the data into the copied
structure in the new database. We then perform all of the calculations
on these tables as opposed to the master 'live' ones.

I know it probably seems daft exporting and importing, but it's the
quickest way of getting the data without causing the users problems.
SQL server can easily handle the workload on the data, but we were
causing too many problems with locking. By doing this, we have
improved the speed dramatically.

The advantages are that you can take the data as read only and reduce
locking (although this will be minimal) and speed of export and
import.

A simple scheduled task runs this for us every 10 minutes.

If you get a chance, try this and see how it performs against your
normal method, it may prove quick enough for you to take further.

Hope that helps, let me know if you need more info.

Ryan|||Sam (simantahandique@.indiatimes.com) writes:
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message
> news:<Xns952F720586F5AYazorman@.127.0.0.1>...
>>
>> Then again, does data really change that wildly? Maybe you are better off
>> loading deltas only?
>>
> I run the extractor every hour to synchronize data from the past 7
> days from Exchequer. This is very much the requirement, so I do not
> have any choice there. This results on an average 10,000 records to be
> moved into the main_table from the #temp_table, which is when the
> dead-lock happens. Does this information help you in any way to
> suggest some improvements?

Not really. If I understood your previous post, you are once an hour
flushing the target database, and reloads everything from Exchequer. This
sounds very ineffecient to me. Is there no way to find out what has
changed, and load only the changes?

> I was thinking of running two parallel dBs in some sort of a
> round-robin load balanced setup.
> ...
> The only problem is that the dB has around 20 tables out of which only 6
> are being updated by Extractor. How do I synchronize the other 14 tables
> between dB1 and db2? Does SQL Server provide any facility for that?

You could use triggers for this, although you need to take some care that
you don't trigger forth and back.

However, having two databases is a bit overkill. It might be possible
to do this with two tables that you rename once an hour. Not that this
is particularly exciting. (Loading only deltas still seems like the best
bet.)

> What are these bulk-load operations? Are they a group of SQL Commands?
> If you can point me to the right place in the SQL Server help file, I
> can read up from there.

There are several methods to bulk-load:

o BCP, which is a command-line tool.
o BULK INSERT which is an SQL command.
o There are bulk-copy APIs both in OLE DB and ODBC.
o DTS.

The easiest to use in my opinion is BCP. But these ways have many things
in common, including how you specify how the data to import looks like.
(With the reservation for DTS that I don't know much about.)

With bulk-load, you could get all data into a staging table, and then
work from there. Preferably set-based, and not one-by-one processing
like in ADO.

> I just enquired with the administration team. They are using a
> third-party software called Backup Exec from Veritas for all backup
> activities. The backup software uses some thing it calls as SQL Server
> agents to backup the dB. I'm not sure if these SQL Server agents would
> cause the transaction log to get truncated once they are done with the
> backups.

I don't know about Backup Exec, you will have to sort that out with
your admin people. But I would guess, that it's a configuration thing
whether you also backup the log with Backup Exec.

Then again... You said, 10000 rows on average each time. If I assume
an average row size of 500 bytes, this means that you insert and delete
10000 * 500 * 2 * 24 = 240.000.000 bytes a day. If your transaction log
never had been truncated since 2002, you would have a terabyte transaction
log by now. So I would guess that it is backed up after all.

> If I use a simple recovery model, is the transaction log table still
> used by the dB? Would SQL statements which have a BEGIN TRANS use the
> Transaction Log? Once the transaction is committed in this case, does
> it automatically truncate the Transaction Log and thus keep its file
> size in check?

More or less, yes. That is, transaction atomicity is still maintained,
so that a transaction can be rolled back (or forward). The log is never
truncated past the oldest active open transaction. The actual truncation
does not really happen with commit, but by the checkpointing process
which runs about once minute or so. But that's really nothing you have
to bother about.