Find everything about objects and columns in SQL Server 2012

In SQL Server 2012, sys.columns is a view you can find in the node System Views in the msdb database.

The various columns in sys.views are shown in this next image. You can find everything about the Columns in this view.



If you know the ObjectID of the Column of a table in the database, you can find its name from this query, the intellisense gives you an idea of this selection.

SysColumnName.png

A typical query and its output are as shown for finding the column name from ObjectID.

SysColumnName_02.png

If you know the Column_Name then you can find Column_ID, the intellisense gives you an idea of this selection.

SysColumnID.png

A typical query and its output are as shown for finding Column ID from Column Name.

SysColumnID_02.png

Starting from table name you can get the columns from these queries:

You can also get the ObjectID of the table from this query:

Use Northwind
go
Select *
From sys.tables
where tables.name='Employees'
go

The above query returns one value=245575913
Using the above you can get a whole lot of information for the columns from this query:

Select *
from sys.columns
where Columns.object_id=245575913

Finding Collation used by the SQL Server objects

Collation is locale specific and you choose while installing the SQL Server. Sorting depends on this as well as case sensitiveness.

Collation.jpg

You can also search to find what kind of collation is supported on your server using the following query for the whole database (this example is for the master database)
Select name, COLLATION_NAME from sys.databases where name=N'master';
Response: SQL_Latin1_General_CP1_CI_AS master
If you have a column that is of varchar data type (something textual) you use this query for the column,
SELECT name, collation_name FROM sys.columns WHERE name = N'optname';
Response: optname SQL_Latin1_General_CP1_CI_AS

Note that there is a column called optname in the master database
You can find all the collations that are supported using this query:

CollationsSQLServer2012.jpg

Starting on Jan 28: Introduction to Structured Query Language

Classes will be starting promptly on January 28. You still have time to register.

Register here: http://ce.uhcc.hawaii.edu/search/publicCourseSearchDetails.do?method=load&courseId=78659

Reporting Services 2014 and Windows 10: The way out

Microsoft Edge, or simply Edge is the default browser in Windows 10. It is not designed to start with elevated permissions.

Reporting Services Server is accessed in the default Edge browser and you always end up with access denied for the user, <domain>/<User> for lack of sufficient permissions. One could use another browser such as Firefox but Reporting Services server cannot be accessed even when the browser is launched with elevated permissions. Firefox did not have problems in accessing the Report Server in SSRS 2012.

The way out for this in using the IE 11 browser that comes with Windows 10.

Jump start getting a handle on Microsoft R Server

Enterprises will benefit enormously if they can get actionable insights into the data they hold in their servers. Analytics of the data is the key, predictive or otherwise. R language is well suited for this task and more and more companies will be looking for folks with this skill.

Analytics will be the beacon to follow by all enterprises in the future. Having mere data and not being able to act on it is something that everyone will rush to avoid. R language, a language meant for statisticss and analytics will be one of the most sought after skills in the near future.

Microsoft has thrown in a lot to be at the forefront, not only acquiring the Revolution Analytics Company but also making it one of the prime features of SQL Server 2016.

Microsoft will be rebranding Revolution Enterprise for Hadoop, Linux and Tera data as R Server.
If you are interested in this growing technology area you could jump start downloading the R Server Developer Edition which is free (as free can be). It's just like all the other developer's stuff, has all the features of enterprise but not production worthy.

Also Revolution R Open has been rebranded as Microsoft R Open which is also free. For those who are already using MicrosoftData Science Virtual will have a preinstalled R Server. It is not a stretch that R Server will be on Azure as well for machine learning and IOT.

Download R Server here.
http://blogs.technet.com/b/machinelearning/archive/2016/01/12/making-r-the-enterprise-standard-for-cross-platform-analytics-both-on-premises-and-in-the-cloud.aspx

Read more here:
http://blogs.technet.com/b/machinelearning/

Students following an acdemic course will be benefitted with the Microsoft DreamSpark Program .

Looking for Tables and Columns in SQL Server 2012

In SQL Server 2012, sys.columns is a view you can find in the node System Views in the msdb database.

The various columns in sys.views are shown in this next image. You can find everything about the Columns in this view.



If you know the ObjectID of the Column of a table in the database, you can find its name from this query, the intellisense gives you an idea of this selection.

SysColumnName.png

A typical query and its output are as shown for finding the column name from ObjectID.

SysColumnName_02.png

If you know the Column_Name then you can find Column_ID, the intellisense gives you an idea of this selection.

SysColumnID.png

A typical query and its output are as shown for finding Column ID from Column Name.

SysColumnID_02.png

Starting from table name you can get the columns from these queries:

You can also get the ObjectID of the table from this query:

Use Northwind
go
Select *
From sys.tables
where tables.name='Employees'
go

The above query returns one value=245575913
Using the above you can get a whole lot of information for the columns from this query:

Select *
from sys.columns
where Columns.object_id=245575913

Creating a ColumnStore Index using the SQL Server Management Studio

ColumnStore index is  described in this post .

Of course you need Enterprise edition of SQL Server 2012 ColumnStore Index as on no other version it is supported. This post uses the SQL Server 2012 Developer Edition for demonstration.

However, if you have a developers edition of SQL Server 2012 you can see a few basic steps as described here.

Here are some step-by-step screen shots to create the  index

We will be creating the ColumnStore index for the Employees table which has already the following indexes.

In order to create a ColumnStore Index (only one per table) right click Indexes node and choose the Non-Clustered Columnstore Index as shown.

This brings up the New Index window as shown. The windows comes up with a default name for the index which you change or modify.

Modify or change name and click on the Add... button as there should be at least one column for the index. When you click on Add... button, the Select Columns from 'dbo.Employees' windows is displayed as shown.

Place check mark for the columns to be included. Here in three columns will be added. Preferably the data types of columns are either int or nvarchar. The one likely to be advantageous are those that are repeated in the table. Here Country, City and Reports to column are added.

Click OK and the information gets into the New Index window.

Click OK on the New Index window and the index is created as shown in the Indexes node in Object Explorer.

 You can look for this in sys.indexes as shown

Creating and Executing a SSIS Project Package - Part 4

This post is about deploying a SSIS package using SQL Server Data Tools.
Before you start on this post review the following:

http://hodentekmsss.blogspot.com/2015/12/creating-and-executing-ssis-project.html
http://hodentekmsss.blogspot.com/2015/12/creating-and-executing-ssis-project_28.html
http://hodentekmsss.blogspot.com/2015/12/creating-and-executing-ssis-project_29.html

There are two modes deployment SSIS 2012:

• A mode to deploy individual packages
• A mode to deploy SSIS projects

SSIS Project deployment is the default for SSIS 2012. Verify that the project you created earlier has no problems. For this, bring up the project in Visual Studio and execute the package as shown in Part 3.

In Solution Explorer right click the project and select Deploy Package as shown:

deploy_02

The wizard Integration Services Deployment Wizard gets displayed. There are a couple of steps (5) you need to follow. This wizard will deploy the package to an Integration Services Catalog on an instance of SQL Server 2012.

deploy_03

Click Next. You need to select the destination.

deploy_04

Click in the box under Server name: and click Browse...

Browse for Servers window is displayed as shown displaying the instance name.

deploy_04a

Click OK to Browse for Servers window.  The Browse for Project window is displayed the SSISDB is the only object.

deploy_04b

SSISDB is organized as folders and the Projects are housed in the folders.
Click on New folder...button. The Create New Folder window is displayed as shown.

deploy_04c

Provide a name and a description(Optional) and click OK.

The Browse for Project now contains the folder you created, SSIS_Dec27Pkg.

deploy_04d

Click New project...The Create New Project window us displayed.

deploy_04e

Provide a name and a description and click OK. The Browse for project window now displays the Project (All these names I have given were taken from my SSIS project created in Parts 1, 2 and 3).

deploy_04f

Now click OK. Now the Select Destination page of the wizard has all the needed information as shown.

deploy_04g

Click Next. The Review you selections section of the Review page is displayed.

deploy_05

Just check the Source and Destination paths and click Deploy.

After some processing the results are displayed in the Result page of the wizard as shown.

deploy_06

Looks like the deployment failed for some reason. Clicking the link Failed brings up the next window.

deploy_07FailedLink.png

The message appears to indicate failing to find a stored procedure. Searching for the package on the SQL Server instance using PowerShell did indicate the creation of folders and projects but did not find the package.

deploy_08.png

On a hunch, the SQL Server Data Tools was closed and opened with administrative privileges and
the package was deployed to the same folder in the SQL Server.

This time the deployment was successful with any errors.

deploy_09.png

The next image shows the package in the Integration Services Catalogs in SQL Server Management Studio.

deploy_10.png

For getting a jump start on SSIS review my popular book and describes some 20 routine tasks that you can configure. The version may be different but the procedures have not changed much.

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

Installing SQL Server 2012 SP3 on Windows 10

Service pack SP3 for SQL Server 2012 fixes many of the bugs and adds other enhancements. The list is long, please go here for details.

http://support.microsoft.com/En-us/enkb/3072779

Download your copy if SP3 executable from here:
https://www.microsoft.com/en-us/download/details.aspx?id=49996

When this executable is clicked the program begins to kick in with this initial window. SQL Server 2012 update checks the files.

SP3_01

Click Next brings in the License Agreement window.

SP3_02

After agreeing click Next.

SP3_03

Selected features for the only existing version of SQL Server 2012, the Regency Park instance.

 SP3_04

This is the present version on this computer. Click Next.

SP3_05

The Check Files in Use command is processed. There were two files active, the Full text directory service and the WD Backup service. These were flagged during ‘Check files in USE’. These were stopped in Control Panel|…|Services window and clicked on the Refresh recheck.

 SP3_06

Clicking Update begins the process of the upgrade.

SP3_07

 SP3_08

The upgrade completed successfully. The next two windows shows the same window as above but scrolled to show more info.

SP3_09

  SP3_10

The following changes were made to the program listing in the Control Panel.

SP3_11

After SP3 installation a configuration manager app gets added to All Apps on Windows 10

The version details are as follows:

Developer Edition
11.3.6020.0