Category Archives: SQL
Microsoft’s Channel 9 Visual Studio Toolbox talks about bringing DevOps practices to the database.
Database DevOps with Redgate Data Tools
Colors Are Spice For Your Eyes
One of the definitions of color at Dictionary.com 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.
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.
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]
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).
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.
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.
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.
Ever start up SSMS (SQL Server Management Studio) and not have your tabs for each new query window? Don’t panic, it is an easy fix.
- Go to Tools. In that drop down menu click on Options.
- In your options window you will want to expand the plus sign next to Environment if it is not already expanded.
- Select General. Please note, this should be the same as just selecting Environment, but I’m just going the long route.
- Now you should see some options on the right side of the option window. Look under Environment Layout. You will see two options.
- Select Tabbed documents.
- Click the OK button.
Now your tabs have been restored. Of course to remove tabs just select the MDI environment instead of Tabbed documents.
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 HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC