T-SQL Tuesday #191 - String Parsing

For T-SQL Tuesday #191, Steve Jones (blog) asked about favorite string parsing routines.

T-SQL Tuesday #171 - Describe the Most Recent Issue You Closed

This month T-SQL Tuesday is hosted by Brent Ozar (blog). For this month's T-SQL Tuesday, we are asked to describe the most recent ticket or issue that we closed.

Trigger Windows Scheduled Task to Run Upon the Successful Completion of Another Scheduled Task

In one of the projects that I worked on, I have two tasks/jobs on Windows Task Scheduler, let say the name of the first job is “FirstJob” and the second job is “SecondJob”. I have a requirement where the “SecondJob” should run immediately after the “FirstJob” has been completed successfully. There are several ways to achieve this. For this particular project, to fulfil the requirement, on the “SecondJob”, I would trigger the “SecondJob” based “On an event”, which the the successful completion of the “FirstJob”.

Books in Review - 2018

Back in 2017, one of my personal goal for the year was to read more books. In 2017, I managed to read 25 books. For a complete list of books that I read in 2017, it can be found on Books in Review - 2017. I tried to keep it up in 2018. In 2018, I was able to read 25 books as well. There were a lot of good books to read.

Executing xp_cmdshell with Non SysAdmin Account

In SQL Server, xp_cmdshell is an extended stored procedure that is stored in the master database that would allow us to execute Windows command shell. By default, the ability to execute xp_cmdshell is disabled. We could enable the ability to run xp_cmdshell by executing the following query against the SQL Server instance:

Windows 10 – Mobile Hotspot

A few weeks ago, I was doing a demo on a couple of Alexa skills that I've developed using Amazon Echo Dot. However, the place where I have to do the demo use enterprise class Wi-Fi (it is where after connecting to the Wi-Fi network, we need to authenticate using web browser). Unfortunately, at the moment, as far as I know, Amazon Echo Dot doesn't support enterprise class Wi-Fi. To make matter worse, my cell phone signal was very weak in the meeting room, so I was unable to use my cell phone as hotspot.

T-SQL Tuesday #99 – Work-Life Balance

This month T-SQL Tuesday is hosted by Aaron Bertrand (blog | twitter). For this month's T-SQL Tuesday, we are given two options. The first option is to write about something that we are passionate about outside of SQL and tech community. The second option is to write about T-SQL bad habit. Mr. Bertrand has a long list of them. For this entry, I choose to write about activities that I do outside SQL or tech community #sqlibrium.

Books in Review - 2017

One of my personal goal for 2017 was to read more books. I was gunning to read 2 books a month. In 2017, I read 25 books (1 more book than the yearly target goal). I am not a fast reader. Normally, I read books when I commute to and from work. Most of the books were rented from local library. I used to prefer hard copy books than digital books. However, a couple of years ago, for non technical books, I made the transition to get digital (Kindle) books whenever possible. It felt awkward at first, but after reading several books on my kindle, I am starting to get used to it. The good thing is that my local library also has a good selection of books in digital format. Since 2017 has just ended, I think it would be good to inventory the books that I've read in 2017. It seems that I read various genres of books from fiction, self-help, autobiography, etc.

PASS Summit Video Recordings

PASS (Professional Association for SQL Server) Summit is the largest conference for data professional working with Microsoft SQL Server and Business Intelligence stack. Typically, PASS would have the Summit once a year (in the fall) in Seattle, Washington. PASS Summit offers a wealth of knowledge. Each year, there are hundreds of sessions on various SQL Server and Business Intelligence topics presented by industry experts and also Microsoft employees. If you are a data professional, who are working with Microsoft SQL Server and Business Intelligence, PASS Summit is a great event to learn and network with fellow data professionals. If you were unable to attend the past PASS Summits (or if you attended the past PASS Summits and want to relive and review those sessions), you can go to SQL PASS site and watch the past PASS Summit Session Recordings for free. As of the writing of this blog post, you can view recordings for PASS Summit 2015 and older for free (you need to be a member of SQL PASS but the registration is free). Even though they might be a few years old, they are excellent learning resources.

By the way, if you can attend PASS Summit in person, I would recommend it. PASS Summit 2018 is going to be in Seattle, Washington, in November 2018. You can get more information from PASS Summit 2018 site.

T-SQL Tuesday #97 - 2018 Learning Goals

This month T-SQL Tuesday is hosted by Malathi Mahadevan (blog | twitter) and it is about 2018 learning goals. Given that we are at the end of 2017, this is a timely topic. As data professionals and technology enthusiasts, we are living in an interesting time. There are a lot of opportunities to learn, so much so that we might feel overwhelmed by them. Goals can certainly help to focus our learning efforts.

Backup Analysis Services Database Using PowerShell

We can use PowerShell to backup Analysis Services (AS) multidimensional or tabular database. This is great because it offers us more flexibility in automating the AS database backup process. Typically, we might have multiple Analysis Service databases within an Analysis Services instance. As a good Database Administrator responsible for the Analysis Services, we want to backup all those Analysis Services databases.

Remove Log Shipping When Secondary Server Not Available

The other day, I came accross a scenario where I need to remove log shipping from a database on the primary server. However, we have already shutdown/decommissioned the secondary server. If we try to disable the log shipping from the database properties window, we would most like get the following:

Reflection on Steve Stedman's Database Corruption Challenge

Over the past few months, Steve Stedman (blog | twitter) has been conducting database corruption challenge. It is a challenge where Steve would post corrupt SQL Server databases and the participants would try to fix the corruption to the best of their ability. The participants would need to avoid any data loss when trying to fix the database corruption.

Change Data Capture (CDC) and Simple Recovery Mode

One of the misconceptions that some people have about change data capture (CDC) in SQL Server is that it needs the database to be set up with full recovery mode. If we look at the following TechNet article, it mentioned:

It should be noted that change data capture works with all recovery models. But when change data capture is enabled, operations that would normally be minimally logged in simple or bulk-logged recovery models are fully logged to enable change data capture to capture all changes.

SQL Script: Database Auto Grow and/or Auto Shrink Report

Below is a SQL script that can be used to list recent auto grow and/or auto shrink events that happened on your SQL Server. It queries the SQL Server default trace. By default, default trace is enabled (you can check this by running: sp_configure 'default trace enabled').

PowerShell Script: List SQL Server Analysis Service Role Permission By Database and Cube

A while ago, I wrote a couple of blog entries in regards to using PowerShell scripts in order to list roles within the database and their members. The script can be found here and here.

Let’s try to expand on those scripts further.

PowerShell Script: Adding Database into Always On Group

Every once in a while, I have  a new database that I need to put into an existing Always On group. So I have decided to create a PowerShell script that would do just that. The script can be found here.

Stale View

If you have non-schema bound view in SQL Server and the underlying table was updated, you potentially can have a stale view. A quick way to demonstrate this:

Let say that you have the following table:

CREATE TABLE dbo.Contact (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(10),
    LastName VARCHAR(10));
GO

INSERT INTO dbo.Contact VALUES ('John', 'Doe');
INSERT INTO dbo.Contact VALUES ('Jane', 'Doe');

Then, create a simple view against the newly created table:

CREATE VIEW dbo.vwContact
AS
    SELECT *
    FROM dbo.Contact
GO

If we run the following query:

SELECT Id, FirstName, LastName
FROM dbo.vwContact

We would get the expected result:

Now, what happen if we add a new column to the Contact table:

ALTER TABLE dbo.Contact
ADD Country VARCHAR(10) NULL;
GO

UPDATE dbo.Contact
SET Country = 'USA'
WHERE Id = 1;

If we run the following query against the vwContact view:

SELECT    Id, FirstName, LastName, Country
FROM    dbo.vwContact;

We would get the following error message:

Msg 207, Level 16, State 1, Line 28

Invalid column name 'Country'.

That’s odd, it looks like the view does not recognize the newly added Country column.

Ok, let say that your company got a new contact, his name is Mr. Rhino Hippopotamus. Obviously, you will need to have a bigger last name column. So you decide to change the LastName column from varchar(10) to varchar(20).

ALTER TABLE dbo.Contact
ALTER COLUMN LastName VARCHAR(20);
GO

INSERT INTO dbo.Contact VALUES ('Rhino', 'Hippopotamus', 'USA');
GO

If you try to query the view using the following query (let’s forget about the Country column for now):

SELECT Id, FirstName, LastName
FROM dbo.vwContact;

That seems to work just fine. You get the expected result:

But, what happen if you try to run the same query from another server, using a linked server:

SELECT Id, FirstName, LastName
FROM ServerA.TestDB.dbo.vwContact;

Instead of getting the expected result, we got the following error:

Msg 7347, Level 16, State 1, Line 1

OLE DB provider 'SQLNCLI11' for linked server 'HSQL01' returned data that does not match expected data length for column '[ServerA].[TestDB].[dbo].[vwContact].LastName'. The (maximum) expected data length is 10, while the returned data length is 12.

If we check on the column property for the LastName column of vwContact view from the server that has the linked server to the original server, using the following query, we would notice that somehow, the LastName column is still listed as having 10 characters long:

USE [master];
GO

EXEC sp_columns_ex 'ServerA', 'vwContact', 'dbo', 'TestDB', 'LastName', NULL;

Solution

So how to address the issue that we are having with stale view. There are several ways:

We could drop and recreate the view. In the above example, we can run the following query:

IF (OBJECT_ID('dbo.vwContact') IS NOT NULL)
    DROP VIEW dbo.vwContact
GO

CREATE VIEW dbo.vwContact
AS
    SELECT *
    FROM dbo.Contact
GO

We could also run an alter view statement:

ALTER VIEW dbo.vwContact
AS
    SELECT *
    FROM dbo.Contact
GO

Alternatively, we could refresh the view using the following script:

EXEC sp_refreshview 'dbo.vwContact';

SQL Server Agent Job Last Run Time and Result

I've seen situations in which we have some SQL Server Agent jobs that are scheduled to run very rarely  (like once every year or once every half year). Either because of audit or troubleshooting, we need to find out those SQL Server Agent jobs last run time and their outcome. However, the SQL Server Agent history is being purged every few months (Notes: purging SQL Server Agent history periodically is not a bad thing, it is actually a good practice to keep the msdb database size manageable, esp. if your environment has lots of SQL Server Agent jobs, in which many of them have lots of job steps, and some of the jobs are scheduled to run frequently - every few minutes or even seconds).

Are we out of luck? Well not necessarily.

Demo

First create a SQL Agent job with the following script:

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SQL Job Test', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Step 1', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'select 1', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Step 2', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'select 2', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:


GO

Then we run the job using the following script:

USE [msdb];
GO

EXEC sp_start_job @job_name=N'SQL Job Test';
GO

Once that has been completed, there should be a job history for the SQL Server agent job:

Now purge the job history by running the following:

USE [msdb];
GO

EXEC dbo.sp_purge_jobhistory @job_name=N'SQL Job Test';
GO

After purging the job history, we will no longer have the entries for “SQL Job Test” job on dbo.sysjobhistory table in the msdb database.

If we try to query the sysjobhistory, using the following query, we would get 0 row:

USE [msdb];
GO

SELECT    *
FROM    dbo.sysjobhistory
WHERE    job_id IN (
        SELECT job_id
        FROM dbo.sysjobs
        WHERE [name] = N'SQL Job Test');

Or this query:

USE [msdb];
GO

EXEC sp_help_jobhistory @job_name = N'SQL Job Test';
GO

Gulp! No more history for SQL job “SQL Job Test”. But wait, if we look at the job properties, we can see when the job was last executed.

So the information must still be in the database. If we try to run the following query:

USE [msdb];
GO

EXEC sp_help_job @job_name = N'SQL Job Test';

We can see the job’s last run date, time and even outcome (For the definition of last_run_outcome column, you can check the following MSDN article). There’s more. When you run the sp_help_job stored procedure, you can also see each of the job step’s last run date, time and outcome.

If we investigate it further, the last run date, time and outcome are stored on sysjobservers and sysjobsteps within the msdb database. We can query them using the following:

USE [msdb];
GO

SELECT    j.job_id,
        j.Name,
        js.last_run_outcome,
        last_run_outcome_desc = CASE 
            WHEN js.last_run_outcome = 0
                THEN 'Failed'
            WHEN js.last_run_outcome = 1
                THEN 'Succeeded'
            WHEN js.last_run_outcome = 3
                THEN 'Canceled'
            ELSE 'Unknown'
            END,
        js.last_outcome_message,
        last_run_datetime = dbo.agent_datetime(
            CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
            CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END),
        js.last_run_duration
FROM    dbo.sysjobs j
        INNER JOIN dbo.sysjobservers js 
        ON j.job_id = js.job_id

Or if you want to see it for each of the job’s steps:

USE [msdb];
GO

SELECT    j.job_id,
        j.Name,
        js.last_run_outcome,
        last_run_outcome_desc = CASE 
            WHEN js.last_run_outcome = 0
                THEN 'Failed'
            WHEN js.last_run_outcome = 1
                THEN 'Succeeded'
            WHEN js.last_run_outcome = 3
                THEN 'Canceled'
            ELSE 'Unknown'
            END,
        js.last_outcome_message,
        last_run_datetime = dbo.agent_datetime(
            CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
            CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END),
        js.last_run_duration,
        jst.step_id,
        jst.step_name,
        step_last_run_outcome = jst.last_run_outcome,
        step_last_run_outcome_desc = CASE 
            WHEN jst.last_run_outcome = 0
                THEN 'Failed'
            WHEN jst.last_run_outcome = 1
                THEN 'Succeeded'
            WHEN jst.last_run_outcome = 2
                THEN 'Retries'
            WHEN jst.last_run_outcome = 3
                THEN 'Canceled'
            ELSE 'Unknown'
            END,
        step_last_run_datetime = dbo.agent_datetime(
            CASE WHEN jst.last_run_date = 0 THEN NULL ELSE jst.last_run_date END, 
            CASE WHEN jst.last_run_time = 0 THEN NULL ELSE jst.last_run_time END)
FROM    dbo.sysjobs j
        INNER JOIN dbo.sysjobservers js 
            ON j.job_id = js.job_id
        INNER JOIN dbo.sysjobsteps jst
            ON j.job_id = jst.job_id

WHERE    j.name = N'SQL Job Test';

Clean-up

To clean up the demo, you can run the following script:

USE [msdb];
GO

EXEC sp_delete_job @job_name = N'SQL Job Test';

GO

File Share Witness

If you have cluster environment and using the file share witness, you can use the following command line (using cluster.exe) to get the location of your file share witness:

cluster.exe res "FileShare Witness" /priv

Or if you prefer to use PowerShell, you can use:

Get-ClusterResource "File Share Witness" | Get-ClusterParameter | fl