SQL – How To Return Ref of Inserted Row

It is extremely useful to return the URN, or any other field, of a newly inserted row for a given table in a single query. The OUTPUT clause makes this possible.

insert into MY_TABLE (Field_1, Field_2, Field_3)
OUTPUT Inerted.REF
values (‘Foo’,’Bar’,’Example’)

The above example returns the REF field, which contains auto-incremented urn in MY_TABLE.

SQL – View SQL Server Database Mail Log (All / Successful / Failed)

SQL Server keeps a log of mail sent via Database Mail and the sp_send_dbmail Stored Procedure.
I find these code snippets useful to view mail history including sent and failed items. You can view address, subject & body, file attachment(s), status, sent date, etc.

All Messages – regardless of status and deliverability
SELECT top 50 *
FROM [msdb].[dbo].[sysmail_allitems]
ORDER BY [send_request_date] DESC
Sent Items
SELECT TOP 50 *
FROM [msdb].[dbo].[sysmail_sentitems]
ORDER BY [send_request_date] DESC
Failed Items
SELECT TOP 50 *
FROM [msdb].[dbo].[sysmail_faileditems]
ORDER BY [send_request_date] DESC

SQL – The EXECUTE permission was denied on Stored Procedure

The EXECUTE permission was denied on the object ‘STORED_PROC’, database ‘MYDB’, schema ‘dbo’

This massage is received when the SQL Server account under which you make the request doesn’t have permission to execute the stored procedure. you can overcome this problem by modifying the permissions of the SP.
If you want to grant public access, so all users can execute the procedure:

USE [DATABASE];
GRANT EXEC ON dbo.[MYSTOREDPROCEDURE] TO PUBLIC

 

If you want to tighten security to allow only specific users to execute the SP, which is advisable, then its:

USE [DATABASE];
GRANT EXEC ON dbo.[MYSTOREDPROCEDURE] TO MYUSER

OR

USE [DATABASE];
GRANT EXEC ON dbo.[MYSTOREDPROCEDURE] TO MYGROUP

SQL Server SSMS Saving changes is not permitted

You may receive this dialogue if you’re using a fresh install of SSMS, or after an upgrade. It simply means your environment hasn’t been configured to allow certain changes which require the dropping, and re-creation, of a table.

Such actions include:

  • Adding a new column anywhere other than the end of a table
  • Deleting a column
  • Updating column configuration, such as Allow Nulls or the Data Type
  • Changing the column order

You can modify your environmental settings to allow for these actions form the Tools menu. Click Options, expand Designers, and then click Table and Database Designers. Un-tick Prevent saving changes that require the table to be re-created and click OK.

You may now commit your table changes.

 

SQL – How to Update and Select in the same query

The OUTPUT clause Returns information on each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.

These results can be returned to the processing application for further use.

The following example uses the OUTPUT clause to return row values in conjunction with an UPDATE query which effectively enables a SELECT and UPDATE command in the same query.
 
— CREATE TEMP TABLE
CREATE TABLE #TempTable ( URN INT, F1 VARCHAR( 50), F2 VARCHAR(50 ), USED VARCHAR (1))
— INSERT DATA
insert into #TempTable values ( 1,’F1_Val_1′ ,’F2_val_1′, ‘N’)
insert into #TempTable values ( 2,’F1_Val_2′ ,’F2_val_2′, ‘N’)
insert into #TempTable values ( 3,’F1_Val_3′ ,’F2_val_3′, ‘N’)

— UPDATE SINGLE ROW IN TABLE WITH NEW VALUE
— USING OUTPUT COMMAND TO RETURN FIELDS OF UPDATED ROW
UPDATE #TempTable
SET F1 = ‘F1_New_Val’
OUTPUT Inserted.URN, Inserted.F1, Inserted.F2
WHERE URN = 1

— UPDATE MULTIPLE ROWS IN TABLE WITH NEW VALUE
— USING OUTPUT COMMAND TO RETURN FIELDS OF UPDATED ROWS
UPDATE #TempTable
SET F1 = ‘F1_And_F3_new_Val’
OUTPUT Inserted.URN, Inserted.F1, Inserted.F2
WHERE URN in (1 ,3)

— UPDATE ALL ROWS IN TABLE WITH NEW VALUE
— USING OUTPUT COMMAND TO RETURN FIELDS OF ALL ROWS IN TABLE
UPDATE #TempTable
SET F1 = ‘New_Global_Val’
OUTPUT Inserted.URN, Inserted.F1, Inserted.F2

— UPDATE TOP 1 ROW IN TABLE WITH NEW VALUE
— MARK USED = ‘Y’
— USING OUTPUT COMMAND TO RETURN FIELDS OF UPDATED ROW
UPDATE #TempTable
SET F1   = ‘F1_New_Val’
,USED = ‘Y’
OUTPUT Inserted.URN, Inserted.F1, Inserted.F2
WHERE URN in (select max( URN) from #TempTable WHERE used = ‘N’)

— DROP TEMP TABLE
DROP TABLE #TempTable

 

SQL – View SQL Server Database Mail Log (All / Successful / Failed)

SQL Server keeps a log of mail sent via Database Mail and the sp_send_dbmail Stored Procedure.

I find these code snippets useful to view mail history including sent and failed items. You can view address, subject & body, file attachment(s), status, sent date, etc.

All Messages – regardless of status and deliverability
SELECT top 50 *
FROM [msdb].[dbo].[sysmail_allitems]
ORDER BY [send_request_date] DESC
Sent Items
SELECT TOP 50 *
FROM [msdb].[dbo].[sysmail_sentitems]
ORDER BY [send_request_date] DESC
Failed Items
SELECT TOP 50 *
FROM [msdb].[dbo].[sysmail_faileditems]
ORDER BY [send_request_date] DESC

SQL Server – One-off Backup and restore of ALL SQL Server Agent jobs using SQL Server Management Studio

One-off Backup and restore of all SQL Server Agent jobs using SQL Server Management Studio

1. Connect to the SQL server using SQL Server Management Studio.

2. Navigate to the Object Explorer Details view by selecting View à Object Explorer Details from the menu bar or press F7.

3. Double-click SQL Server Agent from the Object Explorer Details window.

4. Double-click the Jobs folder to expand the list of agent jobs currently configured on the SQL Server.

5. At this point you have the option to select only the jobs you want to backup, or select all jobs on the server (Ctrl+A).

6. Once your selection has been made, right-click over the selected area, hover Script Job As à CREATE To à File …

7.Navigate to a file location and save your SQL Server Agent job(s) creation code to a .SQL file.

Open the SQL file to execute on another SQL Server to restore a previous version of agent job(s), or to create the job(s) at a new location.

SQL – How to get the status of an SQL job (sp_help_job)

 

Here is a simple script using sp_help_job to display information about jobs that are used by SQL Server Agent:

USE msdb
EXEC dbo.sp_help_job @JOB_NAME = ‘JOB_NAME’, @job_aspect=‘JOB’

Look for ‘current_execution_status’ to get the status of the job.

1 = Executing.
2 = Waiting for thread.
3 = Between retries.
4 = Idle.
5 = Suspended.
7 = Performing completion actions.

 

Here is a more detailed script to store the results of sp_help_job into a table and query the results:

DECLARE @JOBNAME VARCHAR(100)
SET @JOBNAME = ‘JOB_NAME’

CREATE TABLE #JOBSTATUS(
       job_id uniqueidentifier 
      ,originating_server nvarchar(30)
      ,name sysname
      ,enabled tinyint
      ,description nvarchar(512)
     
,start_step_id int
     
,category sysname
     
,owner sysname
      ,notify_level_eventlog int
     
,notify_level_email int
     
,notify_level_netsend int
     
,notify_level_page int
     
,notify_email_operator sysname
     
,notify_netsend_operator sysname
     
,notify_page_operator sysname
     
,delete_level int
     
,date_created datetime
     
,date_modified datetime
     
,version_number int
     
,last_run_date int
     
,last_run_time int
     
,last_run_outcome int
     
,next_run_date int
     
,next_run_time int
     
,next_run_schedule_id int
     
,current_execution_status int
     
,current_execution_step sysname 
     
,current_retry_attempt int
     
,has_step int
     
,has_schedule int
     
,has_target int
     
,type int     
); 

INSERT INTO #JOBSTATUS
EXEC dbo.sp_help_job
 @JOB_NAME = @JOBNAME
,@job_aspect=‘JOB’

SELECT CASE current_execution_status
WHEN ‘0’ THEN ‘Returns only those jobs that are not idle or suspended. ‘        
WHEN ‘1’ THEN ‘Executing.’
        
WHEN ‘2’ THEN ‘Waiting for thread.’
       
WHEN ‘3’ THEN ‘Between retries.’
        
WHEN ‘4’ THEN ‘Idle.’

WHEN ‘5’ THEN ‘Suspended.’

WHEN ‘6’ THEN

WHEN ‘7’ THEN ‘Performing completion actions.’

ELSE ‘UNKNOWN’
END as ‘current_execution_status’
FROM #JOBSTATUS

DROP TABLE #JOBSTATUS

 

OUTPUT:


current_execution_status
——————————————————–
Idle.

SQL – How to get the day of the week

Using the DATEPART function to calculate the integer value of the day of the week.

select datepart(dw,getdate()) as DayOfWeekINT


Output:
DayOfWeekINT
————
2  

1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday

Using the DATENAME function to calculate the  day of the week.

select datename(dw,getdate()) as DayOfWeekCHAR


Output:
DayOfWeekCHAR
——————————
Monday
 
 

 

You can get the day of the week for a given date (MM/DD/YYYY) using:

select datepart(dw,’03/07/2011′) as DayOfWeekINT
select datename(dw,’03/07/2011′) as DayOfWeekCHAR