Nurah.lee

 SELECT * FROM sys.dm_tran_locks; SELECT creation_time, total_rows, text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) order by creation_time desc SELECT      blocking_session_id AS BlockingSession,     session_id AS BlockedSession,     wait_type, wait_time, wait_resource FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

To trigger synchronization between two tables in Microsoft SQL Server when the source table is updated, you can use SQL Server triggers. Triggers are special stored procedures that are automatically executed or fired in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table. Here's an example of how you can create a trigger to synchronize updates from a source table (SourceTable) to a destination table (DestinationTable):\ CREATE TRIGGER SyncTrigger ON SourceTable AFTER INSERT, UPDATE, DELETE AS BEGIN     SET NOCOUNT ON;     -- Check if the operation is an INSERT or UPDATE     IF EXISTS(SELECT 1 FROM inserted)     BEGIN         -- Perform synchronization for INSERT or UPDATE         MERGE INTO DestinationTable AS dest         USING inserted AS src         ON (dest.PrimaryKey = src.PrimaryKey)  -- Replace PrimaryKey with actual primary...

1. ... 2. ... 3. ... 4. ... 5. ... 6. ... 7. ... 8. ... Sumber rujukan : https://documentation.alphasoftware.com/documentation/pages/HowTo/Databases/Export%20Tables%20from%20a%20Database.xml

    ---All lower case UPDATE stations SET stationname = LOWER(stationname)   ---Capitalize UPDATE stations SET stationname = STUFF(LOWER(stationname), 1, 1, UPPER(LEFT(stationname,1)) )   --Upper case two letter front UPDATE stations SET stationname = STUFF(LOWER(stationname), 1, 2, UPPER(LEFT(stationname,2)) ) WHERE stationname like '__ %'; Ref : https://www.kodyaz.com/articles/sql-capitalize-first-letter-sql-capitalize-string.aspx

  Clustering (Load Balance) TSQL Using Stored Procedure & Linked Server - https://www.youtube.com/watch?v=RbLvYDckiDQ f f f f

  Solution: RESTORE LOG [syncdb_labis] FROM DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\syncdb_labis_LogBackup_2023-01-04_10-46-49.bak' Refer:  SQL Server Database Stuck in Restoring State https://www.mssqltips.com/sqlservertip/5460/sql-server-database-stuck-in-restoring-state/

  Example query: update [signalling].[dbo].[Lokasi] SET eventLocation = s.qEventLocation  FROM [signalling].[dbo].[Lokasi] AS l  INNER JOIN [syncdb].[dbo].[stations] AS s ON s.stationname = l.station;

mssql 'Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.' Solutions:  1)  How can I solve a connection pool problem between ASP.NET and SQL Server?  https://stackoverflow.com/questions/670774/how-can-i-solve-a-connection-pool-problem-between-asp-net-and-sql-server 2) SQL Server Connection Pooling (ADO.NET)   https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#adding-connections 3) d

What's Deadlock in SQL Server? -  https://debug.to/951/transaction-was-deadlocked-on-lock-resources-with-another-process How to fix SQL Server deadlocks -  https://www.red-gate.com/products/dba/sql-monitor/resources/articles/monitor-sql-deadlock Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim -  https://social.msdn.microsoft.com/Forums/en-US/0337e7ea-fc82-4cc2-8ce9-f8d3e04042e8/transaction-process-id-was-deadlocked-on-lock-resources-with-another-process-and-has-been-chosen?forum=aspsqlserver

https://stackoverflow.com/questions/338075/cast-and-isnumeric  https://stackoverflow.com/a/21770230/10763264

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated. Solution: guna data format :  DD/mm/YYYY HH:mm:ss tt INSERT into LCUlog(DateTime,logmessage) values ((convert(datetime,'1/14/2022 4:14:58 PM',103)), '');

Config Connection MSSQL through Network f f f f  References :  https://dba.stackexchange.com/a/62300 http://lexisnexis.custhelp.com/app/answers/answer_view/a_id/1095989/~/sql-server-configuration-manager-general-information

Enable Dark mode -  https://sqlskull.com/2020/06/01/enable-dark-theme-in-sql-server-management-studio/ C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE Shows line number -  https://www.mssqltips.com/sqlservertip/2542/display-line-numbers-in-a-sql-server-management-studio-query-window/

  Typically, to troubleshoot this, you go to SQL Server Configuration Manager (SSCM) and: ensure Shared Memory protocol is enabled ensure Named Pipes protocol is enabled ensure TCP/IP is enabled, and is ahead of the Named Pipes in the settings Maybe it can help:  Could not open a connection to SQL Server Note : If this is a new instance of SQL Server be sure  SQL Server and Windows Authentication  is enabled Right Click the Server in SSMS and pull up server properties Go to Security--> Select 'SQL Server and Windows Authentication Mode' Restart the Server and Login with the credentials https://stackoverflow.com/a/8076038/10763264

Escaping string using @ string path = @"C:\Program Files\Microsoft Visual Studio 10.0\"; On mssql escaping character using ESCAPE  WHERE mycolumn LIKE '%\_%' ESCAPE '\' SELECT isTrack.id AS [translated_pf], platformno FROM stationtrack INNER JOIN isTrack ON isTrack.name = stationtrack.track WHERE stationcode = 'KL' AND isTrack.id LIKE '%KL\_%' ESCAPE '\' ORDER BY platformno ASC; References Why does using an Underscore character in a LIKE filter give me all the results - https://stackoverflow.com/a/19588588/10763264 Escaping in C#: characters, strings, string formats, keywords, identifiers -  https://www.codeproject.com/Articles/371232/Escaping-in-Csharp-characters-strings-string-forma

Apa itu Indexing ?  Contoh coding USE [syncdb] GO SET ANSI_PADDING ON GO /****** Object: Index [train_map] Script Date: 08-Dec-20 16:26:35 ******/ CREATE NONCLUSTERED INDEX [train_map] ON [dbo].[displayfallbackdata] (   [DisplayId] ASC,   [ExpectedDeparture] DESC,   [State] ASC  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  GO Sumber Rujukan Clustered and Nonclustered Indexes Described CREATE INDEX (Transact-SQL) CREATE INDEX Syntax - w3school Top 10 questions and answers about SQL Server Indexes d

Cara - cara untuk setup sqlsrv di local dddddd ddddd ddddd Rujukan  Download driver (my version for backup purpose only) -  https://drive.google.com/file/d/1KVhUuJiO31aV4a77OVuEYFl1l7juvkse/view?usp=sharing PHP Version Support - https://docs.microsoft.com/en-us/sql/connect/php/microsoft-php-drivers-for-sql-server-support-matrix?view=sql-server-2017#php-version-support How to install an SQLSRV extension to php XAMPP -  https://stackoverflow.com/a/50648896 -  https://stackoverflow.com/a/55134712 Download Microsoft driver - https://docs.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server?view=sql-server-ver15#download Addon notes (Tapi tak jadi pun haha) https://sarn.phamornsuwana.com/2018/10/09/installing-xampp-on-windows-10-with-sqlsrv-drivers/

Cara mudah untuk menukar format antara SQL. MSSQL ke MySQL. Di bawah ni Lee letak rujukan.  http://www.sqlines.com/online

 Solution. untick Enforce password expiration Rujukan SQL Server: “The password of the account has expired.”

 Lee simpan kat sini supaya tak lupa cara - cara duplicate database di MSSQL. Right click pada Databases.  Open Restore file and filegroups.  Isi database name dan pilih from device.  select backup device.  locate backup file.  Click OK ➡ OK ➡ Right checkbox restore.  Ok Done.