Blog Archives

Add Some Color to Your SSMS

Colors Are Spice For Your Eyes

Graveyard Trail

One of the definitions of color at is

the quality of an object or substance with respect to light reflected by the object, usually determined visually by measurement or hue, saturation, and brightness of the reflected light; saturation or chroma; hue.

That is quite a mouthful for something we can just look at and instantly appreciate.  Colors are a natural occurrence.  Colors can spice up a day in the mountains making it more enjoyable compared to the bland look of winter or the constant lush green of summer.

Read the rest of this entry

SQL Stored Proc With Dynamic Where

A Learning Opportunity

Today I had a lovely learning opportunity when I discovered a stored procedure that was not quite safe.  It used parameters, and the C# code sent them over using proper parameters.  The problem came to how the entire SQL statement was being built and executed.

In fact, you could say it was more about how the dynamic WHERE clause was being built.

Naturally I went on a quick quest to find a better way to do this and be secure.  This MSDN article really helped.  It gave me enough knowledge to write an example against a Northwind database.

Read the rest of this entry

Automatically Insert A DateTime Stamp On Insert

Here is one I don’t use often and sometimes forget.  So I’ll put it out here.

Normally I do my inserts via .NET or a stored proc and so I do my datetime stamps that way.  What if I do not want to do that, surely there is a way to do it inside the table.  Well, you are right and it is really very easy.  In your table design simply set the default value to (getdate()).

I normally use the visual way of creating my tables so I’ll include a screen shot below.  To show how it is done in code I will paste that after the image.

ALTER TABLE [dbo].[tblMyTable] ADD  CONSTRAINT [DF_tblMyTable_dtImported]  DEFAULT (getdate()) FOR [dtImported]

Creating A Unique Constraint

I forgot all about creating UNIQUE constraints.  Of course I would have a need for it, and you know it goes into the eBrain.

Basically there are two ways to create a UNIQUE constraint.  You can do it via TSQL, or using the SQL Server Management Studio (SSMS).

Read the rest of this entry

Cannot Save Changes That Require Recreate

I know we have all had this at least once. We tend to fix it and forget it. I had it today at work and had to look it up. We all know what that means, its going in my e-Brain.

This is for when you design a table in SSMS (SQL Server Management Studio) and when you try to save the changes you cannot. You get a message like the one below.

Read the rest of this entry

Fixing Orphaned Users In SQL 2008 R2

Here is one that happens to all of us once in a while, and we may tend to forget how to resolve it. I had a database copied from one server to another. I really did not want to have to go through and reset permissions on all the stored procedures, so I needed to save that orphaned user that was listed in the new database’s User list.

The fix is really easy. First I created the user on the server. I did not select any user mappings and left the roles public. To keep things simple I named it the same as the user name in the database; for this example I will call it MySQLUser. Next I ran the magic SQL command.

sp_change_users_login @Action='update_one', @UserNamePattern='MySQLUser', @LoginName='MySQLUser';

If you want more detail about troubleshooting orphaned users you should go to this MSDN page.

SQL Server Date Formats

I found this information several years ago and saved it to a wiki at work.  I thought I would put it out here to share with others and for me to access when away from the office.

I must apologize for not remembering where I found this information.

Read the rest of this entry

Counting Duplicate Records

We all need to do it once in a while, and for me it takes a little time to remember the best way to do it.  I found a post by Pinal Dave that gives a great example.

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn