Archive for December, 2009

Business Objects Fails to Retrieve Rows from MySQL Table

I just encountered a very odd situation in Business Objects where a Web Intelligence (WebI) report running against a MySQL v5 database suddenly stopped returning any rows, even though the data had not been deleted.  It turned out that the underlying application had been upgraded and the database schema had undergone a few minor changes but essentially the table structure appeared not to have changed at all. 

After investigating for a few hours I managed to rule out the change in storage engine (from MyISAM to InnoDB) and had managed to narrow the issue down to a single varchar column.  I didn’t have access to the pre-upgrade table scripts so I couldn’t see what had changed but I did notice that in the new table definition the character set of the column was being specified as ASCII like so…

`column` VARCHAR(10) CHARACTER SET ASCII DEFAULT NULL

To fix this I went back to the Universe level and amended my object definition to CAST the column values…

CAST(`column` AS CHAR CHARACTER SET UTF8)

That solved the problem, if you have the same issue – I hope this helps you on your way.

DATE and TIME Data Types in SQL Server 2008

I recently attended the SQLBits V conference where Simon Sabin pointed out the changes to expect when stepping up to SQL Server 2008 from previous versions, most notable for me is the addition of the new DATE and TIME data types.  Here’s the full table of types…

What’s New in Windows Server 2008 R2

I’ve never been much of a server admin but in order to install a fresh copy of SQL Server 2008 R2 (November CTP) I decided to install a fresh copy of Windows Server 2008 R2.  I downloaded the install from Microsoft’s site and because I’ve been primarily running on Windows Server 2003 I ran through one of their e-Learning sessions to fill in the blanks of what’s new in both R2 and Server 2008.

The main versions are:

• Foundation (up to 8GB RAM, 1 Socket, no VMs)
• Standard (up to 32GB RAM, 4 Sockets, Host + 1VM),
• Web Server (up to 32GB RAM, 4 Sockets, no VMs)
• Enterprise (up to 2TB RAM, 8 Sockets, Host + 4VMs)
• Data Centre (up to 2TB RAM, 64 Sockets, unlimited VMs)

As always there are lots of new features on the list but the biggies seem to be Hyper-V, Remote Desktop Services (RDS) and Virtual Desktop Infrastructure (VDI).  The most stark break from the past here is that 2008 R2 will only run on 64-bit processors, existing users of Server 2008 32-bit installs on 64-bit processors will not be able to perform an upgrade and will have to do a clean install.

Hyper-V is Microsoft’s new virtualisation technology which on paper seems like a good challenger to VMWare ESX and it comes with R2 as standard although you’ll need Enterprise or Datacenter  to make the most of it.  By far the coolest feature of Hyper-V is the Live Migration (similar to VMWare’s VMotion), this allows you to move a guest system from one host server to another without any interruption to the users of the guest, that’s it – zero downtime.

RDS and VDI represent an enhancement of Terminal Services, along with Hyper-V you can now host virtual desktops on a virtual host and permit access from approved devices over the web or via the network, remote desktop now supports multiple monitors and Aero-Glass.

The session mentioned a number of other features, most noteworthy wew BranchCache (WAN optimisation), DirectAccess (seamless and interventionlessVPN replacement) and PowerShell 2.0 (command-line server admin) an there was an incremental 7.5 release of IIS.

Well, I hope you got something from this post, more details and a link to the e-Learning session can be found on Microsoft’s Windows Server 2008 R2 microsite.

Learn Microsoft SQL Server for Free

I had a conversation with a colleague recently about how to start teaching yourself SQL Server  skills outside of the workplace, he’d read an article on SQL Server Central that recommended buying the Developer Edition of Microsoft SQL Server which I think is a great idea but if you’re really just starting out with SQL Server why not do it for free?

Luckily, there are a number of ways that Microsoft helps us to do this…

Microsoft SQL Server Express 2008 is a cut-down (but not time-limited) version of the full SQL Server 2008 product restricted to 1 CPU, 1GB RAM and one single 4GB database.  The express edition contains the core database engine as well as the excellent Management Studio (IDE) and if you download the “Runtime with Advanced Services” version you also get Reporting Services, enabling you to build, deploy and run a small-scale BI solution.  Please note that the express edition does not come with SQL Server Agent (scheduling) or Integration Services (ETL) but you do get the Import/Export wizard to help move data around – check out the full comparison for more detail.

Alternatively, if you want to run with the big dogs you could try SQL Server 2008 Evaluation Edition which contains all the features of the Enterprise Edition but has a time limit of 180 days.

If you’re just starting out and want to get the hang of basic data manipulation, writing queries, creating tables, etc. then you’re probably better off with the Express version.   It’s smaller and won’t use up as much space/RAM on your PC and you won’t have to worry about the time running out – there’s also a chance that you’d be overwhelmed by all of the other features and products that are bundled with the Evaluation version and in the beginning it’s always better to keep things simple. If you’re already an intermediate-advanced user of SQL Server or another database platform, or you already know that you’re interested in SSIS or Analysis Services then you might as well get hold of the evaluation version but don’t forget that it’s time limited!

Extract Rows from Files using QGrep

Whether you work with enterprise Data Warehouses, departmental Data Marts or live operational OLTP systems there’s a good chance that at some point you’ll have a need to import or export data using flat files.  Flat files are great to read because they’re typically in common easy to understand formats such as CSV, the problem comes when you’ve got extremely large volumes of data that exceed your ability to handle them in Excel (e.g. over 65,536 rows for Excel 2003) or when you absolutely must preserve the initial file format.

I had a requirement recently to extract all rows relating to just one customer from an input file containing tens of thousands of rows and I had to leave the structure intact so that the existing SSIS loader would recognise the file.  Sadly the standard Windows command-line utilities are a bit lacking in this regard but you can download the Windows Server 2003 Resource Kit which among other things includes the QGrep tool.

QGrep is simply a Windows equivalent of the Grep command available in Linux and Unix, there is no GUI but if you’re doing something simple the syntax is fairly straight-forward (see SS64.com for more info), in my case I needed to extract all records from “input.csv” containing the CustomerID “476226235076″ and place them in “output.csv” so the command was…

qgrep “476226235076″ inputs.csv > output.csv