The Bit Bucket

Are you trying to connect to a SQL Server instance and ending up with the error:

The certificate chain was issued by an authority that is not trusted

You aren’t alone.

SQL Server 2005 introduced authentication encryption (by default) in the SQL Native Access Client (SNAC). SQL Server will self-generate a certificate that’s then used unless you replace it with your own certificate.

If you do use your own SSL (Secure Sockets Layer) certificate for SQL Server, unless it’s a publicly trusted certificate, your client system will need to trust that certificate. Generally that means that you’ll need to list your own certificate authority (CA) as a trusted publisher on each of your client systems. Then that would work well.

2020-01-16

As part of our free SDU Tools for developers and DBAs, we have many scripting functions. Moving or recreating object-level permissions between SQL Server databases can be time consuming.  To allow scripting these out, we’ve added a tool called ScriptDatabaseObjectPermissions.

It’s a stored procedure (as it has to change databases) and takes a single parameter: @DatabaseName (sysname) as the name of the database whose object permissions you want to script.

2020-01-15

I was in another discussion recently where someone was worried about running out of bigint values. I come across this regularly. A few months back, another customer was in the middle of changing all their bigint values to GUIDs as they were worried about running out of bigints.

People don’t seem to understand just how large a bigint really is. It’s certainly hard to imagine.

How big is a bigint?

I remember reading an article back in 1992. They said that if you got an 8 bit computer, put zero in a register, and made it loop on an instruction to increment the register value, you’d have 256 operations before it overflowed. That would happen in the blink of an eye.

2020-01-14

I previously mentioned that there were three functions for replacing values in strings. We saw REPLACE and STUFF. The other one is relatively new (SQL Server 2017) and it’s called TRANSLATE.

Here’s an example of how I can use it to change the format of a phone number:

The first parameter is the string to work on, the second parameter is a list of individual characters to replace, and the third parameter (which must be a string the same length as the second parameter), is the set of replacement characters.

2020-01-13

I’ve been working to convert our SQL Down Under sample databases like PopkornKraze across to Snowflake. One of the areas that I ran into real challenges with was sequences.

The good news

The syntax for creating and accessing them is basic enough.

CREATE SEQUENCE "SequenceName";

is enough.  (Note the double-quotes are mine as I don’t want the name auto-wrapped to SEQUENCENAME).

And there are some of the same options that SQL Server has:

2020-01-10

There was a recent question on the forums about if there was any way to create a sql parameter with defined type in typescript fashion. What they were after was something like this:

create procedure something
   @myparam 'int' | 'varchar(20)'
as
  ...

They wanted a single parameter that could have different data types, but only from a specific list of data types.

SQL Server doesn’t support that type of construct directly, but what it does do is support the sql_variant data type. So you could have defined the procedure like this instead:

2020-01-09

Version 17 of our SDU Tools shipped out to all our SDU Insiders a few days ago.

It contains the following updates:

DatesInPeriod - Produces a table of dates from a starting date, for a number of intervals. For example, 3 weeks of dates from a starting date.

ServerMaximumDBCompatibilityLevel - Returns the maximum database compatibility level supported by the server.

SetDatabaseCompabilityForAllDatabasesToMaximum​ - Sets the database compatibility level of all databases to the maximum value supported by the server.

2020-01-08

I do a lot of speaking at conferences, user groups, online, and other sorts of events. It’s important to constantly improve, so I take notice of any books released on the topic. I recently listened (via Audible) to TED Talks - The Official Ted Guide to Public Speaking by Chris Anderson.

I’ve been a fan of TED and their conference talks for a long time. The overall quality of the talks is very high, and some (like Ken Robinson’s talk on schools killing creativity) set a really high bar.

2020-01-07

For a long, long time,  users of SQL Server had requested some way to split a string. That’s a common need when working with rows from comma-delimited files (CSVs).

In the example below, I’ve asked it to break up the string ‘Greg,Tom’,Sandra’ whenever it finds a comma. Notice I could use another delimiter like TAB or semicolon instead.

The values returned are in a table. This is a table-valued function.

2020-01-06

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