A couple of months back we were interviewing people for 2 positions, one of the questions I like to ask is the following: If you have a column in a table that's an integer data type how can you restrict the values to be between 1 and 10? Most of the peo…

A person asked the following question:

I am running the following command:

EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline

On the Results tab I get 2 lines Could not find a part of the path '\server\directory\filename'. NULL

How do I capture the first line in an error message? I tried using a Try Catch block with "SELECT @ErrorMessage = ERROR_MESSAGE()" and it doesn't grab it.

The message is not coming from sys.messages. Where is this error message coming from then?

First of all that message comes from the Command S...

Someone gives you a backup of a database, you restore it and the first thing you notice is that the column names have percent signs and underscores in them.

It would be easy to fix this if it was one table but in this case there are hundreds of tables.

The solution is to loop over information_schema.columns, find all the columns that have those characters and then rename those columns by using the sp_rename procedure. I will show you two ways to do this, one way that executes the code and one way that generates a script that you then can execute.

E...

This question came up again yesterday in our SQL Server forum so I decided to create a short blog post about it. The version of SQL Server Management Studio that ships with SQL Server2008 comes with IntelliSense enabled, I still think IntelliSense is s…

This was asked on twitter the other day and I emailed the person the solution to this. The solution uses dynamic management views and it is not perfect because of a couple of reasons.

1) The dynamic management views don't keep this information forever, restart the server and your data is gone

2) If your table name is in a comment it will be picked up by this query

3) If the table name is part of another object it will also be picked up, for example if you have a table name customer and a view name customers then it will return a row if customers was part of the query but y...

This will not work on SQL Server 2000 since the sp_refreshsqlmodule does not exists on that version!

A while back in the What is deferred name resolution and why do you need to care? blogpost I showed you that sp_depens is not reliable because you can create procedures that reference objects that have not been created yet.

You can use sp_refreshsqlmodule to help 'fix' that

let's take a look at how that works

First create this awesome stored procedur...

Take a look at this code create table Foo2(id int, id2 int constraint DefaultID2 default 1) As you can see it is a simple table with 2 columns, the second column has a constraint on it named DefaultID2. We can verify that the table has a default…

String or binary data would be truncated. How can I suppress that message?

This is a frequent enough question on various forums, I answered this one also today and decided to create a quick blog post about this.

Run this code

sp_refreshview or how to make sure that the view will have the underlying table changes

Got a question about this on our Microsoft SQL Server Programming Forum so you know it is time for a quick blog post.

Did you know that when you create a view and then later change the table the view is not updated?

let me show you what I mean.

Run the following block of code

So I posted a teaser in the puzzles forum. Without running this, try to guess what will happen?