In this article, I will explain how to create a simple report (that could be first report for a newbie) using SSRS 2008. Here I am assuming that you have successfully installed SQL Server 2008 along with SSRS.

There are two ways to create Reports in SSRS. You can develop the report manually, or you can use the Report Wizard to give yourself a head start. For this first report, I am going to take advantage of the wizard.

STEP 1:

To begin, start a new Business Intelligence project in Visual Studio 2008 by clicking on Start --> All Programs --> SQL Server 2008 --> SQL Server Business Intelligence Development Studio. Then, from the menu, select File --> New --> Project to open New Project wizard. Now select Report Server Project from Visual studio installed templates and specify Name, Location and Solution Name. I am specifying these ReportProjectSSRS, D:, and ReportProjectSSRS respectively as shown below:



STEP2:

In Solution Explorer, right click on Reports folder and select Add New Report. This will open Report Wizard. Click Next on the Welcome screen and this will bring you to the Select the Data Source screen.



STEP 3:

Enter the name of Data Source as dsLocal and select type as Microsoft SQL Server. Now click on Edit button to set the connecting string for data source, this will open Connection Properties window. Enter Server name and database name and click on Test Connection buttion to make sure the connection is established. Click OK button twice to close Test Results and Conection Properties windows.



You can check the Make this a shared data source checkbox to make this data source as shared so that it can be used for other reports as well. Now click on Next to proceed.



STEP 4:

This will open Design the Query wizard. Here you can define your Query string. Alternatively you can use Query Builder... to build your query. I will use below query to pull data from Employee table:

SELECT
   [Emp_code],[Emp_Name],[Desg],
   [Head],[DOB],[Basic],[Dept_Code]
FROM Employee (NOLOCK)

Click Next to proceed. It will display Select the Report Type wizard. Select Tabular option and click Next to proceed.

STEP 5:

Now you can see Design the Table wizard. Select required fields from Available fields and clicked on details button to make these fields available in details part of a report. You can do data grouping as well using Group button. Click Next to proceed.



STEP 6:

Now you can see Choose the Table Style wizard. Select default style Slate and click Next. Finally you can see Completing the Wizard. Enter report name FirstReport and click Finish to complete the wizard.



Thats all. We are done with our First report in SSRS 2008. Report at design time will look like one shown below:



Click on Preview to generate the report:

When you have automated backup jobs running on your database server, sometimes you forget that they are even running. Then you forget to check to see if they are running successfully, and don’t realize until your database crashes and you can’t restore it since you don’t have a current backup.

That’s where email notifications come in, so you can see the job status every morning when you are sipping your coffee and pretending you are working.

SQL Server provides a built-in method of sending emails, but unfortunately it requires you to have Outlook and a profile installed on the server, which isn’t necessarily the ideal way to send an email. Thankfully there is another method, that involves installing a stored procedure on your server that will allow you to send email via SMTP.

You will want to edit one line in the stored procedure to put the IP address of your SMTP server:

EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields(“http://schemas.microsoft.com/cdo/configuration/smtpserver”).Value’, ’10.1.1.10′

Install the stored procedure into the master database, so it can be easily used from wherever needed.

Open up the SQL Server Agent \ Jobs list, and select the properties for the job you are trying to create a notification for:



Click on the Steps tab, and you should see a screen that looks like this:



Click the New button to create a new job step. We will use this step to send the email notification on success.

Step Name: Email Notification Success

Enter this SQL into the Command window as seen below. You will want to customize the email addresses and message subject to match your environment:

exec master.dbo.sp_SQLNotify ‘[email protected]’,'[email protected]’,'Backup Job Success’,'The Backup Job completed successfully’



Click OK and then click the New button again to create another step. This will be the failure notification step.

Step Name: Email Notification Failure

SQL:

exec master.dbo.sp_SQLNotify ‘[email protected]’,'[email protected]’,'Backup Job Failure,’The Backup Job failed’

Now the idea is to make the items follow a specific workflow. First click Edit on step 1, and set the properties as shown here:



What we are saying is that on success, go to the success step, and on failure, go to the failure step. Pretty simple stuff.

Now edit the second step, the one labled “Email Notification Success”, and set the properties as seen here:



We are saying that if the notification job is successful, then just quit the job without running step 3. If we don’t specify this, then we will end up getting two emails, one with success and one with failure.

Now edit the third step, the one labled “Email notification failure”, and set the properties as seen here:



Now your job steps should look like this:



You should now have email notifications in your inbox for either success or failure. 

Note: The stored procedure used in this article was found here.

This is the error message that I almost find everyday on forum. So, I decide to make this post to help the people who face this problem. Actually, the solution is very simple and I hope this tutorial below can help you.

First of all, you get this error message only if you are trying to connect to a SQL Server named instance. For default instance, you never see this. Why? Because even if we failed at this stage (i.e. error locating server/instance specified), we will continue to try connect using default values, e.g defaul TCP port 1433, default pipe name for Named Pipes. You may see other error message due to failure later, but not this error message.

Every time client makes a connection to SQL Server named instance, we will send a SSRP UDP packet to the server machine UDP port 1434. We need this step to know configuration information of the SQL instance, e.g., protocols enabled, TCP port, pipe name etc. Without these information, client does know how to connect the server and it fails with this specified error message.

In a word, the reason that we get this error message is the client stack could not receive SSRP response UDP packet from SQL Browser. It's easy to isolate the issue. Here are the steps:

1. Make sure your server name is correct, e.g., no typo on the name.
2. Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
3. Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
4. Make sure SQL Browser service is running on the server.
5. If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

If follow the steps above correctly, you should not see this error message anymore. Good luck.

If you're looking for Windows hosting, please check our site at http://www.asphostcentral.com.

Starting from 7th Nov 2011, ASPHostCentral.com starts to offer a hosting service located in our prestigious Amsterdam (Netherland) Data Center. For further information, please click here.

For all our new customers who wish to have their sites activated on our Amsterdam (Netherland) data center, you must indicate your request on our order form at https://secure.asphostcentral.com. For our existing customers, a one-time migration fee applies to all requests on our Amsterdam (Netherland) data center and please creates a ticket from our Help Desk to signal your interest.

Our new data center in Amsterdam provides businesses across Europe region. The Amsterdam data center is complemented by new points of presence (PoPs) in Europe that will provide millions of new users with a direct, dedicated path to our Network, providing lower latency and a superior end user experience.

Network Details

Our global network seamlessly integrates three distinct and redundant network architectures—Public, Private, and Data Center to Data Center—into the industry’s first Network-Within-a-Network topology for maximum accessibility, security, and control.

We leverage best-in-class connectivity and technology to innovate industry-leading, fully automated solutions that empower enterprises with complete access, control, security, and scalability. With this insightful strategy and our peerless technical execution, we have created the truly virtual data center—and made traditional hosting and managed/unmanaged services obsolete.

We are proud of our high speed connection. In fact, one of our most frequent compliments from our customers is how fast their site loads... read on and you will see why. When you combine high speed connectivity and high quality equipment, you get a fast and reliable web site. We have invested in the equipment and staff so you can rest assured that your site will load fast every time. We host sites all over the world, and with our multiple backbone connections, your customers will get through to your site fast.

For more details, please visit ASPHostCentral.com website.

There are many web hosting options available nowadays: free web hosting, shared hosting, dedicated server, and the list goes on. All these options serve the same purpose, which is hosting your content so that it can be accessed and viewed by people on the Internet. The major difference is how each is structured as well as the benefits they offer.

Let's have a closer look at each of them.


Free Web Hosting

What can be better than a free web hosting plan? This is a great option for someone who wants to create a homepage or small website to share with friends and family. While free web hosting has received criticism for its lack of features, security and customer support, there are a few reliable free web hosting providers that you can trust. However, keep in mind that free web hosting is more geared for giving you a taste of having and maintaining a small, personal website. If you want to establish a powerful web presence with an online business, you will need to consider a paid hosting service that offers more control, security and reliability. In other words, we do not recommend free web hosting to those who maintains a business, e-commerce related site. Apart from this, usually a free host provider will usually add banners, advertisements automatically on your site and this can make your site looks unprofessional.

Shared Hosting

As the name implies, shared hosting is an environment where you are sharing space on a web server with other users. As clients are sharing the cost of the server, this is the most affordable and popular solution for personal users and small businesses as companies to set up blog, e-commerce and other advanced applications. However, when hosting on a shared server, you are exposed to all the activities of your neighbors. If someone makes a huge scripting error, the entire server can suffer. If someone experiences a sudden burst in traffic, your site might run slower. If the server goes down, so does your website and ultimately, your business.

When selecting a shared hosting provider, you need to ask several important questions. Some questions are whether the server is overloaded, how many clients in a server, and what sort of server specifications that the server is running. You may also ask whether your site is on an exclusive application pool and if it is, it will certainly reduce the possible downtime on your site (which is caused by the other site on the server). One of the excellent shared hosting providers is ASPHostCentral.com. We do not and never ever overload our server. All the sites on our server are assigned its own application pool. We always monitor our server performance and all the sites activities on our server and this is to ensure that all the sites on the server are not causing issues to the entire server.

Dedicated Hosting

When your business takes off and requires more than the typical sharing server resources, it's time to move up to the dedicated server. Now you're in the big leagues with an entire server dedicated to your hosting needs. However, without any experience, succeeding with this hosting option is nearly impossible - those who require a dedicated server but don't know a thing about server administration can get by with managed hosting. In this scenario, the hosting service provider handles all the management tasks which frees you up to focus on other areas of the business. Keep in mind that a managed service requires is generally more costly.

What is the Best Hosting For You?

The best advice we can give about hosting is to know what you're getting into. Free services are geared for personal sites, shared hosting is suited for small businesses and a dedicated server is designed for larger hosting needs, yet is far more expensive. By knowing what your site requires, it will be much easier to determine which is the best solution

Applies to:

- Internet Information Services 7.o (IIS7)
- Microsoft Report Viewer Redistributable 2005

Symptoms:

- Unable to render ReportViewer on ASP.NET Web pages while running on IIS7.
- You have no problem viewing your reports when running on debug mode with your Visual Studio 2005.
- You are able to view your reports on Report Manager but not able to view them on IIS7.
- You encounter JavaScript error when loading your report page with ReportViewer. Image buttons such as calendar appear as red 'X'.

Cause:

- When the ReportViewer control is added to Web Form (.aspx), the Reserved.ReportViewerWebControl.axd httpHandler is added to System.Web section of the Web.Config file. In IIS7, it should be added under System.Webserver section.
- IIS7 Handler Mappings does not contain Reserved.ReportViewerWebControl.axd httpHandler, and therefore unable to render the ReportViewer elements needed by the JavaSript.

Resolution:

- Open Internet Information Services (IIS) Manager and select your Web application.
- Under IIS area, double-click on Handler Mappings icon.
- At the Action pane on your right, click on Add Managed Handler.
- At the Add Managed Handler dialog, enter the following: Request path: Reserved.ReportViewerWebControl.axd
Type: Microsoft.Reporting.WebForms.HttpHandler
Name: Reserved-ReportViewerWebControl-axd
- Click OK.

Reserved-ReportViewerWebControl-axd handler is now added to your Handler Mappings list. Notice that the following line has also been added to your Web.config file under the system.webserver's handler section:

<add name="Reserved-ReportViewerWebControl-axd" path="Reserved.ReportViewerWebControl.axd" verb="*" type="Microsoft.Reporting.WebForms.HttpHandler" resourceType="Unspecified"/>

Run your report again

With all the new features in SQL 2008 R2, here are the major ones getting all the press:

PowerPivot
Parallel Data Warehouse
Application and Multi-Server Management
StreamInsight
256 core support 

There is so much written on the ones above, I wanted to concentrate on talking about other new features in SQL 2008 R2.  So, in no particular order:   

1.       SMB support – SMB stands for Server Message Block and this protocol is now officially supported by SQL Server 2008 R2 and beyond.  This improvement has formalized the support status of placing SQL database files on SMB network file shares.  From Kevin Farlee, the owner of this feature in SQL Server: “This presents a better-together story with the work that Windows has done in Windows7/Server 2008 R2 to make the Windows SMB stack far more performant and resilient than in the past.  It is also a recognition that with the increasing acceptance of iSCSI, customers are viewing Ethernet as a viable way to connect to their storage.  Finally, it gives customers in consolidation environments a very simple to manage method for moving databases between servers without investing in a large SAN infrastructure.” 

2.       Increased Performance – there are very nice performance improvements, especially with the combination of Windows 2008 R2 and SQL 2008 R2.  The actual TPC-E measurements on have been audited and published.

3.       SYSPREP – Finally!  We can now create Sysprep versions of SQL Server environments, starting with SQL Server 2008 R2, but only for the relational engine.  My favorite thing about this piece is that it even works with HyperV images containing SQL Server 2008 R2.

4.       Report Builder 3.0 and Reporting Services – Too many great new features to talk about in a blog and the development team already has a great blog.  But my favorite is the Report Part feature where you can take an existing report and designate report items and data regions to save and reuse in other reports.  This can amount to a huge time savings for developing new reports.  Other customers tell me they like the improved Sharepoint integration and the performance improvements in Sharepoint.  But that is not all, there is Bing map support, spark lines, and shared data sets. 

5.       Master Data Services – for data consistency across heterogeneous applications. BOL link.

6.       SSIS - Bulk Inserts with ADO.NET provider are now possible, which is extremely nice because it used to do it a row at a time.  Now, if you check the box to “Use Bulk Insert when possible” then you can see vastly improved performance when it kicks in.

7.       Setup – integrated Sharepoint mode setup is vastly improved for both Reporting Services and Analysis Services.  See the link for Powerpivot for Sharepoint to get the instructions.

8.       Excel 2010 – new additions for databases:  slicers, data cleansing, AJAX data feeds, Odata feeds and named set improvements.  To create a Named Set in Excel, once you’ve created a PivotTable against an OLAP source go to the Options tab under PivotTable Tools, and select “Fields, Items, & Sets” à “Manage Sets” à “New…” à “Create Set Using MDX…”.  Another of my new favorites within PowerPivot in Excel is the new Data Cleansing ribbon.  This allows the users to do their own clean up, which will be essential when they combine data from disparate sources.  And you can also get an OData feed from a Reporting Services report.

9.       Database Compression - now supports Unicode.  If you have Unicode date types, like nchar and nvarchar, but the data contained within is normally single byte character sets, you will see significant space savings. 

10.   PHP 5 Driver – Version 1.1 of the PHP 5 driver has a list of new capabilities, allowing access to SQL Server 2005 and SQL Server 2008.  

New and changed Editions  (more details and pricing)
·         Data Center Edition – needed for machine with more than 8 physical CPU sockets plus other improvements needed for the top SQL Server projects. 
·         Parallel Data Warehouse Edition – Massively Parallel Processing (MPP) Edition of SQL Server targeted at data warehouses in the 10’s to 100’s of terabytes.  It is an appliance where you order the hardware and software together and it comes preinstalled and preconfigured.  The minimum installation is one rack so no, you cannot install it on your laptop to play with it.
·         Standard Edition – now has the capability to do backup compression. 

INSERT INTO #CaseInSensitive (cisText)
SELECT 'Abc'
UNION ALL
SELECT 'ABc'
UNION ALL
SELECT 'ABC'
UNION ALL
SELECT 'AbC'

If we need all records where column cisText value is ‘ABC’ then here is simple query

SELECT * fROM #CaseInSensitive
WHERE cisText = 'ABC'



Ops. We got all four records as output but we need only one records where cisText = ‘ABC’. But at the time of table creation we have not set any case sensitive collation. Don’t worry; here is a query which can help us.

SELECT * fROM #CaseInSensitive
WHERE cisText COLLATE SQL_Latin1_General_CP1_CS_AS = 'ABC'


During table design we can also make a column case sensitive

CREATE TABLE #CaseSensitive (csId INT identity(1,1), csText varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS)

One of the repetitive tasks that DBA need to perform is create maintenance plan for database. Maintenance plans enables you to automate maintenance activities for a database, backups, db integrity checks and index maintenance tasks. We can easily create a maintenance plan using a wizard in SQL Server 2008 R2

You can use the following steps to create a maintenance plan

1. Select the Maintenance Plan Wizard from the context menu as shown below

2. You can specify a name and description for the plan and select the desired scheduling options

3. Select next to see the maintenance tasks that you want to perform

4. Select the Maintenance tasks order

5. Select the database for the backup task.

6. Select the reporting option for the plan like write log to a specific location or send an email or both.

7. Click finish to create your plan, while the plan is being created you will get the following status dialogue

8. You can see the created maintenance plan in object explorer when you double click the Backup Plan from above window, you can get the designer

This topic talk about how the SQL Server 2008 help your company in cost saving. But we are describing only a short description about SQL Server 2008. So, if you seek more information about SQL Server 2008, you can choose ASPHostCentral as alternatives. You’ll get the best service at an affordable price. Only with @ 4.99/month, you can directly get the services you want. So, what are you waiting for? Try it!!

Information management, access, and delivery platforms such as Microsoft's SQL Server 2008 lie at the heart of many organizations' IT infrastructures, and these platforms are available from a number of vendors. But one of the ways in which Microsoft SQL Server 2008 stands out from other vendors' offerings is the sheer number of features the platform includes that enable your organization to cut its IT related costs significantly.

Here are eight ways that SQL Server 2008 can help your organization save money:

1. Reduce your hardware, power and licensing costs though virtualization
Using Microsoft's Hyper-V hypervisor it's easy to virtualize SQL Server 2008 servers and run multiple SQL Server 2008 virtual machines on a single physical machine. That means less physical hardware needs to be purchased and maintained, you spend less on power and cooling, and you free up valuable data center space. You can also make significant savings on licensing costs too. That's because you need only one Windows license and one SQL Server 2008 license per physical processor running in each host server, regardless of the number of virtual machines that run in them.

2. Cut data and backup storage costs with compression
SQL Server 2008 can save you money on disk hardware, reducing your storage requirements by as much as two-thirds using data compression to compress tables, hardware indexes, and partitions. It can also reduce backup storage requirements (and costs) significantly using backup compression to store backups more efficiently — while actually speeding them up by reducing the number of writes needed to complete them.

3. Slash your administration overheads using automation
Freeing IT staff from routine database maintenance tasks by using automation saves money and enables IT staffers to be redeployed to more productive tasks. The SQL Server Agent service can be set up to execute many scheduled administrative tasks such as index management and back up automatically, notifying you in the event of a problem. Alternatively, you can automate your own complex tasks by using Microsoft's PowerShell command-line shell to write custom management scripts. You can also use SQL Server 2008's Policy-Based Management to implement and maintain policy compliance for all your databases and servers — or bring them into compliance — with a minimum of manual intervention.

4. Reduce purchasing requirements by optimizing hardware with the Resource Governor
Contention between applications for server CPU and memory resources can lead to application performance that is sluggish, or that varies widely at peak usage times — especially on older hardware. The Resource Governor, new to SQL Server 2008, helps solve this problem by enabling you to give priority to your business-critical applications and to limit the resources that other workload groups can be allocated. This helps eliminate runaway queries and ensures that your business-critical applications always offer acceptable performance levels without the need to spend money upgrading your severs.

5. Save money by consolidating your systems
Using SQL Server 2008's multi-database support you can consolidate databases with similar security and compatibility requirements into a single SQL Server 2008 instance, using the Resource Governor to ensure that all your databases run according to your performance requirements. If you have databases with different security or compatibility requirements you can also cut costs using SQL Server 2008's multi-instance support to consolidate up to 50 instances of SQL Server onto a single physical server. In addition to reducing hardware costs you can make significant license cost savings this way because only one SQL Server license is needed for each processor, regardless of how many SQL Server 2008 instances are installed.

6. Reduce lost revenue from downtime thanks to high availability
System downtime means lost revenue for many organizations, and in many cases every second counts. SQL Server 2008 has been developed using a number of technologies that aim to minimize both planned and unplanned stoppages, including rock solid 16-node clustering, database mirroring, peer-to-peer replication, online backup and restore, and support for hot-swapping failing hardware.

7. Enterprise class Business Intelligence (BI) at no extra cost
Good decision making requires access to the right information, and good BI systems rarely come cheap. But SQL Server 2008 Enterprise Edition saves you unnecessary expenditure because it includes a full BI and integration platform to go with the enterprise-class database system at no extra cost. Using SQL Server 2008 Integration Services, Analysis Services, and the database engine it's even possible to build a complete data warehouse solution using the SQL Server 2008 platform.

8. Save on your security infrastructure using the secure SQL Server platform
Protecting your systems and data can be extremely expensive, and the cost of losing your data or having it compromised by malicious intruders can be even higher. SQL Server 2008 has been designed from the ground up to be secure, offering a minimum attack surface to hackers. It also includes built-in support for transparent data encryption (without expensive custom client application development) to help keep your data safe and minimizing the risk of revenue loss or liability costs from compromised data.

SQL Server goes beyond relational data structures, supporting XML, spatial, and unstructured data. That means that if you need to support these kinds of data structures as your organization develops you can do so without any additional outlay. It's an example of how, while SQL Server 2008 can help your business reduce its IT costs immediately, it's also been designed to continue saving you money well into the future as well.

Top Reasons to trust your SSRS website to ASPHostCentral.com

What we think makes ASPHostCentral.com so compelling is how deeply integrated all the pieces are. We integrate and centralize everything--from the systems to the control panel software to the process of buying a domain name. For us, that means we can innovate literally everywhere. We've put the guys who develop the software and the admins who watch over the server right next to the 24-hour Fanatical Support team, so we all learn from each other:

- 24/7-based Support - We never fall asleep and we run a service that is operating 24/7 a year. Even everyone is on holiday during Easter or Christmas/New Year, we are always behind our desk serving our customers
- Excellent Uptime Rate - Our key strength in delivering the service to you is to maintain our server uptime rate. We never ever happy to see your site goes down and we truly understand that it will hurt your onlines business. If your service is down, it will certainly become our pain and we will certainly look for the right pill to kill the pain ASAP
- High Performance and Reliable Server - We never ever overload our server with tons of clients. We always load balance our server to make sure we can deliver an excellent service, coupling with the high performance and reliable server
- Experts in SSRS Hosting - Given the scale of our environment, we have recruited and developed some of the best talent in the hosting technology that you are using. Our team is strong because of the experience and talents of the individuals who make up ASPHostCentral
- Daily Backup Service - We realise that your website is very important to your business and hence, we never ever forget to create a daily backup. Your database and website are backup every night into a permanent remote tape drive to ensure that they are always safe and secure. The backup is always ready and available anytime you need it
- Easy Site Administration - With our powerful control panel, you can always administer most of your site features easily without even needing to contact for our Support Team. Additionally, you can also install more than 100 FREE applications directly via our Control Panel in 1 minute!

Happy hosting!