Stored Procedure Beginners

an useful link for Stored Procedure for Beginners
http://www.sql-server-performance.com/2003/stored-procedures-basics/

SQL IF...ELSE Statement

SQL IF...ELSE Statement used to test a condition.

IF tests can be nested after another IF or following an ELSE. There is no limit to the number of nested levels.
Syntax for IF...ELSEIF ( Boolean_expression )
BEGIN
Sql Statement Block
END
ELSE BEGIN
Sql Statement Block
END

eX:-

IF (3>2)
BEGIN
SELECT 'Correct'
END
ELSE
BEGIN
SELECT 'Wrong'
END
Value = Correct
IF (3<2) BEGIN
SELECT 'Correct'
ENDELSE BEGIN
SELECT 'Wrong'
END
Value = Wrong

Below is another Example of More than 1 IF...ELSE Sql Statement Block
IF (10<2)
SELECT 'Correct'
ELSE
SELECT 'Wrong'
IF ((10-2) > 5)
SELECT 'More Than 5'
Value = Wrong
Value = More Than 5

If your IF...ELSE Block Sql Statement only have 1 Sql query, you no need to include the BEGIN...END. If your IF...ELSE Block Sql Statement have more than 1 Sql query, you need to include the BEGIN...END to let it executed all the Sql Query in your Sql Query Block.

Auto Refresh Option In SSRS

Report Services Auto Refresh Option ( method 1)

 “How do I get my report to automatically refresh so the end user of the report doesn’t have to constantly click the refresh button?” 

The two different methods to achieve the same result. The first is by going to the report property and clicking anywhere in the space around the Design area(You should not click in the body area or any of chart property ).

Now you click on the Properties Tab or click F4 to get to your properties. When you see your properties options, look under the category “Other”. You should see the AutoRefresh option.

AutoRefresh is calculated in seconds and the student needed it set to refresh every 5 minutes. So we changed the autorefresh option for 300 seconds.

Report Services Auto Refresh Option ( method 2)

The second option to set your autorefresh is by right clicking on the report itself located under the Report Folder in your Solution, and selecting “View Code”.

Once in the code, don’t be alarmed by the amount of content that you see.  Search  AutoRefresh option in the code. you will see codein as follows.

Once you have found your AutoRefresh, you can set the time by deleting the Zero and replacing it with the number of seconds you need it to be. Click the save button and you are done.

thats all :-)

PerformancePoint 2010 error resolved “Code blocks are not allowed in this file” in SP2010

I’ve been playing with PerformancePoint 2010   and one of the first hurdles I came across is that, when i was trying to connect, sharepoint gave me a message saying

"An error occurred during the processing of /PerformancePoint/Pages/ppssample.aspx. Code blocks are not allowed in this file."

This seemed to be an issue with the following reference not being in the web.config on the site.

So add the follwing code into your web.config file.

<PageParserPath VirtualPath="/PerformancePoint/Pages /ppssample.aspx" CompilationMode="Always" AllowServerSideScript="true" />

Once this was in, the error was resolved and I could install the Dashboard Designer as standard.

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode

Integrating Reporting Services with SharePoint is really a fun and it gives extremely user-friendly environment for Reporting Services. There are mainly two ways of integrating Reporting Services with SharePoint. If Reporting Services is installed in Native Mode, Report Explorer and Report Viewer web parts can be used for interacting with reports. When the Reporting Services is installed in SharePoint Integrated Mode, SharePoint takes overall functionalities of Reporting Services, including SharePoint features such as versioning, alerts, and enterprise search. I recently configured SQL Server 2008 R2 in SharePoint 2010 as Integrated Mode, thought to share the experience; the reason for this post. Note that this is the way I configured the integration, you may take different approach for this integration.

I started this with Windows 2008 R2 and configured SharePoint 2010 on it. See this post “Configuring Windows 2008 R2 for SharePoint 2010” if you come across any issue with the installation. When SQL Server Reporting Services is installed, make sure that you select either “Install the SharePoint integrated mode default configuration” or “Install, but do not configure the report server”.

If you need to complete the SharePoint farm installation using non-domain accounts, this post “Complete Farm SharePoint 2010 installation on Single Server using non-domain accounts” shows you the way.

Once the server is ready with SharePoint 2010 and SQL Server 2008 R2, configuration can be started. If you have selected “Install, but do not configure the report server”, you need to configure Reporting Services in SharePoint 2010 mode. Here are steps for configuring it;

• Open Reporting Services Configuration Manager and connect with Reporting Services instance.
• Make sure that Report Service status is Started. Notice that Report Server Mode is Native.
• Open the “Report Service Database” section by clicking the Database navigational tab button. It shows the SQL Server Name, Database Name, and Mode if configured. Click on Change Database button to launch Report Server Database Configuration Wizard. Here is a screenshot of it;
  
• Click Next and open the next window of the wizard. Configure the database server which will host the Reporting Services instance. Once authentication is set, click Test Connection and see whether connection can be established successfully. Click Next.
• Type the database name as ReportServer which is the default name. However you can give a names like ReportServerSharePointIntegrated too. Select the option Report Server Mode as SharePoint Integrated. Click Next to continue.
• Set credentials that will be used for connecting to the database. If everything is in one box, Service Credentials would be enough.
• When you click on Next, you will see the summary of what you have entered/selected. Continue with the wizard and complete it.
• If need to change the port of Web Service URL, go to Web Service URL section by clicking Web Service URL navigational tab button and set it. I have set the port as 20000 and my Web Service URL is http://DP03:20000/ReportServer.
  

Next step is downloading Reporting Services Add-in for SharePoint 2010. Here is the link for downloading it:http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=b3bebf9d-d86d-48cd-94e2-0639a846be80.

Once it is downloaded, start installing it. Starting screen would be this;

Complete the installation. Now all required parts are ready. Next is configuring SharePoint for Reporting Services. Open SharePoint 2010 Central Administration and click on General Application Settings link.

Once the General Application Settings is open, a section called Reporting Services can be seen which allows managing integrating settings.

The link Reporting Services Integration opens the window where Report Server Web Service URL and Authentication Mode can be set. The Report Server Web Service URL should be the URL that has been configured with Web Service URL navigational tab of Reporting Services Configuration Manager. Authentication mode can be either Windows Authentication or Trusted Authentication. Since everything is in one box, Windows Authentication would be fine with this.

In order to user Trusted Authentication, a trusted account has to be predefined. When Trusted Authentication is used, Report Server has no knowledge on the user connected to the SharePoint. Trusted account impersonates connected SharePoint user and will be used for accessing the Report Server. Trusted Authentication is usually used with Form Authentication or windows authentication without AD or Kerberos. Here is my screen;
Set Server Defaults under Reporting Services allows changing default server settings for Reporting Services. Some of this elements can be changed are; Number of Snapshots, Ad-Hoc Reporting and Client-Side Printing.

Now the integration is done. The SharePoint is fully integrated with Reporting Services. We need a hosting environment for Reporting Services. Since Reporting Services Add-In has added necessary content types for the Farm, reports can be added to any site. Or else, a new site can be created for hosting Reporting Services reports and connections.

To Create a site, configuring libraries and content type, and publishing reports from BIDS.

Let’s start creating a Web Application. Go to SharePoint Central Administration and click Manage Web Applications under Application Management. Once Application Management is open, click on New to create a new web application.

Some of the attributed in New Web Application Form as as follows;

• Authentication
  Select Classic Mode Authentication
• IIS Web Site settings
  Select Create a new IIS web site option. I have named it as Report Server – 10005 and set the port as 10005.
• Security Configuration
  Leave default values
• Public URL
  In my case, URL is http://DP03:10005
• Application Pool
  Make sure Create new application pool is selected. I have named it as ReportServer – 10005.
• Database Name and Authentication
  Set the SQL Server name for Database Server. I have named the database as WSS_Content_ReportServer_10005.

Click OK to save setting. Once the process is completed, Application Created windows is appeared. Click OK to continue. New web application will be listed in Web Application window.

Next step is creating a site collection. Go back to Central Administration and click create site collections under Application Management. This opens Create Site Collection window. Let’s ceate one;

• Make sure that correct web application is selected (In my case, it is http://DP03:10005/.
• Add Title as Report Server.
• Let’s use the root for the Web Site Address. Do not select sites from the drop-down. Leave it blank.
• Select the Template as Team Site.
• Set both Primary Site Collection Administrator and Secondary Site Collection Administrator. Since I have everything in one machine, I set both as DP03\Administrator;.
• Click OK to create the site.
  

Once done, a message will be appeared saying Top-Level Site Successfully Created. Click on the link to go the site (In my case, link is http://dp03:10005). This is what I see;

Site needs libraries for holding reports and connections. Let’s create two libraries for the site.

• Click on Libraries link in left pane. It lists all document libraries available.
• Click the button Create for creating a new library.
• Select Document Library and Set the name as Reports.
  
• Click Create for creating the library. Once the library is open, click on Library Settings for adding content types.
  
• When Library Settings is open, library related things such as General Settings, Columns, and Views are shown. By default, it does not show Content Type. In order to enable content type, click Advanced Settings under General Settings. Once the window is open, select Yes for Allow management of content types.
  
• Click OK to save settings. Note that now the Content Type is shown under Library Settings. Click Add from existing site content types link under Content Type. This opens Add Content Types page.
• Select Report Server Content Type from Content Types From drop-down. Then move Report Builder Report from left listbox to right listbox.
  Note that if you are not seeing “Report Server Content Types”, that indicates that Add-In has not been properly installed”.
  
• Click OK to save settings. Report Builder Report content type is now appeared under Content Types of Reports library.
  
• We have to create another library for data connection. Follow same steps for creating the library. Name it as Data Connections. Then enable Allow management of content types as did for Reports library. Then add Report Data Source content type for it.
  

Now SharePoint 2010 is ready for holding reports from Reporting Services 2008 R2. Let’s try to publish some reports to this. Open Business Intelligence Management Studio and create a Reporting Services project. If you have AdventureWorks 2008 Sample Report Project, you can use it for testing. Once the project is open, get the properties of the project. Set following properties;

• TargetDataSourceFolder: http://dp03:10005/Data Connection
• TargetReportFolder: http://dp03:10005/Reports
• TargetServerURL: http://dp03:10005/

Now deploy reports and see. Reports will be published to SharePoint site we created. Once published, open the home page of the site (http://dp03:10005) and click on Reports link in left pane. It lists all the reports published.

Click one of reports and see whether it works fine. Here is the output of Company Sales 2008 report.

All done. Reporting Services 2008 R2 is integrated with SharePoint 2010. I will be exploring more on this, specially SharePoint related things. Once they are explored, I will publish them.

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode________________

You will not face much issues after configuring if it is done with Standalone SharePoint 2010 architecture. If it is a SharePoint farm which components are distributed, few things should be considered. Here are some of points you need to consider when Reporting Services is configured in SharePoint 2010 Farm. Note that this was tested with a Web Application which Authentication is set as Classic and NTLM is set for Authentication Provider.

Domain accounts created
Reporting Services Account: Domain account, no admin privileges. Usually name it as a Domain\ReportingServices. Set this with Reporting Service Configuration Manager.

Application Pool Account: Domain account, no admin privileges. Usually name as Domain\SP_SSRSApp. Use this when the Web Application is created with SharePoint.

Site Collection (or Site) account: Domain account, need local admin rights. Usually name as Domain\SP_Admin.

** All accounts are maintained as Managed Accounts in SharePoint.

Reporting Services Windows Service Account
This should be a domain account. Once it is set with Reporting Services Configuration Manager, you need add the same account to SharePoint too. First, have it as a Managed Account (Central Administration –> Security –> General Security –> Configure Managed Accounts). Then add the account to the Site where you have configured Reporting Services Libraries. You can add this account to Home Members group. Errors you may get if it is not added are “The permissions granted to user ‘Domain\User’ (what ever account you used for logged in) are insufficient for performing this operation.” and “rsAccessDenied”. Note that error has no link with the service account but it is related to it. You may get this error with Report Builder or BIDS when deploying reports, or viewing reports.

In addition to that, you need to make sure that the account is added to Web Application content database which is created for Web Application. If not, you may get and error “Report Server has encountered a SharePoint error”.

Other than that, this account is in ReportServer database too. It is automatically added, but make sure that it is there.

Allow Anonymous Setting
Make sure that Allow Anonymous is set as false when the Web Application is created. I faced an issue related deployment from BIDS if this is not set to false. Once the application is created, double-check with IIS (Site –> Authentication) and see whether it is disabled. As per the Authentication and Authentication Provider we set, Only ASP.NET Authentication and Windows Authentication should be enabled.

Complete Farm SharePoint 2010 installation on Single Server using non-domain accounts

When I was installing and configuring SharePoint 2010 in my machine, the following error appeared, stopping my installation.

The specified user Administrator is a local account. Local accounts should only be used in stand alone mode.

The reason was, I was trying to install and configure complete farm installation on standalone machine. My machine is not a domain controller or connected to a domain. However I found a way to continue my installation. It explains here.

All you have to do is, do not continue the installation with configuration wizard, stop it and as above link explains, use New-SPConfigurationDatabase with PowerShell for specifying none domain account for the farm. Once added, start configuration with SharePoint 2010 Products Configuration Wizard which is in StartMenu – Microsoft SharePoint 2010 Products.

Configuring Windows 2008 R2 for SharePoint 2010

If you are planning for installing SharePoint 2010 on Windows 2008 R2, here are some installations should be done before starting the SharePoint 2010 installation.

• Open Server Manager and click on Add Roles.
• Select Application Server. When selected, it prompts for “Add Required Features”, accept it and continue.
• Under Application Server – Role Services, make sure below items are selected:
• .NET Framework 3.5.1
• Web Server (IIS) Support
• TCP Port Sharing
• HTTP Activation
• TCP Activation
• Named Pipes Activation
• Install SQL Server 2008 (or R2) as the default instance.
• Install ADOMD 10. You can download it from here.
• Install Geneva Framework Runtime. You can download it from here.
• Update windows via Automatic Updates.
• Install the hotfix Windows6.1-KB976462-x64. You can download it from here.

Once you have all installed, you should be able to start and continue SharePoint installation without any issue.

SharePoint 2010 Free Lerning Training Kit

Here is an easy way to learn SharePoint 2010

Site: SharePoint 2010 Developer Training Course
Download link: SharePoint 2010 Developer Training Kit

This training kit provides videos and hands on labs which are very useful to understand how SharePoint 2010 works and how it can be used.

:-)

SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

When you call a Microsoft SQL Server 2008 R2 Integration Services (SSIS) package from a SQL Server Agent job step, the SSIS package does not run. But, when You run it mannulay, it wil run fine

This problem occurs when one of the following conditions is true:

• The user account that is used to run the package under SQL Server Agent differs from the original package author.
• The user account does not have the required permissions to make connections or to access resources outside the SSIS package.

The package may not run in the following scenarios:

• The current user cannot decrypt secrets from the package. This scenario can occur if the current account or the execution account differs from the original package author, and the package's ProtectionLevel property setting does not let the current user decrypt secrets in the package.
• A SQL Server connection that uses integrated security fails because the current user does not have the required permissions.
• File access fails because the current user does not have the required permissions to write to the file share that the connection manager accesses. For example, this scenario can occur with text log providers that do not use a login and a password. This scenario can also occur with any task that depends on the file connection manager, such as a SSIS file system task.
• A registry-based SSIS package configuration uses the HKEY_CURRENT_USER registry keys. The HKEY_CURRENT_USER registry keys are user-specific.
• A task or a connection manager requires that the current user account has correct permissions.

Solution
 further information go to http://support.microsoft.com/kb/918760 page read further.

and what i did was,

• I created a proxy, and navigate to SSIS Packages through Proxy.
• To create a Proxy you need to Have a Credential that can be created by navigating Security Folder in your SQL Management Studio.
• Then Go to the Credential Folder and provide your login Information and give it a name.
• Now Go to the SQL Server Agent and Create a New Proxy for SSIS and provide the Credential which you created.
• Now Schedule Your SSIS Package and Run as with your newly created Proxy.  

:-)