Returning a default value when SQL Server doesn't return any rows.

Sometimes you have the need to run a query but if it returns no rows you still want a response.  I've done this with some monitoring queries and even some structural ones.  Here's an example of how you can accomplish it.

Step one create a table or table variable.  In this case I'm just creating a quick table to hold some demo values.

DECLARE @testtable1 TABLE (
testid int identity (1,1),
testvalue int)

Now populate that table variable.  

DECLARE @insertcounter int
SET @insertcounter = 0  

-- inserts 101 rows into the table from 0 - 100

WHILE @insertcounter < 101      
BEGIN         
INSERT INTO @testtable1(testvalue)   
VALUES (@insertcounter)   
SET @insertcounter = @insertcounter +1  

END;  

Now select the table and make sure it populated as expected

SELECT * FROM @testtable1;

This part shows why the simple ISNULL or COALESCE doesn't work in this case- no rows no values.

SELECT

ISNULL(testvalue,0) as testvaluenull,
COALESCE(testvalue,0) as testvaluecoalesce

FROM @testtable1
WHERE testid >101

This is what I have found to work.  Create the subquery where the column would generally go and then when it returns nothing you get the outside value which in this case is zero.

SELECT
ISNULL((SELECT testvalue from @testtable1 where testvalue > 101),0) as testvaluethatworks

Change the default value to get what you need.

SELECT
ISNULL((SELECT testvalue from @testtable1 where testvalue > 101), 9999) as testvaluethatworks

Or if you're ready to take it to the ╰[ ⁰﹏⁰ ]╯level then change the column to NChar and use the hex of your favorite emoji like:

DECLARE @testtable1 TABLE (
testid int identity (1,1),
testvalue  nchar (255))

--use the below for the final query

SELECT
ISNULL((SELECT testvalue from @testtable1 where testvalue > 101),  NCHAR(0xD83D)+ NCHAR(0xDE20) ) as testvaluethatworks

 

This is the non permanent code to this example using a table variable

-- Declare the table variable, this has an identity for ease of tracking

DECLARE @testtable1 TABLE (
testid int identity (1,1),
testvalue int)

--declare the counter variable so you can insert values into the table from a while loop

DECLARE @insertcounter int
SET @insertcounter = 0

-- inserts 101 rows into the table from 0 - 100

WHILE @insertcounter < 101
BEGIN
INSERT INTO @testtable1(testvalue)
VALUES (@insertcounter)
SET @insertcounter = @insertcounter +1
END;

--Selects the entire table variable so you can see what happened SELECT * FROM @testtable1;

--this part shows why the simple isnull or coalesce doesn't work in this case- no rows no values. SELECT

ISNULL(testvalue,0) as testvaluenull,
COALESCE(testvalue,0) as testvaluecoalesce

FROM @testtable1
WHERE testid >101

-- HOW TO MAKE IT HAPPEN -- NESTED SUBQUERY so if the subquery is zero it supplants the value SELECT ISNULL((SELECT testvalue from @testtable1 where testvalue > 101),0) as testvaluethatworks'''

 

 

 

Customizing the SQL Server Templates in SQL Server Management Studio / SSMS

Sick of the standard templates in SSMS?  Want something specific?  Changing it is pretty simple.  I'm using SQL Server 2016 but this should work back a bunch of versions.

We'll edit the Stored Procedure template.  You can find the templates at
C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\Stored Procedure

You can replace the 130 with the version you're working with.  

  • 90 = SQL2005
  • 100 = SQL2008
  • 110 = SQL2012
  • 120 = SQL2014
  • 130 = SQL2016

The templates that we're going to edit are found here:

The default template looks like:

I don't like the default template so I default it to me as the author, get rid of the top text and due to a recommendation from Ken Fisher Add Return to the top so you don't get an execution that you don't want.   To edit the template I open Notepad ++ using right click > Run as Administrator 

Now you can save that to replace the default template or add your own.  
The alternative method would be to open the template browser and edit it there.  You get that from view >template explorer.  It will open wherever you have the object explorer and other options usually open.

Ken Fisher can be found at

Using a lookup as an inner join across instances in SSIS

I recently had a need to pull data from two different SQL instances and use one instance as the validation for a second one.  I was creating an inner join between SQL instances.  No linked tables or open query.  Depending on your environment you'll need an SSIS Service account that has execute (or read) to both instances so you can get the data back and the ability to use the SSIS service account to write a file somewhere. 

With that aside I'll do a step but step way to create a basic package that queries two sql instances and uses a lookup to give you the inner join effect and writes a file.

I'm using Visual Studio Community 2015 (If you don't have dev essentials yet go get it) and SQL 2014 Developer edition for this guide.

 

Step 1:  Launch Visual Studio then click file>new> project.  Choose integration services as below

 

 

Now drag a data flow task onto the control flow

 

Next go to the Data Flow Tab and add the first OLEDB Source.  This the the data main source not the comparison.

Now you can add the comparison source OLEDB.  You don't connect it to the lookup as the data flowing through is just going to use it as a filter not as an additive.  There's a different transform for that type operation.

Now set up the lookup transform

 

Next step make the equi/ inner join criteria in the columns section

 

Lastly set up the flat file output.  I prefer the CSV output over excel as the overwrite capacity is built into the transform.

Go back to the Control flow and execute the package by right clicking on it and choose execute.

Now confirm that it all executed as expected by checking the data flow for green check marks.

 

 

 

 

New SQL User can't log in (error 18456)

Setting up a test user on SQL Server

I needed to set up a test user for some specific demonstrations.  I created the login and then the user assigning it to dbo and the role of public and db_datareader. 

 

When I attempted to login I received the following error.  Microsoft SQL Server, Error 18456.

 

 

I checked the specific information in the error and then the event viewer to see what the error was being caused by. 

 

 

 

This error appeared under task category logon and was very straightforward stating that the SQL instance isn’t configured to allow for SQL and Windows Authentication.  I generally don't use the direct logins so this makes sense.

The fix was just to change the properties for the instance to allow for both types of connections

 

 

 

 

Just restart the SQLServer service and you’re good to go.

 

 

Microsoft. (n.d.). Change Server Authentication Mode. Retrieved February 21, 2016, from https://msdn.microsoft.com/en-us/library/ms188670.aspx?f=255&MSPPError=-2147217396

 

SQL Server 823 Error - Local installation

 

 I have a local installation of SQL Developer Edition on my laptop and I’ve noticed that I’ve been getting a series of 823 errors recently and I’m attributing that to having the SQL installation on C: a solid state drive and the database files on D: a spindle.  I ‘m confident that the SQL service starts up on boot as expected and then when it tries to connect to D: has a failure as the solid state and service is ready before the spindle.

 

The error looks like:

 

If you try to attach a database you’ll see it doesn’t find the drive and remains greyed out.

 

 

Any actions you attempt will result in the same error as there’s no master db.

 

The main text of the error is:

  TITLE: Microsoft SQL Server Management Studio
------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000272000

 

The fix.  The first thing you can try is to simply restart the SQL Server Service.  Navigate to the services window and down to SQL Server(MSSQLSERVER) right click and restart.

 

 

 

Let it stop then start the service with right click restart or stop then start. 

 

You could also use a script if you want to have something quick and aren’t planning additional mitigation steps.  Here’s a sample Powershell script

Stop-Service -f MSSQLSERVER
Start-Sleep -s 20
Start-Service MSSQLSERVER
get-service MSSQLSERVER

 

 

 

Check and see if it worked by refreshing the database list in SSMS.

 

For me this is effective.  As a mitigation I set the SQL service to delayed start.  The delay is hard to pin down but for me it seems to be about 1-2 minutes after boot and setting this to delayed doesn’t seem to cause problems with the other SQL services.  I’m working on researching dependencies but if you have any issues or other resources I’d love to know.

 

 

 

 

 

 

 

 

 

 

 

Heap to clustered table

This article came to me when my kids were sitting around eating skittles.  My youngest had a pile of them and was sorting them in to colors and counting them.  It for some reason made me think of a heap.  A heap might look like

 

 

A heap is a table without a clustered index (1).  No not in a pile but not in a particular order.  We probably don’t want to store most data like that but it does have its place. (2) If the table is very small, the data is never retrieved or the table is only written to never updated or deleted from then you’d have an ordered heap or something so small no performance concerns would be raised.  Regardless if you do retrieve data you have to look at everything to get what you want.  A heap would be equivalent to a book with no chapters or page numbers.  It might be in the right order but you’d have to read the entire book to be sure. 

                 Further using the book example, if we inserted the book into a SQL database we could have a heap table with a column for page number, a column for chapter name and a column for the text on each page.  This would leave us with a data page for the integer page numbers, likely leaving the chapter names on the same page and a page for text as that would likely be a text or other large object field.  Depending on size there may be more pages but distinctly there would be at least two.  Although in a table, the data being stored as a heap would make for a confusing book potentially coming out different each time.  That wouldn’t be acceptable to we need order.

                We want order in our book to make it possible to read.  So I would add an index.   If I want the data to be ordered I use a clustered index (one per table).  If I decide I don’t need it ordered (and/or its not unique) but would like it more easily searchable, and I have a large number of distinct values I can use a non-clustered index.

In skittles logic a nonclustered index in the first column might look like this.

 

 

Somewhat distinct although not unique.  No particular order.  The table is still a heap since it has no clustered index.  The values would be orange, purple, green, red and yellow.  The query optimizer might not find a use for that index and instead opt for a table scan but if it was useful it could speed things up.  In the book example

  • ·         Yellow is chapter one
  • ·         Orange is chapter two
  • ·         Red is chapter three
  • ·         Green is chapter 4
  • ·         And purple is chapter 5

  If we wanted chapter one that could be returned but without an order by statement we’d get back a somewhat random order.

 

To go back to the book example if I was designing a database like a book the clustered index would be page numbers as they are unique (although not required to be unique) and need to be ordered specifically.  The chapters would be a nonclustered index as they should be relatively distinct but not as specific as a page number.  A skittles clustered index might look like this.

 

  • ·         Yellow is page one
  • ·         Orange is page two
  • ·         Red is page three
  • ·         Green is page four
  • ·         Purple is page 5

They are unique, appropriately ordered but still require an order by statement to assure return order.  We retrieve the book in page order as we would normally expect to read it.  If we wanted a section we would have the ability to pull the chapter out using the nonclustered index. 

In summary we started with a heap, added a nonclustered index and then changed from a heap table into a clustered table by adding clustered index to order the table on the disk for ease of retrieval. 

 

 

(1)   Microsoft. (n.d.). Heaps (Tables without Clustered Indexes). Retrieved February 13, 2016, from https://msdn.microsoft.com/en-us/library/hh213609.aspx

(2)   Microsoft. (n.d.). Using Nonclustered Indexes. Retrieved February 13, 2016, from https://technet.microsoft.com/en-us/library/aa933130(v=sql.80).aspx  

 

String Parser to the rescue

Today I encountered a table that someone had decided to put in a semi colon delimited string column with hours that were stored as the number of seconds in the day.  So it contained values that looked like:

27000;41400;59400;79200;  Which were supposed to represent 7:30, 11:30, 16:30 and 22:00.  

I had two problems 1 get the comma delimited fields into columns and 2 get the times into hours.  

For step one I used the function from this thread.  Its a bit old but I'm still on SQL Server 2008 R2 so it was perfect.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

When I tried it right out of the box I recieved the following error:

 

Msg 8116, Level 16, State 1, Procedure fn_UDF_ParseString, Line 16
Argument data type text is invalid for argument 1 of reverse function.

Msg 8116, Level 16, State 1, Procedure fn_UDF_ParseString, Line 16Argument data type text is invalid for argument 1 of reverse function.

So I changed the datatype in the text parameter from TEXT to NVARCHAR(max) and it worked just fine.  

You then call it like this :

dbo.fn_UDF_ParseString(-1,';',ht.OccurrenceTimes) as Occurence_1,  -1 for the first delimiter and -2 for the second and so on.  

I was close now because  My query results look like:



Great now I needed to get the time from seconds of the day into hours and minutes so the end users would have a pleasant view of the data.

I referenced http://blog.sqlauthority.com/2014/08/17/sql-server-convert-seconds-to-hour-minute-seconds-format/ as I'm a huge fan of Pinal Dave.  


I only needed hours and minutes so I just used 2/3 of the conversion 

ISNULL(RIGHT('0' + CAST(ots.Occurence1 / 3600 AS VARCHAR),2),'') + ':' +

ISNULL(RIGHT('0' + CAST((ots.Occurence1 / 60) % 60 AS VARCHAR),2),'') AS occurence_1,

I then added the isnull as I like to format all my concatenations with that so my output is consistent with a value.  I most often use crystal and am ok with the blank (:) placeholder. 

I also elected to take the parse function and insert that result into a temp table and then do the string conversion in the query from the temp table for readibility from a future standpoint but it worked perfectly.