Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

All Blogs

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Your profile

    Search

    XML Feeds

    Google Ads

    Tags: trick

    comments

    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…

    Read More...
    comments

    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...

    Read More...
    comments

    How to clean a table from badly named column names

    by SQLDenis on Jun 11, 2010 in categories Data Modelling & Design. Article views: 1307 views

    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...

    Read More...
    comments

    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…

    Read More...
    comments

    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...

    Read More...
    comments

    Can't depend on sp_depends? Try using sp_refreshsqlmodule

    by SQLDenis on Jun 26, 2009 in categories Data Modelling & Design. Article views: 4397 views

    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...

    Read More...
    comments

    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…

    Read More...
    comments

    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

    tsql Sample Code (See Article for Rest)
    Read More...
    comments

    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

    tsql Sample Code (See Article for Rest)
    Read More...
    comments

    What is deferred name resolution and why do you need to care?

    by SQLDenis on Sep 08, 2008 in categories Data Modelling & Design. Article views: 3826 views

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

    tsql Sample Code (See Article for Rest)
    Read More...

    :: Next >>