Oct 31 2019

Check SQL Lock

SELECT s.session_id    AS 'SessionId',
        s.login_name    AS 'Login',
        COALESCE(s.host_name, c.client_net_address) AS 'Host',
        s.program_name  AS 'Application',
        t.task_state    AS 'TaskState',
        r.start_time    AS 'TaskStartTime',
        r.[status] AS 'TaskStatus',
        r.wait_type     AS 'TaskWaitType',
        TSQL.[text] AS 'TSQL',
        (
            tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count
        ) +(
            tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count
        )               AS 'TotalPagesAllocated'
FROM   sys.dm_exec_sessions s
        LEFT  JOIN sys.dm_exec_connections c
             ON  s.session_id = c.session_id
        LEFT JOIN sys.dm_db_task_space_usage tsu
             ON  tsu.session_id = s.session_id
        LEFT JOIN sys.dm_os_tasks t
             ON  t.session_id = tsu.session_id
             AND t.request_id = tsu.request_id
        LEFT JOIN sys.dm_exec_requests r
             ON  r.session_id = tsu.session_id
             AND r.request_id = tsu.request_id
        OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL
WHERE  (
            tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count
        ) +(
            tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count
        ) > 0;



--kill 80

Jun 28 2019

DELETE TEMP TABLE

IF OBJECT_ID(‘tempdb..#TEMP_AP_INVOICE’) IS NOT NULL DROP TABLE #TEMP_AP_INVOICE

Aug 3 2017

Rebuilding Indexes using the SSMS Database Maintenance Wizard

Usually I do all 3 job under one single task but with separate schedule for each of them:

Reorganize_Index

Rebuild_Index

Update_Statistics

Details can be found here:

https://www.red-gate.com/simple-talk/sql/database-administration/rebuilding-indexes-using-the-ssms-database-maintenance-wizard/

May 1 2017

Update Statistics Manually. Re-indexing manually

DECLARE @tablename varchar(80),@shemaname varchar(80)
DECLARE @SQL AS NVARCHAR(200)
DECLARE TblName_cursor CURSOR FOR
SELECT t.name,s.name FROM sys.tables t join sys.schemas s
on s.schema_id=t.schema_id


OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor
INTO @tablename,@shemaname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'UPDATE STATISTICS '+@shemaname+'.[' + @TableName + '] WITH FULLSCAN ' ---+ CONVERT(varchar(3), @sample) + ' PERCENT'
PRINT @SQL

EXEC sp_executesql @statement = @SQL

 FETCH NEXT FROM TblName_cursor
 INTO @tablename,@shemaname
END

CLOSE TblName_cursor
DEALLOCATE TblName_cursor

May 1 2017

MS SQL add Update Statistics job (Schedule) to get better performance ( Reindexing )

/****** Object: Job [Update CRM Statistics] Script Date: 05/01/2017 09:43:42 ******/
 BEGIN TRANSACTION
 DECLARE @ReturnCode INT
 SELECT @ReturnCode = 0
 /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 05/01/2017 09:43:43 ******/
 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
 BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
 EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Update CRM Statistics',
 @enabled=1,
 @notify_level_eventlog=3,
 @notify_level_email=0,
 @notify_level_netsend=0,
 @notify_level_page=0,
 @delete_level=0,
 @description=N'No description available.',
 @category_name=N'[Uncategorized (Local)]',
 @owner_login_name=N'xxxx', @job_id = @jobId OUTPUT

/*xxxx = login username for the database which has dbaadmin permission*/
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 /****** Object: Step [Update Statistics] Script Date: 05/01/2017 09:43:43 ******/
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update Statistics',
 @step_id=1,
 @cmdexec_success_code=0,
 @on_success_action=1,
 @on_success_step_id=0,
 @on_fail_action=2,
 @on_fail_step_id=0,
 @retry_attempts=0,
 @retry_interval=0,
 @os_run_priority=0, @subsystem=N'TSQL',
 @command=N'DECLARE @tablename varchar(80),@shemaname varchar(80)
 DECLARE @SQL AS NVARCHAR(200)
 DECLARE TblName_cursor CURSOR FOR
 SELECT t.name,s.name FROM sys.tables t join sys.schemas s
 on s.schema_id=t.schema_id
 OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor
 INTO @tablename,@shemaname

WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @SQL = ''UPDATE STATISTICS ''+@shemaname+''.['' + @TableName + ''] WITH FULLSCAN '' ---+ CONVERT(varchar(3), @sample) + '' PERCENT''
 PRINT @SQL

EXEC sp_executesql @statement = @SQL

FETCH NEXT FROM TblName_cursor
 INTO @tablename,@shemaname
 END

CLOSE TblName_cursor
 DEALLOCATE TblName_cursor
 ',
 @database_name=N'CRM',

/*CRM is the database NAME*/
 @flags=0
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Reindexing',
 @enabled=1,
 @freq_type=4,
 @freq_interval=1,
 @freq_subday_type=1,
 @freq_subday_interval=0,
 @freq_relative_interval=0,
 @freq_recurrence_factor=0,
 @active_start_date=20141124,
 @active_end_date=99991231,
 @active_start_time=193000,
 @active_end_time=235959,
 @schedule_uid=N'b5ec9450-ee63-43bc-926f-ab5e36d5dc9e'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 COMMIT TRANSACTION
 GOTO EndSave
 QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
 EndSave:

GO

Mar 2 2017

sql drop stored procedure if exists

Drop and re-create procedure if it exists in T-SQL not working

IF EXISTS(SELECT 1

          FROM   INFORMATION_SCHEMA.ROUTINES

          WHERE  ROUTINE_NAME = 'YOUR_STORED_PROCEDURE_NAME'

                 AND SPECIFIC_SCHEMA = 'dbo')

  BEGIN

      DROP PROCEDURE YOUR_STORED_PROCEDURE_NAME

  END

GO




CREATE PROCEDURE dbo.YOUR_STORED_PROCEDURE_NAME

AS-- Logic Comes Here

GO

Apr 22 2015

how to generate index number or serial number in select statement

party_code_table

ID	Code
43224	R06048
42335	J06600
42324	M06791


I want a select statement to get data like this:

SL	Code
1	R06048
2	J06600
3	M06791

SELECT ROW_NUMBER() OVER (ORDER BY CODE) AS SL, Code
FROM party_code_table

ORDER BY CODE

Jan 10 2015

mySQL give permission to a computer IP address

GRANT ALL PRIVILEGES ON *.* TO root@192.168.1.131 IDENTIFIED BY “*************”;

To get a list of MySQL users:

mysql> select user,host from mysql.user;

Oct 6 2014

DataSet panel in SSRS designer (BIDS or SQL Server Data Tools for SQL 2012) is gone

Dataset not showing in the left side ?

If you are using BIDS with SQL 2008 R2 you can only get the “Report Data” menu by clicking inside the actual report layout itself.

Click inside the actual report layout.
Now select “View” from the main menu bar.
Now select “Report Data” which is the last item.

Nov 20 2013

Restore database from network drive

Configuring Security & Sharing on the Remote Server (ServerName)

On the remote server called “ServerName” which has the “DB.bak” file on its file system, browse to the folder including the file, right-click, properties and then follow the screenshots below.

Creating Mapped Network Drive on the SQL Server

Log on the SQL server machine to create a mapped network drive which is mapped to the shared path you already created in the previous step. To do this, follow the screenshot below.

Registering the Mapped Network Path on SQL Server Management Studio

You, you need to register the mapped network path on the SQL Server Management Studio in order to be able to access the mapped drive from SQL Server Management Studio File Explorer. To do so, open SQL Server Management Studio and execute the query below.