Friday, April 20, 2018

Using Transactions

I wanted to jot some notes down about using transactions and @@trancount - mostly, I personally like checking for if I should begin a transaction or not at the beginning of stored procedures.  I know this comes from me being a software developer and thinking the database guy/gal handles all that, and then learning more about the database side of things and realizing database coding isnt always reviewed that closely by a DBA.  So, it came time for me to learn.  Now that I switched jobs, I dont have all the code blocks to copy/paste - so this blog post will help with that! :)

Lets start with the basics:

A transaction is a sequence of operations performed in it's own "bubble" from other processes and/or transactions occuring.  Once a transaction is completed, the effects are permanent in the database.

Now, that is a simple explanation, as with everything, there are many things that go into it - such as how transactions affect each other because the data that is modified in 1 transaction is the same data needed for another transaction, so the "bubble" a transaction is in isnt completely isolated - or how if a transaction is rolled back, there is no permanent effect because nothing was done, etc.  I am going to talk about some of the ways of starting, monitoring, and ending a transaction, as well as nested transactions, and possibly uncommitable transactions.

To start a transaction, the syntax is BEGIN TRANSACTION (or BEGIN TRAN).  No matter what, this is pretty much straight forward.  To end a transaction, you would either use COMMIT TRANSACTION, or ROLLBACK TRANSACTION, depending on if you want to make the changes you made, or not make the desired changes, respectively.  That, in and of itself, is all you need to start utilizing transactions now - but I will keep going with more juicy info. :)

Read more: Using Transactions

Write comment (0 Comments)

T-SQL Random Number Generator

I recently needed to generate a random number.  I know using rand() was the way to go, but it wsnt giving me the formatting I needed.  I went online (lazy, cause I know I could format a stupid number) and found an amazing little script that I expanded upon.  It gives you a min and max range which was perfect!

Here is the code and a link to the site I got it from (giving props to Michelle at for it!)

Declare @maxRandomValue tinyint = 100
	, @minRandomValue tinyint = 0;
Select Cast(((@maxRandomValue + 1) - @minRandomValue) 
	* Rand() + @minRandomValue As tinyint) As 'randomNumber';

Write comment (0 Comments)


I was wary of XML when I first heard about it - mostly because I was still trying to wrap my head around other stuff that I was learning.  Therefore, I didnt learn much about it at first.  Then, I needed to pass multiple values to a stored procedure and looked into it.  I quickly fell in love!!  There are many posts about using XML within T-SQL.  I am posting one so that I can search on my own blog for this when I need it - I dont seem to keep it in my head long enough to use it the next time I need it. :)

I am going to use examples from my alerter system, that is where I am using it as I am writing this article - so 2 birds with 1 stone - cant beat that!  By the way, I will format the SQL code better - it looks weird being all gray!

Read more: T-SQL and XML

Write comment (0 Comments)

T-SQL Database Alert/Mail System

I am working on a T-SQL "system" to send emails.  This will help so that in all the things that I use to send update emails, (ETL, sql jobs, etc.), I can just write a record in a table somewhere and the email will be handled for me.  Some benefits:

  • I can have the templates and recipients pre-defined so I dont have to worry about what to say. 
  • I dont have to worry about updating my SSIS packages or sql jobs when the text or recipients of the notification email needs to be changed. 
  • A history of the emails sent, to whom, and when (purely for taking up space, I am sure noone would doubt me if I said an email was for sure sent even though they didnt get it).
  • A queue that will allow the emails to be sent even if services are down (after they are recovered).

Read more: T-SQL Database Alert/Mail System

Write comment (0 Comments)

SSIS connecting to Oracle

Ohh boy - this is a biggie for me right now (actually it has been for a couple of weeks, but I have successfully put it off until today). :)

I have a situation where there are already SSIS packages, and sql statements that pull from an Oracle database into our SQL Server database.  The person who set them up left the company (a positive for him).  Therefore, I was scrambling when I started getting all these error messages that were basically laughing at me!

These are the steps I have had to do:

Read more: SSIS connecting to Oracle

Write comment (0 Comments)

Versions of SQL Server

Ever needed to know what version you are running - and what service packs were installed?  I found the following when I was searching for this exact question for myself.  It was very helpful.  I printed it out, so if it is ever is a url that is not found, I can put the data here instead.

Write comment (0 Comments)

SSIS - Same recordset into multiple tables

Are you wanting to put a set of data into multiple tables - without needing a primary key from the first table?  I ran into a situation where I had records that I wanted to insert into a table, but I wanted to be able to email people saying "hey look you need to do this, this and this because there are new records to look at!".  So, I wanted to use the same exact set of data and put it in 2 tables (1 as the actual source, the other to use as a mail queue).

Voila - Multicast!  It allows you to use the same recordset in multiple destinations.  Wonderful!

Hopefully this helps someone else who runs into this issue.


Write comment (0 Comments)


Powered by mod LCA