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 strip out all non-alphabetic characters from string

Create the following function to strip out non-alphabetic characters from a string in a SQL statement:

CREATE Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
While PatIndex(‘%[^a-z]%’, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(‘%[^a-z]%’, @Temp), 1, ”)

Return @TEmp
End

Call the function using the following syntax:

select RemoveNonAlphaCharacters(‘qwerty123uiop56789’)

returns:

qwertyuiop

 

You can allow numbers 1-9  along with letters a – z by replacing the PatIndex with ‘%[^a-z0-9]%’

If you want to allow letters a – z, numbers 0 – 9 and other characters such as underscore the syntax is ‘%[^a-z0-9_]%’