# Wednesday, April 4, 2007

Just read on Mary Jo's Microsoft wants Open XML to be an ISO standard... They have my support.

Sign the petition
Wednesday, April 4, 2007 8:11:07 PM (W. Europe Daylight Time, UTC+02:00)

Recently I finished teaching a couple of courses, to be more specific; the Microsoft courses 2779 and 2780. This resulted in a little todo-list for postings on the blog, either because time was lacking or because I needed to look up some details to properly answer the question.

In the next couple of days, I will post answers (if I can answer the question) on the following questions;

As the answer start appearing on the blog, I will update this post, linking to the answers. Another answer is also relevant in this context, the things that changed when doing full server recoveries.

Wednesday, April 4, 2007 7:35:02 PM (W. Europe Daylight Time, UTC+02:00)

Lately is was confronted with a couple of textfiles that wouldn't load into the database (SQL Server 2005). I was getting the errors 

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

when trying to acquire the file through

BULK INSERT tblImport
FROM 'Z:\concat-file.txt'
WITH(TABLOCK, FORMATFILE = 'Z:\xmlformatfile.xml')

Turned out I had an arrow sitting at EOF (end of file) with the troublesome textfiles, which where concatenated using the copy-command;

COPY source*.txt concat-file.txt

The arrow representation was new to me, but after a little digging it boiled down to CTRL+Z and the difference of ASCII and binary copying... by default copy is ASCII and when merging files, so an end of file character gets inserted. The EOF character is omitted when doing a binary copy, so

COPY source*.txt /b concat-file.txt

resolved the error.

Curious about the arrow representation on Vista, I decided to verify with Windows Server 2003. Here the EOF story is (almost) the same, only the arrow is shown as a rectangle. Read more about copy here.

Wednesday, April 4, 2007 4:13:08 PM (W. Europe Daylight Time, UTC+02:00)
# Saturday, March 24, 2007

Does your backup and recovery strategy take the new SQL Server 2005 mssqlsystemresource database into account? What's different for SQL Server 2000 and 2005 from backup/restore perspective.

----

Compared to SQL Server 2000, SQL Server 2005 has a lot of nice features aimed at minimizing downtime during "unplanned events", most notably piecemeal restore and the inclusion of Full-Text indexes in the database backup and restore. However, before being able to do database restores, a functional database server is needed. Besides being a SQL Server instance, the database server also is at least a Windows operating system and hardware (or a virtual machine if you like). The later two usually are the domain of the system administrator who will also take care of the operating systems, including installed applications, being backed up. Likewise the DBA will take care of databases.

A typical system backup will exclude certain files and directories known to be always locked, like pagefile.sys and the SQL Server \Data folder. The other SQL Server folders and registry-setting (system state), are backed up. By restoring this backup for a SQL Server 2000, you get a database server with the Service Pack level of at time of the backup. The only thing that's missing for the database to start is the master database. In SQL Server 2005 only the master database isn't enough to get you started, you need mssqlsystemresource a.k.a. Resource Database as well.

What has changed that this is an important issue in the last step of getting a SQL Server instance running (quickly). The master database in SQL Server 2000 holds all information, the configuration related to the instance (like; logins, linked servers, databases) and the definition of system objects. When applying a service pack, typically maintenance on the system object is done, so the master database changes and is therefor Service Pack dependant. In SQL Server 2005 these two types of information are split, instance configuration still goes into the master database, but the system objects are held in the Resource Database. The advantage is that master is not depending on the Service Pack and since none but Microsoft should alter system objects, the Resource Database can remain read-only and will only be changed by Microsoft when applying the Service Pack.

When disaster strikes the (original) server, in many cases, a restore of the file system and system-state is performed on a more or less comparable machine configuration. This is the quick route, compared to installing Windows, a new instance and applying the Service Pack. The quick route has two extra advantages, no access to installation media is needed and other applications are restored too. But the quick restore route does not bring back a fully functional server, both sysadmin and DBA are aware of that. Therefor for this procedure to be successful, an offline backup of master (copy master.mdf and mastlog.ldf) was done after installation and application of a Service Pack. Now the last step is to copy master.mdf and mastlog.ldf to the SQL Server \Data folder. From here the DBA can start his/her database restore sequence (starting with the latest online backup of master (2000/2005)). This approach works on SQL Server 2000.
For SQL Server 2005, you have to add include the Resource Database in this strategy. And here is a little (positive) difference, because the Resource Database is accessed read-only, it only has read locks and thus can be copied while the instance is running. So you can either copy it after the installation or when the Service Pack is applied, or you can alter the file system backup to specifically include the files mssqlsystemresource.mdf and mssqlsystemresource.ldf.

----

What if disaster struck your SQL Server 2005 instance and you haven't cared for the Resource Database... well then you're off to the slow route of installing the instance after the restore and applying the Service Pack, before the database restore sequence can be started.

Saturday, March 24, 2007 2:29:51 PM (W. Europe Standard Time, UTC+01:00)
# Monday, March 19, 2007

Recently a student who was going through the Microsoft eLearning course 2778 asked for an explanation of "accent sensitive". Before going into why it matters to SQL Server, lets look into the role of accents. A lot of languages make use of accents to change the pronunciation of some letters. Like with the accents in the German language, which are called Umlaut, when translating the word Umlaut very directly, you'd get alternate tone.

Back to SQL Server and why it should be sensitive to accents, or for that matter case, kana or width (things we'll get to once we touch COLLATION). If we ask a question to our server, frequently we want the answer to be in order. And this order very much depends on data type.

Let's take a group of downhill skiing contestants. In preparation of a match, some overview is needed. The contestants have to be grouped in ages, so there is a need for sorting on birthdate (a datetime data type). During the match, there are results, so there also is a need to order by the time taken to get from start to finish (the number of seconds as decimal in descending order), so the winner is at first place. And probably before the match, there is the need for a list with all contestants ordered by lastname.

Where a programmer (VB, C#, etc) would see this lastname as a string, SQL Server actually has a lot of possible data types to represent and handle textual information.

Single Byte Double Byte
Fixed length char(x) nchar(x)
Variable length varchar(x) nvarchar(x)
CLOB (Character Large OBject) varchar(max) nvarchar(max)
Back-Compat CLOB text ntext

Though these data types all can store and handle textual data types, the collation has to be specified as well.

Collation

Collation is best thought of as the order of characters in a given character-set. In this sense, an alphabet is a character-set, but collation applies to other writing systems too. In SQL Server, the collation includes the character-set and the options;

  • binary
  • binary-code point
  • Case sensitive
  • Accent sensitive
  • Kana sensitive
  • Width sensitive

which are all explained at MSDN. These options are also referred to as sort order.

Collation exists at the levels of instance, database and column. If a collation is not defined when creating a column, it is inherited from the database. Likewise, if no collation is specified when creating the database, it inherits the collation from the instance. The collation of the instance is defined when it is installed. When defining the collation, the designer (see picture) can be used.

Note there is a choice for SQL Collations and Windows Collation. In SQL Server 2005, go for the Windows Collation (SQL Collation is mainly there for compatibility reasons).

Sorting and storing

The collation is used by SQL Server both when sorting data (SELECT ... FROM ... ORDER BY ...) and when storing textual data as part of an index (both clustered and non-clustered). It can happen that you need data that is stored in one collation, to be evaluated in another collation, like when comparing data from two different collations. However, two different collations can not be compared, so collations have to be made the same. This is where the COLLATE clause comes into play, so you can specify a collation in the query.

Example

To show some differences with sort order and collation, I've added a script for a demo database with two tables, both containing some character combinations with variations in case and accents. One table is stored in the Latin1_General_CS_AS collation, the other one in German_PhoneBook_BIN2. Both contain the same data;

RowNum
int
TextData
nchar(10)
1 ABC
2 ÄBC
3 abc
4 äbc
5 ABc
6 ÁBC
7 abC
8 ábc

When selecting from the Latin1_General_CS_AS collated table, ordered by TextData, you receive the order 3, 7, 5, 1, 8, 6, 4, 2. Whereas with the German_PhoneBook_BIN2 collation, the order is 1, 5, 7, 3, 6, 2, 8, 4. Now with the COLLATE clause, you can alter the collation in a query. So

SELECT RowNum, TextData FROM GerPB_BIN2 ORDER BY TextData COLLATE Latin1_General_CS_AS

though selected from the table in German_PhoneBook_BIN2 collation, is returned in the order of the Latin1_General_CS_AS collation. When comparing two from two different collations, like you do in a join, SQL Server will raise an error.

SELECT * FROM GerPB_BIN2 G INNER JOIN Latin1_CS_AS L ON G.TextData = L.TextData

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CS_AS" and "German_PhoneBook_BIN2" in the equal to operation.

However, by using the COLLATE clause with one of the compared columns, they can be joined.

SELECT * FROM GerPB_BIN2 G INNER JOIN Latin1_CS_AS L ON G.TextData COLLATE Latin1_General_CS_AS = L.TextData

Guess this is the long answer, but not the last time I have to give it...

Monday, March 19, 2007 11:40:48 AM (W. Europe Standard Time, UTC+01:00)
# Thursday, March 15, 2007

To be more specific;

  • Microsoft Virtual Server 2005 R2
  • Microsoft SQL Server 2005 Reporting Services

James Kovacs and Virtual PC Guy provide the details per server, aggregated it becomes:

  • Internet Information Server
    • Web Management Tools
      • IIS 6 Management Compatibility
        • IIS 6 WMI Compatibility
        • IIS Metabase and IIS 6 configuration comparability
      • IIS Management Console
    • World Wide Web Service
      • Application Development
        • (.NET Extensibility comes with ASP.NET)
        • ASP.Net
        • CGI
        • ISAPI Extension (comes with ASP.NET)
        • ISAPI Filters (comes with ASP.NET)
      • Common HTTP Features
        • Default Document
        • Directory Browsing
        • HTTP Errors
        • HTTP Redirection
        • Static Content
      • Health and Diagnostics
        • HTTP Logging
        • Request Monitor
      • Performance Features
        • Static Content Compression
      • Security
        • (Request Filtering comes with ASP.NET)
        • Windows Authentication

Installing SQL Server 2005 (including SSRS)

Ignore any compatibility warnings, just proceed installation and apply SP2 after installing. During the install you also see a warning about a compatibility issue for Visual Studio (SQL Server Business Intelligence Development Studio is based on the Visual Studio 2005 Development Environment), you can solve this by installing the Visual Studio 2005 SP1 followed by the Update for Vista.

Now you may have heard about the re-release of SP2, but the only one you can download from Microsoft is the fixed one (available since March 5th 2007). If you're not sure, read KB-article 933508 how to verify.

Installing Virtual Server 2005 R2

Start the installation of Virtual Server by right-clicking the setup-exe and running it as administrator. If you don't, it pretends IIS is missing (also, it won't detect the IIS portnumber).

Thursday, March 15, 2007 10:40:03 AM (W. Europe Standard Time, UTC+01:00)
# Wednesday, March 14, 2007

Okay, you are an administrator, for you just successfully installed the SQL Server on your Vista box. Now you want to start SSMS. 

SQL Server Management Studio If you just click this, you're likely not able to logon with Windows Authentication although you are member of BUILTIN\administrators (how else did you install?)!!! This is User Account Control (UAC) trying to nag you, but there are five ways to be relieved of this behavior:

  1. Log on as sa in Mixed Authentication mode. Not recommended, besides you either had to have this configured during install, or have to do it now (go to point 2 to get started).
  2. Right-click SQL Server Management Studio and "Run as administrator" (if you don't want to do this every time you start SSMS, go to point 3).
  3. Add your user account, or an appropriate group (other than BUILTIN\administrators) where you're a member as a login with the sysadmin server role.
  4. If your fed up with UAC altogether, visit the How-To Geek
  5. Install SP2 and use the SQL Provisioning tool after the Service Pack is applied.
Wednesday, March 14, 2007 2:59:55 PM (W. Europe Standard Time, UTC+01:00)
# Tuesday, March 13, 2007

Today I had the challenge of joining a Vista x64 computer to an SBS 2003 domain. Okay, not a straightforward thing and there is some patching needed before Vista can be joined to an SBS 2003 domain. However that was not the issue today, as it wasn't the first Vista machine to be joined to this domain. The SBS 2003 Server already received the 926505 patch (which was needed to get the first Vista x64 into the domain). However this time upon trying to join a Vista x64 machine, this error came up.

Your computer could not be joined to the domain because the following error has occurred:

The remote procedure call failed and did not execute.

And nothing in the eventlogs of either the Vista machine or the SBS-box! Also, after receiving this error, the computer account for the Vista machine is disabled in AD.

A bit of Googling showed the failing RPC might very well be a firewall issue, not specific to SBS and applicable to both Microsoft ISA and CheckPoint (see 899148). Not that this was the issue right now as ISA was running SP2 and SP1 is said to resolve the issue. But it was the hint I needed, knowing that the ISA Server 2004 was put on the SBS box after the first Vista machine was joined to the domain... To sum it up, the issue is 917903 and is solved in rollup update 930414.

CAUTION!!! After applying the 930414 patch, likely your Exchange Routing Engine, Simple Mail Transport Protocol and World Wide Web Service are stopped and publications (HTTP, HTTPS and SMTP) could be failing too.

But I did join the Vista machine to the SBS domain!

UPDATE(2007-03-15), applying the 930414 patch solved the problem where Outlook loses connection to the Exchange server, then keeps asking for a password, without restoring connection. 

SBS | Vista
Tuesday, March 13, 2007 7:42:46 PM (W. Europe Standard Time, UTC+01:00)
  1. Run Internet Explorer as administrator (go via Start, All Programs, click right mouse key on Internet Explorer, see the shield...).
  2. In IE, click Tools, Internet Options, Content, Certificates.
  3. Click Import to start the wizard. Click next and browse to the SBS Cert (\\yourSBSbox\C$\ClientApps\SBScert\SBSCert.cer). Click Next.
  4. Choose "Place all certificates in the following store", then click Browse and check to "Show physical stores".
  5. Browse to place the certificate in "Trusted Root Certification Authorities\Local Computer". OK, Next, Finish to complete the wizard.
Vista | SBS
Tuesday, March 13, 2007 2:59:59 PM (W. Europe Standard Time, UTC+01:00)
# Wednesday, March 7, 2007

Sometimes there are those things that annoy you, like the amount of memory seen by my computer. I have a HP Compaq nx6325 equipped with 4 GB of RAM, but Vista only reports 2943 MB. Not that I expected to see 4096 MB, I'm a bit smarter than that. This machine has an ATI Radeon Xpress 1150 which has no memory of its own and uses HyperTransport HyperMemory to share the system memory between CPU and GPU. But why should the GPU chew up over 25% of the systems memory?!?! (And why would I want to limit the amount of memory used for the GPU? I'm running Vista and SQL Server Developer x64 Editions, and would like to have maximum memory for the database services.)

Well, today I read a paper from Microsoft explaining what is going on with memory. Not that I have a solution now, but at least I can ask smarter questions now.

Wednesday, March 7, 2007 3:22:29 PM (W. Europe Standard Time, UTC+01:00)