Extra columns in Excel with SP2

I would like to upgrade to Reporting Services service pack 2 so that I can
specify report colours and specify to move the header into Excel's header.
However when we upgrade to Service Pack 2 it causes extra totals columns to
appear at the end of the months in the generated reports. These columns
could possibly acceptable if they were titled as Total however they are not.
Can anybody make any suggestions in how to generate the report in excel
without the additional columns appearing in the excel output? Many thanks.Does anybody have any ideas about how to do this - any guesses even would be
appreciated. Thanks
"Nicola Jones" wrote:
> I would like to upgrade to Reporting Services service pack 2 so that I can
> specify report colours and specify to move the header into Excel's header.
> However when we upgrade to Service Pack 2 it causes extra totals columns to
> appear at the end of the months in the generated reports. These columns
> could possibly acceptable if they were titled as Total however they are not.
> Can anybody make any suggestions in how to generate the report in excel
> without the additional columns appearing in the excel output? Many thanks.

External web service as datasource for Reporting Service report

Hello,
I would like to make some Reporting Services reports from a datasource
that is a web service. Is this possible? I am using SSRS 2005. Any
suggestions or directions would be greatly appreciated.
photogulliverIt is possible but I haven't done it. Read up in Books On Line on XML.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/d23408e5-e65b-4f49-a98f-234454d5d267.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<ehausig@.yahoo.com> wrote in message
news:1141931933.379723.119120@.e56g2000cwe.googlegroups.com...
> Hello,
> I would like to make some Reporting Services reports from a datasource
> that is a web service. Is this possible? I am using SSRS 2005. Any
> suggestions or directions would be greatly appreciated.
> photogulliver
>|||Yes. it is possible.
however, there are serious limitations on this approach. see other
postings in this group by me and Teo's responses to my postings.
1. the XML returned by the web service may not be readily consumable by
SSRS.
2. the query syntax to consume the XML is xpath like, but it is neither
xpath not xquery
3. security is a big concern. webservices consumption is non-secure
however, you can write your own custom data processing extension which
can overcome the above limitations.
thanks
- jasthi|||Ok, thanks for the feedback!
Eric

External Activation and Receive Timeout

I have a set of service broker services setup that rely on external activation to process messages. I'm using the GotDotNet ExternalActivator, and it launches console applications that do the actual retrieval from the queues. The console applications are written to run continuously to avoid the cost of starting up .NET based console apps over and over again.

I am observing very odd timing behavior. With the receive queues empty and the external activator configured to run a minimum and maximum of 5 instances, I observe in SQL Profiler that most of the receive operations finish in about the same amount of time as my WAITFOR command in my receive stored procedure. However, there is usually one receive command that consistently takes upwards of 30 seconds and often causes sql timeout exceptions to be thrown. I know that I could code around this, but I wasn't really expecting this behavior.

Does anyone have any thoughts on why it might be occurring? I would have expected to routinely see my receive operations taking 15 seconds, give or take, especially when the queue is empty. Also, I have observed this behaviour on both SQL 2k5 Express and Dev Editions, so I don't think it's a version thing.

The stored procedure I am using to do the receive is:

Create PROCEDURE [dbo].[P_RTD_MessageBase_Receive]

@.receiving_queue_name varchar(255),

@.receiving_time_out int

AS

Declare @.receiveQuery nvarchar(300)

Set @.receiveQuery = 'WAITFOR(RECEIVE * FROM [dbo].['+ @.receiving_queue_name +']),

TIMEOUT ' + cast (@.receiving_time_out as varchar)

Execute sp_executeSql @.receiveQuery

Using SQL profiler can you find out if the actual command being executed has the correct timeout?

Some questions to help me understand why you made some design decisions:

1. Why do the apps run forever instead of shutting down after a long period of inactivity? Sure I understand you don't want yout .NET programs being launched and torn down over and over but you could use a longer timeout to avoid this. (Also, you should probably ngen your assemblies to improve startup time).

2. Why are you setting minimum instances to 5? Most people want minimum to be 0 so that there are no instances running when the queue is empty. Do all instances execute the same code path or are you doing something special here?

|||

I have confirmed that the actual command that seems to take much longer than the timeout has the correct timeout. IOW, I issue a receive with a 15000 ms timeout, and it takes about 28000 ms even though the queue is completely empty. The other four receives seems to finish and around 15000 ms consistently.

I don't have a great reason for the apps to run forever, except to avoid startup time. I could implement a longer timeout, but I am seeking to minimize the amount of time it takes to process a single message. The nature of the system is that messages drip in during some periods of the day, and I don't really want those messages to have to wait any longer than when messages are regularly coming in. nGen could help, but I still think my approach is the fastest.

I was setting the minimum instances to 5 in order to test the apps to make sure they weren't leaking any memory and that they did indeed run indefinitely.

How might these three issues affect the timeout?

|||

Is it possible for you to capture the trace using SQL Profiler with all Broker events as well as the following events enabled?

- RPC:Starting
- RPC:Completed
- SP:StmtStarting
- SP:StmtCompleted
- SP:Starting
- SP:Completed
- SQL:StmtStarting
- SQL:StmtCompleted
- SQL:BatchStarting
- SQL:BatchCompleted

You could e-mail that to rushi (dot) desai (at) microsoft (dot) com.

My questions were unrelated to the problem you are facing. I was intrigued by the atypical setup and so I was curious why you were using this setup.

Thanks,

Rushi

|||Hey Rushi - I just sent the trace file to you...|||

Rushi has come back to me and said that this is a bug. I have posted a bug report at:

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

Extent of FullText Population

Is there a means to determine to what extent an existing
SQL table has been populated with the FullText indexing
service .... so to try to assess how long an
incremental population may take ?
Thanks
PhilipPhilip,
Yes, you can use one or more the system metadata, such as
FullTextCatalogProperty('<FT_Catalog_Name>', 'populatestatus') to monitor FT
Populations.
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext
Regards,
John
"Philip" <plippard@.nc.rr.com> wrote in message
news:02b301c34f27$15108970$a301280a@.phx.gbl...
> Is there a means to determine to what extent an existing
> SQL table has been populated with the FullText indexing
> service .... so to try to assess how long an
> incremental population may take ?
> Thanks
> Philip

Extended Stored Procedure Permissions Problem

I have devloped a .NET windows service to manage
server-side traces for an ongoing database auditing effort
here at work. The service creates traces, rolls them over
periodically, and loads the trace file contents into a
reporting instance. This service logs into each audited
instance using its a domain user account. The service must
call the sp_trace_create, sp_setstatus, sp_setevent, and
sp_setfilter extented stored procedures. Addtionally, it
must access the fn_trace_getinfo and fn_trace_gettable
functions.
The service works very well when the domain account is a
member of the sysadmin role in an audited instances. The
service fails to work when it is just a regular db user
with EXECUTE permissions on the above stored procedures.
The service also does not work when configured as a dbo in
the master database of the audited instance.
I am guessing that this issue arises from the trace
procedures being extended stored procedures and perhaps
there is some underlying OS permission issue. Can anyone
offer some guidance that can help me avoid running this
service as a sysadmin?
Thanks in advance,
TimIn SQL2000 you have to be a sysadmin to run trace procs. This is a change
from SQL7 where you could grant exec on the extended stored procedures
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tim Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:dce201c40ad0$2f8e3260$a101280a@.phx.gbl...
> I have devloped a .NET windows service to manage
> server-side traces for an ongoing database auditing effort
> here at work. The service creates traces, rolls them over
> periodically, and loads the trace file contents into a
> reporting instance. This service logs into each audited
> instance using its a domain user account. The service must
> call the sp_trace_create, sp_setstatus, sp_setevent, and
> sp_setfilter extented stored procedures. Addtionally, it
> must access the fn_trace_getinfo and fn_trace_gettable
> functions.
> The service works very well when the domain account is a
> member of the sysadmin role in an audited instances. The
> service fails to work when it is just a regular db user
> with EXECUTE permissions on the above stored procedures.
> The service also does not work when configured as a dbo in
> the master database of the audited instance.
> I am guessing that this issue arises from the trace
> procedures being extended stored procedures and perhaps
> there is some underlying OS permission issue. Can anyone
> offer some guidance that can help me avoid running this
> service as a sysadmin?
> Thanks in advance,
> Tim|||Thanks, Jasper.

>--Original Message--
>In SQL2000 you have to be a sysadmin to run trace procs.
This is a change
>from SQL7 where you could grant exec on the extended
stored procedures
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Tim Richardson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:dce201c40ad0$2f8e3260$a101280a@.phx.gbl...
>
>.
>

Express Installation

Hi,

I have tried to install the SQL 2005 Server Express and get the following message:

Your operating system does not meet service pack level requiements for this SQL server release. Install the service pack.

Can anyone tell me what service pack it will be and where I can get it.

hi,

please have a look at http://www.microsoft.com/sql/editions/express/sysreqs.mspx

regards

|||

What is the service pack of Windows where you are trying to install the SQLExpress?

Service packs can be obtained from MS download site.

Express Edition SP2 - How long to wait for complete installation?

Just curious if there's a reason why everytime I try to apply the Microsoft SQL Server 2005 Express Edition Service Pack 2 from Windows Update that it will sit at "Installing" and appear to do nothing (no hard drive activity, but the MSI and setup processes are chewing a little bit of CPU time). How long should I give this update to complete? I am running a 2-CPU dual-core Xeon 3.0GHz w/ 2GB of RAM setup, so I would have thought it would be completed very quickly. :(

Edit:

Disregard; I had to wait for a full 15 minutes! :OFifteen minutes isn't a 'unusual' amount of time. Depends upon what activity is occuring on the computer.|||I was surprised because nothing else was chewing CPU and Firefox was the only other application open besides IE7 running the Microsoft Update.

Exporting to PDF through web service produses blank pages

Hi all,
I am exporting to PDF through web service a report that contains matrix.
I get almost every second page a blank page.
The same report when exported to PDF by the report manager does not contain
blank pages.
Is it a bug, Is there any work around
Thanks,
YuvalNobody is going to answer your question. I've been asking it for days. I
emailed all those Microsoft bloggers. I posted here. Nothing. I did
enough research that I am conviced Microsoft knows about the issue.
Note they claim you can find known bugs here:
http://msdn.microsoft.com/bugs/
But you can only find bugs they want you to find (ie ones they know how to
fix). If you seach for reporting services, you will find 0 known bugs.
The problem is that the List containing the matrix is growing so much that
the report won't fit on one page. So it prints two pages. You can see this
by changing the background colour of the List that contains the matrix.
When you run it, you will find the coloured list extends onto the 'blank'
page.
I haven't seen a fix yet. You can shrink your matrix so that the list won't
grow onto the next page. But that's not usually an acceptable fix, because
it means leaving about a third of the page blank.
Seems to me there's no acceptable way to print a matrix report. This issue
affects the tiff, and I found a different issue in Excel.
Scott
"Yuval Rabinovitz" <yvlrb@.netvision.net.il> wrote in message
news:e66FusBkEHA.3848@.tk2msftngp13.phx.gbl...
> Hi all,
> I am exporting to PDF through web service a report that contains matrix.
> I get almost every second page a blank page.
> The same report when exported to PDF by the report manager does not
contain
> blank pages.
> Is it a bug, Is there any work around
> Thanks,
> Yuval
>|||I did eventaully get a reponse from Microsoft. Everyone was quite friendly,
but it took a while to find the person who knew the answer.
If you are not using subtotals in your matrix, you might try "KB article
875518 is FIX: Width of the list that contains a matrix grows unexpectedly
when exported to PDF." This hotfix (only available by calling support)
didn't solve my problem.
If you are using subtotals in your matrix (like I was), this is the answer.
This response was from Sarah Parra:
"
The problem is that the list always extends a certain amount to the right
and bottom of the matrix, and the amount that it extends is based directly
on how wide (for column totals) or tall (for row totals) the total textbox
is. If you look at your report in the designer, in the bottom right corner,
under the totals boxes, there is a gray/brown empty space. That space is
effectively how much extra list you will see in the final output. We take
the size of the matrix to be the actual size that you see, minus the totals
boxes. That means that the list effectively looks like it is that much
bigger than the matrix (instead of being the exact same size). When the
rendering happens, it then adds that much padding to the right and below the
actual matrix.
This is a bug as well, it's just a different bug from the one described in
the article/hotfix. :) This one does have a fairly simple workaround. You
need to resize the list to be the size of the matrix, minus the size of the
totals columns/rows. The designer will not allow you to size the list
smaller than the matrix (which includes the totals), but you can do this
manually in the rdl. The following are the instructions I sent to another
customer on a sample report he had provided, so the sizes and line number
will not match up for you:
(1) Find the size of the column total box. You can just click on it and look
at the Width property. It's 0.875in.
(2) In the Solution Explorer, right click the .rdl file and select View
Code. In the code, find the Width definition for the List itself. You'll see
it on line 78, and it looks like this : <Width>5.875in</Width>. Change that
to that value, minus the size of the total column, .875. So just make it
5.0in, or you can make it a little bigger if you want some extra space
displayed to the right of the matrix itself. In the designer at this point,
it will look like the Total box is gone, but it's still there. You can do
the same thing for the row totals if you want. You will probably want to do
this, because if you have a situation where the matrix should end at the
very bottom of the page, you may still get an extra page if the extended
list height would force it.
(3) Preview and render the report. You should no longer see any blank pages.
"
"Scott Stonehouse" <scott@.mphec.ca> wrote in message
news:%23N3MVjFkEHA.2812@.tk2msftngp13.phx.gbl...
> Nobody is going to answer your question. I've been asking it for days. I
> emailed all those Microsoft bloggers. I posted here. Nothing. I did
> enough research that I am conviced Microsoft knows about the issue.
> Note they claim you can find known bugs here:
> http://msdn.microsoft.com/bugs/
> But you can only find bugs they want you to find (ie ones they know how to
> fix). If you seach for reporting services, you will find 0 known bugs.
> The problem is that the List containing the matrix is growing so much that
> the report won't fit on one page. So it prints two pages. You can see
this
> by changing the background colour of the List that contains the matrix.
> When you run it, you will find the coloured list extends onto the 'blank'
> page.
> I haven't seen a fix yet. You can shrink your matrix so that the list
won't
> grow onto the next page. But that's not usually an acceptable fix,
because
> it means leaving about a third of the page blank.
> Seems to me there's no acceptable way to print a matrix report. This
issue
> affects the tiff, and I found a different issue in Excel.
> Scott
>
> "Yuval Rabinovitz" <yvlrb@.netvision.net.il> wrote in message
> news:e66FusBkEHA.3848@.tk2msftngp13.phx.gbl...
> > Hi all,
> >
> > I am exporting to PDF through web service a report that contains matrix.
> > I get almost every second page a blank page.
> > The same report when exported to PDF by the report manager does not
> contain
> > blank pages.
> >
> > Is it a bug, Is there any work around
> >
> > Thanks,
> > Yuval
> >
> >
>