Hot answers tagged ssrs
7
You could create a job that checks the msdb.dbo.sysjobhistory table every minute (or however frequently you want). You might want to implement a queue table so you only ever send the message for any single instance failure once.
USE msdb;
GO
CREATE TABLE dbo.ReportServerJob_FailQueue
(
job_id UNIQUEIDENTIFIER,
run_date INT,
run_time INT, -- horrible ...
6
Okay, I got it. Found this link which helped: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/581c7068-0abe-49f9-a1a5-93e94f472641/
I distilled that information to this query:
select
Catalog.name,
cat1.Name datasource
from
Catalog
join DataSource
on Catalog.ItemID = DataSource.ItemID
join Catalog cat1
on ...
6
Once you're ready to start building reports on that data an alternative (or supplemental) approach would be to pre-aggregate your data. If the reports don't need the same level of detail as the table then it would be a good idea to do periodic loads to some snapshot tables or an SSAS cube that the reports would directly connect to.
Directly reporting off ...
6
There are a couple of courses of action for you:
If you're friendly with your Windows Admin, I'd thoughtfully think out some AD Groups and then present them to the admin and ask them to be created (in whatever workflow you have).
From there you can assign permissions on the individual folders by these groups. Click on the folder, then click on Properties.
...
5
You mentioned that you have the table (technically clustered index) partitioned.
Does your query use the clustered key(s) as part of the search predicate? If not, you are going to get scans, as opposed to seeks, against the partitioned index.
Do you have any nonclustered indexes? Are they also partition-aligned?
Have you investigated your query ...
5
Have you considered READ_COMMITTED_SNAPSHOT row versioning for the database?
Kim Tripp has a good article about it at http://msdn.microsoft.com/en-us/library/ms345124%28v=sql.90%29.aspx
READ_COMMITTED_SNAPSHOT allows better functionality than WITH (NOLOCK) in that it provides absolute point-in-time consistency for long-running aggregations or queries ...
4
I'd say less as an initial starting point but more as an indispensable resource, there are some great blogs out there:
SSIS Junkie/Jamie Thomson
SSIS Team Blog
4
Reporting Services can be demanding in terms of memory and CPU, so it often makes sense to separate it from the database server element of SQL Server - obviously this isn't cheap in terms of licencing. One thing to bear in mind is that the metadata for SSRS is stored in a SQL database, but this can be on a different box. Where SSRS gets the business data ...
4
We actually ran into a similar issue at StackOverflow and Kyle blogged about it:
http://blog.serverfault.com/2011/03/16/views-of-the-same-problem-network-admin-dba-and-developer/
The problem can be a number of things:
Queries bringing back too much data (like select * from a wide table with a lot of XML or binary fields)
Client-side apps processing data ...
4
Why not change permissions so that folk can't deploy datasources to anything other than this folder?
So, remove "Manage data sources" from all folders except for /Data Sources. This can be done at root level and then set custom permissions on /Data Sources
You may need to setup a custom role for this if you can't change the existing ones.
4
I don't have 2008 to look at but do you have access to the RDL files that were published to the server? I can't recall if those are plain old XML files, but I think if you have those, it's probably a convenient way - you could parse them with Linq to XML, or XSLT.
The schema is documented at:
http://msdn.microsoft.com/library/dd297486%28SQL.100%29.aspx
4
Quick answers:
Works, as you noted.
Works, as you noted.
This does not appear to work. I didn't see an option off-hand and whenever that question gets asked, the answer always comes back to setting the isolation level in the stored procedure.
I wouldn't believe so. SSRS is at a higher abstraction layer than the database engine, so in some sense, it ...
3
Unless I'm completely misunderstanding your question, then the below T-SQL should do it (please let me know if this isn't what you're looking for). It utilizes bitwise operators to pull out the bit masks for the days:
-- <TEST DATA>
create table DayTable
(
id int identity(1, 1) not null,
DayMask tinyint not null
)
go
insert into DayTable
...
3
Check your setting for parallelism.
Check your volume set up on the SAN, know how your volumes map to spindles.
How's your tempdb set up?
Don't forget to check to obvious stuff too, make sure your NIC's are giving you the speed you think they should, I've lost track of the times I've seen GBit cards set to auto that negotiated 100MB speed. And your HBA ...
3
With RB (and sufficient privilege), users can save reports straight to the Report Manager environment. At that point, they're deployed. But if you want to have them save those reports to a Dev/Test box, and want to push them live, there's a few options.
One easy option is to move reports into a known folder 'For Deployment'. You can then have a script which ...
3
The two ideas are unrelated
A linked server is for SQL Server to other database server connections at the database level.
In SSRS you have DataSets which point to a database
There are some cases where the DataSet calls a SQL Server database that uses a linked server: but I suspect you aren't that far yet.
If you think you need to use a "linked server" ...
3
Well... I figured it out!
Here's what I did:
Deploy all the reports from the BIDS project to a main folder, say Source Reports.
On the ReportServer, create site-specific folders in another folder, say Site-Specific Reports. Also in that folder, create a Master Reports folder.
Copy the Master reports to the Master Reports folder.
For each site report, ...
3
Hi I am afraid that this is not possible in 2008. You can workaround it though by implementing the logic in the dataset itself or you can create sub-reports to achieve what you are after.
However, if you have the ability to use a 2008R2 instance you can indeed do what you are asking by using the Lookup functions. Here is more information on it:
LOOKUP:
...
2
Depending on what type/volume of data you are reporting on, SSAS can possibly make it easier. I would not take that as a blanket statement. The decision to generate reports off of a relational or dimensional database should be made on a case by case basis.
Keep in mind that the development required to build an OLAP database around a subject area is far more ...
2
Probably the easiest way would be to use a drill through report. Add an action to the row group that links to a new report. Duplicate the layout of the new report, but implement a filter on the dataset of interest.
Pass the row group value ([SQLFieldName]) to a parameter of the report. If you use a multivalue variable with an IN statement in the SQL, ...
2
Re-run through the database configuration part to select the same database and it will re-issue the grants to make sure the permissions are in place, but make sure the account you run the wizard with has sysadmin access to the instance. If it's 2005 you can generate the grant script but it's kind of buggy.
2
If your query is returning a lot of rows across a slow WAN link then you might be seeing a slowdown due to network traffic. I have that class of problem where I'm working now.
If you report server is on the same fast LAN as the database server it would be quicker under these circumstances. This might explain the difference in performance.
Also, if you ...
2
Since you cannot create a function sproc you could join to an in memory lookup table :)
SELECT bin
from
(
select 0 as dec, '0000000' as bin
UNION
select 1 as dec, '0000001' as bin
UNION
select 2 as dec, '0000010' as bin
UNION
select 3 as dec, '0000011' as bin
UNION
select 4 as dec, '0000100' as bin
UNION
select 5 as dec, '0000101' as bin
UNION
...
2
Not out of the box as far as I am aware, and if you need to distribute reports to external parties this is a bad idea as they will all have to install third party encryption software (incurring licensing and support costs). It will also put your help desk people into the job of babysitting all the users as they forget their keys.
You're much better off ...
2
The easiest way to do it is to set up a Reply email: address when you you are configuring database mail. This will send the bounce back messages to that email account.
UPDATE:
The bounce-back for invalid email addresses will go to the FROM address. The Reply-To address is used only by an email program when a user tries to reply to the message. Change ...
2
I think I found a work around for you.
Set up a new dataset to pull back the language value (let's say language_dataset into column language_value).
Create a parameter, let's say called language. Set it to internal and set its default value -> Get values from a query and use the dataset and value you just set up.
In the Report properties, Language -> ...
2
Neither of these components are cluster aware and they will be separate from the database install, so you would just run the SQL 2008 R2 Add/Remove programs from the control panel of each cluster node and select uninstall for both of these components. A reboot may be required to take effect.
2
You can do this. You can create a single report that has multiple data sources (one per environment). Add the data sets required. Recreate the graphs (you may be able to leverage some cut-and-paste). In order to format them nicely, you will need to place the graphs into the cells of a matrix.
You could also build a single report that links to other ...
2
I wouldn't worry about setting the time component. Just write your query like this instead:
WHERE SomeColumn >= @StartDate
AND SomeColumn < DATEADD(Day, 1, @EndDate)
You have @EndDate = Today() which by default does not have a time component (or rather it's already 00:00:00). Adding a whole day to that gets you tomorrow at midnight. So the ...
2
If the common column(s) between two data sets are going to be used as the report parameters, then joing the data sets are automatically maintained via parameter. In my case, the common columns were in fact the reports parameters , so by assigning these parameters to each data set definition individually, the link between the data sets are created. Simple!
...
Only top voted, non community-wiki answers of a minimum length are eligible
