Archive for Red Gate

Catching Up With Mickey

IMG_0555I can’t believe the year is almost half way through. I keep trying to slow the days down, but it just isn’t working. This year I’ve already accomplished so much, and I still have a long list before the year ends. Here is a recap and some events to look forward too!

January

I started the year off with a bang by starting a brand new Business Intelligence chapter in Irvine called Business Intelligence Group, A PASS Community (AKA BIG PASS Community). We consistently have 15 people every month and I’m really happy to announce that I have speakers lined up for the rest of the year! (Yippee!)

I also had the opportunity to participate in Pragmatic Work’s Training on the T’s. This is a free webinar series they have every Tuesday and Thursday.  I was able to present my Scalable SSRS Reports Achieved Through the Powerful Tablix presentation. You can still go to their website and view it.

I also had the honor of presenting remotely to the LA SQL UG for their 10th anniversary!

February

This month was spent writing abstracts for the year…and still understanding my new user group. I was also being courted for what became my new job. You can read about it here.

March

March was extra special. I had the opportunity to present at the Silicon Valley SQL Saturday. It was extra special, because it marked my 1 year anniversary for speaking in the SQL community. I also had my largest class to date! 97 people! Here was my favorite tweet of the day too. (Thanks Glenn!)

April

This month was full of meetings for our local Huntington Beach SQL Saturday that I helped host at the end of April. It was great having SQL Family come out to my neck of the woods beach.

May

I didn’t speak anywhere this month, but I did spend time every weekend writing. (Actually, I write every month.) I really enjoy participating in the T-SQL Tuesday Blog Parties, writing for myself, and participating in #SQLCoOp with my friends Julie, Chris, and Jeffrey.

June

And here we are in June, where I decided I would do EVERYTHING. I’m writing, speaking, leading, writing, and participating in #SQLHangout. Oh, and I’m getting my first dog. (More on her in a moment.)

My friend Boris Hristov (b|t|f), from Bulgaria, invited me to participate in an “episode” of SQL Hangout. We hung out in our two countries with 10 time zones between us and chatted about data types. You might not think this is an exciting topic, but it is a cornerstone to all databases. We came up with some great reasons why all database professionals should care about the data types of every field in their tables. So grab some popcorn or a glass of whiskey and hang out with us for half an hour.

 

You can find out about up and coming SQL Hangouts by following #SQLHangout on twitter, and you can find the full list of recorded SQL Hangouts here.

This month, I’ve also been blessed with a co-leader for my (now our) BI user group. His name is Rob Hatton, and I’m really happy he asked to lead the group with me.

I also had the opportunity this month to drive out to Riverside to speak with the Inland Empire User Group. This is the third time they’ve had me present, but the first time I’ve actually presented in person. Riverside is not a quick drive from where I live, but my boss, Steven was happy to be a carpool buddy for me. It ended up being a perfect presentation for him to hear, since it was on source controlling your SQL scripts with Red Gates’ SQL Source Control.

Now we get to look into the future…

2014-06-15 22.26.14Well, not to far into the future. Tomorrow (Wednesday) I’m heading out to Kentucky for a week. One of the events on my vacation will be speaking at SQL Saturday #286, Louisville. I’m really looking forward to the event since I enjoyed it so much last year. My husband and I are also going whiskey tasting with friends, we’ll hopefully be visiting the Corvette factory, and we’ll be picking up this adorable Labradoodle puppy who we’ve named Lucy. She will be 10 weeks old, and I can’t wait to hold her.

Here is a list of other events that I’ll be speaking at this year. You can also go to my 2014 Speaking Engagements page for an updated list through out the rest of the year.

I’ve applied to a few other events, but the accepted speaker lists have not been sent out for those events yet.

I’ll also be attending PASS Summit 2014 in Seattle in Nov this year. I hope to see all of you there.

T-SQL Tuesday #54 – How LinkedIn and Red Gate Landed Me My Job

My friend Boris Hristov (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. This month’s invitation is on the topic of interviewing and hiring. This happens to be an apropos topic for me, since I have a new job.

It was a dark and stormy night…

OK, it was 8:30 am on a Monday morning and it was a beautiful January morning. It is Southern California after all…

I was checking my email like I always do after I get my morning coffee. There was yet another LinkedIn request. I’ve gotten to the point in my career where I’m picky about who I connect with. Basically, I always keep my LinkedIn account up to date, I’ve stopped connecting with people whom I couldn’t possibly help because our professions are so different, and I don’t connect with recruiters unless they are on my good side. This particular LinkedIn request was from a CEO named Richard at a company called DeskSite. Little did I know it would change my professional life.

A String of Events

Let’s step back a few days. I was already at a relatively new job. I had been there for six months, but I had also been frustrated. It was not the position I was expecting. I decided to pray about it the whole weekend and figure out if I wanted to start interviewing again or if I wanted to stick it out for another six months. By Monday I had decided to start looking.

So, we’re back to the LinkedIn request sitting in my email box on a Monday morning asking if I want to connect. Normally I would have said no. “He’s a CEO. He’s not a SQL person. We have no common connection.”, I would tell myself. But I was in a good mood. I thought, “Sure. Why not.”

Within an hour, I had an email from Richard, and it said:
Hey, we’re looking for a Database Architect. I was wondering if you could spend 10 minutes on the phone with me to see if you could help us find one.

I happen to be a chapter leader of a local BI user group called BIG PASS Community. While our group is BI focused, I know we have database professionals that cover the board, so I agreed to speak with him to see if I could find a match.

Side Story: I went down to my car to speak with Richard so that my colleagues wouldn’t know. I didn’t want them to think I was looking for a new job. Unfortunately, I have a blue tooth speaker at my desk and it was close enough to my car that my phone started transmitting through it. (Face Palm) So, I had to move my car. Problem solved.

Richard painted a picture for me of his startup company, DeskSite. He then told me where it currently stood technically and where he wanted to take it over the next three years. He then asked if I knew anyone who might be interested in joining his team or if he could possibly lure me away from my current company.

I kind of stumbled over my words, “I…I’m available. I just decided…literally, this weekend to start looking for a new position.” I cannot tell you how happy I made him. By time we got off the phone, DSC_3187I had an in-person interview scheduled for the next night at their office.

Side Story: When Richard called to verify that I could still make it, he told me that he had seen my SQL earrings that I wore in one of my Avatars. He loved them! So I wore them to the interview.

When I arrived at their office, I was greeted with a lot of enthusiasm. They were so happy to meet me. You see, they had Googled me. They knew that I’m heavily involved in the PASS Community. They had even seen the YouTube video that Red Gate published of me speaking at one of their SQL in the City events in 2013. They absolutely loved my enthusiasm and my obsession with SQL. They had already decided they wanted me on their team. They just had to convince me that I wanted to be on their team.  (You see, I was at a startup when the dot com bubble burst. It makes me leery of startups.)

My half hour interview ended up being three hours. At some point I was offered a job. Richard then wanted to know what it would take to have me on his team. Normally I don’t bring up the fact I like to speak and attend conferences in the first interview, but I’m also not normally sought after. So I asked if they would send me to conferences. Richard didn’t even blink. He gave me an allotment of days AND a budget. Wow. I did tell him I needed it in writing. I learned the hard way by taking something like that on good faith.

I was not prepared to have left with a verbal offer in hand. I was definitely thinking this offer was too good to be true. So like a good data professional, I started researching.

Over the next week or so, I asked many of my SQL friends what they thought. I sent Richard quite a few questions about the position, the company, the stock options, the offer, and even the culture of the company. (If you have never worked at a startup, they are VERY different than a mid-size or larger company.) I also made two more visits to their office.

The first trip was at lunch. You see, Richard’s ideal company is more like a family and families eat together. For those of you who don’t know me, I’m a people person. I despise eating by myself. It depresses me. Now that I have a Kindle, I have gotten more used to it, but I still prefer to eat with people. The bottom line is, I loved the culture of the company and Richard was one step closer to getting me on his team.

The second trip was a technical trip. They wanted me to meet with one of the consultants they use, to make sure I knew what I said I knew, and to talk deeper about the technical environment I would be working in. This one hour interview ended up being three hours. I think the technical part was only an hour, the other two hours was about the company… And my acceptance of their offer. (Aaacckkkk!)

Side note. I don’t do anything without talking to my awesome husband. I did take a bathroom break and talked to him on the phone for a bit and he was completely supportive of me taking the position.

The money dance

This is the part that has always been hard for me. Making sure I get paid fairly. I was asked what I wanted for a salary. I spent several hours researching what my salary should be. I have a wonderful friend who I got to talk real numbers with. I knew that I had been undervalued 2 jobs ago, but that had to do with the growth I experienced at that company. I grew professionally so fast when I first got involved with PASS that my salary soon became disproportionate to my knowledge, but because of red tape, my salary could not be fixed.

Anyhow, I finally came up with a number and sent it in. They made me an offer based on that number. It was made up of cash and options in the company. Unfortunately the cash portion was much too low. I was crest fallen. I know I could make a lot of money when the company goes public, and I really do think it will, but I have a daughter who will be heading to college in two years. I can’t risk her education or her younger sister’s education.

So what did I do? I talked to my friend who was also crest fallen for me. He offered some great advice and helped me devise a counter offer. I thought for sure it wouldn’t happen.  A week went by with no word. I prepared to start interviewing with other companies.

Then the clouds broke

I then received an email apologizing for the delay. They were in the processes of acquiring a larger office space do to the growth of the company and it had taken up much of their time. They really wanted me on their team. They understood my financial needs, but they had to discuss my counter offer.

In the end we came to an agreement and I became a member of an amazing team. I have been at DeskSite for a month and a half as of this writing. I’m very excited about the challenges ahead of me and I’m happy about being part of an amazing team.

So you see, I owe my awesome job to LinkedIn and Red Gate. I suppose Google should be added to the list since it was used to find my SQL in The City video on YouTube. So, YouTube should be added to the list as well……………

Thanks for all the fish

Thanks go out to Boris Hristov for hosting this month’s T-SQL Tuesday blog party. I always love and appreciate Boris’ enthusiasm about participating in T-SQL Tuesday, so please visit his website at http://borishristov.com/.

New and Improved SQL Search By Red Gate

MCJJ_ALL

The Third Mission

In this installment of SQLCoOp, we are sharing our views on the newly released SQL Search 2.0 product by Red Gate. This amazing product is free to the public and can be downloaded here.

 


 

SQL Search is one of those amazing products that you just can’t believe they give away. While I always find it useful, I found it the most useful this year when I was getting to know a new (to me) database. Why? Because I could quickly and easily search all the databases for particular keywords. Below I’ve included some of my favorite Use Cases for using SQL Search.

Use Case 1: Where did I leave my TODO list?

One of the things I love to do is leave notes for myself in my SQL objects, but I’m not always good at remembering where those notes are. With SQL Search, I can find them easily by searching for the keyword TODO. Now I can go a step further and look for all the TODO’s that ALSO have my name.

 

I do this by using the AND operator to search for the two keywords todo and mstuewe. There are some special rules to note.

1. AND and it’s counter part OR are both case sensitive.

2. You can only use one at a time. I can have todo AND mtuewe AND 2014, but  I can’t have todo AND (mstuewe OR mickey). (Maybe in a future version – hint, hint.)

 

SNAGHTML341ae0

Use Case 2: How many databases do I need to look through?

George is new to the company and he needs to get to know how orders are stored in the database. He decides to use SQL Search to look at all the tables that have Order in their name. He is a bit shocked when he find out that there are hundreds of databases on the server and he is not sure which ones he should start with. George, luckily got some help from one of his co-workers Sarah. She told him he should start with three specific databases.

 

In previous versions of SQL Search, you could look through one database or all the databases. This could lead to a very long list of values if you have quite a few databases on your server. This is now a problem of the past. (And there was much rejoicing.)  Now you have the option of selecting only the database you are interested. You can even select the system tables which are all listed at the bottom  of the pull down window.

 

image

 

Use Case 3: Rolling the dice for a job.

imageLiz needs to find all the SQL objects that use the SalesOrder  table, but she isn’t interested in searching through tables and views. She does, however want to search through all the jobs first, and then she’ll take a look at triggers, stored procedures and functions.

 

I found this next new feature a huge bonus. Jobs are not something I’m looking through often, but now I have a way to search through them without any trouble. Simply, select Jobs.

 

I’m also very happy to be able to select ONLY the database objects I want to search through. For me this means I can ignore tables and views. (Yippee!)

Use Case 4: Getting to know your neighbors.

There have been some implicit conversion problems with some of the date fields in the database. Sarah has been tasked to verify that all fields that end in “DTS” are actually datetime data types and not date data types.

 

Red Gate has added the ability to see table definitions in the results pane. It’s a simple view of the table structure, but it beats having to go back to the Object Explorer to see the entire table structure. Now when you’re researching field data types, you can do it quickly and efficiently.

 

image

Bonus Tips

Here are a few bonus tips that make SQL Search even more useful:

1. You can highlight all the rows in the grid, copy it, and paste it directly into Excel for further reference. This can be very helpful if you are changing all the objects in the list and you want to keep track of the original list. (This isn’t a new feature, but it is an awesome one to know about.)

2. They have improved the indexing of the database objects. This is GREAT  news. In the past, if you were expecting a modified object to appear on the list and it didn’t, you would have to close SSMS and restart it in order for the SQL objects to be properly re-indexed. You no longer need to do that. Just go to the Index menu item, and select Reindex.

3. Each of the columns in the grid can be sorted. This can be very handy if you want to deal with all the tables at one time, or a particular schema first.

 

image

Don’t Stop Yet

If you want to read more about the newly released SQL Search, don’t forget to check out these blog posts:

 

See you next time!!

 

On a SQL Collaboration Quest

Four 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. Along the way, they will also include other SQL professionals to join in the collaboration.

 

Original Post: On a SQL Collaboration Quest

On a SQL Quest Using SQL Search by Red Gate

OLYMPUS DIGITAL CAMERA

Four 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. Along the way, they will also include other SQL professionals to join in the collaboration.

Original Post: On a SQL Collaboration Quest 

The First Mission

For our first collaborative entry in this blog series, we have chosen to investigate Red Gate’s FREE tool, SQL Search. The question we posed to each other was: What is the purpose of Red Gate’s SQL Search and how do you use it in your daily work life?

The Search Begins

Right now I’m in retro-fit mode at work. I have an application that I recently inherited that needs a new feature. This requires me to add new columns to tables in the database(s), as well as find all the objects (Stored procedures, triggers, etc.) that reference the tables I need to modify to make sure they will still work and to add logic to support the new feature.

Red Gate has a whole suite of tools for me to use on my current adventure at work, but the tool I used the most this past week was their FREE tool, SQL Search. This tool will work with SQL 2005 and up.

Instead of writing about how to use SQL Search, I want to highlight some shortcut keystrokes that will improve the speed of using this awesome tool. Did I mention it is a FREE awesome tool?

The Task

I have a set of tables that I modified and I need to look at the code for all the objects that reference that set of tables to not only make sure they still worked properly, but to add code to support the new feature that was added. I’ll use the Adventureworks2008R2 database in my example below.

After I found the list of objects I needed to inspect, I did the following:

  1. Scripted out the object as an ALTER statement.
  2. Quickly searched for the table in the script (some of the scripts were 1000+ rows long).

Here is my favorite part

If you double-click one of the objects in the SQL Search list of found objects, that object will be found and selected in the in the Object Explorer. Unfortunately, if that object is a view, it takes several mouse clicks to get an ALTER script created, soooo

With the help of Mark Holmes (b|t), we figured out what the hot key combination is to script out the highlighted object as an ALTER statement. You ready? SHIFT  F10 + S+A+N. Yes, that is a lot of keys, but if you are like me, keystrokes are faster than mouse clicks.

Note: I remember it as SHIFT F10 + SAN

The CTRL + F shortcut will take you into the Find dialog box where you can search for the name within the script. If you had been searching  for the same value prior to this script, then F3 is your best friend and it will take you directly to the search term in the ALTER script.

But wait! There’s more

If you are a Red Gate junkie like myself and have SQL Prompt installed, then there is another shortcut you can add between SHIFT + F10 + SAN and F3.  If you use CTRL K + CTRL Y then you can have the alter script formatted to your taste. (aka, you can read it faster. Win!)

Note: I remember it as CTRL + KY

image

Scenario Time

So here is what it would look like if you were using the Adventureworks2008R2 database and looking for the Person table.

  1. Enter Person into the search field
  2. Select the “Exact Match” option. (Optional)
  3. Select the “All Objects” from the list of objects
  4. Select “All Databases” from the list of databases (The search term is automatically searched. Hitting ENTER is not required.)
  5. Click on one of the objects listed
  6. SHIFT  F10 + S + A + N to create an ALTER script
  7. CTRL K + CTRL Y to format it (this step requires SQL Prompt)
  8. CTRL  F to pull up the SSMS search window to search within the script
  9. Type in Person and click enter. You’ll be taken to the first instance of Person
  10. Use F3 to find the other instances of Person in the script

Now to look at a second script. You already have the list of objects, so you don’t have to search for them again.

  1. Click on one of the objects listed.
  2. SHIFT  F10 + S + A + N to create an ALTER script
  3. CTRL K + CTRL Y to format it (this step requires SQL Prompt)
  4. Use F3 to find the other instances of Person in the script

Repeat these four steps for each script you need to look at. Win!

Don’t Stop Yet

If you want to read more about SQL Search, don’t forget to check out these blog posts:

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

Tribal Award Winner for Best New Community Voice

2013 was an amazing year for me and winning this award was the cherry on top. I want to not only thank the people who voted for me, but for the people who nominated me.

Thank You!

I appreciate your belief in me and for accepting me into the SQL Family.

While we can’t all clap together, we can tweet our approval. Please help me recognize the four others who were nominated with me. It’s a big planet, and they have contributed in their countries and abroad to be noticed and nominated too.

  • Koen Verbeeck (b|t) from Belgium
  • William Durkin (b|t) from Germany
  • Rob Sewell (b|t) from United Kingdom
  • Mark S. Rasmussen (b|t) from Denmark

Very Sincerely,
Mickey Stuewe

Tribal Awards – Nominated For Best New Community Voice

In December Red Gate introduced the Tribal Awards. Some of the awards are fun like “SQL Karaoke Performer of the Year” and others are more serious like “Blog of the Year”. They collected nominations and I found out that I was selected for “Best New Community Voice”. I’m very honored to be included with the other four gentlemen… all from Europe.

  • Koen Verbeek from Belgium
  • William Durkin from Germany
  • Rob Sewell from United Kingdom
  • Mark S. Rasmussen from Denmark

Now that the nominees have been selected, the community can now vote for the winners of the Tribal Awards, but be quick. The voting ends on January 3rd with the winners being announced on January 7th.

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 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

%d bloggers like this: