Sunday, March 22, 2015

How do you explain this correlated subquery?

The syntax of subqueries is as shown here.

Outer query(some operator) (Inner query)

While non-correlated subqueries are quite easy to understand correlated sub queries
are not quite obvious.

In the case of non-correlated subqueries, the inner query is evalauted first. The value or values returned from inner queries are used in the outer queries based on some operation.

In the case of correlated subquery the outer query is run and for each value returned by the outer query the inner query is evaluated and when the match is found the outer query stops. Usually the inner query just returns either some value or no value and the logical operation is usually Boolean (exists, not exists, any, all, etc.).

The correlated subquery you are asking about is the following posed to the Northwind database:

Use Northwind
Go

Select o.EmployeeID, ShipName, o.OrderID ​
from Orders o​
Where  exists​
(Select i.ShipCity​
from Orders i​
Where i.ShipCity=o.ShipCity and i.EmployeeID in (5,7))

Go

This returns the following result set (here are sme 6 rows out of 674 rows):

CorrelatedSubquery
You are probably intrigued why you are getting employee ID's other than 5 and 7 in
the result.

The reason for this is the inner query result when processed with the logic (exists) returns true or false. If it is true the Orders table returns the columns requested. Inner query result set are not part of the rows returned.

Try the same query replacing 'exists' with 'not exists'. You will find that the subquery returns 156 rows.

The total of all rows returned by the query,

Select o.EmployeeID, o.ShipName, o.OrderID ​
from Orders o​


is 830 which is (156+674).

Saturday, March 21, 2015

How do you design a Query using Query Editor in SQL Server Management Studio?

From scratch:
Open a Query pane in SSMS (from a database, for example) and right click to display the drop-down menu as shown.

DesignQueryinEditor

Choose the Design Query in Editor... option and the Query Designer window gets displayed with a Add Tables window. You can add as many or as few tables as you want and design your query. Holding control key down, select multiple tables and click ADD to add all the selected tables. If you suspect that the data has changed recently, you can click Refresh to use latest data.

DesignQueryinEditor2

Display a query you created in the designer

You can create a query in the query pane. Highlight the query, right click the highlighted portion and select Design Query in Editor... as shown.


QueryEditorUp
The query you designed gets displayed in the Query Editor as shown.

SimpleSelect
 Selecting the query and displaying it in designer will not work for more complicated queries.


Wednesday, March 18, 2015

I do not see the available databases toolbar item in SSMS, Why?

Missing Toolbar item? NO

The SQL Server Management Studio(SSMS) is highly context sensitive.

For example, unless a Query Window is open (by creating a new query or by clicking first in the query window) the menu item Query will not be displayed.


SQLQueryeditor00

When you open a query window (by using New Query  from database or SQL Server
instance node) then you will see the Query menu item.

SQLQueryeditor01

In a similar manner depending on how whether View | Toolbars | Sql Editor
is chosen or not you will not see the 'Available databases' item as shown in the
next image.


SQLQueryeditor01

Saturday, March 14, 2015

How do you install Windows PowerShell 4.0 on Windows 7?

Windows PowerShell 3.0 is installed on Windows 7 by default but not Windows PowerShell 4.0.
You can check for version and verify if you have Windows PowerShell 4.0 as detailed in the following post:
http://hodentekhelp.blogspot.com/2014/08/if-you-have-multiple-versions-of-power.html

Review this image:

So, how do you install Windows PowerShell 4.0 on Windows 7?

First of all lets take a look at what are the new features that we can use in Windows PowerShell 4.0
Here are the Windows PowerShell 4.0 new features:

 •Support for workflow and remote script debugging
•Improved workflow authoring experience to make it more consistent with script authoring
•Added PipelineVariable as a common parameter
•Better support for downloading updatable help by using Save-Help and Update-Help in offline scenarios
•Updated version from 3.0 to 4.0
•Several bug fixes and performance improvements

Read more here:
http://www.microsoft.com/en-us/download/details.aspx?id=40855

In order to get Windows PowerShell 4.0 you need to install Windows Management Framework 4.0. This can be installed on Windows 7 SP1.
 I am not focussing on Windows Servers for this post.
It is not available for Windows 8.0 but installs by default when you upgrade to Windows 8.1
 
You can download WMF 4.0 from the previous link.
The download  has multiple files as shown here:

WMF40files.png

Installing WMF 40 confers the following functionality:
•Windows PowerShell 4.0
•Windows PowerShell ISE
•Windows PowerShell Web Services (Management OData IIS Extension)
•Windows Remote Management (WinRM)
•Windows Management Instrumentation (WMI)
•The Server Manager WMI provider
•A new feature for 4.0, Windows PowerShell Desired State Configuration (DSC)

You have a much more power full interface now.
 

Tuesday, February 24, 2015

How do you create a database in SQL Server using Power Shell?

Review this previous post    http://hodentekmsss.blogspot.com/2014/11/accessing-sql-server-via-smo-using.html

In the above post you will get an introduction to Power Shell and SQL Server maangement Objects. You also learn how to access the SQL Server and carry out a few tasks like listing out all the databases.

In this post you will learn how to create a new database in SQL Server using SMO and Power Shell. Using Transact-SQL or the SSMS you can easily create a database. Using PowerShell is another option to create a database using a script.

Launch Power Shell by right clicking the instnace node as shown.


PS_SMO_DB01

This ensures that you have access to the SQLServer's SMO library.

You will be launching the shell with the following line:
PS SQLSERVER:\SQL\HODENTEK8\REGENCYPARK>

Enter the following code one line at a time. After you enter the line and click enter you may not get any message. Continue with the other lines.

$server= new-object Microsoft.Sqlserver.management.smo.server 'Hodentek8\RegencyPark'
$dbname= "Feb6"
$db=new-object  Microsoft.Sqlserver.management.smo.Database ($server,$dbname)
$db.Create()


When the final command line is processed you will have created a new database Feb6 in your SQL Server Instance as shown. It will have default settings.


PS_SMO_DB02

How can I connect to an Microsoft EXCEL file on my Windows 8.1 laptop?

If you have Office running on your laptop it should be immediately possible and you would not be asking this question. Although you do not have Office sometimes you may want to connect to it.
For example you may use a Export/Import wizard to create a Excel file from the data on your SQL server. In such cases you come up with the following error:


Excel11

You will find that there are no ODBC (x64) drivers on your machine which will enable to correct this error and you need to download and register them on your computer. Open ODBC Manager on your computer and verify.
 

Internet search reveals that you can download the Access driver (Microsoft Access Database Engine 2010 Redistributable) from here:
http://www.microsoft.com/en-us/download/details.aspx?id=13255


AccessDriver

The details of this driver are shown here from the Microsoft site. This installation will provide drivers for office applicaitions including MS ACCESS and MS EXCEL.

Download and install the AccessDatabaseEngine_x64.exe file and double click the file to run the program.
Excel 22

This will install the Microsoft EXCEL and Microsoft ACCESS drivers needed for connectivity.



 

Thursday, January 29, 2015

How popular is JavaScript?

It has gained lot of popularity because of the many libraries developed succh as jQuery, Bootstrap, Node.JS, win.JS, dogo etc. Toagether with HTML5 it has become all the more popular for web development.

The popularity of a programming language is described by an inex called the TIOBE Index.
TIOBE index provides a measure for the popularity of programming languages as mined from search engines. It includes many browser results as well as many programming languages. The index is published every month and for the month of January you can find it here:
http://www.tiobe.com/index.php/content/paperinfo/tpci/index.html

Programming languages C and Java are the top dogs and Javascript jumped from 9 to 7 in January 2015 and C# steady from 2014 to 2015 at 5.

TIOBE Index is calculated by the query +"Language> programming" to the search engines.

Get more info on TIOBE herre:
http://www.tiobe.com/index.php/content/products/tics/TICS_framework.html