find-dbaagentjob bug - doesn't work on 2012/2014? #628

mbourgon opened this Issue Feb 2, 2017 · 2 comments


None yet

4 participants

mbourgon commented Feb 2, 2017 edited

Is this a feature OR bug:


System Details

  • Operating system name and version:
  • Output from $PSVersionTable:
Name                           Value                                                                                   
----                           -----                                                                                   
PSVersion                      3.0                                                                                     
WSManStackVersion              3.0                                                                                     
CLRVersion                     4.0.30319.42000                                                                         
BuildVersion                   6.2.9200.16481                                                                          
PSCompatibleVersions           {1.0, 2.0, 3.0}                                                                         
PSRemotingProtocolVersion      2.2                                                                                     

  • Output of dbatools version:
  • SQL Server version for source/target
    SQL Server 2012, SQL Server 2014

Steps to Reproduce

find-dbaagentjob -sqlserver my-ser-ver-name -category "REPL_Distribution"

Action Results


Here's what a profiler trace caught:

DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 1 else 0 end as 'IsCloud'

select is_srvrolemember('sysadmin') * 1 +is_srvrolemember('serveradmin') * 2 +is_srvrolemember('setupadmin') * 4 +is_srvrolemember('securityadmin') * 8 +is_srvrolemember('processadmin') * 16 +is_srvrolemember('dbcreator') * 32 +is_srvrolemember('diskadmin') * 64+ is_srvrolemember('bulkadmin') * 128

select SERVERPROPERTY(N'servername')

(@@microsoftversion / 0x1000000) & 0xff AS [VersionMajor]

				declare @RegPathParams sysname
				declare @Arg sysname
				declare @Param sysname
				declare @MasterPath nvarchar(512)
				declare @LogPath nvarchar(512)
				declare @ErrorLogPath nvarchar(512)
				declare @n int

				select @n=0
				select @RegPathParams=N'Software\Microsoft\MSSQLServer\MSSQLServer'+'\Parameters'
				select @Param='dummy'
				while(not @Param is null)
					select @Param=null
					select @Arg='SqlArg'+convert(nvarchar,@n)

					exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @RegPathParams, @Arg, @Param OUTPUT
					if(@Param like '-d%')
						select @Param=substring(@Param, 3, 255)
						select @MasterPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
					else if(@Param like '-l%')
						select @Param=substring(@Param, 3, 255)
						select @LogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
					else if(@Param like '-e%')
						select @Param=substring(@Param, 3, 255)
						select @ErrorLogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
					select @n=@n+1

				declare @SmoRoot nvarchar(512)
				exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT

CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],
@LogPath AS [MasterDBLogPath],
@MasterPath AS [MasterDBPath],
@ErrorLogPath AS [ErrorLogPath],
@SmoRoot AS [RootDirectory],
CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],
@@MAX_PRECISION AS [MaxPrecision],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],
CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],
CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],
convert(sysname, serverproperty(N'collation')) AS [Collation],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],
SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],
SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],
SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],
SERVERPROPERTY(N'CollationID') AS [CollationID],
SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],
SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],
SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],
SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],
SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName]

exec sp_executesql N'SELECT
dtb.collation_name AS [Collation], AS [DatabaseName2]
master.sys.databases AS dtb
(',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'master'

Expected Results

distribution jobs from that server. Had that happen on a SQL Server 2012 box as well as a SQL Server 2014 box.

Attached Logs or any exception errors

  • Use of Start-Transcript can help collection of console output and exceptions
  • Certain commands will generate an exception log that you can find in following directory: dir $env:USERPROFILE\Documents\dbatools-exceptions.txt
ctrlbold commented Feb 3, 2017

@mbourgon - you're such a great bug reporter, thank you. @Staggerlee011 - can you take a look?

@SirCaptainMitch SirCaptainMitch added the bug label Feb 3, 2017
mbourgon commented Feb 3, 2017 edited

Okay, this is user error. We just had it work successfully - after making sure the databases the jobs reference, existed. (We're building out a new server based on existing, and can't use start-sqlmigration).

Is there a way to return that (something like "Job not created because database used does not exist")? We didn't get any real messages from the app, so we weren't sure exactly why.

Apologies for wasting y'all's time.

And to make it worse, the other person who's working on this DID get an error. I'm so confused. Maybe because I'm running dev branch and they're not?
WARNING: [Job: Adhoc Job - Rebuild XML] Database(s) my_db_missing doesn't exist on destination. Skipping.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment