Interactive Services Dialog Detection window for SQL Agent

Today I got the below error on my client machine: 

"Interactive Services Dialog Detection"

The solution of above problem is desable the interactive service dialog detection window. Follow below steps:

• Go to Start - Run and type services.msc to Starts, stops, and configures Windows services.
• Select SQL Server Agent--> Right click on properties
• Select the logon tab
• Uncheck the “Allow Service to interact with Desktop”
• Restart the SQL Server Agent

Cannot connect to WMI provider. You do not have permission or the server is unreachable.

Today when I was trying to open SQL Server 2012 configuration manager, I got below error: 

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. 
Invalid class [0x80041010] 

I follow below steps to solve above error:

1. First I search for the file "sqlmgmproviderxpsp2up.mof"  (generally it is located under shared folder under version folder). and open command prompt and run below command:
mofcomp C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof
Output
Microsoft (R) MOF Compiler Version 6.1.7600.16385
Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
Parsing MOF file: C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmp
roviderxpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository...
Done!
2. After it is stored in the repository, 
3. Now open SQL Configuration manager, it will work.

Retrieve SQL Server Service Account

This script will pull the name of the SQL Server Service account out of the registry using an undocumented stored procedure. 


-- Declare a variable to hold the value 
DECLARE @serviceaccount varchar(100)

-- Retrieve the Service account from registry 
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'

--Display the Service Account 
SELECT @Serviceaccount

Row count for all the tables in all the database

Use below query to get the row count for all tables in all the databases:


SET NOCOUNT ON
DECLARE @strQuery VARCHAR(4000)
DECLARE @strTable TABLE (DBName VARCHAR(200), TableName VARCHAR(300), COUNT INT)
SET @strQuery='SELECT  ''?'',sysobjects.Name, sysindexes.Rows
FROM   ?..sysobjects INNER JOIN ?..sysindexes ON sysobjects.id = sysindexes.id
WHERE  type = ''U''  AND sysindexes.IndId < 2 order by sysobjects.Name'
INSERT @strTable
EXEC sp_msforeachdb @strQuery
SELECT * FROM @strTable WHERE DBName <> 'tempdb' ORDER BY DBName

CHECK_EXPIRATION option cannot be used when CHECK_POLICY is OFF

Sometime you may get the below error while executing some script:

Msg 15122, Level 16, State 1, Line 1The CHECK_EXPIRATION option cannot be used when CHECK_POLICY is OFF.

To solve this error, Turn On CHECK_POLICY in the script or remove CHECK_EXPIRATION from the script.

CREATE LOGIN [username] WITH PASSWORD=N'pa@Ssword' , CHECK_EXPIRATION=ON, CHECK_POLICY=ON

Unable to start execution of step 1 (reason: line(1): Syntax error)

You may receive the error “Unable to start execution of step 1 (reason: line(1): Syntax error” under below circumstances in SQL Server 2005/2008. 

1. Created a new maintenance plan 
2. Modified the path in existing maintenance plan 
3. Existing database fails due after File servers IP Change..etc 

Error Message:
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details:
Additional Information:
Job ‘ Log_Backup’ failed. (SqlManagerUI) 
Additional information from Log File Viewer:
Executing the query "EXECUTE master.dbo.xp_create_subdir N'\\\\backup\\t..." failed with the following error:"Cannot open backup device \\backup\testdb.trn'. Operating system error 53(The network path was not found.). BACKUP LOG is terminating abnormally.” Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. 
Solution: 
1. Check the login used to schedule the backup has access to backup device path and read/write permissions.

Run below t-sql from New Query to check the permission.
EXECUTE master.dbo.xp_create_subdir N'\\backup \testdb' 

2. If your Backup Maintenance plan suddenly stopped working after Network maintenance then it may be related to name resolution to File Server Name or IP Change or Alias name that you are using in the backup.

Check whether you are able to resolve correct name, IP address using nslookup or check with your Windows team to fix name resolution.
For ex:- nslookup "server name or IP address or File server name"

3. If you still have issues 

• Go to SQL Server Agent\Jobs and click properties 
• Go to Steps section 
• Highlight the Sub plan and click Edit 
• In the “ package section” add a Backslash to the beginning of the path listed 

Script to fetch the code that is executed by an SPID

Use below TSQL Script to fetch the code that is executed by a SPID:


DECLARE @sql_handle AS VARBINARY (1000)

SELECT @sql_handle = SQL_HANDLE
FROM   sys.sysprocesses WITH (NOLOCK)
WHERE  spid = 56; --To Do: Update with SPID OF the Process
  
SELECT * 

FROM   sys.dm_exec_sql_text (@sql_handle)

Some stored procedure related to security

Sp_addrolemember : Adds login to database role in the current database
Syntax : sp_addrolemember 'role_name','login_name'
eg : sp_addrolemember 'db_owner', 'akl'

Sp_droprolemember : Removes server role of the specified login
Syntax sp_droprolemember role_name, login_name
eg : sp_droprolemember 'db_owner', 'akl'

Sp_changedbowner : changes owner of the current database
Syntax : sp_changedbowner 'login name'
eg : sp_changedbowner 'akl'

Sp_addlogin : Create a new login in SQL Server Instance
Syntax : sp_addlogin login_name, password, [def db], [def language]
eg : sp_addlogin 'akl', 'kumar', master

Sp_adduser : Creates a new user and mapped to login in the current database.
Syntax : sp_adduser 'login_name','user_name_in_db' 
eg  : use akldb;GO; sp_adduser 'akl','akl' 

Sp_defaultdb : changes the default database for any login
Syntax : sp_defaultdb login_name, database
eg : sp_defaultdb 'akl','master'

Sp_dropuser : Removes user from the current database
Syntax : sp_dropuser 'user_name
eg : sp_dropuser 'akl'

Sp_droplogin / drop login : Removes login from SQL Server Instance
Syntax : sp_droplogin loginname
eg : sp_droplogin 'akl' (or) drop login akl

TempDb Tips

TempDB must be optimized after some time interval for every SQL Server. There are some techniques to optimize TempDB. Here are some suggestions and techniques found from different sources:

Place TempDB files on the fastest drive available (beware of SSD…great performance, but might burn it out).

Your system may work better with a different TempDB file count. Monitor different configurations for performance.

Create separate data files for each CPU/Core. Make the files the same size, even if they are on the same drive.

If possible, isolate TempDB on a separate disk from other databases.

You only need one TempDB log file, but it should also be on the fastest drive available

Make sure you have enough space in your TempDB files so they do not have to autogrow.

TempDB has some optimization techniques. It caches space for tables that may be used again. For this reason you may see the data allocation of TempDB remain long after your query has completed. 

Be sure to commit or roll back your transactions. If you don’t, any space allocated in TempDB as part of that transaction may not be released.

Remember, if you decide to change the files and/or the size of your TempDB files you will need to stop and restart SQL Server in order for your changes to take place.

A transport-level error has occurred when sending the request to the server

Sometime you may get the below error while connecting to SQL Server:


.Net SqlClient Data Provider: Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)

Generally this error occurs when connection to SQL server is forcible closed due to some reasons. Some possible reasons are:

• KILL Command is issued by any other user.
• SQL Server Service is restarted / Stopped
• When the connection was IDLE for a long time
• The database to which the connection is connected to is restored

To solve this error reconnect the SQL Server

Error: 7139, Severity: 16, State: 1

Today I was getting error while uploading documents to the table. Whenever I was uploading documents with size greater than 64 KB, I got the below error:

Server: Msg 7139, Level 16, State 1, Line 1
Length of text, ntext, or image data (200) to be replicated exceeds configured maximum 10.
The statement has been terminated.

So I got some clue that there might be some settings related to size of the documents. Finally I squared replication configuration and found that one of the server level replication setting is set to small value. max text repl size is the server level setting set to a small value of 64 KB. Then I increased this value to 1073741824 (1 GB) and it started working fine. The query I ran is below:

EXEC SP_CONFIGURE 'max text repl size', 1073741824
GO
RECONFIGURE
GO

SET NOCOUNT ON

SET NOCOUNT ON gives a performance boost to action queries by suppressing the "(n row(s) affected) message that results from running a query.

The performance boost is due to the few bytes of information that make up the "(1 row(s) affected)" message not being transmitted to the client application.

Communication between the database and the client application on a stand-alone machine will be as fast as it is possible to get.

For queries that retrieve data the performance boost will be less simply because the size of the "(1 row(s) affected)" message is small compared to the volume of data being returned.

In .NET applications an ExecuteNonQuery command returns the number of records affected by the operation. Set NOCOUNT ON means that the value that this call returns is always zero.

Script to remove Tab, Line Feed and Carriage Return from a string

The below TSQL script removes the Tab, Line feed, Carriage Return characters in a string:


SET NOCOUNT ON
DECLARE @String VARCHAR(100)
DECLARE @CorrectedString VARCHAR(100)

SELECT @String = 'ARUN    KUMAR 
LADHA'

PRINT @String

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(13),'')
PRINT @CorrectedString

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(10),'')
PRINT @CorrectedString

SELECT @CorrectedString = REPLACE(@String, CHAR(9),'')
PRINT @CorrectedString

Convert Delimited String to Table

Sometime we may need to convert delimited string to table. From below function we can do this conversion:


CREATE FUNCTION Func_String_To_Table
(@strTemp VarChar(1000))
Returns @Table Table (VAL int)
AS
BEGIN
    SET @strTemp = @strTemp + ','
    DECLARE @curr_char VarChar(1)
    DECLARE @char VarChar(6)
    DECLARE @len int
    DECLARE @num int
    DECLARE @counter int
    SET @char = ''
    SET @curr_char = ''
    SET @counter = 1
    SET @len = len(@strTemp) 

    WHILE @counter <= @len 
    BEGIN
        SET @curr_char = substring(@strTemp, @counter, 1)
        IF (@curr_char <> ',')
        BEGIN
            SET @char = @char + @curr_char         
        END
        ELSE
        BEGIN  
            SET @num = convert(int, @char)         
            INSERT INTO @Table VALUES(@num)
            SET @char = ''
        END
       SET @counter = @counter + 1
    END
    RETURN
END