While working on our iTechnology Forms Accelerator I encountered an interesting SharePoint-related development situation I want to share with you.
We all know installing WSS 3.0 with the Basic configuration adds the flavor of SQL Server Embedded Edition (aka WYukon or Windows Internal Database). There is not much official information on the topic but it seems that this edition has not many but significant differences to SQL Server Express:
- no 4 GB Database limit
- limited connectivity
It is also known (from Internet knowledge) that you can connect to SQL Server Embedded Edition even with SQL Management Studio Express but you need to provide the following hardcoded string as server name: \\.\pipe\mssql$microsoft##ssee\sql\query. Pretty much that's all the info for WYukon on the net... Not quite helpful when your SharePoint feature cannot connect to the database, right?
In order to find out more about WYukon and how SharePoint operates with it I used the usual suspect - Reflector and Microsoft.SharePoint.dll.
In order to find out more about WYukon and how SharePoint operates with it I used the usual suspect - Reflector and Microsoft.SharePoint.dll.
SharePoint uses a class Microsoft.SharePoint.Utilities.SqlSession to work with the database and there are some interesting fields and properties there:
public bool m_wyukonSafeMode;
private const string sqlstrSetSingleUser = "CHECKPOINT; EXEC sp_dboption '{0}', 'single user', '{1}'";
private const string WYukonDataSource = @"np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query";
It is also interesting how they open and close the connection to WYukon DB:
public bool m_wyukonSafeMode;
private const string sqlstrSetSingleUser = "CHECKPOINT; EXEC sp_dboption '{0}', 'single user', '{1}'";
private const string WYukonDataSource = @"np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query";
It is also interesting how they open and close the connection to WYukon DB:
Some interesting methods for WYukon compliance as well:
It is not really common for WSS production environments to be setuped on a single server (we all know the huge performance implications of doing it) but for developement virtual machine to test something quick, this can be a prefered environment. For our free product we do our forms magic by utilizing SharePoint's Content database with some tables and custom SQL queries and if it fails for a potential client who wants to be impressed real quick thus has just configured his brand new clean environment - well... it is not pretty to fail miserably not able to... connect to database.
So, how to solve it? I'll show you using a modification to the step-by-step approach - exception-by-exception.
The first exception you get is the standard:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified.
This was easy - just verify whether SharePoint connects to a WUkon server by comparing SPWeb.Site.ContentDatabase.Server with the hardcoded source: "np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query". If so make sure your code connects to it as well.
The next problem that you will face when you try to run the create table scripts is:
DDL statement is not allowed
Cool! Now what? Appearantly Microsoft forces you to get the database into single user mode in order to execute multiple statements (this was "reflected" conclusion, see the later screenshots)! Nice!
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified.
This was easy - just verify whether SharePoint connects to a WUkon server by comparing SPWeb.Site.ContentDatabase.Server with the hardcoded source: "np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query". If so make sure your code connects to it as well.
The next problem that you will face when you try to run the create table scripts is:
DDL statement is not allowed
Cool! Now what? Appearantly Microsoft forces you to get the database into single user mode in order to execute multiple statements (this was "reflected" conclusion, see the later screenshots)! Nice!
Solution:
ALTER DATABASE SharePointContentDatabase SET single_user;
// Create your tables
ALTER DATABASE SharePointContentDatabase SET multi_user;
Now the even trickier part is that if there is an open connection to the database you cannot connect. You can wait, or even start a timer job to try squeeze in when there is no open connection but our clients want to see the product right away!... not tomorrow, next week...
Thank god, there is a workaround here. A veeeery dangerous workaround... Force the other bastards out of your way - kill their connections:
ALTER DATABASE SharePointContentDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Doing this will drop SharePoint's connection to the database (if it was the bastard in your way) so you will need to reconnect (recreate) SPSite, SPWeb, etc.
If you are lucky though and no one in your way it means success! Well, no! Life is not perfect, and because you are a great developer who always executes multiple SQL statements in a transaction you are facing your next SQL Server Embedded related problem:
ALTER DATABASE statement not allowed within multi-statement transaction.
Cool! So you need to open connection, set db in Single User mode, close connection. Open another one, execute your transacted scripts, close connection. Open connection, return DB to original state, close connection.
ALTER DATABASE SharePointContentDatabase SET single_user;
// Create your tables
ALTER DATABASE SharePointContentDatabase SET multi_user;
Now the even trickier part is that if there is an open connection to the database you cannot connect. You can wait, or even start a timer job to try squeeze in when there is no open connection but our clients want to see the product right away!... not tomorrow, next week...
Thank god, there is a workaround here. A veeeery dangerous workaround... Force the other bastards out of your way - kill their connections:
ALTER DATABASE SharePointContentDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Doing this will drop SharePoint's connection to the database (if it was the bastard in your way) so you will need to reconnect (recreate) SPSite, SPWeb, etc.
If you are lucky though and no one in your way it means success! Well, no! Life is not perfect, and because you are a great developer who always executes multiple SQL statements in a transaction you are facing your next SQL Server Embedded related problem:
ALTER DATABASE statement not allowed within multi-statement transaction.
Cool! So you need to open connection, set db in Single User mode, close connection. Open another one, execute your transacted scripts, close connection. Open connection, return DB to original state, close connection.
Finally, /relative/ success!
Hope this post gives you some insights for working with SQL Server Embedded Edition.
No comments:
Post a Comment