BumbleBI launched service provider: SQL Hotel – Shared and dedicated hosting of SQL Server databases and solutions

BumbleBI launched a new service provider called:
SQL Hotel – Shared and dedicated hosting of SQL Server databases and solutions.

Our servers are hosted in EasyNet’s datacenter at Schiphol-Rijk (Amsterdam, The Netherlands) which is directly connected to one of the world’s fastest internet hubs: Amsterdam Internet Exchange (AMS-IX).
Ideal for smaller databases without paying the expensive licensing costs.
Or for the large corporations for world-wide database access for free usage of Excel, PowerPivot, Power View and/or tablet apps.
Our Microsoft Business Intelligence (BI) consultants can support deploying your data to this cloud-environment (near realtime of by a daily import job).
The databases of these software suites can be made available in our SQL Hotel environment for analysis, reporting, data mining and more:

Interested? Check one of our websites:
Spread the word!

MS SQL Server 2012 Error – OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2).

I’ve encountered this annoying error which causes the SQL Agent Service to immediately stop after it is started.

SQL Agent was displaying ‘Agent XPs disabled‘:

Like Microsoft tells you, run this query to enable the ‘Agent XPs’-option in SQL Server:

sp_configure 'show advanced options', 1;
sp_configure 'Agent XPs', 1;
sp_configure 'show advanced options', 0;

…SQL Agent Service still refused to start….so back to Google! 🙂

After some Googling, I’ve found this golden tip:

This error can be due to an invalid ErrorLogFile path. Check the ErrorLogFile value under the instance registry key. The default instance key:


It appeared that because I installed another instance earlier and uninstalled that one, somehow the SQLAGENT.OUT-file was locked. I manually deleted the file in the logging directory:


After the deletion, I simply started the SQL Agent Service and it ran fine again! 🙂

Google also says:

This issue has been identified as a bug
It will be fixed with their next (service pack) update.

Stored Procedure: Automatically ‘Generate Scripts’ for local or linked server

Normally you would have done this manually:

Time for a change!
Schedule it in SQL Agent as a Stored Procedure and export it to a TXT-file.

SP GenerateScripts on LocalServer

On execution:

To export it to a TXT-file:
SQL Agent Job – ExportDatasetToTXT

Make sure that xp_cmdshell is enabled:

EXEC master.dbo.sp_configure 'show advanced options', 1
EXEC master.dbo.sp_configure 'xp_cmdshell', 1

List columns and attributes for every table in a SQL Server database

A nice addition for your Cookbook (Code Snippets):


sys.tables provides one row for each table in a database. This does include user tables and system tables that exist in each database. There is a column within sys.tables: [is_ms_shipped] that identifies system tables. This allows you to filter your results to just user tables if you so desire without having to join to the sys.sysobject system compatibility view’s type column (where sys.sysobjects.type = ‘U’) in order to do so.

sys.all_columns offers a row for each column for every object in a database. Many of the columns are shared with sys.types, and we pull most of the metadata from this view, but there is still one column lacking from our result set that we must pull from sys.types.

sys.types is the catalog view that stores rows relating to system and user-defined data types and their properties. The only field from this view we will need is the data type’s name, as it is the only field in our desired result set we can’t return from sys.all_columns as it pertains to column metadata.

SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
T.[name] AS [table_name], AC.[name] AS [column_name],
TY.[name] AS system_data_type, AC.[max_length],
AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded]
FROM sys.[tables] AS T
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]
WHERE T.[is_ms_shipped] = 0
ORDER BY T.[name], AC.[column_id]

Going independent as a freelancer/contractor!

Last month I made the decision to start working as a freelancer/contractor starting from the 1th of April 2011!
I’m very excited!

Launched my own company called BumbleBI IT (Dutch).

SSIS Import Flat Files with Headers

Well today I did something new 😉

First a little background information.
The project is called ‘DLT Containment’, here’s a short quote from the PID:
DLT stands for Die Level Traceability – this system contains all genealogy for our LED’s from EPI Reactor run through the LED location in the end-product we ship to a customer (full traceability).

I made a SSIS package that processes the logging files…BUT…they contain headers (see below).
It’s actually based upon the T-SQL code from my previous post.

First I’ll show the Destination table design:

Nothing that difficult:
FST_Lot_ID = (believe it or not) WO NO
FST_Date – Current datetime (smalldatetime..Grrrr)

I was struggling for quite a time on how I should do it.
After minutes (maybe an hour), I got an idea and worked on it.
Here’s the result of my brainstorm session:

Suggestions are welcome ofcourse.

HowTo: Setup SQL Server Linked Server to MySQL

This week I had to create a linked server to a MySQL database on SQL Express (development environment)….whoohooo!…NOT
But ok, it’s possible. Found this step-by-step blogpost.

Start by downloading the MySQL Connector/ODBC.
(You need to register first…)

The funny thing is, I had to enter a full provider string (instead of what the blog mentioned):

Driver={MySQL ODBC 5.1 driver};Server=;Port=3306;Database=;Uid=;Pwd=

(Port is optional and default btw)

The result:

Conclusion: It’s easy once you’ve done it before 🙂
@webhosting companies: Want reports or a PowerPivot on that thing?
Send me a mail! @BumbleBI (Dutch)