“SQL Server Configuration Manager” gives “Invalid class [0x80041010]” when starting.


If you experience Invalid Class [0x80041010] when starting SQL Server Configuration Manager, see Michael Aspengren’s MSDN Blog Post for the solution. This solution fixes the original issue, but then you are presented with a second issue: Provider load failure [0x80041013].

To fix the Provider load failure [0x80041013] issue, locate your SQL Server installation medium. In my case I am using SQL Server 2016 Developer Edition. Then run sql_common_core.msi, which in my case is located as follows: en_sql_server_2016_developer_x64_dvd_8777069\x64\Setup\sql_common_core_msi\sql_common_core.msi. Note: You will need to run this installation as an administrator in order for it to install. So open a command prompt as administrator, and then type msiexec /i sql_common_core.msi and press enter. You should find that you can now open SQL Server Configuration Manager 2016.

Silverlight with WCF Services and SQL LocalDB Development and Deployment using CassiniDev.


Problem: You are required to deploy a Silverlight application that accesses a database via WCF Services onto a standalone computer that does not have IIS, IIS Express, or SQL Server Express installed.  Your end users are people with very limited or no real technical background that find even simple day-to-day IT tasks difficult. What database and web server do you use, and how can you make the installation as easy as possible for your end users with little, or preferably no configuration?

Solution.

  • Install SQL Server 2012 or higher on your development computer.
  • Connect to the LocalDb server instance in management studio using: (localdb)\v11.0.
  • Create a new query and enter the following command to create     your database:

    create database databaseName on (name=’databaseName’, filename=’fileLocation\fileName.mdf’)

  • Create your tables.
  • Create a new Visual Studio Silverlight with WCF RIA Services project.
  • Right click on the web project and select Manage NuGet Packages…
  • Select the online option and type entityframework.
  • Install EntityFramework.
  • Add a model.  The server name should be: (localdb)\v11.0.
  • Build your project.
  • Create your domain service.
  • Build your project.
  • In your Silverlight application import the System.ServiceModel.DomainServices.Client namespace, and the namespace of your web project.
  • Create a member variable for your domain context and IEnumerable of your domain object.
  • In the appropriate method that will instantiate the domain service add the relevant EntityQuery and LoadOperation lines of code.
  • Add you method that will handle the LoadOperation.Completed event, check that args is not nothing.
  • Use your entities as required.
  • Now add a Windows Form application.
  • Download and install CassiniDev.
  • In your Windows Forms application add a reference to the CassiniDev4-lib.dll.
  • For the tutorial on Using CassiniDev to host ASP.NET in your application.
  • Build your solution.
  • Copy the deployable portions of the web project into the WebContent folder located in the Windows Forms executable’s root folder.

On the client computer that you are going to deploy the solution on you will need to install:

  • Silverlight 5;
  • the .NET Framework 4.02 update (NDP40-KB2544514-x86-x64.exe);
  • and the SQL Server Local Database (SqlLocalDB.msi).

There is the Express Edition of InstallShield which is free to use.  Use this to create a Basic MSI project that will silently install the above components, and copy your application onto the client computer.  Create a desktop shortcut.  Run the executable from the shortcut to test that the application is working.

Inserting Strings with Apostrophes Into SQL Server


Strings in SQL Server are enclosed in single quotes. So if you have a string such as a person’s name like O’Mally, then the string would be ‘O’Mally’.  This obviously breaks the SQL statement.  To overcome this issue you need escape the apostrophe by doubling it up.  So to use our previous example the modified string would be ‘O”Mally’.

Unable To Attach Database in SQL Server Management Studio


If you have a SQL Server database that you have created in Visual Studio, and you want to attach the database in SQL Server Management Studio but get an access denied error, then give read permissions to the account your SQL Server is running under such as NETWORK SERVICE on the folder under which your database files are stored.

T-SQL Alternative To Using Cursors


Today I found myself writing a T-SQL Cursor statement to condense multiple rows in a table to a single row of data in which the data column was a comma separated key/value list representing each of the columns in the source table, and each row of data was separated by a pipe ‘|’.

The reason behind this is that the data was to be transformed from a normalised table in one database for transference to a new table in a different database.  The new table in the new database has one row that represents the source table in the source database.  The new table consists of 3 fields Name AS NVARCHAR(256) NOT NULL, Value AS NVARCHAR(256) NOT NULL DEFAULT N”, and Data AS NVARCHAR(MAX).  The Name and Value columns provide the compound primary key.

I started off by writing a cursor statement, but I ran into problems.  So I did a bit of Googling and came across the article Using T-SQL to Concatenate Data Into a Variable published in SQL Server Pro June 25, 2002 by Brian Moran.

This article provided a very simple SQL statement that negates the need to use T-SQL cursors, and it is fast for my purposes.  The T-SQL statement below is my modified version for doing string concatenation.


USE [DatabaseName]
GO

DECLARE @DATA NVARCHAR(MAX)
SET @DATA = ”
SELECT @DATA = @DATA + ‘Data to be concatenated’
FROM [DatabaseTable]
SELECT @DATA AS DATA

T-SQL String Contatenation


I came across a situation today working on a new piece of software where I needed some data from T-SQL.  The product is a VB.NET modernisation of existing VB6 source code that accesses a SQL Server 2008 R2 database.  The database for the VB6 application uses multiple tables with multiple rows for various resources.  As for the VB.NET software, this uses a single table for resources.  The columns in the new database’s resources table is Name, Value, and Data.  These columns are all strings and Name, Value is a compound primary key.

The problem I had to solve was how do I concatenate each column of each row into a single comma separated string of key value pairs, and then concatenate each string of each row separated by a pipe ‘|’ to form a single pipe separated string.  The final format of the string should be: k1=v1,k2=v2,k3=v3|k4=v4,k5=v5,k6=v6|.

I started using cursors to achieve my goal, but for some reason that I could not figure out I could not get a variable to update.  So after some searching, I found that the method I was using was the one used for SQL Server 2005 and earlier, and that there is a new way of concatenating strings in later versions of SQL Server.

To achieve the above use the T-SQL STUFF command.

INSERT INTO [Database].[Schema].[Table]
VALUES('Name', 'Value', (SELECT STUFF((
SELECT
' k1=' + v1 + ',k2=' + v2 + ',k3=' + v3 + ',k4=' + k4 + ',k5=' + k5 + ',k6=' + k6 + '|'
FROM [Table]
FOR XML PATH ('')),1,1,'')))

In the above code k stands for the field name, and v is the data for that column. And that’s all there is to concatenating all the columns together, and then concatenating each row to produce a single line of data. The good thing about this statement is that you can select from one database table, and insert directly into a table in a different database.

Installing SQL Server on a Domain Controller


Installing SQL Server on a Domain Controller is not supported by Microsoft.  If you must install SQL Server on a Domain Controller, then you must create a NT AUTHORITY\NETWORK SERVICE account on that computer.  Information on how to do this can be found on the MSDN website: NetworkService Account.

Novell have a good visual guide to setting up a NETWORK_SERVICE account for their software.  This may be a good place for you to receive guidance: Steps to setup NT AUTHORITY\NetworkService as the logon account.