Stored Procedures - An Introduction

Stored Procedure

A stored procedure is a set of TSQL statements that can be compiled as a single execution plan.

Properties

A stored procedure is registered at the server (in common language, resides in the server).

A stored procedure may or may not return output result set. Result set could be tabular or scalar value.

A stored procedure can return messages to the client; invoke DDL and DML statements and return output parameters.

A stored procedure may or may not have parameters.

A stored procedure can be used inside other stored procedures called nested procedures but cannot be used in user-defined functions.

Advantages

* Performance enhancement

Since the execution plan can be re-used for subsequent executions, performance can be greatly enhanced.

 

* Allows Modular Programming

Instead of re-writing all the logic every time required, it can be embedded into a stored procedure and can be executed in a single line. It improves the modularity of the code.

Any change to the logic can done at a single location (stored procedure) and it can reflect where ever it is called. A stored procedure can contain any amount of logic in it.

* Reduce network traffic

Network traffic is reduced when a single execution line is sent to the server instead of the huge code.

The execution plan created during the execution of a stored procedure can be re-used in the subsequent executions.

* Security Constraints

Stored procedures can have security attributes such as permissions, ownership etc. associated with them

***Users can be granted permission to execute a stored procedure without having to have direct permissions on the objects referenced in the procedure.

* Prevent SQL Injection Attacks

Parameterized stored procedures (stored procedures with parameters) can help to prevent SQL Injection attacks on the applications.

***SQL Injection means direct insertion of malicious code into the application through user-input variables. It can never be detected as long as no syntax error occurs.

Types

There are three types of stored procedures.

User-Defined - Transact-SQL and CLR Stored Procedures

Transact-SQL SPs are those to which all the above discussed points are applicable.

CLR stored procedures are actually a reference to MS .Net Framework Common Language Runtime that can take and return user-supplied parameters. It allows creating .Net assemblies in the database.

Extended Stored Procedures

 These are actually the DLLs that SQL Server can dynamically load and run. They are programmed using Extended Stored Procedure API. They allow us to create our own routines in programming language like C.

***This feature will be deprecated in the future versions of SQL Server 2008. So, do not use.

System Stored Procedures

 System stored procedures are used to perform many administrative activities in SQL Server. They are physically stored in "Resource" database and have a prefix "sp_".

Syntax

CREATE PROCEDURE dbo.ProcedureName (@parametername datatype)

AS
BEGIN

<<<<<<<  Business Logic  >>>>>>

END

GO

Notes:

*Parameters are three types.

Input – Used to give input values

Output – Used to capture output values

In-Out – A parameter that can act be used to give input value and capture output value

* Maximum number of parameters in SQL Server 2005 and 2008 is 2,100.

* Maximum nested stored procedure levels – 32

Points to keep in mind when writing a stored procedure

Writing an optimized stored procedure is as important as writing the correct logic in the stored procedures. Here are some tips.

* Never use * in the SELECT statement. Retrieve only required columns as it reduces the network traffic.

* Try to use temp tables where a very huge sub-query is required.

* Avoid using cursors by making use of the temp table and WHILE loop.

* Make use of the indexed columns in the filters

* Make best choice in selecting temp table or table variable

Table - An introduction

Table

* A table is a database object which stores data in an organized format as rows and columns.

* All the data present in a row can be called as a record and it represents a single unit.

* The data in a record can be organized as fields (columns) to represent the various attributes of the unit.

* The columns can host any type of data that is pre-defined using what is called data type.

* Data type – int, char, varchar, bit, float, money, text, varbinary, Table etc.

* The data type selection depends on the business requirements.

* There are four special types of tables

        1.       Partitioned tables

        2.       Temporary tables

        3.       System tables

        4.       Wide tables

Partitioned tables are those which are horizontally divided so that they can spread over more than one file group in a database. It makes the large tables or indexes more manageable.

Temporary tables, as the name suggests, are those which are created (in tempdb automatically) on demand to achieve the logic and can be killed later. They are of two types- Local and Global

Local temporary tables are identified by # prefix and exist until dropped or till the session exists.

Global temporary tables are identified by ## prefix and exist until any of the session that is referencing it exists.

In other words, local temporary table is local to the session whereas global temporary table can span across multiple sessions.

There are exists a TABLE variable, identified by the @ prefix like @table, which when created allocates the SQL memory to store the data.

*** Using table variables is suggested in scenarios where we come across very less data else if large data is stored in a table variable. It uses the available SQL memory to store the data and only the remaining memory is used for other manipulations which may even lead contention or bottle-neck scenarios that hit the performance of the server. If we have to store large data temporary tables are the best. If we use the temporary tables to store very small data, the database IO operations cost more than when the data is stored in memory.  So, in this case table variable is preferable.

System tables are those in which SQL Server stores the data of the server configuration and all the database objects. In common words, they store the meta-data (data of the data). Those cannot be updated or modified except though a Dedicated Administrator Connection (DAC). The applications referencing the system tables may have to re-write the code before they are upgraded to other versions.

Wide tables are those which have a defined column set but use sparse columns.

Sparse columns mostly contain a NULL value in them.

Some maximum limitations on a table:

Property	SQL Server 2008R2(32-bit)	SQL Server 2005(32-bit)
Maximum row-size of any table	8060 Bytes	8060 Bytes
Columns per base table	1024	1024
Columns per primary/foreign/index key	16	16
Columns per SELECT	4096	4096
Columns per INSERT	4096	1024
Foreign key table references per table (recommended)	253	253
Clustered indexes per table	1	1
References per table	253	253
Rows per table	Limited by available storage	Limited by available storage
Triggers per table	Limited by number of objects in a database	Limited by number of objects in a database
Tables per database	Limited by number of objects in a database	Limited by number of objects in a database

 

Simple Programs for Learners 1. Fibonacci Series

/****************************************************

                FIBONACCI SERIES

*****************************************************/


DECLARE @i INT = 1, @o INT = 2
DECLARE @tbl TABLE(Num INT)

INSERT @tbl
SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 1

WHILE(@o<1000)
BEGIN
    INSERT @tbl
    SELECT @o

    SET @o = @o + @i
    SET @i = @o - @i
END

SELECT * FROM @tbl
GO

SSRS - SOME IMPORTANT TO KNOWS

/****************************************************************************/

                       SSRS - SOME IMPORTANT TO KNOWS

/****************************************************************************/

1.Shared data sets

One can share data sets between reports in which version of Reporting Services?

Choose your answer:    

       SQL2005 and above

       SQl2008 and above

       SQL2008 R2 and above

       Code name "Denali"

       Can not be done

Correct answer:

      SQL2008 R2 and above

Explanation:

      This is a new feature added with SQL2008R2! A dataset can be created and shared amongst numerous reports (both in the same project and any other project). This facilitates code reuse and assists to get reports built and deployed faster.

2.Data Driven Subscription

What is the null delivery provider in SSRS Data Driven Subscriptions used for?

Choose your answer:    

       To help preparing data driven schema with a blank dataset

       To help null values be accepted for parameters passed in data driven subscription

       To help improve server performance by preloading the cache

       To help deliver the reports to a default shared folder when delivery to specific shared folders fail

Correct answer:

      To help improve server performance by preloading the cache

Explanation:

      When the Null Delivery Provider is used as the method of delivery in the subscription, the report server targets the report server database as the delivery destination and creates cached copy of the report. When subsequent requests come to the server from users, the server picks the cached copy instead of precessing/rendering the report.

3.Report Server Web Service

SQL Server Reporting Services provides access to the full functionality of the report server through the Report Server Web service that has its endpoint at --

Choose your answer:    

       http://<Server Name>/ReportServer/SSRSManagement.asmx?wsdl

       http://<Server Name>/ReportServer/ReportService2006.asmx?wsdl

       there is such service: for security reasons, SSRS is accessible by scripts only

4.SSRS case sensitivity

What is true about parameter values passed thru the SSRS report prompt?

Choose your answer:    

       It is case-sensitive

       It is not case-sensitive

       Case sensitivity can be configured

       Default is case-sensitive

Correct answer:

Case sensitivity can be configured

Explanation:

Case sensitivity can be configured. You can set Case Sensitivity to True, False, or Auto.

The default value, Auto, indicates that the report server should attempt to derive the value from the data provider when the report runs.

If the value cannot be derived, the report runs as though the value were False.

5.SSRS timeout

If I have a SSRS report in SQL Server 2000 that should NEVER timeout, what value should I provide under timeout (in dataset):

Choose your answer:    

       any big integer (+ve)

       any big integer (-ve)

       - infinity

       leave it blank

       set it to Zero

       it cannot be set to NEVER timeout

Correct answer:

      leave it blank

Explanation:

      The value for Timeout must be empty or greater than zero. If it is empty, the query does not time out. The default is 30 seconds.

6.SSRS snapshot

What of these is NOT true about a SSRS report snapshot?

Choose your answer:    

       Snapshot is a report containing layout & dataset information (belong to a particular point in time)

       If a report prompts users for credentials or uses the security context of the user requesting the report, it cannot run as a snapshot

       If a parameterized report supports multiple values for a single parameter, snapshot is stored for all values

       Report snapshot is stored in a report server database

       Report snapshots are not saved in a particular render format

7.Reporting

You have to create a report where rows as well as the columns are not fixed. How will you design the report?

Choose your answer:    

       Use Pivot.Unpivot funtion in SQL server SP and call the same from SSRS

       Use Matrix/Cross tab report

       Use drill down report

Correct answer:

Use Matrix/Cross tab report

Explanation:

You need to use Matrix report of SSRS. This will automatically take care of converting all the rows to columns based on the selection.

8.Reporting Services Database

In SQL Server 2008, if you have a Reporting Services report server using the Express edition, what edition of database should you have?

Choose your answer:    

       Express only

       Express, Standard, or Enterprise

       Standard, and Enterprise

       You cannot have an Express edition Reporting Server

Correct answer:

      Express only

Explanation:

      The editions matter with Reporting Services. The database for an Express Edition reporting server should be an Express edition database.

9.Reporting Services Security

In SQL Server 2008 Reporting Services, what is the best practice recommendation for the service account?

Choose your answer:    

       Use a local administrator account

       LocalSystem

       Network Service

       Domain Account

Correct answer:

      Domain Account

Explanation:

      You always want to use the least privileged account that you can for a service account, or any reason.

      In Reporting Services 2008, the ASP.NET account for IIS is no longer used. Instead, you want to pick and

      account and a domain account, with minimum privileges is preferred as it is not a shared account in case it is attacked.

10.Category: Reporting Services (SSRS)

With which versions of SQL Server Reporting Services is IIS required?

Choose your answer:    

      SQL Server 2000 only.

      SQL Server 2000 and 2005 only

      SQL Server 2005 only

      All versions of SQL Server including 2008.

Correct answer:

      SQL Server 2000 and 2005 only

Explanation:

      IIS is required with Reporting Services 2000 and 2005, but not 2008. Or at least that''s what we hope :)

11.Category: Reporting Services (SSRS)

You have created a report that requires a user to enter credentials to run for secure purposes. You want to save this report as a snapshot so it can be saved for auditing purposes. How can you do this?

Choose your answer:    

      Just check the "Run as snapshot" option.

      Add this report to the Maintain Report History list and set it to run as a snapshot.

      You cannot set up this report to run as a snapshot.

Correct answer:

      You cannot set up this report to run as a snapshot.

Explanation:

      Certain types of reports cannot be run and saved as snapshots.

      If a report uses Windows Integration security or requires credentials, it cannot run as a snapshot.

Ref: Setting Report Execution Properties (Running Reports From Snapshots section) - Running Reports From Snapshots

12.Category: Reporting Services (SSRS)

In Reporting Services 2005 you have configured report snapshots for your Quarterly Sales Report. A developer only removes the YTD column from the report and one of your managers complains that both the YTD and the YTD% columns were removed. What can you do?

Choose your answer:    

      Once you have set up report snapshots, no columns can be removed, only hidden, so you can check the layout in a development tool.

      The report history will maintain the old layout for already saved reports, so you can examine an old report from the history.

      There is no way to determine this, so you need to just add the columns back if requested.

Correct answer:

      The report history will maintain the old layout for already saved reports, so you can examine an old report from the history.

Explanation:

      Report history for a report are still maintained even if the current report layout is changed. You can call up a report snapshot and examine it to determine if the YTD% column existed.

13.Category: Reporting Services (SSRS)

You are building a report that summarizes sales for a salesman and are asked to allow a user to display details for a sale if it is selected. What can you do to your report to allow this?

Choose your answer:    

       You need to display all the data in the report, but you can change the font size of the details to a smaller size so it looks better.

       You can set up details as invisible with a toggle to show them on your report.

       You need to build a second report and link it to this report.

       There is nothing you can do to your report to enable this.

Correct answer:

      You can set up details as invisible with a toggle to show them on your report.

Explanation:

      You can hide sections of a report and then add a visibility clickable item to each sale to allow its details to be shown.

Ref: How to: Add a Visibility Toggle to an Item -

14.Category: Reporting Services (SSRS)

You are running a high performance SQL Server 2005 Reporting Services server with default settings that must return reports quickly to prevent a load from stacking up on the database server. No sales report should run longer than 60 seconds. You decide to set a timeout of 65 seconds for the 4 sales reports you have deployed, however users report that some reports run up to 90 or 100 seconds. What is wrong?

Choose your answer:    

      You must have mistyped the timeout and set it for 120 seconds.

      The report server timeout setting does not work. It is expected to be fixed in Service Pack 3.

      The report server only checks timeouts every 60 seconds.

      You cannot configure settings for individual reports, only the entire server.

Correct answer:

The report server only checks timeouts every 60 seconds.

Explanation:

The report server in SQL Server 2005 only checks jobs every 60 seconds. So it will check at 60 seconds, but then not until 120 seconds.

15.Category: Reporting Services (SSRS)

You are configuring a SQL Server 2005 Reporting Services instance for email delivery of reports. What are your options for delivering email?

Choose your answer:    

       Local SMTP server only.

       Only Exchange servers running MAPI.

       Local or Remote SMTP servers.

       Local or Remote SMTP servers, or MAPI servers.

Correct answer:

      Local or Remote SMTP servers.

Explanation:

      Rports can be delivered using local or remote SMTP servers.

15.Category: Reporting Services (SSRS)

You wish to back up the symmetric key used to protect data on the Reporting Services 2005 instance installed on Sales01. From your workstation, what command do you use?

Choose your answer:    

       rskeymgmt -e -f a:\backupkey\keys -p <password>

       rsadmin -e -o a:\backupkey\keys -p <password>

       rskeybackup -f a:\backupkey\keys -p <password>

       You cannot do this from your workstation

Correct answer:

You cannot do this from your workstation.

Explanation:

While you can extract and back up the key to a file with the rskeymgmt utility, you must do it from the server itself. The typical command is:

rskeymgmt -e -f a:\backupkey\keys -p <password>

16.Category: Reporting Services (SSRS)

Which of the following statements best describes the filter capabilities of Report Builder?

Choose your answer:    

       Users can do simple equality filters only

       Users can do the standard equals, greater than, less than comparisons, as well as logical ANDS. There is no support for NOT

       Users can do equals, greater than, less than, etc, plus they can do logical AND, OR, NOT operations.

       Users can do equals, greater than, less than, etc, plus they can do logical AND, OR, NOT operations. Users can also group filters to allow more advanced filters.

Correct answer:

      Users can do equals, greater than, less than, etc, plus they can do logical AND, OR, NOT operations. Users can also group filters to allow more advanced filters.

Explanation:

      While it looks a little different than you may be used to, the filter builder is reasonably rich, allowing most standard evaluation types and rich boolean comparisons. The only weak spot in the set is no support for LIKE, you have to make do with CONTAINS.

17.Category: Reporting Services (SSRS)

True or false, Report Builder supports user defined run time parameters?

Choose your answer:    

       True

       False

Correct answer:

True

Explanation:

Absolutely true. Users can define any portion of a filter to be a run time prompt, letting other users easily change the filter as needed. Not only is it easy to set up, Report Builder automatically populates a list of all possible choices based on the column being filtered.

18.Category: Reporting Services (SSRS)

When discussing image support in Report Builder, which of the following is the most accurate statement?

Choose your answer:    

      Report Builder does not support images at all

      Users can add one or more images, but they will appear in the header or footer of the report, they cannot be added to the detail row

      Users can add one image to a report that will appear in the header or footer of the report

      Users can add one or more images, but they will appear in the header or footer of the report, they cannot be added to the detail row. Images that are stored as row data in the database can be rendered at the detail level.

Correct answer:

      Users can add one or more images, but they will appear in the header or footer of the report, they cannot be added to the detail row. Images that are stored as row data in the database can be rendered at the detail level.

Explanation:

      Multiple images can be added, but they appear in the header or footer depending on where placed on the report. The only way to get an image at the detail level (row based) is to have it be part of the database and included in the model.

19.Category: Reporting Services (SSRS)

Using Report Builder, which of the following is the best statement about the formatting options for boolean columns?

Choose your answer:    

      Booleans can be formatted as Yes/No or True/False

      Booleans can be formatted as Yes/No or True/False, or a user defined format

      Report Builder does not support boolean columns

      Booleans are formatted as True/False and there are no other built in options

      Booleans are formatted as True/False and there are no other built in options, but you could build an expression using IIF that would let you do other formatting

Correct answer:

      Booleans are formatted as True/False and there are no other built in options, but you could build an expression using IIF that would let you do other formatting

Explanation:

      From the format dialog there are no extra formatting options for booleans, Report Builder renders them as True/False.

      Writing an expression that you add to the model view is the easiest way to work around this limitation

20.Category: Reporting Services (SSRS)

What is the easiest way to capture the SQL statement for a Report Builder report you''re troubleshooting?

Choose your answer:    

       It''s not possible to see the raw SQL, it uses the Report Builder API to access the data

       It''s not possible to see the raw SQL unless you run the report from BI Design Studio

       Run Profiler

       Click Edit, View SQL to see the SQL generated by Report Builder.

Correct answer:

      Run Profiler

Explanation:

      Profiler will work as long as you have permissions to profile the server and is the the best solution because it requires

      no change to the Report Server itself. There is a way to log all report SQL to a log file, but that option was not listed

      here and is better used if you want to do analysis rather than troubleshooting.

21.Category: Reporting Services (SSRS)

Clicking File, Save in Report Builder does which of the following?

Choose your answer:    

      Saves the report to disk as an RDL file

      Saves the report to disk as a model

      Saves the report to the report server

      Saves changes to the model to the report server

Correct answer:

      Saves the report to the report server

Explanation:

      File|Save writes the report to the Report Server.

      Users have the option to also save the report to disk by using File|Save to File.

      Report Builder users cannot modify the model.

22.Category: Reporting Services (SSRS)

Which of the following choices show the three report formats supported by Report Builder

Choose your answer:    

       Image, Table, Chart

       Excel, Table, Chart

       Cross Tab, Matrix, Chart

       Table, Matrix, Chart

Correct answer:

      Table, Matrix, Chart

Explanation:

      Report Builder can build a report formatted as a table, chart, or matrix (cross tab), but only ONE can be used in any given report.

     

23.Category: Reporting Services (SSRS)

True or false, Report Builder supports using the LIKE function inside filters?

Choose your answer:    

      True

      False

Correct answer:

      False

Explanation:

      There is no LIKE support, the next best thing is the CONTAINS function which works as if you specified both a leading and trailing wild card.

24.Category: Reporting Services (SSRS)

Using Report Builder that is bundled with Reporting Services 2005, which of the following would work as a way to add a derived field to a report?

Choose your answer:    

       Derived fields are not possible in Report Builder

       Add a field to the model in Report Builder using the built in formula/function support

       Edit the SQL statement that brings back the report data to include the derived column

Correct answer:

Add a field to the model in Report Builder using the built in formula/function support

Explanation:

While Report Builder cannot be used to build or maintain models, it does allow you to add a virtual field that exists only within that report - to the end user it looks like the model is being modified.

25.Category: Reporting Services (SSRS)

True or false, Report Builder offers direct export to a Microsoft Access database as one of its supported export options?

Choose your answer:    

      False

      True

Correct answer:

False

Explanation:

False. Supported formats include Excel, CSV, XML, TIFF, MHTML, and PDF. XML or CSV could be easily imported into Access, but there is way to add the data directly from Report Builder

26.Category: Reporting Services (SSRS)

Out of the box Report Builder supports two report level fields that can be shown on a report. Which option below has those two options?

Choose your answer:    

      The current filter and the number of rows that matched that filter

      The current date/time and the execution time of the report

      The current date/time and the title of the report

      The total number of rows that were returned from the server before any filter was applied and the current filter

Correct answer:

      The current filter and the number of rows that matched that filter

Explanation:

      By default, the current filter definition and the number of rows that matched the filter are added to the end of the report. They can be removed and added back as needed.

27.Category: Reporting Services (SSRS)

Using Reporting Services 2005, it is true or false that subreports execute a query against the datasource once for every detail row in the report?

Choose your answer:    

      True

      False

Correct answer:

      True

Explanation:

      True. Subreports can be used for a master-detail relationship, or the subreport can be a separate item, but in either case RS will query to get the data for the report once for each detail row. If end users are going to only occasionally look at the data you''re displaying in the subreport or only view it for a few rows, a better option is to create a link to the other report.

28.Category: Reporting Services (SSRS)

Report Builder has no built in support for source control. Which of the following choices could be implemented if you decided that those reports needed to be under source control?

Choose your answer:    

       Report Builder reports are too simple to need source control

       Have users save the RDL files locally and check into source control using a source control utility, or write code to script them out each night and check them into source control

       Install the VSS add-in for Report Builder

Correct answer:

Have users save the RDL files locally and check into source control using a source control utility, or write code to script them out each night and check them into source control

Explanation:

Users can do faux source control by just saving copies of their RDL files locally before they make a change. Beyond that, you either teach them how to use a source control utility or you write a utility you can run on a schedule.

29.Category: Reporting Services (SSRS)

In Reporting Services 2005, how is Report Builder typically deployed to end users?

Choose your answer:    

      Report Builder is a web application that needs no deployment

      Network Admin uses Active Directory to deploy an MSI to groups that need to use it

      Users copy the files they need from a file share, including the Report Builder exe

      One click deployment launched from a menu on the Report Manager home page

Correct answer:

One click deployment launched from a menu on the Report Manager home page

Explanation:

One click deployment is fast and easy, users click the menu on the Report Manager page. It is not a web application, but rather a .Net Winform application.

30.Category: Reporting Services (SSRS)

Before a user can build reports using Report Builder in Reporting Services 2005, which one of the following steps must be done first?

Choose your answer:    

       Add the user to the Report Builder group

       Run RSCONFIG and check 'Allow Report Builder'

       Build and deploy a model

       There are no prerequisites, users can launch Report Builder from the Report Manager home page

Correct answer:

Build and deploy a model

Explanation:

Report Builder requires a model and that model cannot be defined using Report Builder. The advantage of using models is that users do not need to understand SQL statements to build a report (but they will still benefit from a basic understanding of the relationships between various bits of data)

31.Category: Reporting Services (SSRS)

What tool is available for ad hoc reporting by non IT end users in Reporting Services 2005?

Choose your answer:    

       Report Writer

       Report Builder

       Report Viewer

       BI Design Studio

       There is no ad hoc reporting capability

Correct answer:

Report Builder

Explanation:

Report Builder is bundled as part of Reporting Services 2005 and is targeted at end users needing to build ad hoc reports. BI Design Studio could be used, but typically requires more time and experience to use than the average end user will be willing to invest.

32.Category: Reporting Services (SSRS)

What does a report model provide in SQL Server 2005 Reporting Services?

Choose your answer:    

       A report model provides a visual layout template for a report.

       A report model provides familiar business names for database tables and fields

       A report model determines whether a report is cached, prebuilt, or ad hoc.

       A report model is a sample report for business users to customize.

Correct answer:

A report model provides familiar business names for database tables and fields

Explanation:

A Report model provides business names and terms for database fields and tables. It allows for predefined relationships between tables as well as grouping items together logically.

33.Category: Reporting Services (SSRS)

In building a PDF report in Reporting Services 2005, images that are originally stored in jpg format are rendered in jpg format. What format are images rendered in if they were stored originally in another format?

Choose your answer:    

       jpg

       gif

       tif

       png

Correct answer:

png

Explanation:

Images stored originally in formats other than jpg are rendered in png format.

34.Category: Reporting Services (SSRS)

You are building a report in Reporting Services using stored procedures that provide data to another application. One of the stored procedures returns 2 result sets for efficiency. Can you resuse this stored procedure in your reporting services report to get all the data from the stored procedure?

Choose your answer:    

       Yes, all data from the stored procedure can be consumed by the report.

       No, only the first resultset is visible to Reporting Services.

       No, multiple recordsets will cause an error.

Correct answer:

No, only the first resultset is visible to Reporting Services.

Explanation:

Reporting Services can use stored procedures to return data, but it cannot handle multiple result sets from a single stored procedure. You would need to break this up into two stored procedures and call each one from a separate dataset.

35.Category: Reporting Services (SSRS)

A collegue of yours has been working on some reports and reporting models with SQL Server 2005 Reporting Services. Everything he made was deployed on your test webserver. This collegue quit the job and apparently he also took the RDL files. What can you do to recover the RDL files? Submitted by Frederik Vandeputte

Choose your answer:    

       You''re doomed, there is now way to recover the source files. The are encrypted in ReportServer database.

       Don''t worry the .RDL files are just there on the webserver (c:\ineputpub\wwwroot\Reports\ ....)

       Click Edit under Report Definition for each report on the Report Manager website

       Write a SELECT statement on Catalog table in the ReportServer database and recover the source code from there

Correct answer:

Click Edit under Report Definition for each report on the Report Manager website

Explanation:

Correct answers are c and d. C is the easiest. D is fun to known and will also work if your webserver crashed.

SELECT Path, Name,

Type = CASE Type WHEN 2 THEN 'Report'

WHEN 6 THEN 'Report Model' END,

SourceCode = CAST(CAST(content AS VARBINARY(max)) AS XML)

FROM ReportServer.dbo.Catalog

WHERE Type IN (2,6)

ORDER BY Path, Type, Name

SELECT Path, Name,

Type = CASE Type WHEN 2 THEN 'Report'

WHEN 6 THEN 'Report Model' END,

SourceCode = CAST(CAST(content AS VARBINARY(max)) AS XML)

FROM ReportServer.dbo.Catalog

WHERE Type IN (2,6)

ORDER BY Path, Type, Name

36.Category: Reporting Services (SSRS)

You are working with Reporting Services 2000 and need to set the page size for a particular report. You have the following code:

Dim rs As New ReportingService.ReportingService

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

rs.Url = "http://localhost/reportserver/reportservice.asmx"

Dim p(5) As ReportingService.Property

p(0) = New ReportingService.Property

p(0).Name = "PageHeight"

p(0).Value = 8.5 * 25.4

Why is the page height set to 8.5 * 25.4?

Choose your answer:    

       This allows for the printer to control the height for any size between 8.5 and 25.4

       The PageHeight property is in mm, so this is the conversion for an 8.5 inch paper source.

       The PageHeight property is in cm, so this is the conversion for an 8.5 inch paper source.

       This allows for a height in two orientations, one for the portrait format and one for landscape

Correct answer:

The PageHeight property is in mm, so this is the conversion for an 8.5 inch paper source.

Explanation:

The properties are in mm and there are 25.4 mm per inch, so this will set the page height to match a standard US 8.5 x 11 piece of paper in portrait mode.

37.Category: Reporting Services (SSRS)

You have deployed Reporting Services on one of your SQL Server 2000 servers. You have noticed that there have been some performance complaints on your database.

You''d like to determine how heavily the reporting server is being used. Which performance counter can help you determine this?

Choose your answer:    

       Reports Executed/Sec

       Reports Completed/Sec

       Report CPU %

       Processor % for the server.

Correct answer:

Reports Executed/Sec

Explanation:

The only valid counters in this list are Reports Executed/Sec and Processor %. Of these, only the Reports Executed/Sec will give you some idea of how heavily the reporting service is being used.

38.Category: Reporting Services (SSRS)

You have a web farm for SQL Server 2000 Reporting Services and you need to add a new server to this farm. Which utility would you use to add this server to the farm.

Choose your answer:    

       rssrve.exe

       rsconfig.exe

       rsactivate.exe

       rsfarm.exe

Correct answer:

rsconfig.exe

Explanation:

To activate a new report server, thje rsactivate command is used. Prior to this, however to add the server to a web farm, the rsconfig utility is used.

39.Category: Reporting Services (SSRS)

If your query contains parameters, Report Designer automatically creates corresponding report parameters in the report when you type the query. When the report runs, values for the report parameters are passed to the query parameters. Which of the following SQL query will create a report parameter named EmpID

Choose your answer:    

       SELECT FirstName, LastName FROM Employee WHERE EmployeeID = ?EmpID

       SELECT FirstName, LastName FROM Employee WHERE EmployeeID = @EmpID

       SELECT FirstName, LastName FROM Employee WHERE EmployeeID = {EmpID}

       SELECT FirstName, LastName FROM Employee WHERE EmployeeID = parameters!EmpID

Correct answer:

SELECT FirstName, LastName FROM Employee WHERE EmployeeID = @EmpID

Explanation:

B is correct – the other options have syntax errors

40.Category: Reporting Services (SSRS)

You have written a stored procedure in a SQL Server database which returns a list of invoices for a report. This list could potentially contain about 30,000 rows. You want to provide the report users with the ability to reduce this recordset to a handful of relevant records. You have the choice of using parameters or filters. Which option would you use and why?

Choose your answer:    

       Parameters

       Filters

Correct answer:

Parameters

Explanation:

Answer: ‘Parameters’.

A stored procedure will easily receive parameters from the report which will reduce the size of the dataset that is returned to the report. In this case, using filters would return the entire dataset to the report, and then filter the data making this an extremely poor performing report.

Parameters

You can use report parameters to solicit values from the user. These values can be passed to a parameter in a query, passed to a filter, or they can be used in expressions within the report. A report parameter can provide a simple text box into which a user types a value, or it can provide a list of valid values from which the user must choose. This list can be static, or it can be updated dynamically with data from a dataset.

You can also define a default value to be used by a report when it runs, which can be a static value or one retrieved from a dataset. For information about adding parameters to a report using Report Designer, see Using Parameters in a Report.

Filters

You can use filters to filter report data after it has been retrieved from the data source. You can place filters on datasets and data regions.

Filters are different than query parameters in that when you use a query parameter, the returned data is filtered at the source. When you use a filter, the entire data set is retrieved, and then the filter is applied to retrieved data. This is useful for snapshot reports, in which the data is retrieved and stored with the snapshot report. Using filters, you can still limit the data the report displays, while still using the static snapshot data. Filters are also useful when the data source does not support the use of query parameters to filter data.

41.Category: Reporting Services (SSRS)

You create a report that has a title, and 2 sub-reports. The 2 sub-reports utilise a shared data source. Where is that data source stored?

Choose your answer:    

       Within the main Report.

       Within the sub-reports themselves.

       A separate file.

Correct answer:

A separate file.

Explanation:

Answer: A separate file. A data source can be contained solely within a single report, or it can be shared by several reports. The definition for a report-specific data source is stored within the report itself, while the definition for a shared data source is stored in a separate file on the report server. A report can contain one data source (report-specific or shared) or many.

Ref: Shared Data Sources and Report-Specific Data Sources - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_datasources_v1_33oi.asp.

42.Category: Reporting Services (SSRS)

You want to expose your sales reports on the web for the salespeople that are out the office. However, you are want to mitigate a denial of service attack by limiting the number of open connections. How would you do this?

Choose your answer:    

       Set the MaxOpenConnections in the rsreportserver.config file to a low value.

       Set the MaxConcurrentConnections in the rsreportserver.config file to a low value.

       Set the MaxActiveReqForOneUserin the rsreportserver.config file to a low value.

       There is nothing you can do in reporting services. You must depend upon a firewall.

Correct answer:

Set the MaxActiveReqForOneUserin the rsreportserver.config file to a low value.

Explanation:

To limit the number of open connections, specify the MaxActiveReqForOneUser configuration setting in the rsreportserver.config file. By default, this value is set to 20. Unlimited passing of credentials only occurs if you enable Kerberos V5 for your servers. The report server manages the number of outstanding URL and Web service requests. You can set an upper limit on the number of requests that are open and in process for each user. When the upper limit is reached, all subsequent requests are dropped. Setting an upper limit is a precautionary step that you can take to mitigate a denial of service attack.

43.Category: Reporting Services (SSRS)

You have written and deployed 4 reports that were required by the sales manager. He wants to receive these 4 reports simultaneously in a single email. Is this possible?

Choose your answer:    

       Yes

       No

Correct answer:

No

Explanation:

To distribute or receive a report by e-mail, you define either a standard subscription or a data-driven subscription. You can subscribe to and request delivery for only one report at a time. You cannot create a subscription that delivers multiple reports in a single e-mail message. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_subscribing_v1_62ci.asp

44.Category: Reporting Services (SSRS)

In Reporting Services 2000, the connection information used during setup for the report server has changed and you want to modify the connection information. Which utility allows you to do this?

Choose your answer:    

       RS utility

       RSACTIVATE utility

       RSCONFIG utility

       RSKEYMGMT utility

       RSCONNECT utility

Correct answer:

RSCONFIG utility

Explanation:

The connection information used to establish the connection is initially defined during setup. However, if you want to modify the connection information, or if you are moving components to different computers, you can run rsconfig to correct it. There is no such utility as RSCONNECT. Ref: rsconfig( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/cpu_rsconfig_v1_5f1e.asp).

45.Category: Reporting Services (SSRS)

You want to use scripts to copy content between report server databases and publish reports. Which utility allows you to do this?

Choose your answer:    

       RS utility

       RSACTIVATE utility

       RSCONFIG utility

       RSKEYMGMT utility

Correct answer:

RS utility

Explanation:

Answer: The RS utility. You can use rs.exe to run scripts on a report server. You can use scripts to copy content between report server databases, publish reports, and more. For more information, see RS BOL Scripting Deployment and Administrative Tasks (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsuiref/htm/cpu_rs_v1_5fxe.asp).

46.True or False? MSDE and SQL Server Personal Edition can be used as a source of data for reports.

Choose your answer:    

       True

       False

Correct answer:

True

Explanation:

Answer: 'True' The report server database is a SQL Server database that stores data used by a report server. MSDE and SQL Server Personal Edition cannot host the report server database, though it can be used as a source of data for the reports.

47.Category: Reporting Services (SSRS)

When installing Reporting Services components onto a server, you are creating...

Choose your answer:    

       A Microsoft Windows service

       A Web service

       Both

Correct answer:

Both

Explanation:

Answer: 'C' The report server is a stateless server that stores the meta data and object definitions in a report server database. The report server is implemented as a Microsoft Windows(r) service and as a Web service that runs on a Web server. When the report server component is installed, both the Windows service and Web service are installed. You can have one installation of report server on one computer. Report server does not install as multiple instances on one computer.

Reference: SQL Server 2000 Reporting Services Deployment Guide (http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx).

48.Category: Reporting Services (SSRS)

True or false? SQL Reporting Services has to be installed on a machine with SQL Server installed?

Choose your answer:    

       True

       False

Correct answer:

False

Explanation:

Answer: 'False'. You need a SQL Server 2000 license for the machine on which it is installed, and you need a SQL Server 2000 machine to host the ReportServer and ReportServerTempDB databases, but Reporting Services does not have to be on the same physical box as your SQL Server.

49.Category: Reporting Services (SSRS)

You are a Reporting Services power user and want to have a large report sent to the printer before you arrive at work each day.

You plan on scheduling this through a subscription and sent to a specific printer on your floor.

You don''t see a subscription option that will send directly to a printer though. How can you accomplish your task?

Choose your answer:    

       Modify the ISAPI filter in IIS

       Install Internet Printing under Windows Components

       You have to install a new printer extension

       Add the option under Site Settings in the Report Manager

       This can''t be done or extended with Reporting Services

Correct answer:

You have to install a new printer extension

Explanation:

This is done through an additional printer extension. As an example, you can use the sample code files located in the Application\PrinterDeliverySample directory of the samples folder to learn about data processing extensions. By default, these files are located at C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Extensions\PrinterDeliverySample.

Ref: Sample Printer Delivery Extension - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_sampleapps_v1_3t69.asp

50.Category: Reporting Services (SSRS)

You have recently installed SQL Server 2000 Reporting Services and you are now finalizing your installation. During setup, you did not specify the correct mail server to send reports out of and you need to change it. How would you go about configuring the mail server setup for Reporting Services?

Choose your answer:    

       Open the Administration console in Internet Explorer and select Administration Options and Mail Delivery

       Modify the global.asa in the \Reporting Services\ReportManager folder

       Modify the RSReportServer.config in the \Reporting Services\ReportServer folder.

       Modify the web.config in the \Reporting Services\ReportManager folder

       You must reinstall Reporting Services

51.Category: Reporting Services (SSRS)

You have recently installed SQL Server 2000 Reporting Services and you are now finalizing your installation. During setup, you did not specify the correct mail server to send reports out of and you need to change it. How would you go about configuring the mail server setup for Reporting Services?

Choose your answer:    

       Open the Administration console in Internet Explorer and select Administration Options and Mail Delivery

       Modify the global.asa in the \Reporting Services\ReportManager folder

       Modify the RSReportServer.config in the \Reporting Services\ReportServer folder.

       Modify the web.config in the \Reporting Services\ReportManager folder

       You must reinstall Reporting Services

Correct answer:

Modify the RSReportServer.config in the \Reporting Services\ReportServer folder.

Explanation:

The answer is to modify the RSReportServer.config file located at: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\

You change the SMTP setting in this file.

Ref: Configuring a Report Server for E-Mail Delivery - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_configserver_v1_4bzl.asp.

52.Category: Reporting Services (SSRS)

What tool is used to create and preview reports in SQL Server Reporting Services after you have installed the client?

Choose your answer:    

       Report Manager

       Report Creator

       Enterprise Manager

       Any text editor

       Visual Studio

       None of the above

Correct answer:

Visual Studio

Explanation:

To create a report in Reporting Services, you''ll need to use Visual Studio. You could design a report in almost any text editor since the report is just an XML file. This would not however allow you to preview the report.

Ref: SQL Server™ 2000 Reporting Services Deployment Guide - http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx

53.Category: Reporting Services (SSRS)

You are evaluating Reporting Services for your environment and want to make sure you select the correct edition of Reporting Services.

Below is a list of your requirements that must be achieved with the edition you select.

Which of the following can''t be achieved with Reporting Services Standard Edition but can with Enterprise Edition?

Choose your answer:    

       Public schedules shared between multiple reports. These schedules can be managed, paused, and resumed centrally.

       Uses the same report definition as another report, but may have different properties, permissions, and parameters. Offers another way for administrators to control and refine reporting.

       Users can create their own personalized subscriptions, defining preferences for receiving reports as well as which reports they want to receive. Subscriptions are subject to the security model, so users can only subscribe to the reports to which they have access.

       Supports Web farm configurations (multiple Report Servers remotely connect to a single Report Server database)

       Create alternative report delivery mechanisms, in addition to the default e-mail delivery option

       All the above can be achieved with Standard Edition.

Correct answer:

Supports Web farm configurations (multiple Report Servers remotely connect to a single Report Server database)

Explanation:

Of the above features, the only one that does not work in Standard Edition is the web farm tactic. For more information on editions and their features, please see http://www.microsoft.com/sql/reporting/productinfo/features.asp.

54.Category: Reporting Services (SSRS)

You began to use Microsoft SQL Server Reporting Services and have created your first report. What standard schema does Reporting Services create once you develop a report?

Choose your answer:    

       Web.config files

       FSS

       RDL

       DDL

       DMO

       Reporting services can output to any of the above

Correct answer:

RDL

Explanation:

Report Definition Language (RDL) is an XML-based schema for defining reports. The goal of RDL is to promote the interoperability of commercial reporting products by defining a common schema that allows interchange of report definitions. To encourage interoperability, RDL includes the notion of compliance levels that products may choose to support. For more information see http://www.microsoft.com/sql/reporting/techinfo/rdlspec.asp.