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