[SQL] Test Connection

I feel this is a good and simple step to check the connection to a database. I would like to re-post here  and credit the writer.

How to Test a Database Connection String using NotePad
Create and Configure a Universal Data Link (.udl) File with Notepad.

I just came across a way to test a data providers connection string (like a SQL Server database) with the help of a plain text file using Notepad. To investigate and test out if your connection string works, your going to want to create a UDL file. To do this, follow these steps:

1) Open up Notepad and create an empty text file, then click File -> click Save -> and save it with the File name: TestConnection.udl to your desktop.
2) Go to your desktop and double-click on the TestConnection.udl file you just created and the Data Link Properties box will popup.
3) Select the Provider tab and Find the provider that you want to connect with and click Next >>.
4) Now from the Connection tab, select or enter your source/ server name -> then enter information to log on to server -> and select the database on the server.
5) Click Test Connection and click OK to save the file.

Note: If errors occur during testing of your connection string, you will get a popup box with the error message.

Once, you’ve successfully tested your connection string, now go and compare the details of your TestConnection.udl with your (website) project connection string to see if they are similiar.

 Source: http://www.gotknowhow.com/articles/test-a-database-connection-string-using-notepad
Credit to: Doug Kennard

[SQL] Generate Multiples Rows of Execution Query

I needed to run few hundreds rows of records and it is very troublesome when I have to change the ID for each time I execute a command. I was taught to write out the command in this way:-

select ‘DECLARE @i INT DECLARE @s VARCHAR(200) EXEC [dbo].[adm_sp_coin_adjust2]  ‘ +
””+PassportID+”’, ”liyen”, ”127.0.0.1”, @i output, @s output ‘ from dbo.MFlist

It grabs all the IDs from the table and the output from the above syntax will be the hundreds rows of records that are ready to be executed. It is really amazing.

[SQL] NEWID()

The following is the syntax of getting a unique ID using the NEWID() function. First, declare a variable to store the unique ID and then set the unique ID to the variable.

DECLARE @chvGUID VARCHAR(20)
SELECT @chvGUID =  (LEFT(NEWID(),(20)))

A common NEWID() function will return values for example like this, ‘A972C577-DFB0-064E-1189-0154C99310DAAC12’

[SP] Cursor

The Cursor function is quite useful at times. Here is one of the syntax of using Cursor. It is possible to use two cursor in a stored procedure.

DECLARE @i INT
DECLARE @s VARCHAR(200)
EXEC [dbo].[adm_sp_tb_register_addautomatching] 4, 1, 2, ‘admin’, ‘127.0.0.1’, @i output, @s output

SELECT @i, @s
SELECT * FROM dbo.ep_tb_event_match
**/
ALTER PROCEDURE [dbo].[adm_sp_tb_register_addautomatching]
(
@i_intEventIdx INT,
@i_intMatchRound INT,
@i_intNumOfTeam INT,
@i_chvUsrCode VARCHAR(50),
@i_chvClientIp VARCHAR(50),
@o_intResult INT OUTPUT,
@o_chvResult VARCHAR(200) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @intMatchIdx BIGINT

SELECT @intMatchIdx=MAX(matchIdx)
FROM dbo.ep_tb_event_match WITH(NOLOCK)

IF @intMatchIdx IS NULL OR @intMatchIdx = ” OR @intMatchIdx = 0
BEGIN
SELECT @intMatchIdx = 1
END
ELSE
BEGIN
SELECT @intMatchIdx = @intMatchIdx+1
END

PRINT ‘MatchIDx’ + CAST(@intMatchIdx AS VARCHAR(50))

DECLARE @intSeqId INT
DECLARE @intTotalTeam INT

SELECT @intTotalTeam=COUNT(matchIdx)
FROM dbo.ep_tb_event_match WITH(NOLOCK)
WHERE eventIdx = @i_intEventIdx AND mt_matchlevel = @i_intMatchRound AND matchIdx = @intMatchIdx

PRINT ‘total’ + CAST(@intTotalTeam AS VARCHAR(50))

WHILE (@intTotalTeam < @i_intNumOfTeam)
BEGIN
SELECT @intSeqId = 1
DECLARE @intRegisterIdx BIGINT
DECLARE @chvIGN VARCHAR(50)
DECLARE @chvMatchGroup VARCHAR(5)
DECLARE @chvTeamName VARCHAR(50)
/**
Get Player/Team List
**/
DECLARE Players_Cursor CURSOR FOR
SELECT a.registerIdx, a.rg_ign, a.rg_matchgroup ,b.tm_name
FROM dbo.ep_tb_register a WITH(NOLOCK) LEFT OUTER JOIN dbo.ep_tb_register_team b WITH(NOLOCK)
ON a.tm_teamcode = b.tm_teamcode
WHERE a.eventIdx = @i_intEventIdx AND a.ev_matchlevel = @i_intMatchRound AND a.rg_status = 100
ORDER BY a.eventIdx, a.ev_matchlevel, a.rg_matchgroup, a.registerIdx
OPEN Players_Cursor
FETCH NEXT FROM Players_Cursor
INTO @intRegisterIdx, @chvIGN, @chvMatchGroup, @chvTeamName
WHILE @@FETCH_STATUS = 0
BEGIN
/**
Add
**/
INSERT INTO dbo.[ep_tb_event_match]
(eventIdx, mt_matchlevel, mt_matchgroup, matchIdx, mt_seqid, registerIdx, mt_ign,
mt_teamname, mt_videolink, mt_createdate, mt_createby, mt_lastupdatedate, my_lastupdateby)
VALUES (@i_intEventIdx, @i_intMatchRound, @chvMatchGroup, @intMatchIdx, @intSeqId, @intRegisterIdx, @chvIGN,
@chvTeamName, ”, GETDATE(), @i_chvUsrCode, GETDATE(), @i_chvUsrCode)
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
SELECT @o_intResult = -1, @o_chvResult = ‘Error : Insert fail’
CLOSE Players_Cursor
DEALLOCATE Players_Cursor
RETURN
END
END
FETCH NEXT FROM Players_Cursor
INTO @intRegisterIdx, @chvIGN, @chvMatchGroup, @chvTeamName
END
CLOSE Players_Cursor
DEALLOCATE Players_Cursor

SELECT @o_intResult = 0, @o_chvResult = ‘Success : Insert ok’
RETURN

[SQL] A SP Calls Another SP

Example of a stored procedure calls/executes another stored procedure. The example syntax creates a temp table and insert the value by executing another stored procedure.

CREATE TABLE #Temp (
[ProfileName] NVARCHAR(20) COLLATE Chinese_PRC_CI_AS,
[Level] VARCHAR(5) COLLATE Chinese_PRC_CI_AS,
[CarOwn] VARCHAR(5) COLLATE Chinese_PRC_CI_AS,
[Guild] VARCHAR(50) COLLATE Chinese_PRC_CI_AS,
[Link] VARCHAR(50) COLLATE Chinese_PRC_CI_AS,
[Announcement] NVARCHAR(500) COLLATE Chinese_PRC_CI_AS
)
INSERT INTO #Temp
EXEC dbo.adm_sp_player_profile @i_chvGameCode, @i_chvPlayerId

[SQL] Create a Function

Example of creating a function and calling it from a stored procedure:

ALTER FUNCTION dbo.mf_fn_redemption_check
(
@i_intItemId BIGINT,
@i_chvPassportId VARCHAR(20)
)
RETURNS INT
AS
BEGIN

DECLARE @intResult INT

SELECT @intResult=COUNT(redeemID) FROM dbo.mf_tb_redemption WITH(NOLOCK)
WHERE itemID=@i_intItemId AND passportID=@i_chvPassportId

IF @intResult = 1
SET @intResult=0
ELSE
SET @intResult=1

RETURN (@intResult)

END

In the stored procedure:
/**
Action: Select by filter
**/
SELECT itemID, itemName, itemType, itemDescription, itemCoinNeeded, itemIsNewbie,
(SELECT dbo.mf_fn_redemption_check (itemID, @chvPassportId)) AS RedeemAction
FROM dbo.mf_tb_item WITH(NOLOCK)
WHERE itemIsNewbie=@i_intIsNewBie

[SQL] Split Function

One of the examples found to do string splitting.
Example syntax:
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
GO

Calling the function
SELECT * FROM dbo.Split (‘,’,’E,M,’)

Another method of calling the function:
SELECT commentHeaderID, comContent, createBy, CONVERT(VARCHAR(10),createDate,111) + ‘ ‘ + CONVERT(VARCHAR(15),createDate,114)
–,(SELECT * FROM dbo.SplitTable (commentHeaderID,’,’,comGame))
FROM dbo.rb_tb_comment_header A WITH(NOLOCK)
INNER JOIN (dbo.SplitTable (commentHeaderID,’,’,comGame)) AS G on A.commentHeaderID=G.intDIX
WHERE commentHeaderID = 1

Another example syntax:
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
GO

IF OBJECT_ID(‘dbo.Split’) IS NOT NULL
DROP FUNCTION dbo.Split

GO
CREATE FUNCTION dbo.Split(@data NVARCHAR(MAX), @delimiter NVARCHAR(5))
RETURNS @t TABLE (data NVARCHAR(max))
AS
BEGIN

DECLARE @textXML XML;
SELECT    @textXML = CAST(‘<d>’ + REPLACE(@data, @delimiter, ‘</d><d>’) + ‘</d>’ AS XML);

INSERT INTO @t(data)
SELECT  T.split.value(‘.’, ‘nvarchar(max)’) AS data
FROM    @textXML.nodes(‘/d’) T(split)

RETURN
END
GO

DECLARE @text NVARCHAR(max)
SELECT @text = REPLICATE(‘ab,’, 300) + ‘ab’

SELECT * FROM dbo.Split(@text, ‘,’)

Besides that, someone also suggested to use the patindex.
Example syntax as below:
SELECT RIGHT(ItemDescription,PATINDEX(‘% %’,
REVERSE(ItemDescription))-1) AS COL1,
LTRIM(REVERSE(LEFT(REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX(‘% %’, REVERSE(ItemDescription))))),
PATINDEX(‘% %’,REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX(‘% %’, REVERSE(ItemDescription)))))))))
AS COL2
FROM Inventory

[SQL] MOD

It is applicable for MSSQL and MySQL.

The function MOD returns the remainder of the division from 2 integer values.

SQL Syntax 1:		MOD( dividend, divisor )
Dividend:		Any INT expression.
Divisor:		Any INT expression.
Escape Syntax:		{fn MOD( dividend, divisor )}
Return Type:		INT

Examples:

• SELECT MOD(20,3), {fn mod(15,3)}Returns:2, 0

[SQL] Back Up and Restore Database

Guidance on how to do back up and restore a database:-

Using GUI

How to backup and restore database on Microsoft SQL Server 2005

http://www.techrepublic.com/blog/window-on-windows/how-do-i-restore-a-sql-server-database-to-a-new-server/454

Using SQL syntax
http://msdn.microsoft.com/en-us/library/ms191304%28v=sql.90%29.aspx
http://msdn.microsoft.com/en-us/library/ms186858.aspx

[SQL] Move Database

I have tried to move a database from one server to another server. I was taught to use the Take Offline/Online method and Detach/Attach method. For some databases, I cannot use the offline/online method and therefore, I used the detach/attach method to complete the task.

Shortly after the database has been taken offline or detach, quickly copy out the mdf and log files in to another back up location/folder. Once the copy process is done, quickly put the database back online or re-attach the database.

Then, move the database to the new server and set up the new database from there. It is also using the same method, take offline/detach the database when I wanted to copy the mdf and log file into the location.

Additional reading from an article online,
Two methods for moving database files. The first method involves detaching a database, moving the files, then re-attaching the database. The second method involves taking a database offline, running the ALTER DATABASE command to change file locations, moving the files, and bringing the database back online.

Both methods suffer from a major limitation: The database has to be offline. Another limitation is that for these methods to work, the DBA needs full access to the folders where the database files are kept. In large organizations, this often isn’t the case, so the DBA doesn’t have sufficient permissions to move the files. Finally, the detach/re-attach method carries with it additional issues, such as having to change the database owner and having to reset the database options to their defaults (e.g., ENABLE_BROKER).