Daily change of output file name

-- Daily change of job log file
-- 20140416 John Minkjan i8Solutions

DECLARE @nSQL varchar(max)
DECLARE c cursor for
SELECT
--      [sJOB].[job_id] AS [JobID]
--    , [sJOB].[name] AS [JobName]
--    , [sJSTP].[step_uid] AS [StepID]
--    , [sJSTP].[step_id] AS [StepNo]
--    , [sJSTP].[step_name] AS [StepName]
--    , [sJSTP].[database_name] AS [Database]
      'EXECUTE msdb.dbo.sp_update_jobstep
       @job_id = N'''+ CAST([sJOB].[job_id] as varchar(50)) +'''
      ,@step_id = N''' +  CAST([sJSTP].[step_id] as varchar(50)) + '''
      ,@output_file_name = N'''+ '\\SVRAPPL5\D$\i8log\i8_job_'+ replace([sJOB].[name],' ','_') + '_' +replace([sJSTP].[step_name],' ','_')+'_' +convert(varchar(8),getdate(),112)+'.txt''
      ,@flags = 2 -- append to output file '
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]   
        where [sJSTP].flags = 2 -- 2 = append to output file


OPEN C

FETCH NEXT FROM C INTO @nSQL;

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC(@nSQL);

FETCH NEXT FROM C INTO @nSQL;
END

CLOSE C
DEALLOCATE C

Till Next Time

Free space fixed drives

IF OBJECT_ID('tempdb..#drives') IS NOT NULL
    DROP TABLE #drives

CREATE TABLE #drives (
        drive char,
        [free] int
)
      
INSERT INTO #drives
EXEC master..xp_fixeddrives

SELECT drive, [free] / 1024 as FreeSpaceGB
FROM #drives


Till Next Time

Full backup databases starting with i8

/* Daily Full backup of i8* databases
   20140403 JJM (c) i8 solutions
*/

DECLARE @eSQL NVARCHAR(MAX)

DECLARE C cursor for
    select 'BACKUP DATABASE ['+name+'] TO  DISK = N''D:\i8\BU\'+ name +'.bak'' WITH NOFORMAT,
    INIT, 
    NAME = N'''+ name + '-Full Database Backup'',
    SKIP,
    NOREWIND,
    NOUNLOAD, 
    STATS = 10' from master.sys.databases
    where name like 'i8%'

OPEN C
FETCH NEXT FROM C INTO @eSQL

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@eSQL);
FETCH NEXT FROM C INTO @eSQL
END

CLOSE C
DEALLOCATE C
Till Next Time

MSSQL View grants for specific user

USE [database];

 SELECT
    class_desc
  , CASE WHEN class = 0 THEN DB_NAME()
               WHEN class = 1 THEN OBJECT_NAME(major_id)
               WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable]

  , USER_NAME(grantee_principal_id) [User]
 , permission_name
 , state_desc
FROM sys.database_permissions
where USER_NAME(grantee_principal_id) = 'USER_NAME'
and state_desc = 'GRANT'
Till Next Time

MSSQL Connect to a database from a TSQL script

Step 1:

Switch to SQLCMD mode from the query menu in Management Studio:

Step 2:

Add the connection info using:

:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P password]]
Till Next Time

MSSQL Check if Function or Procedure has changed

I use this query to check if a procedure or function has changed in the past 5 days:
[code]

SELECT * from ods.information_schema.routines i where routine_type in ('PROCEDURE' ,'FUNCTION') AND LAST_ALTERED > getdate() - 5

[/code]

Till Next Time

MSSQL101 enable xp_cmdshell

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE