Posts

Hey, I rewrote UNION by way of UNPIVOT

Yesterday a team mate of mine asked me to review a piece of SQL script that looked like a good candidate for UNPIVOT. Below is the amended version of that code (I obfuscated the data and added a simple data initialization using the WITH clause): ;with u as ( -- data setup select * from ( values(1, 1, 1, 1), (2, 1, 1, 0), (3, 1, 0, 0), (4, 0, 0, 0) ) t(UserID, HasDbSkills, HasBackendSkills, HasFrontEndSkills) ) select * from ( select UserID, 0 as SkillType, 1 as DevelopmentSkill from u where HasDbSkills = 1 union select UserID, 1 as SkillType, 1 as DevelopmentSkill from u where HasBackendSkills = 1 union select UserID, 2 as SkillType, 1 as DevelopmentSkill from u where HasFrontEndSkills = 1 ) t(UserID, SkillType, DevelopmentSkill) order by UserID, SkillType Indeed, after a quick analysis, this script turned out to be ripe for UNPIVOT:
Post a Comment
Read more

Clean up app uninstall failing with error 0x80070490

Over recently I've been busy building the MSIX package for our WPF application. I would configure the pipeline, run it, fail, and start over. At a moment I found myself unable to uninstall the package I built and installed on my Windows 10 machine, as it wasn't listed in the Programs. When I tried to reinstall the app using the new, valid installer I got the message saying: App installation failed with error message: Deployment Add operation with target volume C: on Package x from: y_1.0.0_neutral__8wekyb3d8bbwe failed with error 0x80070490. I searched for a solution on  stackoverflow , learn microsoft and some other resources but to no avail. And boomer! a super simple solution emerged from the back of my mind: Remove-AppxPackage y_1.0.0_neutral__8wekyb3d8bbwe In all likelihood  Remove-AppxPackage  picks up the installation package and runs the uninstall but it's just an educated guess I cannot back by any code or expert comments. That's all folks! PS A quick referen
Post a Comment
Read more

Initializing xslt variable with static content

I had to use a variable in an xslt template. The value for that variable would be returned by a stored procedure at a SQL Server DB. So, I decided to populate the variable with static content:     <xsl:variable name="hideColumns" select ="'ISBN,author_id,vendor_id'" /> Populated with the static content, the variable proved to be a good replacement for the real-life data. So, as soon as the stored procedure was deployed, my xslt template was readily updated with the appropriate select statement in the hideColumns variable declaration.
Post a Comment
Read more

T-SQL: COALESCE returns int for integer operations

DECLARE @can_import BIT PRINT CAST (SQL_VARIANT_PROPERTY( COALESCE (@can_import, 0), 'BaseType') AS VARCHAR(20)) Result: int
Post a Comment
Read more

Filter XML elements by their namespace using XSLT

XSL Transformations (XSLT) is a powerful tool that can do much chore for us developers. Occasionally, XSLTs can compete with the other tools intended to transform XML. Look, for example, at the snippet that follows: < xsl:template match =" o:* " /> It strips out all the elements that are defined in the namespace referenced by alias o . Explanation A normal template expression has a format similar to the one of below: < xsl:template match =" author-group " >   < xsl:apply-templates select =" author " /> </ xsl:template > This example processes all the author children of the author-group . By default, the XSLT processor processes each matched element only once. After an author-group element is selected, the processor outputs the author children of the matched element to XML. So, if the xsl:apply-templates element is omitted, the children of the source element are skipped during the transformation.
Post a Comment
Read more

CREATE DATABASE (SMO)

Create a SQL Server Database by using SMO In this small tutorial I will show you how to create a database with SMO and C# in Visual Studio 2008. I will use a Console application. Prerequisites: SQL Server 2005 is installed Visual Studio 2008 is installed Code Assemblies Your C# project must reference: System System.Data Micrososft.SqlServer.ConnectionInfo Micrososft.SqlServer.Management.Sdk.Sfc Micrososft.SqlServer.Smo Namespaces The source class file must use: System System.Data.SqlClient Micrososft.SqlServer.Management.Common Micrososft.SqlServer.Management.Smo class ConsoleSmo {    public static Main()   {      string connLocal = "Data Source=(local); Integrated Security=SSPI;"      SqlConnection connection = new SqlConnection (connLocal);      Server server = new Server ( new ServerConnection (connection));      Database db = new Database (server, “TestDb” );     db.Create();     connection.Close();   } } Explanations This code opens the connectio
Post a Comment
Read more

CREATE DATABASE (T-SQL)

Create a SQL Server Database using T-SQL Your need to create a database by using T-SQL. The simplest command you can issue to achieve this is: CREATE DATABASE <database_name> If you open the database, you will notice that its metadata has been set to some values and perhaps, the database already contains tables, stored procedures, UDFs and other objects. How does this magic work? Actually, the SQL Server derives a new database from the model database. Below is how  MSDN explains this process: All user-defined objects in the model database are copied to all newly created databases. You can add any objects, such as tables, views, stored procedures, data types, and so on, to the model database to be included in all newly created databases. When a CREATE DATABASE database_name statement is specified without additional size parameters, the primary data file is made the same size as the primary file in the model database. Unless FOR ATTACH is specified, each new database inherits th
Post a Comment
Read more