Archive for the ‘SQL Server’ Category

How to fix “The report server has encountered a configuration error. Logon failed for the unattended execution account.”

January 17, 2012 1 comment

Recently after I have deployed some SSRS reports to a client staging environment, I got this error as below

After some Goggling and binging, I have found out the problem is that SSRS Execution Account in SSRS configuration manager is set to a normal user account , after the user change its password , it cause the problem above. After some further research, I have found out we do not need to have an Unattended Execution account specified as EnesysRSExtension adheres to SharePoint security and passes through the end user security to maintain permissions. So after I have cleared the SSRS Execution Account, it works like a charm.The unattended execution account refers to the SSRS Execution account from the SSRS Configuration manager as shown below.


The report server has encountered a configuration error. Logon failed for the unattended execution account.

Configuring the Unattended Execution Account

Categories: SSRS

Create a parameterized report using SSRS –Part2

January 8, 2012 Leave a comment

In the first part of the series, I have created a parameterized report using SSRS. However, you have to type in the ship Country instead of selecting it from a dropdown list. In this post, I will show you how to create that dropdown list.

1. Create a new Shared dataset called ShipCountriesDS

2. Create a new report dataset using the shared dataset

3. Go to the report parameter and double-click the @ShipCountry and then go to the available values properties tab. Select Get values from a query and select the dataset you created above.

4. Run the report, you will see the dropdown list.

5. Click the view report button and then you will see the report shown below.

You can download a complete solution here.

Categories: SSRS Tags:

Create a parameterized report using SSRS — Part1

January 6, 2012 Leave a comment

In this post I will show you how to create a parameterized report using SSRS. I will be using the Northwind database and the report I am creating are Orders by ShipCountry.

1. First, open SQL Server management studio and create a stored procedure called GetCustomerOrdersbyShipCountry.

2. Open Visual Studio 2008 and create report project.

3. Create a new shared data sources called NorthwindDS , it will create the connection to the Northwind database.

4. Create a shared dataset using the stored procedure we created before.

5. Create a new report using the Report template (not the Report Wizard template)

6. Drag a table from the toolbox to the report area and it will prompt you a window to create a dataset for report. Create the dataset using the shared dataset.

7. Drag the fields from the dataset to the report columns.

8. If you could not find dataset you can find it from first clicking the report panel then go to View-Report data.

9. After that, you will see the report like the one below.

10. If you run the report you will see the parameter on the top of the report.

11. If you fill the parameter and run the report, you will see the report below.

You can download a complete solution here.

Categories: SSRS

Deploy SSRS Report to SharePoint document library

August 4, 2011 1 comment

In previous post, I showed you how to configure SSRS and SharePoint using SSRS SharePoint integrated mode. In this post, I will show you how to deploy SSRS report into SharePoint document library using Business Intelligence Development Studio.

1. Create site collection using Business Intelligence Center template.

2. Create a document library called Reports and add the following content types to this document library.


3. Open you SSRS project using Business Intelligence Development Studio and configure your project properties as below

4. Right-click on the project and deploy the solution.

5. If you get a pop up for credentials and no matter what you try for the user name and password, the pop up keeps coming back up and nothing happens as shown below, you can find the fixes from my previous post.

6. After the deployment succeeds and navigate to the reports document library. Click on the report that has been deployed to your document library. You can view the report as below

Categories: SharePoint 2010, SSRS

How to fix “Not able to deploy report to SharePoint 2010 in integrated mode”

August 4, 2011 4 comments

I am running SharePoint 2010 Enterprise with SQL2008R2 in the same server as my development environment. I have installed the reporting services R2 add-on to the SharePoint server. I have configured reporting services on the central admin properly. I can browse to the report server fine too. The problem I have is that when I try to deploy my report developed in visual studio2008 I get a pop up for credentials and no matter what I try for the user name and password, the pop up keeps coming back up and nothing happens as shown below

My environment looked something like this:

SharePoint site for reports: http://win-oirj50igics/

Reporting Service Integrated Mode URL: http://win-oirj50igics:80/ReportServer

The report deployment properties are as shown below:

After asking this question in MSDN and SharePoint Overflow and thanks for infotekka, I finally got the solution for it. The following are posted by infotekka from SharePoint Overflow and I keep it here for my own references as I have not found an explanation for this issue anywhere.

So after searching through the logs I determined that when I was trying to deploy my reports Visual Studio was seeing that the hostname I was using was part of the reserved hostname of the report server (win-oirj50igics), and so it was treating the path /Reports not as a SharePoint site but rather a subdirectory in a native mode Report Server implementation (since we never actually have a way of telling Visual Studio that we are deploying to SharePoint or native mode it tries to detect on its own). The reason why we get the constant requests for authentication is because since Visual Studio believes we are trying to connect to a native mode instance it tries to find the web service at http://hostname/reportservice2010.asmx (if you’re using SQL R2). SharePoint out-of-the-box does not allow for asmx files to be downloaded, and so it returns an access denied error, which in turn causes Visual Studio to prompt for better credentials.

In order to resolve this mess I added an alternate access mapping to my SharePoint site, http://www. and used that URL in Visual Studio to connect to my SharePoint reporting site and viola, it worked! Since I used an AAM that was different from the reserved URL for reporting services, Visual Studio did not assume that I was connecting to a native mode instance and instead looked _vti_bin/ReportServer for the files it needed to upload the reports.

So, after that long winded explanation the bottom line is that it will fail if you are using the same URL for your reporting services integration and your SharePoint site, so add a new URL to your SharePoint site by either adding a new name in your Alternate Access Mappings or extending the Web App onto a whole new IIS site.

Configure SSRS with SharePoint2010

August 4, 2011 Leave a comment

In this post, I will show you how to configure SSRS with SharePoint2010.Overall, there are three steps. Firstly, you need to install SSRS in a SharePoint integrated mode and create SharePoint-integrated Report database. Secondly, you need to install SSRS Add-in; it should be installed as part of the prerequisites before installing SharePoint server. Finally, configure the SSRS integration in SharePoint Central Admin (CA). The detailed steps are as follow.

1. Go to CA àGeneral Application Settings and if you have seen the Reporting Services as below, it means you have successfully installed the add-in.

2. I assume you have already installed SSRS using SharePoint integrated mode, if you have not, you need to install SSRS, however, you will not be able to select SharePoint integrated mode. What you need to do is to select “Install, but do not configure the report server” option to install SSRS and create SharePoint integrated Report database using SSRS Configuration manager. Your SSRS database configuration should look like below

3. Go to CA àGeneral Application Settings à Reporting Services Integration and fill in the information as below

4. After the process completes, you should be able to see the following messages below and now you are all setup to start create report in SharePoint2010.

Categories: SharePoint 2010, SSRS

Using SMO to drop a SQL Database

May 7, 2010 6 comments

SQL Server Management Objects(SMO) is the API you can use to manipulate the sql server,like create databse and delete database.
To get more details you can check the msdn documentation.
There are 2 ways you can drop a database
1. You could create a Database object and call Drop method:

Dim database As Database = New Database(Your database name)

2.However if you have existing connections to the database ,attempting to drop it using the above method will fail.Recall that when you try to drop the database from management studio ,you can tick the check box to close all the connections before drop the database.It is not so obvious , but you can do the exact same thing using SMO:

Dim server As Server= New Server(ServerConn)
                server.KillAllProcesses(Your database name)
                server.KillDatabase(Your database name)

Shout it

Categories: SQL Server