Tech blog

If you have faced this problem when trying to attach your MSSQL database just copy MDF and LDF file to some directory where MS SQL service has full rights. It is usually something like this

c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup

Excellent post for achieving great SQL productivity.

I had to do some data clean up the other day, and really needed some regular expression replacements to do the job.

Since .NET has a great RegularExpressions namespace, and since SQL 2005 allows you to integrate .NET CLR functions in your T-SQL code, I thought I’d go ahead and experiment with creating a RegExReplace() function.

I am not so sure that I recommend using a function like this in production (there’s lots of pros and cons of CLR integration in SQL databases), but for data cleaning or quick tasks or just learning how to use new features or technology, it is very interesting and easy to do. All you need is a SQL Server 2005 database (Express is fine) and Visual Studio 2005.

Open up Visual Studio 2005 and create a new SQL Server Project, and after giving it a name and location, you will be prompted to connect to the SQL Server 2005 database in which you’d like to add your code.

Once the project is created, choose Project->Add User Defined Function, and name the .cs file anything you like, such as “RegExFunction.cs”.

Once the file has been added to your project, open it up and paste in the following code (changes made to the original template are in bold):

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
	[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true,IsPrecise=true)]
	public static SqlString RegExReplace(SqlString expression,
                 SqlString pattern, SqlString replace)
	{
	  if (expression.IsNull || pattern.IsNull || replace.IsNull)
	  	return SqlString.Null;

	  Regex r = new Regex(pattern.ToString());

	  return new SqlString(r.Replace(expression.ToString(), replace.ToString()));
	}
};

It’s really quite simple; within the class definition, just define public static methods that accept and return SQLTypes, and if those methods are marked with the SqlFunction attribute, when deployed they become available in your database code as T-SQL User-Defined Functions! Quite cool.

In this example, our function is accepting 3 SQLString parameters, and if any are null, we return null. If they are all legit, we construct a RegEx object from the pattern passed in, do the replace, and return the result. Note that this will not be especially efficient, since the RegEx object is created and destroyed for each call, but it does work and it is interesting at the very least to play around with. You might also want to experiment with other options, such as ignoring whitespace or case sensitivity, provided by the RegEx class. This particular code is very basic, and doesn’t handle error checking or anything like that, you may wish to make improvements or optimizations in your own implementation.

Now that your code is ready to go, choose Build->Deploy Solution. If all goes well, your assembly and new function have been deployed to your SQL database!

There is one final thing you must do before you can use the function, and that is configure your server to allow CLR code to execute, if it hasn’t been configured already. To do this, you must execute the following T-SQL statement:

--ENABLE CLR FUNCTIONS AND PROCEDURES FROM C#
sp_configure 'clr enabled',1
GO
RECONFIGURE
GO

Once that is complete, you can now use your new function like any other User Defined T-SQL function. For example,

select dbo.RegExReplace('Remove1All3Letters7','[a-zA-Z]','')

-------------------------
137

(1 row(s) affected)

Now you can do a standard Regular Expression Replacement within your database directly, for example as an UPDATE:

UPDATE MessyTable
SET MessyColumn = dbo.RegExReplace(MessyColumn, ... , ....)
WHERE ...

Here’s my two cents on using CLR code in a database: If the code is purely a generic function or tool that has nothing specific to do with your data, and it fits and works logically in a database querying language, and there is no way to efficiently implement that code in T-SQL, then it may be worthwhile to implement that function via the CLR. This is a pretty good example. A bad example would be a .NET function that returns a CustomerName when passed a customerID, or something along those lines. That’s just my take on things, for what it’s worth.

So, use wisely and have fun!

We have found nice explanation how you can upgrade Transact-SQL to work with Regular Expressions

First, you need to create this SQL functions for regular expressions:

CREATE FUNCTION [dbo].[FindRegularExpression]
	(
		@source varchar(5000),
		@regexp varchar(1000),
		@ignorecase bit = 0
	)
RETURNS bit
AS
BEGIN
	DECLARE @hr integer
	DECLARE @objRegExp integer
	DECLARE @objMatches integer
	DECLARE @objMatch integer
	DECLARE @count integer
	DECLARE @results bit

	EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
	IF @hr <> 0 BEGIN
		SET @results = 0
		RETURN @results
	END
	EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
	IF @hr <> 0 BEGIN
		SET @results = 0
		RETURN @results
	END
	EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
	IF @hr <> 0 BEGIN
		SET @results = 0
		RETURN @results
	END
	EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
	IF @hr <> 0 BEGIN
		SET @results = 0
		RETURN @results
	END
	EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
	IF @hr <> 0 BEGIN
		SET @results = 0
		RETURN @results
	END
	EXEC @hr = sp_OADestroy @objRegExp
	IF @hr <> 0 BEGIN
		SET @results = 0
		RETURN @results
	END
RETURN @results
END

This is an example to test it:

DECLARE @intLength AS INTEGER
DECLARE @vchRegularExpression AS VARCHAR(50)
DECLARE @vchSourceString as VARCHAR(50)
DECLARE @vchSourceString2 as VARCHAR(50)
DECLARE @bitHasNoSpecialCharacters as BIT

-- Initialize variables
SET @vchSourceString = 'Test one This is a test!!'
SET @vchSourceString2 = 'Test two This is a test'

-- Our regular expression should read as:
-- [a-zA-Z ]{}
-- eg. [a-zA-Z ]{10}  ...  For a string of 10 characters

-- Get the length of the string
SET @intLength = LEN(@vchSourceString)

-- Set the completed regular expression
SET @vchRegularExpression = '[a-zA-Z ]{' + CAST(@intLength as varchar) + '}'

-- get whether or not there are any special characters
SET @bitHasNoSpecialCharacters = dbo.FindRegularExpression(
   @vchSourceString, @vchRegularExpression,0)

PRINT @vchSourceString
IF @bitHasNoSpecialCharacters = 1 BEGIN
	PRINT 'No special characters.'
END ELSE BEGIN
	PRINT 'Special characters found.'
END

PRINT '---'

-- Get the length of the string
SET @intLength = LEN(@vchSourceString2)

-- Set the completed regular expression
SET @vchRegularExpression = '[a-zA-Z ]{' + CAST(@intLength as varchar) + '}'

-- get whether or not there are any special characters
SET @bitHasNoSpecialCharacters = dbo.FindRegularExpression(
   @vchSourceString2, @vchRegularExpression,0)

PRINT @vchSourceString2
IF @bitHasNoSpecialCharacters = 1 BEGIN
	PRINT 'No special characters.'
END ELSE BEGIN
	PRINT 'Special characters found.'
END

GO

If you got an error message you need to activate SQL server property with the code bellow this message:

Msg 15281, Level 16, State 1, Line 22
SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, see “Surface Area Configuration” in SQL Server Books Online.
Test one This is a test!!
Special characters found.
—
Msg 15281, Level 16, State 1, Line 40
SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, see “Surface Area Configuration” in SQL Server Books Online.
Test two This is a test
Special characters found.

In order to get rid or red text (error) do this to enable this function (sp_OACreate ‘VBScript.RegExp’) :

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

This scripts shows how you can sort by some parameter in Transact SQL:

DECLARE @SortOrder tinyint
SET @SortOrder = 2

SELECT CompanyName,
       ContactName,
       ContactTitle
FROM Customers
ORDER BY CASE WHEN @SortOrder = 1 THEN CompanyName
              WHEN @SortOrder = 2 THEN ContactName
              ELSE ContactTitle
         END

It would be fine to have this system function, and because we don’t this can be a replacement.

Note: additional checking can be added. You can also create your function for this. This is only logic.

This example shows how to get file extension.

DECLARE @Filename nvarchar(255),
	     @OnlyName nvarchar(255),
	     @Extension nvarchar(255),
	     @Pos int

SET @Filename = 'this.is.my.filename.with.dots.jpg'

SELECT @Pos = CASE (CHARINDEX('.', @Filename))
		        WHEN 0 THEN -1
			ELSE LEN(@Filename) - CHARINDEX('.', REVERSE(@Filename)) +1
		      END
SET	@Extension = SUBSTRING(@Filename, @Pos, LEN(@Filename))
SET	@OnlyName = SUBSTRING(@Filename, 1, @Pos-1)

SELECT @Filename AS [Filename], @Pos AS Position,
           @OnlyName AS OnlyName, @Extension AS Extension

We tried to add new ntext nullable column in MS SQL table.  We were quite surprised to get the following error message:

Saving changes is not permitted.  The changes you have made require the following tables to be dropped and re-created.  You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

It wouldn’t let us add an “Allow Nulls” column?  That just seemed absurd.

Apparently, this is now the default behavior for any of the following changes to a table:

• Adding a new column to the middle of the table
• Dropping a column
• Changing column nullability
• Changing the order of the columns
• Changing the data type of a column

In order to prevent this default behavior, you simply need to uncheck a box in the table designer options using the Tools -> Options menu item

Expand the Designers section to display the Table and Database Designers options.

To change this behavior, just uncheck the “Prevent saving changes that require table re-creation” checkbox.

Original article was published at http://codeslammer.wordpress.com/2008/10/19/saving-changes-is-not-permitted/

Standard Security
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Standard Security alternative syntax
Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;

Trusted Connection
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Trusted Connection alternative syntax
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Connecting to an SQL Server instance
Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True;

Trusted Connection from a CE device
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;

This is a tutorial from very nice Blog:

Hi, I saw many developers are asking this questions in different forums. Recently I saw this question on MSDN forum and I thought let me write a blog on this. I know many of us found this too easy however for new bees its bit hard.

In this article, I had used SQL Server 2005 as back end and C# as front end. SQL Server has “Image” data type to store the image. In Oracle and some other database you can use a data type which is used to store binary value (may be BLOB). I have created a simple aspx which has File upload control and a button. When user selects a file to upload, I am checking it for valid image type and converting it to array of Bytes. Then I will store that byte array into database. Below is the code,

if (objFileUpload.PostedFile !=null)
{
if (objFileUpload.PostedFile.ContentLength > 0)
{
HttpPostedFile objHttpPostedFile = objFileUpload.PostedFile;
if (CheckValidFileType(objHttpPostedFile.FileName))
{
int intContentlength = objHttpPostedFile.ContentLength;
byte[] bytImage =new Byte[intContentlength];
objHttpPostedFile.InputStream.Read(bytImage, 0, intContentlength);
}
}
}

Read Uploaded file (here Image) in Byte Array

Pass this Byte array to you DAL and use it for storing image in database. I am using Enterprise Library as DAL so my code will look like,

Database db =DatabaseFactory.CreateDatabase();
string sqlCommand ="StoredProcedureName";
DbCommand dbCommandWrapper = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommandWrapper,"@Image",DbType.Binary,bytImage );
try
{
db.ExecuteNonQuery(dbCommandWrapper);
}
catch {throw; }

Insert Image in to database

This is how you can store the Image in database. Retrieving the image is the same process. Write a SP which will return your image. Store this value in a Byte Array. Once you get the image in Byte array, you just have to write it on form as shown below,

Byte[] bytImage =Byte array retrieved from database.
if (bytImage !=null)
{
Response.ContentType = "image/jpeg";
Response.Expires = 0;
Response.Buffer =true;
Response.Clear();
Response.BinaryWrite(bytImage);
Response.End();
}


Code to display Byte array as Image on form.

To use this at multiple places in your application, you create a page to which you can pass ID of Image and it will retrieve image from database and create image. To do this copy paste above code in aspx.cs file. Now on every page you require to show this image take on that page and set its ImageURL property to the path of newly created user control. See the code below,

Image control on any aspx page (Lets say Sample.aspx).

string strURL ="~/ViewImage.aspx?ID= 1";
ViewImage.ImageUrl = strURL;

set Image URL for image control on code behind (Sample.aspx.cs)

You can see the image will be displayed in your page where you had put Image tag.
Happy Programing.