Run a simple query in HeidiSQL

It is really no different from running a query in SQL Server Management Studio. You need to have an instance of SQL Server running on your computer. Launch HeidiSQL and get connected to the instance.

You will be seeing this (different in your case) displayed in the HeidiSQL user interface. Either you can get connected to a session you created or a new one using the New button at the bottom of the UI.
 
RunAQuery01.png

You can connect to a single database by selecting it from the list as shown. Here Northwind was selected.

RunAQuery02.png
The Nov21 sessions gets changed and the changed sessions window opens as shown:


RunAQuery03.png

Click on Query to open a window for inserting query related statements as shown:
RunAQuery04.png

In the Query Window insert your SQL statement. For example a query such as the one shown:

SELECT TOP 50 [CategoryName]
      ,[ProductName]
      ,[ProductSales]
  FROM [Northwind].[dbo].[Product Sales for 1997]

 

Click Query and the response will appear below the query window as shown.

 
RunAQuery07.png

The tabbed window in the center has all the necessary tabs like the SAP SQLAnywhere Studio Central.

Uniqueidentifier in SQL Servers

Uniqueidentifiers are also called GUIDs(Globally Unique Identifier) which are unique and different from any other. It was introduced in SQL Server 7.0. The GUID is obtained by the function call NewID(). The unique identifier data type is stored in the computer as a 16-byte (128 bits) binary value. The formatted GUID using Hexadecimal is usually shown as a group separated by hyphens:
4bytes-2bytes-2bytes-2bytes-6bytes
Here is how the uniqueidentifier obtained by calling the NewID() function.
---
Declare @myid uniqueidentifier
set @myid=NEWID()
---
A uniqueidentifier is obtained every time this function is called as shown here:



While it is great for security, it takes up too much space (think of millions of rows with a uniqueidentifier data type column).

More about uniqueidentifier in SQL Servers here.

SQL Server 2005 nears EOL

It is sunset time for SQL Server 2005 as it reaches end of life  (EOL). While EOL may be an extreme word to use, it is going to be End of Support (EOS) by Microsoft beginning April 2016.
SQL Server 2005 was the beginning of a new era for SQL Servers and according to information available on the Internet, SQL Server 2005 is still used extensively tied up with Windows Server 2003.
There are a couple of upgrade options and probably most go for either SQL Server 2012 or SQL Server 2014. Perhaps it may be better to go the whole 9 yards and get SQL Server 2016 which is in CTP right now.

The good news (for me) is that SQL Server 2005 will continue to stay for few more years and I can still sell my book.

https://www.packtpub.com/networking-and-servers/beginners-guide-sql-server-integration-services-using-visual-studio-2005

Connecting to SQL Server 2012 using HeidiSQL

 HeidiSQL is a client application and you can use it to work with databases. If you want to know more about it, or download and install HeidiSQL go here.

Launch HeidiSQL from your desktop or click the HeidiSQL app from the All Apps menu shown.

The Heidi Session Manager is launched as shown.

 
Heidi_Session_00.png

Click New and choose Session in root folder from the drop-down list as shown.

Heidi_Session_01.png

You can change the session name by over-writing as shown. Herein it is Nov21.

Heidi_Session_02.png

By default the connection is to MySQL using TCP/IP and localhost(127.0.0.1)

Let us change the Network type to Microsoft SQL Server using TCP/IP by using the drop-down menu shown.

Heidi_Session_03.png

Choose Windows authentication as shown.

Heidi_Session_04.png

Click Open. The connection is refused.

 Heidi_Session_05.png

OK. Let us look if the SQL Server has started. Open the Control Panel|...|Services and verify that the server has started. Indeed it is running (option chosen at SQL Server Installation).

Heidi_Session_06.png

Now let us change the Hostname /IP to Hodentek8\RegencyPark (over write 127.0.0.1)
Click Open after changing the hostname.
 You may get a save modifications message.

 
Heidi_Session_07.png

Click Yes. The program processes this information and the client is now connected to the named instance of SQL Server 2012 as shown.


Heidi_Session_08.png

Now you are cooking!

Tools in SQL Server Integration Services

In 2005 there was no program called SQL Server Data Tools and Microsoft had just come up with .NET based technology. Microsoft Business Intelligence’s SQL Server Integration Services had Visual Studio components that had replaced the Data Transformation Services of SQL Server with tighter integration with SQL Server 2005. It was a big leap. I wrote my first book on SQL Server Integration Services 2005 during 2007. It had a lot of reviews good/bad but it survived and quite a good number of folks thought it was useful and it still sells.

SSIS2005.00
Fast forward to 2015, a lot of data related methodologies has changed. In the interim, I have not paid attention to the details although I am aware of the changes. In the intervening years technology has been on a rapid pace with the advent of Cloud Services, Streaming data, BigData, NoSQL etc.

It is no wonder that Microsoft has worked on their tools to address these developments and fortify Visual Studio Tools. This post is about a comparative look at the Toolbox items then and now.  Of course there is lot more to it than just adding new tools.

In Visual Studio 2005 IDE there were a set of Business Intelligence project templates to address the various needs at that time:

•    Analysis Services Project
•    Integration Services Project
•    Report Model Project
•    Import Analysis Services Database…
•    Report Server Project Wizard
•    Report Server Project

The Visual Studio 2005 IDE, a very thorough discussion of this in video format may be found here.

“The full comprehensive and detailed use of the Visual Studio 2005 IDE is described in my book, "Beginners guide to SQL Server Integration Services 2005" published by Packt Publishing Inc. The book has 20 of the more commonly used SSIS tasks that you may come across in your developmental work. Targeted to beginners as well as for those moving from DTS to SSIS, the book is essentially hands-on with screen shots that explains the subject matter. By the end of the book you would have learnt those 20 tasks and ready to probe more on your own.
- See more at: http://www.sswug.org/articlesection/default.aspx?TargetID=44780#sthash.ik4QFFCB.dpuf”

In the case of Integration Services, basically the project was based on Packages that used Data Sources, Data Destination and transformations that worked between the Sources and Destinations while Data Flow Tasks did the actual task of moving the data based on the choice of data flow task.
The package was an assembly consisting of several objects and, other packages could be nested as well.

Specifically, a package can consist of:

•    Connections
•    Control Flow Elements
•    Data Flow Elements
•    Event Handlers
•    Variables
•    Configurations

Here are a few images relevant to Integration Services 2005 for Data Flow:
Data Flow Sources

SSIS2005_DataFlowSources.jpg

Control Flow Tasks

 

SSIS2005_ControlFlowTasks.jpg

 Data Flow Transformations

SSIS2005_DataflowDestinations.jpg

Data Flow Destinations

 

SSIS2005_DataflowDestinations.jpg

In Visual Studio the appropriate tool for working with SQL Servers as described here is the SQL Server Data Tools to match the version of Visual Studio edition. A stand-alone version is also available which gives access to Visual Studio Shell.

The download mentioned here gives access to SSDT that can work with SQL Servers from version 2005 to SQL Server 2016 CTP3:

•    SSDT Preview for Visual Studio 2015
SQL Platform support:
SQL Server 2005 – 2016 CTP 3.0
Analysis Services for SQL Server 2008 - 2016 CTP 3.0
Integration Services for SQL Server 2016 CTP 3.0
Reporting Services for SQL Server 2008 - 2016 CTP 3.0
Azure SQL Database
Azure SQL Data Warehouse

The following paragraphs use the above preview installed on a Windows 10 (x64) OS platform on a Toshiba Laptop.

Herein follows a description of the Toolbox of the Integration Services for SQL Server 2016 CTP3.0. The Business Intelligence templates in this version of SSDT is shown in the next image.

SSDT_01.jpg

The two Analysis Services project refer to two modes of working with the services. Examples in my Reporting Services 2012 book provide specific examples of these two types of projects.

The Reporting Services Wizard gives guidance at each step,  you can also use the Report Server Project to fashion your own.

Herein we look at the Toolbox items in the SQL Server Integration Services arsenal to compare with those in SSIS 2005 and identify the new ones.

Common folder in Data Flow  (Click Data Flow tab in designer):

SSIS_02.PNG
Other Transforms group in Data Flow :

SSIS_03.PNG

Other Sources group in Data Flow:

 

 SSIS_04a.PNG

 Other Destinations group in Data Flow:

SSIS_05.PNG
 Control Flow Tasks (access the following when Control Flow Tab is chosen in the package designer) in SSIS 2005:
The following image shows the Control Flow items in the SSIS 2005 designer:

controlFlow 001.jpg

Visual Studio BI 2005 also had a Maintenance Plan related controls as shown here.

MaintenancePlan 001.jpg

In SSDT 2015 the tasks are divided into two groups, Common and Other Tasks. The following image shows the tasks in the two groups. Those highlighted in light blue are the same ones found in the Maintenance Plan tasks in 2005 and the ones in Yellow are those related to Control Flow Items in SSIS 2005.

SSIS_10_CF3.PNG

SSIS_9_CF5a.PNG

The For Loop Container, ForEach Loop Container and Sequence Container in the Control Flow Items of VS 2005 have been placed in a separate Containers category as shown.

 SSIS_8_CF3.PNG

Visual Studio has gone through many iterations from 2005 to 2015 and some of these changes have entered the IDE during this period.  While there are additions to the toolbox there are no removals with many of the items rearranged or grouped differently.

Hands-on training event in Honolulu: SQL Server Reporting Services 2012 in Native Mode

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(PACTT): http://pcatt.hawaii.edu/home

Report writing is one of the most important IT related jobs in an enterprise. 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.
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 22 - Apr  28, 2016; TTh; 5:30 pm - 8:30 pm; 12 mtgs

I will be using my latest book on SQL Server Reporting Services for this course:

More here:http://hodentekmsss.blogspot.com/2014/11/things-you-learrn-hands-on-in-my-book.html

SQL, CLR and SQLCLR

SQL is a language that is needed to run queries (post questions to) against SQL Server databases. It is not specific to Microsoft SQL Server. It can also be used with other databases like Oracle, Sybase, etc.

However Transact SQL (T-SQL) is the enhancement to add some programming capability to SQL. Each database vendor has his own database specific programming capability. For example PL/SQL is Oracle specific procedural programming capability on top of SQL.

Common Language Runtime is the programming that manages the execution of programs written in any of the supported languages such as C#, VB, Visual C++ etc to share common object orineted classes. Thiss is somewhat like Java Virtual Machine for programs run from Java. CLR is also called 'managed execution environment'. CLR is therefore related to .NET programming.

SQL Common Language Runtime (SQLCLR) is the combo technology (SQL Server and .NET) for hosting .NET common language runtime engine within SQL Server. In other words you can run managed code from within SQL Server.

Creating a Visual C++ CLR Console Application in Visual Studio 2015 go here.
http://hodentekhelp.blogspot.com/2015/08/how-do-you-create-visual-c-clr-console.html

Get an inventory of SQL Server and Oracle instances on your computer with MAP

During a year's duration I install and uninstall a number of products: CTPs; SQL Servers; express versions etc from a number of vendors that fit all kinds of scenarios. This is really more frequent in the case of SQL Servers and the CTPs/versions. Although I de-install older versions and older CTPs before I install new ones, there is always a chance of missing and, knowing the inventory is of great help. If you add the other scenarios where this tool can handle  you will immediately realize the great value of this tool.

MAP which stands for Microsoft Assessment and Planning (MAP) toolkit is in Microsoft's own language,

"The Microsoft Assessment and Planning (MAP) Toolkit is an agent-less inventory, assessment, and reporting tool that can securely assess IT environments for various platform migrations—including the latest versions of Windows client and server operating systems, Microsoft Office and Office 365, Microsoft SQL Server, Hyper-V, Microsoft Private Cloud Fast Track, and Windows Azure.
"

MAP Toolkit can deal with most of the following scenarios although some of what you read in this post deal with SQL Servers (database):

• Cloud

• Desktop

• Server

• Desktop Virtualization

• Server Virtualization

• Database

• Usage tracking

• Environment

You can download the tool from here:

You can start installing by double clicking the downloaded file MAPSetup.exe (73MB).

 
Map_01.png
 

Click Continue and in the license acceptance page click Next after accepting the terms.

Map_03
 
You can accept the default or browse for a new location.
Click Next.
This one about joining the Customer experience which I usually do.

Map_04

This starts initializing the MAP Toolkit 9.3



Map_05

After the initialization process is completed you get this Overview page which shows all the various scenarios where MAP can be used.

Map_06

As I am interested in the SQL Server related items the Database is chosen.

 

Map_07

With this you can get information on not only SQL Servers but also Oracle Products, Azure VM Readiness, etc

Clicking On SQL Server Discovery you get the SQL Server related items as shown in the next image. The option for this Collect Inventory Data Step 1 is to create/select database to keep info.


Map_08

SQL Server Discovery will start from here.









 

Uninstall SQL Server 2016 CTP2.1

Starting with SQL Server 2016, there has been a number of CTPs. Every time you install a CTP you are ushering in a lot of programs into your computer and the uninstalling does not seem to remove the CTP completely even after uninstalling the database engine.



The latest CTP version of SQL Server 2016 is CTP 3 and therefore in order to keep the inventory to minimum, the CTP2.1 was uninstalled prior to installing the CTP3.

Fortunately uninstalling has become a lot easier, still depending on how you uninstall you may be asked to locate the executable used in installing. This post shows some details of uninstalling SQL Server 2016 CTP2.1.

Uninstall starts from Control Panel | Programs | Programs and Features.
This launches the installation Studio which does a rules check and brings up this screen. The CTP2.1 instance May2015 is ready to be removed as shown.

Uninstall2016CTP2.1.png

Click Next.

You can select the features to remove. Herein Select All is the choice including shared items.

Uninstall2016CTp2.2.png

Click Next.
The Ready to Remove screen is displayed.

Uninstall2016CTp2.4.png

Click Remove.

 

Even after removing all the programs successfully, the items programs are not removed from the Control Panel.