Tag: dbms
Clone SQL Server database
Option 1 Detach Original Database, copy its MDF and LDF files and create a new Database from copied files. USE master; GO EXEC sp_detach_db @dbname = N'OriginalDB'; GO copy "c:\\OriginalDB.mdf" "c:\\NewDB.mdf" copy "c:\\OriginalDB_log.ldf" "c:\\NewDB_log.ldf" USE master; GO CREATE DATABASE OriginalDB ON (FILENAME = 'C:\\OriginalDB.mdf'), (FILENAME = 'C:\\OriginalDB_log.ldf') FOR ATTACH; GO CREATE DATABASE NewDB ON (FILENAME […]
SQL Server Handle Transaction and Try Catch
SQL Server handles exceptions just like any programming language. Below is a stub to help understand SQL exception handling and you can use in your Procedures as a starting point. BEGIN TRAN BEGIN TRY EXEC P1 EXEC P2 COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN DECLARE @ErMessage NVARCHAR(2048), @ErSeverity INT, @ErState INT SELECT @ErMessage […]
SQL SERVER – Remove the last character in a string
Sometimes it is required to remove last character usually a comma from SQL string, below are 3 way to achieve it. Option 1: Using LEFT function DECLARE @String as VARCHAR(50) SET @String='1,2,3,4,5,' SELECT @String As [String] ,LEFT(@String,LEN(@String)-1) As [Last Character removed from string] GO Option 2: Using STUFF function DECLARE @String as VARCHAR(50) SET @String='1,2,3,4,5,' […]
SQL Server CONVERT() Function
Definition and Usage The CONVERT() function is a general function that converts an expression of one data type to another. The CONVERT() function can be used to display date/time data in different formats. Syntax CONVERT(data_type(length),expression,style) Value Description data_type(length) Specifies the target data type (with an optional length) expression Specifies the value to be converted style […]
SQL Snippets
Rounding off to 2 decimal places cast(round([value],2) as decimal(18,2)) Compare only Year from datetime field select * from [table_name] where year(date_time_field) = year(getdate()) check if a SQL server string is null or empty ISNULL(NULLIF(text, ''), '') AS Offer_Text The differences between LEN and DATALENGTH in SQL Server LEN Returns the number of characters, rather than […]
Accessing MySQL databases from remote computer
To expose MySQL other than localhost uncomment following line in /etc/mysql/my.cnf and assign to your computers IP address and not loopback bind-address = xxx.xxx.xxx.xxx # replace xxx with IP Next for a remote user to connect with the correct privileges you need to have that user created in both the localhost and '%' as in. […]
SQL Joins
Comprehensive image to show how SQL joins work