Hi there!

Photo credit: https://www.flickr.com/photos/kalexanderson/5696097036/
Looking through binoculars/en spanare. Credit: Kristina Alexanderson

Hi there, fellow learner!

Welcome to this blog with articles about SQL Server, TSQL, SSIS, SSRS, SSAS, Power BI, Databases, Data Visualization, Tips, Administration, and Productivity.

Mostly.

Check out the post categories and the tag cloud in the sidebar to get a feel of what else you can expect to read here. I have contributed scripts and tools to MSDN TechNet and Wikis. Check out the Download section for a list.

Do you like awesome learning opportunities and resources? Me too! I often share that kind of stuff on Twitter.

Are you facing a challenge in learning about SQL Server? Tell me about it. I love to listen and might have some inputs. Let me know via comments, email or a tweet.

Do subscribe via email or connect on social media so that we can keep in touch. Scroll to find the links at the bottom of the page.

By the way, if you were wondering about the name of the blog –

erudition
[er-yoo-dish-uh n, er-oo-]
noun
knowledge acquired by study, research, etc.; learning; scholarship.

100s of Free Microsoft eBooks

Mirosoft Director of Sales Excellence, Eric Ligman, writes a post about 100s of FREE Microsoft eBooks each year.

These are legal DRM-free copies (PDF/EPUB/MOBI) on various Microsoft technologies like Windows, SQL Server, Azure, SharePoint, PowerShell, Visual Studio, Office, Dynamics etc.

The titles may repeat in multiple links below so I’d suggest that you begin browsing at the latest year URL and go backwards from there.

2017

https://blogs.msdn.microsoft.com/mssmallbiz/2017/07/11/largest-free-microsoft-ebook-giveaway-im-giving-away-millions-of-free-microsoft-ebooks-again-including-windows-10-office-365-office-2016-power-bi-azure-windows-8-1-office-2013-sharepo/

2016

https://blogs.msdn.microsoft.com/mssmallbiz/2016/07/10/free-thats-right-im-giving-away-millions-of-free-microsoft-ebooks-again-including-windows-10-office-365-office-2016-power-bi-azure-windows-8-1-office-2013-sharepoint-2016-sha/

2015

https://blogs.msdn.microsoft.com/mssmallbiz/2015/07/07/im-giving-away-millions-of-free-microsoft-ebooks-again-including-windows-10-windows-8-1-windows-8-windows-7-office-2013-office-365-sharepoint-2013-dynamics-crm-powershell-exchange-se/

2014

https://blogs.msdn.microsoft.com/mssmallbiz/2014/07/07/largest-collection-of-free-microsoft-ebooks-ever-including-windows-8-1-windows-8-windows-7-office-2013-office-365-office-2010-sharepoint-2013-dynamics-crm-powershell-exchange-server-lync-2/

2013

https://blogs.msdn.microsoft.com/mssmallbiz/2013/06/18/huge-collection-of-free-microsoft-ebooks-for-you-including-office-office-365-sharepoint-sql-server-system-center-visual-studio-web-development-windows-windows-azure-and-windows-server/

2012

https://blogs.msdn.microsoft.com/mssmallbiz/2012/07/27/large-collection-of-free-microsoft-ebooks-for-you-including-sharepoint-visual-studio-windows-phone-windows-8-office-365-office-2010-sql-server-2012-azure-and-more/

SSIS Error DTS_E_OLEDBERROR with code: 0x80040E55

SQL Agent and SSIS packages have multiple failure codes but DTS_E_OLEDBERROR or 0x80040E55 don’t tell you much about the cause.

One of my jobs failed sporadically with the following error.

Message
Code: 0xC0202009
Source: [29]
Description: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred.
Error code: 0x80040E55.
An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80040E55
Description: "Column does not exist.".
End Error

Error: 2016-11-30 11:20:03.11
Code: 0xC0047038
Source: SSIS.Pipeline
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on returned error code 0xC0202009.
The component returned a failure code when 
the pipeline engine called PrimeOutput().
The meaning of the failure code is defined 
by the component, but the error is fatal and 
the pipeline stopped executing.
There may be error messages posted before this
with more information about the failure.
End Error

The error message “Column does not exist.” led me to think that the stored procedure or one of the tables have been altered.

A quick look at the created date and modified date of the stored procedure and the tables confirmed that there were no schema changes.

So why the missing column error?

I executed the stored procedure manually and that revealed the issue.

There was TRY CATCH block in the procedure. It didn’t re-raise the error but returned it with a SELECT statement.

For example –

BEGIN TRY
  -- some sample sql
  SELECT
    o.OrderNumber,
    o.OrderLineNumber
  FROM
    dbo.Order as o
END TRY
BEGIN CATCH
  SELECT
     ERROR_NUMBER() AS ErrorNumber
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

So when there was any error, the error message was returned as a SELECT output.

The column names of this output were different from the original columns. This caused the SSIS package to fail.

Take away –
Use RAISERROR or THROW in TSQL to return the errors to the calling application. Using a SELECT for this purpose can confuse the calling application to consider that as a data set.

SQL Formatting and Object Naming Convention

SQL formatting may not increase the functionality of the code but good and consistent formatting by the developers certainly affect the readability of the code and therefore makes it easier to maintain. Keyword and separator alignment, spaces and tabs provide opportunities for a lot of variations in code layout. A case-insensitive server/database give further flexibility in formatting with choosing upper or lower casing of the text.

Just as an experiment to see the effect of casing and object naming conventions on readability, I created this dummy stored procedure and formatted it in four different ways. The white-space layout, i.e. tabs, spaces, newline etc is same in all of them.

Which one do you prefer?

Do you have your own layout options that you would like to share?

Feel free to copy the script and share your formatted version in comments.

Version 1

-- underscore as word separator
-- lowercase keywords
create procedure dbo.sp_get_employee
	@employee_id int,
	@department_id int
as
begin
	select
		e.first_name,
		e.last_name,
		e.designation,
		e.salary,
		e.manager_first_name,
		e.manager_last_name,
		d.department_name
	from
		dbo.employee as e with (nolock)
		inner join dbo.department as d with (nolock)
		on e.department_id = d.department_id
	where
		employee_id = @employee_id and
		department_id = @department_id;

	return 0;
end

Version 2

-- underscore as word separator
-- UPPERCASE keywords
CREATE PROCEDURE dbo.sp_get_employee
	@employee_id INT,
	@department_id INT
AS
BEGIN
	SELECT
		e.first_name,
		e.last_name,
		e.designation,
		e.salary,
		e.manager_first_name,
		e.manager_last_name,
		d.department_name
	FROM
		dbo.employee AS e WITH (NOLOCK)
		INNER JOIN dbo.department AS d WITH (NOLOCK)
		ON e.department_id = d.department_id
	WHERE
		employee_id = @employee_id AND
		department_id = @department_id;

	RETURN 0;
END

Version 3

-- camel/pascal casing for objects names
-- lowercase keywords
create procedure dbo.spGetEmployee
	@EmployeeId int,
	@DepartmentId int
as
begin
	select
		e.FirstName,
		e.LastName,
		e.Designation,
		e.Salary,
		e.ManagerFirstName,
		e.ManagerLastName,
		d.DepartmentName
	from
		dbo.Employee as e with (nolock)
		inner join dbo.Department as d with (nolock)
		on e.DepartmentId = d.DepartmentId
	where
		EmployeeId = @EmployeeId and
		DepartmentId = @DepartmentId;

	return 0;
end

Version 4

-- camel/pascal casing for objects names
-- UPPERCASE keywords
CREATE PROCEDURE dbo.spGetEmployee
	@EmployeeId INT,
	@DepartmentId INT
AS
BEGIN
	SELECT
		e.FirstName,
		e.LastName,
		e.Designation,
		e.Salary,
		e.ManagerFirstName,
		e.ManagerLastName,
		d.DepartmentName
	FROM
		dbo.Employee AS e WITH (NOLOCK)
		INNER JOIN dbo.Department AS d WITH (NOLOCK)
		ON e.DepartmentId = d.DepartmentId
	WHERE
		EmployeeId = @EmployeeId AND
		DepartmentId = @DepartmentId;

	RETURN 0;
END

SSIS Naming Convention – Updated for SSIS 2016

I use an SSIS naming convention that is based on assigning a prefix to each type of component. My first version of SSIS naming convention list was based on SSIS 2008 R2. Since then, Microsoft has introduced new connections, tasks, sources and destinations that are mostly related to Azure, Hadoop, ODATA and CDC.

With the launch of SQL Server 2016, I have updated my list to include all those new components and assigned prefixes to them. There are minor changes to some existing prefixes too e.g. RBLDIDXT is now REBLDIDXT and few others like this.

Why Should You Use a SSIS Naming Convention

A naming convention may not appear to be a big deal at the time of SSIS development because you have a GUI development environment that makes it very easy to identify the components. But during the support and maintenance phase, when trying to read the execution logs or error messages, bad naming choices make it difficult to identify the source of errors quickly. Having a consistent naming convention across the enterprise makes a lot of difference in supporting the packages.

Download

Feel free to reuse the conventions in the list or use it as a template to define your own. I’d love to hear your thoughts and suggestions in the comments, Twitter or Facebook.

You can download the complete list at the link below –

=> Download SSIS Naming Convention (Updated for SSIS 2016) <=

Example screenshot –

SSIS Naming Convention
Screenshot of the SSIS Naming Convention Excel Workbook

Download Free SQL Server 2016 Developer Edition

Microsoft announced the general availability of SQL Server 2016 today. This version has many path-breaking updates for performance, security and business intelligence. On top of that, the Developer Edition of SQL Server 2016 and 2014 are now completely free! There is no restriction on features, capabilities or how long you can use them! Read on to find out how to download free SQL Server 2016 Developer Edition and other goodies.

In these Developer Editions, the databases do not have to be smaller than X GB, the feature Y will not be disabled, the time is not restricted to Z days (i.e. 180 days) and you don’t have to pay any $ Dollars. These Developer Editions have all the features of the Enterprise Edition, but should not be used for Production.

Download Free SQL Server Developer Edition

To download the Developer Edition, all you need is a free membership to the Visual Studio Dev Essentials and then go to the following URL to download the installation file.
Download Free SQL Server 2014 and 2016 Developer Edition

Download Free SQL Server Tools

If you don’t plan on downloading the full install of SQL Server yet, you can still get the latest tools.

New 2016 Version of SQL Server Management Studio (SSMS)

SSMS is a free client tool for general SQL Server management and TSQL development. It is compatible with prior versions of SQL Server too.
https://msdn.microsoft.com/library/mt238290.aspx

New 2016 Version of SQL Server Data Tools (SSDT)

SSDT is a free development tool that is a subset of Visual Studio IDE. It is focused on database and business intelligence related development i.e. TSQL, SSIS, SSRS and SSAS.
https://msdn.microsoft.com/en-us/library/mt204009.aspx

Download Free eBook

While at your downloading spree, get the final version of the free MS Press eBook that gives an overview of the latest features in the SQL Server 2016 version.

Introducing Microsoft SQL Server 2016
Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

Authors: Stacia Varga (t), Denny Cherry (t), Joseph D’Antoni (t)
Pages: 215
ISBN: 978-1-5093-0195-9
https://info.microsoft.com/Introducing-SQL-Server-2016-eBook.html

Although the title says that it is an introductory book, it is certainly not a beginner’s level text. In its nine chapters, it introduces the new features of SQL Server 2016. So it will be more useful if you have already worked with SQL Server for a while and are aware of features (or possibilities for enhancements) in the previous versions.

Free SQL Server eBook - Introducing SQL Server 2016

Free Developer Editions of SQL Server 2014 and 2016

Microsoft is releasing its Developer Editions of SQL Server 2014 and 2016 for free!

Developer Edition is the same as the Enterprise Edition but it is licensed for development and testing purposes only.

The Developer license for the previous versions (2008 R2, 2012) used to go for about $50 and up. Another option was to download the fully functional edition valid for 180-days and later buy a licence.

More information about Microsoft’s new approach to developer licensing is at our Facebook post –

Use 130 Characters to Store an Object Name

SQLErudition.wordpress.com-Use-130-Characters-to-Store-an-Object-Name

Some index maintenance scripts or dynamic SQL scripts use SQL object names as values for variables or columns i.e. database name, table name, index name etc.

SQL Server object names can be at most 128 characters long, so common wisdom is to declare the holding variable or column as SYSNAME data type or one of the alphanumeric datatype with a width of 128 characters.

Example –

declare @dbname sysname
declare @tablename nvarchar(128)
declare @indexname varchar(128)

This is technically correct.

Another technically correct thing to do is to use the QUOTENAME function to wrap the object name in [ and ] brackets. This handles those cases where there are special characters in the name or the name is a reserved keyword.

Example –

set @dbname = quotename(db_name(db_id()))
set @tablename = quotename(object_name(object_id))
set @indexname = quotename(object_name(object_id))

The Issue

The brackets will add two more characters to the value.

So for really long object names that are 128 characters in length, the value would be 128 + 2 = 130 characters.

The two extra characters will break the variable assignment or row insert statements with truncation error –

String or binary data would be truncated. [SQLSTATE 22001] (Error 8152).

In my shop, we do have some long index names and I have had to debug some scripts for this issue. And this is not the first time!

The Solution

I suggest that you use 130 as the width for the variables or columns that will store an object name. SQL Server object names can be maximum 128 characters long so using 130 characters in scripts will handle the extra two characters.

Simple Fix to a Backup Restore Error Due to Disk or Cluster Resource Issue on SQL Server

One of our database restore attempt failed with an error message that mentioned cluster resources. At least the error message indicated that the issue was not related to backward compatibility but rather a physical resource or cluster settings.

Error Details

The Error Message Window –

SQL Restore Error - sqlerudition.wordpress.com
SQL Restore Error – sqlerudition.wordpress.com

The Error Message –

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'MYDEVSQLSERVER'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&

EvtSrc=Microsoft.SqlServer.Management.Smo.

ExceptionTemplates.FailedOperationExceptionText

&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Cannot use file 'J:MSSQL10_50MSSQLDATAMyDatabaseName.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Both the links in the error message above pointed to a missing information message on Microsoft website –

No information on the restore error.
No information on the restore error – sqlerudition.wordpress.com
Details
ID: Restore Server
Source:
We’re sorry
There is no additional information about this issue in the Error and Event Log Messages or Knowledge Base databases at this time. You can use the links in the Support area to determine whether any additional information might be available elsewhere.
Thank you for searching on this message; your search helps us identify those areas for which we need to provide more information.

Cause and Resolution

We determined the cause rather quickly. The source system of the backup file had a drive letter layout that was different from the destination server. The restore process was trying to create the data files on a drive that didn’t exist on the destination! So the location of the files was changed in the restore dialog to a correct drive letter of the destination server. After that the restore progressed normally.

Embed Facebook Posts in WordPress. But Why?

Facebook with WordPress - aalamrangi.wordpress.com

Posts on a WordPress blog can be shared on social media sites like Facebook, Twitter etc. You can do the reverse too and share Facebook page posts in a WordPress blog post which will include the Facebook comments, likes and shares. The Facebook page timeline has to be public.

So you first post something on your blog. Then you share it on Facebook. Then you again share the Facebook post back to your blog! Why would someone do this kind of cross posting?! The answer is, to promote your Facebook page with your blog readers and vice-a-versa. Even if you are not promoting a page (because it is not YOUR page), Facebook posts from others’ pages can still be useful to your blog readers or relevant to what you already write about.

Now, why Facebook? A Facebook user is most likely just browsing, not actively looking for solutions to any technical issue. But they sure love to share and read interesting stuff. So by posting to a Facebook page, there is a higher chance of someone liking or sharing it with their friends and as a result catch the attention of another casual browser. Oh yes, there are social-sharing buttons on a WordPress post too. But those buttons will be used by a reader who is already on your blog. Also, in some situations the sharing buttons may not even work due to restrictions like work network, device issues etc. The point here is to engage a casual reader on Facebook who is not actively looking for content on a search engine or a forum.

On the other hand, most of blog readers are looking actively for a solution to an issue. There is a high chance that they have been directed to the blog via a search engine result or a technical forum thread about a specific issue. (Check your stats, duh!) Very few are here for general reading. As a blogger, I would like to capture the interest of this “accidental” reader and hope that they return. One way for me to stay connected with them is to have them subscribed to or follow my blog through (at least one or preferably) various channels like email, RSS, WordPress, Twitter, Facebook etc. Some people may be reluctant to share their email address but may be willing to Like the Facebook page. Sending a new reader to your Facebook page and getting them to Like your Facebook page is equivalent to getting a new subscriber. Of course the restrictions mentioned above will come into play here too but the opportunity can still be used to make the reader aware of a Facebook page. With its huge active user count, Facebook is a good medium for outreach and engagement. Major websites report that bulk of their views are driven by social media sites. Consider the Facebook cross-post as a banner ad for yourself!

To promote your blog, you can either use your personal Facebook profile page or create a new page for the blog. I would suggest the latter so that you don’t spam your non-tech friends with technical rants.

I plan to experiment with various blog promotion strategies, especially in the technical niche, and write in more detail about them. I have started a Facebook page for this blog and will share my adventures periodically. If you are interested in following my learning path, you can subscribe to this blog or like my Facebook page. (Notice what I did right there?!)

For now, here is an embedded Facebook post –

Resolve Error: 102 while creating Full-Text Index Stoplist in SQL Server

Full-text index stoplist error 102One of my SQL Server databases was returning an error 102 while creating a full-text stoplist. We were trying to create a stoplist based on the system stoplist and later also tried to create a blank stoplist. The error happened both via SSMS, and equivalent TSQL commands.

The Error, 102

The following TSQL gave the error –


USE [DEMO_Database]
GO
CREATE FULLTEXT STOPLIST [DemoStopList]
AUTHORIZATION [dbo];
GO

CREATE FULLTEXT STOPLIST [DemoStopList]
FROM SYSTEM STOPLIST
AUTHORIZATION [dbo];
GO

The error dialog box –

Full-text index stoplist error 102
Image 1 (click to enlarge)

The text in the error message –

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot execute changes.

------------------------------
ADDITIONAL INFORMATION:

Create failed for FullTextStopList 'Demo'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+FullTextStopList&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Incorrect syntax near 'STOPLIST'. (Microsoft SQL Server, Error: 102)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

The Cause

I looked at the MSDN page related to the TSQL command to check if I was using the right syntax.

REFERENCE:

  • CREATE FULLTEXT STOPLIST (Transact-SQL)
    https://msdn.microsoft.com/en-us/library/Cc280405(v=sql.105).aspx

My syntax was correct but there was something else on the page that looked relevant. Right at the top of the documentation page is the following message –

Important noteImportant
CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported only under compatibility level 100. Under compatibility levels 80 and 90, these statements are not supported. However, under all compatibility levels the system stoplist is automatically associated with new full-text indexes.

To verify if the compatibility level of my database could indeed be an issue, I checked the properties of the database by –

SELECT
is_fulltext_enabled,
compatibility_level
FROM
sys.databases
is_fulltext_enabled compatibility_level
0 90

There you have it! My database was originally on a SQL Server 2005 installation so its compatibility level was 90, and that was the reason the CREATE/ALTER/DROP STOPLIST commands were unavailable. The current server that I was working on was SQL Server 2008 R2, which could be checked by –

SELECT @@VERSION
GO
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1

So the resolution to the error lies in changing the compatibility level. As per the documentation, the highest compatibility level I could go on a SQL Server 2008 R2 installation was 100.

REFERENCE:

  • View or Change the Compatibility Level of a Database
    https://msdn.microsoft.com/en-us/subscriptions/index/bb933794
  • ALTER DATABASE Compatibility Level (Transact-SQL)
    https://msdn.microsoft.com/en-us/subscriptions/index/bb510680

Changing the Compatibility Level

I checked that no other users were connected to the database and then issued this command to change the compatibility level.

USE [master]
GO
ALTER DATABASE [DEMO_database]
SET COMPATIBILITY_LEVEL = 100;
GO

It ran successfully and I could verify in the sys.databases catalog view that the compatibility level has changed to 100.

Now I was able to create a Stop List, Full-text Catalog and a Full-text Index on my table, and was able to run queries using the CONTAINS and CONTAINSTABLE keywords.

Fixed? Not so fast!

Interestingly, even though I could use the Full-text features now, the is_fulltext_enabled property still showed up as 0 (i.e. Disabled).

That was fixed by running the following –

EXEC [DEMO_Database].[dbo].[sp_fulltext_database]
@action = 'enable'
GO

REFERENCE:

  • sp_fulltext_database (Transact-SQL)
    https://msdn.microsoft.com/en-us/library/ms190321(v=sql.105).aspx