Good article.

I find this type of wrong triggers in a lot of databases.

I also start with adding "set nocount on" at the start of each trigger.

Otherwise, an update/insert/delete statement also...

I changed the code and made it a stored procedure in my dba database.

I also added a filter to list only enabled jobs.

if object_id('dbo.usp_mailFailedJobs') is null exec('create procedure dbo.usp_mailFailedJobs as...

I have this version, and it does NOT support compression :

productversion:11.0.2383.0

productlevel:RTM

edition:Web Edition (64-bit)

I am still trying to make the script MS SQL 2012 compatible.

How can I send you the adapted script ?

Awesome script, thanks a lot.

I wanted to try this on my server, but failed because my server uses collation 'Latin1_General_bin' ( case sensitive => also for object names ).

I changed...

You can easely resolve the "unique" problem by adding a distinct to the select top().

I tried this, and it works fine on SQL 2005. The distinct will not generate an...

[font="Courier New"]I suppose there is only 1 customer, and 1 or more subcustoerms. This query would do the job :

DECLARE @docHandle int

DECLARE @doc varchar(1000)

SET @doc ='

...

Here is an exemple : ( but 53 becomes AZ ?! )

create function dbo.fn_conv ( @number int )

returns varchar(3)

as

begin

 -- next 2 variables could be parameters

 declare @charset varchar(255)

 declare @len int

 set @charset...

Strange XML ... But you can  do it like this :

SELECT *

FROM   OPENXML (@docHandle, '/ShipmentHeader',2)

         WITH (SettleFlag      char(1)      '@SettleFlag',

               CustomerID      varchar(10) 

I use my stored procedure sp__droptable to test existance of a table ( also temp table ) and ropt it.

You can use sp__droptable '#tmp'.

Script to create the procedure ( in...

Do not forget that the inserted table can have multiple rows.

You should test this !

INTO : only for first select

UNION : If distinct is needed, do not use UNION ALL, but use UNION

Correct syntax is :

SELECT DISTINCT Column1 AS Name

INTO            New_Table

FROM         Old_Table

UNION

SELECT DISTINCT Column2 AS...

your demand is not very clear ( what is first? ), but this script could be a start :

set nocount on

go

use tempdb

go

if object_id('ori') is not null drop table ori

if object_id('TableA')...

When you need to convert back to datetime, you should also specify the style :

declare @d1 datetime

set @d1 = getdate()

select dateASchar       =                  convert(varchar(10),@d1,103)

go

declare @d1 datetime

set @d1...

Try this ...

-- clean up : DO NOT RUN ON PRODUCTION DB : tables will be dropped !!

if db_name() = 'tempdb'

begin

 declare @object_id int

 set @object_id =  object_id('dbo.fn_colsplit')

 if @object_id is...