Set Number with decimal format in SQL SERVER

When we want to set number with decimal format

Example 205402.5
And the result = 205402.50

We can convert amount value to decimal or numeric

Example in SQL Server =
select cast(205402.5 as decimal(10,2))

 

Technorati Tags: decimal format in SQL SERVER,set amount with decimal format

Check temporary table exist or not in SQL Server

You can check temporary table exist or not in SQL Server, and this is the keyword :

IF OBJECT_ID('tempdb..#tmp_attendance') IS NOT NULL
begin
  drop table #tmp_attendance
end

It work !!!




PS : i tried this in SQL Server  =  Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

Technorati Tags: Check temporary table exist or not in SQL Server,Check temp table in SQL Server

How to add Linked Server in SQL Server

I use SQL Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) in this case.

In some condition we need to access different server and different database. Example we need to see data attendance in server A, and compared with server B (server or pc) with the same database type (in this case SQL Server)

Wondering how to do it ?, here the steps :
1. Check server that already linked in your pc

use master
select * from sysservers

2. Set linked server that want to add

-- add linked server
EXEC master.dbo.sp_addlinkedserver @server = N'156.17.22.100', @srvproduct=N'SQL Server'

-- set password to access database
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'156.17.22.100'
, @locallogin = NULL 
, @useself = N'False'
, @rmtuser = N'sa'
, @rmtpassword = N'passwordvalue'

3. Testing linked server connection

EXEC sp_testlinkedserver N'156.17.22.100'

if connection success then information displayed like this :

Then you can connect with the linked server.


You can try this :

-- example
select * from [Servername].[databasename].[ownername].[tablename]
select * from [156.17.22.100].[dbTest].[dbo].[Personal]

Technorati Tags: How to add Linked Server in SQL Server,linked server in sql server,view linked server,test linked server

How to reset identity on SQL Server

When we create temporary table that contain row number on that table, we can create it by using int IDENTITY(1,1) – for iteration 1 step, here the scripts that we use :

drop table #tempTable

create table #tempTable
(  id int identity(1,1)
  ,name varchar(50)
  )

insert #tempTable
select 'John'

insert #tempTable
select 'Light'

insert #tempTable
select 'Lus'

delete #tempTable

insert #tempTable
select 'JJ'

insert #tempTable
select 'BB'

delete #tempTable
-- reset identity value on table
--DBCC CHECKIDENT (#tempTable, RESEED, 0)


insert #tempTable
select 'AA'

insert #tempTable
select 'DD'


select * from #tempTable

And the result like this :







If we use keyword : DBCC CHECKIDENT we can handle reset identity column value in SQL Server, here the script that we tried:

drop table #tempTable

create table #tempTable
(  id int identity(1,1)
  ,name varchar(50)
  )

insert #tempTable
select 'John'

insert #tempTable
select 'Light'

insert #tempTable
select 'Lus'

delete #tempTable

insert #tempTable
select 'JJ'

insert #tempTable
select 'BB'

delete #tempTable
-- reset identity value on table
DBCC CHECKIDENT (#tempTable, RESEED, 0)


insert #tempTable
select 'AA'

insert #tempTable
select 'DD'


select * from #tempTable
-- check identity lastposition
DBCC CHECKIDENT (#tempTable)

And the result will be right this :







Hope this information will help us to know SQL Server more.

Technorati Tags: Reset Identity Column Value in SQL Server,How to reset identity on SQL Server,Identity,Identity SQL Server

To solve problem on SQL Server : truncate_only' is not a recognized BACKUP option

One day i tried to shrink my log file on sql server, but the file xxx_log ( file.ldf –physical file ) still on huge size. Previous script that i use :

BACKUP LOG DATABASE WITH TRUNCATE_ONLY

And i get this issue : truncate_only' is not a recognized BACKUP option

This issue happen on SQL Server 2008, and after i run this scripts it work for me, hope it will work with you.

-- this script is work on sql server 2008, it work for me
-- Use sp_helpfile to identify the logical file
USE DatabaseName;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
-- the log file name (dbname_log)
DBCC SHRINKFILE (DatabaseName_log , 1);  
GO
-- Reset the database recovery model.
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GO

Shrink Log file on SQL Server

I write this scripts on SQL Server 2008, and this script is work.
You can test this scripts on your SQL server management studio.

declare @dbName	nvarchar(300)
		,@sqlSyntax nvarchar(2000)
set @dbName = 'DatabaseName'				-- database name to shrink
set @sqlSyntax = 'use ' + @dbName + 
	'

	declare @fileLogNm	nvarchar(300)
	
	set @fileLogNm = (
	select name from sys.master_files
	where type = 1
	and database_id = db_id()
	)

	DBCC SHRINKFILE(@fileLogNm, 1)
	BACKUP LOG ' + @dbName + ' WITH TRUNCATE_ONLY
	DBCC SHRINKFILE(@fileLogNm, 1)
	'				
exec (@sqlSyntax)

Hope this scripts will work with you.

Add Job with Script – SQL Server 2010

This is the main step to create Job on SQL Server (2005 or above) with scripts :

1. for add job                      = sp_add_job 2. for add job steps              = sp_add_jobstep
3. for add job schedule          = sp_add_jobschedule 4. for add job target server    = sp_add_jobserver
This complete scripts, you can download on this links.

Make better world with your passion.

Menghitung Total Hari dalam suatu Bulan – SQL Server

How to count total days on month.

Cari-cari informasi dalam SQL Server bagaimana menghitung total hari untuk bulan-bulan tertentu. Hal ini dapat dilakukan dengan membuat function ini dan menjalankannya dalam Query Analyzer.

Cara membuat functionnya :

CREATE FUNCTION DaysInMonth 
	(@date DATETIME)
RETURNS INTEGER
AS
BEGIN
	RETURN 
	CASE WHEN MONTH(@date) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
			WHEN MONTH(@date) = 2 THEN 
				CASE WHEN YEAR(@date) % 4 = 0 THEN 29 ELSE 28 END
			ELSE 30  END
END

Sedangkan untuk menjalankannya dengan script berikut ini :

select dbo.daysInMonth('2008/2/01')

Selamat berquery ria.

Menyimpan Hasil Store Procedure ke dalam Table Temporary

Kita bisa menyimpan hasil SP untuk digunakan lagi di sp yang lainnya, dengan Sql Server 2005 hal ini mudah dilakukan, caranya sebagai berikut:

misal ada sp - procGetMhs2009_IT

lalu kita hendak membuat sp baru - procGetMhs2009 , dimana gabungan dengan sp yg telah ada sebelumnya

maka sp baru tersebut kita harus:
1. membuat table temporary #mhs2009IT - pastikan struktur datanya sama dengan sp - procGetMhs2009_IT

2. kemudian sintak sql nya:
insert into #mhs2009IT
exec procGetMhs2009_IT

lalu data table #mhs2009IT bisa olah untuk dugunakan dalam sp yang baru tersebut.