Transact SQL – Warning – “No Join Predicate”

 

Background

A couple of weeks ago, I noticed a warning in a query plan.

The warning read “No Join Predicate“.

Query Plan

Warning – No Join Predicate

Sample Queries

Conventional Join

Code

use [DBLab]
go

if object_id('school.usp_TripParticipants_List_NJP_Legacy') is null
begin

    exec('create procedure school.usp_TripParticipants_List_NJP_Legacy as ')

end
go

alter procedure [school].[usp_TripParticipants_List_NJP_Legacy]
as

begin

    ; with cteParticipant
    as
    (
        select *

        from   [school].[student]

        union 

        select *

        from   [school].[faculty]

    )
    select 

              [trip]
				 = tblT.[name]
            , [tripDate]
				 = [tblT].tripDate

            , [participant]
				=
				  tblP.firstname
				+ ' '
				+ tblP.lastname

    from
          [school].[tripParticipant] tblTP
        , [school].[trip] tblT
        , cteParticipant tblP

    where tblTP.tripID = tblT.id

    -- and   tblTP.participantID = tblP.id

end

go

Query Plan

school__usp_TripParticipants_List_NJ__20180618__1145A

Explanation
  1. In this example we are using the old classic join
  2. We have three tables, but only joined the first two tables

 

ANSI Join

Code

use [DBLab]
go

if object_id('school.usp_TripParticipants_List_NJP') is null
begin

    exec('create procedure school.usp_TripParticipants_List_NJP as ')

end
go

alter procedure [school].[usp_TripParticipants_List_NJP]
as

begin

    ; with cteParticipant
    as
    (
        select *

        from   [school].[student]

        union 

        select *

        from   [school].[faculty]

    )
    select 

              [trip] = tblT.[name]
            , [tripDate] = [tblT].tripDate
            , tblP.firstname
            , tblP.lastname

    from   [school].[tripParticipant] tblTP

    inner join [school].[trip] tblT

        on tblTP.tripID
            = tblT.id

    inner join cteParticipant tblP
        /*
            on tblTP.participantID
                = tblP.id
        */
        on tblTP.tripID
             = tblT.id

end

go

Query Plan

school__usp_TripParticipants_List_NJ__20180618__1150AM

Explanation
  1. In this example we are using ANSI join
  2. We have three tables and two join clauses
  3. Unfortunately the second join does not actually join the two tables that it is meant to join
    • We intentionally cut and pasted the early join.
    • But, did not modify the pasted clause and reference the actual tables we are trying to join

 

Summary

The samples listed above are simple and easy to correct.

The one I actually ran into is a bit more difficult to address.

 

 

Transact SQL :- Get Object Dependency Using sys.dm_sql_referenced_entities

 

Background

In a previous post we covered using sys.sql_expression_dependencies to track the objects utilized by our programmable object.

The post that uses sys.sql_expression_dependencies is here.

In this post we will discuss using sys.dm_sql_referenced_entities to accomplish same.

sys.dm_sql_referenced_entities

ResultSet

Column Description Version Active
is_selected The object or column is selected. SQL Server 2012 (11.x)
is_updated The object or column is modified ( Insert/Update/Merge). SQL Server 2012 (11.x)
is_select_all The object is used in a SELECT * clause (object-level only). SQL Server 2012 (11.x)
is_insert_all The object is used in an INSERT statement without a column list (object-level only). SQL Server 2016 (13.x) through SQL Server 2017.
is_incomplete The object or column has a binding error and is incomplete. SQL Server 2016 (13.x) SP2

Code

Stored Procedure

[dbo].[sp_ObjectReferencedEntities]

SP Code


use [master]
go

if object_id('[dbo].[sp_ObjectReferencedEntities]') is null
begin

    exec('create procedure [dbo].[sp_ObjectReferencedEntities] as ')

end
go

alter procedure [dbo].[sp_ObjectReferencedEntities]
(
      @object sysname
    , @referencingClass nvarchar(60) = 'object'
)
as

begin

    set nocount on;
    set XACT_ABORT on;

    declare @strLog nvarchar(600)

    if object_id(@object) is null
    begin

        set @strLog =
                        'In database '
                        + db_name()
                        + ', '
                        + @object
                        + ' not found!'
                        ;

        raiserror( @strLog, 16,1);

        return;

    end

    ;with cteObjectDependencies
    as
    (

        -- find dependency
        -- exec sp_help 'sys.dm_sql_referenced_entities'
        select

          [referecedObjectClass]
            = sre.referenced_class_desc

        , [referencedObjectID]
            = sre.referenced_id

        , [referencedObject]
            =
                isNull
                (
                    quoteName
                    (
                        sre.referenced_schema_name
                    )
                    + '.'
                , ''
                )
                + quoteName
                    (
                        sre.referenced_entity_name
                    )

            , [referencedObjectType]
                = objRef.[type_desc]

            , [isSelected]
                = case
                    when (sre.is_selected=0) then 'N'
                    else 'Y'
                  end	

            , [isInsert]
                = case
                    when (sre.is_insert_all=0) then 'N'
                    else 'Y'
                  end	

            , [isUpdated]
                = case
                    when (sre.is_updated=0) then 'N'
                    else 'Y'
                  end	

            , [isCallerDependent]
                = case
                    when (sre.is_caller_dependent=0) then 'N'
                    else 'Y'
                  end	

            , [isAmbiguous]
                = case
                    when (sre.is_ambiguous=0) then 'N'
                    else 'Y'
                  end	

        FROM sys.dm_sql_referenced_entities
            (
                  @object
                , @referencingClass
            ) AS sre   

        INNER JOIN sys.objects AS obj
            ON object_id(@object) = obj.object_id

        LEFT OUTER JOIN sys.objects AS objRef
            ON sre.referenced_id = objRef.object_id	

    )
    select
            distinct

           [referencedObject]

         , [referencedObjectType]

         , [referecedObjectClass]

         , [isSelected]

         , [isInsert]

         , [isUpdated]

         , [isCallerDependent]

         , [isAmbiguous]

         , [referencedObjectID]

    from   cteObjectDependencies cteOD

    order by

           [referencedObject]

         , [referencedObjectType]

end
go

exec sp_MS_marksystemobject '[dbo].[sp_ObjectReferencedEntities]'
go

Invoke


declare @object sysname

set @object = '[school].[usp_TripParticipants_List]'

--exec sp_depends @object

exec [dbo].[sp_ObjectReferencedEntities]
        @object = @object

Output

objectDependency_20180615_1217PM.png

Source Code Control

GitHub

DanielAdeniji/TransactSQL.ObjectDependency
Link

Summary

sys.dm_sql_referenced_entities offers more compared to sys.sql_expression_dependencies.

The former tracks usage and enjoys better support for identifying code that is a bit brittle.

 

Transact SQL :- Get Object Dependency Using sys.sql_expression_dependencies

 

Background

As a database Administrator, one has to quickly look at SQL and try to identify which objects it touches.

API

There are a couple of pathways to identify objects that a programmable object references.

Inclusive are:

  1. sp_depends
  2. sys.sql_expression_dependencies

Code

Sample

Procedure

SP – [school].[usp_TripParticipants_List]

Here is a SP that list those going on a trip…..


use [DBLab]
go

if object_id('school.usp_TripParticipants_List') is null
begin

    exec('create procedure [school].[usp_TripParticipants_List] as ')

end
go

alter procedure [school].[usp_TripParticipants_List]
(
    @tripID bigint
)
as

begin

    set nocount on

    set XACT_ABORT on

    ; with cteSchoolBody
    as
    (
        select
                 tblS.id
               , tblS.identifier
               , tblS.firstname
               , tblS.lastname
               , [pool] = 'Student'

        from   [school].[student] tblS

        union all

        select
                 tblF.id
               , tblF.identifier
               , tblF.firstname
               , tblF.lastname
               , [pool] = 'Faculty'

        from   [school].[faculty] tblF

    )
    select
              [tripID] = tblT.[id]

            , [tripName] = tblT.[name]

            , cteSB.[pool]

            , cteSB.lastname

            , cteSB.firstname

            , cteSB.identifier

    from   [school].[trip] tblT

    --inner join [school].[tripParticipant] tblTP
    inner join [tripParticipant] tblTP

        on tblT.id = tblTP.tripID

    inner join cteSchoolBody cteSB
        on tblTP.participantID = cteSB.id

    where (

            (
                tblT.[id]
                    = isNull(@tripID, tblT.[id])
            )

          )

    order by
              [tripName]

            , [pool]

            , cteSB.lastname

            , cteSB.firstname

end
go

metadata

Procedure

SP – [master].[dbo].[sp_ObjectDependants]

Here is a SP that we use to seek out dependants….


use [master]
go

if object_id('[dbo].[sp_ObjectDependants]') is null
begin

    exec('create procedure [dbo].[sp_ObjectDependants] as ')

end
go

alter procedure [dbo].[sp_ObjectDependants]
(
    @object sysname
)
as

begin

    ;with cteObjectDependencies
    as
    (

        -- find dependency
        -- exec sp_help 'sys.sql_expression_dependencies'
        SELECT
            [referencingObjectID]
            = referencing_id

        , [referencingObject]
            =   quoteName
                (
                    OBJECT_SCHEMA_NAME ( referencing_id )
                )
                + '.'
                + quoteName
                    (
                        OBJECT_NAME(referencing_id)
                    )	

        , [referencingObjectType]
            = obj.[type_desc]

        , [referecedObjectClass]
            = sed.referenced_class_desc

        , [referencedObjectID]
            = sed.referenced_id

        , [referencedObject]
            =
                isNull
                (
                    quoteName
                    (
                        sed.referenced_schema_name
                    )
                    + '.'
                , ''
                )
                + quoteName
                    (
                        sed.referenced_entity_name
                    )

            , [referencedObjectType]
                = objRef.[type_desc]

            , [isCallerDependent]
                = case
                    when (sed.is_caller_dependent=0) then 'N'
                    else 'Y'
                  end	

            , [isAmbiguous]
                = case
                    when (sed.is_ambiguous=0) then 'N'
                    else 'Y'
                  end	

        FROM sys.sql_expression_dependencies AS sed

        INNER JOIN sys.objects AS obj
            ON sed.referencing_id = obj.object_id

        LEFT OUTER JOIN sys.objects AS objRef
            ON sed.referenced_id = objRef.object_id	

        WHERE sed.referencing_id = object_id(@object)

    )
    select

           [referencedObject]

         , [referencedObjectType]

         , [referecedObjectClass]

         , [isCallerDependent]

         , [isAmbiguous]

         , [referencedObjectID]

    from   cteObjectDependencies cteOD

    order by

           [referencedObject]

         , [referencedObjectType]

end
go

exec sp_MS_marksystemobject '[dbo].[sp_ObjectDependants]'
go

Demo

sp_depends

Sample


use [DBLab]
go

declare @object sysname

set @object = '[school].[usp_TripParticipants_List]'

exec sp_depends @object

Output

sp_depends_20180615_1215PM

[dbo].[sp_ObjectDependants]

Sample


use [DBLab]
go

declare @object sysname

set @object = '[school].[usp_TripParticipants_List]'

exec [dbo].[sp_ObjectDependants]
	@object = @object

Output

objectDependency_20180615_1217PM

Source Code Control

GitHub

DanielAdeniji/TransactSQL.ObjectDependency
Link

Summary

sp_depends tells us the action invoked on dependent objects.

It lets us know whether updates were made or just reads.

Whereas sys.sql_expression_dependencies does not appear to make that distinction.

Windows Subsystem for Linux ( WSL ) – Error – “The app that you are trying to run is not supported on this version of Windows”

 

Background

If you try to enable “Windows Subsystem for Linux” ( WSL ) on a Windows 10 machine, you may unfortunately run into the error noted in our title message.

 

Error

Error Message

Image

NotSupportedOnThisVersionOfWindows_20180612_1012PM.png

Textual


The app that you are trying to run is not supported on this version of Windows.

 

Steps to reproduce

Currently, I am able to reproduce this error very easily.

The pathway towards reproducing are simple and direct.

And, they are:

  1. lxrun.exe /install
  2. bash

The commands above are meant to enable Linux if not currently installed.

Why?

Why am I not able to enable Linux on my Windows 10 system.

Requirements

The basis requirements are :-

  1. Bitness
    • 32-bit ( NO )
    • 43-bit ( YES )
  2. MS Windows 10 – Marketing
    • Anniversary Update  ( 2016-August-2nd )
    • Creator Update ( 2017-April-5th )

 

Versioning

Version & Build

Let us go get our Version Number

There are a couple of ways to get our Version Number.

Outline

  1. Windows System
    • About
  2. Winver

Steps

Windows System – About

  1. Access Windows System
  2. In Windows System, click on the menu Item ( Help \ About )

Image

controlPanel_System_Help_About_20180613_1050PM

Explanation

  1. Windows Edition
    • Windows 10 Enterprise 2016 LTSB

 

winver

Image

winver_20180613_1058PM

Explanation

  1. Version :- 1607
  2. OS Build :- 14393.2312

Marketing

Let us use Wikipedia to map Version Number to the Marketing Name.

Image

Windows 10 version history
Link

wikipedia_20180613_1106PM

Explanation

Our version # is 1607.

1607 maps to “Anniversary Update”.

Can we get Linux ?

Can we enable Linux Sub-system?

MSFT’s documentation:

Install the Windows Subsystem for Linux
Link

Based on the doc referenced above, here are the Marketing Versions Supported:

  1. Anniversary Update  ( 2016-August-2nd )
  2. Creator Update ( 2017-April-5th )

But, we are still stuck!

Why Stuck

It appears that though we are able to take all the necessary steps :

  1. Enable Developer Mode
  2. Enable Linux Subsystem feature

but, because we are on LTSB, we are in a tight mud.

LTSB?

DUKE

TO LTSB or Not

Link

With the release of Windows 10 in 2015, Microsoft introduced a new sub-edition of Windows 10 Enterprise called “Long Term Servicing Branch” or “LTSB”. Each release of Windows 10 Enterprise LTSB will remain relatively unchanged–receiving only security updates and bug fixes, but no feature updates–through a 10-year lifespan.

To date, Microsoft has delivered two releases of Windows 10 Enterprise LTSB (2015 and 2016) with the next expected in 2019. While, according to Microsoft, LTSB was “designed for special-purpose PCs such as those used in point-of-sale systems or controlling factory or medical equipment”, some in IT have deployed it to common end-user computers, citing the benefit of having no Windows Store apps (which includes Microsoft Edge and Cortana) and no semi-annual feature updates to deal with.

However, recent articles and an updated Microsoft FAQ point out that, as released versions of Windows 10 Enterprise LTSB will not receive newer features, they will also not be supported on newer computer processors (such as Intel’s eighth-generation “Kaby Lake Refresh” architecture, released in August, 2017) . This introduces a potential down-side to deploying LTSB, but it’s not a new concept, as both Windows 7 and Windows 8.1, both still fully supported by Microsoft on older hardware, are only partially supported on Intel’s sixth-generation “Skylake” processors and are not supported on the seventh-generation “Kaby Lake” processors.

Windows 10 – Marketing Name via PowerShell

Background

Reading through MSFT’s documentation on installing the “Linux Subsystem on MS Windows” brought to light the need to familiarize oneself with the various marketing moniker for each Released build of MS Windows 10.

Literature

Here is the specific document…

Install the Windows Subsystem for Linux
Link

And, here are the specific texts that juiced my appetite:

Image

Falls Creator Update

linuxPlatform_FallCreatorUpdate_20180613_1150AM.png

Anniversary Update and Creators Update

linuxPlatform_AnniversaryAndCreatorsUpdate_20180613_1152AM.png

Explanation

So the question is which MS Windows 10 Update am I running ….

Referenced Work

Thanks goodness there are various credible sources doing the heavy work of lining up MSFT’s version #, Marketing Version, and Build Number  low & high bars.

Here are the sources we used for this post:

  1. Microsoft
    • Windows 10
      • Windows 10 release information
        Link
  2.  Wikipedia
    • Windows 10 version history
      Link
  3. PureInfotech
    • Mauro Huculak
      • Windows Update History information helps you know exactly what’s the latest version of Windows 10 available.
        Link

Image

Wikipedia – Windows 10 – Version History

wikipedia_Windows10VersionHistory_20170613_1225PM

Code

Text file

Text file – UpdateList.txt

Outline

  1. Here is our file’s content
    • Version
    • Marketing
    • Build Number – Min
    • Build Number – Max

1809, Windows 10 version 1809 (Late 2018), 17686.00, 17686.00
1803, Windows 10 version 1803 (April 2018 Update) history, 17133.73, 17134.83
1709, Windows 10 version 1709 (Fall Creators Update) history, 16299.15, 16299.461
1703, Windows 10 version 1703 (Creators Update) history, 15063.11, 15063.1112
1607, Windows 10 version 1607 (Anniversary Update) history, 14393, 14393.2189
1511, Windows 10 version 1511 (November Update) history, 10586.104, 10586.1176
1507, Windows 10 version 1507 (Initial Release) history, 10240.16683, 10240.17831

PowerShell

PowerShell – getWindows10MarketingName.ps1

Outline

  1. Read flat file ( updateList.txt)
  2. Get MS Windows Version via calling [Environment]::OSVersion
    • Build Number
  3. Get MS Windows by issuing “Get-WmiObject Win32_OperatingSystem
    • Returns
      • Caption
      • OSArchitecture
  4. Registry Access – Get-ItemProperty
    • Argument :- HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion
    • Returns
      • ProductName
      • ReleaseID
      • CurrentBuildNumber
      • CompositionEditionID
  5. Iterate read file
    • Condition to indicate match
      • If ReleaseID from registry Path HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion matches read record
      • If Build Number falls into range of read record
    • Save Pointer
  6. If Pointer Saved
    • Display OS Version from table

Preparatory

Registry
Registry – HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion

registry_20180614_0700AM.png

 

Actual Code


Set-StrictMode -Version 1
  

[boolean]$debug=$false;
[string] $objOSVerNumberAsString = $null;
[double] $versionNumberMinAsNumber = 0;
[double] $versionNumberMaxAsNumber = 0;
[boolean] $bConverted = $false;
[string] $strLog = $null;
#[string] $CHAR_EMPTY='';
#PowerTip: Create Empty String with PowerShell
[string] $CHAR_EMPTY=[string]::Empty;

[string] $productName =$null;
[string] $releaseID =$null;
[string] $currentBuild =$null
[string] $edition =$null;

[string] $releaseIDInList = $null;

$debug=$false;
#$debug=$true;

$updateListFilename = 'updateList.txt';

#read file updateList.txt
$updateList = Get-Content $updateListFilename;

#Display Update List
#$updateList;

#Get OS version [Environment]::OSVersion
$objOSVer =[Environment]::OSVersion;

#Display OS version
Write-Host('OS Version');
Write-Host('----------');
Write-Host($objOSVer);


$objOSVerPlatform = $objOSVer.Platform;
$objOSVerServicePack = $objOSVer.ServicePack;
$objOSVerNumber = $objOSVer.Version;
$objOSVerString = $objOSVer.VersionString;


$objOSVerNumberAsString = [system.String]::Join($CHAR_EMPTY, $objOSVerNumber);


$objOSVerNumber0 = $objOSVerNumberAsString.split('.')[0];
$objOSVerNumber1 = $objOSVerNumberAsString.split('.')[1];
$objOSVerNumber2 = $objOSVerNumberAsString.split('.')[2];
$objOSVerNumber3 = $objOSVerNumberAsString.split('.')[3];


$objOSVerNumberBase = $objOSVerNumber0;
$objOSVerNumberSP = $objOSVerNumber1;
$objOSVerNumberBuild = $objOSVerNumber2;
$objOSVerNumberMinor = $objOSVerNumber3;

if ( $debug)
{

    Write-Host('');

    Write-Host('OS Version ( broken into pieces)');
    Write-Host('--------------------------------')  

    Write-Host('OSVersionPlatform      :- {0}' -f $objOSVerPlatform);
    Write-Host('OSVersionServicePack   :- {0}' -f $objOSVerServicePack);
    Write-Host('OSVersionNumberAsArray :- {0}' -f $objOSVerNumber);
    Write-Host('objOSVerNumberAsString :- {0}' -f $objOSVerNumberAsString);
    Write-Host('OSVersionString        :- {0}' -f $objOSVerString);

    Write-Host('objOSVerNumberSP       :- {0}' -f $objOSVerNumberSP);
    Write-Host('objOSVerNumberBuild    :- {0}' -f $objOSVerNumberBuild);

    Write-Host('');
}

  

$objWMIOS = (Get-WmiObject Win32_OperatingSystem);

if ($objWMIOS -ne $null)
{
	Write-Host('');

    Write-Host("WMI - Win32_OperatingSystem");
	#$objWMIOS;

	$WMIOSCaption = $objWMIOS.caption;
	$WMIOSArchitecture = $objWMIOS.OSArchitecture;

	$strLog = "`t WMI OS - Caption        :- {0}" -f $WMIOSCaption;
	Write-Host $strLog;

	$strLog = "`t WMI OS - OSArchitecture :- {0}" -f $WMIOSArchitecture;
	Write-Host $strLog;	

	Write-Host('');
}


$strRegPath = "HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion";
$objRegWinOS = (Get-ItemProperty $strRegPath);

if ($objRegWinOS -ne $null)
{

	Write-Host('');
    Write-Host("Registry Path $strRegPath");
	#$objRegWinOS;

	$productName = $objRegWinOS.ProductName;
	$releaseID = $objRegWinOS.ReleaseID;
	$currentBuild = $objRegWinOS.CurrentBuildNumber;
	$edition = $objRegWinOS.CompositionEditionID;

	$strLog = "`t WinOS Registry - productName :- {0}"`
				-f $productName;
	Write-Host $strLog;

	$strLog = "`t WinOS Registry - ReleaseID :- {0}" `
				-f $releaseID;
	Write-Host $strLog;

	$strLog = "`t WinOS Registry - Current Build :- {0}" `
				-f $currentBuild;
	Write-Host $strLog;

	$strLog = "`t WinOS Registry - Edition :- {0}" `
				-f $edition;
	Write-Host $strLog;

	Write-Host('');
}


$i = 0;
$entry = $null;
$entrySaved = $null;


$iNumberofUpdates = $updateList.Length;

Write-Host '';
Write-Host 'Iterating Build List looking to match OS Build Number ... ';
Write-Host '';


foreach ( $entry in $updateList )
{

  $releaseIDInList =$entry.split(',')[0];
  $update=$entry.split(',')[1];
  $versionNumberMin=$entry.split(',')[2];
  $versionNumberMax=$entry.split(',')[3];

  
  $bConverted = [double]::TryParse(`
                      $versionNumberMin`
                    , [ref] $versionNumberMinAsNumber);

  $bConverted = [double]::TryParse(`
                    $versionNumberMax`
                      , [ref] $versionNumberMaxAsNumber);                               

  if ($debug)
  {

    Write-Host('Build {0} -f $objOSVerNumberBuild ');

    $strLog = 'versionNumberMin {0}' `
				-f $versionNumberMinAsNumber;
	Write-Host($strLog);

    $strLog = 'versionNumberMax {0}' `
				-f $versionNumberMaxAsNumber;
	Write-Host($strLog);	

  }     

  if (
			( $releaseID -eq $releaseIDInList) `
		-or `
			( `
				     ( $objOSVerNumberBuild -ge $versionNumberMinAsNumber) `
				-and ( $objOSVerNumberBuild -le $versionNumberMaxAsNumber) `
			)
	 )
    {
        # save entry
        $entrySaved = $entry;

        break;
    }

  $i = $i + 1;  

} # foreach ( $entry in $updateList )   


if ($entrySaved -ne $null)
{

    # Get fields
    $matchVersion = $entrySaved.split(',')[0];
    $matchMarketing = $entrySaved.split(',')[1];
    $matchBuildMin = $entrySaved.split(',')[2];
    $matchBuildMax = $entrySaved.split(',')[3];

    Write-Host("Version Matched");

    Write-Host("`t Version   :-  $matchVersion");

    $strLog = "`t Build     :-  {0} through {1}" `
                -f $matchBuildMin.Trim() `
				 , $matchBuildMax.Trim();

    Write-Host($strLog);

    Write-Host("`t Marketing :- $matchMarketing");

}
else
{

    $strLog = 'Version Number ({0}) not found!' -f `
                $objOSVerNumberBuild;

    Write-Host($strLog);            

}

Invoke

Syntax

powershell -file ./getWindows10MarketingName.ps1

Output

getMSWindows10UpdateName_Work_20180613_0742PM.png

Source Code Control

GitHub

DanielAdeniji/WinOSMarketingNameUsingPS
Link

Listening

Listening to the song I first heard during yesterday’s parade:

Drake – Nice for What
Link

Lyrics

You got to be nice for what to these …..

SSMS – Scripting – Job – Error – “msdb.dbo.sp_add_jobserver” – “is already targeted at server”

 

Background

Using SSMS, Scripted SQL Server Job so that I can apply it against other SQL Servers, but ran into error.

Scripting

Script SQL Server Agent Job

scriptJob_20180612_0128PM.PNG

 

Error

Error Message


Msg 14269, Level 16, State 1, Procedure msdb.dbo.sp_add_jobserver, Line 101 [Batch Start Line 8]
Job 'SharePublicly' is already targeted at server 'LAB'.

Troubleshooting

When a job is scripted, the code generated includes a portion that invokes sp_add_jobserver.

The sp_add_jobserver procedure binds the job to the targeted server.


EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
         @job_id = @jobId
       , @server_name = N'(local)'

IF (@@ERROR  0 OR @ReturnCode  0)
      GOTO QuitWithRollback

Remediation

Outline

  1. Check msdb system tables
    • dbo.sysjobservers
    • dbo.systargetservers
  2. If matching records
    • Here are the tables that needs to be considered
      • dbo.sysjobservers.jobName ( @jobID )
      • dbo.systargetservers.serverName ( @servername)
    • Conditions satisfied
      • Local variable @skipWhenJobExist is enabled ( set to 1 )
  3. Action
    • Skip invoking msdb.dbo.sp_add_jobserver

Code Snippet


DECLARE @jobId   BINARY(16)
DECLARE @jobName sysname
DECLARE @serverName sysname
DECLARE @serverNameLocal sysname
declare @commit bit
declare @skipWhenJobExist bit

set @jobName = 'SharePublicly';
set @servername = N'(local)'
set @serverNameLocal = N'(local)'

--set @commit = 0
set @commit = 1

set @skipWhenJobExist = 1
--set @skipWhenJobExist = 0

/*
	Check if Job is already attached to Target Server
*/
if not exists
(

	select
			  [src] = 'dbo.sysjobservers'
			, tblSJS.* 

	from   dbo.sysjobservers tblSJS

	left outer join dbo.systargetservers tblSTS
			on tblSJS.server_id = tblSTS.server_id

	where  tblSJS.job_id = @jobId

	and  

		(

			(
					 ( tblSJS.server_id = 0)
				 and ( @servername = @serverNameLocal )

			)

			or
			(
					 ( tblSJS.server_id = tblSTS.server_id)
				 and ( tblSTS.server_name = @serverName )
			)

		)
	and (@skipWhenJobExist = 1)
)
begin

	EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
			  @job_id = @jobId
			, @server_name = @serverName

	IF (@@ERROR  0 OR @ReturnCode  0)
	begin
		GOTO QuitWithRollback
	end

end

Source Control

GitHub

DanielAdeniji/SQLServerScriptingAgentErrorJobAlreadyTargeted
Link