Hands-on learning event in Honolulu -SQL Server Reporting Services 2012

This hands-on course will provide you with the ability to create cutting edge reports using Microsoft's latest reporting services product, SQL Server 2012.

The course is offered by the Pacific center for Advanced technology Training: http://pcatt.hawaii.edu/home

Report writing is one of the most important IT related jobs in an enterprise. This course if for you if you would like to master report writing/authoring using SQL Server Reporting Services. The course begins with a presentation of essential technical background, after which you will work through guided hands-on exercises. At the end of the course you will have gained:
• A solid understanding of how to interact with SQL Servers
• Knowledge and experience installing and configuring reporting Services
• A working knowledge of using Report Builder to author reports and deploy them to report server
• A working knowledge of Report Manager's ability to carry out administrative tasks related to all aspects of reports; creating shared data sources, folder and folder hierarchies on the report server, and report models; managing security and subscriptions; and configuring data driven subscriptions
• An understanding of how to embed report viewer controls in windows and web projects.


Venue: Honolulu Community College Honolulu Community College
874 Dillingham Blvd Honolulu, HI 96817-4598 Phone: (808) 845-9211
Official Website: honolulu.hawaii.edu/

Section 1 - Mar 31 - May 7, 2015; TTh; 5:30 pm - 8:30 pm; 12 mtgs

I will be using my latest book on SQL Server Reporting Services for this course:

 

More here:http://hodentekmsss.blogspot.com/2014/11/things-you-learrn-hands-on-in-my-book.html

 

Things you learn hands-on in my SQL Server Integration Services book

With over 24 hands-on exercises this book covers,

A detailed explanation of designing over 20 important SSIS tasks using hands-on examples.

Use of Visual Studio 2005's IDE as related to SSIS development.

Working with single and multiple tasks; working with more than 20 different tasks.

Connecting to Microsoft Access, Flat Files, Excel Spread Sheets, and third party vendor products such as Oracle.

Linking data source and data destination to a task.

Learn sending emails and understand FTPing with SSIS.

Transform data from a source going to a destination.

Using the scripting support provided by the IDE.

Learn tasks such as web service and XML new in SQL Server 2005.

Monitor the data as it is being transported, also new in SQL Server 2005

Understand event bubbling and Learn to handle events

Packed with over 24 hands-on examples; no experience of SQL Server Integration Services required. Knowledge of DTS helpful but not essential.

Learn Querying SQL Server using LINQ - Part 1

The easiest way to do this is to use the popular tool, LINQPAD. It is a great tool
and you can download a free version here:
http://www.linqpad.net/

If you want a brief intro to LINQ review this link:
http://hodentekhelp.blogspot.com/2014/12/what-is-linq.html

In this post we will see how to connect to the SQL Server on your Computer. LINQPAD does a lot more than querying, but for now let us limit ourselves to the immediate task.

Download LINQPAD. It is somewhat like WYSIWYG. You create the query or an expression in LINQPAD and the click of a button executes the query/expression/statement etc. LINQPAD brings the whole of .NET Framework to a very easy interface where you interact. It is also a small program and does not slowdown whatever you are doing.

The specifics of connecting to SQL Server

Download LINQPAD and launch the program which typially opens up as shown (don't be
disappointed at this minimal screen, lot more hides behind).

Now click on Add Connection in the top left pane. The Choose Data Context window

is displayed as shown.

 

 

Of the two panes, let us concern ourselves with the top panel. Assume the Default(LINQ to SQL). Click Next. The LINQ to SQL Connection window is displayed as shown.

 

In the Provider field you have various radio button options and you can connect tot SQL Server, two versions of SQL Compact Edition and SQL Azure. Accept the default SQL Server and that is what this post is about.

• Since our SQL Server Instance name is Hodentek8\RegencyPark enter this here.(SQL Server 2012)
• Log on details default is OK because that is what we chose during installation.
• Also accept that we want to display all in a tree-view
• For specifying new or existing database, choose Specify new or existing database and click on the drop-down to reveal the databases on the local instance as shown.

 

Choose Adventureworks2012. .Well, well you could include production data.

Now the LINQPAD4 interface changes as shown. Accept all other fields and click the Test button.
You should get a connection successful message as shown.

 

Click OK. Well the LINQPAD4 is  now connected to SQL Server 2012 instance on your computer.

In the next post we look at how to use this tool.
Learn querying SQL Server here:
http://hodentekmsss.blogspot.com/2014/12/learn-querying-sql-server-2012-using.html

 

Regarding my books on Microsoft Business Intelligence

Regarding the books

This app (which also happens to be my first app) brings together details of my Microsoft Business Intelligence related books from 2007 to 2014 based on the numerous articles I wrote to various online forums from 1996 to 2006. The focus of all these books is really Microsoft SQL Servers and Microsoft Visual Studio programming IDE. Also the books were written so that even beginning level readers can profit a great deal so that their learning is accelerated.

The first book on SQL Server Integration Services Using Visual Studio 2005 provides you with a basic knowledge you should have before you move on to more advanced topics. It is a step-by-step, hands-on guide that takes you directly to the Visual Studio IDE where you learn to work with projects rapidly and efficiently. You get the first hand experience of connecting to various data sources and transform them in many ways.

 

The second book Learning SQL Server 2008 Reporting Services is ideal for beginners who want to spin-out reports from a variety of data sources and deploy them to report servers. The book is replete with step-by-step, hands-on exercises where the users can hone their skills.

The third book Microsoft SQL Azure: Enterprise Application Development was written at the time Microsoft seriously entered the Cloud Arena. Microsoft took its flagship SQL Server Technology to the Azure Cloud. The books gives you a comprehensive knowledge of how to work with SQL Azure in the cloud from scratch to finish.

The fourth book Microsoft Visual Studio Lightswitch Business Application Development is a jump start, step-by-step guide to take your data out of data sources and present them to the stake holders be it on desktop; the web or the Cloud. The book does not require prior knowledge of VS Lightswitch or Visual Studio.

The fifth book Learning SQL Server Reporting Services 2012 is a revised edition and a deals with both Native and SharePoint Integrated modes of Report Servers. This covers every aspect of the first edition(second book) with lots of added material to include development in the intervening years.

 

As the author of these Microsoft Business Intelligence related books I have great confidence in their usefulness. I would love to hear from you. Email me at: hodentek@live.com.

Things you learrn hands on in my book on SSRS 2012

With over 65 hands-on exercises

• Install SQL Server 2012; SharePoint Server 2010; and configure Reporting Services for both native and SharePoint Integrated mode on Windows 7.
• Install SQL Server Data Tools and author reports both local and remote. Deploy reports to report servers.
• Develop desktop and web applications using Report Viewer Control.
• Author different kinds of reports with the latest gadgets working with Report Builder 3.0
• Manage access to the report server; report scheduling; report delivery; report uploads and downloads with Report Manager
• Create Tabular Models with SQL Server Analysis Services and deploy them to SQL Server Analysis Server using SQL Server Data Tools.
• Author Power View Reports from your SharePoint Site and export them as PowerPoint Files.

How to overcome error while importing the SQLPS module into Powershell?

In the previous post you learnt how to use SQL Server PowerShell Provider using the SQLPS.exe on your computer.

You could also run the SQL Server PowerShell Provider by importing the SQLPS module into PowerShell. It may produce the following error as shown:
--------------------
PS C:\Windows\system32> Import-Module “sqlps” -DisableNameChecking

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' fa

iled with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' fa

iled with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' fa

iled with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' fa

iled with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' fa

iled with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with

the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with

the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with

the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with

the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with

the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)  Now how do you overcome this error.

The error is telling that the RPC server is unavailable. However you would notice that this service is started automatically on your machine and in fact there is no Start/Stop for this service.

 

 

However you notice that the RPCLocator service is manual and it can be started and stopped. Start this service as shown.

 

 

Now Import the module again from PowerShell prompt as shown in the next listing:

 
PS SQLSERVER:\> Import-Module “sqlps” -DisableNameChecking

PS SQLSERVER:\>

As you can see the exception is not raised.

Now you can happily look at the SQL Server as shown:

 

PS SQLSERVER:\> cd SQL
PS SQLSERVER:\SQL> dir

MachineName
-----------
HODENTEK8

PS SQLSERVER:\SQL> cd LocalHost
PS SQLSERVER:\SQL\LocalHost> dir
Instance Name
-------------
REGENCYPARK

Accessing SQL Server via SMO using PowerShell - A quick workout

In order to understand SQL Server the surest way is to get to understand the SQL Server Management Objects known by its acronym 'SMO'. You can programmatically access and manage SQL Servers with this object model.

I have described with code listing as to how you may access Azure SQL Database in my comprehensive Azure SQL Database related book.
"A step-by-step procedure to connect to SQL Azure using SMO" is described on page 119, Chapter 3 of this book.
Microsoft SQL Azure: Enterprise Application Development, Packt Publishing,  Dec 2010.

SMO is the foundation using which the SSMS was built. The name space for the SMO is Microsoft.SqlServer.SMO.

In a previous post, I showed you how to run PowerShell in SQL Server Management Studio. Here is a quick screen shot to refresh your memory.

As you can imagine the SMO object model is quite large and starts off with the Server at the top. This image shows only a part of this object model.

You should be able to access the complete model at this link:
http://msdn.microsoft.com/en-us/library/ms162209.aspx

In order to access SMO using PowerShell you need to load the assembly as shown in the next image.

With the following you can get all the members as shown.

Once you do that you can access the top-level object, the Server.

SMO is object based and hierarchical. You define the SQL Server as a new object using the next listing where "servername" is your computer or the server and instance name is the SQL Server instance. In the present listing it is "Hodentek\RegencyPark"
-----------
PS C:\Users\Jayaram> $sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "Hodentek8\RegencyPark"

Since you defined  the server, you can find its version as shown in the following listing:
----
PS C:\Users\Jayaram> $sqlServer.version
Major  Minor  Build  Revision
-----  -----  -----  --------
11     0      2218   -1

---------------
The next two lines of code finds the status of the server as well as the root directory of the instance. 
-------------
PS C:\Users\Jayaram> $sqlserver.status
Online
PS C:\Users\Jayaram> $sqlserver.RootDirectory
c:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL
----------
Accessing the databases:

Databases are also objects and you need declare a variable for them as shown here:
----
PS C:\Users\Jayaram> $sqlDatabase=$sqlServer.Databases
----
The above declaration will not display a response but you have indeed declared the variable $sqlDatabase
Since there are more than one database, you need to get information from this collection as shown in the next listing:
-------------
PS C:\Users\Jayaram> foreach($sqlDatabse in $sqlServer.Databases){$sqlDatabase.name}
AdventureWorks2012
master
model
msdb
ReportServer$REGENCYPARK
ReportServer$REGENCYPARKTempDB
tempdb

These are the databases in my SQL Server Instance named RegencyPark.

In a future post we will see how to query database and other tasks