I do a lot of work with SQL Server, and I’m often bouncing in and out of domains, using VPN to remote sites, etc. I always thought that you couldn’t connect using Windows Authentication to a SQL server in a different domain, so I’d remote desktop in and use SSMS on that machine.

But it turns out there’s a way!

1. Open Administrative Tools > Credential Manager (Control Panel > Stored Usernames and Passwords in older versions).
2. Add a Windows credential
3. • Use the fully qualified domain name for the server, followed by the SQL Server port, for example: server.domain.com:1433
   • Enter your fully qualified domain user, for example: domain\user
   • Your domain password

Now you can connect to that server in SSMS! Use the fully qualified domain name there too (no port this time): server.domain.com. It'll show your local user in the disabled user segment there, but actually pick up the credentials you entered before.

Of course, the proper ports will have to be open, etc. but if you’re connecting through VPN that should be handled.

How do you get a numeric-only string and ignore all non-numeric characters, using just standard SQL functions? There’s SQL replace and pattern functions, but they only work once on a string. So barring 53 nested REPLACE functions, it’s tough. I’ve run into this issue many times before while writing import scripts and the like.

Today, I had a stroke of brilliance – what if it could be done using FOR XML and spt_values to parse the entire string? A bit of googling and stack overflowing later, and some gluging of different parts together, and I came up with the following:

SELECT 
    (SELECT CAST(CAST((
        SELECT SUBSTRING(FieldToStrip, Number, 1)
        FROM master..spt_values
        WHERE Type='p' AND Number <= LEN(FieldToStrip) AND
            SUBSTRING(FieldToStrip, Number, 1) LIKE '[0-9]' FOR XML Path(''))
    AS xml) AS varchar(MAX)))
FROM
    SourceTable

Replace SourceTable with your table and FieldToStrip with your field, and away you go. If you want to include other allowed characters, change the pattern in the LIKE -- that specifies which characters to retain. All other fields will be removed.

By default, SQL Server 0’s out database files as it allocates space for them. This can be good for security/reliability in a production environment, but it means SQL Server is writing a bunch of extra data. Turn on instant file initialization in dev/stage/test environments for dramatically faster database creation and backup/restore operations.

There’s no switch for this in SQL Server, strangely enough. To get instant file initialization enabled, you’ve got to grant the “Perform volume maintenance tasks” permission to the SQL Server service account.

Brad McGehee’s got a great detailed analysis of what happens with instant file initialization and walkthrough steps with screenshots.

UPDATE: Added support for importing tracked forums and topics.

I'm in the process of converting my forums from Community Server to AspNetForum. While CS may support every single feature under the sun, its very big and unwieldly to work with. The more recent 2007 and 2008 versions are better, but still behemoth. I just want to drop in one simple forum with a couple subgroups, not host a million forums and blogs on my site. For this, AspNetForum fits the bill exactly.

There's no official importer for Community Server to AspNetForum, so I wrote my own. It turns out to be fairly simple. Just a SQL script will do it. Here are the steps the script goes through:

1. Import the ASP.NET application, user, and membership tables
2. Create the AspNetForum single sign-on linkages in its ForumUsers table
3. Import the groups, forums, topics, and messages

I packaged all this up into a SQL script that handles the whole process end to end. Just edit it to point to your database and forums and it will do the rest. It assumes the following:

• You've created and set up an AspNetForum database
• You've created the ASP.NET user and membership tables in the AspNetForum database
• The AspNetForum and ASP.NET user and membership tables are new and empty

This script will import everything with the same ID values from Community Server, making mapping or redirecting easy.

To use the script in SQL Management Studio, do the following steps:

1. Open the script (Import.sql) and connect it to your AspNetForum database
2. Execute a search and replace operation that replaces "CSDatabaseName" with the name of your Community Server database
3. Set the @applicationId variable to the ApplicationId matching your Community Server instance in the aspnet_Applications table in your CS database
4. Set the @groupId variable to the GroupId of the forum group to import from the cs_Groups table
5. Execute the script

Download the script and get importing! – CommunityServer2AspNetForumsImport.zip

Since I recently posted a Split UDF, I figured I'd do some research on the best way to do a Join operation. Unfortunately, UDF's can't take table variables as input parameters, so I had to just write a snippet of straight sql. Here's the solution:

DECLARE@valueListvarchar(1000)

SELECT
@valueList=COALESCE(@valueList+',','')+CAST(FieldASvarchar)
FROM
Table

The only way I'd seen before to do this operation involved a cursor, so it was great to come across this idea. Basically, the use of COALESCE function makes this work. For the first row, it returns an empty string, as @valueList is initially null. This makes the result 'val1'. Then, for subsequent rows, it returns the value with a comma appended, so the result is a comma seperated list. In one statement!