Managing the Windows Internal Database (SQL Server 2005 Embedded Edition)

As a follow up to the entry on Windows Internal Database, I keep getting questions on how to manage this if in case you do not want to remove it. The first time I heard about this, I thought Microsoft may have missed out on informing me about the SQL Server 2005 Embedded Edition as this does not appear in the list of Editions for SQL Server 2005. This is the replacement for WMSDE which a few of the Microsoft products - Windows Sharepoint Services 3.0 and Windows Software Update Services 3.0, for example- use as a backend database. Most of the questions I get are administration related like how to shrink the database files, move them on a different partition, etc. By default, there is no tool available to manage the database hosted in this instance (MICROSOFT#SSEE). But you can use the available SQL Server 2005 tools to do administration and maintenance. The simplest there is will be to install SQL Server 2005 Management Studio Express. This is an available download from the Microsoft Download Center. You just have to register this instance on the Management Studio Express to be able to administer it. One thing to remember is that during the registration process, you have to specify the Network Protocol option in the New Server Registration window to be Named Pipes as this is the one being used by this instance. Once connected, you can now start managing the databases running on this instance. Another way is to use the sqlcmd tool. This is the command-line utility of choice for SQL Server 2005, although you can still use the osql utility. You need to download the SQL Server 2005 Native Client and SQL Server 2005 Command Line Query Utility from the Microsoft Download Center. You need to install the Native Client first before installing the Command Line Query Utility. Once installed, you can run sqlcmd.exe from the command line. This is typically installed in this directory

C:\Program Files\Microsoft SQL Server\90\Tools\binn

To connect to the Windows Internal Database instance (MICROSOFT##SSEE), run this command

sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE \sql\query –E

Once connected, you can now execute those TSQL scripts that you are familiar with to manage and administer databases in this instance.

NOTE: This post was originally from my old blog site

Uninstalling SQL Server 2005 Embedded Edition

I thought I was just seeing things when I saw this in the Services applet - SQL Server 2005 Embedded Edition(MICROSOFT##SSEE). This is the version of SQL Server 2005 that Windows Sharepoint Services 3.0 installs on your server if you simply choose all the defaults. The problem with this is that there is no way for you to manage it from the SQL Server tools we are all familiar with (I tried it with Management Studio and sqlcmd but with no luck as it just throws an error saying that the server instance does not exist). Besides, if you want to scale up your database, you would have to migrate it to either Standard or Enterprise Edition. This renders this instance useless. Most of us will simply recommend uninstalling this version. The problem is, you won't see this in your Add/Remove Programs applet in Windows. It's an invisible instance that the only way to uninstall this is to uninstall Windows Sharepoint Services as well (unless you want to go through the process, I suggest not doing a complete reinstall). So how do you uninstall SQL Server 2005 Embedded Edition? I found it through this website.

1. Start Registry Editor, and then locate the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall

2. In the left pane, click each GUID. For each GUID that you click, look for a display name in the right pane that matches “Microsoft SQL 2005 Embedded Edition…”.

3. When you see a display name that matches the name, copy the value of the Key named “UninstallString”
4. Open a Command-Window (Start->run->cmd)
5. Paste the Copied string. It should be something like this: MsiExec.exe /X{BDD79957-5801-4A2D-B09E-852E7FA64D01}
6. Append “ CALLERID=OCSETUP.EXE” at the end of the MsiExec.exe eg “MsiExec.exe /X{BDD79957-5801-4A2D-B09E-852E7FA64D01} CALLERID=OCSETUP.EXE”

7. Run the command. This will go through the process of uninstalling SQL Server 2005 Embedded Edition

8. Reboot.

To verify, you can check the Services applet and see if the SQL Server 2005 Embedded Edition (MICROSOFT##SSEE) service is no longer there

What happened to my WSUS 2.0? November 12, 2007

This problem has been on a lot of newsgroups and forums available on the Internet and it has something to do with WSUS Administration Console screwing up.

Apparently, a category for a product was renamed cuasing it to introduce restricted characters in the form of double-quotes in the backend database (the product category happened to be codenamed Nitrogen).

This has caused the application to throw an error, thus, making the WSUS Administration Console inaccessible (see this Microsoft WSUS blog entry for more details). A workaround has been provided by the WSUS Team that deals with this problem but has something to do with updating or modifying some records in the SUSDB database. Here's my version of one of those SQL scripts which you need to run from your command-line

osql.exe –S\WSUS –E –Q”USE SUSDB; BEGIN TRAN; EXEC dbo.spStartCatalogSync; COMMIT TRAN”

Make sure to replace with your server hostname before running this script. Another version of the workaround can be found from the SANS Internet Storm Center website

OSQL -S \WSUS -E

1> USE SUSDB

2> GO

1> Update tbPrecomputedCategoryLocalizedProperty Set Title = Replace(Title, '"', '') Where Title like '%"%'

2> GO

1> Update tbPreComputedLocalizedProperty Set Title = Replace(Title, '"', '') Where Title like '%"%'

2>GO

1>QUIT

One thing to highlight here for those who are not comfortable with Transact-SQL is that we are trying to update the Title field of the tbPrecomputedCategoryLocalizedProperty and tbPrecomputedLocalizedProperty tables and replacing the double-quotes with single-quotes using the Replace function.

What I don't understand is why Microsoft tried push the update for public consumption if it's still in Beta (try reading the description of the product to find out) although they did promise that it was pulled out of their servers. I'm still keeping my fingers crossed

Table Valued Parameters in SQL Server 2008

As developers, there is always a need to pass data structures in one form or another in any programming language. TSQL is no exemption. This has been addressed by some workarounds as OPENXML in SQL Server 2000 where you can pass data around as VARCHAR data type. in SQL Server 2005, you have the XML data type where you can store data in XML form in a database column and use XPath with XQuery to manipulate the data. Both of which can, indeed, help pass data structures but still needs a lot of work to make it happen. Another approach is to create a temporary table on demand, populate the table before actually using the data in a stored procedure or function. SQL Server 2008 offers you the capabilities of passing a table as a parameter in a stored procedure and a function. Imagine populating a table on a client application and pass the table as a parameter to the backend database at one shot, thereby eliminating a lot of roundtrips, not to mention a couple of lines of codes. To understand how it works, let's have a look at a sample code. First, I'll create a very simple table named TableTVP1 which will be our target table.

--create sample table
CREATE TABLE [dbo].[TableTVP1]
(rowID int)
GO

The first step in understanding the concept of a table-valued parameter is to create a table-type; a new parameter which is of type table.

--Create TABLE type
CREATE TYPE tvp_TableTVP1 AS TABLE
(rowID int)
GO

The good thing about creating a table-type is that you can re-use it similar to re-using a user-defined data type. This means, you can use it to access any table with similar structure using the table-type(imagine having tables like Employees, Customers, Partners, etc. having similar table structures). After we have created the table-type, we can now use it it a stored procedure or function. The code below illustrates the use of the table-type we've just created

--Setup stored procedure to accept the new TABLE type
CREATE PROCEDURE usp_tvpInsert (@tableparam tvp_TableTVP1 READONLY)
AS
INSERT TableTVP1
SELECT * FROM @tableparam
GO

Notice how we used the table-type tvp_Table1TVP1 as a type to define the parameter @tableparam. It's just like any parameter you pass to a stored procedure or function except that it is defined as a table. The READONLY attribbute passed simply tells us that a table-type passed as a parameter cannot be modified and no DML operations are permitted unlike when dealing with temporary tables. This means you need to do the data manipulation outside of this stored procedure or function before you can pass the table-type. Now that we have both the table-type and the stored procedure, let's see how it works.

--Use the stored procedure
DECLARE @tableparam tvp_TableTVP1
DECLARE @i SMALLINT
SET @i = 1

WHILE (@i <=1000)
BEGIN
INSERT @tableparam VALUES (@i)
SET @i = @i + 1
END

EXEC usp_tvpInsert @tableParam

The code simply inserts records from 1 to 1000 on a table-type named @tableparam and passed it to the stored procedure. Imagine doing this in your client application and passing it one-time as a parameter in a stored procedure. It reduces the number of server roundtrips and improves response time aside from benefitting from being able to pass a table as a parameter. Check out my SQL Server 2008 videos at BlogCastRepository.com where a .NET application is also available for download as a sample that uses TVP