How To Added Linked Server in SQL Server

Read more »

Apply To :
Microsoft SQL Server 2008 R2

Problem :
Want to access stored procedure and table in another SQL Server

How To Create an Stored Procedure with COMMIT or ROLLBACK Transaction

Read more »

Apply To :
* Microsoft SQL Server 2008 R2


Want to :

* Create an procedure with syntax COMMIT TRANSACTION and ROLLBACK TRANSACTION

How To Create Pivot Table with Flexible Column

Read more »

Apply To :
* Microsoft SQL Server 2008 R2

Problem :

* Want to create pivot table with flexible column 

How To Create Temporary Table in Multiple User

Read more »

Apply To :
* Microsoft SQL Server 2008 R2

Problem :

* Want to create query or procedure for temporary table that use in multiple user
* Using syntax procedure in procedure again

Create Function with Return Table in SQL Server

Read more »

Apply To :
* Microsoft SQL Server 2008 R2

Problem :

* Want to using CREATE FUNCTION with Parameters syntax then Return an TABLE 

How To Start or Stop Services SQL Server Agent

Read more »

Apply To :
* Microsoft SQL Server 2008 R2
* Microsoft Windows Server 2008 Standard


Problem : 

* Want to start or stop services of SQL Server Agent
* Using Command Prompt or File System Object
* Create schedulling task

How To Create Flexible Variable Using Cursor in SQL

Read more »

Example How To Select Multiple Column in SQL Using Cursor

Read more »

Example of Pivot Table - 002

Read more »

Apply To :
* Microsoft SQL Server 2008 R2

How To Create Connection with Different Database Server

Read more »

Apply To :
* Microsoft SQL Server 2000
* Microsoft Visual Basic 6

Want to :
* Create connection with different Database Server

Query Paging Using SQL Server

Read more »

Apply To :
* Microsoft SQL Server 2000

How To Schedulling Automatic Restore Database SQL Server

Read more »

Apply To :
* Microsoft SQL Server 2000
* Microsoft SQL Server 2008 R2

Want to :

* Create Schedulling Automatic Restore of Database 

* Use an Task Scheduler 

* Using VBScript

* Using osql command  

* Create an output file if already done restore processs 

* To know the last progress date of restore

Date Formats SQL Server

Read more »

Apply To :
Microsoft SQL Server 2008 R2


1.
Date Format = Mon DD YYYY HH:MI AM (or PM)
Standard = Default
SQL Statement =
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)   [Mon DD YYYY HH:MI AM (or PM)]
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Results Messages:

How To Add Auto Running Number in Selected Data

Read more »

Apply To:
Microsoft SQL Server 2008 R2

Problem: 

Want to add a auto running number in selected data. 

Solution:

1. Example, dummy data is

How To Convert Format Datetime Become HH:MM

Read more »

Apply To:
Microsoft SQL Server 2000

                                     

How To Convert Format Datetime Become DD-MMM-YYYY

Read more »

Apply To:
Microsoft SQL Server 2000

1. Example: Table "AbsensiHistory"

2. Run this query
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
select tglabsen,
replace(convert(char(15),tglabsen,106),' ','-') [Format-DD-MMM-YYYY]

from AbsensiHistory

How To Search / Find String or Text in Column of Specific Table SQL Server

Read more »

Apply in:
Microsoft SQL Server 2008 R2

1.
Query:
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
CREATE PROCEDURE sp_FindStringInTable_Test
@stringToFind VARCHAR(100),
@schema sysname,
@table sysname
AS

DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)

How To Search / Find Text or String in Column of Tables SQL Server

Read more »

1.
Example: Use Database Northwind
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
USE Northwind
GO
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------


2.
Query:
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
CREATE PROC SearchAllTables_Test
(
@SearchStr nvarchar(100)
)
AS
BEGIN

How To Search String in Database Objects

Read more »

1. Create a procedure

-----code:start------------------------------------------------------------

CREATE PROCEDURE [dbo].[Test_DBA_FindStringInDB]
(
/* String to find */
@chvStringToFind varchar(256)

/* Only look for objects of this type */
,@chrObjectType char(2)=null

/* Number of characters to extract before and after the string to find */
,@intNbCharToExtract int=50
)

AS

How To Show Column Headers To Row Headers in Database

Read more »

1. Create Example Table

-----code:start----------------------------------------------------
Create table #Sample_Table_1
(
num int
,descr varchar(20)
,[amount] decimal(9,2)
,[Date_1] smalldatetime
)
-----code:end----------------------------------------------------


2. Try to insert dummy data