Category: SQL Server

SQL Server and T-SQL Development

Cluster terminology – What “Active/Active” actually means

As a follow-up to my last entry (attempting to clear up some Windows Clustering terminology), I’ve found an article that makes another distinction that I forgot to include – the difference between an active/passive and an active/active cluster:

The misconception of active/active clustering (a la AirborneGeek.com)

The understanding among those new to cluster seems to be that a/a vs. a/p is a licensing question, and then if you’re licensed for it, you just turn it on. In reality, it really just describes whether you have clustered services living on only one node or split between both nodes (during normal operation – during a cluster failover, any cluster might be active/active for a short period of time. Or, I suppose, your cluster is active/active if your quorum drive lives on the opposite node from your clustered service). There’s no load-balancing involved in clustering at all – at any time, only one node owns a particular resource, and only that node is responding to client requests for that resource.

In SQL Server 2012 AlwaysOn, the new high-availability feature, the SQL Server service is running on both cluster nodes, but client access (through the “Availability Group”) is controlled by the cluster service. That means that all clients making a connection go first to the active server, and then the SQL Service there might send them to get their data from one of the other nodes (it’s worth reiterating here that, in AlwaysOn, SQL Server isn’t clustered, but the SQL services operate independently on each node).

Clean up vendor names and other data with unwanted numbers/characters

In creating an accounting report, the vendor names we get back from our credit card processor needed some major clean-up: “52334SOUTHWESTAIR1234”, “ABD2343-BLUE DINER 843”, and so on. I initially found a great function for this from Pinal Dave:

http://blog.sqlauthority.com/2007/05/13/sql-server-udf-function-to-parse-alphanumeric-characters-from-string/

But I wanted to make a few enhancements to it:

  1. He leaves numbers in the string and I’d like to remove them
  2. I’d like to combine multiple spaces in a row into a single space, but leave spaces intact

The first is pretty easy to do – just remove the [0-9] and add a space to the PATINDEX. The second one uses a trick from another post I did a few years ago.

Here’s the modified version:

CREATE FUNCTION dbo.UDF_ParseAlphaChars2
(
   @string VARCHAR(8000)
)
RETURNS VARCHAR(8000) WITH SCHEMABINDING
AS
BEGIN
   DECLARE @IncorrectCharLoc SMALLINT
   SET @IncorrectCharLoc = PATINDEX('%[^ A-Za-z]%', @string)

   WHILE @IncorrectCharLoc > 0
   BEGIN
      SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
      SET @IncorrectCharLoc = PATINDEX('%[^ A-Za-z]%', @string)
   END

   -- Trim groups of spaces into single space
   SET @string = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@string,' ','<>'),'><',''),'<>',' ')))

   RETURN @string
END
GO

--Test
SELECT dbo.UDF_ParseAlphaChars2('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
SELECT dbo.UDF_ParseAlphaChars2('52334SOUTHWESTAIR1234')
SELECT dbo.UDF_ParseAlphaChars2('ABD2343-BLUE DINER 843')
GO

SQL Server Contention Monitor (super-alpha) posted to Codeplex

(Obviously with Codeplex long gone, I migrated over to Github – I don’t have the commit history anymore but maybe this code is still interesting to somebody)

In response to some conversation in an Experts-Exchange question, I decided to post an application I wrote about four years ago and never did anything with (or add much polish, as you’ll see). It’s a small VB.NET application that polls MSSQL servers and displays information about currently blocked SPIDs, as well as the block chain and age of blocked processes.

It’s something I’ve used when I troubleshoot blocking on a SQL Server, and generally leave running in my system tray – it will pop up a balloon when there’s a blocked process that lasts longer than some pre-determined threshold, and then optionally pop another balloon when it’s resolved. It works well as an early warning system for a critical server, and I’ve often used it when I’m performing maintenance on a production server that I’m concerned might block legitimate use – running the application lets me know within a few seconds when I’ve gotten in somebody’s way so I can act accordingly.

It’s still very much a work in progress and is pretty rough around the edges (and well inside the edges), but I’ve posted it to a Codeplex project Github repository for anybody else who might find it useful. Though I’m working on a few other things right now and haven’t done any development with this app in almost four years, I’ll keep an eye on the issues list at Codeplex Github (or feedback here) and try to address anything that comes up.

Without any further ado, here it is!

https://mssqlblockmonitor.codeplex.com/ https://github.com/rwmnau/sql-contention-monitor/

Presenting a pair of lightning sessions at SQL Saturday in Phoenix

I’ll be presenting a pair of lightning sessions at SQL Saturday in Phoenix, AZ, on April 28th – if you’re in the area and up for a day of free training on everything database-related, as well as some networking time with your fellow DBAs and Developers, please join us!

Here’s the summary for my two lightning sessions (they’ll be 15 minutes each):

Automating SQL Server source control – This session will look at a quick and easy process that takes regular snapshots of object definitions in the database and stores any changes.

Representing SQL Server data spaces visually – This session will take a quick look at a Codeplex Github project that does this, SQL Space Map, and how it accomplishes it by leveraging a library from Microsoft Research (Obviously with Codeplex no more, I’ve migrated this code to Github, though it still hasn’t been updated in probably 10 years)

If you’d like to see what else is planned for the day, http://sqlsaturday.com/131/schedule.aspx. You can also follow the event on twitter at #sqlsat131

Export from SQL Server to XLS and email results

Sometimes you want to take some query results and export them directly to an XLS file – here’s how you can set that up in SQL Server. The biggest caveat is that you need to run it from an x86 instance of SQL Server – the x64 instance won’t have access to the Jet driver needed to write the Excel file (Microsoft.Jet.OLEDB.4.0), where the x86 version will. In fact, we maintain an older x86 instance of SQL Server for random processes like this that need it – x64 is better in almost every case, but we can’t see to completely ditch x86… 🙂

I use a stored proc that I call from a SQL Agent Job, which works great. The actual process is a bit awkward – for starters, you’ll need access to xp_cmdshell. SQL Server can’t create a new Excel file from scratch, so you have to keep a blank Excel file around, make a copy of it, and then insert into the copy to get your final result.

That said, here’s the code to generate the XLS file from your query results:

SELECT Column1, Column2, Column3, Column4
  INTO ##YourTempTable
  FROM SomeOtherTable

SET @Folder = 'C:\Temp\'
SET @DocumentBlank = 'Your Document - Blank'
SET @DocumentLong = 'Your Document - ' + CONVERT(VARCHAR(10), GETDATE(), 120)

DECLARE @CMD NVARCHAR(4000)
SET @CMD = 'COPY "' + @folder + @DocumentBlank + '.xls" "' + @Folder + @DocumentLong + '.xls"'
exec master..xp_cmdshell @CMD

-- Export the Excel sheet
SET @CMD = 'insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
	''Excel 8.0;Database=' + @Folder + @DocumentLong + '.xls;'',
	''SELECT * FROM [Sheet1$]'')
	select Column1, Column2, Column3, Column4 from ##YourTempTable'

exec sp_executesql @CMD

Once that’s exported, you can just set up the email process using sp_send_dbmail and attach the file you just generated:

DECLARE @Body VARCHAR(2000)

SET @Attachments = @Folder + @DocumentLong  + '.xls'
SET @Body = 'Your file has been generated for ' + CONVERT(VARCHAR(10), GETDATE(), 120)

exec msdb..sp_send_dbmail @profile_name = 'YourMailProfile',
	@Recipients = 'Recipients@YourDomain.biz',
	@subject = 'Your file is ready',
	@Body = @Body,
	@file_attachments = @DocumentLong