“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.


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((
' 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.

SQL Server Error 15100: Could Not Open File

In the unfortunate event that your database becomes corrupted, check your event log in the first instance.  If you receive an error as follows:

FCB::Open Failed: Could not open file .mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15100).

You will need to detach and attach the database. If you are unable to do this then the following method will work, but you will stand a chance of losing all your data. So be very careful and think before you act.

In Configuration Manager stop the SQL Server. Move the MDF and LDF to a different location. Open SQL Server Management Studio as Administrator and delete the database. Move the MDF and LDF back to the same location. In SQL Server Management Studio, attach the database. You should now be up and running.

Hopefully, all your data should be there. If you have lost your data, perform a database repair from a backup. Remember, it is a good practice to perform nightly backups of a SQL Server. In my experience providing customer support for our products at the company where I work, it is surprising how many establishments do not perform server backups at all! When I encounter this situation, I always advise them on performing regular server backups, and from within our software regularly saving archives in case they ever need to recover a damaged database or lost data.

Data Management British Standards

There are two British Standards that deal specifically with data management. These are Principles of Good Practice for Information Management (BSI-DISC PD 0010), and Code of Practice for Information Security Management (British Standard BS7799 – now ISO 27002).

Principles of good practice for Information Management (BSI-DISC PD 0010) by Bernard Dyer and Bill Mayon-White is intended for use by all individuals and organisations with a duty to create and maintain records. It provides a practical framework to guide organisations through the operation and development of new methods and technologies for managing information. The framework is structured around the following five core principles:

  1. Recognise and understand all the types of information being managed
  2. Understand the legal issues and execute ‘duty of care’ responsibilities
  3. Identify and specify business processes and procedures
  4. Identify enabling technologies to support business processes and procedures
  5. Monitor and audit business processes and procedures

BS7799 (BS7799-2:2005), which now has the international number ISO 27001:2005, is the international best practice information security management standard, defining and guiding Information Security Management System (ISMS) development.

IT Governance: A Manager’s Guide to Data Security and ISO 27001 / ISO 27002 contains everything necessary to help you implement an ISO 27001 (BS7799) Information Security Management System (“ISMS”), in any organisation, in any sector, anywhere in the world.