Archive for the ‘T-SQL’ Category

Dynamically getting the execution status of a SQL Server Agent Job

April 23, 2010 2 comments


This problem seems trivial but there is no straight forward way to get this information. If you have tried the INSERT into EXEC command to insert the results of the sp_help_job procedure into a temporary table like below then you are familiar with the below error.

insert into #jobstatus
execute msdb..sp_help_job

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.

So you can not directly insert the results to a temp table from this procedure. Here is a trick I have used using the OPENROWSET to get around with the INSERT EXEC problem. This query results can be stored into a temporary table or a table variable using the INSERT INTO or SELECT INTO command which can be used for further processing.

   select name, case when current_execution_status = 1 then ‘Executing’
when current_execution_status = 2 then ‘Waiting For Thread’
when current_execution_status = 3 then ‘Between Retries’
when current_execution_status = 4 then ‘Idle’
when current_execution_status = 5 then ‘Suspended’
when current_execution_status = 6 then ‘[Obsolete]’
when current_execution_status = 7 then ‘PerformingCompletionActions’
else NULL end as [status]  from
(‘SQLNCLI’, ‘Server=(local);Trusted_Connection=yes;’,‘EXEC msdb..sp_help_job’)

The OPENROWSET is one of the workarounds for the nested INSERT EXEC problem and many other solutions are proposed here (Thanks to Kalman for the workarounds).

Hope it helps

🙂 ranjith 🙂

Script to drop all objects of a Schema in SQL Server

January 31, 2010 8 comments


I have spent almost 3 hours to complete the script and test it for couple of sample scenarios during this weekend (31/01/10).  The script initially drops all the objects of the schema and then drops the schema itself. It takes care of all the object dependencies with in the schema by dropping all of them in a specific order that will resolve the dependency issues.

Download the SP created using the script from here (MS Word Document) or use this Google Doc link to view the script(Thanks to Elias for the link).

The stored procedure takes two parameters SchemaName and the WorkTest. Use WorkTest equal to ‘t’ to print all the drop statements without executing them or specify anything else to execute the drop operations. The default option is ‘w’ i.e. Work for me.

EXEC CleanUpSchema 'MySchema', 't'        -- debug
EXEC CleanupSchema 'MySchema', 'w'        -- work for me


These are the known limitations of the script

  • It can not drop a PK table in the schema with an XML index or Spatial index defined
  • It can not drop the schema which is referred by a XML schema collection
    Please let me know if you find any more issues with the script. I will list all of them here for others reference and will fix them to improve it further.


Hope it helps

🙂 ranjith 🙂