Sunday, December 29, 2013

SQL Training in Honolulu: SQL Server Reporting Services 2012

This is a comprehensive, hands-on course. The above course will be offered as a non-credit course by the Pacific Center for Advanced Technology Training

 Course summary:
SQL Server Reporting Services 2012
Report writing is one of the most important IT related jobs in an enterprise. 
Among the BI stack components the component that provides most employment is Reporting 
Services.

This course is 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.

You will start off learning the details of installing SQL Server 2102 
Developer edition and configuring Native mode Reporting Services. For 
authoring reports and interacting with reporting services you will also learn 
how to install and work with Report Builder 3.0. This is essential for 
deploying authored reports to report sever.

Report Manager the web front end is an essential part of Reporting Services 
2012 and you will learn a comprehensive set of tasks that you can carry out.
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 is 
essential for managing reports.

Many applications depend on embedding these reports so that the user has the 
opportunity to view these reports. To this end, an understanding of how to 
embed report viewer controls in windows and web projects is essential. You 
will learn the basics of creating these applications.

During each meeting the students will be assessed for their knowledge, 
competency and proficiency in installing, configuring, authoring, deploying, 
and managing reports.

The hands-on exercises will draw largely from the authors first and second editions of his book on SQL Server Reporting Services 2012.

 

and the first edition related to SQL Server 2008


Course schedule: 
Feb 3 - Mar 17, 2014; Mondays and Wednesdays; 5:30 pm - 8:30 pm; 12 meetings

Location:
Honolulu Community College

Friday, December 27, 2013

SQL Training in Honolulu: Introduction to Structured Query Language (SQL)

This is a comprehensive, hands-on course.

The above course will be offered again as a non-credit course by the Pacific Center for Advanced Technology Training

Course summary:
Databases, organized repositories of information, have become indispensable in today's world. In this introductory course you will learn about relational databases and the basics of Structured Query Language (SQL) including sorting; grouping result sets; using DDL, DML, DCL, and TCL. All SQL statements will initially be written for one table. Most practical, modern and relational databases will include a large number of tables and SQL queries have to access information from several tables. This course will then introduce you to querying more than one table. With this skill you will be able to query two or more tables in a database. This is a hands-on course which will take you from installing SQL Server 2012; learning the ins and outs of SQL Server Management Studio and of course a full dose of SQL.

The course covers most of the requirements to take Microsoft’s latest certification (98-364), the Microsoft Technology Associate.  During each meeting the students will be assessed for their knowledge, competency and proficiency in SQL.

Schedule:
Feb 4 - Mar 6, 2014; Tuesdays and Thrusdays; 5:30 pm - 7:30 pm; 10 mtgs

Thursday, December 26, 2013

Deploying DAC to SQL Server 2014

The acronym DAC in SQL Server has many different meanings as in the following bullet list:
  • DAC - Data-tier Application
  • DAC - Dedicated Administrator Connection
  • DAC - Data Access Components
  • DAC - Data Application Component
This post describes deploying a Data-tier Application extracted from SQL Server 2012 Express database to SQL Server 2014 CTP1 (should work with CTP2 as well). The two servers are on two partitions on a x64 bit Windows laptop, with SQL Server 2012 on Windows 7 Partition and CTP1 on Windows 8 Preview partition.

There are no issues for this deployment and the next few images shows the stages of deployment wizard while deploying to SQL Server 2014 CTP1. We start with a .dac file created on Windows 7 partition and copied over to the desktop on Windows 8 partition.





 
The steps are very clear and you may also review the following for more detailed discussion of DAC with earlier versions on older OS.


https://www.packtpub.com/article/working-with-data-application-components-sql-server-2008-r2
http://hodentekmsss.blogspot.com/2010/07/sql-server-2008-r2-and-data-tier.html

Friday, December 20, 2013

Querying SQL Server 2012 with Power Query

Power Query is a great way to access data and Power View is a great way to view a report.

Some details of Power Query can be found here,
http://hodentek.blogspot.com/2013/12/what-is-microsoft-power-query.html
http://hodentekhelp.blogspot.com/2013/12/what-is-difference-between-ms-query-and.html

Power View is covered in detail in my book,


This post shows how you may connect to SQL Server 2012 Express and query the databases therein using Power Query.

Step 1. If you have not installed the add-in do it now. Get it from this link here,
http://www.microsoft.com/en-us/download/details.aspx?id=39379

Step 2. Launch MS Access 2010 ( or your version). You should see in the splash screen that the add-in is also loaded.

Step 3. Anyway verify it from the Excel Options window that you can access from File menu as shown.

Step 4. Click menu item Power Query followed by From Database as shown.


Step 5. Click From SQL Server Database. In the window that pops-up type in the server name as shown. There is no browse feature here, you have to manually enter as shown.



Step 6. Click OK after entering SQL Server name and the following window pops-up.


Step 7. You can choose encrypted or unencrypted for you connection. Here Unencrypted is chosen.

Step 8. Click Save. The query window appears with a default name Query1 as shown. Here Northwind has been expanded to show the tables.


Step 9. Click on Categories as shown. The table content gets shown on the right side.


Step 10. You can further process the query using the built-in controls which can be displayed by right clicking the corner of the table as shown.


Step 11. The chosen options updates the steps, each time you make a choice a step is added as shown. Note that this next query is from AdventureWorksLT2012 database.


This will be saved to the workbook which you may name appropriately by completing this window. Right now there is no link to publish this result except saving it to the SkyDrive and use it later.

Enjoy

Saturday, December 14, 2013

Query Designer in SQL Server 2012 Express

 Running queries is one of the basic tasks that you undertake. You have two options in SQL Server 2012 Express (also in all other versions), use T-SQL and execute the statements in the query pane of SQL Server 2012 or use the query designer. You will be able to design the query in the designer but you will not be able to execute inside the designer. Once you design the query in the editor the T-SQL code that gets generated is copied to the query pane. Once the T-SQL code is in the pane you can execute (using the Toolbar item EXECUTE(!) to run the query

There are two ways you can bring up the query designer

Step 1. Open a Query pane by right clicking either the Server Node or right clicking one
of the user databases as shown.
 SERVER NODE


USER DATABASE NODE

Step 2. Once the query pane is open, click inside the query pane. The main menu
changes
to display the Query menu item as shown.

 


Step 3.Click Query menu item to display a drop-down list and click on Design Query in Editor or Ctrl+Shift+Q
 

This should bring up the designer as shown.


Step 3b. Another way is to right click inside the query pane and choose Design Query in Editor as before.