I was going through a couple of emails this morning when I found one from Pinal Dave. It was exposing a really nifty T-SQL trick that I will surely used very soon.
Very often, we have a CSV field in a database and we want to split on the separator. This can be done easily. Have a look at this snippet:
DECLARE @t TABLE
INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')
SELECT * FROM @t
SELECT EmployeeID, LTRIM(RTRIM(m.n.value('.','varchar(8000)'))) AS Certs
SELECT EmployeeID,CAST('' + REPLACE(Certs,',','') + '' AS XML) AS x
) AS t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
The snippet starts by creating an in-memory table with some CSV values. then the trick is to convert this field to an XML value which makes it easier to access each value inside it.
If you have a small amount of rows, that trick can really help you but if you have a large result set this same trick could hurt you because we all know that SQL Server is not really at its best with XML!
BTW, this trick was found on http://blog.sqlauthority.com/2015/04/21/sql-server-split-comma-separated-list-without-using-a-function/ which also link to other ways of achieving the same thing using a stored proc and a UDF.
I have been invited by the SQL Pass Global French Virtual Chapter to present 2 webcasts.
The first one will be presented Saturday, May 2nd. It will be presented in French and it will be about SQL CLR Integration (related to my latest article). More details are available from http://globalfrench.sqlpass.org/Home.aspx?EventID=2921.
The second one will be presented Saturday, May 16. This one will show how to backup Microsoft SQL Server 2014 databases to Azure, how to copy database to SQL Databases (still in Azure). More details on this one are available from http://globalfrench.sqlpass.org/Home.aspx?EventID=2922.
Recordings should be available some time after the live events from the archive.
Yes another (surely) great free virtual conference is coming in May. The 2-days event will be held May 14 and 15.
This conference will be hosted by MVPs. It offers 5 tracks (IT Pro, Developer, Consumer, Spanish, and Portuguese).
Join me! Register for the MVP Virtual Conference and feel the power of community! #MVPvConf #MVPBuzz
I just published a new article in which I revisit something I have written in May 2011. In that article, I was showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still read this article from here.
Creating a DLL to be hosted in SQL Server let you run code directly in the database. You may think of it as a Stored Procedure written in VB or C#. it might be helpful to prevent doing large processing on the client after having transited a lot of data on the network wire.
Lately, I was reviewing training material on that topic to see if it was still accurate and found out that many things have changed since then. We are now using Visual Studio 2013 (or 2015 in CTP) and SQL Server 2014.
You can read it from http://emoreau.com/Entries/Articles/2015/04/SQL-CLR-Integration-in-2015-year-not-product-version.aspx
Believe me. I searched the web not so long ago to find this information and couldn’t find anything complete. This article will try to document it.
I was using DataAnnotations to validate my classes before saving to Entity Framework 6 and tried to use IDataErrorInfo using the same validation attributes already in place instead of recreating them.
This seem simple but partial classes and the MetadataType attributes add some complexity to the task.
I made the demo so that even if you are not using the MetadataType and partial classes, you can still use this code presented in this article.
You can read this article from http://emoreau.com/Entries/Articles/2015/03/WPF-Using-IDataErrorInfo-on-a-class-exposing-validation-attributes-from-a-MetaData-class.aspx.
If you ever tried WPF (and Silverlight had the same issue) and had any kind of error in your binding, you know that it just fail silently. Of course you get a warning in your Output window but are you always looking there to see if something went wrong? I don't!
After I lost some time yesterday digging for an error that was finally only a typo in a command name, I started looking around for an easier way of debugging binding errors.
I found one that just shows a message box on the screen whenever a binding error occurs. In fact, it is just a listener filtering only for binding events.
So far, I have to say that I rather have a message box showing me that I mistyped the command name rather than searching for almost an hour.
This little gem can be downloaded from http://tech.pro/tutorial/940/wpf-snippet-detecting-binding-errors
Mercredi 11 mars à 10h00 heure de l’est (GMT-5), Guy Barrette animera le premier webinaire en français sur OzCode. Lors de ce webinaire, Guy vous montrera quelques-unes des meilleures fonctionnalités de OzCode. Vous apprendrez comment utiliser OzCode afin d’améliorer vos sessions de débogage et ainsi, trouver les erreurs et corriger beaucoup plus rapidement.
Pour vous inscrire, visitez http://blog.guybarrette.com/post/2015/03/04/Debogage-magique-avec-OzCode-webinaire-en-francais.aspx
We never get tired of free training!
The dotNetConf 2015 is another these free events and it is organized by Microsoft and the community. The virtual conference will be help March 18 & 19.
Visit the official web sites for all details and to (optionally) register at http://www.dotnetconf.net/
Lately, I have spoken at 2 Azure events in Montréal.
When preparing for the 2 events and while demoing in the 2 events, I created many Azure SQL Databases and Azure SQL Servers.
Now it is the time to cleanup not to use too much credits.
Azure SQL Databases are easily deleted using either the old portal (https://manage.windowsazure.com) or the new portal (https://portal.azure.com/).
But deleting Azure SQL Servers is another story. There is nothing (yet) letting you do it.
I searched and found that we need to use Azure PowerShell. You need to use Remove-AzureSqlDatabaseServer as shown in https://msdn.microsoft.com/en-us/library/dn546728.aspx. To install Azure PowerShell, check http://azure.microsoft.com/en-us/documentation/articles/install-configure-powershell/. If it is your first experiments with Azure PowerShell, you might want to have a look at http://michaelwasham.com/windows-azure-powershell-reference-guide/getting-started-with-windows-azure-powershell/.
Lately, I talked about Microsoft Azure in 2 events. When it is time to explain the different levels of services offered by Azure, I normally rely on this slide:
It not exactly clear for everybody. Yesterday, I showed the following slide (I don't even remember where I took it from) that makes a great analogy with pizza and everybody now understand.
And this morning, I was catching up on some .Net Rocks episode, and they mentioned a brand new "as a service" that I wasn't aware off!
If you have 10 minutes, go try http://foaas.com/ and have a good laugh!