See Re: Simple example of a transaction Thread #1108501 Message #1108514

>Here is my stored procedure
>

>DECLARE @ErrorInsert int, @ErrorUpdate int,
>
>INSERT INTO [111.11.1.11,1433].ipo.dbo.ui_userlookup
>	SELECT * FROM OPENQUERY(ADSI, 'SELECT ciscoEcsbuDtmfID, department, ciscoEcsbuDtmfID, mail, title, cn, SAMAccountName
>		FROM ''LDAP://DC=something,DC=something''
>		WHERE objectCategory = ''Person''
>		AND objectclass = ''user''
>		AND title = ''Instrument Specialist*''
>		AND numberrestrictiongroup <> ''*removed*''
>		AND (accountexpires = 9223372036854775807 OR accountexpires = 0)
>		ORDER BY cn ')
>
>SELECT @ErrorInsert = @@Error
>
>UPDATE [111.11.1.11,1433].ipo.dbo.ui_userlookup
>	SET cusertype = 'IS' WHERE cusertype = cphoneext
>
>SELECT @ErrorUpdate = @@Error
>
>
>IF @ErrorInsert <> 0 OR @ErrorUpdate <> 0
>	BEGIN	
>
>		--send an email using exec master.dbo.xp_smtp_sendmail
>	END
>

>
>When there is a problem with the insert statment the @@error is not being assigned and program never gets to the if statement.
>
>Daniel