Boban's blog

20 Database Checks

2010-08-13T14:48:00Z

Recently I discovered a small application named SqlCop that integrates many database tests. I tried the tool and it was pretty much ok, although I’ve discovered some application bugs and also some of the queries(tests) were not working in my environment. So I decided to completely rewrite all those queries, without worrying about older Sql Server versions, and stick only with views from sys schema. Queries are tested on Sql Server 2005 and 2008. Some of tests are searching strings, so sometimes problematic strings can be founded in comments or object names.

If you have ideas about some other type of tests or you already have ones, please don't hesitate to comment or share.

-- Procedures or functions with prefix sp_

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name

, o.type_desc

FROM sys.objects o

WHERE o.is_ms_shipped = 0

AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')

AND LOWER(o.name) LIKE 'sp[_]%'

AND o.name NOT IN ('sp_alterdiagram', 'sp_creatediagram',

'sp_dropdiagram', 'sp_helpdiagramdefinition',

'sp_helpdiagrams', 'sp_renamediagram',

'sp_upgraddiagrams')

ORDER BY Found

-- Tables with prefix tbl

SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name

, t.type_desc

FROM sys.tables t

WHERE t.type = 'U'

AND LOWER(t.name) LIKE 'tbl%'

ORDER BY Found

-- SELECT *

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name

, o.type_desc

FROM sys.objects o

WHERE o.is_ms_shipped = 0

AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')

AND REPLACE(REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ' ', ''), ' ', ''), ']', ''), 'EXISTS(SELECT*', '') LIKE '%select*%'

ORDER BY Found

-- VarChar missing size

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name

, o.type_desc

FROM sys.objects o

WHERE o.is_ms_shipped = 0

AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')

AND REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ' ', ''), ' ', ''), ']', '') LIKE '%varchar[^(]%'

AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'

ORDER BY Found

-- Decimal missing size

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name

, o.type_desc

FROM sys.objects o

WHERE o.is_ms_shipped = 0

AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')

AND (REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ' ', ''), ' ', ''), ']', '') LIKE '%decimal[^(]%'

REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ' ', ''), ' ', ''), ']', '') LIKE '%numeric[^(]%'

)

AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'

ORDER BY Found

-- Using of float or real data types

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name

, o.type_desc

FROM sys.objects o

WHERE o.is_ms_shipped = 0

AND o.name <> 'sp_creatediagram'

AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'

AND (LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%float%'

LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%real%')

UNION ALL

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name + '.' + c.name

, type_desc = 'COLUMN'

FROM sys.columns c

INNER JOIN sys.objects o ON c.object_id = o.object_id

INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

WHERE o.is_ms_shipped = 0

AND t.name IN ('float', 'real')

ORDER BY Found

-- Using undocumented procedures

DECLARE @Temp TABLE(ProcedureName VARCHAR(50))

INSERT INTO @Temp VALUES('sp_MStablespace')

INSERT INTO @Temp VALUES('sp_who2')

INSERT INTO @Temp VALUES('sp_tempdbspace')

INSERT INTO @Temp VALUES('sp_MSkilldb')

INSERT INTO @Temp VALUES('sp_MSindexspace')

INSERT INTO @Temp VALUES('sp_MShelptype')

INSERT INTO @Temp VALUES('sp_MShelpindex')

INSERT INTO @Temp VALUES('sp_MShelpcolumns')

INSERT INTO @Temp VALUES('sp_MSforeachtable')

INSERT INTO @Temp VALUES('sp_MSforeachdb')

INSERT INTO @Temp VALUES('sp_fixindex')

INSERT INTO @Temp VALUES('sp_columns_rowset')

INSERT INTO @Temp VALUES('sp_MScheck_uid_owns_anything')

INSERT INTO @Temp VALUES('sp_MSgettools_path')

INSERT INTO @Temp VALUES('sp_gettypestring')

INSERT INTO @Temp VALUES('sp_MSdrop_object')

INSERT INTO @Temp VALUES('sp_MSget_qualified_name')

INSERT INTO @Temp VALUES('sp_MSgetversion')

INSERT INTO @Temp VALUES('xp_dirtree')

INSERT INTO @Temp VALUES('xp_subdirs')

INSERT INTO @Temp VALUES('xp_enum_oledb_providers')

INSERT INTO @Temp VALUES('xp_enumcodepages')

INSERT INTO @Temp VALUES('xp_enumdsn')

INSERT INTO @Temp VALUES('xp_enumerrorlogs')

INSERT INTO @Temp VALUES('xp_enumgroups')

INSERT INTO @Temp VALUES('xp_fileexist')

INSERT INTO @Temp VALUES('xp_fixeddrives')

INSERT INTO @Temp VALUES('xp_getnetname')

INSERT INTO @Temp VALUES('xp_readerrorlog')

INSERT INTO @Temp VALUES('sp_msdependencies')

INSERT INTO @Temp VALUES('xp_qv')

INSERT INTO @Temp VALUES('xp_delete_file')

INSERT INTO @Temp VALUES('sp_checknames')

INSERT INTO @Temp VALUES('sp_enumoledbdatasources')

INSERT INTO @Temp VALUES('sp_MS_marksystemobject')

INSERT INTO @Temp VALUES('sp_MSaddguidcolumn')

INSERT INTO @Temp VALUES('sp_MSaddguidindex')

INSERT INTO @Temp VALUES('sp_MSaddlogin_implicit_ntlogin')

INSERT INTO @Temp VALUES('sp_MSadduser_implicit_ntlogin')

INSERT INTO @Temp VALUES('sp_MSdbuseraccess')

INSERT INTO @Temp VALUES('sp_MSdbuserpriv')

INSERT INTO @Temp VALUES('sp_MSloginmappings')

INSERT INTO @Temp VALUES('sp_MStablekeys')

INSERT INTO @Temp VALUES('sp_MStablerefs')

INSERT INTO @Temp VALUES('sp_MSuniquetempname')

INSERT INTO @Temp VALUES('sp_MSuniqueobjectname')

INSERT INTO @Temp VALUES('sp_MSuniquecolname')

INSERT INTO @Temp VALUES('sp_MSuniquename')

INSERT INTO @Temp VALUES('sp_MSunc_to_drive')

INSERT INTO @Temp VALUES('sp_MSis_pk_col')

INSERT INTO @Temp VALUES('xp_get_MAPI_default_profile')

INSERT INTO @Temp VALUES('xp_get_MAPI_profiles')

INSERT INTO @Temp VALUES('xp_regdeletekey')

INSERT INTO @Temp VALUES('xp_regdeletevalue')

INSERT INTO @Temp VALUES('xp_regread')

INSERT INTO @Temp VALUES('xp_regenumvalues')

INSERT INTO @Temp VALUES('xp_regaddmultistring')

INSERT INTO @Temp VALUES('xp_regremovemultistring')

INSERT INTO @Temp VALUES('xp_regwrite')

INSERT INTO @Temp VALUES('xp_varbintohexstr')

INSERT INTO @Temp VALUES('sp_MSguidtostr')

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name

, o.type_desc

FROM sys.objects o

INNER Join @Temp T ON LOWER(OBJECT_DEFINITION(o.object_id)) Like '%' + LOWER(T.ProcedureName) + '%'

WHERE o.is_ms_shipped = 0

AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')

AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'

ORDER BY Found

-- Procedures without SET NOCOUNT ON

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name

, o.type_desc

FROM sys.objects o

WHERE o.is_ms_shipped = 0

AND o.type = 'P'

AND UPPER(OBJECT_DEFINITION(o.object_id)) NOT LIKE '%SET NOCOUNT ON%'

AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'

ORDER BY Found

-- Procedures with SET ROWCOUNT

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name

, o.type_desc

FROM sys.objects o

WHERE o.is_ms_shipped = 0

AND o.type = 'P'

AND UPPER(OBJECT_DEFINITION(o.object_id)) LIKE '%SET ROWCOUNT%'

AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'

ORDER BY Found

-- Procedures with @@identity

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name

, o.type_desc

FROM sys.objects o

WHERE o.is_ms_shipped = 0

AND o.type = 'P'

AND o.name <> 'sp_creatediagram'

AND LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%@@identity%'

AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'

ORDER BY Found

-- Invalid characters in column or object names

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name

, o.type_desc

FROM sys.objects o

WHERE o.is_ms_shipped = 0

AND LOWER(o.name) LIKE '%[^a-z0-9_.-]%'

AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'

UNION ALL

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name + '.' + c.name

, type_desc = 'COLUMN'

FROM sys.columns c

INNER JOIN sys.objects o ON c.object_id = o.object_id

WHERE o.is_ms_shipped = 0

AND LOWER(c.name) LIKE '%[^a-z0-9_.-]%'

ORDER BY Found

-- Using of text, ntext or image data types

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name

, o.type_desc

FROM sys.objects o

WHERE o.is_ms_shipped = 0

AND o.name <> 'sp_creatediagram'

AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'

AND (LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%text%'

LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%ntext%'

LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%image%')

UNION ALL

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name + '.' + c.name

, type_desc = 'COLUMN'

FROM sys.columns c

INNER JOIN sys.objects o ON c.object_id = o.object_id

INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

WHERE o.is_ms_shipped = 0

AND t.name IN ('text', 'ntext', 'image')

ORDER BY Found

-- Collation problem. Column's collation is different from database collation

SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name + '.' + c.name

, type_desc = 'COLUMN'

FROM sys.columns c

INNER JOIN sys.objects o ON c.object_id = o.object_id

WHERE o.is_ms_shipped = 0

AND CAST(c.collation_name AS VARCHAR(50)) <> CAST(DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS VARCHAR(50))

-- Empty tables

SET NOCOUNT ON ;

CREATE TABLE #EmptyTables (Table_Name VARCHAR(100))

EXEC sp_msforeachtable 'If Not Exists(Select * From ?)

Insert Into #EmptyTables Values(''?'')'

SELECT Table_Name

FROM #EmptyTables

ORDER BY Table_Name

DROP TABLE #EmptyTables

-- Tables without primary key

SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name

, type_desc = 'PRIMARY_KEY'

FROM sys.tables t

LEFT OUTER JOIN (SELECT object_id FROM sys.indexes WHERE is_primary_key = 1) i ON t.object_id = i.object_id

WHERE i.object_id IS NULL

-- Tables without clustered index

SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name

, type_desc = 'TABLE'

FROM sys.tables t

LEFT OUTER JOIN (SELECT object_id FROM sys.indexes WHERE type = 1) i ON t.object_id = i.object_id

WHERE i.object_id IS NULL

ORDER BY Found

-- Missing foreign Keys

-- If a column ends with id but is not a foreign key, it should be renamed

SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name + '.' + c.name

, type_desc = 'FOREIGN_KEY_CONSTRAINT'

FROM sys.tables t

INNER JOIN sys.columns c ON t.object_id = c.object_id

LEFT OUTER JOIN (SELECT fk.parent_object_id, fkc.parent_column_id

FROM sys.foreign_keys fk

INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id

) fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id

LEFT OUTER JOIN (SELECT ic.object_id, ic.column_id

FROM sys.index_columns ic

INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id

INNER JOIN (SELECT object_id

FROM sys.index_columns

GROUP BY object_id, index_id

HAVING COUNT(*) = 1) icu ON ic.object_id = icu.object_id

WHERE i.is_primary_key = 1

) ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id

WHERE fkc.parent_column_id IS Null

AND ic.object_id IS NULL

AND t.name <> 'sysdiagrams'

AND t.name NOT LIKE '%Log'

AND t.name NOT LIKE 'Log%'

AND c.name Like '%Id'

ORDER BY Found

-- Uniqueidentifier column used in clustered index as first column and have default value newid()

-- check for default value is commented to show all clustered indexes with first column with uniqueidentifier type

-- it is posible that having such first column and that to be fine if that is a foreign key column.

-- But if you set a default values on all such columns to newid(), even so you set their values in insert procedure,

-- then you can uncomment the last check, and with that remove the ones that are fine as described.

SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name + ': ' + i.name + ' (' + c.name + ')'

, type_desc = 'CLUSTERED_INDEX'

FROM sys.index_columns ic

INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id

INNER JOIN sys.tables t ON i.object_id = t.object_id

INNER JOIN sys.columns c ON t.object_id = c.object_id AND ic.column_id = c.column_id

INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id

WHERE i.type = 1

AND ic.column_id = 1

AND tp.name = 'uniqueidentifier'

--AND c.default_object_id <> 0

ORDER BY Found

-- Missing index for foreign key columns

-- It is posible that creating indexes for all foreign keys is not required. So you should decide for which ones to create indexes.

SET NOCOUNT ON ;

CREATE TABLE #SmallTables (object_id int)

EXEC sp_msforeachtable 'If Not Exists(SELECT * FROM (Select Rows = Count(*) From ?) R WHERE Rows < 100)

Insert Into #SmallTables Values(OBJECT_ID(''?''))'

SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name + '.' + fkc.name + ' (' + c.name + ')'

, type_desc = 'INDEX'

, Resolution = 'CREATE NONCLUSTERED INDEX [IX_' + t.name + '_' + c.name + '] ON [' + OBJECT_SCHEMA_NAME(t.object_id) + '].[' + t.name + ']([' + c.name + '])'

FROM sys.tables t

INNER JOIN sys.columns c ON t.object_id = c.object_id

INNER JOIN (SELECT fk.parent_object_id, fkc.parent_column_id, fk.name

FROM sys.foreign_keys fk

INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id

) fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id

LEFT OUTER JOIN (SELECT ic.object_id, ic.column_id

FROM sys.index_columns ic

INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id

INNER JOIN sys.index_columns icu ON ic.object_id = icu.object_id

WHERE ic.index_column_id = 1

) ic ON fkc.parent_object_id = ic.object_id AND fkc.parent_column_id = ic.column_id

-- INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id

INNER JOIN #SmallTables st ON t.object_id = st.object_id

WHERE ic.object_id IS NULL

AND t.name <> 'sysdiagrams'

AND t.name NOT LIKE '%Log'

AND t.name NOT LIKE 'Log%'

--AND tp.name <> 'uniqueidentifier'

ORDER BY Found

DROP TABLE #SmallTables

-- Wide tables

SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + OBJECT_NAME(t.object_id)

, type_desc = 'TABLE'

FROM sys.tables t

INNER JOIN sys.columns c ON t.object_id = c.object_id

INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id

GROUP BY t.object_id

HAVING SUM(ISNULL(NULLIF(CONVERT(BIGINT, tp.max_length), 8000), 0) + CASE WHEN c.collation_name IS NULL THEN 0 ELSE c.max_length END) > 8060

CodeSmith, a tool for every developer

2009-11-09T19:26:00Z

I first had a contact with this tool, few years ago. My first impression was not good at all. In that moment I didn’t find it useful, especially the templates that comes with it. After some time I saw a very good template for procedures that was some 70% of my expectations. I decide to spend and have a deep look at the template and realized that it is not that hard to learn and write my own templates. That is how all started. After several years of using it, have such a large help in some big projects, now I think that if you work on some large project with some very large database then this or some other code generation tool is a must have for every developer. On fifth November 2009 I had some presentation on the regular monthly meeting organized by local .NET group. I presented what this tool is capable for and I present my several templates that I use every day. I mainly use four templates. First one is for stored procedures. Template is used only with SQL Server database but it requires very small change especially in data types in order to be used with some other types of database. This template knows to generate several useful procedures:

· Delete procedure

· Insert procedure

· Update procedure

· SelectAll procedure

· SelectByKey is a procedure that knows to generate the correct primary key parameter and to return one table’s record

· SelectBy??? are procedures if required for every foreign key in the table

· SelectByUniqueKey are procedures that selects table’s record by the unique keys

· SelectPaged and SelectDinamic that are not complete procedures like previous ones but are starting 80% of the final procedure.

One condition is required for all templates, and that is “table must have primary key”.
What are the main characteristics of the three Delete, Insert and Update procedures. The template has some additional properties that are used for generating procedures. WindowsAuthentication property if true assumes windows authentication and if false sql authentication. Later this property is used to decide how to create the log part of DML actions. I can generate two types of log actions. One that is created if AddActionLogging and will write only the name of procedure, user who executed it, date of execution and the primary key for the record in one LogAction table. Second type of logging is full table logging in parallel table with identical design as the main table if AddTableLogging is set to true. Mainly the first type is used, but if the complete logging is required for some mainly very important table, then this type of logging is used. IncludeDropStatements and IsolationLevel are standard properties in procedures templates that decide whether a drop statements before create statements will be generated and the second will decide which isolation level will be used. All the procedures are schema aware and are created in the same schema as the main table. And some other properties which are language related (are we use English or Macedonian names for tables) and did I have some read only columns, and if I have, then they are not generated as input parameters. It looks like I have too many properties, but large number of features provided by the template requires some properties for configuring it before usage. My templates are divided in cst file (the main template) and the code behind or CodeTemplate file where only C# methods are located.

My other templates are for Business Data Layer class which will call all these procedures, BusinessObject that is object presentation for record in the table and BussinessCollection which is collection for BussinessObject.

Additionally I have a template for generating calling C# code for existing procedure in the database. Of course I have some additional templates used for my specific needs not mentioned in this post.

I have attached all important templates, and hopefully will be very simple to use others, without explanation. In fact only the one for procedures and for DataLayer have so many properties. Others are very simple with one or two properties. If you have any questions about my templates please write me a note or send me an email.

Simple Class for Performance Test

2008-09-27T18:13:08Z

My last post was about measuring memory consumption of .Net objects. So I though, why don't continue with simplest performance testing class.

We are very often in doubt about particular part of code, could we make it faster. So you make a change and rely on your expectation but not on real proof. Sometimes because of not having time to start some performance tool, sometimes because it's not that important. However, in rare situations you really need to test some part of code or two or more different code parts that do the same job and you need the time of execution for every one of them. So here is one simple but very accurate performance testing class, but most important very easy to use.

using System.Runtime.InteropServices;

public class PerformanceTimer
{
    [DllImport("Kernel32.dll")]
    public static extern void QueryPerformanceCounter(ref long ticks);

    long startTime;
    long stopTime;

    public void Start()
    {
        QueryPerformanceCounter(ref startTime);
    }

    public void Stop()
    {
        QueryPerformanceCounter(ref stopTime);
    }

    public long Time
    {
        get { return stopTime - startTime; }
    }
}
Here is sample code how to use this class:

PerformanceTimer pt = new PerformanceTimer();
pt.Start();
//here is the code that is tested
pt.Stop();
long result = pt.Time;

This timer is very accurate and measure the time in nanoseconds. So if you test some code that runs very long time, make sure you divide the result with 1000000 to get result in milliseconds or with 1000000000 to get the result in seconds.

Measure memory consumption by .NET object

2008-09-23T21:38:25Z

There are many profiling tools like dotTrace (My favorite), CLR Profiler, ANTS, NProf and many others. I use dotTrace regularly, but sometimes I need to measure the size of two objects only, and just to see the difference. I don't wish for such a small operation to start debugging with any tool. So here is what I found very useful:
long memoryStart = 0;
long memoryEnd1 = 0;
long memoryEnd2 = 0;

Thread.MemoryBarrier();
memoryStart = GC.GetTotalMemory(true);
string xmlContent = xDoc.OuterXml;
Thread.MemoryBarrier();
memoryEnd1 = GC.GetTotalMemory(true);
object t = base.FromXml(xDoc);
Thread.MemoryBarrier();
memoryEnd2 = GC.GetTotalMemory(true);

long first = memoryEnd1 - memoryStart;
long second = memoryEnd2 - memoryEnd1;

In my case first one is string(xml content) loaded from xDoc(XmlDocument), and second is some manually(base.FromXml) de-serialized object but you can use any type of object. Most of cases when you want to perform this kind of measure, it is done on the something that is in two different states.
The italic lines are the one that are not important in the sample and are just used to make this code functional.

Dummy methods

2008-09-14T08:01:00Z

I have noticed that in every language, in every environment, there are always many dummy methods. My definition for dummy methods is a method that is there just to support some backward compatibility, or there is a new much better method but the old still exists, methods that have some dummy overloads, or methods used for wrong purpose.
Let’s start with examples to see what I'm thinking:
1. Parse method in .NET. This method exists since .NET 1.0 framework. Since .NET 2.0 all data types that had Parse method also have TryParse method. This method is much better and should be used but I can notice that Parse method is still used. I'm surprised that this method is not used just by some beginners, but you can find it in some MVP's blogs, on MSDN examples and books. When someone tries to warn them, they either are rough and say that Parse is faster method (they don't know the difference) or have no idea that TryParse exists.
2. ToString() method in .NET. Also this method existed since first .NET version but also this method has some overloads that should be used. Sometimes this is not a big issue, but if someone moves to Multilanguage application environment can discover how much this is important.
3. ISNULL() function in T-SQL. This method is overused in the WHERE clause when a condition should be implemented on nullable parameter. This method is intended to be used in SELECT clause even there is a replacement too. So developers often used it in wrong places where a much better and correct way exists to do the same thing:

--invalid because don't solve every situation, not to mention some other reasons
WHERE Status = ISNULL(@Status, Status)
--instead of
WHERE (Status = @Status OR @Status IS NULL)
/* Status is a column and @Status is a nullable parameter*/

4. Use of Microsoft.VisualBasic assembly methods in .NET application. This is more like stupid than dummy so I will not comment more.

What we can do to protect from dummy methods. Unfortunately there is no way to remove them for good. There are tools that help in discovering such problems like FxCop for .NET or BPA for SQL Server, if someone uses them. Maybe the best way to protect yourself from these dummy methods is reading good books, and try to be as consistent developer as possible.
I've wrote this post because quality code does matter.

Run a .sql script files in C#

2008-08-07T07:52:00Z

If you have placed in situation to execute .sql script files from .NET code on SQL Server, you will see that SqlCommand class is not that useful. In other words, you can't execute batch commands that contains 'GO' (batch finalizer command). So you can think of several options, like split that script to several commands, and execute one by one. But you realize how complex is that task.
If you wanted to do that prior to SQL Server 2005, the only option is using osql utility. Since SQL Server 2005 there is another option which is much better and preferred. That option is using SMO library which comes with SQL Server and can be used for managing everything on SQL Server 2005. You can backup, restore databases, configure permissions, replication, etc.
I plan to post about how to backup and restore database using this SMO library in near future. Here will write only about how to read sql file and execute the content on SQL Server.

using System.Data.SqlClient;

using System.IO;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

string sqlConnectionString =

"Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";

FileInfo file = new FileInfo("C:\\myscript.sql");

string script = file.OpenText().ReadToEnd();

SqlConnection conn = new SqlConnection(sqlConnectionString);

Server server = new Server(new ServerConnection(conn));

server.ConnectionContext.ExecuteNonQuery(script);

}

This post is based from my answer on one MSDN forums' question located here.

Turn on Versioning on SharePoint document library

2008-07-22T13:01:00Z

Shared Documents is default document library that is created when you create new Site on SharePoint Server 2007. By default Versioning or Checkout is turned off, but these features are very useful for document management. First one when turned on will create new document version whenever document is changed, and you can access old versions of the document, make old version as current document. Second option when turned on, will provide Source Safe like functionality for documents. If you like to edit one document you have to check out the file. After that you can edit this document, and nobody else can edit it. After you finish editing, you will have to check-in document (you can provide comment for that edit if you like) after which somebody else can edit it. I had a project where I create sites programmatically, and I wished that this features should be turned on. So here is some code that shows which properties should be changed:

SPSite myRootSite = new SPSite("http://myroot");
SPWeb myWeb = myRootSite.OpenWeb("mysubsite");
SPList sharedDocuments = myWeb.Lists["Shared Documents"];
sharedDocuments.EnableVersioning = true; //enable versionning
sharedDocuments.EnableMinorVersions = false; // chose one of two types of versioning
sharedDocuments.ForceCheckout = true; //enable check out option
sharedDocuments.Update(); //don't forget to update the list whenever you make some change

This code is changing Shared Documents, but you can change any document library on your site. You can even turn on versioning on any SharePoint List, but Check out option is only available for Document Library lists.