Monday, December 15, 2014

Honolulu learning event: Basic SQL

You are welcome to attend my course at the Pacific Center for Advanced Technology Training (PACTT). Please register at the PCATT.org site.

Summary:

Databases, organized repositories of information, have become indispensable in today's world. In this introductory course you will learn about databases and the basics of Structured Query Language (SQL) including sorting, distinct, and aggregate functions and grouping of data. All SQL statements will initially be written from one table. Most practical, modern and relational databases will include a large number of tables. 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.


New in 2015: You will also get an introduction to Power Shell.

Dates: (Section 1) Section 1 - Feb 17 - Mar 12, 2015; TTh; 5:30 pm - 7:30 pm; 10 mtgs.
Venue: Honolulu Community College;

Note: SQL Server 2012 Express will be used and a knowledge of SQL Server is not assumed. Students will be using the free version.

For details : Access http://pcatt.org/index.php/search and look up the course or write to:
Hodentek@live.com with course name in the Subject line.

Friday, December 12, 2014

Learn Querying SQL Server 2012 using LinqPad - Part 2

In Part 1 of this series I showed how you can connect to your instance of
SQL Server on your computer. If you have not seen this please go here:
http://hodentekmsss.blogspot.com/2014/12/learn-querying-sql-server-using-linq.html

In this post you will learn how the tables in the SQL Server database
AdventureWorks2012 are represented on the LinqPad and learn to run a few
statements or expressions to fetch information on the SQL Server to display in LinqPad.

This next image shows how the tables and other objects in SQL Server are
displayed grouped in LINQPad.

All the tables in HumanResources are grouped and you can expand to see
the individual tables in LinqPad. Also all the Stored procedures, Functions
and Views related to the HumanResources are shown under HumanResources in
LinqPad but they are shown under the object nodes Programmability |
StoredProcedures; Programmability | Functions and non-system Views in SSMS.

Evaluating Expressions in LinqPad

When you launch LinqPad and connect to SQL Server Instance as shown in the
previous post, the LinqPad UI appears as shown (only top part shown here).


Here the Green arrow at left (right pane) is a button which executes the statement or
query you enter the pane and it will show Execute(F5) if you hover over the
green button.

The one next to it shown in grey is the Stop button and stops the execution.  The next two icons when clikced displays the results either in rich text (Ctrl+Shift+T),or data grid (Ctrl+shift+G).

The Language drop-down has a number of options and to get the correct result
you should indicate whether it is C# Expression(appears as default) or the
other types shown here.


The Connection drop-down comes up with default <None> which means the query
will not be execcuted since it is not connected despite the fact that you see the connection on the left. When you click on the handle (downward pointing arrow) you will see that you can connect to the database shown in the left.


Querying examples:

1. Click the drop-down for Connection and choose AdventureWorks2012
2. Write an expression to evaluate
1+2+3  as shown and click the green arrow to execute the expression and you
will see the result in the results pane that gets displayed.


This is not really SQL related but the engine evaluates it.

3. Let us say we want to get all the Employees information from the
Employees table in the Human Resources group. Then create a new query by
clicking on the + tab right next to Query1 and enter

Employees

as shown(this will be Query2). Don't forget to click the green arrow to execute. You get the following displayed.


As far the results you have other options to review as shown in the next composite image


4. Let us do some filtering. Let us we need only two columns from the
Customers table
Type in C#Expression the following"

from c in Customers
select new {c.CustomerID, c.StoreID}

Click the green arrow for query execution and you will see the following:



5. Let us say you want to filter further to display fewer rows then you can type in the following and click execute.
from c in Customers
where c.CustomerID <10
select new {c.CustomerID, c.StoreID}


Click Execute and the following will be displayed:

6. Let us say you want it sorted in the increasing order of StoreID then you can do the following:
Type in the following:

from c in Customers.OrderBy(c=> c.StoreID)
where c.CustomerID <10
select new {c.CustomerID, c.StoreID}


Click Execute and after some processing you will see the following:


You have learnt in this post how to recognize the UI and how to run a few simple queries against the SQL Server 2012 database.

In the next Part you will learn a few more topics
 

Thursday, December 11, 2014

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:

 
 

Tuesday, December 9, 2014

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.

    Saturday, December 6, 2014

    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.

    Thursday, November 27, 2014

    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.