The Bit Bucket

There was a recent question asked on Stack Overflow. The poster asked:

“I have this stored procedure and I need to fetch result of select query as output:”

USE abc
GO 

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE OR ALTER PROCEDURE ABC_XYZ_LOAD 
AS 
BEGIN  
    SELECT * FROM [CNT_01] 
END 
GO

Now, it always annoys me when the Stack Overflow rule enforcers decide that there wasn’t enough info there and close it. (Particularly when it was a new contributor).

2020-01-05

I posted last time that I’d completed my Part 1 eBook on Core Concepts. The Part 2 eBook is on the differences in the SQL language between Snowflake and SQL Server.

This next series of blog posts will focus on the query language, particularly on DML (data manipulation language). DDL (data definition language i.e. CREATE, ALTER, DROP) differences will be covered in the Part 3 series on administration.

Snowflake has quite a rich SQL language but it’s quite different to the T-SQL offered by SQL Server in many ways.  I’m planning to work right through the Snowflake SQL language documentation and try everything in it, so you don’t have to.

2020-01-03

I was recently answering a forum question about working with a hierarchy. The poster had a table of nodes but importantly, nodes could be disabled. If that happened, the children of the node would move up to their nearest parent that wasn’t disabled.

That is, if their parent was disabled and their grandparent was also disabled, then their great-grandparent would become their effective parent.

I provided the following code example, and I hope it helps someone.

2020-01-02

I wrote recently about menu items being missing from SSIS and other BI projects in VS2019, but another thing that’s gone MIA is the Integration Services Connector for Power Query.

This is the connector that allows you to use Power Query (and the M language) to input data into SSIS.

The SSIS designer for VS2019 recently went GA (general availability). It’s great that it’s released, but if you install it, you’ll find it removes the Power Query option for SSIS.

2020-01-01

If you’ve recently installed Visual Studio 2019 (VS2019), and then installed the SQL Server Reporting Services (SSRS), the SQL Server Analysis Services (SSAS), and/or SQL Server Integration Services (SSIS) extensions, when you open a project you might be wondering where your menus went.

How it was

For example, if you had a report open in the VS2017 report designer, this menu was present:

And if you had an AS model open in the VS2017 tabular designer, these menus were present:

2020-01-01

As part of our free SDU Tools for developers and DBAs, we have many scripting functions. SQL Server allows you to create your own database roles, and generally, you should do this instead of using the fixed database roles like db_owner, or db_datareader and db_datawriter, as it lets you allocate just the required permissions. To allow scripting out the permissions assigned to these roles, we’ve added a tool called ScriptUserDefinedDatabaseRolePermissions.

2020-01-01

Wow, it’s been a really big year in many ways. I had a list of goals for the year, and overall I’m pretty happy with where it’s ended up. One area that remains a challenge for me is personal health. But there are things I have in place that make me confident that one will be addressed better in 2020.

I liked the way that a few of my friends this year posted that they aren’t making New Year resolutions, they’re just stating goals for the next year. That’s a great way to look at things.

2019-12-31

It’s useful to be able to find one string within another string. T-SQL has two basic functions for that: CHARINDEX and PATINDEX.

CHARINDEX is used to see if one string appears within another string. Take a look at this code:

In this first example, I asked SQL Server to look for the string lot in the string Hellothere. You can see that it found it at position 4.

2019-12-30

I’ve had a lot of great feedback on my series of blog posts about Snowflake, and explaining the concepts for an existing SQL Server audience.

Just out for Christmas is a new free eBook for our SDU Insiders. I’m preparing a three part series on Snowflake for SQL Server users:

• Part 1: Core Concepts (available now)
• Part 2: SQL Language (coming soon)
• Part 3: Administration (sometime after that :-))

You can get part one now! And did I mention free? (for SDU Insiders)

2019-12-27

One issue that comes up time and again in the forums is when an INSERT statement fails with a violation of a UNIQUE or PRIMARY KEY constraint but when the user checks the existing table, the value that’s being complained about isn’t already in the table.

The Symptom

For example, a table might have primary key values of 2, 12, and 14.

When an INSERT is performed, there is an error telling you that you can’t insert a duplicate key value of say 15.

2019-12-26