Nurah.lee

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. 

Nota Ringkas List nota Code Sample Code Description Rujukan SET LOCK_TIMEOUT (Transact-SQL) sp_testlinkedserver (Transact-SQL)

Rujukan :  Date and Time Conversions Using SQL Server https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/ SQL Server CONVERT() Function https://www.w3schools.com/sql/func_sqlserver_convert.asp