Hi Gurus
Currently we are using SQL Server Reporting Services on top of our client data warehouse. Almost all the reports deployed on the report production server are executing Stored Procedures (behind the scene).
Can anyone tell me how can I query Report Server database to get a list of reports and the stored procedures being executed by these reports?
Early Thanks,
Salman Shehbaz.
Database Administrator
Xavor Corp.
92-321-440-0797
Hi ALL Currently we are using SQL Server Reporting Services on top of our client data warehouse. Almost all the reports deployed on the report production server are executing Stored Procedures (behind the scene). Can anyone tell me how can I query Report Server database to get a list of reports and the stored procedures being executed by these reports? Early Thanks, Salman Shehbaz. Database Administrator Xavor Corp. 92-321-440-0797
I have created a linked report in which I have two screen: First Screen: Clicking on the bar of particular country the details of that particular country should be visible (the second screen)
Second Screen: When I clicked on a bar. All the count values are very well correct. In first chart Count of values under one bar "Pending decision"(yellow) was 337 for a country and in second screen its 337 again(shown in light green bar). <!--[if !vml]--><!--[endif]--> Problem: My problem is to make it sure that the colors that the first screen is having for particular status (as shown in the legend) should remain same in second screen also. For example for a status say "pending decision" the chart is using yellow color, so in screen two as well it must be shown with yellow color and not with lany other color. Can anyone help me in this context. Is there any way to customize colors used by bar chart. Please note that the question is not about using appropriate color scheme(palette) the question is how to "capture/ Re-use/ customize/ pass as a parameter" the colors used by a bar chart in SQL Server 2005 Reporting Services.
I have a process that stores a machine status every 2 minutes in a bit field of an SQL database. Running = True, Stopped = False. I am trying to build a report that shows the percent run time every hour. This information would be the count of Trues divided by the total count for each hour. I can select and group the data on the hour but I can either count() the total number of readings or count() the number of readings where the status is true. How do I collect both pieces of information in the same data collection?
SELECT TOP (100) Max(Time) As Time_M, Count(Running) As Count_T FROM Compact10 Group by DatePart(mm,Time), DatePart(dd,Time), DatePart(hh,time) Order by DatePart(mm,Time) desc, DatePart(dd,Time) desc, DatePart(hh,time) desc
Leon writes "I am trying to connect to a W2K3 Server's 2005 Reporting Server through my laptop's (WXP Pro SP2) SQL Server Management Studio.
I turned Port 1433 on in the server's Windows Firewall and was able to connect to the server's database engine from my laptop, but I have to turn Windows Firewall on the server off completely to be able to connect to the Reporting Server.
I have tried all the ports listed for SQL Server in the Microsoft documentation (TCP and UDP), but they seem to have no effect.
Can anyone tell me which port or ports I need to turn on in the server's firewall so that I can access Reporting Services through my laptop's SSMS?
In VS2008 SSRS, when creating a dataset, only the name of the stored procedure was required. When populating the dataset, VS prompted for the parameters, whether or not there were any. In VS2010, the name of the stored procedure is not enough if there are parameters.When creating a dataset in Visual Studio 2010 for databases and stored procedures in SQL 2012, I must provide (what should be) run-time parameters to obtain a field list and then to preview the report.
So the embedded dataset looks like this (wherein the only parameter is a password): exec dbo.spu_procedure1 @PW='999'.This works well and as expected during report design and development. But 999 must be provided at run-time by the requester, and not in the dataset definition. If I remove the parameter in the dataset's exec string before deploying the report, the field list is cleared and the report is unusable. So I cannot overcome what seems a contradiction: necessary to obtain a field list, but "yet-to-be-provided" when the user selects the report.
By keeping @PW='999' in the dataset specification, the user cannot override the parameter and automatically has a password to view the report.
I have a question about granting enough rights to allow a non admin user to start and stop a sql server service via the SQL Server Management Studio by right clicking on the server node.
I have changed the acl's of the SQL Server service security and gave the user rights to start and stop the service. This does allow them to connect remotly to the server using computer manager and they successifully start and stop the service. But, in SQL Server Management Studio the option still does not show up, unless he is a admin of the server.
Does anyone know what other security settings I need to address for the start and stop to show up when I right click on the server node?
I am trying to migrate my reports from SQL server 2000 reporting services 32bit to 2005 64bit. I am following the migration steps that MS specified. Restored my Reportserver and ReportserverTempDB databases Then I was using the configure Report services to upgrade these databases but I always end up getting the follwoing exception when I run the upgrade on the "Database Setup" configuration for 'ReportServerTempDB' database System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database 'ReportServerTempDBTempDB'. No entry found with that name. Make sure that the name is entered correctly. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script) at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
It's driving me crazy, why is it looking for 'ReportServerTempDBTempDB' in the catalog instead of 'ReportServerTempDB'? Is it possible to migrate from 32bit to 64bit?
I started my Reporting Server and if I use the Internet Explorer as a Administrator everything works fine.But if I don't open it as an admin it calls "rsAccess Denied The user [...] does not have the permission...".Problem is that I want to run it in an SAP Program with an HTML Viewer. how I can get those Permissions ?By the way somehow I'm not able to create or change roles in the Microsoft SQL Server Management Studio.
I'm attempting to obtain a cost effective solution for my existing customers to develop reports on their SQL 2000 Server installations using their Reporting Services 2000. With products like Visual Basic.NET 2003 becoming almost impossible to obtain, I have at least one customer who is running into a dead end.
One option possibly is the SQL Express with Advanced Services download, which has Reporting Services. My questions are as follows:
Can the report designer component of SQL Express Reporting Services be configured to connect to an external database (which would happen to be a SQL 2000 database) to establish its datasets? Does the resultant designed report end up in an RDL file? If the customer published this report via the Reporting Services 2000 Report Manager, would the report be able to run?
Sorry for asking a question like this that I could probably answer on my own, but my customer needed this answer yesterday. I have scoured the web and microsoft sites - and posted a question on the official SQL Reporting Services cateogy ... in an attempt to answer the basic question of how to design reports for Reporting Services 2000 in the absence of Visual Basic.NET 2003 (or other .NET 2003 tools) with no success.
I am trying to learn Reporting Services using the title "MS SQL Server 2005 Reporting Services Step by Step" by Stacia Misner and Hitachi Consulting, published in 2006. I am experiencing problems with some of the exercises. I got as far as Chapter 4 when I followed directions to create a SQL statement to define a query string for a dataset. Pg 80:
select * from vProductProfitability
where year = 2003 and
MonthNumberOfYear = 1
The view vProductProfitability does not exist in the tutorial database that came with the book, rs2005sbsDW. The result of this query is the basis for the entire chapter on developing basic reports and I'm being denied a learning opportunity because the view does not exist. In short, I'm stuck.
I have tried to find somewhere at Microsoft to place this question and get some answers so I can continue thru the tutorial. To no avail. Does anyone have any suggestions?
BTW, the solution that came in the CD is also wrong because the query noted above is also in the solution.
Hi, I need software that will write a report on the following items that I can have prepared to show our auditors. Any suggestions ??? OUr databases are SQL*Server 2000, SP 3a / SP4
1) database consistency checking 2) re-indexing 3) shrinking 4) allocation of growth boundaries 5) backup routines 6) disk space 7) table space 8) processing speed
I want to create a web browser based MIS suite to use our data held onSQL 7. We have Dreamweaver and Crystal Reports available but I want toknow if these are suitable or if there are any recommendations forsomething I can pick up quickly.It will be simple reports based on complex queries (I can handle mostof this in SQL 7), don't need fancy graphs, just plain boringfinancial reports are what I need to produce.Obvious things I need to create are combo boxes and list boxes forselections. My background is more Delphi/VB and SQL than web basedstuff, so please let me know any recommendations or good places tostart looking.ThanksRyan
I work in a big project and we will begin in using reporting services as the base technique for reports and I will be responsible for this part. but I have a problem I will discuss in the following:
Currently: We use currently devexpress reports and we have 2 languages(Arabic and English). the data in tables saved in two ways (Arabic and English). when the end user change the language of the web site the report data language changed when run it.
Example:
we have table with (ID, NAME_AR, NAME_EN, JobTitle_AR, JobTitle_EN). designed report will display(ID, NAME_EN, JobTitle_EN) . but the end user change the language of the system the report will
How do you create a dynamic column in reporting services? On the report I created I've a field call "Address 2', this field doesn't display data all the time. I wan this field to only display when there is data present.
I have to find out how to use the Reporting services in SQL Server 2000. Can any one expert in this field give some sort of direction. Any kind of resource on SQL server reporting will help.
I managed to install Reporting services ok on a laptop running XP Pro. However I have run into all sorts of problems trying to install it on a Laptop running Vista Business with II7.0
When I tried browsing the reports folder (by using the url: localhost/reports), it could not resolve it.
I got a bt further by using the url: 127.0.0.1/reports. However having part displayed the page it gave me the following error: The underlying connection was closed. An unexpected error occurred on a send.
I googled this error and was told to check the Reporting Configuration tool.
When I did this I got the following error
There was an error while refreshing the reporting services configuration manager. The exception details are: A connection to the computer cannot be established
Details: Access denied
However I can connect to SQL Server via Management Studio
hi, I am new to sql server reporting services,in my case i am passing parameters from frontend(Asp.net). when i pass more than 30 parameters my report is showing parameters which is not a desired one even though i am making showparameterPrompts property to false.
can any one tell me how to pass more no of parameters to the Report.
I've come across what appears to be a bug in SSRS. If after reading this post you feel otherwise, please reply to this thread...
I am trying to use SSRS to print to a Dymo LabelWriter 400 printer. This is a specialized label printer that prints one label to a page. The label paper I am using 2-1/2 inches by 1-1/2 inches. The Dymo printer driver prints portait layout as being the 2-1/2 inches width and 1-1/2 inches height. I assume that is because the paper rolls through the printer that way and labels normally are printed with more width than height.
Other software programs I use to print to this printer handles it properly (Microsoft Word, Microsoft Office Document Imaging, Dymo Label software). However, SSRS seems to assume that portrait layout must mean paper height > width, as there is no explicity setting in SSRS to for page layout. The result is that no matter how I layout my SSRS report (e.g., switch width-height values, use tb-rl to rotate text), SSRS makes the final decision on whether it is portrait or lanscape. And in this case of the Dymo printer, it always sets it to the opposite of what it should be. Changing the printer settings (whether default or whether in print dialog) does not correct it.
Although assuming that portrait layout must mean paper height > width may seem to make logical sense, not every printer works that way; and other software programs (including Microsoft Office) do not make that assumption. It seems that SSRS and/or RDL spec should allow one to explicitly set portrait/landscape setting and not care about width-height ratio. It should be left to the printer and printer driver to decide.
Have other people come across this issue, either with Dymo printer or in other context? Were they able to find a workaround?
I have a relatively simple question about SQL Server Reporting Services.
I want to know if SQL Server "Reporting Services" are included as part of SQL Server in all flavors or editions? Do you ever have to buy Reporting Services separate or is it ever not included with SQL Server?
i have a server with SQL Server 2000 installed on it. Also IIS is installed with Executive Viewer on HTTPS (a program (www.temtec.com) which creates webreports of MSAS Cubes).
A list of services is started everytime i start the server. My question is very simple. Which services are needed, and which services are only taking memory but are of no use at all to have my above configuration work.
This is my list: Alerter - Started Automatically Application Expreience Lookup Service - Automatically Application Management - Manually Automatic Updates - Automatically COM+ Event System - Automatically Computer Browser - Automatically Cryptographic Services - Automatically DCOM Server Process Launcher - Automatically DHCP Client - Automatically Distributed Link Tracking Client - Automatically Distributed Transaction Coordinator - Automatically DNS Client - Automatically Error Reporting Service - Automatically Event Log - Automatically Executive Viewer Server - Automatically Help and Support - Automatically HTTP SSL - Manual IIS Admin Service - Automatically IPSEC Services - Automatically Logical Disk Management - Automatically Machine Debug Manager - Automatically Microsoft Search - Automatically MSSQLSERVER - Automatically MSSQLServerOLAPService - Automatically Net Logon - Automatically Network Connections - Manual Network Location Awareness (NLA) - Manual NT LM Security Support Provider - Manual Plug and Play - Automatically Print Spooler - Automatically Protected Storage - Automatically Remote Access Connection Manager - Manual Remote Procedure Call (RPC) - Automatically Remote Registry - Automatically Secondary Logon - Automatically Security Accounts Manager - Automatically Server - Automatically Shell Hardware Detection - Automatically SQLSERVERAGENT - Automatically System Event Notification - Automatically Task Scheduler - Automatically TCP/IP NetBIOS Helper - Automatically Telephony - Manual Terminal Services - Manual Windows Audio - Automatically Windows Management Instrumentation - Automatically Windows Time - Automatically Wireless Configuration - Automatically Workstation - Automatically World Wide Web Publishing Service - Automatically
these are the services which are Started... there is also a list of Services that is not started....
SQL Server Reporting Services 2005 Service would not Start on windows server 2003. Working great till yesterday and no changes were made to config file.
Here is the error in Event log
Event Type: Error Event Source: SQL Server Report Service Event Category: None Event ID: 0 Date: 12/13/2007 Time: 8:25:59 AM User: N/A Computer: XXXXXXXXXXX Description: Service cannot be started. Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information. at Microsoft.ReportingServices.Library.ServiceAppDomainController.Start(Boolean firstTime) at Microsoft.ReportingServices.NTService.ReportService.OnStart(String[] args) at System.ServiceProcess.ServiceBase.ServiceQueuedMainCallback(Object state) For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Here is i found in log files ReportingServicesService!resourceutilities!4!12/13/2007-08:25:59:: i INFO: Running on 0 physical processors, 1 logical processors ReportingServicesService!servicecontroller!4!12/13/2007-08:25:59:: e ERROR: Exception caught loading and setting code permissions policy level: System.Security.XmlSyntaxException: Invalid syntax on line 158. at System.Security.Util.Parser.GetRequiredSizes(TokenizerStream stream, Int32& index) at System.Security.Util.Parser.ParseContents() at System.Security.Util.Parser..ctor(Tokenizer t) at System.Security.SecurityManager.LoadPolicyLevelFromStringHelper(String str, String path, PolicyLevelType type) at System.Security.SecurityManager.LoadPolicyLevelFromString(String str, PolicyLevelType type) at Microsoft.ReportingServices.Library.ServiceController.SetAppDomainPolicy() ReportingServicesService!servicecontroller!4!12/13/2007-08:25:59:: e ERROR: Error Starting Service: System.Security.XmlSyntaxException: Invalid syntax on line 158. at System.Security.Util.Parser.GetRequiredSizes(TokenizerStream stream, Int32& index) at System.Security.Util.Parser.ParseContents() at System.Security.Util.Parser..ctor(Tokenizer t) at System.Security.SecurityManager.LoadPolicyLevelFromStringHelper(String str, String path, PolicyLevelType type) at System.Security.SecurityManager.LoadPolicyLevelFromString(String str, PolicyLevelType type) at Microsoft.ReportingServices.Library.ServiceController.SetAppDomainPolicy() at Microsoft.ReportingServices.Library.ServiceController.StartService(Boolean firstStart)
I have installed SQL Server 2000 Reporting Services with the default names for the virtual directories , (ReportServer and Reports). Is there any way i can change the name of these directories by running a script or some thing.
Also is there a way to use the Cascaded Style Sheet used in my ASP.NET application for the reports.
Hi, I just came across SQL Server Reporting Services and wondered when it came into the market. Is it something like Crystal Reports and Actuate Reports?? Is Microsoft contending into the Reporting Software market too??
I've installed SQL Server Reporting Services and designed few useful reports on local workstation, now I want to deploy those on the production reporting server.
I have the following questions:
1) We have some demo databases with the similar structure so the the same report would fit all the demos. Is there any way to change the connecting string for the report programmatically without recreating report each time ? May be there is a way to pass database name to the report (encrypted) ? Is there any way to
2) I'd like to be able to protect those reports from unauthorised users ? Is there any way of doing that ? Is there any way to set up security on the base of folder anf for the whole group of reports ?
We had someone leave work and he was responsible for the reports for our company. He used SQL 2000 w/ Reporting Services. The connection to the report server was tied to his userid and password. Since his account was disabled the website will not connect to the reporting database because it was tied to his account. Is there a way to reset the connection creditions? I looked on the server and I can't find the Reporting Services Admin Tool.
