I got a question from Amit Vasu regarding the installation of SharePoint Portal Server 2003 with SQL Server versus MSDE. The issue was that his customer first wanted to use MSDE and if needed later on migrating to SQL Server.

This is possible and i did some investigation on MSDN and found the following. There is an article describing the migration from WMSDE to SQL Server:

http://www.microsoft.com/resources/documentation/wss/2/all/adminguide/en-us/stsf17.mspx

After this you have to change the connections in SharePoint Administration of the databases. the following walkthrough describes it:

Open the SharePoint Central Administration website and go to the "Windows SharePoint Services Central Administration". You will find under "Server Configuration" the option "Set configuration database server". Here you can change the configuration database to its new location.

Go to the "SharePoint Portal Server Central Administration". You will find under "Server Configuration" the option "Configure server topology". Here you change the content, component settings and configuration database to their new location.

Its very cool when you debug T-SQL in Visual Studio .NET. Just step from your code into a stored procedure in the SQL Server database. I found some articles at Microsoft about T-SQL Debugging in Visual Studio .NET which could be helpfull to setup your environment.

Setting Up SQL Debugging
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vxlrfsettingupsqldebugging.asp

Enabling SQL Debugging
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vxlrfsettingupsqldebuggingenablingsqldebugging.asp

Troubleshooting tips for T-SQL Debugger in Visual Studio .NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;817178&Product=sql

SQL Debugging: Unable to Start SQL Debug Session
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vxtbstroubleshootingsqldebuggingsetu
perrormessageunabletostartsqldebugsession.asp

If you know any other articles about T-SQL Debugging please don't hesitate to give a comment on this post. I will put it into the list. :)

Passing a null value to an int is not possible. The int type is mapped to the System.Int32 structure type and is not capable of storing a null value. Only integer values can be stored in there.

There are two ways which i know of how to solve this problem. The first method is using an object in stead of the int type. For example:

object val = 200;

if (val != null)
{
   return (int)val;
}

In this way you can set the val variable to a null value or an integer value. In case of an integer value you unbox the value to retrieve the integer value.

A second method is using the SqlInt32 type found in namespace System.Data.SqlTypes which is a SQL type. This type supports nullable values. For example:

SqlInt32 val = SqlInt32.Null;

or

SqlInt32 val = 200;

I needed some tool to look for keywords, texts or other things by searching through all fields of all columns of all tables of a specific database.

I found an article written by Vyas were he writes about a stored procedure which searches through all tables of the database it is created in.

It accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database.

The output of this stored procedure contains two columns:

- 1) The table name and column name in which the search string was found
- 2) The actual content/value of the column (Only the first 3630 characters are displayed)

Here's a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirements.

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO

Here is the complete stored procedure code:

           CREATE PROC SearchAllTables ( @SearchStr
nvarchar(100) ) AS BEGIN  -- Copyright
© 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns
of all tables for a given search string -- Written by: Narayana Vyas Kondreddi --
Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 --
Date modified: 28th July 2002 22:50 GMT  CREATE TABLE #Results (ColumnName
nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256),
@ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2
= QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName
= '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA)
+ '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA)
+ '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL)
AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND
TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar',
'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL
BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''',
LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName
+ ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results
END 
        

Yesterday was one of those days.... I was working on a project and doing a lot of debugging in SharePoint. At some point the system crashed and I had to reboot. After rebooting the computer it seems that my master database in SQL Server was crashed.

Ofcourse i did not made any backups of the master database. :(

I finally decided to copy all the mdf and ldf files from the data folder to another folder and deinstalled SQL Server. After reinstalling SQL Server i created the databases which ofcourse created the default mdf and ldf files. Then i copied the earlier copied files back over the just created mdf and ldf files. Do'nt forget to stop the SQL Server otherwise it will complain about in use files. I was very suprised to see that my databases returned including the data after starting SQL Server again.

It also seems that doing this will update the master database automatically. In my case i'm working with SQL Server 2000. I'm not sure if this also works for earlier versions.

Another problem arised because the rights which were set by programs like SharePoint when they create the database were gone. So it will not work in every case.

Later on i came to the conclusion that if i only had overwritten the master database files (mdf and ldf) with those of a clean install of SQL Server, after restarting SQL Server everything could work again. I still have to try this.

Hope this helps in case your master database gets corrupt.