Using common table expression in SQL 2005

SQL 2005 has a new feature called Common Table Expression (CTE). You don't need to use table variable any more. It is more powerful. You can use it for recursive query, aggregation query etc.

Ex.

WITH tmp_a (col1, col2, col3)
AS
(
SELECT col1, col2, col3 FROM a WHERE a.flag = 1
)

SELECT * FROM tmp_a
WHERE tmp_a.col1 like 'aa%'

Moving to spaces.live.com

I move this blog to spaces.live.com. So see you there.

Running SSIS packages under other Users

There is one place need your very special attention when you try to deploy SSIS package, and let other users run the package. That is "Package Protection Level". By default it chooses "Encrypt sensitive data with user key". That is why you can't run it under the other users' account. Maybe you can try to use "Rely on server storage and roles for access control".

SQL 2005 64bit with Linked Server SQL 2000 Query Issues

We just upgrade our main DB from SQL 2k to SQL 2k5 64bit. But we still need to use linked server to link to another SQL 2k for running some queries. But I get the following error when trying to execute some query:

OLE DB provider "SQLNCLI" for linked server "SQL 2k Server" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0TCP Provider: An existing connection was forcibly closed by the remote host.Msg 18456, Level 14, State 1, Line 0

The solution is: When running a query, SQL 2k5 64bit is trying to invoke the sp_tables_info_rowset_64 store procedure. But this proc is not in SQL 2k server. So one wrapper is needed to put in SQL 2k server to make the query work.

Create Procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as
declare @Result int
set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

Running SSIS package programmatically

Here I found a nice blog post for Running SSIS package programmatically. Sql Server Integration Service is still a little bit confusing. Not just like you can easily run some SSIS packages on remote DB server which most likely every DBA dreaming for that. You need to use SQL Agent or do some programming of Web Service to run it.

Silverlight Cool...

Yesterday on MIX08, Scott Guthrie announced the Silverlight 2 Beta. Demonstrating their support for Silverlight, customers and partners also took part in the keynote, including AOL, Aston Martin, Cirque du Soleil, DoubleClick, Hard Rock, Move Networks and NBCOlympics.com on MSN. Scott talked about Web, Media, RIA, and Mobile. Those cool demos really impressive. You can't image how good those HD Video, and Rich Inteactive Applications experiences are. AOL demo their Blazing Fast Email client using Silverlight. And the performance is so good, much better than AJAX.

IE 8 beta 1 for Developer

Microsoft announced IE 8 Beta 1 for Developer on MIX 08 yesterday. I download and install it today. I'm really interested in a few features:

• Activities: No more copy and paste to search, map or blog in another tab or window. Very convenient.
• Webslice: This is also a cool feature like they demo it using ebay.
• Developer tools: Very cool and useful.
• Emulate IE7: If you don't like IE8, you can switch back to IE7 engine.

But when I tried out, still have some problems because it is still in "Beta".

• Performance: it is too slow on my XP SP2 machine.
• Most of the website doesn't look good. I try Yahoo, MSN, MSDN etc.

Hope it will be better in next Beta. :)

Microsoft Office Live is up

Microsoft is offering their new Office Live Workspace service now for free. The user can upload and share documents as well as access important information from any computer. The user will also be entered into a $100,000 Sweepstakes drawing or win one of 30,000 other prizes just for signing up.

Although it is still in Beta. But looks very nice. Especially more familiar than Google Document for me who works with Microsoft Office everyday.

Free Microsoft Software for Students

Microsoft is giving out free softwares for students, including Visual Studio 2008, Windows Server 2003 etc. It is nice.

Microsoft DreamSpark

SQL Server 2008 Feb CTP

A few features I like in the SQL 2008:

• Database encryption
• Database backup compression
• FileStream support for big files
  

I also like the IntelliSense in the new SQL Management Studio.

Execute SSIS Package in C# Apps

Add referrence to Micosoft.SQLServer.ManagedDTS.dll. It is in your SQL Server Program Files Folder\SDK\Assemblies.

using Microsoft.SqlServer.Dts.Runtime;

namespace ExecuteSSIS
{
class Program
{
static void Main(string[] args)
{
Application app = new Application();
//
// Load package from file system
//
Package package = app.LoadPackage(@"c:\myPackage.dtsx", null);
package.ImportConfigurationFile(@"c:\myPackage.dtsConfig");
//Pass parameters
Variables vars = package.Variables;
vars["variable"].Value = "values";
DTSExecResult result = package.Execute();
Console.WriteLine("Package Execution results: {0}",result.ToString());

//
// Load package from Sql Server
//
Package package2 = app.LoadFromSqlServer(
"myPackage","server_name", "sa", "password", null);
package2.ImportConfigurationFile(@"c:\myPackage.dtsConfig");
//Pass parameters
Variables vars2 = package2.Variables;
vars2["variable"].Value = "value";
DTSExecResult result2 = package2.Execute();
Console.WriteLine("Package Execution results: {0}",
result2.ToString());
}
}
}

Use Active Directory to Authenticate

Today I spent sometime to review the System.DirectoryServices name space in .Net 2.0. It is very convenient to use it to access A.D. and doing Authentication for your apps. Here are some sample codes in C# you might need to start with:

//Variables for getting groups
string _path;
string _filterAttr;

bool validateUser(string username, string password)
{
//Validate Users
string path = "LDAP:// your domain";
string domainUserName = domain + @"\" + username;
DirectoryEntry entry = new DirectoryEntry(path, domainUserName, password);

try
{
// Bind to the native object to force authentication to happen
Object obj = entry.NativeObject;
DirectorySearcher search = new DirectorySearcher(entry);
search.Filter = "(SAMAccountName=" + username + ")";
search.PropertiesToLoad.Add("cn");
SearchResult result = search.FindOne();
if (result != null)
{
// Authenticated
_path = result.Path;
_filterAttr = result.Properties["cn"][0].ToString();
return true;
}
else
return false;
}
catch (Exception ex)
{
throw new Exception("User not authenticated: " + ex.Message);
}
}

//Getting the groups this user belongs to
string getUserGroups()
{
DirectorySearcher search = new DirectorySearcher(_path);
search.Filter = "(cn=" + _filterAttr + ")";
search.PropertiesToLoad.Add("memberOf");
StringBuilder groups = new StringBuilder();

try
{
SearchResult result = search.FindOne();

int nCnt = result.Properties["memberOf"].Count;

for (int i = 0; i < nCnt; i++)
{
string dn = result.Properties["memberOf"][i].ToString();
int eidx = dn.IndexOf("=", 1);
int cidx = dn.IndexOf(",", 1);

if (eidx == -1)
{
return string.Empty;
}

groups.Append(dn.Substring(eidx + 1, cidx - eidx - 1));
groups.Append("|");
}
}
catch(Exception ex)
{
throw new Exception("Groups not getting: " + ex.Message);
}
return groups.ToString();
}

Referenced assembly System.Deployment is not installed for ClickOnce?

My computer has been complaining for "Referenced assembly System.Deployment is not installed..." whenever trying to install any ClickOnce apps. And those apps were not installed successfully since sometime. Here are the solutions I found from msdn forum. It worked very well after I delete the ClickOnce store folder.

1. Run "Mage -cc". (Mage.exe can be found in the .NET 2.0 SDK)

2. Uninstall the application via Add/Remove Programs and reinstall it

3. If neither of these work then the last option is to delete the ClickOnce store and get back to clean state. To delete the ClickOnce store delete the folder "%userprofile%\Local Settings\Apps".

Use Authorization Manager (AzMan) with ASP.NET 2.0

It is very convenient to use AuthorizationStoreRoleProvider with ActiveDirectoryMembershipProvider for ASP.net application authentication and security control. But there are a few tricks you need to know when developing and deploying. Here are the useful links:

How To: Use Authorization Manager (AzMan) with ASP.NET 2.0

When I tried to deploy the app to Windows 2003 Service Pack 1, it didn't work. I found out it needs to apply this fix (KB915786) to make it work.

LINQ Pad

Want to try LINQ. This is a cool start for LINQ. Have fun.

LINQ Pad