Entity Framework Model SQL datatype Error System.ArgumentException: The version of SQL Server in use does not support datatype 'datetime2

It is common practice we develop applications in latest environment but in production environment we find a downward configuration which lead us to some strange exception in application. If entity framework (EF) is model is created with SQL Server 2008 there are quite a bit chances you will get some datatype exceptions. The most common one is datetime2.

System.ArgumentException: The version of SQL Server in use does not support datatype 'datetime2

It is very easy to fix just right click on your EF Model and select "Open With" and choose XML Editor from list.

In editor find property ProviderManifestToken its value will be 2008 you can change it to 2005, rebuild your application again and you will see your application will work without any exception on your SQL Server 2005 production environment as well.

How to import/export data in SQL Server from one to other

Transferring database or data from development machine to production machine is very common thing in deployment stage.

I will show you steps to do that.

Open Microsoft SQL Server Management Studio, select your database, right click on it and select Import Data.

On this step you need to choose a Data Source, give the Server name and authentication and select source database from server. If authentication is correct it will automatically show you the list of databases available on up given server.

Before step you should already create a database where you want to transfer the data. 

Next choose destination where you want to transfer your data again same steps as mentioned above.

Wizard will show table and views available in both databases. Select table you want import from source to destination. Click on Edit Mappings and selection required options. 

Here one thing is notable if you don't have tables created in destination database you still able to transfer and create tables but in case you will loose your all keys and index which you need to setup again on destination database.

Click finish and it will start transferring tables.

Author Qasim Sarfraz

How to convert SQL Server 2008 database to 2005/2000

Some time it is a big problem when you develop any application with higher compatibility and customer production environment has downward compatibility, it is very common in in version of SQL Server version.

As Microsoft don't support higher to lower version compatibility in SQL Server.

In our my example we are going to convert a SQL Server 2008 database to SQL Server 2005 version downward.

Open Microsoft SQL Server Management Studio and right click on your database.

  

After this select you required database and don't forget to check on option "Script all objects in the selected database"

In options select Script for Server Version to SQL Server 2005 or 2000. Here is one thing is notable if want your data also with the script then select Script Data option to true, but sometime your database is enough big to load in Management Studio so it is unable to open in IDE. In this situation you can skip  Script Data option and leave it false as default. I will explain how you can transfer data in this post http://www.codeonlyyours.com/2013/10/how-to-importexport-data-in-sql-server.html.

Now you can run the script on production environment. If database is big and your have no option to transfer data via SQL Server connection then you have to install a older version of SQL Server on your development machine and first make a copy of converted database to that and then you can backup the database and restore it on production machine.

Click Next you need to choose output option as you want to save a script file or in clipboard or to a new query window in SQL Management Studio. 

Click Finish

Author Qasim Sarfraz