The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. (rsReportServerDisabled) Get Online Help Keyset does not exist (Exception from HRESULT: 0x80090016)

I have received the below error when I was tried to open web service URL.




The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. (rsReportServerDisabled) Get Online Help Keyset does not exist (Exception from HRESULT: 0x80090016)




Possible Reasons :


1. First time Report server was configured on instance and the databases were mapped to different server while configuring the report server.


Solution :


Go to the reporting service configuration manager --> Connect to the respective report server instance --> Encryption key page --> Delete .


Now open your Report URL in IE or any browser (run as administrator) .


Now your issue should get resolved. 

Script to find what is causing transaction log file fill in SQL Server

SELECT db.[name] AS [Database Name]
 ,db.recovery_model_desc AS [Recovery Model]
 ,db.log_reuse_wait_desc AS [Log Reuse Wait Description]
 ,ls.cntr_value AS [Log Size (KB)]
 ,lu.cntr_value AS [Log Used (KB)]
 ,CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Log Used %]
 ,db.[compatibility_level] AS [DB Compatibility Level]
 ,db.page_verify_option_desc AS [Page Verify Option]
FROM sys.databases AS db
INNER JOIN sys.dm_os_performance_counters AS lu ON db.NAME = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls ON db.NAME = ls.instance_name
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
 AND ls.counter_name LIKE 'Log File(s) Size (KB)%';

Script to find who did what in SQL Server ?

Below script is very use full to find out  who has performed what kind of action in SQL Server.

SELECT sys.dm_exec_sessions.session_id
 ,sys.dm_exec_sessions.host_name
 ,sys.dm_exec_sessions.program_name
 ,sys.dm_exec_sessions.client_interface_name
 ,sys.dm_exec_sessions.login_name
 ,sys.dm_exec_sessions.nt_domain
 ,sys.dm_exec_sessions.nt_user_name
 ,sys.dm_exec_connections.client_net_address
 ,sys.dm_exec_connections.local_net_address
 ,sys.dm_exec_connections.connection_id
 ,sys.dm_exec_connections.parent_connection_id
 ,sys.dm_exec_connections.most_recent_sql_handle
 ,(
  SELECT TEXT
  FROM master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle)
  ) AS sqlscript
 ,(
  SELECT db_name(dbid)
  FROM master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle)
  ) AS databasename
 ,(
  SELECT object_id(objectid)
  FROM master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle)
  ) AS objectname
FROM sys.dm_exec_sessions
INNER JOIN sys.dm_exec_connections ON sys.dm_exec_connections.session_id = sys.dm_exec_sessions.session_id

How to rename user in MySQL

USE MYSQL;

RENAME USER 'CAMROON' @ 'LOCALHOST' TO 'CAMROON1' @ 'LOCALHOST'
 ,'USER1' @ 'LOCALHOST' TO 'USER2' @ 'LOCALHOST';

SELECT * FROM USER WHERE USER LIKE 'CAM%';

How to update the user or anyonymous password in MySQL

How to Change password with mysqladmin

mysqladmin - u root - p password "new password"

Change your password to pwd5 for the user account under which you're currently logged on to the system
SET PASSWORD = PASSWORD('pwd5'); 

Connect to the server at a specific IP address, with a username of myname and password of mypass:

shell> mysql host=192.168.1.33 user=myname password=mypass

shell> mysqladmin -u root password 'rootpass'

shell> mysqladmin -u root -h host_name password 'rootpass'

shell> mysql -u root mysql

If you want to assign passwords to the anonymous accounts

UPDATE user SET Password = PASSWORD('anonpass') WHERE User = '';

UPDATE user SET Password = PASSWORD('rootpass') WHERE User = '' AND Host = 'localhost';

How to update root password in MySQL

We can update the root password by using the below command.

UPDATE user
SET password = PASSWORD('secret')
WHERE user = 'root'

MySQL - How to drop user in My SQL

Dropping user in MySQL

We can drop user in MySQL in the below methods

1. Drop a User 

USE mysql;
SELECT * FROM user WHERE user = 'KRISH';

USE mysql;

DROP USER KRISH;

2.  Drop the user1@domain1.com user account

USE mysql;
DROP USER user1@domain1.com;

3. Delete anonymous users
USE mysql;

SELECT * FROM user WHERE user = '';
DELETE FROM user WHERE user = '';

MySQL : Grant privileges to the user

GRANT : GRANT statement grants privileges to a user account 



By default, MySQL creates the following grant tables:

SELECT * FROM columns_priv;

SELECT * FROM db;

SELECT * FROM tables_priv;

SELECT * FROM user;

The following grant command will provide Select,Insert access to the user SaiReddy on the desired database.


GRANT SELECT ,INSERT ON databaseName.tableName

 TO SaiReddy@localhost IDENTIFIED BY 'pw1';

Column-Level Privileges :

GRANT SELECT ,UPDATE (BookTitle ,Copyright ) ON test.Books
 TO 'user1' @ 'domain1.com' IDENTIFIED BY 'pw1';
Grant Create , alter,drop and create view permission for a database


GRANT CREATE ,ALTER ,DROP ,CREATE VIEW ON TENNIS.* TO USER1

Grant permission for a procedure
GRANT EXECUTE ON PROCEDURE PROCEDURE_NAME TO User1

GRANT ALL PRIVILEGES to a user from localhost
GRANT ALL PRIVILEGES ON *.* TO 'User1' @ 'localhost' WITH GRANT OPTION



GRANT Create,Alter,Drop privileges to ALL
GRANT CREATE ,ALTER ,DROP ON *.* TO MAX
GRANT Create,Alter,Drop view for a database
GRANT CREATE ,ALTER ,DROP ,CREATE VIEW  ON TENNIS.* TO KRISH



Give user permission for querying a view
CREATE USER 'TEST' @ 'localhost' IDENTIFIED BY 'TEST@123';

CREATE VIEW VW_EMP (DEPT ,NUMBER_OF) AS
SELECT DEPT ,COUNT(*) FROM EMP GROUP BY DEPT;

GRANT SELECT ON VW_EMP TO DEPT

MySQL Privileges

Privileges in MySQL.

MySQL - Verifying user permissions.

After creating the user and granting the permissions then you can use the below script to find out the granted privilege.

USE MYSQL;

SELECT host ,user,select_priv ,update_priv FROM user
WHERE user = 'KRISH';

SELECT host ,db ,user ,table_name ,table_priv ,column_priv FROM tables_priv
WHERE user = 'KRISH';

SELECT host ,db ,user ,table_name ,column_name ,column_priv FROM columns_priv
WHERE user = 'KRISH';

MySQL - Creating user in MySQL

Creating user

Creating a user is 4 step process i.e

1. Check the list of available databases in MySQL

show databases ;

2. Select the database on which you want to create the user.

use MySQL ;

3. Execute the below script to created the desired user.

CREATE USER 'KRISH' @ 'localhost' IDENTIFIED BY 'KRISH'
 ,'CAMROON' @ 'localhost' IDENTIFIED BY 'CAMROON';

4. Verify whether user has been created or not.

SELECT * FROM USER;

Script to find currently running or active queries in SQL Server

Some times we used to get calls or mails from the user and asking for what are currently running queries in a particular instance and also will get complaints like queries are running slow.

For this scenario, we have to run the below query on a requested instance to find out currently running queries.

SELECT r.start_time [Start Time]
 ,session_ID [SPID]
 ,DB_NAME(database_id) [Database]
 ,SUBSTRING(t.TEXT, (r.statement_start_offset / 2) + 1, CASE 
   WHEN statement_end_offset = - 1
    OR statement_end_offset = 0
    THEN (DATALENGTH(t.TEXT) - r.statement_start_offset / 2) + 1
   ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
   END) [Executing SQL]
 ,STATUS
 ,command
 ,wait_type
 ,wait_time
 ,wait_resource
 ,last_wait_type
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id != @@SPID
 AND session_id > 50 -- To avoid system queries
ORDER BY r.start_time

MySQL Strengths

MySQL Strengths

MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).

MySQL Introduction

MySQL Introduction

MySQL is one of the most popular Open Source RDBMS ( Relational SQL database management system) because of its flexibility such as  fast, easy-to-use for many small and big businesses.
MySQL is developed, marketed, and supported by MySQL AB, which is a Swedish company.
MySQL is becoming so popular because of many good reasons:
MySQL is released under an open-source license. So you have nothing to pay to use it.
MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
MySQL uses a standard form of the well-known SQL data language.
MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.
MySQL works very quickly and works well even with large data sets.
MySQL is very friendly to PHP, the most appreciated language for web development.

Sys.dm_exec_requests - What is use of this DMV ?

This DMV will provide the information about each request that is executing with in the SQL Server.
To work with this DMV , the user should have VIEW SERVER STATE permission.

SELECT SESSION_ID
 ,percent_complete
 ,estimated_completion_time
FROM SYS.dm_exec_requests

Percent_complete will provide the completion details of the below activities i.e

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

How to change all database comparability level from 90 to 100

During database migration ,I got the requirement to change the database recovery model from 90 to 100 i.e from SQL Server 2005 to SQL Server 2008.

You can also use the below script if database count is more.

Just execute the below script and paste the results appeared in message window to new query window and then press F5 to execute the scripts.

SELECT 'alter database ' + '' + NAME + ' ' + 'SET COMPATIBILITY_LEVEL = 100'

 ,recovery_model_desc
 ,compatibility_level
FROM sys.databases
WHERE compatibility_level = 90
 AND NAME NOT IN ('AdventureWorksDW')

Kindly note that you can also use the above script if you want to change the compatibility of the databases where you doesn't know the databases count or >100 databases at a time.

How to change all database comparability level from 90 to 100

During database migration ,I got the requirement to change the database recovery model from 90 to 100 i.e from SQL Server 2005 to SQL Server 2008.

You can also use the below script if database count is more.

Just execute the below script and paste the results appeared in message window to new query window and then press F5 to execute the scripts.

SELECT 'alter database ' + '' + NAME + ' ' + 'SET COMPATIBILITY_LEVEL = 100'

 ,recovery_model_desc
 ,compatibility_level
FROM sys.databases
WHERE compatibility_level = 90
 AND NAME NOT IN ('AdventureWorksDW')

Kindly note that you can also use the above script if you want to change the compatibility of the databases where you doesn't know the databases count or 100 databases at a time.