SQL Job Monitoring Queries

Failed Job

SELECT name,last_outcome_message
FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B
WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

Disabled Job

SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name

Running Job

msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

Last Backup Date

SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

SQL Logs

EXEC xp_readerrorlog

Custom attributes in AD does not show in Property Mapping for Synchronization

If you have a problem with the Profile Synchronization in SharePoint 2010.

I had made a connection to our Active Directory and the Synchronization of profiles is working like a charm but when I try to map User Properties to attributes in our AD I can't see the custom attributes we made in the AD. The attributes are added to the AD after the initial connection and synchronization was made.

Solution : recreate Active Directory Connection.

How To Enter Binary String Literals?

-- Size matches
DECLARE @x BINARY(8);
SET @x = 0x2605260626402642;
PRINT @x;
GO
0x2605260626402642

-- Truncated
DECLARE @x BINARY(4);
SET @x = 0x2605260626402642;
PRINT @x;
GO
0x26052606

-- Padded
DECLARE @x BINARY(12);
SET @x = 0x2605260626402642;
PRINT @x;
GO
0x260526062640264200000000

-- No padding on variable length data type
DECLARE @x VARBINARY(8);
SET @x = 0x; -- Empty binary string
PRINT @x;
GO
0x

-- Padding on fixed length data type
DECLARE @x BINARY(8);
SET @x = 0x; -- Empty binary strings
PRINT @x;
GO
0x0000000000000000

Find Tables With Foreign Key Constraint in Database

SELECT
CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME

OR


select Referencing_Object_name, referencing_column_Name, Referenced_Object_name, Referenced_Column_Name from
(select Referenced_Column_Name = c.name, Referenced_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.rkeyid = o.id) and c.id = o.id and c.colid = f.rkey) r,
(select referencing_column_Name = c.name, Referencing_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.fkeyid = o.id) and c.id = o.id and c.colid = f.fkey) f
where r.Referenced_Column_Name = f.referencing_column_Name
and r.constid = f.constid
order by f.Referencing_Object_name

OR


select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id

Converting Varchar to Date format

declare @dt date
if(isdate('Thursday, April 01, 2010') = 0)
begin
set @dt = convert(date, substring('Thursday, April 01, 2010',CHARINDEX(',', 'Thursday, April 01, 2010') + 1,len('Thursday, April 01, 2010')), 103)
end
print @dt

How to Set the Day/Month/Year Date Format in SQL Server

Set the Language on the SQL Server

To set the language on the server you must add a language by using sp_addlanguage. The example below sets the language for British English and gives the dates in DD/MM/YY format. The example can also be applied to other countries, but you may need to modify the parameters for sp_addlanguage.

exec sp_addlanguage 'British', 'English',
'January,February,March,April,May,June,July,August,September,October,
November,December',
'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec',
'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday',
dmy,1
sp_configure 'default language', 1
reconfigure with override

To set the default language back to U.S. English after having installed another language, use the following SQL statements:

sp_configure 'default language', 0
reconfigure with override


To check what default language a server has installed, use the following SQL command:

sp_configure 'default language'

If the resulting value is 0, the default language U.S. English. If the result is not 0, run the following SQL command to find the installed default language setting and date format used:


select name ,alias, dateformat
from syslanguages
where langid =
(select value from master..sysconfigures
where comment = 'default language')

Disabling All Constraints on a Table

When there are several constraints on a table you can disable them all with one statement. To do so, use the ALTER TABLE command, providing the name of the table and the clause, NOCHECK CONSTRAINT ALL. For example, the following disables all of the constraints for the CustomerAddresses table:

ALTER TABLE CustomerAddresses NOCHECK CONSTRAINT ALL

To re-enable the constraints, issue use the same command, substituting NOCHECK with CHECK, as follows:

ALTER TABLE CustomerAddresses CHECK CONSTRAINT ALL

Disabling Individual Constraints

Sometimes you will wish to disable one constraint only. You can use the same ALTER TABLE command but instead of specifying ALL, provide the name of the constraint that you wish to remove. The following disables the FK_CustomerAddresses_Customers constraint on the CustomerAddresses table.

ALTER TABLE CustomerAddresses NOCHECK CONSTRAINT FK_CustomerAddresses_Customers

To re-enable the constraint change NOCHECK to CHECK:

ALTER TABLE CustomerAddresses CHECK CONSTRAINT FK_CustomerAddresses_Customers