Software development, .Net, SQL Server, TDD, Agile, Community and other Odds and Sods
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
Thursday, June 16, 2016
SQL Server: Create a linked server from an on-premise SQL Server* to SQL Azure
* SQL Server 2008 onwards
The use of SQL Server’s sp_addlinkedserver can be a little arcane. I recently needed to connect from an on-premise SQL Server to SQL Azure for the purpose of querying and downloading data to a reporting server, and there a couple of things to note.
One is that you should ensure that data is encrypted on the wire (* and also when connecting to remote servers from SSMS).
The other is that you have to may specify the server name as a DNS name (depending on the client connection library you are using). This is the case if you receive this error message:
Here’s the working TSQL script I used:
-- If linked server already exists, drop it and any associated logins.
exec sp_dropserver 'LocalLinkedServername', 'droplogins'
-- Create the linked server:
@server = 'LocalLinkedServername',
@srvproduct = N'Any',
@provider = 'SQLNCLI',
@datasrc = '??????????.database.windows.net', -- Azure server name
@location = '',
@provstr = N'Encrypt=yes;', -- * Important!
@catalog = 'RemoteDatabaseName'; -- remote(Azure) database name
-- Create the login credentials for the linked server
@rmtsrvname = 'LocalLinkedServername',
@useself = 'false',
@rmtuser = 'remotesqlusername@??????????.database.windows.net',
@rmtpassword = 'remote password';
EXEC sp_serveroption 'LocalLinkedServername', 'rpc out', 'true';
-- Don’t elevate to distributed transactions
EXEC sp_serveroption 'LocalLinkedServername', 'remote proc transaction promotion', 'false';
---- Finally, check you can access remote data via your linked server:
select top 100 *
Another issue you might run into is if you have SQL Database auditing turned on in Azure, Azure wants all connections to be from a secure connection string, and if you run the above script from certain versions of SSMS (I believe SQL Server 2012 and below but not verified) then you might get an error saying only connections with secure connection string are allowed (despite a secure connection being specified). The fix is easy, change
Keep in mind that prior to SQL Server 2012 SP1, remote server statistics won’t be able to be used to determine query plans unless admin credentials are used (not a good idea!). Prior to SQL Server 2012 SP1 there is a workaround shown here: SQL Server Linked Servers and Remote Statistics
Thursday, June 02, 2016
SQL Server and SQL Azure: Clear Plan Cache
For on-premise SQL Servers you can run
which clears the entire server cache (provided you have the ALTER SERVER STATE permission). Many online resources and SQL Server Books Online give scary warnings about running this, but running DBCC FREEPROCCACHE will cause very few problems, even on a busy OLTP system. It will cause a small CPU spike for a few seconds as query plans get recompiled. It can be a useful tool when base-lining expensive queries or stored procedures.
If that’s not selective enough, you can free the cached plans for a single database using an undocumented DBCC command, FLUSHPROCINDB:
-- Flush all plans from the plan cache for a single database declare @dbid int; select @dbid = dbid from master.dbo.sysdatabases where name = 'MyDatabaseName'; dbcc flushprocindb(@dbid);
If you want to remove a single plan from the cache:
-- Get the plan handle (varbinary(64)) for a cached query plan select cp.plan_handle, st.text from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_sql_text(plan_handle) st where text LIKE N'%GetJournal%'; -- Remove a specific plan from the cache using its plan handle dbcc freeproccache (0x060050000C267C1030CE4EC70300000001000000000000000000000000000000000000000000000000000000);
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
Tuesday, March 04, 2014
SSAS: The Measures cube either does not exist or has not been processed
If you are attempting to deploy your existing SQL Server Analysis Services cube with calculation changes and get the following (not very helpful!) error:
then you probably forgot a CURRENTCUBE prefix on one or more of your MDX Create Member statements:
CREATE MEMBER CURRENTCUBE.Measures.[MyMeasure] AS
Sunday, December 01, 2013
SQL Server Agent Job Visualisation: SQLjobvis
SQL Server must be older than 20 years and yet it still has no agent job schedule visualiser, despite the fact it has been asked for many times. How can that be? Go figure…
I needed one at work recently to identify schedule clashes, so I thought “I wonder how hard it would be to write something fairly basic”. Then programmer laziness swiftly kicked in and the next thought was “If this problem is a pain for so many, then someone must have written something already…?"
And of course, they have: SQLjobvis
It’s basic, simple, works great, and it’s free!
It instantly identifies:
Monday, May 13, 2013
Office 2013 Rant: Not wearing any Clothes!
Having not been able to automatically upgrade to the 64bit version of Office (having the 32 bit version of 2010 installed), I installed the 32bit version of Office 2013, so I could try out a few of Excel’s new features (Yeah, I know I should have VM’ed, but I trust those programmers at Microsoft…)
Unfortunately, just like VS2012, Microsoft have broken just about every well established and tried and tested UI design rule: and not for the better. Office 2013 looks as if the folks at Microsoft have confused the concepts of reducing too much ‘Chrome’ with proper visual ‘Demarcation’. Having everything blend into everything else is not a good UI, by any measure. I’m gobsmacked that they could have produced something so poor. It’s simply, bloody awful! Microsoft: The Emperor isn’t wearing any clothes!!
I don’t believe this design ‘strategy’ was done to benefit users. It was done so that Microsoft can seamlessly (supposedly) integrate their mobile and desktop markets. This is not a strategy –> “We need to shift our emphasis to the mobile market (we sure as hell aren’t selling many versions of Windows 8 for the desktop), lets change our UI to ‘fit’ tablets. Bugger the existing desktop users…”. I’m sorry but that’s a Fail.
BTW, looks like some Microsoft Teams didn’t get the memo about using All CAPS!! (Excel 2013 [Vanilla] Screenshot)
Was making Windows 8 share a similar interface to the phone and tablets done to benefit end users? Seems unlikely. Windows 8 is shaping up to be another Vista (Microsoft have been very shy the last 2 quarters and not released the sales figures for Windows 8). Looking forward to having the Start button back? Windows codenamed ‘Blue’ is coming soon…Hurrah!
Sad thing is, I really want to like Microsoft products. SQL Server is by far the best software I’ve ever used. It is awesome.
So having said all that, I thought I’d give 2013 ago to see if I could subdue my bias and get used to the rather poor UI. First email I try to send, Outlook 2013 crashes. Restart. Try again, it crashes. I’ve used Outlook 2010 every day for over 2 years and haven’t had a single crash.
Uninstalled Office 2013. HELLO Office 2010 !
Tuesday, May 07, 2013
Office 2013 64 bit Setup
I’ve used every version of Microsoft Office pretty much since it came out. I currently have the 32 bit version of Office Pro 2010 installed. Attempting to install the 64 bit version of Office 2013 to evaluate Excel’s Power View results in:
Wouldn’t it be nice if it had the option to do this for me and migrate all my settings?!? Especially since I don’t recall there being a 64 bit version of Office 2010 on release (or was it just that it had issues with drivers and add-ins?).
Friday, April 12, 2013
SQL Diagnostic Runner Updated
Several users reported a bug running SQLDiagCmd; I’d introduced a silly error in the command line options. [SQLDiagUI was unaffected]
I’ve uploaded an updated version (v22.214.171.12401) which you can download from the links below (or from any of the previous posts):
Sunday, March 10, 2013
No, it’s not Uncle Ernie!
It currently supports these database engines (in various versions): MySQL, PostgreSQL, MS SQL Server, Oracle, and SQLite
Wednesday, March 06, 2013
.NET Regex: Character Class Subtraction
You’ve heard of a positive match character group  and a negative match character group [^]. But did you know there is also a Character Class Subtraction? I didn’t. It’s supported in .NET but not in the majority of RegEx flavours.
Using this format, the pattern
can be used in .NET to match all alphanumeric characters (any word character) excluding the letter v and numbers 123.
The MSDN page for the .NET Regex definitions doesn’t seem to appear high in the search indexes, so bookmarking here for my future reference: Character Classes in Regular Expressions
This is useful for comparing Regex capabilities in different languages: regex flavor comparison chart
Tuesday, February 26, 2013
SQL Diagnostic Runner Updated
Thanks to Todd who reported a bug when connecting with username and password (I messed up the connection string).
I’ve uploaded an updated version (v126.96.36.19957) which you can download from the links below (or from any of the previous posts):
[Servername will now take a semi-colon separated list of servers to run against, but with the limitation of using the same credentials and diagnostic script.]
Sunday, February 24, 2013
Parsing Command Line Arguments
If you want a full blown Command Line Parser then there are several good options available:
[I used the Command Line Parser Library recently in the SQL Diagnostic Runner I wrote.]
If you just want a very basic parser, supporting simple options in the format /argname:argvalue then you could use this:
Monday, February 18, 2013
Largest .NET Object….
In .NET versions prior to .NET 4.5, the largest allocation for any single object is 2GB.
On 64-bit platforms, in .NET versions 4.5 and greater, it is possible to enable the allocation of arrays that are larger than 2 GB in total size (but NOTE this does not change other limits on object size or array size):
The default setting is not enabled.
You can enable this feature by using the gcAllowVeryLargeObjects element in your application configuration file:
Sunday, February 17, 2013
Guide to Recognizing Popular Timestamp Formats
Bookmarking for future reference: Guide to recognizing popular timestamp formats
Thursday, February 07, 2013
SQL Diagnostic Runner Updated
David Vogelaar (and others) kindly reported a bug: I wasn’t converting invalid filename characters when using an SQL Server instance name for the auto-generated results filename. This has been fixed. You can download version 1.0.2 from the previous download links or the ones below.
There is a known issue:
The results file is generated OK but sometimes when you open it in Excel a seemingly ‘nasty’ message is shown:
The file will open OK if you chose to recover: simply accept the prompts and save over the original. I will fix as soon as I can.
MSN, Email: mitch døt wheat at gmail.com