SQL – Getting list of jobs and their steps

How to get list of jobs an their steps. For this I will use three tables dbo.sysjobs, dbo.sysjobsteps and dbo.sysjobschedules in msdb database.

You can create select from each of these tables to get relevant data.

select name,enabled,description from msdb.dbo.sysjobs
select job_id,step_id,step_name,command,database_name from msdb.dbo.sysjobsteps
select job_id,next_run_date,next_run_time from msdb.dbo.sysjobschedules

 

But we can combine these three tables, to get unified complex select.

SELECT
    job.name,
    job.enabled,
    job.description,
    steps.step_name,
    steps.command,
    steps.database_name,
    sch.next_run_date AS ‘Sched Date’,
    sch.next_run_time AS ‘Sched Time’
FROM msdb.dbo.sysjobs job JOIN
     msdb.dbo.sysjobsteps steps       
        ON job.job_id = steps.job_id INNER JOIN
    msdb.dbo.sysjobschedules AS sch ON job.job_id = sch.job_id
–where name like ‘%BACKUP%’ –you can also filter with where clause
order by name

 

 

 

Good Luck

SQL- Creating linked server to AS400

Have you ever tried to connect to AS400. It’s been a hassle for me. But finally I got it to work, so let me share little of my wisdom with you.

First create linked server, then change options to it.

EXEC master.dbo.sp_addlinkedserver @server = N’AS400′, @srvproduct=N’i520′, @provider=N’IBMDA400′, @datasrc=N’AS400′, @provstr=N’User Id=USER;Password=PWD;Default Collection=DAT;’

Change option:

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’NonTransactedUpdates’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’SqlServerLIKE’, 1
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’collation compatible’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’rpc out’, @optvalue=N’true’
GO

So, you should now have functional linked server. Only, in my case its very slow, so it could be in your case to.

Good Luck

SQL – Table comparison

For table comparison, i like to use Red Gate SQL compare. Great tool, but if you don’t have money for it, you can always try to write your own comparation script.
You can then script it as function, and use it. It does not have, options like SQL Compare, but for now it will do the trick.
This simple comparation, uses only names of columns to compare. But you can code it to check also field type and field length.

First of all, lets create two simple tables:

CREATE TABLE [dbo].[Table_One](
    [Column1] [nchar](10) NULL,
    [Column2] [varchar](50) NULL,
    [Column3] [int] NULL,
    [Coulmn4] [datetime] NULL,
    [Column10] [char](20) NULL
) ON [PRIMARY]

CREATE SCHEMA [test] AUTHORIZATION [dbo]
GO
CREATE TABLE [test].[Table_Two](
    [Column1] [nchar](10) NULL,
    [Column7] [int] NULL,
    [Column8] [int] NULL,
    [Coulmn9] [datetime] NULL,
    [Column10] [varchar](20) NULL
) ON [PRIMARY]

As you see, I left

Then, declare variables for table names. It will be easier to handle table names in one place.
DECLARE @T1 VARCHAR(30) = ‘Table_One’, –names without schemas
        @T2 VARCHAR(30) = ‘Table_Two’

 

Now, we just have to create comparison select. Selecting distinct values from information_schema view first for one table, than the other.

— In TableOne but not in TableTwo
SELECT DISTINCT
       @T1 AS [First table],
       ‘>>’ AS Dir, –Direction
       @T2 AS [Second table],
       ISC1.COLUMN_NAME,
       ISC1.DATA_TYPE        
  FROM INFORMATION_SCHEMA.COLUMNS ISC1
WHERE ISC1.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ISC2 WHERE ISC2.TABLE_NAME = @T2)
        AND ISC1.TABLE_NAME = @T1
UNION ALL
— In TableTwo but not in TableOne
SELECT DISTINCT
       @T1 AS [First_table],
       ‘<<‘ AS Dir, –Direction
       @T2 AS [Second_table],
       ISC1.COLUMN_NAME,
       ISC1.DATA_TYPE        
  FROM INFORMATION_SCHEMA.COLUMNS ISC1
WHERE ISC1.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ISC2 WHERE ISC2.TABLE_NAME = @T1)
        AND ISC1.TABLE_NAME = @T2
ORDER BY Dir DESC, COLUMN_NAME ASC

 

And the result is:
image

 

Good Luck