Windows Live provides SQL Server 2005 Books Online scoped search:

http://search.live.com/macros/sql_server_user_education/booksonline

As promised I put a photo of my son Wojtek. I hope he will leave the hospital tomorrow.

Today (27.05) at 17.10 my son Wojtek was born. Wojtek and his mother are fine. I need to take a breath and then I will put some photos of my child here. I am so happy!!! :-)

Mariusz 'masakra' Kędziora is new IT Pro Evangelist in Poland. Mariusz is an active member of Polish WSS.pl community and SE Club. Wish him luck in his new job!

There are two nice undocummented functions in SQL Server 2005 that allow to convert varbinary(max) data to nvarchar(max) data type. Those functions are called sys.fn_varbintohexsubstring and sys.fn_varbintohexstr. The second one is just a special call to the first one.

Scalar function sys.fn_varbintohexsubstring takes four parameters: bit @fsetprefix parameter (it tells if '0x' prefix should be added to the returned string), varbinary(max) @pbinin parameter (passes binary to convert), two int parameters - @startoffset (starting byte) and @cbytes (number of bytes to convert). Function returns value of nvarchar(max) data type. I use this function in my utilities for scripting CLR assemblies (assembly content is stored as varbinary(max)).

Try this:

DECLARE @x NVARCHAR(MAX)
SET @x = REPLICATE(N'1', 4001)
SELECT  LEN(@x)

Then try this:

DECLARE @x NVARCHAR(MAX)
SET @x = REPLICATE(CAST(N'1' AS NVARCHAR(MAX)), 4001)
SELECT  LEN(@x)

As you can see REPLICATE function returns the data type of the first parameter. So in the first example it is nvarchar(4000) (not max!!!). Explicit cast to nvarchar(max) can help here.

Varchar(max) and nvarchar(max) have higher precedence than traditional varchar and nvarchar (this behaviour is undocummented). The precedence of those types is shown below (from highest to lowest):

nvarchar(max)
varchar(max)
nvarchar
varchar

SQL Server Management Studio remembers the names of SQL Server instances it has ever connected to. If you want to clean this list you need to clean the following file (for English and Polish version of Windows):

EN: %userprofile%\Appliaction Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

PL: %userprofile%\Dane Aplikacji\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

If you think that LEN function in T-SQL returns the real length of your string data, you may be disappointed. Try the following statement:

SELECT  LEN(' abc '), LEN('abc ')

It appears LEN executes RTRIM function before it counts the number of chars in the string. This behaviour is docummented in BOL.

To get the real number of chars in the string you need to write your own version of LEN, something tricky like:

CREATE FUNCTION dbo.LEN2 ( @string nvarchar(max) )
RETURNS int
    WITH SCHEMABINDING,
         RETURNS NULL ON NULL INPUT
AS BEGIN
    RETURN LEN(@string + 'a') - 1
   END
GO

-- Test
SELECT dbo.LEN2(' abc '), dbo.LEN2('abc ')

If you use subst.exe to associate a path with a drive letter, you must know that every SQL Server hotfix installation will fail. Just unsubst all virtual disks created with subst.exe (subst.exe <drive> /D) to run the installation of hotfix successfully..

If you receive a message in SSMS that tells you don't have some SSIS library installed (for example Microsoft.SQLServer.ManagedDTS) - see the example below (this one was generated after installation of SP2 and 3159 cumulative hotfix when user tried to run maintenance plan):

Exception has been thrown by the target of an invocation. (mscorlib)

------------------------------
ADDITIONAL INFORMATION:

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Niezarejestrowana biblioteka. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED)). (Microsoft.SqlServer.ManagedDTS)

then do the following:

1. Find the file dts.dll (usually it is located in C:\Program Files\Microsoft SQL Server\90\DTS\Binn\).
2. In command-line go to the folder in which the file is located.
3. Run regsvr32.exe dts.dll.

If you have lots of SQL Server 2000 and 2005 instances you may sometimes make a mistake and try to attach a database detached from SQL Server 2005 to SQL Server 2000. Below is the error message you will see when you do that:

Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID <<db_id>> object ID 1 index ID 1. Run DBCC CHECKDB sysindexes.

This is what can happen if you try to copy several thousands lines of text in Management Studio (BTW, Visual C++ Runtime Error, huh?).

If you have a problem with CLR debugging (stored procedure, function etc.) that hangs in Visual Studio 2005, this is what you can do:

1. Stop debugging.
2. Restart SQL Server.
3. Open Server Explorer (in Visual Studio 2005).
4. Right-click on the connection, uncheck and then check again Application Debugging and Allow SQL/CLR Debugging options.
5. Re-run debugging.

Yesterday I played with ownership of database objects and schemas. Here is what I found in BOL (look for ALTER AUTHORIZATION statement):

If the target entity is not a database and the entity is being transferred to a new owner, all permissions on the target will be dropped.

What does it mean? It means that when you change the owner of schema or object, all permissions on the schema/object will be lost. And this is by design. IMHO, another reason to use single schema/object owner in my databases (or just use dbo schema and forget about this mess).