Showing posts with label troubleshoot. Show all posts
Showing posts with label troubleshoot. Show all posts

August 20, 2014

Form Data is not valid

I have this asp.net web forms application through which users can log on and view a number of records from a database and then make modification as needed. However, as the number of records have increased for users; for some users with a large number of records, the site is failing with the message "form data is not valid".

Since the issue is happening only for large accounts, I was somewhat certain that it's related to the number of form elements that can be posted back to the server. Microsoft introduced an upper limit to the number of the form elements that can be posted back to the server - http://weblogs.asp.net/scottgu/archive/2011/12/28/asp-net-security-update-shipping-thursday-dec-29th.aspx. So, to fix the issue, I added a new app setting in web.config file called "aspnet:MaxHttpCollectionKeys" and set its value to a number - 5000. And that's it - the site started working fine again.

Just to be clear though, I think this is a temporary fix and the application should be modified anyway as sooner or later that new limit might be reached as well.

I build an application using Visual Studio and Sql Server and built the model using Entity Framework but when I deployed the application, I keep getting an error. To give bit more information, I set up the database using Sql Server 2012 and applied the compatibility level to Sql Server 2005 as the database server only had Sql Server 2005. Ya, I know it's an old server but it's not a priority to the business to update that server.

So, after running the application against the production database, I realised that the error is happening from the database server and not from the web server. Basically, it's failing to a add a record when the table has a datetime column and it's failing with the message "The version of SQL Server in use does not support datatype 'datetime2'". I was a bit confused as I thought I have applied the compatibility settings correctly.

To further test, I reloaded the data model from the Sql Server 2005 database but the error was still there.

Now, to fix the issue, I opened the edmx file in an xml editor and within <edmx:StorageModels> element there is "ProviderManifestToken" attribute in "Schema" element. The value for "ProviderManifestToken" was set to 2008. Bingo. I changed that value to 2005 and recompiled the application which fixed the issue.

This is probably a bug in Entity Framework as it not picking up the right database and instead applying a default lowest value.

To apply the compatibility level, right click the database name in SQL Server Management Studio, and click on Properties, navigate to Options and then set compatibility level.

image

I am working on an older product and updating it to use the latest version of .NET and Entity Framework. Now that I have created the Entity Framework model using database first approach, I am finding issues like "EntityType 'EntityName' has no key defined". Basically, what this means is that the table in the database does not have a primary key defined. To fix the issue, the database needs to be updated and a primary key needs to be defined.

I have downloaded a visual studio solution from a source control repository like github or tfs but now the project is not building. It's giving an error message - referenced component 'EntityFramework' could not be found.

In this scenario, the issue is that Entity Framework has been added as a NuGet package and the package has not been restored. It's not necessary to store packages in source control as these can be downloaded and installed on the fly. So, to enable automatic restore of packages, click on Tools > NuGet Package Manager > Package Manager Settings . A prompt will appear where "Allow NuGet to download missing packages" and "Automatically check for missing packages during build in visual studio" needs to be ticked.

May 24, 2014

I am importing an excel spread sheet to sql server database but for some reason I am getting the error “The value violated the integrity constraints for the column”. I checked the table data type and it should really just work but could not get it to work.

I am importing an excel spread sheet into a sql server database and is getting the error "DTS_E_INDUCEDTRANSFORMFAILUREONERROR" and it more messages like "The value could not be converted because of a potential loss of data" and "The source column is of nVarchar(272 -length) and the destination column is nVarchar(256 -length)"

So, really there are 2 failure messages here. After some investigation, I realised that with the first message "The value could not be converted because of a potential loss of data", it's because one of the rows had a double value whereas I initially set the sql table to have int data type. As a result, it failed while converting this particular row. Changing the data type to float fixed this issue.

The second error message is because the string value in the excel is lot bigger in length compared to the max length set in the sql table. Changing the max number of characters in sql table fixed this issue.

Overall, importing an excel spread sheet to sql table is quite easy provided correct data types are applied to the sql table.

May 2, 2014

We may need to take a database offline various reasons, for example, it was a test database and we don’t need it anymore or the application has been decommissioned, etc. Sometimes, I have noticed that Sql Server Management Studio becomes unresponsive and hangs for a long time while taking the database offline. In this post I will talk about how to fix this issue.

Usually, the database can be taken offline by right-clicking on the name of the database and clicking Tasks > Take Offline as shown below.

image

Once I click on “Take Offline”, I expect the database to be taken offline but instead it hangs. To avoid the issue, I can write sql script to take the database offline. Following is the script that will take the database offline and then bring it online.

Take Database Offline / Online
  1. --take offline
  2. USE master
  3. GO
  4. ALTER DATABASE [database_name]
  5. SET OFFLINE WITH ROLLBACK IMMEDIATE
  6. GO
  7.  
  8. --take online
  9. USE master
  10. GO
  11. ALTER DATABASE [database_name]
  12. SET ONLINE
  13. GO

April 29, 2014

I was working on a Sql server database and since there were few issues, I copied the backup of the database to run some queries. Since I don't use it often and the database is huge, I saved the mdf file in an external hard drive but then I notice that every now then when I open Sql Server  Management Studio, the database fails to open with the message "Recovery Pending".

To solve this issue, I can simply reload the database from the backup but then I will need access to the backup file every time. Alternatively, I can write a few queries to solve this issue.

To start with, I will set the database to Emergency state and change it to a Single User mode.

  1. USE master
  2. GO
  3.  
  4. --run 1
  5. ALTER DATABASE [db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  6. GO
  7. ALTER DATABASE [db_name] SET EMERGENCY
  8. GO

 

Next, check if the database has errors using CHECKDB command.

  1. --run 2
  2. DBCC CHECKDB WITH NO_INFOMSGS


This is the step where you need to decide what to do with the errors that you have received. Depending on your scenario, you can repair the database with or without any data loss.

  1. --run 3
  2. --no data loss
  3. DBCC CHECKDB ('db_name', REPAIR_REBUILD);
  4.  
  5. --potential data loss
  6. DBCC CHECKDB ('db_name', REPAIR_ALLOW_DATA_LOSS);

 

Finally, change the database mode to multi user mode.

  1. --run 4
  2. ALTER DATABASE [db_name] SET MULTI_USER
  3. GO

 

Your database should be running again now.

Few days back I ran into a issue where my website failed with the message that

  1. {System.Data.SqlClient.SqlException (0x80131904): Could not allocate space for object 'dbo.Table1'.'PK_TableID' in database 'TestDB' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
  2. Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

 

Initially I thought the database was out space but then it sounded strange as I was not expecting the database to run out of space. After checking the database space I noticed that it only had a few MBs of data remaining for the primary while the log file had a huge data capacity. To fix the issue, update the max size of the database so it can save the data. Also, make sure the server has sufficient space to cater for the increased space.

However, sooner or later you are going to run out of space and therefore it's a good idea to increase the max size to unlimited but you need to make sure the server can cater for it.

April 15, 2014

I was trying to kill all spid that points my database but I kept getting the error message "Cannot Use Kill to kill your own Process". The error message was a bit strange as I have done this a number of times without any issues. So, basically this is what I tried.

April 7, 2014

I have this scenario where I need to insert xml data to a xml column in Sql Server that already has an empty string in it. The xml column I have allows null and I need to update both null and and empty xml to a default value. When I started writing the script finding the null value was really easy - I could write something like this.

  1. SELECT xmldata
  2. from Table_1
  3. where XmlData IS NULL


But finding the xml column with empty string was difficult. First of all, I could not write the following

  1. SELECT xmldata
  2. from Table_1
  3. where XmlData = ''

 

as it returned error

Msg 402, Level 16, State 1, Line 2
The data types xml and varchar are incompatible in the equal to operator.

So, to solve the issue, I used the inbuilt function DataLength() on the xml column and compared if the value is 5 or not. Data length for empty string is 5. Based on this comparison, I updated the xml column.

  1. SELECT xmldata
  2. from Table_1
  3. where DATALENGTH(XmlData) = 5

I had this situation where I had a XML column in a table within a Sql Server database which allowed NULL and I had to change this to must have values. Changing this is trivial when it is a new database with some test data but in my situation, I had to modify an existing database and therefore - the quickest way to change was to add the default xml to all fields that had NULL and then to apply a default value for this column. However, when I add the default value via Sql Server Management Studio, it complained - "Error validating the default for column - column name".

March 24, 2014

I created an application by copying an existing one and then changing the namespaces but I had to go through some trouble to get it working. It continuously failed with the message - "The located assembly's manifest definition does not match the assembly reference" - that is, the application is picking up wrong version of the referenced assembly.

Firstly, I had to change the names of the classes to the new namespace and then compile and make sure that it ran fine.

Secondly, instead of copying the bin directory directly, I add the nuget packages by right-clicking on Solution Explorer and clicking "Manage Nuget Packages". Now, doing this was easy until I realised the application has some compatibility issues with the new versions of the packages. Now, in order to avoid fixing the problem which might require code changes, I decided to get the old packages which was a bit difficult. Eventually, I deleted the packages from "Manage Nuget Packages" and then copied the bin directory and manually added references to the dlls by right clicking on References and choosing "Add Reference".

Now, I was confident that the application would work but it failed again with the message "The located assembly's manifest definition does not match the assembly reference". This is basically saying that I am pointing to a wrong package. I checked on "Manage Nuget Packages" but it was displaying the expected version. What I realised is that I have one package which already called another package which had the wrong version. After checking, I realised that I still had one of the newer packages that I needed to change to the older one. So, went through the same step described above. (The issues I had was with System.Web.Optimization and Microsoft.AspNet.Web.Optimization.WebForms packages).

Finally, I built the application again but it failed with not finding certain controls on the pages. That is, the code-behind could not find the controls on the aspx pages which was a bit weird. I have discussed this on another article as it is not directly relevant here - find out how I fixed it.

Finally, the application ran and it worked fine.

I created a project by copying some files from another project and for some reason the project would not compile and kept failing to find the asp.net controls from code behind. This was really strange as it looked all fine. So, I thought, I would give it a try by creating another new page and then copying the source code from one of the pages that is failing. Surprisingly, this worked and the new page could easily find the controls from the aspx pages which is the correct behavior.

Now, I could create new pages and delete the old ones but then it's silly and will take me a lot of time to fix it. Basically, what happened is that asp.net is somehow caching the old compiled version of the application and therefore pointing to the wrong namespace and so not find the controls.

To fix the issue, I placed incorrect xml in web.config file so it won't be a valid xml file anymore. Then, I recompiled the application and yeahhh - the application started working again as usual.

So, changing web.config to something invalid is forcing the application to remove the cache and rebuild.

March 11, 2014

I was working on a site with a few input fields and one of those would accept xml. When I ran the code, it failed with with the message "A Potentially dangerous Request.Form was detected." Usually, I would set ValidateRequest to false when these kind of errors occur and validate on the backend but I realised it does not work in .NET 4 anymore. Well, at least, not without changing the web.config file.

So, I added the following section (requestValidateMode) in web.config and ran the page and it worked fine.

  1. <system.web>
  2.   <compilation debug="false" targetFramework="4.0"/>
  3.   <httpRuntime requestValidationMode="2.0"/>
  4. </system.web>

March 6, 2014

I am using Entity Framework Code First approach but I am receiving the error “The model backing the ‘ModelContext' context has changed since the database was created. Consider using Code First Migrations to update the database”. What does this mean?

Basically, the above error can occur when we have enabled and added a migration and updated a database and after that we have changed the model for the application. If the model has changed, Migrations process will need to be re-run before we can run the application. Read on Enable Migrations on how to use the Migrations in Code First Approach. At this stage, add a new Migration and update the database and then re-run adding the new update.

Reference: Shahed Kazi at AspNetify.com