Development – Leet 4 Life

Software Development Code Samples and More

Skip to: Content | Sidebar | Footer

Category: T-SQL

Enable CLR Sprocs in MSSQL

31 October, 2012 | SQL Server, T-SQL | By: Michael Adams

Recently I inherited a project where the developer was using CLR stored procedures in Microsoft SQL Server. I found out that you must execute the following SQL Script to be able to use those stored procedures. This statement simply enables Common Language Runtime integration feature for the entire SQL instance that you are currently on. The CLR Integration feature is off on a new SQL Server instance by default so in order to use it you must run this statement. To enable this setting you must have the alter settings server level permission on the user account that you are [Read More…]

Find all Tables With Schema that Contain a Column by Name

29 October, 2012 | SQL Server, T-SQL | By: Michael Merrell

Sometimes you need to find tables that contain a specific column name. Maybe you want to find all the tables that have a User_ID column. This was my case when I came across writing this statement although it can be used for any cases where you need to find tables that contain a specific column. It ends up being a very simple query to the sys.objects and sys.columns tables using a join. First you query from the sysobjects table and find all objects that are type ‘U’ which stands for User Table. Then you join to the syscolumns and find [Read More…]

Kill All User Processes Connections For A Database

14 June, 2012 | SQL Server, T-SQL | By: Michael Merrell

When performing some actions in SQL Server against an existing database, like restore or take offline, it will fail if there are currently any users or processes accessing the database. The problem with this is that even on SQL Server 2008 Management Studio there is no easy way to just drop existing connections. A lot of times these process connections that keep you from performing your desired action are actually inactive as a process can sit inactive on SQL Server for a good deal of time. The Easiest way to accomplish the goal that I have found is with the [Read More…]

Selecting The Same NewID (Unique Identifier) into the same Row

17 May, 2012 | T-SQL | By: Michael Merrell

If you have ever needed to select the same newly created unique identifier into multiple columns with a single select statement in T-SQL then you would have run into the same problems as me. It’s certainly not something that has a lot of uses as the majority of the time you are using the NewID method in T-SQL you will be inserting into a new table and if you need the same value for multiple columns then you are just duplicating data. Technicalities aside I ran into this issue and fought with it due to the fact that you can’t [Read More…]

Randomizing Results of a Data Query in T-SQL and the Entity Framework

9 April, 2012 | C#. NET, Entity Framework, T-SQL | By: Michael Merrell

There are many scenarios where you may need to randomize the results of a query that is pulling data out of your database.  When you are able to do this using straight T-SQL the statement is simple and easy.  All you have to do to get random ordered results using T-SQL is add the following order by clause to the end of your statement. The issue comes into play when you need to do this inside of the Entity Framework when pulling form LINQ to SQL. While there are several different ways you can do this when you are using LINQ to [Read More…]