×


Category: SQL Issues and Solutions

  • Home
  • Blog
  • Category: SQL Issues and Solutions

SQL Error 50000 - Best method to fix

This article covers method to fix SQL Error 50000.

This SQL error happens in the process of installing SQL Server Management Studio on one of the client machines.
If you receive SQL Server 50000 error on a SQL Agent Job, you need to Quit the job reporting failure.

To fix this SQL error, you can try the following queries:
1. Enable the SQL Broker on the Site database:

USE master; 
GO 
ALTER DATABASE CM_PR1 SET ENABLE_BROKER 
GO

2. SET the Site Database as trustworthy:
USE master; 

GO 
ALTER DATABASE CM_PR1 SET TRUSTWORTHY ON 
GO

3. SET the Database to honor the HONOR_BROKER_PRIORITY:

USE master; 
GO 
ALTER DATABASE CM_PR1 SET HONOR_BROKER_PRIORITY ON; 
GO

Read More

SQL Server Error 5009 – Different scenarios and fixes

This article covers how to fix SQL Server Error 5009 error which occurs while adding or removing a database file or extending the database file size.
 
To fix Microsoft SQL Server Error 5009 While Adding Database File:
1. Set the Operating system permission on the mentioned .ldf file to full permission for your login account and SQL Server service account.
2. Change the location to the path where you have enough permission to create or add database files.

Read More

SQL error 300 - Fix it Now

This article covers tips to resolve SQL error 300. The reason of the error is related with user permission on VIEW SERVER STATE. You can upgrade SQL Server Management studio with the same version like SQL Instance or higher. 


Also, To mitigate this error, give the following GRANT and the error message will disappear:

USE master
GO
GRANT VIEW SERVER STATE TO "LoginName"

Read More

SQL error 1071 - Fix it Now

This article covers methods to resolve SQL error 1071 which generally happens if the combined key is too long, and adjusting the varchar value must resolve this error.

MySQL always reserves the max amount for a UTF8 field which is 4 bytes so with 255  + 255 with your DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 

you are over the 767 max key length limit.

You can only reduce the single varchar length or don't use a composite key.


To fix SQL error 1071:

You can also add these lines to a new conifg file in /etc/my.conf.d directory named umb4-support.cnf:

[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true

Then restart the maria db service.

Read More

Delete Data in SQL using DELETE statement - How to use it

This article covers how to Delete Data in SQL using the DELETE statement. 

The SQL DELETE Query is used to delete the existing records from a table.

You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.


In Structured Query Language, more commonly known as SQL, the DELETE statement is one of the most powerful operations available to users. 

DELETE operations irreversibly delete one or more rows of data from a database table. 

Being such a fundamental aspect of data management, it's important for SQL users to understand how the DELETE statement works.


The basic syntax of the DELETE query with the WHERE clause is as follows:

DELETE FROM table_name
WHERE [condition];

You can combine N number of conditions using AND or OR operators.


Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. 

The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!


To Delete All Records:

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name;


DROP is used to delete a whole database or just a table. The DROP statement destroys the objects like an existing database, table, index, or view. A DROP statement in SQL removes a component from a relational database management system (RDBMS).

Read More

Checkpoint Process in SQL Server - An Insight into it

This article covers the Checkpoint process in SQL server and the four types of checkpoints available.
Checkpoints are the useful repositories of information and serve best for the recovery of SQL server databases.

Different Types of Checkpoint in #SQL:
1. Automatic Checkpoint
An automatic checkpoint is the most common type that is triggered by a background process. Server Configuration Option “Recovery Interval” is used by the SQL Server Database Engine to determine how often automatic checkpoints are issued on a given database.
2. Indirect Checkpoint
A new type of #checkpoint introduced in SQL Server 2012 is an Indirect checkpoint. Indirect checkpoint also runs in the background, but it meets user-specified target recovery time for a given database.
3. Manual Checkpoint
Manual checkpoint runs like any other Transact-SQL command. It runs to completion by default. This type of checkpoint occurs in the current database only. It is also possible to set the time frame in which you want your checkpoint completed.
4. Internal Checkpoint
The fourth type is Internal checkpoint that cannot be controlled by user.

Read More

Resources