Friday, January 31, 2014

Honolulu Technical Training: SQL and SQL Server Reporting Services 2012

The two non-credit courses on SQL and Reporting Services offered by the Pacific Center for Advanced Training are going to start soon. Register and upgrade your database skills.

Course content and more details here:
http://hodentek.blogspot.com/2013/12/sql-training-in-honolulu-introduction.html

Tuesday, January 28, 2014

Troubleshooting the connection to a Contained Database in SQL Server 2012

Contained databases are the best choice if you have migration in mind and with
SQL Servers both in-house and the cloud, migration will always be an important
item to consider.

Read this article for the benefits of Contained databases:
http://stackoverflow.com/questions/5932293/what-is-the-real-benefit-of-
contained-databases

SQL Server 2012 only supports partially contained databases and in future
versions this may change.

You can create a partially contained database in SQL Server 2012 Express using
either T-SQL or SQL Server Management Studio.

Creating a contained database
1.The first step is enabling containment in the Server. It is assumed here that
you have an instance of SQL Server 2012 Express installed on your desktop/laptop.

Right click the server and click on Properties to open the Server Properties
window. Click on Advanced in the Select a Page list.
The Advanced Page is displayed and here you can enable containment as shown.


Right click Databases node and click New Database... to open the New Database
window.
  • In the General page you can provide a name for the database, herein HContained.
  • In the Options page pick Partial for Containment type as shown.



The database will be created as shown in the Server (local)/PCATT owned by
Hodentekwin7\mysorian, the computer administrator.


2. Now you need to create a User with a password (which means the server
authentication should be SQL Server and Windows Authentication mode as shown in
the Server properties page):


3. Click Security node of the HContained database to expand and right click Users
node and click New User....

This opens the Database User - New window where you provide a username and a
password (herein huser, password:resuh). By default the new user will neither
own any schema,nor has any membership in database role. For him to be able to
connect, he should be the owner of db_owner schema. The new user should also
have login for the computer.


Here is how the user huser was created
USE [HContained]
GO
/****** Object:  User [huser]    Script Date: 1/27/2014 10:40:01 PM ******/
CREATE USER [huser] WITH PASSWORD=N'hÀ]™t#Ux ÐöÀ Ó <Ö—Y¡nWâýgÁ˜éayÅÄ',
DEFAULT_SCHEMA=[dbo]
GO

Connecting to partially contained database HContained
1. Bring up the Connect to Server dialogue as shown. Change authentication to SQL
Server Authentication, enter the user created earlier(huser, password resuh)


and if you just click connect you will get an error as huser has no server
login.

2.Now click Options button in the Connect to Server dialogue.
Click the download handle on Connect to database and replace <default> with
HContained as shown.


3. Now click Connect.
You immediately get connected to Hcontained database as shown.



This was achieved after taking care of some errors that cropped up. The fixes
were as follows:
1. SQL Server Management Studio version 11.0.2100.60 was used. Attempts to
connect to HContained resulted in the following error message. The fix to
rectify the situaion is also shown in the image.


2. The above fix still did not allow connecting to the Contained database. The
reason appears to be that the RTM version of SSMS does not support connection as described. In order to connect, the SP1 version of SSMS 2012 Express was needed. There are atleast two ways to get the SP1 version of SSMS 2012. Install SQL Server 2012 Express SP1 Advanced version which installs the client tools or install SSMS 2012 Express SP1.

Follow this link to download SQL Server 2012 Express SP1 files:
http://msdn.microsoft.com/en-us/evalcenter/hh230763.aspx
You will be able to download any of the following:

  • LocalDB (MSI installer)
  • Express (Containing only the database engine)
  • Express with Tools (with LocalDB, Includes the database engine and SQL Server Management Studio Express)
  • SQL Server Management Studio Express (Tools only)
  • Express with Advanced Services (contains the database engine, Express Tools, Reporting Services, and Full Text Search)
Note that SSMS 2012 Express RTM is 11.0.2100.60 and SSMS 2012 Express SP1 version is 11.0.3128.0.




 

Friday, January 24, 2014

What are recovery models in SQL Server 2012 and how do you choose?

Backup and recovery are some of the most important operations in order to plan for emergencies and keep the data happy at all times.The role of recovery models in SQL Server is for controlling the transaction log maintenance. SQL Server's backup and restore operations takes place depending on what recovery model is chosen.

There are 3 types of recovery models:

  • Simple - No need for log backups. If you are concerned with transactions this is not the type to choose as the changes since most recent backup are  unprotected.
  • Full - Requires log backups. Changes to a specific point in time can be recovered.
  • Bulk logged-Requires log backups. Point in time recovery is not supported but recovery to the end of any backup is. Good for high performance bulk operations.
How do you change the model?

This is with reference to SQL Server 2012 Express. The default model for recovery operations is simple as shown.


Right click on the database node of the database to be recovered (herein database Manoa). On the Select a page that gets displayed, click Options. The related page is displayed as shown.
Here you can change the recovery model. Herein Simple was changed to Full.



Now right click the database node and to display the tasks



From the submneu click Back Up... and you will see it is reflected in the backup operations although you will not be able to chnage the model here.


 

Tuesday, January 21, 2014

How do I use SQLCMD in SQL Server 2005?

SQLCMD lets you write SQL Commands as well as T-SQL Statements and it is the preferred command-line utility in SQL Server 2005. SQLCMD can also be run within SQL Server Managment Studio and runs in side a query pane by enabling the SQLCMD Mode in SQL Server Management Studio from the Query menu's drop-down list.



One of the requirements for running the SQLCMD from the command line (in a DOS screen) is that the client and the server should be listening on/to the same TCP Port. This can be set using the SQL Server Configuration Manager.

Here is an example fo SQLCMD run from command-line.


Commandline access to the program is perhaps one of the nicest features any program can have and the keyboard wizards love them. They can do most things you want done as long as you understand the switches and when & how to use them. A good wizard does most of the things these days, but cannot beat the command-line.

Did I hear you say, short and sweet?

Friday, January 17, 2014

Moving from SQL Server 2012 to SQL Server 2014

SQL Server 2014 is still in CTP2 and SQL Server 2012 has been around for some time. You could get a SQL Server 2012 Express free of cost but with limited functionality.

You can download SQL Server 2014 CTP2 here:
http://technet.microsoft.com/en-US/evalcenter/dn205290.aspx

While on the above make sure you read all the good things you can do with SQL Server 2014.

You may want to know what you can do with SQL Server 2014 CTP1 that you cannot do with SQL Server 2012 Express. If you are looking at just the tasks that you can accomplish, then compare the two images below (the limited number of tasks is that of SQL Server 2012 Express).

SQL Server 2012 Express


SQL Server 2014 CTP1 (enterprise)

 
Quite a lot of the tasks are new features such as Managing Database Encryption, Deploying to Windows Azure VM, etc.
 

Thursday, January 16, 2014

Window function in SQL Server with an example

Windows functions in SQL Server's T-SQL is not related to the Windows operating system but the kind of more detailed ordering (and all the calculations you can do with the ordered set) than you can get by using the regular Group By clause. Windows functions are obviously much more powerful than what you get by mere grouping and subqueries.

The following windows functions were introduced in SQL Server 2005:
Row_Number
Rank
Dense_Rank
NTILE


These are enhanced in SQL Server 2012 by providing that provide better analytics,

  • Windows Order and Frame clauses
  • Windows Offset functions
  (Lag, Lead, First_Value, Last_value)
  • Windows distribution functions
  (Percent-Rank, Cume_Dist, Percentile_Disc and Percentile_Cont)

Conceptually it is as if you have created a window of rows to which you apply some processing and the result is returned in a single row as related to the windowed rows). You define the window in which the processing takes place by the clause OVER.

The following example is using the Orders and Order Details table in Northwind database. If you need a copy of Northwind follow this link:

The following query when run to get raw data,

Get raw filtered data

[Order Details].OrderID, Cast((Orders.OrderDate) as nvarchar(12)) as 'Order Date', [Order Details].Quantity
FROM            [Order Details] INNER JOIN
                         Orders ON [Order Details].OrderID = Orders.OrderID and [Order Details].OrderID <=10253

Returns the following:

OrderID     Order Date   Quantity
----------- ------------ --------
10248       Jul  4 1996  12
10248       Jul  4 1996  10
10248       Jul  4 1996  5
10249       Jul  5 1996  9
10249       Jul  5 1996  40
10250       Jul  8 1996  10
10250       Jul  8 1996  35
10250       Jul  8 1996  15
10251       Jul  8 1996  6
10251       Jul  8 1996  15
10251       Jul  8 1996  20
10252       Jul  9 1996  40
10252       Jul  9 1996  25
10252       Jul  9 1996  40
10253       Jul 10 1996  20
10253       Jul 10 1996  42
10253       Jul 10 1996  40

(17 row(s) affected)

Using Group BY clause:
There are groups (17 rows are in 6 groups) and for these groups we can find the sum of quantity orderd like in,

Select   [Order Details].OrderID, SUM([Order Details].Quantity) as 'QUAN'
FROM            [Order Details] INNER JOIN
                         Orders ON [Order Details].OrderID = Orders.OrderID and [Order Details].OrderID <=10253
                         group by  [Order Details].OrderID

the rows returned are,
OrderID     QUAN
----------- -----------
10248       27
10249       49
10250       60
10251       41
10252       105
10253       102

(6 row(s) affected)

Using the Rank() function

Now we can rank the value Quan and show they are ranked using the Rank() function as in the following:

Select   [Order Details].OrderID,
         SUM([Order Details].Quantity) as 'QUAN',
        RANK() OVER (ORDER BY SUM([Order Details].Quantity)) as RankQuan
FROM            [Order Details] INNER JOIN
                         Orders ON [Order Details].OrderID = Orders.OrderID and [Order Details].OrderID <=10253
                         group by  [Order Details].OrderID

We have a new column produced by,
 RANK() OVER (ORDER BY SUM([Order Details].Quantity)) as RankQuan
which really shows how the rank is evaluated providing a rank for each row.

The response to the above is,

OrderID     QUAN        RankQuan
----------- ----------- --------------------
10248       27          1
10251       41          2
10249       49          3
10250       60          4
10253       102         5
10252       105         6

(6 row(s) affected)

As you notice the Rank() function took care of ordering as well in addition to ranking them.



Use Rank() function one more time:

Let us modify the query further
(and answer the questions whether one can use another Rank() function.

Select   [Order Details].OrderID,
         SUM([Order Details].Quantity) as 'QUAN',
        RANK() OVER (ORDER BY SUM([Order Details].Quantity)) as RankQuan,
        Rank() OVER (ORDER BY [Order Details].OrderID) as 'OrdIDRank'
FROM       [Order Details]        INNER JOIN
                         Orders ON [Order Details].OrderID = Orders.OrderID and [Order Details].OrderID <=10253
                         group by  [Order Details].OrderID

The response is as follows:
OrderID     QUAN        RankQuan             OrdIDRank
----------- ----------- -------------------- --------------------
10248       27          1                    1
10249       49          3                    2
10250       60          4                    3
10251       41          2                    4
10252       105         6                    5
10253       102         5                    6

(6 row(s) affected)

You can see that this has not affected the RankQuan except that the ordering in the final rank gets ordered consequitively.





Sunday, January 5, 2014

What is a SQL Server Managment Studio project and how to create one?

SQL Server Management Studio provides a means to group together all your codes in one place as part of a SQL Server Project. A solution is a container for a project(s) containing all logically related scripts and files in one place. It is available even in SQL Server 2012 Express. This is the platform to use if you want to develop scripts for Database Engine and Analysis Services.

A solution explorer is a pane in SQL Server Management Studio with project containers for managing scripts and files. The concept of Solutions and Projects in Visual Studio is too well known requiring elaboration and this is carried over to SSMS and in fact SQL Server is tightly coupled with Visual Studio. In Visual Studio you can carry a lotsof things that you do in SSMS

How to display Solution Explorer in SQL Server Management Studio?

  • Click File | New | Project... in SSMS to open the New Project window of Visual Studio as shown.


  •  If you choose SQL Server Management Studio Solution then the above interface adds a Blank Solution to which you may add projects. On the other hand
    If you choose SQL Server Management Studio projects then you have the option of adding a SQL Server scripts project or a Analysis Services scripts project as shown.


  • When you accept the option of creating a SQL Serverscripts project Visual Studio creates a Solution and a project within the solution as shown. The Project has folders for Connections, Queries and Miscellaneous scripts.  The default location of the project is at (generally at the logged in Users folder): 
c:\users\mysorian\documents\sql server management studio\Dec2013\Dec2013\Dec2013.ssmssqlproj 


The properties of the solutions shows where the source code is located.


  • From the Solution node you can create a new project or add an existing project to it.

    From the project node (right click) you can add New Item, or an existing item, or a new query, or a new connection.
When you click add New Item, the VS interface is displayed as shown,

  • You can start off with any of the templates to create a query. 

You can open the same project using File | Open |Project/Solution... in SSMS as shown here.


This displays the  Solution explorer as shown by accessing the Source saved on SSMS as shown.


Now you will be developing the project in SQL Server Management Studio.

You can do it both ways or in mixed mode.

Saturday, January 4, 2014

Connecting to SQL Server Express 2012 from Power Pivot - 2

Before you start using Power Pivot you need to install this add-in.

Determine which version (x32 bit or x64bit) Excel you have on your computer. You can find this in File | Help as shown.


You then download the appropriate version of Power Pivot add-in. Also review the following thread here:
http://social.technet.microsoft.com/Search/en-US/Technet?query=Power%20BI%20and%20Jayaram%20krishnaswamy&beta=0&ac=5

Download the add-in from here:
Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010 

This is the power of this add-in in Microsoft's own language:
"Microsoft PowerPivot for Microsoft Excel 2010 is a data analysis tool that delivers unmatched computational power directly within the software users already know and love — Microsoft Excel."
 For the 32bit Office, the PowerPivot_for_Excel_x86.msi (98.5MB) was downloaded and installed.

The following are some of the screen shots for connecting to SQL Server 2012 Express:

Launch PowerPivot window from MS Excel as shown by clicking on PowerPivot
 Window (extreme left)


Click from drop-downs From DataSources followed by From SQL Server in the PowerPivot for Excel window.

Table Connect Wizard gets displayed requesting SQL Server  details as shown.


Provide the database you want to connect to as shown. You better have permissions to do so.

I

If want you can test (and troubleshoot) connectivity from this screen.

Click Next. You can import a set of tables or design data using query as shown in the screen that gets displayed.


The following windows is displayed for the first option and it shows few tables selected. The second part (right-side) of the image shows the Preview and if needed some filtering that can be made.


The import was successful as shown here after some processing.

 
Click Close and the data gets into the PowerPivot window as shown.


You can see relationships that exists as shown.

Now begins the hard work of things that you want to do with this data.

Good luck


Friday, January 3, 2014

Connecting to SQL Server 2012 Express from Power Pivot - 1

Previously we have seen connecting to a database from Power Query. Microsoft also has Power Pivot which also can access SQL Server 2012 to model a data source. In what way is Power Query different from Power Pivot?

There is a functionality difference between the two. Power Query is more like SQL Server Integration Services while Power Pivot is akin to SQL Server Analysis Services. Even a cursory look at the
'ribbon' would reveal this as shown:


In Power Query UI you do not see analysis related controls.

As to connecting to data you do see there is some overlap. However, Power Query can access lot more types of data sources than Power Pivot.  These are data sources that Power Query can access,


The following image shows data sources that Power Pivot can bring in,

 Also, review the user interface differences between the two tools, one provides more analytical support than the other,

However both Power Query as well as Power Pivot can connect to SQL Server 2012 Express by clicking on the From Database drop-down.

Go to Part 2 here:



Wednesday, January 1, 2014

A Happy New Year to you all

Wishing you all a more prosperous and a more eventful New Year.