Thursday, December 7, 2023

SQL Server SSMS stuck on query while truncate the table

 I was going to truncate a table in sqlserver database but it seems to be went in deadlock. Apart from this if issue persist regularly then use delete command instead of truncate command.

List of Deadlock
SELECT
    SESSION_ID
FROM SYS.DM_EXEC_REQUESTS
WHERE BLOCKING_SESSION_ID != 0


Kill the deadlock:
kill SESSION_ID

Create Sequence Using SQL in MYSQL

Generate a range of consecutive/Sequence numbers (1 to 100)  using a MySQL query, see below. Replace 100 number as per your choice, so it will generate sequence of numbers 1 to number you will going to replace

select t0.rn+t1.rn+t2.rn+t3.rn  as Sequence
from
    (select 0 rn union select 1    rn union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    (select 0 rn union select 10   rn union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90) t1,
    (select 0 rn union select 100  rn union select 200 union select 300 union select 400 union select 500 union select 600 union select 700 union select 800 union select 900) t2,
    (select 0 rn union select 1000 rn union select 2000 union select 3000 union select 4000 union select 5000 union select 6000 union select 7000 union select 8000 union select 9000) t3
where
    t3.rn<=100
    and t2.rn<=100
    and t1.rn<=100
    and t0.rn+t1.rn+t2.rn+t3.rn<=100
    and t0.rn+t1.rn+t2.rn+t3.rn<>0
    order by 1

Wednesday, August 23, 2023

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.


Solution

EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO




hide again (Rollback)
-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

Driver class 'org.gjt.mm.mysql.Driver' could not be found

Driver class 'org.gjt.mm.mysql.Driver' could not be found, make sure the 'MySQL' driver (jar file) is installed. org.gjt.mm.mysql.Driver

Issue : Creating connection with MySQL with kettle, was using 

  • Data Integration Kettle version 9.0
  • Mysql Driver version 8.0.25


Error

Driver class 'org.gjt.mm.mysql.Driver' could not be found, make sure the 'MySQL' driver (jar file) is installed. org.gjt.mm.mysql.Driver

Solution

Get version 5 latest driver from url https://downloads.mysql.com/archives/c-j/  (it is 5.1.49)


Saturday, July 1, 2023

ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Server Type - MS SQL  Server

Scanerio : Error faced when tried to apply foreign key constraint on table having data in it (non empty table).

Error

Msg 547, Level 16, State 0, Line 20 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "". The conflict occurred in database "", table "dbo.", column '**'

Solution

Empty table first then apply foreign key

 

web stats