Database configuration failed: Column Type does not allow DBNull Value

"Configuration wizard error "Database configuration failed: Column ‘Type’ does not allow DBNull Value". We need to allow nulls for the 'Type' column in SQL management studio for the User table.


Configuration Wizard ends with the following error:

Configuration Wizard error "Database configuration failed: Column ‘Type’ does not allow DBNull.Value"

CAUSE
Nulls for the 'Type' column for the ReportDefinitions table were not allowed in SQL Management Studio.

RESOLUTION
Run the following SQL query against the Current database:

update User set type = '' where type = NULL

for example, ‘0000-00-00’ works well with ‘Zero-date in NOT NULL Columns’ setting), or set destination column to NULL so you could process null data after the migration is complete.

update  User  set RENEWAL_DATE= '2000-01-01' where  RENEWAL_DATE = '0000-00-00'

update  User  set THE_TIME= '2000-01-01 00:00:00' where  THE_TIME= '0000-00-00 00:00:00'

Notes:

1. The '' is 2 single quotes one after the other and not a single double quote. The 2 single quotes back to back designates the query to enter a blank value in the field. If you use a double quote, the query will fail.
2. Make sure to backup your database before making direct changes to the database

SQL Server Row Count for all Tables in a Database

SQL server Syntax :

SELECT
     sOBJ.name AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
 
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO


Output : -

Get record counts for all tables in MySQL database

mySql  Syntax:


SELECT  table_rows,table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'SchemaName' and table_rows<>0; --  Schemaname (Databasename)



output

Change Schema Name Of Table In SQL

I want to change schema name of table "Employees"in Database. In the current table Employees database schema name is "Schemaconverted" I want to change it to dbo.

Example:

FROM

Schemaconverted.Employees
TO

dbo.Employees


Syntax

ALTER SCHEMA dbo
    TRANSFER Schemaconverted.Employees

syntax  for All Base tables

use TestDB
go
declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema   varchar(1000)

  set @oldschema = 'Schemaconverted'
  set @newschema = 'dbo'

 while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)

  begin
      select @table = name from sys.tables
      where object_id in(select min(object_id) from sys.tables where  schema_name(schema_id)  = @oldschema)

    set @sql = 'alter schema ' + @newschema + ' transfer ' + @oldschema + '.' + @table

   exec(@sql)
 end

Suppress zero in Crystal Report

Right Click on Report Field, go to Format Object, Click on Number, Check the Suppress if Zero Field. Report will not show zero values.

Crystal  Report  Shown as below

Show/ Hide a data field and a label for some records in crystal report

From Crystal report designer,
select the field you want to hide + Right mouse click to open Format object + Common + Check the Suppress and open format formula editor and an expression like:

{tableName.Amount}=0

how do you reorder rows in a table based on a TR attribute using JQuery

A table with rows similar to the following.
These rows get updated from time to time via jquery calls.

 Using jquery,
 how would I construct a function that reorders these table rows based on the CSSclass value putting the highestCSSclass value rows at the top and the lowest CSSclass  value rows at the bottom? There could be up to n rows in the table.

<table id="dtmarksList">
<tbody>
<tr>
 <th >S.NO</th><th >Name</th><th >Marks</th><th >Subject</th>
</tr>
<tr>
 <td> 1</td>
<td> test</td>
<input name="txtmarks1" type="text"  id="txtmarks1" Class="markvalue">
<td> Science</td>
</tr>
</tr>
 <td> 2</td>
<td> test2</td>
<input name="txtmarks2" type="text"  id="txtmarks2" Class="markvalue">
<td> Maths</td>
</tr>

</tr>
 <td> 3</td>
<td> test3</td>
<input name="txtmarks3" type="text"  id="txtmarks3" Class="markvalue">
<td> Maths</td>
</tr>
......
.....
......
</tr>
 <td> n</td>
<td> testn</td>
<input name="txtmarksn" type="text"  id="txtmarksn" Class="markvalue">
<td> Maths</td>
</tr>
</tbody>
</table>

Method: 

$("input[type=text].markvalue").blur(function () {

var $table=$('#dtmarksList');

var rows = $table.find('tr').get();

rows.sort(function(a, b) {

var txtmark=$(a).find('.markvalue').val();

if (txtmark) return  0;    /* Small Value for sorting */ 

return 1;     /* High value for sorting */

});

$table.children('tbody').html(rows);

/*

$.each(rows, function(index, row) {

$table.children('tbody').append(row);

});
*/

});

Method 2:

            $("#dtmarksList tr input[type=text].markvalue").each(function (i) {
                if ($(this).val() != '')
                {
                  $($("#dtmarksList").find('tr:first')).after($("#" + $(this).attr("id")).closest('tr'));
                } 
            });