SQL Agent: Use a batch file to run RoboCopy

\ by

It’s really hard to run RoboCopy in a SQL Agent command step. We want to use CmdExec so we can execute using a DOS proxy. But RoboCopy has a lot of weird exit values.

Easily handle the exit codes by calling a batch file. I’ve hard coded our options to make it easy and standard. 

@ECHO ON

IF {%3}=={} (
  @ECHO USAGE
  @ECHO RoboCopyFiles SourcePath DestinationPath Files
  @ECHO.
  GOTO FINISHED
) ELSE (

IF NOT {%4}=={} (
  (robocopy %1 %2 %3 /COPY:DAT /NP /R:0 /A+:A) 
) ELSE (
  (robocopy %1 %2 %3 /COPY:DAT /NP /R:0 /A+:A  /L) 
)

)
:FINISHED
IF %ERRORLEVEL% GEQ 8 exit /B %ERRORLEVEL%
exit /B 0
Posted in: Coding, DOS

SQL: Count rows for each Range of rows

\ by 
/*

For a table with INT column count rows for each Range of rows. 

Set to 10M rows...
*/
SELECT
    MIN([KeyTable].[Key]) AS [MinKey],
    MAX([KeyTable].[Key]) AS [MaxKey],
    [KeyTable].[Range] * 10000000 AS [Range10M],
    COUNT(*) AS [RangeRows]
FROM (
    SELECT
        [Key],
        (([Key] - 1) / 10000000) + 1 AS [Range]
    FROM [dbo].[Detail]
) AS [KeyTable]
GROUP BY [KeyTable].[Range]
ORDER BY [KeyTable].[Range]

-- Test one.
SELECT COUNT(*)
FROM [dbo].[Detail]
WHERE [Key]
BETWEEN 150000001 AND 160000000
GO
Posted in: Coding, SQL

PowerShell: Get Windows Update scheduled date

\ by

Look for WU install dates for all servers.

$DayOfWeek = @{n='DayOfWeek';e={ ($_.LastRunTime).DayOfWeek }}
$Computer = @{n='Computer';e={$ComputerName}}
$TimeFromNow = @{n='HoursFromNow';e={"{0}" -f (New-TimeSpan -Start (Get-date) -End $_.NextRunTime) } }


$SQLInstanceDev + $SQLInstanceTEST + $SQLInstancePROD  | %{
    $ComputerName = $_.Split('\')[0]
    Write-Host $ComputerName
    # Actually get STATE for Windows Updates for each computer

    .\adhoc\Get-ScheduledTask.ps1 -ComputerName $ComputerName  |
        Where-Object {$_.Name -in 'ProgramDataUpdater','AUScheduledInstall' } |
            Select-Object $Computer, $TimeFromNow, Name, State, Enabled, LastRunTime, NextRunTime, ComputerName, $DayOfWeek

} | Out-GridView
Posted in: Coding, PowerShell, Windows

PowerShell: What files changed for DaysAgo?

\ by

Pass in multiple paths.
Create a variable for Owner, using Get-Acl.
Output to GridView so we can review all columns.

$PathList = @('\\Shared\1','\\Shared\2', '\\Shared\3')
$DaysAgo = 2
$Owner = @{
  Name = 'File Owner'
  Expression = { (Get-Acl $_.FullName).Owner }
}
Get-ChildItem -Path $PathList  -Recurse -Filter '*.sql'  |
     ? { $_.LastWriteTime -gt (Get-Date).AddDays(-$DaysAgo) } |
        sort LastWriteTime |
            Select LastWriteTime,  $Owner, Name, Directory    | Out-GridView
Posted in: Coding, PowerShell, Reports

CMD: Parse DATE to get a string for searching files

\ by

Sometimes we want to look for files with dates like yyyyMMdd in the name. So for given this code we can run in DOS CMD:

for /f "tokens=1-4 delims=/-. " %i in ('date /t') do @ECHO %i %j %k %l

Thu 02 21 2019

We can make our batch file to search for files with today’s date in name, using multiple %%s.

@ECHO OFF
 
rem First list everything sorted by date.
for /f "tokens=1-4 delims=/-. " %%i in ('date /t') do DIR  /OD "\\FileServer\?M*%%l%%j*.txt"

@ECHO.
@ECHO **SEARCHING for six ?M_ and one ?M_ files ...
for /f "tokens=1-4 delims=/-. " %%i in ('date /t') do @ECHO ...for date ?M*%%l%%j%%k*.txt && @ECHO. && DIR /OD "\\FileServer\?M*%%l%%j%%k*.txt"
Posted in: Coding, DOS, Files

SQL What is the second Monday of this month?

\ by

What is the second Monday of this month?

;WITH [cteDate]
AS
 (
 -- @first + 7 * (@nth - 1) + (7 + @dow - DATEPART(WEEKDAY, @first + 7 * (@nth - 1))) % 7 -- Get @nth @dow 2 = Monday for us. Formula from Steve Kass.
 SELECT
      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) + 7 * (2 - 1)
      + (7 + 2 - DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) + 7 * (2 - 1))) % 7 AS [SecondMonday])
SELECT
    [cteDate].[SecondMonday],
    DATENAME(WEEKDAY, [cteDate].[SecondMonday]) AS SearchWeekDay,
	DATEDIFF(DAY,GETDATE(),[cteDate].[SecondMonday]) AS DaysFromNow
FROM [cteDate]
Posted in: Coding, SQL, SQL Server

SQL: Fast Table RowCount

\ by

We aren’t looking for accuracy, so this works fine for us. The NOLOCKs helped e.g. when indexes were in the process of creating.

Fast way to count large tables.

SELECT
    SCHEMA_NAME([Tables].[schema_id]) AS [SchemaName],
    [Tables].[name] AS [TableName],
    SUM([Partitions].[rows]) AS [TotalRowCount]
FROM [sys].[tables] AS [Tables] WITH (NOLOCK)
JOIN [sys].[partitions] AS [Partitions] WITH (NOLOCK)
    ON [Tables].[object_id] = [Partitions].[object_id]
       AND [Partitions].[index_id] IN (
               0, 1
           )
WHERE [Tables].name = N'TRANSACTIONS'
--	and [Partitions].[rows] > 0
GROUP BY SCHEMA_NAME([Tables].[schema_id]),
         [Tables].[name];
Posted in: Coding, Performance, SQL

Dates Explained

\ by

Here are some of the ways dates can be output.

declare @date datetime
set @date = getdate()
select 
	@date	as SampleDate,
	datepart(year,@date)	as 'year',
	datepart(month,@date) 	as 'month',
	datepart(day,@date) 	as 'day',
	datepart(week,@date) 	as 'week',
	datepart(quarter,@date) as 'quarter',
	datepart(dy,@date) 	as 'day of year',
	datepart(weekday,@date) as 'weekday',
	datepart(hour,@date) 	as 'hour',
	datepart(minute,@date) 	as 'minute' ,

	datename(month,@date) as 'month name',
	datename(weekday,@date) as 'weekday name',

	'fiscal year' =   
	case            
		when datepart(month,@date) >= 7
		then datepart(year,@date) + 1
		else datepart(year,@date)
	end,
	CONVERT(varchar(12), getdate() ,112) 	as 'today date as integer',
	CONVERT(varchar(12),DATEADD(d, -7 ,getdate()),112) 	as 'lastweek date as integer',
	CONVERT(varchar(12), getdate() ,108) 			as 'time',
	REPLACE(CONVERT(varchar(12), getdate() ,108),':','') 	as 'time as integer',
	dateadd(d,-1, cast(month(dateadd(mm,1,getdate())) as varchar) + 
		'/01/' + cast(year(getdate()) as varchar)) as [Last day of month],
	DATEADD(D,1, 
		cast(month( getdate() ) as varchar) + '-' +
		cast(day( getdate()) as varchar) + '-' +
		cast(year(getdate() ) as varchar) ) as [Midnight Tonight]

declare @tim varchar(8)
set @tim = REPLACE(CONVERT(varchar(12), getdate() ,108),':','')
select @tim,
case when len(@tim) >= 5 then
	substring(@tim,len(@tim)-5,2 ) + ':' +
	substring(@tim,len(@tim)-3,2 ) + ':' +
	substring(@tim,len(@tim)-1,2 )
else
	@tim
end
Posted in: Coding, SQL

Where are tables located in database files?

\ by

This uses indexes to locate the files.


SELECT  [o].[crdate],
        DATEDIFF(MINUTE, [o].[crdate], GETDATE()) AS [MinAgo],
        [o].[name] AS [Tablename],
        [i].[indid],
        [i].[name] AS [IndexName],
        [i].[rowcnt],
        [i].[groupid],
        [f].[name] AS [FileGroupName],
        [d].[file_id],
        [d].[physical_name],
        [s].[name] AS [DataSpace]
FROM    [sys].[sysobjects] [o]
JOIN    [sys].[objects] [so]
ON      [so].[object_id] = [o].[id]
JOIN    [sys].[sysindexes] [i]
ON      [i].[id] = [o].[id]
JOIN    [sys].[filegroups] [f]
ON      [f].[data_space_id] = [i].[groupid]
JOIN    [sys].[database_files] [d]
ON      [f].[data_space_id] = [d].[data_space_id]
JOIN    [sys].[data_spaces] [s]
ON      [f].[data_space_id] = [s].[data_space_id]
WHERE   [is_ms_shipped] = 0
        AND [i].[name] IS NOT NULL
ORDER BY [Tablename],
        [IndexName],
        [d].[file_id]
Posted in: Coding, Configuration, SQL

SQL: Where are the indexes located?

\ by

Where are the indexes located?

SELECT  'table_name' = OBJECT_NAME(i.id),
        i.indid,
        'index_name' = i.name,
        i.groupid,
        'filegroup' = f.name,
        'file_name' = d.physical_name,
        'dataspace' = s.name
FROM    sys.sysindexes i,
        sys.filegroups f,
        sys.database_files d,
        sys.data_spaces s
WHERE   OBJECTPROPERTY(i.id, 'IsUserTable') = 1
        AND f.data_space_id = i.groupid
        AND f.data_space_id = d.data_space_id
        AND f.data_space_id = s.data_space_id
        AND i.name LIKE 'ndx%'
ORDER BY f.name,
        OBJECT_NAME(i.id),
        groupid

g

Posted in: Coding, Configuration