Tag Archive for 'sqlserver'

Beginning SQL Server 2005 Programming

After a while (busy days…) I’ve got a new book: Beginning SQL Server 2005 Programming by Robert Vieira.

I’m not considering myslef a SQL Server expert but to be honest I was afraid of this beginning word in the title. Yet I’ve been using SQL Server for a couple of years (apart from Java, C#, etc. – so not full time SQL developing). Also I attended a few SQL related courses when I was at the university. So I should have quite solid theoretical and pratctical background. However, as I was not working with SQL Server full time I decided to give that book a bash. You can always stop reading if you don’t like the book icon smile Beginning SQL Server 2005 Programming

Continue reading ‘Beginning SQL Server 2005 Programming’

T-SQL – How to select multiple rows into a single row

I spend too long on finding the solution for this problem…

Basically I had a table which looked like this
table T SQL   How to select multiple rows into a single row

What I wanted was to select all as one row as a set of items separated with a comma, where an item was ‘id: name’. The items had to be sorted by the id. So the result was supposed to look as below:
result T SQL   How to select multiple rows into a single row

Continue reading ‘T-SQL – How to select multiple rows into a single row’

Brief Introduction to SQL Server Reporting Services (SSRS)


If there’s no presentation embeded, you can
view it on slideshare

Security in accessing reports on Report Server

Once you deploy reports on to Report Server they can be accessed from any Web application with an established URL. However, I see a scenario when one wants to prevent some users from accessing reports (e.g. a user should be able to view reports presenting only their personal information).

The way around this is to view reports on an aspx page by employing ReportViewer control, which makes it possible to view reports hosted locally or deployed on (in particular remote) ReportServer. I’ve chosen the former approach.

In order to use ReportViewer with reports hosted locally, you need to:

  • change the extension of the report files from RDL to RDLC
  • configure ReportViewer control so it is associated with an appropriate RDLC file and DataSource (you will need to configure it by providing the connection details, specifying the SQL query or stored procedure that will retrieve the data)
  • in code behind for that aspx page (in Page_Load method) you can add some logics that checks if the currently logged user can access the report; if not, you can just throw an Exception
  • if you want to pass parameters to the ReportViewer, you will have to add a few lines to the above mentioned method:
    ReportParameter p = new ReportParameter(PARAM_NAME, PARAM_VALUE);
    reportViewer.LocalReport.SetParameters(new ReportParameter[] { p });
 Security in accessing reports on Report Server

Reporting Services – deploying RDL files

In this post I’ll describe how to deploy RDL files using Report Manager (more often http://localhost/Reports or http://localhost/Reports$SQLExpress).
Note: This description applies to the situation where Microsoft SQL Server is used.

  1. Go to Report Manager
  2. Too keep all clean, you can create a folder in which you will keep all your reports
  3. Create the data source, if not yet existing, by selecting the New Data Source link.
    • Specify Microsoft SQL Server as the Connection Type.
    • Specify the database Connection String as follows:
      data source=[Name of database server SQL Instance];initial catalog=[Name of database]
    • Connect Using the Credentials stored securely in the report server and specify the user name and password used to connect to SQL Server instance installed in the database server.
      ssrs config3 350x303 Reporting Services   deploying RDL files

      Datasource configuration

  4. Upload the report by selecting the Upload File link and navigate to the RDL file stored in the system.
  5. Using Show Details view, edit the uploaded reports by selecting the Edit link.
  6. Under the Data source link, assign the previously created shared datasource as the source of data for the report.

    Note: The “Apply” button needs to be selected to apply the changes made for the report.

 Reporting Services   deploying RDL files

Reporting Services – problem with passing parameters directly in the URL

In one of the projects I had to create reports (using Reporting Services) that then should be accessible from the application I’ve been developing.

As the first thing I created the reports themselves (RDL files), deployed them using the Report Manager (more often http://localhost/Reports or http://localhost/Reports$SQLExpress), and configured so I could access them. However, each time I accessed a report I had to provide the input parameters which were used by the stored procedure in order to deliver appropriate amount of information. I wanted to avoid that by passing the parameters somwhowin the URL.

Google came with two interesting articles: Using SQL reporting services in an asp.net application with some notes on report parameters, and Passing parameters and other options directly through a URL in Reporting Services. I followed the instructions but failed. In fact I got stuck making minor changes to the URL, checking the configuration, googling further…

It was only my friend who spotted that I tried to pass the parameters to the Report Manager using URL similar to http://localhost/Reports/Pages/Report.aspx?ItemPath=XXX&rs:Command=Render&ParamName=ParamValue. Instead I should use the Report Server, so link looking this way: http://localhost/ReportServer/?XXX&rc:parameters=false&rs:Command=Render&ParamName=ParamValue.

Note: Please remember there are two services: Report Manager and Report Server and understand the difference between them. This way you won’t loose presous time, as I did icon smile Reporting Services   problem with passing parameters directly in the URL

 Reporting Services   problem with passing parameters directly in the URL

Reporting Services – “Your browser does not support scripts or has been configured not to allow scripts.”

When I run a report in the Report Manager using Internet Explorer (I had this issue on IE7) I got the following error:

Your browser does not support scripts or has been configured not to allow scripts.

Solution

Should you get it as well follow the instruction below:

  • go to (from its menu) Tools/Internet Options
  • go to Security tab
  • select Trusted zone from the list of available zones
  • click Sites button
  • in new window provide the URL of the Reports Manager (e.g. http://MACHINE_NAME/*); if you are running Reports Manager not on the default port, provide the port number as well

That helped me.
Should you have any problems with it, let me know.

Create a report with Reporting Services

If you want to create a report in 5 mins, you should follow this tutorial.

All requirement software can be found there. I installed the following:

The tutorial instructs you how to create the report (RDL file): how to define the data source, layout and how to preview the report.

Once you’re done, you can use Report Manager to deploy the report. In my environment Report Manager is available at http://localhost/Reports$SQLExpress and looks as below:

report manager 350x169 Create a report with Reporting Services

It is possible that Report Manager is available at a different URL on your machine. To check the URL go to Control Panel\Administrative Tools, then Internet Information Services (this is how to get this screen under Windows XP). On the window you get expand Internet Information Services\XXX (local computer)\Web Sites\Default Web Sites. You should see anything that is similar to Reports and ReportServer. As I described before, in my case those virtual directories were named with $SQLExpress suffix at the end.

Anyway, once you get to the Report Manger, click Upload File button. In the new screen choose the file to upload (RDL file you’ve created with the tutorial). Then in main screen of Report Manager select the report you’ve added. Now you should see the report. As the ouput is long, it is paginated so you can use Previuos Page and Next Page buttons to show different parts of the report. You can also choose the format in which the report is presented; by default you can view it in the following formats:

  • embedded in the website
  • PDF file
  • Excel file

That’s all. Now you can create any report you want.

Reporting Services – blank Report Manager screen

I believe Reporting Services are very powerful tool for report generation. Unfortunately the installation process can be time consuming as you might face some problems with the cofiguration… Of course such problems can be the reason of unsufficient knowledge in SQL Server configuration and management, etc.

Anyway, blank Report Manager screen can be one of the problems, which displays as shown below:
 Reporting Services   blank Report Manager screen

Solution

This issue can be easily fixed by unsetting Enable anonymous access feature for either Reports or ReportServer virtual directory in IIS. In my case (on Windows 2003) I had to switch that of for both that virtual directories.
To do that follow the steps below:

  • go to Properties of either virtual directory in the IIS manager
  • select Directory Security tab in the Property window
  • click Edit in the Authentication and access control section
  • disable Enable anonymous access feature
  • close all windows by clicking OK buttons in the windows that showed up

Data Transformation Services

Recently, I’ve learned how to write DTS (Data Transformation Services) in SQL Server 2000. In general, this tool allows transformation of data: copying between different databases (by different vendors) and files (Excel, CSV, etc.). The idea is simple; the usage often priceless.

The execution path of a package is defined using a kind of graph, where nodes are tasks to execute. To operate on data one can write VB/ActiveX script and SQL queries (either direct queries or encapsulated within stored procedures).

Everything would be great if it were easier to write the packages… I believe it’s quite difficult to write the first package, especially with loops – not everything is obvious why/how to use. Also, there’s much less information on the Internet (guides, code examples, fora) than for example for Java programming language…

Anyway, I found the following links useful: SQL DTS and this