display SQL Server: reports

Showing posts with label reports. Show all posts

Friday, March 30, 2012

How do I migrate reports to new server

Hi all,
I just installed reporting services on a new server and need to migrate
existing reports from an old server to the new one. Being new to Reporting
Services, I am not sure of how to do this. Any help you gurus can provide
will be greatly appreciated.
Thanks,
--
LynnYou might want to use Reporting Services Scripter
http://www.sqldbatips.com/showarticle.asp?ID=62
Any questions just drop me a mail using the link in the readme file
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:7BEBF34B-420F-42D0-BEB0-33BF5E01AD46@.microsoft.com...
> Hi all,
> I just installed reporting services on a new server and need to migrate
> existing reports from an old server to the new one. Being new to
> Reporting
> Services, I am not sure of how to do this. Any help you gurus can provide
> will be greatly appreciated.
> Thanks,
> --
> Lynn|||> I just installed reporting services on a new server and need to migrate
> existing reports from an old server to the new one. Being new to
> Reporting
> Services, I am not sure of how to do this. Any help you gurus can provide
> will be greatly appreciated.
Hi Lynn,
I think this tool [1] can help you.
Look in the generated script to find out how it is working.
[1] http://www.sqldbatips.com/showarticle.asp?ID=62
Best regards,
--
Martin Kulov
http://www.codeattest.com/blogs/martin
MCAD Charter Member
MCSD.NET Early Achiever
MCSD

Wednesday, March 28, 2012

how do I make 30 sec running query (select c1 sum(x) from t1 where c1 > 1000 group by c1) run

It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.

Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?

if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?

Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?

Thanks in for you help in advance

if the memory available is not enough for internal operations like aggregation and ordering, SQL Server will implictly go to Tempdb and will store the results intermediately here. You cannot avoid is, beside putting more available RAM on the process. Don′t know why this slows down your process that much, did you had a look in the SQL Server logs, esprically on database growth ? Maybe SQL Server is increasing the data files one by one, leading to the problem that the query wioll be halted for the time needed to extend the database.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How do I link two different field lengths with the same data

Hi,
In Crystal Reports 8.5, I am linking one file to another but do not get report data. The reason is the fields I am using to link one string is 4 characters while the other is 6. Is there a way to get two fields containing the same data in different files but wth different field lengths to link. thank youBump. This post is old, but I'm needing to do basically the same thing. I've got Crystal Reports 11.

Thanks

How do I know the version of Crystal Reports that I am using ??

How do I know the version of Crystal Reports that I am using. I am using Crystal Reports that has come bundled with Visual Studio 2003. How do I know its Version ?Open it? Help -> About.

Friday, March 23, 2012

How Do I Increase File Upload Size?

Does anyone know what the max upload size is? I want to save some documents
with my reports, but I get the following errors:
"Maximum request length exceeded."
or
"There was an exception running the extensions specified in the config file.
--> Maximum request length exceeded."
Thanks in advance,
-RGJIs the report server running on Windows 2003 with IIS 6? IIS 6 has a
security restriction of a default 4 MB file upload/download limit. This is
due to a buffering restriction implemented in IIS 6.0
(AspMaxRequestEntityAllowed setting in MetaBase.xml).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RGJ" <RGJ@.discussions.microsoft.com> wrote in message
news:81C3E356-EE8C-490E-BC38-8F3A83DAF9E8@.microsoft.com...
> Does anyone know what the max upload size is? I want to save some
documents
> with my reports, but I get the following errors:
> "Maximum request length exceeded."
> or
> "There was an exception running the extensions specified in the config
file.
> --> Maximum request length exceeded."
> Thanks in advance,
> -RGJ|||You got my hopes up :-)
Yes, It is running on Windows 2003 with IIS 6. I have tried changing that
value, but no luck.
Here is the documentation I found on that setting:
he AspMaxRequestEntityAllowed property specifies the maximum number of bytes
allowed in the entity body of an ASP request. If a Content-Length header is
present and specifies an amount of data greater than the value of
AspMaxRequestEntityAllowed, IIS returns a 403 error response. This property
is related in function to MaxRequestEntityAllowed, but is specific to ASP
request. Whereas you might set the MaxRequestEntityAllowed property to 1 MB
at the general World Wide Web Publishing Service (WWW Service) level, you may
choose to set AspMaxRequestEntityAllowed to a lower value, if you know that
your specific ASP applications handle a smaller amount of data.
I also tried increasing the AspBufferingLimit which has a 4Mb limit to 40Mb
- no good
The AspBufferingLimit property sets the maximum size of the ASP buffer. If
response buffering is turned on, this property controls the maximum number of
bytes that an ASP page can write to the response buffer before a flush occurs.
Do you know if there is a setting in web.config that might override this?
Thank you for your assistance,
-RGJ
"Robert Bruckner [MSFT]" wrote:
> Is the report server running on Windows 2003 with IIS 6? IIS 6 has a
> security restriction of a default 4 MB file upload/download limit. This is
> due to a buffering restriction implemented in IIS 6.0
> (AspMaxRequestEntityAllowed setting in MetaBase.xml).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "RGJ" <RGJ@.discussions.microsoft.com> wrote in message
> news:81C3E356-EE8C-490E-BC38-8F3A83DAF9E8@.microsoft.com...
> > Does anyone know what the max upload size is? I want to save some
> documents
> > with my reports, but I get the following errors:
> >
> > "Maximum request length exceeded."
> > or
> > "There was an exception running the extensions specified in the config
> file.
> > --> Maximum request length exceeded."
> >
> > Thanks in advance,
> >
> > -RGJ
>
>|||Please verify that in IIS Manager, you have turned on "Enable direct
metabase edit / allow changes to MetaBase configuration while IIS is
running" before changing metabase.xml. Otherwise the changes will be
ignored! Also did you try to run iisreset?
You should also be able to do this from the command-line:
1. cscript adsutil.vbs get w3svc/aspmaxrequestentityallowed
2. replace 'get' to 'set' to configure the new value
3. then get again
Is it new or the old value?
-Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RGJ" <RGJ@.discussions.microsoft.com> wrote in message
news:5947747C-0259-4424-BA3D-272FECD7D33C@.microsoft.com...
> You got my hopes up :-)
> Yes, It is running on Windows 2003 with IIS 6. I have tried changing that
> value, but no luck.
> Here is the documentation I found on that setting:
> he AspMaxRequestEntityAllowed property specifies the maximum number of
bytes
> allowed in the entity body of an ASP request. If a Content-Length header
is
> present and specifies an amount of data greater than the value of
> AspMaxRequestEntityAllowed, IIS returns a 403 error response. This
property
> is related in function to MaxRequestEntityAllowed, but is specific to ASP
> request. Whereas you might set the MaxRequestEntityAllowed property to 1
MB
> at the general World Wide Web Publishing Service (WWW Service) level, you
may
> choose to set AspMaxRequestEntityAllowed to a lower value, if you know
that
> your specific ASP applications handle a smaller amount of data.
> I also tried increasing the AspBufferingLimit which has a 4Mb limit to
40Mb
> - no good
> The AspBufferingLimit property sets the maximum size of the ASP buffer. If
> response buffering is turned on, this property controls the maximum number
of
> bytes that an ASP page can write to the response buffer before a flush
occurs.
> Do you know if there is a setting in web.config that might override this?
> Thank you for your assistance,
> -RGJ
> "Robert Bruckner [MSFT]" wrote:
> > Is the report server running on Windows 2003 with IIS 6? IIS 6 has a
> > security restriction of a default 4 MB file upload/download limit. This
is
> > due to a buffering restriction implemented in IIS 6.0
> > (AspMaxRequestEntityAllowed setting in MetaBase.xml).
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "RGJ" <RGJ@.discussions.microsoft.com> wrote in message
> > news:81C3E356-EE8C-490E-BC38-8F3A83DAF9E8@.microsoft.com...
> > > Does anyone know what the max upload size is? I want to save some
> > documents
> > > with my reports, but I get the following errors:
> > >
> > > "Maximum request length exceeded."
> > > or
> > > "There was an exception running the extensions specified in the config
> > file.
> > > --> Maximum request length exceeded."
> > >
> > > Thanks in advance,
> > >
> > > -RGJ
> >
> >
> >|||Robert,
You were close. Thanks for putting me on the right track.
I was able to increase the File Upload by following this article ..
====================================================
Upload Larger Files
By default, ASP.NET only permits files that are 4,096 kilobytes (KB) (or 4
MB) or less to be uploaded to the Web server. To upload larger files, you
must change the maxRequestLength parameter of the <httpRuntime> section in
the Web.config file.
Note When the maxRequestLength attribute is set in the Machine.config file
and then a request is posted (for example, a file upload) that exceeds the
value of maxRequestLength, a custom error page cannot be displayed. Instead,
Microsoft Internet Explorer will display a "Cannot find server or DNS" error
message.
If you want to change this setting for all of the computer and not just this
ASP.NET application, you must modify the Machine.config file.
By default, the <httpRuntime> element is set to the following parameters in
the Machine.config file:
<httpRuntime executionTimeout="90" maxRequestLength="4096"
useFullyQualifiedRedirectUrl="false" minFreeThreads="8"
minLocalRequestFreeThreads="4" appRequestQueueLimit="100" />
The Machine.config file is located in the \System
Root\Microsoft.NET\Framework\Version Number\CONFIG directory.
====================================================
I modifiedthe machine.config file and reboot - and it worked !!!
Thanks for your help,
-RGJ|||Thanks for getting back and sharing how you resolved it.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RGJ" <RGJ@.discussions.microsoft.com> wrote in message
news:609C3712-46CF-451A-AC98-B09864097228@.microsoft.com...
> Robert,
> You were close. Thanks for putting me on the right track.
> I was able to increase the File Upload by following this article ..
> ====================================================> Upload Larger Files
> By default, ASP.NET only permits files that are 4,096 kilobytes (KB) (or 4
> MB) or less to be uploaded to the Web server. To upload larger files, you
> must change the maxRequestLength parameter of the <httpRuntime> section in
> the Web.config file.
> Note When the maxRequestLength attribute is set in the Machine.config file
> and then a request is posted (for example, a file upload) that exceeds the
> value of maxRequestLength, a custom error page cannot be displayed.
Instead,
> Microsoft Internet Explorer will display a "Cannot find server or DNS"
error
> message.
> If you want to change this setting for all of the computer and not just
this
> ASP.NET application, you must modify the Machine.config file.
> By default, the <httpRuntime> element is set to the following parameters
in
> the Machine.config file:
> <httpRuntime executionTimeout="90" maxRequestLength="4096"
> useFullyQualifiedRedirectUrl="false" minFreeThreads="8"
> minLocalRequestFreeThreads="4" appRequestQueueLimit="100" />
> The Machine.config file is located in the \System
> Root\Microsoft.NET\Framework\Version Number\CONFIG directory.
> ====================================================> I modifiedthe machine.config file and reboot - and it worked !!!
> Thanks for your help,
> -RGJ
>

Monday, March 19, 2012

How do i get rid of page breaks for HTML view...

Hello, I would like to get my reports to be on one page when viewing in HTML
in the report viewer. People generally dont want to have to flip through
pages when looking at reports online... they would much rather see the entire
report and just keep scrolling... however when printing you would want the
page-breaks to still happen (naturall).
Currently in Crystal using their report viewer control I simply removing the
paging controls to get this effect iirc... i tried hiding the paging control
in the RS report viewer control and i simply got a paged report with no way
of advancing through the pages...
Can someone please help me do this.Here is the solution. This works only for RS 2005. RS 2005 introduced
interactivesize which consists of interactivewidth and interactiveheight. To
have no page breaks set the interactiveheight to 0. This only works for
height, not width. You need to get to the report properties. You know how
when you click on the body or a textbox you see the property page for that
element on the right. If you click to the left of the ruler, that gets the
report properties to show (the menu, report properties does allow setting
this). Look for interactivesize, expand and then set interactiveheight to 0.
Not very discoverable but pretty cool.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"thejez" <thejez@.discussions.microsoft.com> wrote in message
news:E4ABC9C1-43F0-4564-B248-7EE7D818CC0B@.microsoft.com...
> Hello, I would like to get my reports to be on one page when viewing in
> HTML
> in the report viewer. People generally dont want to have to flip through
> pages when looking at reports online... they would much rather see the
> entire
> report and just keep scrolling... however when printing you would want the
> page-breaks to still happen (naturall).
> Currently in Crystal using their report viewer control I simply removing
> the
> paging controls to get this effect iirc... i tried hiding the paging
> control
> in the RS report viewer control and i simply got a paged report with no
> way
> of advancing through the pages...
> Can someone please help me do this.|||I think im posting this question to wrong place (but in all fairness its very
confusing from MS) - I am using a LOCAL report in VS 2005 ReportViewer
control - so i dont see the interactive options. I have RS 2005 but I am
unable to use it because of the limitations of how i can poplate the data (I
have to do pre-aggregation on the data before it gets to the dataset to get
around the aggregate of aggregates lmiitation). So my only option was to use
a local report where i could get the data from the DB pre-process it and then
pop the dataset and then render the report... this a big pain i know but what
can i do...
so is there any way in the VS 2005 ReportViewer and a local Report file
(rdlc). I have height set to 100% on everything and i even went to far as to
define the report at 160in high... but alas the control seems to break at the
same place ignoring everything i throw at it!
:(
"Bruce L-C [MVP]" wrote:
> Here is the solution. This works only for RS 2005. RS 2005 introduced
> interactivesize which consists of interactivewidth and interactiveheight. To
> have no page breaks set the interactiveheight to 0. This only works for
> height, not width. You need to get to the report properties. You know how
> when you click on the body or a textbox you see the property page for that
> element on the right. If you click to the left of the ruler, that gets the
> report properties to show (the menu, report properties does allow setting
> this). Look for interactivesize, expand and then set interactiveheight to 0.
> Not very discoverable but pretty cool.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "thejez" <thejez@.discussions.microsoft.com> wrote in message
> news:E4ABC9C1-43F0-4564-B248-7EE7D818CC0B@.microsoft.com...
> > Hello, I would like to get my reports to be on one page when viewing in
> > HTML
> > in the report viewer. People generally dont want to have to flip through
> > pages when looking at reports online... they would much rather see the
> > entire
> > report and just keep scrolling... however when printing you would want the
> > page-breaks to still happen (naturall).
> >
> > Currently in Crystal using their report viewer control I simply removing
> > the
> > paging controls to get this effect iirc... i tried hiding the paging
> > control
> > in the RS report viewer control and i simply got a paged report with no
> > way
> > of advancing through the pages...
> >
> > Can someone please help me do this.
>
>|||Bruce, i managed to just put the <InteractiveHeight>0in</InteractiveHeight>
element into my RDLC file and it worked... so even though there isnt any
design-tim support for setting that value (at least that i saw) adding it to
the source file did the trick.
thanks!
"thejez" wrote:
> I think im posting this question to wrong place (but in all fairness its very
> confusing from MS) - I am using a LOCAL report in VS 2005 ReportViewer
> control - so i dont see the interactive options. I have RS 2005 but I am
> unable to use it because of the limitations of how i can poplate the data (I
> have to do pre-aggregation on the data before it gets to the dataset to get
> around the aggregate of aggregates lmiitation). So my only option was to use
> a local report where i could get the data from the DB pre-process it and then
> pop the dataset and then render the report... this a big pain i know but what
> can i do...
> so is there any way in the VS 2005 ReportViewer and a local Report file
> (rdlc). I have height set to 100% on everything and i even went to far as to
> define the report at 160in high... but alas the control seems to break at the
> same place ignoring everything i throw at it!
> :(
> "Bruce L-C [MVP]" wrote:
> > Here is the solution. This works only for RS 2005. RS 2005 introduced
> > interactivesize which consists of interactivewidth and interactiveheight. To
> > have no page breaks set the interactiveheight to 0. This only works for
> > height, not width. You need to get to the report properties. You know how
> > when you click on the body or a textbox you see the property page for that
> > element on the right. If you click to the left of the ruler, that gets the
> > report properties to show (the menu, report properties does allow setting
> > this). Look for interactivesize, expand and then set interactiveheight to 0.
> >
> > Not very discoverable but pretty cool.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "thejez" <thejez@.discussions.microsoft.com> wrote in message
> > news:E4ABC9C1-43F0-4564-B248-7EE7D818CC0B@.microsoft.com...
> > > Hello, I would like to get my reports to be on one page when viewing in
> > > HTML
> > > in the report viewer. People generally dont want to have to flip through
> > > pages when looking at reports online... they would much rather see the
> > > entire
> > > report and just keep scrolling... however when printing you would want the
> > > page-breaks to still happen (naturall).
> > >
> > > Currently in Crystal using their report viewer control I simply removing
> > > the
> > > paging controls to get this effect iirc... i tried hiding the paging
> > > control
> > > in the RS report viewer control and i simply got a paged report with no
> > > way
> > > of advancing through the pages...
> > >
> > > Can someone please help me do this.
> >
> >
> >|||You beat me to it, that is what I was going to suggest.
Also, here is a suggestion for development. Develop reports in RS 2005
designer. The designer is better and you can quickly preview. Don't deploy
the report. Then, rename the rdl file to rdlc and bring it into your
project. This is how I do it. Much nicer development environment.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"thejez" <thejez@.discussions.microsoft.com> wrote in message
news:1A2F6C1F-3453-412A-AC0E-363C18C89D08@.microsoft.com...
> Bruce, i managed to just put the
> <InteractiveHeight>0in</InteractiveHeight>
> element into my RDLC file and it worked... so even though there isnt any
> design-tim support for setting that value (at least that i saw) adding it
> to
> the source file did the trick.
> thanks!
> "thejez" wrote:
>> I think im posting this question to wrong place (but in all fairness its
>> very
>> confusing from MS) - I am using a LOCAL report in VS 2005 ReportViewer
>> control - so i dont see the interactive options. I have RS 2005 but I am
>> unable to use it because of the limitations of how i can poplate the data
>> (I
>> have to do pre-aggregation on the data before it gets to the dataset to
>> get
>> around the aggregate of aggregates lmiitation). So my only option was to
>> use
>> a local report where i could get the data from the DB pre-process it and
>> then
>> pop the dataset and then render the report... this a big pain i know but
>> what
>> can i do...
>> so is there any way in the VS 2005 ReportViewer and a local Report file
>> (rdlc). I have height set to 100% on everything and i even went to far
>> as to
>> define the report at 160in high... but alas the control seems to break at
>> the
>> same place ignoring everything i throw at it!
>> :(
>> "Bruce L-C [MVP]" wrote:
>> > Here is the solution. This works only for RS 2005. RS 2005 introduced
>> > interactivesize which consists of interactivewidth and
>> > interactiveheight. To
>> > have no page breaks set the interactiveheight to 0. This only works for
>> > height, not width. You need to get to the report properties. You know
>> > how
>> > when you click on the body or a textbox you see the property page for
>> > that
>> > element on the right. If you click to the left of the ruler, that gets
>> > the
>> > report properties to show (the menu, report properties does allow
>> > setting
>> > this). Look for interactivesize, expand and then set interactiveheight
>> > to 0.
>> >
>> > Not very discoverable but pretty cool.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "thejez" <thejez@.discussions.microsoft.com> wrote in message
>> > news:E4ABC9C1-43F0-4564-B248-7EE7D818CC0B@.microsoft.com...
>> > > Hello, I would like to get my reports to be on one page when viewing
>> > > in
>> > > HTML
>> > > in the report viewer. People generally dont want to have to flip
>> > > through
>> > > pages when looking at reports online... they would much rather see
>> > > the
>> > > entire
>> > > report and just keep scrolling... however when printing you would
>> > > want the
>> > > page-breaks to still happen (naturall).
>> > >
>> > > Currently in Crystal using their report viewer control I simply
>> > > removing
>> > > the
>> > > paging controls to get this effect iirc... i tried hiding the paging
>> > > control
>> > > in the RS report viewer control and i simply got a paged report with
>> > > no
>> > > way
>> > > of advancing through the pages...
>> > >
>> > > Can someone please help me do this.
>> >
>> >
>> >|||another update... after re-starting visual studio (since my properties window
was laggin very badly) i went back into the report and now i notice that when
you click on the out-grey area of the report i can see the interactive
property... hrmm so it is there... at least now it is... i dont think it was
there before.. but like i said my properties page was acting VERY funky (like
showing the properties for a DD control on another page while editing the
report...)
"thejez" wrote:
> Bruce, i managed to just put the <InteractiveHeight>0in</InteractiveHeight>
> element into my RDLC file and it worked... so even though there isnt any
> design-tim support for setting that value (at least that i saw) adding it to
> the source file did the trick.
> thanks!
> "thejez" wrote:
> > I think im posting this question to wrong place (but in all fairness its very
> > confusing from MS) - I am using a LOCAL report in VS 2005 ReportViewer
> > control - so i dont see the interactive options. I have RS 2005 but I am
> > unable to use it because of the limitations of how i can poplate the data (I
> > have to do pre-aggregation on the data before it gets to the dataset to get
> > around the aggregate of aggregates lmiitation). So my only option was to use
> > a local report where i could get the data from the DB pre-process it and then
> > pop the dataset and then render the report... this a big pain i know but what
> > can i do...
> >
> > so is there any way in the VS 2005 ReportViewer and a local Report file
> > (rdlc). I have height set to 100% on everything and i even went to far as to
> > define the report at 160in high... but alas the control seems to break at the
> > same place ignoring everything i throw at it!
> >
> > :(
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Here is the solution. This works only for RS 2005. RS 2005 introduced
> > > interactivesize which consists of interactivewidth and interactiveheight. To
> > > have no page breaks set the interactiveheight to 0. This only works for
> > > height, not width. You need to get to the report properties. You know how
> > > when you click on the body or a textbox you see the property page for that
> > > element on the right. If you click to the left of the ruler, that gets the
> > > report properties to show (the menu, report properties does allow setting
> > > this). Look for interactivesize, expand and then set interactiveheight to 0.
> > >
> > > Not very discoverable but pretty cool.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "thejez" <thejez@.discussions.microsoft.com> wrote in message
> > > news:E4ABC9C1-43F0-4564-B248-7EE7D818CC0B@.microsoft.com...
> > > > Hello, I would like to get my reports to be on one page when viewing in
> > > > HTML
> > > > in the report viewer. People generally dont want to have to flip through
> > > > pages when looking at reports online... they would much rather see the
> > > > entire
> > > > report and just keep scrolling... however when printing you would want the
> > > > page-breaks to still happen (naturall).
> > > >
> > > > Currently in Crystal using their report viewer control I simply removing
> > > > the
> > > > paging controls to get this effect iirc... i tried hiding the paging
> > > > control
> > > > in the RS report viewer control and i simply got a paged report with no
> > > > way
> > > > of advancing through the pages...
> > > >
> > > > Can someone please help me do this.
> > >
> > >
> > >|||Good advice! Ill do that from now on!
thanks
"Bruce L-C [MVP]" wrote:
> You beat me to it, that is what I was going to suggest.
> Also, here is a suggestion for development. Develop reports in RS 2005
> designer. The designer is better and you can quickly preview. Don't deploy
> the report. Then, rename the rdl file to rdlc and bring it into your
> project. This is how I do it. Much nicer development environment.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "thejez" <thejez@.discussions.microsoft.com> wrote in message
> news:1A2F6C1F-3453-412A-AC0E-363C18C89D08@.microsoft.com...
> > Bruce, i managed to just put the
> > <InteractiveHeight>0in</InteractiveHeight>
> > element into my RDLC file and it worked... so even though there isnt any
> > design-tim support for setting that value (at least that i saw) adding it
> > to
> > the source file did the trick.
> >
> > thanks!
> >
> > "thejez" wrote:
> >
> >> I think im posting this question to wrong place (but in all fairness its
> >> very
> >> confusing from MS) - I am using a LOCAL report in VS 2005 ReportViewer
> >> control - so i dont see the interactive options. I have RS 2005 but I am
> >> unable to use it because of the limitations of how i can poplate the data
> >> (I
> >> have to do pre-aggregation on the data before it gets to the dataset to
> >> get
> >> around the aggregate of aggregates lmiitation). So my only option was to
> >> use
> >> a local report where i could get the data from the DB pre-process it and
> >> then
> >> pop the dataset and then render the report... this a big pain i know but
> >> what
> >> can i do...
> >>
> >> so is there any way in the VS 2005 ReportViewer and a local Report file
> >> (rdlc). I have height set to 100% on everything and i even went to far
> >> as to
> >> define the report at 160in high... but alas the control seems to break at
> >> the
> >> same place ignoring everything i throw at it!
> >>
> >> :(
> >>
> >> "Bruce L-C [MVP]" wrote:
> >>
> >> > Here is the solution. This works only for RS 2005. RS 2005 introduced
> >> > interactivesize which consists of interactivewidth and
> >> > interactiveheight. To
> >> > have no page breaks set the interactiveheight to 0. This only works for
> >> > height, not width. You need to get to the report properties. You know
> >> > how
> >> > when you click on the body or a textbox you see the property page for
> >> > that
> >> > element on the right. If you click to the left of the ruler, that gets
> >> > the
> >> > report properties to show (the menu, report properties does allow
> >> > setting
> >> > this). Look for interactivesize, expand and then set interactiveheight
> >> > to 0.
> >> >
> >> > Not very discoverable but pretty cool.
> >> >
> >> >
> >> > --
> >> > Bruce Loehle-Conger
> >> > MVP SQL Server Reporting Services
> >> >
> >> > "thejez" <thejez@.discussions.microsoft.com> wrote in message
> >> > news:E4ABC9C1-43F0-4564-B248-7EE7D818CC0B@.microsoft.com...
> >> > > Hello, I would like to get my reports to be on one page when viewing
> >> > > in
> >> > > HTML
> >> > > in the report viewer. People generally dont want to have to flip
> >> > > through
> >> > > pages when looking at reports online... they would much rather see
> >> > > the
> >> > > entire
> >> > > report and just keep scrolling... however when printing you would
> >> > > want the
> >> > > page-breaks to still happen (naturall).
> >> > >
> >> > > Currently in Crystal using their report viewer control I simply
> >> > > removing
> >> > > the
> >> > > paging controls to get this effect iirc... i tried hiding the paging
> >> > > control
> >> > > in the RS report viewer control and i simply got a paged report with
> >> > > no
> >> > > way
> >> > > of advancing through the pages...
> >> > >
> >> > > Can someone please help me do this.
> >> >
> >> >
> >> >
>
>

How Do I Get Rid of Blank Pages in Reports?

Every report I've done puts a blank page in between each report page. The
page header is printed on all the blank pages.
I haven't specified any page breaks anywhere.
Is there a way to get rid of the blank pages?On Apr 20, 4:34 pm, Liz <L...@.discussions.microsoft.com> wrote:
> Every report I've done puts a blank page in between each report page. The
> page header is printed on all the blank pages.
> I haven't specified any page breaks anywhere.
> Is there a way to get rid of the blank pages?
One way to alleviate this, in some cases, would be to enclose your
controls (i.e., tables/matrices/textboxes, etc) in a list or rectangle
control and make sure that 'Fit table on one page if possible' is
selected for the table/matrix controls (where applicable). Hope this
is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Apr 21, 7:34 am, Liz <L...@.discussions.microsoft.com> wrote:
> Every report I've done puts a blank page in between each report page. The
> page header is printed on all the blank pages.
> I haven't specified any page breaks anywhere.
> Is there a way to get rid of the blank pages?
The most likely cause for this is either:
* your report items stretch too wide (beyond the page dimensions) and
are causing extra pages to be printed
* but more likely; the dimensions for your report are not the same as
your printing paper dimensions. Always check the width of your report
body + margins is about equal to the size of your printing paper (e.g.
for A4, your report body should be around 19.5cm wide, and left and
right margins 0.75cm each)|||Well, I'm afraid none of this works.
I put my table in a rectangle and specified to fit on one page when
possible. My report size is not larger than the page size. Nothing is
running off the sides or bottom. The whole report prints; I just get an
extra page between each report page.
I've messed with all of my margins making them bigger then smaller but that
doesn't make any difference either.
If you have any other ideas, I'm all ears. This is going to be a huge waste
of paper.
Thanks,
Liz
"Tokes" wrote:
> On Apr 21, 7:34 am, Liz <L...@.discussions.microsoft.com> wrote:
> > Every report I've done puts a blank page in between each report page. The
> > page header is printed on all the blank pages.
> >
> > I haven't specified any page breaks anywhere.
> >
> > Is there a way to get rid of the blank pages?
> The most likely cause for this is either:
> * your report items stretch too wide (beyond the page dimensions) and
> are causing extra pages to be printed
> * but more likely; the dimensions for your report are not the same as
> your printing paper dimensions. Always check the width of your report
> body + margins is about equal to the size of your printing paper (e.g.
> for A4, your report body should be around 19.5cm wide, and left and
> right margins 0.75cm each)
>|||On Apr 23, 11:56 pm, Liz <L...@.discussions.microsoft.com> wrote:
> Well, I'm afraid none of this works.
> I put my table in a rectangle and specified to fit on one page when
> possible. My report size is not larger than the page size. Nothing is
> running off the sides or bottom. The whole report prints; I just get an
> extra page between each report page.
> I've messed with all of my margins making them bigger then smaller but that
> doesn't make any difference either.
> If you have any other ideas, I'm all ears. This is going to be a huge waste
> of paper.
> Thanks,
> Liz
I'm fairly sure its to do with the size of your report body being
bigger than the paper size (width or height); try setting the report
body size to the absolute minimum it needs to be (i.e. the EXACT size
of the table in the design view) and see if that makes a difference
> "Tokes" wrote:
> > On Apr 21, 7:34 am, Liz <L...@.discussions.microsoft.com> wrote:
> > > Every report I've done puts a blank page in between each report page. The
> > > page header is printed on all the blank pages.
> > > I haven't specified any page breaks anywhere.
> > > Is there a way to get rid of the blank pages?
> > The most likely cause for this is either:
> > * your report items stretch too wide (beyond the page dimensions) and
> > are causing extra pages to be printed
> > * but more likely; the dimensions for your report are not the same as
> > your printing paper dimensions. Always check the width of your report
> > body + margins is about equal to the size of your printing paper (e.g.
> > for A4, your report body should be around 19.5cm wide, and left and
> > right margins 0.75cm each)|||I did all of the above; nothing worked. My entire report width was 10 inches
for a landscape report.
Finally, changing all my margins to zero fixed the problem.
"Tokes" wrote:
> On Apr 23, 11:56 pm, Liz <L...@.discussions.microsoft.com> wrote:
> > Well, I'm afraid none of this works.
> >
> > I put my table in a rectangle and specified to fit on one page when
> > possible. My report size is not larger than the page size. Nothing is
> > running off the sides or bottom. The whole report prints; I just get an
> > extra page between each report page.
> >
> > I've messed with all of my margins making them bigger then smaller but that
> > doesn't make any difference either.
> >
> > If you have any other ideas, I'm all ears. This is going to be a huge waste
> > of paper.
> >
> > Thanks,
> > Liz
> I'm fairly sure its to do with the size of your report body being
> bigger than the paper size (width or height); try setting the report
> body size to the absolute minimum it needs to be (i.e. the EXACT size
> of the table in the design view) and see if that makes a difference
> >
> > "Tokes" wrote:
> > > On Apr 21, 7:34 am, Liz <L...@.discussions.microsoft.com> wrote:
> > > > Every report I've done puts a blank page in between each report page. The
> > > > page header is printed on all the blank pages.
> >
> > > > I haven't specified any page breaks anywhere.
> >
> > > > Is there a way to get rid of the blank pages?
> >
> > > The most likely cause for this is either:
> > > * your report items stretch too wide (beyond the page dimensions) and
> > > are causing extra pages to be printed
> > > * but more likely; the dimensions for your report are not the same as
> > > your printing paper dimensions. Always check the width of your report
> > > body + margins is about equal to the size of your printing paper (e.g.
> > > for A4, your report body should be around 19.5cm wide, and left and
> > > right margins 0.75cm each)
>
>|||>I did all of the above; nothing worked. My entire report width was 10
>inches
> for a landscape report.
> Finally, changing all my margins to zero fixed the problem.
You had a 10 inch width (not sure of height) and you had margins. OK. What
is your printer's printable page area?
IOW: I'm pretty sure you were given correct advice here. Your height +
your top and bottom margin were > your printer's printable page height..
versus actual paper page height...
>L<
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:F1707B09-ABE5-4CB8-BE2E-FA8436DE5FA6@.microsoft.com...
>I did all of the above; nothing worked. My entire report width was 10
>inches
> for a landscape report.
> Finally, changing all my margins to zero fixed the problem.
> "Tokes" wrote:
>> On Apr 23, 11:56 pm, Liz <L...@.discussions.microsoft.com> wrote:
>> > Well, I'm afraid none of this works.
>> >
>> > I put my table in a rectangle and specified to fit on one page when
>> > possible. My report size is not larger than the page size. Nothing is
>> > running off the sides or bottom. The whole report prints; I just get
>> > an
>> > extra page between each report page.
>> >
>> > I've messed with all of my margins making them bigger then smaller but
>> > that
>> > doesn't make any difference either.
>> >
>> > If you have any other ideas, I'm all ears. This is going to be a huge
>> > waste
>> > of paper.
>> >
>> > Thanks,
>> > Liz
>> I'm fairly sure its to do with the size of your report body being
>> bigger than the paper size (width or height); try setting the report
>> body size to the absolute minimum it needs to be (i.e. the EXACT size
>> of the table in the design view) and see if that makes a difference
>> >
>> > "Tokes" wrote:
>> > > On Apr 21, 7:34 am, Liz <L...@.discussions.microsoft.com> wrote:
>> > > > Every report I've done puts a blank page in between each report
>> > > > page. The
>> > > > page header is printed on all the blank pages.
>> >
>> > > > I haven't specified any page breaks anywhere.
>> >
>> > > > Is there a way to get rid of the blank pages?
>> >
>> > > The most likely cause for this is either:
>> > > * your report items stretch too wide (beyond the page dimensions) and
>> > > are causing extra pages to be printed
>> > > * but more likely; the dimensions for your report are not the same as
>> > > your printing paper dimensions. Always check the width of your report
>> > > body + margins is about equal to the size of your printing paper
>> > > (e.g.
>> > > for A4, your report body should be around 19.5cm wide, and left and
>> > > right margins 0.75cm each)
>>

Monday, March 12, 2012

How do I format mailing address fields for display in reports

I am very new to development with SQL Server but I have lots of
experience with Access. I am producing a report in VB.Net using
CrystalReports that will display a company's address differently
depending on the contents of the data. For example, if the second
address line is NULL then I don't want it to display at all or if the
customer is not from the US then I want the country field displayed. My
database design is your typical address line 1, line 2, city, state,
postal code, country with lookup tables providing the full text for
state/provinces and countries. There's nothing fancy with the data.
My learning curve is both with Crystal and with SQL Server. I think
I'm better off trying to write a function in Transact SQL that I
could call from the query that VB.Net will use to create the XML that
will drive the report. I simply want to send a completely formatted
string to VB.Net, including commas, spaces and carriage returns and
line feeds. Please let me know if I'm nuts. I'm basing this
decision on the fact that Crystal is not the easiest tool to deal with
and that I probably have a better chance doing it on the server end.
I also have to believe that I'm not the first one who's ever wanted
to do this and am hoping that this code is out there somewhere for me
to legally pilfer and modify. I'm posting because my searches have so
far been unsuccessful.
Now, if anyone in Redmond is listening, those VB developers need to
walk down the hall and talk to the Access developers! I REALLY miss the
Access report writer and query builder. Their functionality and
productivity are superb. I find myself often linking Access to my SQL
Server databases, going into Access to build my query and then cutting
and pasting the SQL into Enterprise Manager. Now, if I could just write
my user-defined functions in VB or C then the world would then be a
much prettier and productive place.I kind of understand where you are coming from here, but you are probably
best served by doing this kind of formatting within crystal rather than in
T-SQL. This really is a presentation issue rather than a data issue.
"Foofs" <marta@.mindcrafted.com> wrote in message
news:1132331118.184758.42090@.g43g2000cwa.googlegroups.com...
>I am very new to development with SQL Server but I have lots of
> experience with Access. I am producing a report in VB.Net using
> CrystalReports that will display a company's address differently
> depending on the contents of the data. For example, if the second
> address line is NULL then I don't want it to display at all or if the
> customer is not from the US then I want the country field displayed. My
> database design is your typical address line 1, line 2, city, state,
> postal code, country with lookup tables providing the full text for
> state/provinces and countries. There's nothing fancy with the data.
> My learning curve is both with Crystal and with SQL Server. I think
> I'm better off trying to write a function in Transact SQL that I
> could call from the query that VB.Net will use to create the XML that
> will drive the report. I simply want to send a completely formatted
> string to VB.Net, including commas, spaces and carriage returns and
> line feeds. Please let me know if I'm nuts. I'm basing this
> decision on the fact that Crystal is not the easiest tool to deal with
> and that I probably have a better chance doing it on the server end.
> I also have to believe that I'm not the first one who's ever wanted
> to do this and am hoping that this code is out there somewhere for me
> to legally pilfer and modify. I'm posting because my searches have so
> far been unsuccessful.
> Now, if anyone in Redmond is listening, those VB developers need to
> walk down the hall and talk to the Access developers! I REALLY miss the
> Access report writer and query builder. Their functionality and
> productivity are superb. I find myself often linking Access to my SQL
> Server databases, going into Access to build my query and then cutting
> and pasting the SQL into Enterprise Manager. Now, if I could just write
> my user-defined functions in VB or C then the world would then be a
> much prettier and productive place.
>|||There is a *crystal* related microsoft newsgroup.
"Foofs" <marta@.mindcrafted.com> wrote in message
news:1132331118.184758.42090@.g43g2000cwa.googlegroups.com...
>I am very new to development with SQL Server but I have lots of
> experience with Access. I am producing a report in VB.Net using
> CrystalReports that will display a company's address differently
> depending on the contents of the data. For example, if the second
> address line is NULL then I don't want it to display at all or if the
> customer is not from the US then I want the country field displayed. My
> database design is your typical address line 1, line 2, city, state,
> postal code, country with lookup tables providing the full text for
> state/provinces and countries. There's nothing fancy with the data.
> My learning curve is both with Crystal and with SQL Server. I think
> I'm better off trying to write a function in Transact SQL that I
> could call from the query that VB.Net will use to create the XML that
> will drive the report. I simply want to send a completely formatted
> string to VB.Net, including commas, spaces and carriage returns and
> line feeds. Please let me know if I'm nuts. I'm basing this
> decision on the fact that Crystal is not the easiest tool to deal with
> and that I probably have a better chance doing it on the server end.
> I also have to believe that I'm not the first one who's ever wanted
> to do this and am hoping that this code is out there somewhere for me
> to legally pilfer and modify. I'm posting because my searches have so
> far been unsuccessful.
> Now, if anyone in Redmond is listening, those VB developers need to
> walk down the hall and talk to the Access developers! I REALLY miss the
> Access report writer and query builder. Their functionality and
> productivity are superb. I find myself often linking Access to my SQL
> Server databases, going into Access to build my query and then cutting
> and pasting the SQL into Enterprise Manager. Now, if I could just write
> my user-defined functions in VB or C then the world would then be a
> much prettier and productive place.
>

Wednesday, March 7, 2012

How do i find all subscriptions

Hi There

I need to get rid of all subscriptions (including cleaning up all associated sql jobs) for RS 2000 and 2005.

We have hundreds of reports , going through them 1 by 1 is not an option.

When i look at the system tables, i join Subscription to Catalog, but i find reports with subscriptions in the system tables but when i go to Report Manger there is no subscription for the report.

In a nutshell , how can i see all valid subscription for all reports in RS, how can i delete all of them and make sur eall sql jobs associated etc are deleted.?

Thanx

Hi Dietz, try it with the object-model of RS:

rs = new rs2005.ReportingService2005();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

rs.Url = "http://<Server>/ReportServer/ReportService2005.asmx";

Get all Subscriptions from a related Report

string RelatedReport = ConfigurationManager.AppSettings["RelatedReport"];

rs2005.Subscription[] subscr = rs.ListSubscriptions(RelatedReport, "");

string[] SubscID = new string[subscr.Length];

int icount = 0;

foreach (rs2005.Subscription s in subscr)

{

SubscID[icount] = s.SubscriptionID;

icount++;

}

Delete all Subscriptions from this Report.

for (int j = 0; j <= SubscID.Length-1; j++)

{

rs.DeleteSubscription(SubscID[j]);

}

If you want to delete just all Subscriptions, please read following Remarks from Microsoft:

You can supply a null (Nothing in Visual Basic) value for the Owner and Report parameters. The information that the ListSubscriptions method returns varies depending on the parameters that are submitted:

If the values of both Owner and Report are null, the method returns all subscriptions for all reports that the current user has permission to view.

If only the Owner parameter is submitted, the method returns all subscriptions for all reports that the specified user has created and has permission to view.

If only the Report parameter is submitted, the method returns all subscriptions for all users of the specified report that the current user has permission to view.

If valid values are supplied for both the Owner parameter and Report parameter, the method returns all subscriptions for the specified report that the specified user created and has permission to view.

|||

Hi Ronny

Thank You for the feedback, unfortunately i am tsql guy. Does anyone know of system stored procs that can give me this info, or delete subscriptions for RS?

Or a tsql equivelant of the code above?

Thanx

|||

Build a cursor to iterate over all Reports (Catalog)

Then set up a delete statement in interation

Delete from Subscriptions where Report_OID=Catalog.ItemID;

All Subscriptions will be deleted and also the jobs, beacause the triggers are doing their job.

Or use sp DeleteActiveSubscriptions, DeleteSubscription

Ronny

|||Awesome, thanx Ronny i will try this.|||

Sorry - we don't support folks directly accessing the report server database. If you build a solution on top of database calls, you will find that with service packs/hotfixes, or major versions, your solution will eventually break.

I have a blog post for how to monitor subscriptions here:

http://blogs.msdn.com/lukaszp/archive/2005/12/30/508328.aspx

Thanks,

-Lukasz

|||

Hi Lukasz, thank you for not supporting me!

i just want to let you know that accessing the Report Server Database directly is a job on admins. As a admin i am responsible for service packs / hofixes or major versions and i am able to read the release-notes. I also remember that i wrote a little tool which supports me in deleteing subscriptions. I can have a short look to see if the schema changes.

btw: arent the SOAP-API calls using the same stored procedures to acces the database?

All the Best

Ronny

|||

The SOAP APIs to use various stored procs. However, the definition of stored procs can change over time and unexpectedly. As such, I have to empahsize that you shouldn't rely on them.

The difference is that we version the SOAP APIs to ensure backwards compatibility. For stored procs/tables/views/etc, we just change them as needed to support our development efforts.

-Lukasz

|||

Hi Lukasz,

in general i agree with your statements, but until the Reporting Services are having some gaps in Administration i'm forced to do so.

And it was not so bad, because we earned money to sell those kind of hacks even to global players.

All the Best

Ronny

|||

Ronny,

Thanks for your honest feedback - I will communicate this more broadly in the Reporting Services team.

Thanks,

-Lukasz

How do i find all subscriptions

Hi There

I need to get rid of all subscriptions (including cleaning up all associated sql jobs) for RS 2000 and 2005.

We have hundreds of reports , going through them 1 by 1 is not an option.

When i look at the system tables, i join Subscription to Catalog, but i find reports with subscriptions in the system tables but when i go to Report Manger there is no subscription for the report.

In a nutshell , how can i see all valid subscription for all reports in RS, how can i delete all of them and make sur eall sql jobs associated etc are deleted.?

Thanx

Hi Dietz, try it with the object-model of RS:

rs = new rs2005.ReportingService2005();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

rs.Url = "http://<Server>/ReportServer/ReportService2005.asmx";

Get all Subscriptions from a related Report

string RelatedReport = ConfigurationManager.AppSettings["RelatedReport"];

rs2005.Subscription[] subscr = rs.ListSubscriptions(RelatedReport, "");

string[] SubscID = newstring[subscr.Length];

int icount = 0;

foreach (rs2005.Subscription s in subscr)

{

SubscID[icount] = s.SubscriptionID;

icount++;

}

Delete all Subscriptions from this Report.

for (int j = 0; j <= SubscID.Length-1; j++)

{

rs.DeleteSubscription(SubscID[j]);

}

If you want to delete just all Subscriptions, please read following Remarks from Microsoft:

If the values of both Owner and Report are null, the method returns all subscriptions for all reports that the current user has permission to view.

If only the Owner parameter is submitted, the method returns all subscriptions for all reports that the specified user has created and has permission to view.

If only the Report parameter is submitted, the method returns all subscriptions for all users of the specified report that the current user has permission to view.

|||

Hi Ronny

Thank You for the feedback, unfortunately i am tsql guy. Does anyone know of system stored procs that can give me this info, or delete subscriptions for RS?

Or a tsql equivelant of the code above?

Thanx

|||

Build a cursor to iterate over all Reports (Catalog)

Then set up a delete statement in interation

Delete from Subscriptions where Report_OID=Catalog.ItemID;

All Subscriptions will be deleted and also the jobs, beacause the triggers are doing their job.

Or use sp DeleteActiveSubscriptions, DeleteSubscription

Ronny

|||Awesome, thanx Ronny i will try this.|||

I have a blog post for how to monitor subscriptions here:

http://blogs.msdn.com/lukaszp/archive/2005/12/30/508328.aspx

Thanks,

-Lukasz

|||

Hi Lukasz, thank you for not supporting me!

btw: arent the SOAP-API calls using the same stored procedures to acces the database?

All the Best

Ronny

|||

The SOAP APIs to use various stored procs. However, the definition of stored procs can change over time and unexpectedly. As such, I have to empahsize that you shouldn't rely on them.

The difference is that we version the SOAP APIs to ensure backwards compatibility. For stored procs/tables/views/etc, we just change them as needed to support our development efforts.

-Lukasz

|||

Hi Lukasz,

in general i agree with your statements, but until the Reporting Services are having some gaps in Administration i'm forced to do so.

And it was not so bad, because we earned money to sell those kind of hacks even to global players.

All the Best

Ronny

|||

Ronny,

Thanks for your honest feedback - I will communicate this more broadly in the Reporting Services team.

Thanks,

-Lukasz

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

Friday, February 24, 2012

How do I enable 2005''s taskpad ?

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005's taskpad ?

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I embed and retrieve Crystal Reports from my app

Using VB .Net 2005 with Crystal Reports XI Release 2. I have a form that has a CrystalReportViewer control on it. When someone prints I run a sub that create a new instance of the form, creates a new reportdocument, loads the report to the new document, then passes the document to the report viewer on the form. This works great except I would like to either embed my crystal reports directly into my app or better yet embed them into thier own reports dll file. I want to do this so updates are easier and I can use the "Publish" function of VB 05 which doesn't seem to like publishing all the seperate files. The embedding should be the easy part, just set them as embedded resource. But I'm having trouble with the syntax to pull them out. For example you can use this to pull out a embdded icon:
Function GetEmbeddedIcon(ByVal strName As String) As Icon
Return New Icon(System.Reflection.Assembly.GetExecutingAssembly.GetManifestResourceStream(strName))
End Function

But I cannot get the syntax right to pull out and return a crystal report. Anyone have any ideas on how to pull the reports back out or a better way to do this. I dont have a lot of reports, about 15 right now, but that could grow.

-AllanWell...I couldn't find the answer anywhere and no one replied here. But I figured it out so I might as well help someone down the road.

Set your crystal reports to "embedded resource" and the copy to "do not copy". Then in your code you can clal them like any other object with a "New (reportname)". For example I have a crystal reports viewer control (apptly named CrystalReportViewer) on a form called ReportViewerForm. I made a small sub to print reports:

Friend Sub PrintReports(ByVal myReportFileName As ReportDocument, Optional ByVal mySQLFormula As String = "")
' Prints out reports pass to it by other procedures and forms.
Dim PrintPreview As New ReportViewerForm
Try
If mySQLFormula <> "" Then myReportFileName.RecordSelectionFormula = mySQLFormula
PrintPreview.CrystalReportViewer.ReportSource = myReportFileName
PrintPreview.ShowDialog()
Catch ex As CrystalDecisions.CrystalReports.Engine.EngineException
MsgBox("A error was generated by the Crystal Reports engine. Error details: " & ex.Message, MsgBoxStyle.Critical, "Engine Error")
Finally
myReportFileName = Nothing
PrintPreview = Nothing
End Try
End Sub

Then when I need to print a report, for example my Log report (called LogEntryReport.rpt) I call it like this:

PrintReports(New LogEntryReport, "{qLogEntryReport.LogIdNumber} = " & CurrentLogId)

passing my report as a new ReportDocument and my sql command (which is optional for those reports that don't need it). Now I can have one routine that will display reports and call it from anywhere passing the report name. Works dandy and the reports are embedded in the app...less clutter.

Hopefully this helps someone and its not a waste of bandwidth.

-Allan.

how do i do this using Crystal Reports IX?

Hi all,

Please help me with my CR IX problems...I am new to crystal reports.

I have a table that consist of:
[City, Program, Type of Payment, Total # of participants]
example:
[LA, Aerobics, Cash, 100
LA, Aerobics, Credit, 120
LA, Dance, Cash, 50
LA, Dance, Credit, 20]

I wanted to do a report so that it will show the folowing datas:
[City, $Aerobics, $Dance, CR Aerobics, CR Dance, Total]
example:
[LA, # of participants in Aerobics that pay by cash, # of participants in Dance that pay by cash, # of participants in Aerobics that pay by credit card, # of participants in Dance that pay by credit card, Total # of participants in LA]

Thank you very much in advance for your answer and time.
Have a wonderful day!Do a group by 'City' field. Create running total for #of participants and
select 'Use a formula' option. Use the following formula :

if Program="Aerobics" and Type of Payment="Cash" then
true
else
false;

This will give you the total # of participants with Cash payment for Aerobics. Similar running totals for Credit card payment etc.

Hope this helps!
Rashmi

Sunday, February 19, 2012

How do I disable the help (?) link?

I'm using RS 2000 SP2 and have implemented a custom security extension.
The reports are embedded in our web application. Everything works
great except for the help link, which when clicked, launches an IE
window that displays the login page. Is there any way to disable,
hide, or change the href for this link?
TimCan someone please just tell me it's not possible so that I can stop
asking?

How do I deploy XSL file to Reports Server?

Hi:
I have developed a report that uses and XSLT file to format data found in a
CLOB field from our DB2 database. It works just great in Report Designer,
but when I deploy to the Reporting Server, it does not render the data from
the CLOB but rather an error.
The value of the text box is
= Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
I have created a C:\XSL directory on the Reports Server and placed a copy of
the ClearingHouseException.xsl file in it, but it just isn't working.
What am I doing wrong?
Thanks.
Bruce.If this works from development and not from production then you are hitting
a security problem. Could be one of two places, either the rights on the
c:\xsl directory OR (more likely) the rights for the code you are executing.
By default RS limits what code can do to only safe things, which accessing
the file system is definitely not safe. I suggest reading up on what you
would have to do if you were using your own assemblies (this isn't your own
assembly but it is the same thing). You might need to have thise be your own
assembly instead of code behind in order to get the proper security rights
but I am not sure. I feel pretty confident it is one of these two things.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:8C18EF57-E9AC-47D8-9081-B761360C5F8E@.microsoft.com...
> Hi:
> I have developed a report that uses and XSLT file to format data found in
a
> CLOB field from our DB2 database. It works just great in Report Designer,
> but when I deploy to the Reporting Server, it does not render the data
from
> the CLOB but rather an error.
> The value of the text box is
> = Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
> I have created a C:\XSL directory on the Reports Server and placed a copy
of
> the ClearingHouseException.xsl file in it, but it just isn't working.
> What am I doing wrong?
> Thanks.
> Bruce.|||Hi Bruce:
Thanks for the reply, although the "assembly" approach seems like a lot of
work to get right...
I did read in one book on RS that you could upload the XSL file to the same
folder in Report Manager and it could be accessed from there. Of course, it
didn't say how the report would call the file, ie: the normal C:\ path
wouldn't work. Is it possible to point to the file in the Report Manger
folder?
Thanks again.
Bruce.
"Bruce L-C [MVP]" wrote:
> If this works from development and not from production then you are hitting
> a security problem. Could be one of two places, either the rights on the
> c:\xsl directory OR (more likely) the rights for the code you are executing.
> By default RS limits what code can do to only safe things, which accessing
> the file system is definitely not safe. I suggest reading up on what you
> would have to do if you were using your own assemblies (this isn't your own
> assembly but it is the same thing). You might need to have thise be your own
> assembly instead of code behind in order to get the proper security rights
> but I am not sure. I feel pretty confident it is one of these two things.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
> in message news:8C18EF57-E9AC-47D8-9081-B761360C5F8E@.microsoft.com...
> > Hi:
> >
> > I have developed a report that uses and XSLT file to format data found in
> a
> > CLOB field from our DB2 database. It works just great in Report Designer,
> > but when I deploy to the Reporting Server, it does not render the data
> from
> > the CLOB but rather an error.
> >
> > The value of the text box is
> >
> > = Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
> >
> > I have created a C:\XSL directory on the Reports Server and placed a copy
> of
> > the ClearingHouseException.xsl file in it, but it just isn't working.
> >
> > What am I doing wrong?
> >
> > Thanks.
> >
> > Bruce.
>
>|||Sorry, I haven't tried to get to a file. Since I haven't had to mess with
the security stuff for assemblies I can't help too much there (or with
getting to the file). It does make sense for security reasons that they
would not by default allow access to the hard drive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:EE73655D-F3D0-4525-A03B-04DE703FB560@.microsoft.com...
> Hi Bruce:
> Thanks for the reply, although the "assembly" approach seems like a lot of
> work to get right...
> I did read in one book on RS that you could upload the XSL file to the
> same
> folder in Report Manager and it could be accessed from there. Of course,
> it
> didn't say how the report would call the file, ie: the normal C:\ path
> wouldn't work. Is it possible to point to the file in the Report Manger
> folder?
> Thanks again.
> Bruce.
> "Bruce L-C [MVP]" wrote:
>> If this works from development and not from production then you are
>> hitting
>> a security problem. Could be one of two places, either the rights on the
>> c:\xsl directory OR (more likely) the rights for the code you are
>> executing.
>> By default RS limits what code can do to only safe things, which
>> accessing
>> the file system is definitely not safe. I suggest reading up on what you
>> would have to do if you were using your own assemblies (this isn't your
>> own
>> assembly but it is the same thing). You might need to have thise be your
>> own
>> assembly instead of code behind in order to get the proper security
>> rights
>> but I am not sure. I feel pretty confident it is one of these two things.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com>
>> wrote
>> in message news:8C18EF57-E9AC-47D8-9081-B761360C5F8E@.microsoft.com...
>> > Hi:
>> >
>> > I have developed a report that uses and XSLT file to format data found
>> > in
>> a
>> > CLOB field from our DB2 database. It works just great in Report
>> > Designer,
>> > but when I deploy to the Reporting Server, it does not render the data
>> from
>> > the CLOB but rather an error.
>> >
>> > The value of the text box is
>> >
>> > = Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
>> >
>> > I have created a C:\XSL directory on the Reports Server and placed a
>> > copy
>> of
>> > the ClearingHouseException.xsl file in it, but it just isn't working.
>> >
>> > What am I doing wrong?
>> >
>> > Thanks.
>> >
>> > Bruce.
>>

Friday, March 30, 2012

How do I migrate reports to new server

Wednesday, March 28, 2012

how do I make 30 sec running query (select c1 sum(x) from t1 where c1 > 1000 group by c1) run

How do I link two different field lengths with the same data

How do I know the version of Crystal Reports that I am using ??

Friday, March 23, 2012

How Do I Increase File Upload Size?

Monday, March 19, 2012

How do i get rid of page breaks for HTML view...

How Do I Get Rid of Blank Pages in Reports?

Monday, March 12, 2012

How do I format mailing address fields for display in reports

Wednesday, March 7, 2012

How do i find all subscriptions

How do i find all subscriptions

How do I enable 2005''s taskpad ?

How do I enable 2005''s taskpad ?

How do I enable 2005''s taskpad ?

How do I enable 2005''s taskpad ?

Friday, February 24, 2012

How do I enable 2005''s taskpad ?

How do I enable 2005's taskpad ?

How do I embed and retrieve Crystal Reports from my app

how do i do this using Crystal Reports IX?

Sunday, February 19, 2012

How do I disable the help (?) link?

How do I deploy XSL file to Reports Server?

display SQL Server

Blog Archive

About Me