Tag Archive for Templates

Becoming a SQL Prompt Power User

MCJJ_ALLFour SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. This month we are cooperatively blogging about SQL Prompt by Red Gate.

The Next Mission

What makes up a SQL Prompt power user? Well, they use the SQL prompt commands like they were extensions of SSMS. They know the shortcuts, and they’ve memorized the key words to call up their favorite scripts. Let’s take a look what these short cuts are and how to integrate them seamlessly into your every day SQL Life.

Playing The Ebony Keys

A power user of any software is one who uses the hot keys in lieu of the menus. SQL prompt has a plethora of hot key combination.

Before I use <ctrl> + e to execute my code, I always use <ctrl> + k, <ctrl> + y. This hot key combo will structure your code based on the styles you have set in SQL prompt. This is my most heavily used hot key combo right after <ctrl> + e and <ctrl> + r (hiding the results window).

There is a new hot key combination that is coming out soon in version 6.4, and I’m very excited about it. It adds a semicolon at the end of each statement! I’ve been trying to add the habit of using semicolons for 2 years now, and it never sticks. Now I can use <ctrl> + b, <ctrl> + c and away I go.

Here is a table of the hot key combinations I use the most, plus the shortcut way I remember them.

Hot Key Combination Shortcut Hot Key Combo What It Does
<ctrl> + b,
<ctrl> + w
<ctrl> + bw Expands ALL of the wild cards.
<ctrl> + b,
<ctrl> + q
+ bq Qualifies all the SQL Objects and fixes the capitalization as well.
in beta <ctrl> + b,
<ctrl> + c
<ctrl> + bc Adds semicolons to the end of each statement. (Available soon in 6.4)
<ctrl> + k,
<ctrl> + y
<ctrl> + ky Standardizes the code based on the formatting styles that were set in SQL Prompt.

As a power user, my key strokes will look like this when I use them all at once:

<ctrl> + bw, bq, bc, ky and finally <ctrl> + e to execute my code. Here is an example of how an ugly duckling turns into a beautiful swan by applying all of these hot keys at one time.

Before


alter procedure [dbo].[salesteamassignment_rptugly](@group as varchar(1000), @startdate as date, @enddate as date)as
set nocount on
declare @uselessvar as varchar(max)
select
p.businessentityid ,firstname,lastname,s.name as store,[group] as TerritoryGroup,cr.name as CountryRegion,st.name as Territory,customercount,s.*
from
adventureworks2008r2.sales.salesperson as sp join adventureworks2008r2.person.person as p on sp.businessentityid = p.businessentityid
join adventureworks2008r2.sales.salesterritory as st on sp.territoryid = st.territoryid join [adventureworks2008r2].[person].[countryregion] as cr on st.countryregioncode = cr.countryregioncode
join adventureworks2008r2.sales.store as s on s.salespersonid = sp.businessentityid join dbo.fn_split(',', @group) as grps on st.[group] = convert(nvarchar(50), grps.separatedvalue)
outer apply (select count(*) as customercount from adventureworks2008r2.sales.customer as c where s.businessentityid = c.storeid ) as cc

After

ALTER PROCEDURE dbo.SalesTeamAssignment_rptUGLY
(
@group AS varchar(1000)
,@startdate AS date
,@enddate AS date
)
AS
SET NOCOUNT ON;
DECLARE @uselessvar AS varchar(MAX);
SELECT
p.BusinessEntityID
,p.FirstName
,p.LastName
,s.Name AS store
,st.[Group] AS TerritoryGroup
,cr.Name AS CountryRegion
,st.Name AS Territory
,cc.customercount
,s.BusinessEntityID
,s.Name
,s.SalesPersonID
,s.Demographics
,s.rowguid
,s.ModifiedDate
FROM
AdventureWorks2008R2.Sales.SalesPerson AS sp
JOIN AdventureWorks2008R2.Person.Person AS p ON sp.BusinessEntityID = p.BusinessEntityID
JOIN AdventureWorks2008R2.Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
JOIN AdventureWorks2008R2.Person.CountryRegion AS cr ON st.CountryRegionCode = cr.CountryRegionCode
JOIN AdventureWorks2008R2.Sales.Store AS s ON s.SalesPersonID = sp.BusinessEntityID
JOIN dbo.fn_Split(',', @group) AS grps ON st.[Group] = CONVERT(nvarchar(50), grps.SeparatedValue)
OUTER APPLY (
SELECT
COUNT(*) AS customercount
FROM
AdventureWorks2008R2.Sales.Customer AS c
WHERE
s.BusinessEntityID = c.StoreID
) AS cc;

But Wait, There’s More

Formatting your code is only half of the features in SQL Prompt. There are three other features that every power user should use. (Ok, the truth be told, there are more than 3, but my article shouldn’t be the length of a book.)

Feature 1:

SQL Prompt offers the ability to save snippets of code. This is really useful for repetitive tasks. Here is a list of articles that I’ve written talking about specific snippets that I find handy.

Placeholders are one of the most helpful features that SQL Prompt provides for you to work with snippets. The placeholders are reserved words that are variables for specific pieces of information or represent actions that can be applied to your snippet. They are always completely capitalized and are surrounded by dollar signs ($). You can find the current list for SQL Prompt 6 here.

One of the new placeholders is prefect for the power user. It allows you to insert a snippet and have part of it highlighted. Here is an example of how I like to use it. This particular snippen, which I named “st1”, will add SELECT TOP 100 * FROM in front of the table name that I”ve highlighted and copied to the copy buffer.

$SELECTIONSTART$SELECT TOP 100 *
FROM $PASTE$$SELECTIONEND$

The $PASTE$ placeholder will be replaced with whatever is in my copy buffer. The $SELECTIONSTART$ and $SELECTIONEND$ placeholders will cause the code within the placeholders to be highlighted.

To use it, I follow these steps.

1. I highlight a table I want to use and hit <ctrl> + c to get the table  into my copy buffer.

SQLCoop5_image1

 

2. I type st1 + to get the snippet. I now have a highlighted SELECT statement ready to execute, so I hit + e to execute it.

SQLCoop5_Image2

 

3. Finally, I type <ctrl> + z twice to undo the changes I’ve made.

SQLCoop5_image1

 

This allows me to quickly see some sample data from the table without ever leaving my current query window.

Feature 2:

This is a hidden gem. This feature finds all the invalid objects in your database. This is a great feature for those of you who need to perform code reviews and want to make sure that any DDL changes have not broken any existing SQL Objects.

In this example I have a table with a trigger that uses the field ModifiedDTS. I deleted the field and ran the command. It listed all the SQL Objects that are now broken due to the deleted field.

SQLCoop5_Image3

This feature does not have a Hot Key, but it is definitely worthy of a power user.

Feature 3:

This is a great “clean up” feature, especially when you are working with stored procedures with 200 + lines that you have inherited. You know the ones, they are filled with old code and have never been cleaned up. Well, this feature is perfect for it, because it finds all the unused variables and parameters. Simply use the hot key combination + b, + f. SQL Prompt, will then underline in green all the unused variables and parameters for you to evaluate for usefulness.

SQLCoop5_Image4

 

I encourage you to spend 10 minutes going through the SQL Prompt menu to find out what other goodies exist for you to leverage. There are also new features coming soon in version 6.4. Here is a great reference to all the release notes for each version, plus the release notes for the current beta version.

Don’t Stop Yet

If you want to read more about how to work with SQL Prompt, then check out these blog posts:

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

See you next month!!

Original Post: On a SQL Collaboration Quest

T-SQL Tuesday #50 – Automation Equates To Saved Time

SqlTuesday50Hemanth D (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009 and we hit the 50th “episode” this month. Could this be coincidentaly occurring during the 50th anniversary of Doctor Who?? I think not…

Anyhow, the topic this month is automation.

I can play that in 3 notes

There used to be a game show called Name That Tune. A contestant had to underbid the other contestant on what the minimum number of (musical) notes they needed in order to recognize a piece of music. Automating tasks remind me of this game show. First, you automate step one, then step two. Each time you tweak it and tune your automated process, you’re seeing just how far you can go… and how little manual work you are left with.

The first step

I’m going to talk about automating the first step of creating an SSRS report. I’ll be working with SSRS 2012, but the steps are the same for 2008 and 2008R2. The only difference is WHERE you store the templates so that they can be easily leveraged. Since that is the last thing you do, you’ll have to wait for the end of this post. So, hold on to your Tardis, and let’s go!

The first thing you need to know, is what are all the common elements to all of your reports. Here is a list of the elements I found to be the same on my reports:TSQL Tuesday 50 - Image 1

  • The location of the title: While my title was different each time, the font, font color, font size, and location remained static. So I created a placeholder for my title.
  • The company logo: The company logo was static as well as its location.
  • The color scheme: Since I used the same five colors, I created a temporary “pallet” for my colors. This was a mini Tablix control, with each of the five cell’s background color set to a different color in my pallet. I left it on the report until I was done setting all the properties of the report, then I deleted the Tablix. (No more looking up the colors in my documentation. Win!)
  • The location of the parameters: I personally think that the parameters should always be displayed on the report. This helps when troubleshooting a paper/pdf copy of a report. It also lets the users know the boundaries of the data they are looking at. (Note: While not depicted in my image, I put my parameters under the logo.)
  • The company address: Static location.
  • The confidentiality notice: Static location.
  • The page numbers: Static location.
  • The report identifier: This is a special number that helps you identify your report. Mine always has three parts.
    • TX or DW to mark the report as having transactional or data warehouse based data. This allows me to speak intelligently about a report that someone shows me in a meeting, especially since our data warehouse data was always older than our transactional data.
    • A number that corresponded to the documentation for the report. In our case, it was the TFS (Team Foundation Server) number.
    • An iteration number. This iteration number was specific to how many times the report was re-introduced into production. This allowed me to verify that the user was looking at the latest copy of the report, and it allowed me to document how many times the owner had requested changes to the report.
Creating the templates

A template is an RDL file saved in the templates folder. I created three templates for my team. Each template was identical to the others, except for two things. The paper size and orientation. I needed to make different templates to accommodate these attributes so that the controls that were centered or right aligned ended up in the correct location for viewing and printing.

  • SSRS 2008(R2) Location: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
  • SSRS 2012 Location: C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
But wait! There’s more

If you want to go a bit further with your templates, you can add a watermark that can be leveraged during development all the way through user acceptance testing. The watermark will then be suppressed in production. The watermark says DRAFT. I found it helped with certain end users, who were getting caught up in the data (even though it was fake) and they weren’t focusing on the design and algorithms present in the report.

Here are the steps to add the dynamic watermark:

  1. Create a table in the database that contains the name and ID of the environment you are in, plus a parameter that dictates whether to show or hide the watermark. Note: You can also use this table to point to development file locations for documents and development URLs referenced in your reports.
  2. Add an image to the background of the report body that says DRAFT.
  3. Set the Background Repeat property to Repeat
  4. Create a data source in the report that points to the environment table you created in step 1 (preferably through a stored procedure).
  5. Use the data source to hide or show the background image of the report.

If you use shared data sources, then add the data source to the template directory so that it can be added to new projects the same way report templates are.

There you have it! You just automated quite a few (initial) steps for creating SSRS reports.

Thanks for all the fish

Thanks go out to Hemanth for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://sqlchow.wordpress.com/.

SQL Advent Calendar – Day 17 – Now, Which Tables Still Need a Trigger?

AdventCalendar17First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Oh where, oh where did my trigger go?

On Day 8, Day 9, and Day 10 I wrote about using 4 Change tracking fields in tables. Two of the fields, ModifiedBy and ModifiedDTS are updated in a trigger. If you are slowly making changes to many tables, you can loose track of which tables have the triggers you need.

This little snippet for SQL Prompt, called FindTrig, will get two result sets for you. The first result set is a comma delimited list of tables that have the ModifiedDTS field, but do not have a trigger with the pattern trg__u. The second result set will show you a the same list of tables and the triggers they already have. This will be helpful when the code to update ModifiedBy and ModifiedDTS was added to a different trigger than expected. You definitely don’t want two triggers updating the same two fields. That will cause an infinite loop of updates.

USE <DBName,string, $DBNAME$>
GO
/* ***********************************************************************************
Purpose:    Creates a comma delimited list of tables that may need a trigger to populate
			the ModifiedBy and ModifiedDTS fields
Author:     mstuewe
Date:       12/17/2013
************************************************************************************ */
DECLARE @Tables AS nvarchar(max) 

	SELECT
		@Tables = ISNULL(@Tables + ',', '') + t.name
    FROM
        sys.objects AS t
        JOIN sys.all_columns AS c ON c.object_id = t.object_id
        LEFT JOIN sys.Triggers AS Tr ON tr.name = 'trg_' + t.name + '_iu'
                                                 OR tr.name = 'trg_' + t.name + '_u'
    WHERE
        t.type = 'U'
        AND c.NAME = 'ModifiedDTS'
		AND tr.name IS NULL
	ORDER BY 
		t.name

	IF @Tables IS NULL
		PRINT 'All tables have a trigger.'
	ELSE
	BEGIN
		SELECT @Tables AS TableNames

		SELECT
			t.NAME AS TableName
			,tr.name AS ExistingTrigger
		FROM
			(
			SELECT
				t.object_id
				,t.name
			FROM
				sys.objects AS t
				JOIN sys.all_columns AS c ON c.object_id = t.object_id
				LEFT JOIN sys.Triggers AS Tr ON tr.name = 'trg_' + t.name + '_iu'
														 OR tr.name = 'trg_' + t.name + '_u'
			WHERE
				t.type = 'U'
				AND c.NAME = 'ModifiedDTS'
				AND tr.name IS NULL
			) AS t
			LEFT JOIN sys.Triggers AS Tr ON Tr.parent_id = t.object_id
		ORDER BY 
			t.name
	END
GO

As an example, I created three tables with the four changing tracking fields, Employee, Phone, and PhoneType. I added the trigger to Employee and Phone, but the trigger for Phone had it’s name change. After looking at the results returned, I would know that I only need to create a trigger script for the PhoneType table.

 

AdventCalendar2013Day17_Img1

 

Note: You can download this snippet from my Script Library under Resources. It’s called FindTrig.

 

Previous Post In Series: SQL Advent Calendar – Day 16 – Snippet For In-Line Table Functions

SQL Advent Calendar – Day 16 – Snippet For In-Line Table Functions

AdventCalendar16First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Three French Hens, I mean User Defined Functions

There are three kinds of User Defined Functions that you can create in SQL Server.

  1. Scalar Functions
  2. Multi-line Table Functions
  3. In-Line Table Functions

They all have a purpose but unfortunately the first two listed above are misunderstood and misused the most. Below are a couple of articles on the differences and the performance problems that can be caused by Scalar and Multi-line Table Functions.

I created this snippet called NewFun for SQL Prompt when I was first learning the differences between these function types, and I still use to today. Why? It saves key strokes.

USE <DatabaseName, string,$DBNAME$>
GO
--Use Shift + Ctrl + M to set Template Parameters
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'<schemaowner,string,dbo>.<sprocname,string,>') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION <schemaowner,string,dbo>.<sprocname,string,>
GO
/* ***********************************************************************************
Purpose:    [Brief Description]
Notes:      
Sample:    
            EXEC <schemaowner,string,dbo>.<sprocname,string,> [Parameters]
Author:     $USER$
Date:       $DATE$

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */
CREATE FUNCTION <schemaowner,string,dbo>.<sprocname,string,>
(
	--Insert Parameters
)
RETURNS Table
AS 

	RETURN 
	(
		$CURSOR$
	)

GO

 

Note: You can download this snippet from my Script Library under Resources. It’s called NewFun.

Previous Post In Series: SQL Advent Calendar Day -15 – Naming Default Constraints

Next Post In Series: SQL Advent Calendar – Day 17 – Now, Which Tables Still Need a Trigger?

 

SQL Advent Calendar Day -15 – Naming Default Constraints

AdventCalendarDay15First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Standardization

I truly believe in having coding standards. I think the best standards that a team can have, are the ones that they are willing to adhere to. If no one agrees that commas belong on the left rather than the right, then why does your standards say they have to be on the left?

One standard that I really like to have, and is supper easy to do using macros,  is having decent names for indexes and constraints. DF_Mytable_1837489274 does absolutely nothing for me. But DF_Mytable_isActive, is helpful when looking at all the constraints on my table.

ER Studio DA has three macros to help with these naming standards. Name foreign Key Constraints, Name Primary Key Constraints, and Index Naming. When creating default constraints in ER Studio Data Architect ( ER Studio DA), I found that I was getting random numbers on my default names in SQL Server. This was especially true if I created my defaults from Domains. (Domains are templates for fields. I wrote about Domains here.)So I created an additional macro called Name Default Constraints-Selected to round out this collection of macros.

My particular standard for naming defaults is as follows: DF__. If you have different standards that can be derived from information in the field, then you can change the pattern on line 42. Take a look at lines 36 and 37 too. These two lines are necessary when working with Domains. They make it possible to edit the default name for a field that is tied to a domain.

This macro will perform two different actions. It will create a name for defaults that don’t have one, and it will ask the user if they want to change a name that does not adhere to the standards. After the macro has completed, a dialog box is displayed showing how many defaults were named and how many had their name updated.

'----------------------------------------------------------------------------
' PURPOSE:  	This macro will create and update Default Constraint names on
'				Attributes For All selected Entities. If the attribute is
'				bound, it will overwrite the bound default and name the default.
' Author:   	Mickey Stuewe
' Date:     	12/15/2013
'----------------------------------------------------------------------------
Option Explicit

Sub Main

	Dim objModel As Model
	Dim objSubModel As SubModel
	Dim objSelObject As SelectedObject
	Dim objAttrib As AttributeObj
	Dim objEntity As Entity
	Dim iCounter As Integer
	Dim iNCcounter As Integer
	Dim iunBoundCounter As Integer
	Dim strDefaultName As String

	Set objModel = DiagramManager.ActiveDiagram.ActiveModel
	Set objSubModel = objModel.ActiveSubModel

	If Not objModel.Logical Then

		' Iterate through all the selected objects in the current
		' model.
		For Each objSelObject In objSubModel.SelectedObjects
			If objSelObject.Type = 1 Then  'Entities
				Set objEntity = objModel.Entities.Item(objSelObject.ID)
				For Each objAttrib In objEntity.Attributes
					If Len(objAttrib.DeclaredDefault) > 0 Then
						'if the attribute default is bound, then unbind it so that it can be named.
						If objAttrib.DomainId > 0 Then
							'You have to override the bound default value and the default text (name).
							objAttrib.EnableOverride (1,True)
							objAttrib.EnableOverride (2,True)
							iunBoundCounter = iunBoundCounter + 1
						End If

						strDefaultName = "DF_" & objEntity.EntityName & "_" & objAttrib.AttributeName
						If Len(objAttrib.DefaultName) = 0 Then
							objAttrib.DefaultName = strDefaultName
							iCounter = iCounter + 1
						ElseIf objAttrib.DefaultName <> strDefaultName Then
							If MsgBox("The name of the Default Constraint does not match our naming standards. Would you like to replace it?" & vbCrLf & _
								vbCrLf & vbCrLf & "Attribute: " & objAttrib.AttributeName & " - Default Name: " & objAttrib.DefaultName & " - Value: " & _
								objAttrib.DeclaredDefault & vbCrLf & " Standardized Name: " & strDefaultName, _
								vbYesNo + vbQuestion, "Stadardize Name?") = vbYes Then
									objAttrib.DefaultName = strDefaultName
									iCounter = iCounter + 1
							End If
						End If
					Else
						iNCcounter = iNCcounter + 1
					End If
				Next objAttrib
			End If
		Next objSelObject

		MsgBox iunBoundCounter & " had their defaults unbound so that they could be named." & vbCrLf & vbCrLf & _
				iCounter & " Default Constraints had their name created or updated." & vbCrLf & vbCrLf & _
				iNCcounter & " Attributes were not modified.", vbOkOnly + vbInformation, "Default Constraint Name(s) Created or Updated"

	Else
		MsgBox "You are in a Logical model. Move to a Physical model to name of Default Constraints.", vbOkOnly + vbCritical, "Need Physical Model"
	End If
End Sub

 

Note: You can download this macro from my Script Library under Resources. It’s called Name Default Constraints – Selected.

 

Previous Post In Series: SQL Advent Calendar – Day 14 – Working With Forms in a Macro in ER Studio Data Architect

Next Post In Series: SQL Advent Calendar – Day 16 – Snippet For In-Line Table Functions

SQL Advent Calendar – Day 13 – Snippet for New Transactional Procedure

AdventCalendar13First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Begin Transaction

Two days ago, I shared my snippet to create a shell for new procedures. A week ago, I found I was writing quite a few update and insert procedures, so I created this new template called new_iu.

The procedures I’m creating are called by an application, so the template I created provides the following:

  • Begin and End Tran
  • Rollback in the error handler
  • Return statement. (0 is successful, anything else is a failure)
  • An output parameter that returns the number of rows changed. We needed this, because more that one kind of row was updated or inserted, but we only want to count the “main” units.

 

USE <DatabaseName, string,$DBNAME$>
GO

--Use Shift + Ctrl + M to set Template Parameters

IF EXISTS ( SELECT * FROM sys.objects WHERE	object_id = OBJECT_ID(N'<schemaowner,string,dbo>.<sprocname,string,>') AND type IN (N'P', N'PC') ) 
	DROP PROCEDURE <schemaowner,string,dbo>.<sprocname,string,>
GO
/* ***********************************************************************************
Purpose:    <purpose,string,[brief description]="">
Notes:      
Sample:    
            DECLARE @RV as int, @RecordsAffected as int
			EXEC @RV = <schemaowner,string,dbo>.<sprocname,string,> [Parameters],@RecordsAffected OUTPUT
			SELECT @RV as ReturnValue, @RecordsAffected as RecordsAffected
Author:     $USER$
Date:       $DATE$

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */
CREATE PROCEDURE <schemaowner,string,dbo>.<sprocname,string,> 
(
	--Insert Parameters
	,@RecordsAffected AS int OUTPUT
)
AS 
BEGIN TRY
	SET NOCOUNT ON
----------------------------------------------------
-------           Test Parameters           --------
----------------------------------------------------
--DECLARE
--    @StartDate AS date = '12/1/2010'
--   ,@EndDate AS date = '12/5/2010'
--   ,@RecordsAffected AS int --OUTPUT
----------------------------------------------------
----------------------------------------------------
	BEGIN TRAN

		$CURSOR$--[TSQL Statements]

	COMMIT TRAN

END TRY
BEGIN CATCH
	DECLARE
		@ErrorMessage AS nvarchar(3000)
	   ,@ErrorSeverity AS int

	IF @@TRANCOUNT > 0
	BEGIN
        ROLLBACK TRAN
		SET @RecordsAffected = 0
	END 

	SET @ErrorMessage = ISNULL(DB_NAME(DB_ID()) + N'.' + SCHEMA_NAME(SCHEMA_ID()) + N'.' + OBJECT_NAME(@@PROCID, DB_ID()),
						N'SQL Object Name Not Available') + N': Error: ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + N' Line: ' + CONVERT(nvarchar(5), ERROR_LINE()) + N' - '
						+ ERROR_MESSAGE()    
	SET @ErrorSeverity = ERROR_SEVERITY()
	RAISERROR(@ErrorMessage, @ErrorSeverity, 1)

	RETURN 1

END CATCH

SET NOCOUNT OFF
RETURN 0
GO
Commit Transaction

If you have other successful ways to write insert, update, or delete stored procedures, please share in the comments.

Note: You can download this snippet from my Script Library under Resources. It’s called New_IU.

Previous Post In Series: SQL Advent Calendar – Day 12 – And Neo Yelled
Next Post In Series: SQL Advent Calendar – Day 14 – Working With Forms in a Macro in ER Studio Data Architect

SQL Advent Calendar – Day 12 – And Neo Yelled

AdventCaledar12First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

A Needed Siesta

We are half way to Christmas. Can you believe it? Writing this blog series has been great for me, but tonight I’m tired. So with that, I’m going to share some of the Snippets that come with SQL Prompt.

The first two are fun ones. Type Neo + TAB and Yell + TAB and see what you get. They always make me feel better.

If you really want to get into snippets, you can really save on the keep strokes with the following.

ssf SELECT * FROM
st100 SELECT TOP 100 *
FROM
cj CROSS JOIN
ij INNER JOIN
ob ORDER BY
gb GROUP BY

What other quick strokes do you think should be added?

Previous Post In Series: SQL Advent Calendar 2013 – Day 11 – Snippet For a New Stored Procedure
Next Post In Series: SQL Advent Calendar – Day 13 – Snippet for New Transactional Procedure

SQL Advent Calendar 2013 – Day 10 – Creating Change Tracking Domains (3 of 3)

AdventCalendar10First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

The Final Piece

The last couple of days I’ve written here and here about how to add simple auditing to your tables using ER Studio Data Architect. Today is the finishing piece. Today I will share my SQL Prompt script that creates the triggers for the tables with the ModifiedBy and ModifiedDTS fields.

The trigger is an update trigger. Whenever the record is change, the trigger will be executed. If the user provided a value for the ModifieidBy field, then that value will be used. If a new value is not provided, then the SUSER_NAME() function will be used. I put that check in there for applications that use a single credential to log in to SQL Server, but the application can send in the actually user. If you don’t need that feature, then it can be taken out.

Before running the code, do the following.

  1. Use CTRL+SHIFT+M to update the script with the values you need. This will include a comma delimited list of table names, and a single schema name. (Eventually, I’ll add functionality for multiple schemas.)
  2. The script is setup to print the triggers to the Messages pane. You can copy the scripts into another window to execute, or you can set the @ReadyToExecute bit to 1. This will cause the scripts to be executed as the triggers are built.
WARNING: Make sure you verify that a trigger with the same name as the one being generated does not exists. IT WILL BE DROPPED with this code. ALSO, make sure adding this trigger, will not cause issues with other existing update triggers.

 

USE 
GO
/* ***********************************************************************************
Purpose:    Creates new update triggers for tables that use ModifiedDTS and ModifiedBy.
Notes:      1. Use Shift + Ctrl + M to set Template Parameters
			2. Change the @ReadyToexecute bit to 1, when you are ready to execute the
				code, or simply run the sqcript that is generated in the Messages pane.
WARNING: 	Make sure you verify that a trigger with the same name as the one being 
		generated  does not exists. IT WILL BE DROPPED with this code. ALSO, make 
		sure adding this trigger, will not cause issues with other existing 
		update trigers.
Author:     Mickey Stuewe, www.mickeystuewe.com
Date:       12/10/2013

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */
DECLARE 
	@ReadyToExecute AS bit = 1

DECLARE
	 @Tables AS nvarchar(max) = ''
	,@SchemaName AS nvarchar(50) = '<schema,string,>'
	,@TriggerName AS nvarchar(200)
	,@TableName AS nvarchar(126)
	,@SQL AS nvarchar(MAX)
	,@PrimaryKeys_t_to_d AS nvarchar(MAX)
	,@PrimaryKeys_d_to_i AS nvarchar(MAX)

	DECLARE Trig_Cursor CURSOR	
		FOR 
			WITH cte_Pieces
			AS 
			(
				SELECT
					 1 AS ID
					,1 AS StartString
					,CONVERT(int,CHARINDEX(',', @Tables)) AS StopString
				UNION ALL
				SELECT
					ID + 1 AS ID
					,StopString + 1 AS StartString
					,CONVERT(int,CHARINDEX(',', @Tables, StopString + 1)) AS StopString
				FROM
					cte_Pieces
				WHERE
					StopString > 0
			)
			,cte_Tables
			AS
			(
				SELECT
					SUBSTRING(@Tables, StartString, CASE WHEN StopString > 0 THEN StopString - StartString
																ELSE LEN(@Tables)
														   END) AS TableName
				FROM
					cte_Pieces
			)
			SELECT 
				TableName
				,'trg_' + TableName + '_u' AS TriggerName
			FROM 
				cte_Tables

	OPEN Trig_Cursor
	FETCH NEXT FROM Trig_Cursor
	INTO 
		 @TableName
		,@TriggerName
	WHILE @@FETCH_STATUS = 0
	BEGIN

		--Get all the primary keys
		SET @PrimaryKeys_t_to_d = NULL
		SET @PrimaryKeys_d_to_i = NULL

		SELECT
			@PrimaryKeys_t_to_d = ISNULL(@PrimaryKeys_t_to_d  + SPACE(36) + 'AND ','') +  't.' + KCU.COLUMN_NAME + ' = d.' + KCU.COLUMN_NAME + CHAR(13) + CHAR(10) 
			,@PrimaryKeys_d_to_i = ISNULL(@PrimaryKeys_d_to_i + SPACE(36) + 'AND ','') +  'd.' + KCU.COLUMN_NAME + ' = i.' + KCU.COLUMN_NAME + CHAR(13) + CHAR(10) 

		FROM
			INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
			JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON KCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
																				   AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
																				   AND KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
		WHERE
			tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
			AND KCU.TABLE_NAME =  @TableName
			AND KCU.CONSTRAINT_SCHEMA = @SchemaName
		ORDER BY 
			KCU.ORDINAL_POSITION ASC

		--Drop existing Trigger
		SET @SQL = ''
		SET @SQL = @SQL +  N'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[' + @SchemaName + '].[' +  @TriggerName +  ']''))' + CHAR(13) + CHAR(10)
		SET @SQL = @SQL +  N'DROP TRIGGER ' + @SchemaName + '.' +  @TriggerName	+ CHAR(13) + CHAR(10)

		--PRINT @SQL
		EXECUTE sp_executesql @SQL

		--CREATE New Trigger
		SET @SQL =		  N''																						
		SET @SQL = @SQL + N'/* ********************************************************************************'					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Template:	Created with template by Mickey Stuewe, www.mickeytsuewe.com'								+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Purpose:	Set ModifiedDTS and ModifiedBy columns.'					 								+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Notes:		ModifiedBy and ModifiedDTS will only changed if they were not changed'						+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'			by the user.'																				+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N''																										+ CHAR(13) + CHAR(10) 
		SET @SQL = @SQL + N'Author:		$USER$'  																					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Date:		$DATE$' 																					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N''																										+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Revision History'																						+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'(Change Date)	(Author)		(Description of Change)'												+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'-----------------------------------------------------------------'										+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N' *********************************************************************************/'					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'CREATE TRIGGER ' + @SchemaName + '.' +  @TriggerName +													+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	ON ' + @SchemaName + '.' +  @TableName																+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	AFTER UPDATE'																						+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'AS '																									+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'BEGIN'																									+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	SET NOCOUNT ON'																						+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	-- Update the ModifiedDTS/By fields if they are not passed in. '									+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	UPDATE '																							+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		' + @SchemaName + '.' +  @TableName + 															+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	SET '																								+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		 ModifiedBy = CASE '																			+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						WHEN i.ModifiedBy IS NULL THEN SUSER_NAME() '									+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						WHEN i.ModifiedBy <> d.ModifiedBy THEN i.ModifiedBy '							+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						WHEN ISNULL(i.ModifiedDTS,d.ModifiedDTS) = d.ModifiedDTS THEN SUSER_NAME() '	+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						ELSE i.ModifiedBy '																+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						END '																			+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		,ModifiedDTS = GETDATE() '																		+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	FROM '																								+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		' + @SchemaName + '.' +  @TableName + ' AS t '													+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		INNER JOIN deleted AS d ON ' + @PrimaryKeys_t_to_d												
		SET @SQL = @SQL + N'		INNER JOIN inserted AS i ON ' + @PrimaryKeys_d_to_i												+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	SET NOCOUNT OFF'																					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'END '																									+ CHAR(13) + CHAR(10)

		IF @ReadyToExecute = 0
		BEGIN
			SET @SQL = @SQL + N'GO '																									+ CHAR(13) + CHAR(10)

			PRINT @SQL
		END
		ELSE
		BEGIN
			EXECUTE sp_executesql @SQL
		END

		IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[' + @SchemaName + '].[' +  @TriggerName +  ']')) 
			PRINT '--CREATED TRIGGER ' + @SchemaName + '.' +  @TriggerName
		ELSE
			PRINT '--*** TRIGGER ' + @SchemaName + '.' +  @TriggerName + ' was not created.***'

		FETCH NEXT FROM Trig_Cursor
		INTO 
			@TableName
			,@TriggerName
	END
	CLOSE Trig_Cursor
	DEALLOCATE Trig_Cursor

GO

Here is an example of what the trigger will look like. This example has a three part primary key.

AdventCalendar2013Day10_Img1

Previous Post In Series: SQL Advent Calendar 2013 – Day 9 – Creating Change Tracking Fields (2 of 3)

SQL Advent Calendar 2013 – Day 9 – Creating Change Tracking Fields (2 of 3)

AdventCalendar09First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Second Step Is Creating The Change Tracking Fields

Today I’ll share with you my macro called Add Change Tracking Attributes. This script will add the attributes InsertedBy, InsertedDTS, ModifiedBy, and ModifiedDTS to the selected entities. These attributes are based on the domains that were created by the macro in yesterday’s post found here. If you add these fields, and then decide you don’t want them, you can use my macro called Remove Attribute From Selected Tables, which I blogged about here to remove them.

'----------------------------------------------------------------------------
' PURPOSE:  	This macro adds Change Tracking Attributes to Selected Entities.
' Author:   	Mickey Stuewe
' Date:     	12/9/2013
'----------------------------------------------------------------------------

Sub Main

	Dim objModel As Model
	Dim objSelObject As SelectedObject
	Dim objEntity As Entity
	Dim objAttribute As AttributeObj
	Dim iCounter As Integer
	Dim dict As Dictionary
	Dim iInsertedDateID As Integer
	Dim iInsertedByID As Integer
	Dim iModifiedByID As Integer
	Dim iModifiedDateID As Integer

	' Get the Local Data Dictionary.
	Set dict = DiagramManager.ActiveDiagram.Dictionary

    ' Determine Domains.
    Set MyDomain = dict.Domains.Item("InsertedDTS")
    iInsertedDateID = MyDomain.ID

    Set MyDomain = dict.Domains.Item("InsertedBy")
    iInsertedByID = MyDomain.ID

    Set MyDomain = dict.Domains.Item("ModifiedDTS")
    iModifiedDateID = MyDomain.ID

    Set MyDomain = dict.Domains.Item("ModifiedBy")
    iModifiedByID = MyDomain.ID

	Set objModel = DiagramManager.ActiveDiagram.ActiveModel

	' Iterate through all the selected objects in the current
	' model.
	For Each objSelObject In objModel.ActiveSubModel.SelectedObjects

		'Get the object type - we are only concerned
		'with entities.
		If objSelObject.Type = 1 Then

			' Get the actual entity object with this ID.
			' The model contains the collection of all the
			' entities.
			Set objEntity =  objModel.Entities.Item(objSelObject.ID)

			'Create Tracking fields and set DomainIDs
			Set objAttribute = objEntity.Attributes.Add("InsertedBy", False)
			 SetDefaultDomain objAttribute, iInsertedByID,objEntity.EntityName

			Set objAttribute = objEntity.Attributes.Add("InsertedDTS", False)
			SetDefaultDomain objAttribute, iInsertedDateID,objEntity.EntityName

			Set objAttribute = objEntity.Attributes.Add("ModifiedBy", False)
			SetDefaultDomain objAttribute, iModifiedByID,objEntity.EntityName

			Set objAttribute = objEntity.Attributes.Add("ModifiedDTS", False)
			SetDefaultDomain objAttribute, iModifiedDateID,objEntity.EntityName

			iCounter = iCounter + 1
		End If
	Next objSelObject

	MsgBox "Four new Attributes were added to " & iCounter & " Table(s).", vbOkOnly + vbInformation, "Attributes Added To Tables"

End Sub

Sub SetDefaultDomain(objAttribute As AttributeObj, iDomainID As Integer, strEntityName As String)

	objAttribute.DomainId = iDomainID

	If Len(objAttribute.DeclaredDefault) > 0 Then

		'if the attribute default is bound, then unbind it so that it can be named.
		If objAttribute.DomainId > 0 Then
			'You have to override the bound default value and the default text (name).
			objAttribute.EnableOverride (1,True)
			objAttribute.EnableOverride (2,True)

		End If

		If Len(objAttribute.DefaultName) = 0 Then
			objAttribute.DefaultName = "DF_" & strEntityName & "_" & objAttribute.AttributeName

		ElseIf objAttribute.DefaultName <> "DF_" & strEntityName & "_" & objAttribute.AttributeName Then
			If MsgBox("The name of the Default Constraint does not match our naming standards. Would you like to replace it?" & vbCrLf & _
				vbCrLf & vbCrLf & "Attribute: " & objAttribute.AttributeName & " - Default Name: " & objAttribute.DefaultName & " - Value: " & _
				objAttribute.DeclaredDefault & vbCrLf & " Standardized Name: " & "DF_" & strEntityName & "_" & objAttribute.AttributeName, _
				vbYesNo + vbQuestion, "Stadardize Name?") = vbYes Then
					objAttribute.DefaultName = "DF_" & strEntityName & "_" & objAttribute.AttributeName

			End If
		End If
	End If
End Sub

Take a look at lines 81 and 82. These two lines are needed to be able to give the default a name and here is why: This macro creates attributes based off of domains. The Inserted attributes have defaults. If we don’t name the defaults, then SQL Server will provide names for them…and they are not pretty. In order for us to be able to override the domain, we have to set the enableoverride property for two properties. Then we’ll be able to provide a name to the default. This step can’t be done in the domain, because defaults are a type of constraint and all constraints need to have unique names.

Tomorrow I’ll show you a Red Gate Snippet used to create the triggers for the tables with these fields.

Previous Post In Series: SQL Advent Calendar 2013-Day 8-Creating Change Tracking Domains (1 of 3)

SQL Advent Calendar 2013 – Day 8 – Creating Change Tracking Domains (1 of 3)

AdventCalendar08First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

First Step Is The Foundation

Whenever I need to have minimal auditing on a table, I add four fields to the table. InsertedBy, InsertedDTS, Modifiedby, and ModifiedDTS. The Inserted fields tell me who created the row. These two fields are updated based on the defaults set on the columns. The Modified fields tell me who changed the fields and are updated by a trigger.

Over the next couple of days I will show you two ER/Studio Data Architect (ER Studio DA) macros and one SQL Prompt Script that will help you create these four fields and the needed triggers.

The first step is making sure that your data model has four Domains. They reside in the Data Dictionary on the Data Dictionary tab. Domains are templates that fields can be created against.

There are two types of Data Dictionaries. There are local Data Dictionaries and Enterprise Data dictionaries. A data model can only have one local Data Dictionary and it always exists. If you are using ER Studio DA’s Repository feature, then you can create an Enterprise Data Dictionary to use across al Data Models. A Data Model can have multiple Enterprise Data Dictionaries. This is a great feature and I have used it in the past, but it isn’t available if you are not using the Repository.

 

AdventCalendar2013Day8_Img1

 

I’ve created a macro called Create Change Tracking Domains to use the local Data Dictionary.

The macro does the following: If a folder called Change Tracking doesn’t exist in the Data Dictionary, then it is created. If the four change tracking domains don’t exist, then they will be created. Either way, all the properties of these four domains will either be created or reset when this macro is run.

Note: You can modify it to use an Enterprise Data Dictionary, or you can see if I have time to add it to the macro for you.

Below, in the code. You can see the properties that are set for each field (Data type, name, definition, nullability, and default.)

'----------------------------------------------------------------------------
' PURPOSE:  	This macro adds four domains to aid in creating Change Tracking
'				fields.
' Author:   	Mickey Stuewe
' Date:     	12/9/2013
'----------------------------------------------------------------------------

Sub Main

	Dim dict As Dictionary

	' Get the Local Data Dictionary.
	Set dict = DiagramManager.ActiveDiagram.Dictionary

	'If the Change Tracking folder does not exist, then add it.
	If (dict.DomainFolders.Item("Change Tracking") Is Nothing) Then
		dict.DomainFolders.Add("Change Tracking","Domains")
	End If

    ' if Domain doesn't exist, then create it.
	If (dict.Domains.Item("InsertedDTS") Is Nothing) Then
		dict.Domains.Add("InsertedDTS", "InsertedDTS")
	End If

	With dict.Domains.Item("InsertedDTS")
		.AttributeName = "InsertedDTS"
		.Datatype="DateTime"
		.DeclaredDefault = "GETDATE()"
		.Definition = "This is the date the row that was inserted."
		.DomainFolder = "Change Tracking"
		.Nullable = False
	End With

    ' if Domain doesn't exist, then create it.
	If (dict.Domains.Item("InsertedBy") Is Nothing) Then
		dict.Domains.Add("InsertedBy", "InsertedBy")
	End If

	With dict.Domains.Item("InsertedBy")
		.AttributeName = "InsertedBy"
		.Datatype = "nvarchar"
		.DataLength = 255
		.DeclaredDefault = "SUSER_NAME()"
		.Definition = "This is the system user who created the row."
		.DomainFolder = "Change Tracking"
		.Nullable = False
	End With

	 ' if Domain doesn't exist, then create it.
	If (dict.Domains.Item("ModifiedDTS") Is Nothing) Then
		dict.Domains.Add("ModifiedDTS", "ModifiedDTS")
	End If

	With dict.Domains.Item("ModifiedDTS")
		.AttributeName = "ModifiedDTS"
		.Datatype="DateTime"
		.Definition = "This is the date the row was modified. It is updated via a trigger."
		.DomainFolder = "Change Tracking"
		.Nullable = True
	End With

 	' if Domain doesn't exist, then create it.
	If (dict.Domains.Item("ModifiedBy") Is Nothing) Then
		dict.Domains.Add("ModifiedBy", "ModifiedBy")
	End If

	With dict.Domains.Item("ModifiedBy")
		.AttributeName = "ModifiedBy"
		.Datatype = "nvarchar"
		.DataLength = 255
		.Definition = "This is the system user who modified the row. It is updated via a trigger."
		.DomainFolder = "Change Tracking"
		.Nullable = True
	End With

	Set dict = Nothing
End Sub

Tomorrow I’ll share the macro that will add these four fields to selected tables.

Previous Post In Series: SQL Advent Calendar – Day 7 – Verify All Tables and Fields Have a Definition

Next Post in Series: SQL Advent Calendar 2013 – Day 9 – Creating Change Tracking Fields (2 of 3)

%d bloggers like this: