sysobjects 의 xtype

 C  = CHECK 제약 조건
 D  = 기본 값 또는 DEFAULT 제약조건
 F = FOREIGN KEY 제약조건
 L  = 로그
FN = 스칼라 함수
 P  = 저장 프로시저
PK = PRIMARY KEY 제약 조건(유형은 K)
RF = 복제필터 저장 프로시저
 S  = 시스템 테이블
TF = 테이블 함수
TR = 트리거
 U  = 사용자 테이블
UQ = UNIQUE 제약 조건(유형은 K)
 V  = 뷰
 X  = 확장 저장 프로시저

Analysis Services Linked Server error (The peer prematurely closed the connection)

분석서버 연결된 서버 생성 후 에러 메세지

연결된 서버 ""의 OLE DB 공급자 "MSOLAP" 이(가) 메시지 "피어에서 연결을 중간에 닫았습니다" 을(를) 반환했습니다. (Microsoft SQL Server, Error: 7303)

메세지 창

영문메세지

An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.Connectionlnfo)

Cannot initialize the data source object of OLE DB provider MSOLAP’ For linked server " ",

OLE DB provider “MSOLAP” For linked server “ ” returned message “An error was encountered in the transport layer.”.

OLE DB provider “MSOLAP” for linked server “ ’ returned message “The peer prematurely closed the connection.”. (Microsoft SQL Server, Error: 7303)

 EXEC MASTER.DBO.SP_ADDLINKEDSERVER
  @server = N'LinkedServerName',
  @srvproduct=N'MSOLAP',
  @provider=N'MSOLAP',
  @datasrc=N'domainName',
  @catalog=N'catalogName'
EXEC MASTER.DBO.SP_ADDLINKEDSRVLOGIN
  @rmtsrvname=N'LinkedServerName',
  @useself=N'False',
  @locallogin=NULL,
  @rmtuser=N'domainName\administrator',
  @rmtpassword='password'  

Search for Stored Procedure Containing Text

특정 텍스트가 포함된 프로시져 찾기

SELECT DISTINCT so.name

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id=so.id

WHERE sc.TEXT LIKE '%keyWord%'

get a list of table & field from stored procedure

프로시저에 사용된 테이블, 컬럼 리스트 출력

create procedure sp_sp_tbl_col_list

as 

select  

         bb.name           as spName

,        cc.name           as tableName

,        dd.COLUMN_NAME    as columnName

,        dd.DATA_TYPE      as dataType

,        dd.IS_NULLABLE    as isNullable

,        ISNULL(

                  ISNULL(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)

                            ,'( '+CONVERT(VARCHAR,NUMERIC_PRECISION)+', '+CONVERT(VARCHAR,NUMERIC_SCALE)+' )')

                  , DATA_TYPE )     as dataLenth

from sysdepends                    aa

inner join sysobjects      bb

on aa.id = bb.id

inner join sysobjects      cc

on aa.depid = cc.id

left outer join INFORMATION_SCHEMA.COLUMNS dd

on aa.depnumber = dd.ORDINAL_POSITION

where bb.xtype = 'P'

and cc.name = dd.TABLE_NAME

order by 1,2,3

Using order by in View

뷰에서 정렬 사용

뷰 작성시 Order by를 기본적으로 사용할 수 없으나
TOP PERCENT 등을 사용하여 정렬을 사용할 수 있다.

- 코드, 코드명이 정렬되지 않은 데이터 생성
CREATE TABLE ORD_TEST (

        ORD_CODE VARCHAR(2)

,       ORD_NAME VARCHAR(2)

)

INSERT INTO ORD_TEST SELECT '02' AS ORD_CODE , 'V0' AS ORD_NAME 

INSERT INTO ORD_TEST SELECT '03' AS ORD_CODE , 'L0' AS ORD_NAME 

INSERT INTO ORD_TEST SELECT '00' AS ORD_CODE , 'K0' AS ORD_NAME 

INSERT INTO ORD_TEST SELECT '05' AS ORD_CODE , 'T0' AS ORD_NAME 

INSERT INTO ORD_TEST SELECT '04' AS ORD_CODE , 'S0' AS ORD_NAME 

INSERT INTO ORD_TEST SELECT '09' AS ORD_CODE , 'A0' AS ORD_NAME 

INSERT INTO ORD_TEST SELECT '07' AS ORD_CODE , 'B0' AS ORD_NAME 

INSERT INTO ORD_TEST SELECT '01' AS ORD_CODE , 'E0' AS ORD_NAME

SELECT * FROM ORD_TEST

# TOP Percent 99.9999 를 사용한 정렬  

(전체 Row수를 감안하여 누락되는 Row가 없도록 소숫점을 지정한다.)
CREATE VIEW VW_ORD_TEST

AS

SELECT TOP 99.9999 PERCENT  ORD_CODE , ORD_NAME  FROM ORD_TEST

ORDER BY ORD_CODE

SELECT * FROM VW_ORD_TEST

# TOP (number) 를 사용한 정렬

(결과 예상 Row수 보다 높은 숫자를 지정 또는 해당 테이블의 Rowcount를 지정)
ALTER VIEW VW_ORD_TEST

AS

SELECT TOP 10000 ORD_CODE , ORD_NAME  FROM ORD_TEST
-- SELECT TOP (select count(*) from ORD_TEST) ORD_CODE , ORD_NAME  FROM ORD_TEST

ORDER BY ORD_CODE 

SELECT * FROM VW_ORD_TEST

참고: top 100 Percent를 사용했을 때는 정렬이 되지 않음.

ALTER VIEW VW_ORD_TEST

AS

SELECT TOP 100 PERCENT  ORD_CODE , ORD_NAME  FROM ORD_TEST

ORDER BY ORD_CODE

SELECT * FROM VW_ORD_TEST

DROP VIEW VW_ORD_TEST

DROP TABLE ORD_TEST

대용량 데이터베이스에서 효과적인 과거 데이터 삭제

8. 과거데이터 효과적으로 삭제하기

# DELETE FROM ~ WHERE ~ 와 같은 방법으로 데이터를 삭제하지 않는다.
(LOCK, LOGGING에대한 문제가 발생하며 위와 같은 쿼리 실행중 취소를 하면 롤백 또한 길어진다.)

# 일반적으로 인덱스가 잡히지 않은 테이블에 적재 하는 것이 빠르다.
 (인덱스 없는 테이블에 적재 > 인덱스 생성 > 테이블 대체(테이블명을 수정하여 원래의 테이블로)

# 데이터를 삭제하는 방법으로 루프문 내에 delete top (1000) 과 같은 쿼리를 반복 수행하는 방법도 있다.

# 삭제여부와 같은 컬럼을 두어 삭제대상 데이터에 삭제여부 컬럼을 업데이트 한 후
빠른처리를 하지 않아도 될 시간에 해당 데이터를 삭제하는 것도 하나의 방법이다.
(ex>새벽에 일배치가 완료된 후 여유가 되는시간에 삭제)

참조 >>  Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

Forcefully Rename a SQL Server Database

SQL Server 강제로 디비명 변경

작업을 수행하기 위해 데이터베이스를 배타적으로 잠글 수 없습니다. 

라는 메세지가 나오면서 Rename이 되지 않을 때 아래와 같이 해결할 수 있다. 

ALTER DATABASE [old_name]
 
 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [old_name]
 
 MODIFY NAME = [new_name]
GO
ALTER DATABASE [new_name]
 SET MULTI_USER
GO

IIS 7 file download 404 error

web.config 파일에 아래와 같이 다운로드할 파일의 확장자를 추가해준다.

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <system.webServer>
    <security>
      <requestFiltering>
        <fileExtensions allowUnlisted="true" >
          <remove fileExtension=".ZIP" />
          <add fileExtension=".ZIP" allowed="true"/>
        </fileExtensions>
      </requestFiltering>
    </security>
  </system.webServer>
</configuration>

STUFF & FOR XML

DECLARE @SAMPLE TABLE (ID INT, CODE VARCHAR(3))
INSERT @SAMPLE
SELECT 290780, 'LT' UNION ALL
SELECT 290780, 'AY' UNION ALL
SELECT 290781, 'ILS' UNION ALL
SELECT 290780, 'AY'

SELECT
 DISTINCT S1.ID,
 STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + S2.CODE FROM @SAMPLE AS S2 WHERE S2.ID = S1.ID ORDER BY ',' + S2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @SAMPLE AS S1
ORDER BY S1.ID

SELECT
 DISTINCT S1.ID
, STUFF((SELECT TOP 100 PERCENT ',' + S2.CODE FROM @SAMPLE AS S2 WHERE S2.ID = S1.ID ORDER BY ',' + S2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @SAMPLE AS S1
ORDER BY S1.ID

SELECT
 DISTINCT S1.ID
, STUFF((SELECT ',' + S2.CODE FROM @SAMPLE AS S2 WHERE S2.ID = S1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM @SAMPLE AS S1
ORDER BY S1.ID

SSAS Clear Cache

XMLA command for clearing the Analysis Services cache
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Adventure Works DW</DatabaseID>
  </Object>
</ClearCache>

Dimension default_member

ALTER CUBE CurrentCube
    UPDATE DIMENSION [dim_type].[type]
    , DEFAULT_MEMBER = [dim_type].[type].&[1];

Percentage over all dimension and axis

계산된 측정값 (모든 차원에 대한 비율)

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sells %]
AS iif(
([Measures].[Sells],
Axis(0).Item(0).item(Axis(0).item(0).count-1).Dimension.currentmember.Parent)=0,
null,
[Measures].[Sells]/
([Measures].[Sells],Axis(0).Item(0).item(Axis(0).item(0).count-1).Dimension.currentmember.Parent) ),
FORMAT_STRING = "Percent",
VISIBLE = 1 ;

SSIS "VS_NEEDSNEWMETADATA" 에러

데이터원본테이블을 동적으로 바라보도록 패키지를 구성할 경우
(테이블명 변수 , expression 사용 등)

동적으로 변하는 데이터원본의 메타정보가 일치하지 않을경우   아래와같은 에러메세지를 띄우는데

failed validation and returned validation status "VS_NEEDSNEWMETADATA"

데이터원본의 데이터 타입, 컬럼순서 등을 체크한다.

mdf , ldf 파일 위치 변경

1. 사용자 DB

-- services restart

use DBNAME

-- 파일위치확인

exec sp_helpfile      

-- detach db

exec sp_detach_db 'DBNAME'

-- 변경하고자하는위치로mdf / ldf 파일이동

-- 변경한위치로경로변경 후 attach db

exec sp_attach_db @dbname ='DBNAME'

,       @filename1 = 'D:\MSSQL\DBNAME\DBNAME.mdf'

,       @filename2 = 'D:\MSSQL\DBNAME\DBNAME2.mdf'

,       @filename3 = 'D:\MSSQL\DBNAME\DBNAME3.mdf'

,       @filename4 = 'D:\MSSQL\DBNAME\DBNAME4.mdf'

,       @filename5 = 'D:\MSSQL\DBNAME\DBNAME_log.ldf'

...

2. TEMPDB

TEMPDB는 ALTER DATABASE명령으로 변경가능하다.

ALTER명령 후 서비스를 재시작 해야 해당위치에 새로운 MDF,LDF파일이 생성된다. 

--- tempdb 위치변경

use tempdb

exec sp_helpfile

USE master

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\MSSQL\DBNAME\tempdb.mdf' )

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'D:\MSSQL\DBNAME\templog.ldf' )

-- services restart

use tempdb

exec sp_helpfile

3. MASTERDB

use master

-- 파일위치 확인

exec sp_helpfile

SQL Server Configuration Manager 실행

속성 창에서 Startup Parameters 의 masterdb mdf,ldf 파일위치 수정

서비스 중지 후 mdf/ldf파일 위치 이동 후 서비스 재시작.

메타데이터 관리자 오류 / Errors in the metadata manager.

>> 에러메세지 

메타데이터 관리자에서 오류가 발생했습니다. 트랜잭션의 작업으로 인해 ID가 ‘큐브ID’이고 이름이 ‘큐브명’인 CUBE이(가) 무효화되었습니다.

Errors in the metadata manager. The cube with the ID of ‘CUBE_ID', Name of 'CUBE_NAME' was invalidated by operations in the transaction. 

이미 Processing이 완료된 차원의 특성관계(Attribute Relationships)를 수정하고 저장, 또는 처리시 위와같은 에러메세지를 띄우고 저장 및 처리를 하지 못한다.

이러한 경우 해당 차원파일을 삭제(또는 이름변경) 후 다시 저장하면 된다.

C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\DBName.0.db\

위의 위치에서 차원명과 동일한 xml 파일 , 폴더를 삭제또는 이름변경하고 차원을 저장 & 처리한다.

(보통 xml파일은  차원명.0.dim.xml  폴더명은 차원명.0.dim 와같은 형태임)

Development Edition에서 되었으나, Server Standard Edition에서는 가능하지 않았다.

SQL Server에서 특정 컬럼 Collation 변경

SQL Server 의 기본 Collation은 Korean_Wansung_CI_AS 로 구성되어 있다.

Korean / 한글

Wansung / 완성형

CI / Case Insensitive (대소문자 구분하지 않음)

AS / Accent Insensitive (악센트 구분하지 않음)

Korean_Wansung_CI_AS 구성에서는 Primary Key인 컬럼에 NAME , name 이

두 데이터를  적재할 경우 PK중복 오류를 낸다.

오라클에서 데이터를 이관할때 대소문자 구분때문에 PK중복 오류를 많이 내기도 하는데

특정 컬럼만 대소문자 구분을 하도록 할 수 있다.

ALTER TABLE [TABLE_NAME] ALTER COLUMN [COLUMN_NAME] VARCHAR(16) COLLATE KOREAN_WANSUNG_CS_AS