Checking for success/failure in TSQL SQL Agent job step

I am relatively new to SQL Agent job creation. I am trying to create a job that will check for disabled triggers and notify an operator. The job step I have created is Transact-SQL. I am checking sys.triggers for disabled triggers. My question is:

How do I indicate whether the job step was a success or failure in the TSQL, so I can trigger the correct response to it?

Can I check the return code from a stored procedure to determine success or failure?

Thanks,

Larry

Just raise an error in the proc like:

if exists (select * from sys.triggers where is_disabled <> 0)
raiserror('disabled triggers found',16,1)

this will cause the job step to fail

|||

Actually, I've tried using the DDL trigger to catch it. However, when you enter the DISABLE TRIGGER..... command, it does not fire the ALTER TRIGGER or ALTER TABLE event and thus can't be detected by a DDL trigger. Currently, my DDL trigger prevents a DROP trigger or an ALTER TABLE DISABLE TRIGGER event from happening. If anyone can tell me how to use the DDL trigger to prevent a plain DISABLE TRIGGER, I'm all ears.

Thanks,

Larry

|||

If, in your database/server, you are concerned with disabled Triggers, you most likely should NOT provide anyone the level of permissions that would allow them to disable Triggers.

Checking for success/failure in TSQL SQL Agent job step

I am relatively new to SQL Agent job creation. I am trying to create a job that will check for disabled triggers and notify an operator. The job step I have created is Transact-SQL. I am checking sys.triggers for disabled triggers. My question is:

How do I indicate whether the job step was a success or failure in the TSQL, so I can trigger the correct response to it?

Can I check the return code from a stored procedure to determine success or failure?

Thanks,

Larry

Just raise an error in the proc like:

if exists (select * from sys.triggers where is_disabled <> 0)
raiserror('disabled triggers found',16,1)

this will cause the job step to fail

|||

Actually, I've tried using the DDL trigger to catch it. However, when you enter the DISABLE TRIGGER..... command, it does not fire the ALTER TRIGGER or ALTER TABLE event and thus can't be detected by a DDL trigger. Currently, my DDL trigger prevents a DROP trigger or an ALTER TABLE DISABLE TRIGGER event from happening. If anyone can tell me how to use the DDL trigger to prevent a plain DISABLE TRIGGER, I'm all ears.

Thanks,

Larry

|||

If, in your database/server, you are concerned with disabled Triggers, you most likely should NOT provide anyone the level of permissions that would allow them to disable Triggers.

Checking existence of network file

Hi Guys,

Just wonder if anyone know how can I check the network file (whether exists or not) using TSQL or Extended Stored Procedures?

What I can find out is the xp_fileexist, but it is only meant for searching file residing locally... I have tried mapping the drive of another server to the SQL server, but it just won't do it...

Any idea?

Thanks in advance.

Regards,
TangI have used xp_cmdshell as follows:

exec @.result = xp_cmdshell ('dir \\server\share\filename.ext')

if(@.result = 0)

begin
it's there so do something
end

This has worked well so far...

Check the sql server service account

Hello!

I would need to check the name of the sql server service account from inside TSQL.

I had one idea about reading from the sysprocesses system table,
but that only gives me information about the SQL Server Agent service account.

Are there other ways?

(It has to work for both SQL Server 2000 and SQL Server 2005.)

Best regards

Ola Hallengren

There is an undocmented approach using 'registry key reading' in SQL Server 2000 version:

xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key='SYSTEM\ControlSet001\Services\SQLServerAgent',
@.value_name='ObjectName'

I'm working onSQL 2005 and will post here once it is successful.