Microsoft SQL Server

Quick Tip: How to Take Snapshots of Data at Specified Intervals

A few weeks ago I was debugging something and needed to take snapshots of a table every five seconds, as a quick solution a colleague started running the following SQL and I thought I’d share it for anyone else stuck in a pinch…

WHILE (1=1)

BEGIN

	INSERT INTO tmp_snapshot
	SELECT field1,field2,field3
	FROM table1 WHERE field4 = 'value'

	WAITFOR DELAY '000:00:05'

END

Just change the time delay and the query and you’re off, of course you could be a lot cleverer with your WHILE conditions and rework this principle for loads of things.  I thought it worth posting as many database folk forget the power of the humble while loop!

Quick Fix: Invalid Parameter Number (WIS 10901) in BOXIR3.1

I encountered this error whilst testing a Stored Procedure Universe in BO XI R3.1, given that it says “Invalid Parameter Number” you would naturally assume that it was something relating to the stored procedure itself, passing parameters from the front-end to the DB in SQL, etc. but in my case it was a classic red herring error message.

The issue turned out to be permissions problem, the user executing the stored procedure did not have permissions to execute it.  After running the following SQL statement everything ran perfectly…

GRANT EXECUTE ON my_dwh.dbo.usp_do_something_useful TO PUBLIC 

… though bear in mind that you may not want to grant permissions to everyone (‘public’), I just use it as a simple example.

SQL Server Agent PowerShell Jobs Waiting for Worker Thread

My normal Data Warehouse load process runs daily in the early hours of the morning so I’m not used to sitting watching it – this morning however through lack of sleep I happened to be there and I noticed something odd: there seemed to be a queue.

What seemed to be happening is that two jobs would run simultaneously whilst other jobs sat showing a status of “Waiting for Worker Thread”.  After digging around I found that…

EXEC sp_configure;

… showed that the server was configured to automatically manage the max_worker_threads setting (value of 0), additionally…

SELECT max_workers_count FROM sys.dm_os_sys_info;

… showed that the value being used (based on the 32/64 bit status and number of cores) was indeed 512.  A quick check of…

SELECT COUNT(*) FROM sys.dm_os_threads;
SELECT COUNT(*) FROM sys.dm_os_workers;

… showed that the actual running values (54 & 46 respectively) were within expectations and nowhere near the 512 thread limit so what was happening?

Well, it turned out that whilst my jobs are a mix of OS admin tasks, legacy DTS jobs and SSIS packages most of them involve some form of PowerShell script to initialise them (usually moving files around, setting permissions, etc.).  The problem was down to an obscure setting that limits the max_worker_threads to just 2 for the PowerShell subsystem, the setting can be seen here:

SELECT subsystem,max_worker_threads
FROM msdb.dbo.syssubsystems;

In my case I just upped the running value to 40 (the same value set for CmdExec) and restarted SQL Server Agent:

UPDATE msdb.dbo.syssubsystems
SET max_worker_threads = 40
WHERE subsystem = ‘PowerShell’;

I’m yet to spot whether this has any other long term effect but things seem to be behaving fine, I will say that as part of my research I found other people saying that this setting did not persist through a restart on certain versions – I’m running 2008 R2 and that appears to be fine but it’s worth double-checking if you try this.

SQLBits by the Sea a.k.a. Nerds on a Beach!

Who’d of thought it – it’s early April and I’ve come back from a SQL Server technical conference with sunburnt arms and a smile on my face.  Usually when I tell people that I’m going to a Microsoft SQL Server conference they sarcastically say things like “have fun”, or “rather you than me” and when I tell them that it also runs into Saturday and that I’m not even getting paid for it they’re even more incredulous.  I can understand the attitude since on paper it sounds like a rather dry affair but nobody that’s ever attended a SQLBits event would ever agree with that sentiment because it’s the strangest thing, somehow against all odds the SQLBits organisers have built an event crammed full of technical talks from some of the leading lights of the industry and they’ve also made it fun.  The craziest thing of all is that the Saturday ‘community day’ is also completely free and with over 40 talks it’s every bit as good as the the paid days, couple that with the prizes and other swag on offer from the sponsors and you can’t fail to walk away better-off than you went in!

Friday’s keynote was delivered by Microsoft’s Mark Souza and gave an insightful rundown of some new features coming in the next release of SQL Server codenamed Denali, there were some interesting High Availability and Disaster Recovery (HADR) features, a new Column Store styled index, the new data visualisation / dashboard tool Crescent as well as some additional enhancements that sound minor but will probably yield a large benefit (Windows Core support, Contained Databases, FileTable).  Also sharing the stage with Mark was David Flynn, CEO of Fusion-IO who gave a short but interesting overview of their ioDrive product which can provide amazing performance increases by moving IO bottleneck from the SAN onto NAND Flash directly attached to the PCI Express bus – though some nearby graffiti (pictured) showed that there’s still some loyalty to old storage media!

My top talks of the weekend included Jamie Thompson‘s session on SSIS Performance and Vincent Rainardi‘s talk on Advanced Dimensional Modelling but my ‘best of show’ goes to Martijn Evers’ talk on Data Vault – a data modelling technique that complements use of the well established Kimball and Inmon approaches to data warehousing.  I took a lot of useful hints, tips and tricks away from many of the talks I attended but the Data Vault talk gave me something much more valuable – an entirely new idea, something I’d never heard before but will make me think about data modelling in a slightly different way.  It’s the inclusion of these satellite topics (i.e. not directly related to SQL Server) that really makes SQLBits stand out and one of the reasons I keep coming back.  Aside from the talks my other highlights were sitting on the beach eating lunch and getting my first go on the Xbox 360 Kinect!

It’s worth mentioning that the entire event couldn’t happen without the sponsors and in particular I think a lot of good will should be shown to Microsoft.  Windows users tend to see them as a giant corporate entity that they never get to interact with but somehow it’s different in the SQL Server product team and they make a lot of effort drafting in some of their best people including Thomas Kejser, Ewan Fairweather, Lubor Kollar, Mark Souza, Connor Cunningham and Andrew Fryer (who completely schooled me on Kinect boxing).  Many of these guys fly in from the US and of course Microsoft can afford it but the point is that they don’t make a big deal out of it, these guys run sessions and wander around conference offering support to anyone that asks – where else do you get that?

There’s nothing quite like listening to a talk from someone who’s utterly engaged in what they do and wants to share the information with others and that’s what SQLBits is all about so if you’re a SQL Server developer or DBA you just have to go, it’s that simple but if even you’re a .NET developer or work with other database and BI products it’s well worth attending – for now I’ll look forward to seeing you all at the next SQLBits.

Using MySQL BLOB Data via ODBC in SSIS, SQL Server & Business Objects

Whilst trying to build a centralised cross-platform alerting system I spotted a peculiar issue when trying to move the output of a SHOW FULL PROCESSLIST command on MySQL via ODBC.  It seems that the output of the SHOW FULL PROCESSLIST command returns both integers and binary (BLOB) data types even though to they eye (that is, in the MySQL Query Browser) most of the columns appear to be short text fields.

Despite the fact that the data looks like text whenever I tried to return the data into an application, I tried SSIS, SQL Server Linked Servers and Business Objects, each time the data would come back unusable or an error would be returned.  Business Objects gave me the key by declaring “This is a BLOB.” as you can see in the following screenshots…

SQL Server Integration Services

SQL Server Linked Server

Business Objects Desktop Intelligence

As with my recent post about loading data into MySQL with SSIS the saviour turns out to be an ODBC configuration setting, this time in the Metadata tab of the MySQL ODBC driver.  All you have to do is check the “Always handle binary function results as character data” and instantly your problems will be solved…

Out of a crazy fit of completeness I also took screenshots of the final results and it’d be a shame to waste them so here they are…

SQL Server Integration Services

SQL Server Linked Server

Business Objects Desktop Intelligence

Loading Data into MySQL with SSIS via ODBC (sql_mode = ANSI)

I was performing a test earlier, a proof-of-concept to show that we could use Integration Services to move data from one platform (PostgreSQL) to another (MySQL) without SQL Server itself being involved at all. 

Unfortunately I hit a slight snag on the first simple attempt, I could read the data without a hitch but despite multiple attempts at tweaking SSIS settings and reconfiguring connections I could not manage to get data into the MySQL target table.  Amongst other messages in the Output window in BIDS I could see the following error…

[MySQL][ODBC 5.1 Driver][mysqld-5.0.45-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”tblDestination” (“col1″, “col2″, “col3″, “col4″, “col5″, “c’ at line 1

After some Googling I determined that the issue was down to an incompatibility between the SQL being generated by SSIS and the default MySQL engine, specifically that MySQL was not setup to accept ANSI compliant SQL.  Most of the solutions out there seemed to require that the entire server was configured in the ANSI compliant mode which was a problem for me as it’s a shared server and I can’t go reconfiguring it at will.  Thankfully MySQL provides the ability to set the mode on a per connection level with the following command…

SET SESSION sql_mode= 'ansi';

The trick is – how to get SSIS to run this before inserting the data without messing with the package itself?  The easiest way is to have the ODBC driver send the command at the point SSIS opens the connection, this can be configured in the settings of the ODBC source…

This is a relatively common feature of ODBC drivers and allows you to setup initial configuration options for this exact reason, now I know that I will not impact any other users of the server and my SSIS package works just fine.