Monday, August 16, 2010

Custom SharePoint 2010 Home Page in 30 minutes!

UPDATED!

Just one day left to the custom SharePoint 2010 Home Page!

If you are one of those users wishing you could modify your SharePoint 2010 Home Page, then this presentation is just for you - our 30 minute, real-time, how-to presentation will guide you through the process. We will start from scratch - by creating new SharePoint site, and then will go through the entire customization process. The result - a cool new look and feel of your SharePoint web site!

To register please click here.

If you want to learn more about iTechnology, please visit our website. If you want to submit your specific questions about this presentation, please send us an email at info@myitechnology.com

UPDATE: Follow this link to watch the recorded Webinar on our YouTube channel.
Read more on this article...
Bookmark and Share

Wednesday, August 11, 2010

To hell and back with SQL Server Embedded Edition in WSS 3.0

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.


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:


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!
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.

Finally, /relative/ success!

Hope this post gives you some insights for working with SQL Server Embedded Edition.

Read more on this article...
Bookmark and Share