Exception- The definition for user-defined data type ‘MyTypeList’ has changed.

The definition for user-defined data type ‘MyTypeList’ has changed. Exception when we rename or changed any User defined TYPE and it used in stored procedures.
Stored Procedures which is using this type variable will not run and throwing the exception. If we have large numbers of SP then it will to time consuming or irritating to re-compile all SP one by one.
Instead of using this manual process this script will help to fix all references.

DECLARE @Name NVARCHAR(776);

DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.MyTypeList', 'TYPE');

OPEN REF_CURSOR;

FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
	PRINT @Name
    FETCH NEXT FROM REF_CURSOR INTO @Name;
END;

CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;

How to check SQL Job is in running state?

Method 1:
System Stored procedure: sp_help_job, returns information on the job, its steps, and more.

EXEC msdb.dbo.sp_help_job @Job_name = ‘Your Job Name’

Method 2:

IF EXISTS(SELECT 1
FROM msdb.dbo.sysjobs J
JOIN msdb.dbo.sysjobactivity A
ON A.job_id=J.job_id
WHERE J.name=N’Your Job Name’
AND A.run_requested_date IS NOT NULL
AND A.stop_execution_date IS NULL
)
PRINT ‘The job is running!’
ELSE
PRINT ‘The job is not running.’

SQL SERVER – Installation Failure Database engine services failed.

Sometimes when we install SQL Server or Add any new SQL Server Instance on Windows Machine, we can see the database errors listed below.
To resolve these errors we are trying to repair existing installation or do something else.
If nothing happens then we are trying to install it completely from control panel, but it is not 100% sure that the fresh new installation will works. Some of the SQL Server links existing at system registry so we are not able to get it success.

Installation error list
Installation error

Installation error list
Installation error list

To resolve these registry things we can un-install all SQL server related things by theirs ID.

The ID we can get using “WMIC” command. So run this command on console:

WMIC PRODUCT Where "Caption like '%SQL%'" GET Caption, IdentifyingNumber

WMIC Result
WMIC Result

You will get a list of objects with their identifier.

Now un-install these all things one by one using below command:

msiexec /x {GUID}

i.e..

msiexec /x {ABB6AC00-F1D8-4EBF-8128-830D090B76C0}

 

I think after that restart the machine and try fresh installation, will works.

How to Create Multiple SQL Server Instances on Same Server

When you install SQL Server, the installation creates an instance (DEFAULT OR SQLEXPRESS) of SQL Server. In my case I have created instanace named DEVSQL.

Some times we do need to create multiple instances based on requirements. To create more instances, start the setup again. The following screen appears when setup starts for the SQL Server 2012.

Click “New SQL Server stand-alone installation or add features to an existing installation”, the following screen appears:

Installation Start

As you can see DEVSQL instance already exists, it was created by the first installation, now select “New installation or add shared features”. Then click Next and accept the License Agreement.

Then select features you want to install for the new instance.

After that the installation offer you to specify the name of your new instance (MYTEST was chosen in this example):

Click Next to create the new instance and perform the remaining setup steps.

Now you are done!