Exam 70-461 Notes for Chapter 5 Grouping and Windowing

Although exam 70-461 is based on SQL Server 2012, the code here is based on the Wide World Importers database and SQL Server 2016.

Chapter 5 is about data analysis using sub-queries and aggregate queries. Lessons include: 1. Writing Grouped Queries, 2. Pivoting and Unpivoting, and 3. Using Window Functions.

Use grouped queries to group data and then perform analysis computations on each group. The set of attributes your data is grouped on is called the grouping set. You can define one or more grouping sets in a single query.

Continue reading “Exam 70-461 Notes for Chapter 5 Grouping and Windowing”

Call a Web Service using SQL Server Integration Services then Store and Query the Results

Introduction

Often enough, there is a huge benefit in knowing how to leverage SQL Server Integration Services to accomplish a data task that depends on a remote data source. This is a tutorial about a seemingly simple problem and solution that involves calling a web service from a SQL Server Integration Services package, storing the XML in an XML-typed column in a SQL Server database table, then extracting the data using XQuery. Note that web services may also return JSON, which I’ll explain and write a tutorial about later. This simple process and solution can be repeated to gather information from virtually any public web service. Use this method with care and purpose.

Continue reading “Call a Web Service using SQL Server Integration Services then Store and Query the Results”

Solving SQL Server Integration Services Expressions

Solving SQL Server Integration Services Expressions using Expression Builder is a bit of a chore until you have a good understanding and a few working examples of some useful expressions. Here are several common exceptions you might see when working within Expression Builder as well as examples and explanations of correct syntax.

The exception:

Cannot convert System.Int32 to System.String

Here are a couple more exceptions you may come across:

“Cannot convert expression value to property type.”
“Cannot convert ‘System.DateTime’ to ‘System.String’.

Continue reading “Solving SQL Server Integration Services Expressions”

Dotnet Migrate

Time to Migrate from project.json to csproj!

Most of the time I just want the facts. So, here are the facts.

Now that Visual Studio 2017 has been released and my Visual Studio Code updates have brought it to Version 1.10.2, when I opened a recent project, a project created in Visual Studio Code that includes a project.json file, I received the following error when attempting to run dotnet run:

“Couldn’t find a project to run. Ensure a project exists in … Or pass the path to the project using –project”

Now what?

Continue reading “Dotnet Migrate”