Importing table data is an easy enough task in SQL Server 2005 - but this week we stumbled across this error message:

Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Expected the TEXT token in data stream for bulk copy of text or image data.".
 (SQL Server Import and Export Wizard)

Funny thing is, there were only nvarchar(max) columns on the table, and no binary columns whatsoever. A bit of searching and more staring at the table definition finally pointed out us in the right direction - the target table has a computed field in it, like so:

[MyComputedColumn] AS
 (
  [dbo].[MyFunction](OtherColumnAsParameter)
 ),

Although we had told SSIS to <ignore> it, this error came up. The only work around for SSIS: remove the computed field before doing the import.

References:

http://www.eggheadcafe.com/conversation.aspx?messageid=30015415&threadid=30015404
http://sqlug.be/forums/thread/821.aspx

Today I wanted to install SQL Server 2008 Developer Edition. Easy enough, at least so I thought. Started setup for a new instance, chose a different directory from where SQL 2005 lives (my c: drive is nearly full), selected a few components, clicked Next>:

I have no idea what SQL Server tries to tell me. This is sick.

Tonight, SQL Server decided to write a 14GB error log to disk - filling it up neatly, which of course had a couple of "side effects". Drat! All log file directories (HTTPERR, IIS, MailEnable, you name it) - everything except the SQL error log is on a separate partition for exactly one purpose: that no application log can stop the server dead in its tracks.

After clearing up the mess I thought "let's move the logs for SQL Server". Thanks to Alex I finally figured out where to do that - in the Configuration Manager:

Go to the Properties dialog, and modify the Startup Parameters (-eC):

Details can be found in the article Moving System Databases, section Moving the master and Resource Databases. Someone from Microsoft care to enlighten me as to why this log directoy has been hidden so far away from sight?

At long last at least a CTP is available. Definitely worthwhile to check out.

The final SQL Server-related post for today: Working with tempdb in SQL Server 2005.

Another noteable tidbit from the "Achieving High Availability with SQL Server 2005" preconference session: the whitepaper Partitioned Tables and Indexes in SQL Server 2005.

Currently listening to the half-day preconference session "Achieving High Availability with SQL Server 2005" by Kimberly Tripp. Interesting tidbit for decreasing downtime for operational tasks (like create database or restore): Instant File Initialization. The figures really point to huge time savings! Quite a selling point for Enterprise Edition of SQL Server.

Namely Visual Studio Team Edition for Database Professionals. This needs either SQL Server Enterprise or Developer Edition. Of course I had Standard and Express on my machine only...

The November 2006 issue has lots of good security articles, which are available online too. Check out Security Habits, Threat Modeling (STRIDE approach), Extending SDL or SQL Security to name a few.

Will be there Wednesday & Thursday as ATE (Ask the Experts), so drop by in the experts zone and say hello!

David Litchfield published the paper Data-mining with SQL Injection and Inference (more NISR papers). From the abstract: When drilling for data via SQL injection there are three classes of attack – inband, out-of-band and the relatively unknown inference attack. Inband attacks extract data over the same channel between the client and the web server, for example, results are embedded in a web page via a union select. Out-of-band attacks employ a different communications channel to drill for data by using database mail or HTTP functions for example. Inference attacks stand alone in the fact that no actual data is transferred – rather, a difference in the way an application behaves can allow an attacker to infer the value of the data.

Yesterday, I attended Kevin Cox' talk about database mirroring in SQL Server 2005. The event was organized by the SQL Server User Group Austria in Vienna, with Peter Koen (old UG lead) inviting Kevin, and Jaser Elmorsy (new UG lead) organizing it. Session description for the records: Database mirroring is a new SQL 2005 feature for high availability. What happens if you combine mirroring with other features, like snapshot and clustering? This presentation will teach you the basics of database mirroring, then will discuss some advanced topics for common solutions.

Kevin also pointed us to the SQLCAT blog - CAT being shorthand for Microsoft SQL Server Development Customer Advisory Team. The description as well as Kevin promised that they will be posting demanding scenarios and solutions to those in that very blog.

On 12th of August, the Austrian .NET community is hosting a one-day conference on security, targeted at developers (no surprise here). The topics for NCC 2005 A include:

• Threat Modeling
• What's new in .NET 2.0 Security
• What's new in SQL Server 2005 Security
• What's new in Windows Vista Security

Quite a nice line-up I'd say. This event is supported by Microsoft Austria, so attending this event is free, except for your time, but I am sure security does warrant a day of your time! Sign up here

Two more important downloads in addition to all the other B2 craze:

• SQL Server 2005 Express April CTP
• SQL Server 2005 Express Manager April CTP

The # of VPC images starts skyrocketing (now that's a stupid word combination) on my dev machine...

Just seen on NTBugtraq, quote from the Web site: SQLRecon performs both active and passive scans of your network in order to identify all of the SQL Server/MSDE installations in your enterprise. Due to the proliferation of personal firewalls, inconsistent network library configurations, and multiple-instance support, SQL Server installations are becoming increasingly difficult to discover, assess, and maintain.

SQLRecon is designed to remedy this problem by combining all known means of SQL Server/MSDE discovery into a single tool which can be used to ferret-out servers you never knew existed on your network so you can properly secure them.

Didn't yet have time to try SQLRecon myself, but sure will.

The schedule for SQL Server 2005 Web casts is quite packed in December:

• Webcast 12/6: Overview of the new Developer features in SQL Server 2005—Level 200
• Webcast 12/6: Introducing the New SQL Server Management Studio—Level 100
• Webcast 12/6: SQL Server 2005 as a .NET Runtime Host—Level 100
• Webcast 12/7: T-SQL Enhancements in SQL Server 2005—Level 200
• Webcast 12/7: Introducing XML in SQL Server 2005—Level 200
• Webcast 12/7: Introducing ADO.NET 2.0 for SQL Server 2005—Level 200
• Webcast 12/8:The New Security Model in SQL Server 2005—Level 200
• Webcast 12/8: Introducing Web Services in SQL Server 2005—Level 200
• Webcast 12/8: Introducing Service Broker in SQL Server 2005—Level 200
• Webcast 12/9: Introducing Reporting Services for SQL Server 2005—Level 200
• Webcast 12/9: Introducing SQL Server Integration Services for SQL Server 2005—Level 200
• Webcast 12/9: Introducing SQL Server 2005 Analysis Services for Developers—Level 200
• Webcast 12/10: Introducing Full-Text Search in SQL Server 2005—Level 200
• Webcast 12/10: Introducing Replication in SQL Server 2005—Level 200
• Webcast 12/10: Introducing Notification Services in SQL Server 2005—Level 200

With all the hoopla about VS.NET 2005 and SQL Server 2005, people sometimes forget that we still have to solve problems using today's technologies. The SQL Server 2000 Full-Text Search Deployment White Paper is a document that helps you better understand a technology that is already out there, focusing on the following areas:

• The hardware and operating system requirements for efficient full-text
  search performance.
• Full-text search architecture.
• Full-text search administration.
• How to monitor full-text search performance.

On Thursday, Peter did his SQL Server 2005 HOL for members of the SQL Server User Group Austria:

His workshop encompassed a tour of the new Studio management tool and for example TSQL enhancements (CTE's shown on the following photo):

Aside from just watching, I did the workshop part for SQLCLR features - writing functions, stored procedures, UDTs (user-defined datatypes), aggregates and triggers in .NET code. One cool sample is a function for email verification - try to do this in TSQL!

This overview article at the SQL Server Developer Center provides a nice & quick rundown of new features for database development in Microsoft SQL Server 2005.

Microsoft has a new site dedicated to SQL Server 2005 (including the Express edition). Note though that only the Express Beta 2 can be downloaded freely.