From the BlogSubscribe Now

RDS GRANT ALL PRIVILEGES

Due to the way that Amazon RDS is setup, it doesn’t allow you to GRANT ALL PRIVILEGES ON *.*

Here is a workaround to get the same permissions:

 

A Better Git Log

I’ve always been kind of frustrated with the standard implementation of the git log.  It seems like I always find myself using third party tools when wanting to view the history in git.

This is an alias that you can use to create a pretty git log using “git prettylog”, it shows you commit messages, user, and even branches and merges.

Force MySQL Group By to function like SQL Server

Note: MySQL has now made this enabled by default starting in MySQL 5.7.5! Should’ve happened a really long time ago, but hey, better late than never!

MySQL’s group by functionality allows you to not group by all relevant columns. If you’re not careful and if you’re used to how SQL Server or Oracle work, this can bite you. To turn on “ONLY_FULL_GROUP_BY”, you simply input the following command in the mysql command line:

mysql>SET sql_mode = 'ONLY_FULL_GROUP_BY'

Preview Inserts, Updates, Deletes, and Merges in SQL Server

With the complex and convoluted database structures out there in the wild in corporate America, often times you run into statements that could scare you half to death. This is especially true when dealing with Inserts, Updates, Deletes and Merges.

Here is a quick and simple way to test the outcome of your changes before actually executing the change.

The important parts are:

BEGIN TRANSACTION – Starts the transaction.

OUTPUT inserted.* – Outputs the result of the change to the results window.

ROLLBACK TRANSACTION – Reverses the effects of the change, leaving the data in the same state it started in.

Example of Transactions in SQL Server

Here is a quick example of a transaction in SQL Server. This is done so that if there is an error, it will undo any changes that it made.
You’ll notice that I’m setting XACT_ABORT to ‘ON’. When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

There is also a Try-Catch block, when an error occurs within the try block, it will automatically move to the catch block and execute whatever is in that block. In this case, I’m checking the @@TRANCOUNT, which increments when you begin a transaction (signaling that a change is taking place). If the @@TRANCOUNT is greater than one, it means that the statement has at least tried to execute the statement in the TRY block, so I’m rolling back the transaction if this occurs.

Sending SMTP Email in C#

If you’re needing to connect to an SMTP server from within your C# to send emails, here is some example code of how to do that. This example sends an email with a display name that is passed to the method, technically if you wanted you could also pass the from address to the constructor and be able to modify the from address on the fly as well.

Using OUTPUT Clause to return inserted identity value (or any inserted value) in SQL Server

I recently came across a bug in SQL Server 2008 that can cause the SCOPE_IDENTITY() Function to return an incorrect value for the recently inserted row if the query has a parallel execution plan.  While SCOPE_IDENTITY() is generally a pretty safe way to go, it is not fool-proof.  The surefire way to get the correct value is to use an output clause to return the value needed instead. Here is an example of using the OUTPUT clause to get the (in this case auto-incremented) identity value of the recently inserted row:

This should be the standard going forward and is much better than Selecting SCOPE_IDENTITY() or @@IDENTITY at the end of the query to get your recently inserted row’s identity column.

It should also be noted that you can return more than just the identity column, you can return as many inserted columns as you want to and it will return the values:

This will return the identity and the timestamp that were just inserted:

output clause values

So you can then retrieve and use those values how you normally would if you were using a select statement.