HodentekMSSS

Friday, August 19, 2016

Hands-on Learning Event in Honolulu 1 : Introduction to Structured Query Language

The above course will be offered again for the fourth time (Oct 4 - Nov 3, 2016) as a non-credit course by the Pacific Center for Advanced Technology Training.

Course summary:

Databases, organized repositories of information, have become indispensable. Knowledge of databases is a must for professionals and in fact even more relevant since the emergence of Big Data in today's world.

In this introductory course you will learn all 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 2016; learning the ins and outs of SQL Server Management Studio and of course a full dose of SQL. You will be both coding as well using Graphical User Interface during this training.

During each meeting the students will be assessed for their knowledge, competency and proficiency in SQL.

New this session:

Microsoft SQL Azure Database: SQL Server in Microsoft Cloud
Windows PowerShell for SQL Server
SQL Server 2016

My sincere thanks and best wishes to my students in the previous meets of this course.

PowerShell is written in .NET code (C#), not true anymore

The great productivity tool, PowerShell has entered the Open Source community (both Linux and Mac) according to an article that appeared in redmondmag.com.

This is what the Inventor of PowerShell said about this move:

"PowerShell on Linux is now designed to enable customers to use the same tools, and the same people, to manage everything from anywhere," Snover said. The initial ports are available on Ubuntu, Centos and Red Hat, he noted. "PowerShell also now runs on Red Hat Linux and Mac OS X with additional platforms planned for the future. The "alpha" builds and source code are now on GitHub
"

IT pros can use this task-based scripts to provide automaton support to Windows OS and all the other Windows Products.

PowerShell skills are highly marketable and now even more.

Read all my PowerShell posts here:
http://hodentekmsss.blogspot.com/search?q=Powershell

Wednesday, August 17, 2016

Update your SQL Server Data Tools to the latest

SQL Server Data Tools is a modern development tool which can be used to build:

SQL Server Relational DBS
Azure SQL Databases
Integration Service Packages
Analysis Services Models
Reporting Services Reports

You can, not only build you can also deploy SQL Server content with same ease as you do in Visual Studio. If you already have Visual Studio 2015 Community installing SSDT will add full set of SQL Server tools into existing Visual Studio. If there is no Visual Studio, a shell Visual Studio programe will be installed which will have only SQL Server related BI templates.

You will have a stirling exprience if you all ready have Visual Studio 2015 Community which happens to be free as well. This is highly recommended.

This post is related to the July 7, 2016 update. The new version is 14.0.60812.0.

Here are the older versions on my computer:

SSDT_01

If you already have Visual Studio 2015 Community, you will have received a notification which will show the updates available for you as shown:

Executing a stored procedure in dynamic SQL

Stored Procedures can be executed with a command or, using sp_executeSQL and the defined procedure in the context of the database.

Here is a stored procedure in the pubs database (just the name of the database in the instance of SQL Server 2016 is named TestSQL. It has everything that pubs has except for the name).

Well, pubs database may not be available on SQL Server 2016 and you can use any other database.

I am jsut taking the example of the stored procedure as shown here:
---------------

USE [TestPubs]
GO

/****** Object: StoredProcedure [dbo].[reptq2]    Script Date: 8/17/2016 11:37:46 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[reptq2] AS
select
    case when grouping(type) = 1 then 'ALL' else type end as type,
    case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id,
    avg(ytd_sales) as avg_ytd_sales
from titles
where pub_id is NOT NULL
group by pub_id, type with rollup
GO
---------------------
The parameter is the return integer. There is no IN parameter.

The following statement executes the stored procedure as a dynamic sql.

---------------------
declare @proc nvarchar(25);
set @proc='dbo.reptq1'
--exec @proc
exec sp_executeSQL @proc
--------------

Sometimes instead of the procedure being defined with the schema as in the above, it could be just the name of the procedure. However, the statement needs to be executed in the context of the database. There are no input parameters and if there are any, they should be declared and set.

R interactive using Visual Studio 2015 Community Update 3 to access SQL Server

R GUI is an interactive window from R Foundation.

When you install Visual Studio 2015 Community Update 3 or install Microsoft R Client you can bring up R Interactive within Visual Studio IDE. This is really very nice and you have the full access to intellisense that makes it very easy to use the interface. You would really appreciate if you had the experience of using the Open Source R.

Here are some screen shots of its usage in Visual Studio 2015 Community Update 3. You launch R Interactive from R Tools menu in Visual Studio 2015 Community Update 3.

This brings up the message that you have logged into your Microsoft Account.

If you are logged in you get to display the R Interactive pane as shown.

This looks similar to the one from Open Source, but wait. You can load the library RODBC straight away and you get the functions that you can use, thanks to intellisense.

Now I try to establish an ODBC connection to my SQL Server 2016, voila, again intellisense to the rescue. In Open Source you have to look up some documents to get these.

OK, I have a User DSN to my SQL Server 2016 called 'HSQL' and I try to call.

Well, I have the full details of my User DSN.

This is the way to go if you are interested in productivity.

More in my future posts on this blog and the other:
http://hodentekMSSS.blogspot.com

Read the posts related to this post here:

http://hodentekhelp.blogspot.com/2016/08/what-is-needed-to-leverage-r-from.html

http://hodentekhelp.blogspot.com/2016/07/how-are-user-dsn-system-dsn-and-file.html

http://hodentekmsss.blogspot.com/2016/08/viewing-tables-on-sql-server-2016.html

http://hodentekmsss.blogspot.com/2016/07/you-need-this-to-connect-to-sql-server.html

Saturday, August 13, 2016

Viewing tables on SQL Server 2016 Developer using R GUI

In this post we will use ODBC Connectivity to connect to SQL Server 2016. We create a new ODBC UserDSN or modify an existing ODBC DSN to connect to the 'pubs' database on SQL Server 2016 (the modification in the link changed the database from 'master' to 'Adventureworks2014').

You need to user the RODBC package to connect to SQL Server if you are using the R from CRAN.

From R GUI (x64bit) click Packages and choose to install packages.

rodbc_00

You may be directed to a list of mirror sites from where you can get the package.

rodbc_00_1
Click "OK" to download the package to your computer.

I had earlier chosen California site and the package has RODBC.

robdc_01

Click on it and click OK.
The package gets downloaded to local file system.

rodbc_02

Load the library for RODBC so that you can use it in the session.

rodbc_03

Connect to the ODBC User DSN "HSQL" you created earlier. Then take a look at all the tables (this is a long list and only few pieces are shown).

rodbc_04

You can also use sqlTables() and sqlFetch() functions with a table name (such as 'employee' in this case) to get relevant items as shown.

rodbc_05

In case you are using a DSN with a username and passwordd, you can connect to it as shown here:
odbcConnect("dsn", uid="user", pwd="***")

You can close the conneciton with:
odbcClose(ch)

You will not get a response to the above.

It is quite easy if you have the DSN and the RODBC library.

Wednesday, August 10, 2016

Importing a data-tier application to SQL Server 2016

You have seen how to export a data-tier application of a database in SQL Server 2012. In fact, we exported one of the oldest of Microsoft's Sample Databases, the TestPubs. We also saw some limitations in this process of export due to incompatible data, although we have not fathomed as yet what type of data is incompatible.

When the process of export succeeds you create a .bacpac file which can be saved either to a local drive or to Microsoft Azure SQL Database. In our previous export we exported a TestPubs.bacpacfile from TestPubs database to the local drive.

In this post, you will see how you can import it into a higher version of SQL Server, SQL Server 2016.

Start up the named instance of SQL Server 2016. You can see that the server has no user database presently except the one we created recently (AdventureWorks2014).

ImportDT_07

Make a right click on the Databases node in SQL Server 2016 as shown.

ImportDT_00

Click on Import Data-tier Application... submenu to launch the import wizard's Introduction page as shown. Read the instructions on this page.

ImportDT_01

Click Next to display the Import Settings page. Click on the ellipsis button to browse the default page where the .bacpac files are stored as shown.

ImportDT_02

The file enters the processing by the wizard as shown.

ImportDT_03

Click Next to display the Database Settings page. The deployment is targeted to the SQL Server 2016 and .mdf and .ldf files are created. You can change the database name and herein it remains unchanged.