xp_cmdshell and file paths with spaces

Not sure why but xp_cmdshell has issues with spaces in file paths. For example the following 7zip command results in an error:

EXEC xp_cmdshell ‘”c:\Program Files\7-Zip\7z” a “c:\test folder\test.zip” “c:\test folder\x.bmp’

‘c:\Program’ is not recognized as an internal or external command,operable program or batch file.

One workaround to this is to ensure that the qualified path is not at the beginning of the command. For example the following will work as expected

EXEC xp_cmdshell ‘cd.. && “c:\Program Files\7-Zip\7z” a “c:\test folder\test.zip” “c:\test folder\x.bmp’

So the work around here was to insert cd.. && at the beginning of the command string.

passing temp tables between stored procedures

Its not widely known that you can pass temp tables between stored procedures.

The basic idea is that you create a parent procedure which contains the temp table, and one or more child procedures that reference the table. The parent procedure will then call the child procedures after the temp table has been created.

A basic example can be seen below:

USE test
GO

CREATE PROC dbo.pass_temp_table_between_stored_procs

AS

SELECT GETDATE() x INTO #temp

EXEC test.dbo.pass_temp_table_between_stored_procs1
EXEC test.dbo.pass_temp_table_between_stored_procs2

GO

CREATE PROC dbo.pass_temp_table_between_stored_procs1
AS
SELECT * FROM #temp

GO

alter PROC dbo.pass_temp_table_between_stored_procs2
AS

UPDATE #temp SET x = x+1

SELECT * FROM #temp

GO

EXEC dbo.pass_temp_table_between_stored_procs

jobs/schedules/steps views

CREATE VIEW jobschedules

AS

SELECT SJ.name as job_name
, SJ.enabled as is_job_enabled
, SS.enabled as is_schedule_enabled
/*, ISNULL(SJ.description, ”) as job_desc*/
, SS.name as schedule_name
, CASE freq_type
WHEN 1 THEN ‘Occurs on ‘ + STUFF(RIGHT(active_start_date, 4), 3,0, ‘/’) + ‘/’ + LEFT(active_start_date, 4) + ‘ at ‘
+ REPLACE( RIGHT(CONVERT(varchar(30),
CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime) /* hh:mm:ss 24H */
, 9), 14), ‘:000’, ‘ ‘) /* HH:mm:ss:000AM/PM then replace the :000 with space.*/
WHEN 4 THEN ‘Occurs every ‘ + CAST(freq_interval as varchar(10))
+ ‘ day(s) ‘
+ CASE freq_subday_type
WHEN 1 THEN ‘at ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
WHEN 2 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ second(s)’
WHEN 4 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ minute(s)’
WHEN 8 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ hour(s)’
ELSE ” END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ‘ between ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
+ ‘ and ‘
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
ELSE ”
END
WHEN 8 THEN
‘Occurs every ‘ + CAST(freq_recurrence_factor as varchar(10))
+ ‘ week(s) on ‘
+
REPLACE( CASE WHEN freq_interval&1 = 1 THEN ‘Sunday, ‘ ELSE ” END
+ CASE WHEN freq_interval&2 = 2 THEN ‘Monday, ‘ ELSE ” END
+ CASE WHEN freq_interval&4 = 4 THEN ‘Tuesday, ‘ ELSE ” END
+ CASE WHEN freq_interval&8 = 8 THEN ‘Wednesday, ‘ ELSE ” END
+ CASE WHEN freq_interval&16 = 16 THEN ‘Thursday, ‘ ELSE ” END
+ CASE WHEN freq_interval&32 = 32 THEN ‘Friday, ‘ ELSE ” END
+ CASE WHEN freq_interval&64 = 64 THEN ‘Saturday, ‘ ELSE ” END
+ ‘|’, ‘, |’, ‘ ‘) /* get rid of trailing comma */

+ CASE freq_subday_type
WHEN 1 THEN ‘at ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
WHEN 2 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ second(s)’
WHEN 4 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ minute(s)’
WHEN 8 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ hour(s)’
ELSE ” END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ‘ between ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
+ ‘ and ‘
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
ELSE ”
END
WHEN 16 THEN
‘Occurs every ‘ + CAST(freq_recurrence_factor as varchar(10))
+ ‘ month(s) on ‘
+ ‘day ‘ + CAST(freq_interval as varchar(10)) + ‘ of that month ‘
+ CASE freq_subday_type
WHEN 1 THEN ‘at ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
WHEN 2 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ second(s)’
WHEN 4 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ minute(s)’
WHEN 8 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ hour(s)’
ELSE ” END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ‘ between ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
+ ‘ and ‘
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
ELSE ”
END
WHEN 32 THEN
‘Occurs ‘ +
CASE freq_relative_interval
WHEN 1 THEN ‘every first ‘
WHEN 2 THEN ‘every second ‘
WHEN 4 THEN ‘every third ‘
WHEN 8 THEN ‘every fourth ‘
WHEN 16 THEN ‘on the last ‘
END
+ CASE freq_interval WHEN 1 THEN ‘Sunday’
WHEN 2 THEN ‘Monday’
WHEN 3 THEN ‘Tuesday’
WHEN 4 THEN ‘Wednesday’
WHEN 5 THEN ‘Thursday’
WHEN 6 THEN ‘Friday’
WHEN 7 THEN ‘Saturday’
WHEN 8 THEN ‘day’
WHEN 9 THEN ‘weekday’
WHEN 10 THEN ‘weekend’
END

+ ‘ of every ‘ + CAST(freq_recurrence_factor as varchar(10)) + ‘ month(s) ‘
+ CASE freq_subday_type
WHEN 1 THEN ‘at ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
WHEN 2 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ second(s)’
WHEN 4 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ minute(s)’
WHEN 8 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ hour(s)’
ELSE ” END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ‘ between ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
+ ‘ and ‘
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
ELSE ”
END
WHEN 64 THEN ‘Runs when the SQL Server Agent service starts’
WHEN 128 THEN ‘Runs when the computer is idle’

END as [Description]
, CASE freq_type
WHEN 1 THEN ‘One Time’
WHEN 4 THEN CASE freq_subday_type WHEN 1 THEN ‘Daily’
WHEN 2 THEN ‘Second-ly’
WHEN 4 THEN ‘Minutely’
WHEN 8 THEN ‘Hourly’
ELSE ”
END
WHEN 8 THEN ‘Weekly’
WHEN 16 THEN ‘Monthly’
WHEN 32 THEN ‘Monthly, relative to freq_interval’
WHEN 64 THEN ‘Runs when the SQL Server Agent service starts’
WHEN 128 THEN ‘Runs when the computer is idle’
END As [Freq_Type]
, CASE freq_subday_type
WHEN 1 THEN ‘At the specified time’
WHEN 2 THEN ‘Every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ Second(s)’
WHEN 4 THEN ‘Every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ Minute(s)’
WHEN 8 THEN ‘Every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ Hour(s)’
ELSE ”
END AS freq_subday_type

, CASE
WHEN freq_type = 4 /* (daily) */ THEN ‘Every ‘ + CAST(freq_interval as varchar(10)) + ‘ Day(s)’
WHEN freq_type = 8 /* (weekly) */ THEN
REPLACE( CASE WHEN freq_interval&1 = 1 THEN ‘Sunday, ‘ ELSE ” END
+ CASE WHEN freq_interval&2 = 2 THEN ‘Monday, ‘ ELSE ” END
+ CASE WHEN freq_interval&4 = 4 THEN ‘Tuesday, ‘ ELSE ” END
+ CASE WHEN freq_interval&8 = 8 THEN ‘Wednesday, ‘ ELSE ” END
+ CASE WHEN freq_interval&16 = 16 THEN ‘Thursday, ‘ ELSE ” END
+ CASE WHEN freq_interval&32 = 32 THEN ‘Friday, ‘ ELSE ” END
+ CASE WHEN freq_interval&64 = 64 THEN ‘Saturday, ‘ ELSE ” END
+ ‘|’, ‘, |’, ‘ ‘) /* get rid of trailing comma */
WHEN freq_type = 16 THEN ‘On Day ‘ + CAST(freq_interval as varchar(10)) + ‘ of Every Month’
WHEN freq_type = 32 /* (monthly) */ THEN ‘Every ‘ +
CASE freq_interval WHEN 1 THEN ‘Sunday’
WHEN 2 THEN ‘Monday’
WHEN 3 THEN ‘Tuesday’
WHEN 4 THEN ‘Wednesday’
WHEN 5 THEN ‘Thursday’
WHEN 6 THEN ‘Friday’
WHEN 7 THEN ‘Saturday’
WHEN 8 THEN ‘Day’
WHEN 9 THEN ‘Weekday’
WHEN 10 THEN ‘Weekend day’
END
ELSE ”
END as [freq_interval]
, CASE WHEN freq_type = 8 /* (weekly) */ THEN ‘Every ‘ + CAST(freq_recurrence_factor as varchar(10)) + ‘ Week(s)’
WHEN freq_type = 16 /* (monthly) */ THEN ‘Every ‘ + CAST(freq_recurrence_factor as varchar(10)) + ‘ Month(s)’
WHEN freq_type = 32 /* (monthly relative) */ THEN ‘Every ‘ + CAST(freq_recurrence_factor as varchar(10)) + ‘ Week(s)’
ELSE ”
END as freq_recurrence_factor
, LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘)) as starttime
, LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘)) as endtime
, STUFF(STUFF(active_start_date, 5, 0, ‘/’), 8, 0, ‘/’) as schedule_start_date
, STUFF(STUFF(active_end_date, 5, 0, ‘/’), 8, 0, ‘/’) as schedule_end_date
FROM msdb.dbo.sysjobs SJ
INNER JOIN msdb.dbo.sysjobschedules SJS
ON SJ.job_id = SJS.job_id
INNER JOIN msdb.dbo.sysschedules SS
ON SJS.schedule_id = SS.schedule_id

CREATE VIEW jobs

AS
SELECT a.name job_name ,
a.enabled is_job_enabled ,
description job_description ,
start_step_id start_at_step ,
b.name category_name ,
c.name job_owner_name ,
CASE notify_level_email
WHEN 0 THEN ‘never’
WHEN 1 THEN ‘when the job succeeds’
WHEN 2 THEN ‘when the job fails’
WHEN 3 THEN ‘when the job completes’
END notify_level_email ,
d.name notify_email_operator ,
date_created job_created_date ,
date_modified job_modifed_date
FROM msdb..sysjobs a
LEFT JOIN msdb..syscategories b ON b.category_id = a.category_id
LEFT JOIN msdb..sysusers c ON c.sid = a.owner_sid
LEFT JOIN msdb..sysoperators d ON d.id = a.notify_email_operator_id;
GO

CREATE VIEW jobsteps

AS
SELECT b.name job_name,
step_id job_step_number,
step_name job_step_name,
subsystem job_step_type,
command job_step_command,
CASE on_success_action
WHEN 1 THEN ‘go to next step’
WHEN 2 THEN ‘quit the job reporting success’
WHEN 3 THEN ‘quit the job reporting failure’
WHEN 4 THEN ‘go to step’
ELSE NULL
END on_success_action,
on_success_step_id ,
CASE on_fail_action
WHEN 1 THEN ‘go to next step’
WHEN 2 THEN ‘quit the job reporting success’
WHEN 3 THEN ‘quit the job reporting failure’
ELSE NULL
END ON_failure_action,
on_fail_step_id ,
database_name ,
retry_attempts ,
retry_interval retry_interval_mins,
output_file_name ,
CASE last_run_outcome
WHEN 0 THEN ‘failed’
WHEN 1 THEN ‘succeeded’
WHEN 2 THEN ‘retry’
WHEN 3 THEN ‘cancelled’
WHEN 5 THEN ‘unknown’
END last_run_outcome,
LEFT(oa1.last_run_duration, 2) + ‘:’ + SUBSTRING(oa1.last_run_duration, 3, 2) + ‘:’ + SUBSTRING(oa1.last_run_duration, 5, 2) last_run_duration,
last_run_retries ,
last_run_date ,
LEFT(oa1.last_run_time, 2) + ‘:’ + SUBSTRING(oa1.last_run_time, 3, 2) + ‘:’ + SUBSTRING(oa1.last_run_time, 5, 2) last_run_time,
c.name proxy_name
FROM msdb..sysjobsteps a
INNER JOIN msdb..sysjobs b ON b.job_id = a.job_id
LEFT JOIN msdb..sysproxies c ON c.proxy_id = a.proxy_id
OUTER APPLY ( SELECT RIGHT(‘000000’ + CAST(last_run_duration AS VARCHAR(100)), 6) last_run_duration ,
RIGHT(‘000000’ + CAST(last_run_time AS VARCHAR(100)), 6) last_run_time ) oa1;