It’s me

July 14, 2009

SQL Server – 중복되는 레코드 삭제

Filed under: SQL SERVER — rothmans @ 2:50 pm

다음 코드는 중복되는 레코드를 삭제할 때 유용하다. 반드시 테이블은 identity 컬럼을 가져야 하며 중복되는 레코드를 판단하는 기준이된다. 이 예제에서 IDX는 identity 컬림이며, 중복을 확인할 필드명은 dup_col1, dup_col2, dup_col3 이다.


DELETE FROM MyTABLE
WHERE IDX NOT IN
(
SELECT MAX(IDX)
FROM MyTABLE
GROUP BY dup_col1, dup_col2, dup_col3
)

June 25, 2009

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

Filed under: SQL SERVER — Tags: , — rothmans @ 3:16 pm

Visual Studio 2008과 Visual Studio 2008 Express Edition을 사용하고, ASP.NET의 App_Data 에 MDF 파일을 사용하려고 할때 다음과 같은 에러 메시지가 발생하는 경우가 있습니다

“사용자 인스턴스의 프로세스를 시작하지 못했기 때문에 SQL Server의 사용자 인스턴스를 생성하지 못했습니다. 연결이 닫힙니다.”

“Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.”

web.config 내용

<add name=”ConnectionString” connectionString=”Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDB.mdf;Integrated Security=True;User Instance=True” providerName=”System.Data.SqlClient”/>
 

해결방법
다음 폴더를 삭제합니다. 삭제 후 재시도 하면 폴더가 재생성되고 MDF 파일 및 로그등이 생성됩니다

C:\Documents and Settings\Administrator\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

April 29, 2009

Previous releases of Microsoft Visual Studio 2008

Filed under: SQL SERVER — rothmans @ 1:54 pm

Microsoft SQL 2008을 설치할 때 다음과 같이 Visual Studio 2008 SP1 을 요구하는 메시지가 표시됩니다.

규칙 “Microsoft Visual Studio 2008의 이전 릴리스 확인”
이 컴퓨터에 Microsoft Visual Studio 2008의 이전 릴리스가 설치되어 있습니다. SQL Server 2008을 설치하기 전에 Microsoft Visual Studio 2008을 SP1으로 업그레이드하십시오.

Rule “Previous releases of Microsoft Visual Studio 2008″ failed.
A previous release of Microsoft Visual Studio 2008 is installed on this computer. Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008.

Visual Studio 2008 SP1 이 설치되어 있어야 하나, 다음과 같은 방법으로 설치가 가능합니다.

Setup /ACTION=install /SkipRules=VSShellInstalledRule RebootRequiredCheck

이 방법은 문서화되어 있지는 않지만, VSShellInstalledRule 을 체크하는 부분을 Skip 함으로써 설치가 가능하게 합니다

February 25, 2009

SELECT * INTO {TARGET} FROM {SOURCE}

Filed under: SQL SERVER — rothmans @ 1:29 pm

- 새로운 테이블 생성, 전체 컬럼 INSERT
SELECT * INTO {TARGET} FROM {SOURCE}

- 새로운 테이블 생성, 일부 컬럼 INSERT
SELECT * INTO {TARGET}
  FROM (
                  SELECT COLUMN1, COLUMN2 FROM {SOURCE}
                ) AS TMP_TBL

- 기존 테이블 이용, 전체 컬럼 INSERT
INSERT INTO {TARGET} SELECT * FROM {SOURCE}

- 기존 테이블 이용, 일부 컬럼 INSERT
INSERT INTO {TARGET} SELECT COLUMN1, COLUMN2 FROM {SOURCE}

December 9, 2008

지정한 기간내의 일자 목록

Filed under: SQL SERVER — rothmans @ 5:35 pm

declare @todate varchar(10)
declare @frdate varchar(10)
declare @cudate varchar(10)
declare @dtdiff int
declare @cnt int

if object_id (‘tempdb..#tempx’) is not null drop table #tempx
create table #tempx (idx int identity (1,1), cudate varchar(10))

set @frdate = ‘2008-12-30′
set @todate = ‘2009-01-02′
set @cnt = 0
set @dtdiff = datediff(day, convert(datetime, @frdate), convert(datetime, @todate))

while @cnt <= @dtdiff
begin
set @cudate = convert(varchar(10), dateadd(day, @cnt, convert(datetime, @frdate)), 120)
insert into #tempx (cudate) values (@cudate)
set @cnt = @cnt + 1
end

select * from #tempx

간단한 임시 테이블 사용 Stored procedure

Filed under: SQL SERVER — Tags: , , , — rothmans @ 10:45 am

create procedure temporay_table_test
as
begin
declare @frdate datetime
declare @cnt int
create table #tempx (idx int identity (1,1), frdate datetime)

set @cnt = 1
while @cnt < 5
begin
waitfor delay ‘00:00:01′ — 시간 delay
set @frdate = getdate()
set @cnt = @cnt + 1
insert into #tempx (frdate) values (@frdate)
end
select * from #tempx
end

if object_id (‘tempdb..#tempx’) is not null drop table #tempx

October 5, 2007

SQL 서버에서 테이블 내역서 뽑기

Filed under: SQL SERVER — rothmans @ 3:31 pm

SQL 서버에서 테이블 내역서 뽑기 

SQL 서버에서 Table 내역서를 가져온다.

select D.[value] as tName,A.[name] as TableName,e.[value] AS cName,B.[Name] as ColumnName,
CASE WHEN c.[Name] IN (‘int’,'bit’) THEN c.[Name]
ELSE c.[Name] + ‘(‘ +
CASE WHEN B.Max_length = -1 THEN ‘MAX’ ELSE Cast(B.Max_length AS Varchar(100)) END
 + ‘)’ END as DataType
from sys.tables a
 inner join sys.columns b on a.object_id = b.object_id
 inner join sys.types c on b.system_type_id = c.system_type_id
 inner join sys.extended_properties d on A.object_id = d.major_id and d.minor_id = 0
 inner join sys.extended_properties e on A.object_id = e.major_id and e.minor_id = b.column_id
where A.[name] not in (’sysdiagrams’)
order by 2
sys.tables : 테이블 정보

sys.columns : Column 정보

sys.types : 데이타 타입정보

sys.extended_properties : 테이블과 Column의 한글 정보
 

September 27, 2007

SQL2005 저장프로시져 모니터

Filed under: SQL SERVER — rothmans @ 7:49 pm

– 1.저장프로시져별 실행수 뽑기
select db_name(st.dbid) DBName
,object_schema_name(st.objectid,dbid) SchemaName
,object_name(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
from sys.dm_exec_cached_plans cp
join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(cp.plan_handle)st
where DB_Name(st.dbid) is not null and cp.objtype = ‘proc’
group by DB_Name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)
order by sum(qs.execution_count) desc

–2. CPU소모량이 많은 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(st.objectid,dbid) SchemaName
,object_name(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
,sum(qs.total_worker_time) total_cpu_time
,sum(qs.total_worker_time) / (sum(qs.execution_count) * 1.0) avg_cpu_time
from sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where db_name(st.dbid) is not null and cp.objtype=’proc’
group by db_name(st.dbid), object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)
order by sum(qs.total_worker_time) desc

–3. IO량이 많은 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(objectid,st.dbid) SchemaName
,object_name(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) total_IO
,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) / sum
(execution_count) avg_total_IO
,sum(qs.total_physical_reads) total_physical_reads
,sum(qs.total_physical_reads) / (sum(execution_count) * 1.0) avg_physical_read
,sum(qs.total_logical_reads) total_logical_reads
,sum(qs.total_logical_reads) / (sum(execution_count) * 1.0) avg_logical_read
,sum(qs.total_logical_writes) total_logical_writes
,sum(qs.total_logical_writes) / (sum(execution_count) * 1.0) avg_logical_writes
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where db_name(st.dbid) is not null and cp.objtype = ‘proc’
group by db_name(st.dbid),object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)
order by sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) desc

–4. 처리시간이 긴 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(objectid,st.dbid) SchemaName
,object_name(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_elapsed_time) total_elapsed_time
,sum(qs.total_elapsed_time) / sum(execution_count) avg_elapsed_time
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle)st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where db_name(st.dbid) is not null and cp.objtype = ‘proc’
group by db_name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)
order by sum(qs.total_elapsed_time) desc

참고 : http://www.databasejournal.com/features/mssql/article.php/3687186

July 18, 2007

SQL Server 를 사용하여 Date/Time 변환하기

Filed under: SQL SERVER — rothmans @ 9:32 am

SQL Server 는 일자/시간 형식으로 변환할 수 있는 옵션을 제공합니다. 가장 일반적인 경우가 getdate() 인데 현재 서버의 일자와 시간을 가져옵니다. 만약 유니버설 시간/일자를 가져올 경우에는 getutcdate()를 사용하며 다음의 예제와 같이 사용할 수 있습니다.

DATE FORMATS
Format # Query (current date: 12/30/2006) Sample
1 select convert(varchar, getdate(), 1) 12/30/06
2 select convert(varchar, getdate(), 2) 06.12.30
3 select convert(varchar, getdate(), 3) 30/12/06
4 select convert(varchar, getdate(), 4) 30.12.06
5 select convert(varchar, getdate(), 5) 30-12-06
6 select convert(varchar, getdate(), 6) 30 Dec 06
7 select convert(varchar, getdate(), 7) Dec 30, 06
10 select convert(varchar, getdate(), 10) 12-30-06
11 select convert(varchar, getdate(), 11) 06/12/30
101 select convert(varchar, getdate(), 101) 12/30/2006
102 select convert(varchar, getdate(), 102) 2006.12.30
103 select convert(varchar, getdate(), 103) 30/12/2006
104 select convert(varchar, getdate(), 104) 30.12.2006
105 select convert(varchar, getdate(), 105) 30-12-2006
106 select convert(varchar, getdate(), 106) 30 Dec 2006
107 select convert(varchar, getdate(), 107) Dec 30, 2006
110 select convert(varchar, getdate(), 110) 12-30-2006
111 select convert(varchar, getdate(), 111) 2006/12/30

TIME FORMATS
8 or 108 select convert(varchar, getdate(), 8) 00:38:54
9 or 109 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM
14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840

다음과 같이 문자열로 구분할 수도 있습니다

Sample statement Output
select replace(convert(varchar, getdate(),101),’/',”) 12302006
select replace(convert(varchar, getdate(),101),’/',”) + replace(convert(varchar, getdate(),108),’:',”) 12302006004426

Next Steps

Blog at WordPress.com.