Microsoft SQL Server – Pinter Computing

No connection string named ‘…’ could be found in the application config file.

Two-tier applications separate the presentation layer and the data layer, and all database access related objects are located in the data-tier. When the application runs, the config file of the main project is read. In web applications, it is the web.config, in console applications the app.config file. When you get the following runtime exception Exception thrown: ‘System.InvalidOperationException’ in …

Continue reading “No connection string named ‘…’ could be found in the application config file.”

How to restore a Microsoft SQL database from backup with the Microsoft SQL Server Management Studio (MSSMS) user interface

There are multiple reasons to restore a database from backup. One of them can be disaster recovery, the other is to bring the production database to the developer machine. In both cases, the computer already has the old version of the database. In the new version of Microsoft SQL Server Management Studio (MSSMS) we cannot find …

Continue reading “How to restore a Microsoft SQL database from backup with the Microsoft SQL Server Management Studio (MSSMS) user interface”

Disable database triggers in Microsoft SQL databases

If you need to temporarily disable triggers in Microsoft SQL databases during database maintenance use the following script –Disable triggers on all tables DECLARE @enable BIT = 0; DECLARE @trigger SYSNAME; DECLARE @table SYSNAME; DECLARE @cmd NVARCHAR(MAX); DECLARE trigger_cursor CURSOR FOR SELECT trigger_object.name trigger_name, table_object.name table_name FROM sysobjects trigger_object JOIN sysobjects table_object ON trigger_object.parent_obj = …

Continue reading “Disable database triggers in Microsoft SQL databases”

Disable foreign key constraints in Microsoft SQL databases

Databases do not allow the deletion of rows if those are referenced in other tables with the foreign key constraint. You can turn off the validation of foreign keys in Microsoft SQL databases for the duration of the maintenance with the following script. –Disable foreign keys on all tables DECLARE @table_name SYSNAME; DECLARE @cmd NVARCHAR(MAX); …

Continue reading “Disable foreign key constraints in Microsoft SQL databases”

How to add a new table to an existing Microsoft SQL Server replication publication

To add a new table to an existing MS SQL Server publication Remote desktop into the database server, or a server in the same domain of the database server Start Microsoft SQL Server Management Studio (SSMS) Connect to the database with ComputerName\SQLServerName If there is only one instance of Microsoft SQL Server on the computer …

Continue reading “How to add a new table to an existing Microsoft SQL Server replication publication”

There is already an open DataReader associated with this Command which must be closed first

When your MVC 5 web application page tries to read the database using the Entity Framework in a loop you may get the following error message: InnerException: System.InvalidOperationException HResult=-2146233079 Message=There is already an open DataReader associated with this Command which must be closed first. Source=System.Data This usually happens when a cshtml page receives an IEnumerable<> …

Continue reading “There is already an open DataReader associated with this Command which must be closed first”

DbArithmeticExpression arguments must have a numeric common type

The Microsoft Visual Studio 2013 Entity Framework database query does not support the subtraction of two dates to calculate TimeSpan. For example the following statement will throw a runtime error: site.VisitFrequencySec < ((TimeSpan)(dtNow – site.LastVisitDate)).TotalSeconds) To solve the problem create a computed database column to calculate the difference between the dates and use the computed …

Continue reading “DbArithmeticExpression arguments must have a numeric common type”

The underlying provider failed on Open

When you get the following error message in Microsoft Visual Studio 2013 check the inner exception. System.Data.Entity.Core.EntityException was unhandled HResult=-2146233087 Message=The underlying provider failed on Open. Source=EntityFramework The inner exception gives you detailed information on the real cause of the error. Most of the time the inner exception is the following: InnerException: …

Continue reading “The underlying provider failed on Open”

The Entity Framework provider … could not be loaded

When you create a new Microsoft Visual Studio 2013 project that uses the Entity Framework you may get the following error message: The Entity Framework provider type ‘System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer’ registered in the application config file for the ADO.NET provider with invariant name ‘System.Data.SqlClient’ could not be loaded. Make sure that the assembly-qualified name is used …

Continue reading “The Entity Framework provider … could not be loaded”

How to migrate the Visual Studio 2013 user database to SQL Server 2008

When you create a new web application in Microsoft Visual Studio 2013 the user database it creates is version 706. SQL Server 2008 can only open databases up to version 663 or earlier. To migrate the new Visual Studio 2013 databases to SQL Server 2008 script the database tables and create them again in the …

Continue reading “How to migrate the Visual Studio 2013 user database to SQL Server 2008”