SQLLeader http://www.sqlleader.com/image/mainLogo.jpg http://www.sqlleader.com SQLLeader.com :: MSSQL Community Site ko Copyright 2006-2007 ADConsulting All Right Reserved. 2010-07-02T04:30:00.000 <![CDATA[ SQL Server 2000 DTS 패키지 예약 작업에서의 패키지 이름 찾는 방법]]> 한대성 http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=3968 SQL Server 2000 DTS 패키지 예약 작업에서의 패키지 이름 찾는 방법

 

Microsoft Premier Field Engineer

한대성

 

 

SQL Server 2000에서 DTS 패키지를 예약 설정하면 다음과 같이 dtsrun.exe 뒤에 패키지 이름이 암호화 되어 추가된다.

 

 

 



 

패키지를 마이그레이션 하거나 옮겨야 할 때 이 작업에 연결된 이름을 찾는 방법

1) SQL Agent 내에 정의된 이름을 복사한다.

2) 명령창(cmd)에 붙인 후, 마지막에 /!X /!C를 추가해서 실행한다.
  /!X는 실제로 실행하지 말라는 설정이며, /!C는 실행 문을 클립보드로 복사하라는 옵션이다.


3) 메모장을 열고 붙여넣기(CTRL+V)를 하면 다음과 같이 패키지 명이 나타난다.

 

]]> SQL Server 2005 SSIS 2010-07-02T04:30:00.000 <![CDATA[ SSAS Cube Partition을 쿼리로 처리하는 방법]]> 한대성 http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSAS&intSeq=3967

SSAS Cube Partition을 쿼리 또는 프로시저 등에서 처리하는 방법

Microsoft Premier Field Engineer

한대성 


저장 프로시저 또는 쿼리를 통해 Analysis Services의 큐브를 처리하는 방법은 다음과 같은 것들이 있다.

1.     SSIS 패키지를 만들고, xp_cmdshell ‘dtexec.exe ~~’를 이용해서 처리하는 방법

2.     CLR을 만들어 호출하는 방법

3.     SSAS 연결된 서버를 만들고 Cube Processing을 하는 XMLA 명령을 직접 실행하는 방법

 

이 중, 첫 번째 방법은

1)     xp_cmdshell을 활성화 시켜야 하는 부담이 있으며

2)     cmd.exe와 dtexec.exe 프로세스가 실행되어야 하며,

3)     파일 또는 msdb에 저장되어 있는 SSIS 패키지를 로드해야 하는 부하가 있다.

 

두 번째 방법은 CLR을 만들고 특정 DB에 등록해서 사용하면 되지만, 관련된 라이브러리들을 로드해야 한다. 또한 (확실하지는 않지만, 여러 환경에서 테스트 해 본 결과) SQL 2008 R2에서는 C:\Program Files\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll 을 등록할 때 다음과 같은 에러가 발생하면서 Assembly로 등록이 되지 않는 문제가 있다.

CREATE ASSEMBLY [AdomdClient]

AUTHORIZATION [dbo]

FROM 'C:\Program Files\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll'

WITH PERMISSION_SET = UNSAFE;

GO

/*

경고: 등록 중인 SQL Server 클라이언트 어셈블리 'microsoft.analysisservices.adomdclient, version=10.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.'은(는) SQL Server가 호스팅된 환경에서 완전히 테스트되지 않았습니다.

메시지 10301, 수준 16, 상태 1, 줄 1

어셈블리 'Microsoft.AnalysisServices.AdomdClient'이(가) 현재 데이터베이스에 없는 어셈블리 'microsoft.sharepoint, version=14.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.'을(를) 참조합니다. SQL Server가 참조하는 어셈블리와 같은 위치에서 참조된 어셈블리를 찾아 자동으로 로드하려고 시도했지만 해당 작업이 실패했습니다(원인: 2(지정된 파일을 찾을 수 없습니다.)). 참조된  어셈블리를 현재 데이터베이스에 로드하고 요청을 다시 시도하십시오.

*/

 

마지막 방법은 SQL Server에서 Analysis Services에 연결된 서버를 만들고 직접 XML/A 명령을 실행하는 것이다.

1)     SSAS에 대한 연결된 서버를 생성한다.

 

2)     서버 옵션에서 RPC 및 RPC 내보내기 옵션을 True로 설정한다.

 

3)     연결이 제대로 되었으면 다음과 같이 큐브가 브라우징 된다.

 

4)     다음과 같은 XML/A 명령을 실행하면 처리가 된다.

declare @execStr as nvarchar(max)

set @execStr = N'

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Parallel>

    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">

      <Object>

        <DatabaseID>Adventure Works DW</DatabaseID>

        <CubeID>Adventure Works DW</CubeID>

        <MeasureGroupID>Fact Currency Rate</MeasureGroupID>

        <PartitionID>Currency_Rates</PartitionID>

      </Object>

      <Type>ProcessFull</Type>

      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

    </Process>

  </Parallel>

</Batch>'

exec (@execStr) AT KRDSHANAS

 

 

-끝-

]]> SQL Server 2005 SSAS 2010-07-02T04:22:00.000 <![CDATA[ 데이터 원본에 저장 프로시저 사용하기]]> 한대성 http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=3647

데이터 원본에 저장 프로시저 사용하기

 

Microsoft Premier Field Engineer

한대성

 

모처럼 오랜만에 글을 올리네요. 우연찮게 좋은 방법을 알게 되어 공유하고자 올립니다.

 

SSIS에서 데이터 흐름 작업을 사용할 때 여러 제약 사항들 때문에 사용하기가 까다로운 경우가 있었을 것입니다. 다음과 같은 형태의 저장 프로시저를 예로 들어보겠습니다.

CREATE PROC TestProc

AS

        CREATE TABLE #RESULT

        (

               SEQ INT,

               COL1 INT,

               COL2 INT

        )

 

        INSERT #RESULT VALUES(1,2,3)

        INSERT #RESULT VALUES(4,5,6)

 

        SELECT * FROM #RESULT

GO

 

EXEC dbo.TestProc

/*

SEQ         COL1        COL2

----------- ----------- -----------

1           2           3

4           5           6

*/


지극히(?) 정상적인 프로시저처럼 보입니다. 이러한 형태로 프로시저를 만들어서 SSIS의 데이터 원본에서 막 쓸려고 하면,



이와 같은 에러 메시지가 나타나면서 열 정보를 설정할 수가 없습니다. 어떻게든 열 정보가 나와야 매핑을 설정할 수 있을 것인데 열 정보가 나타나지 않으니 더 이상 쓸 수가 없습니다.

 

1. SET FMTONLY OFF 구문을 추가

우선, 다음과 같이 저장 프로시저에 SET FMTONLY OFF 구문을 추가하고 실행해 봅시다.

ALTER PROC TestProc

AS

 

SET FMTONLY OFF

 

        CREATE TABLE #RESULT

        (

               SEQ INT,

               COL1 INT,

               COL2 INT

        )

 

        INSERT #RESULT VALUES(1,2,3)

        INSERT #RESULT VALUES(4,5,6)

 

        SELECT * FROM #RESULT

GO



열 정보가 잘 나타납니다. ~!

이렇게 처리되는 이유를 간략히 설명하자면 다음과 같습니다.

A.     SSIS에서 열 정보를 읽어올 때에는 자동으로 SET FMTONLY ON 옵션을 켠 상태에서 저장 프로시저의 열 정보를 읽어오게 됩니다. SET FMTONLY ON 옵션은 “클라이언트의 메타 정보만 반환합니다. 쿼리를 실제로 실행하지 않고 응답 형식을 테스트하는 데 사용할 수 있습니다.”로 도움말에 설명되어 있습니다.

B.     문제는 SET FMTONLY ON 옵션으로는 임시 테이블이 생성되지 않는다는 점입니다. SET FMTONLY ON 옵션을 켠 상태에서 프로시저를 실행하니깐 임시 테이블이 안 만들어지고, 임시테이블이 없으니깐 열 정보를 몰라서 열 매핑이 실패하는 것입니다.

C.     프로시저 내에 강제로 SET FMTONLY OFF 명령을 넣어버렸습니다. 이렇게 하면 SSIS에서 열 정보를 읽어갈 때 SET FMTONLY ON 명령을 켜서 들어왔더라도 OFF 시켜서 임시 테이블을 만들고 이에 대한 정보를 읽어갈 수 있게 됩니다.

복잡하면 통과.. 하지만, 여기서 끝은 아닙니다. 이제 패키지를 대충 구성해서 실행시켜 봅니다.





[OLE DB Source [1]] 오류: A rowset based on the SQL command was not returned by the OLE DB provider.

이해하기 쉽지 않은 에러 메시지가 출력됩니다. 미리보기 해도 잘 나오고 열 정보도 잘 나오는데 뭐가 문제란 말인가.

 

2. SET NOCOUNT ON 구문을 추가

이번에도, 다음과 같이 저장 프로시저에 SET NOCOUNT ON 구문을 추가하고 실행해 봅시다.

ALTER PROC TestProc

AS

 

SET FMTONLY OFF

SET NOCOUNT ON

 

        CREATE TABLE #RESULT

        (

               SEQ INT,

               COL1 INT,

               COL2 INT

        )

 

        INSERT #RESULT VALUES(1,2,3)

        INSERT #RESULT VALUES(4,5,6)

 

        SELECT * FROM #RESULT

GO

 


잘~~수행됩니다.

SET NOCOUNT ON은 대충 어떤 기능을 하는지 아실 것입니다.

A.     SET NOCOUNT ON 옵션을 넣지 않은 상태에서 저장 프로시저를 실행 시키면 다음과 같이 처리 단계에서 실행된 결과를 반환합니다.

B.     SET NOCOUNT ON 옵션을 넣으면 다음과 같이 결과만 출력됩니다.

C.     SSIS의 데이터 흐름에서는 “몇 개 행이 처리되었습니다.”라는 메시지도 결과값으로 인식해서 처리하게 됩니다. 이미 열 정보를 이용해서 세 개의 열이 출력될 것이다라고 설정해 놨는데 이상한(?) 문자열(1 rows(s) affected)이 먼저 나타나니깐 에러를 발생시킨 것입니다.

D.    SET NOCOUNT ON 옵션으로 저런 정보 메시지를 나타내지 않도록 한 것입니다. 자연히 미리 정한 열 정보 모양대로 출력되기 때문에 정상적으로 패키지가 실행됩니다.

 

 

SSIS Beta 버전부터 해왔지만 조금 전까지도 "SSIS에서는 이건 안 되는 기능이야.”라고 단정하고 있었던 내용입니다. 조금 더 깊이 살펴봤다면 좀 더 일찍 알 수도 있었을 수도 있었는데. 프로젝트 할 때 이것이 안되서 다른 방식으로 고생 고생해서 처리했던 생각이 나네요~~^^

 

]]> SQL Server 2005 SSIS 2009-12-06T08:18:00.000 <![CDATA[ SQL Server 2008의 새로운 기능 - Parameter Embedding Optimization]]> 송혁 http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005TSQL&intSeq=3627 SQL Server 2008에 재미있는 옵티마이저 기능이 추가 되었습니다.

하지만 버그로 인해서 SP1 또는 RTM CU4부터는 이 기능을 사용할 수 없었지만,

얼마 전에 나온 SP1 CU5에서 버그를 수정하여 이 기능을 다시 제공하고 있습니다.

 

Parameter Embedding Optimization이라고 불리며, OPTION(RECOMPILE)을 사용하는 쿼리의 경우 실행계획을 컴파일 할 때 실제 매개변수의 값을 가지고 컴파일 하는 기능입니다. 그렇다면 분명 기존보다 효율적인 플랜을 만들어줄 것 입니다. 뭐 당연하다고 생각할 수 도 있을 것 같습니다. 플랜 캐시를 재사용을 포기하며 recompile옵션도 추가했는데~ 플랜까지 이상하다면 좀 이상하겠죠!

 

이 기능이 도움을 줄 수 있는 경우는 쿼리가 변수에 따라서 플랜이 변경되어야 하는 경우 입니다.
입력되는 변수에 따라 WHERE 조건이 변경되는 경우 SQL Server 2008 이전 버전을 사용했을 때 적절한 플랜으로 처리 되기 위해서는 모든 경우에 따라 분기 문 또는 SP를 분리하여 사용하거나, 쿼리 문자열을 조합하는 형태의 동적 쿼리를 사용할 수 밖에 없었습니다. 동적 쿼리는 플랜 재사용도 거의 안되면서 플랜캐시에 상주할 수 있기에 플랜 캐시가 늘어나는 문제와 보안적인 문제를 가질 수 있습니다. 경우의 수만큼 분기 문을 사용한 쿼리는 유지 보수를 하기가 참~ 어려웠습니다.

 

그럼 이 기능으로 플랜이 어떻게 변경되는지 확인해 보겠습니다.

SQL Server 2008 SP1 + CU5와 SQL Server 2008 SP1 버전으로 비교해 보았습니다.

 

[테스트 1] 입력되는 변수 값에 따라 WHERE 조건이 변경되는 경우

SP1과 SP1 + CU5환경에서 실행하면 아래와 같은 실행계획을 확인 할 수 있습니다.

SP1의 경우는 TABLE SCAN으로 풀렸으며, SP1+CU5의 경우는 Index Seek + RID Lookup을 사용하였습니다.

아래 플랜에서 노란색으로 표시된 부분을 보면 SP1의 경우 조건 절에서 변수로 비교하며, CU5의 경우 상수로 비교하고 있습니다.

그래서 플랜이 서로 다른 모습을 보여주고 있으며, CU5가 파라미터의 값을 가지고 플랜을 생성하여 보다 효율적인 쿼리 플랜을 생성했습니다.

 

SQL Server 2008 SP1

Rows

Executes

StmtText

1

1

SELECT * FROM tbl90   WHERE    (col1 = @a AND @a IS NOT NULL)  OR (col2 = @a2 AND @a2 IS NOT NULL)  OR (col3 = @a3 AND @a3 IS NOT NULL) 

OR (col4 = @a4 AND @a4 IS NOT NULL)   OR (col5 = @a5 AND @a5 IS NOT NULL)  OR (col6 = @a6 AND @a6 IS NOT NULL)  OPTION(RECOMPILE)

1

1

|--Table Scan(OBJECT:([test].[dbo].[tbl90]), WHERE:([test].[dbo].[tbl90].[col1]=[@a] AND [@a] IS NOT NULL OR [test].[dbo].[tbl90].[col2]=[@a2] AND [@a2] IS NOT NULL OR [test].[dbo].[tbl90].[col3]=[@a3] AND [@a3] IS NOT NULL OR [test].[dbo].[tbl90].[col4]=[@a4] AND [@a4] IS NOT NULL

OR [test].[dbo].[tbl90].[col5]=[@a5] AND [@a5] IS NOT NULL OR [test].[dbo].[tbl90].[col6]=[@a6] AND [@a6] IS NOT NULL))

 

SQL Server 2008 SP1 + CU5

Rows

Executes

StmtText

1

1

SELECT * FROM tbl90   WHERE    (col1 = @a AND @a IS NOT NULL)  OR (col2 = @a2 AND @a2 IS NOT NULL)  OR (col3 = @a3 AND @a3 IS NOT NULL) 

OR (col4 = @a4 AND @a4 IS NOT NULL)  OR (col5 = @a5 AND @a5 IS NOT NULL)  OR (col6 = @a6 AND @a6 IS NOT NULL)  OPTION(RECOMPILE)

1

1

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))

1

1

       |--Index Seek(OBJECT:([tempdb].[dbo].[tbl90].[ix_tbl903]), SEEK:([tempdb].[dbo].[tbl90].[col3]=(1)) ORDERED FORWARD)

1

1

       |--RID Lookup(OBJECT:([tempdb].[dbo].[tbl90]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

 

DROP TABLE tbl90

CREATE TABLE tbl90 (col1 INT NOT NULL, col2 INT ,col3 INT,col4 INT,col5 INT,col6 INT)

 

INSERT INTO tbl90

SELECT TOP 100000

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1))

FROM sys.sysindexes a,sys.sysindexes a1,sys.sysindexes a2,sys.sysindexes a3

 

CREATE INDEX ix_tbl90 ON tbl90 (col1)

CREATE INDEX ix_tbl902 ON tbl90 (col2)

CREATE INDEX ix_tbl903 ON tbl90 (col3)

CREATE INDEX ix_tbl904 ON tbl90 (col4)

CREATE INDEX ix_tbl905 ON tbl90 (col5)

-- CREATE INDEX ix_tbl906 ON tbl90 (col6)

GO

CREATE PROC UP_90

@a INT = NULL

,@a2 INT = NULL

,@a3 INT = NULL

,@a4 INT = NULL

,@a5 INT = NULL

,@a6 INT = NULL

AS

SELECT * FROM tbl90

WHERE

                  (col1 = @a AND @a IS NOT NULL)

OR (col2 = @a2 AND @a2 IS NOT NULL)

OR (col3 = @a3 AND @a3 IS NOT NULL)

OR (col4 = @a4 AND @a4 IS NOT NULL)                                                                                                                                                                                            

OR (col5 = @a5 AND @a5 IS NOT NULL)

OR (col6 = @a6 AND @a6 IS NOT NULL)

OPTION(RECOMPILE)

GO

 

SET STATISTICS PROFILE ON

exec up_90 @a3 = 3

 

[테스트 2] 입력되는 변수 값에 따라 조회하는 테이블이 변경되는 경우

UNION ALL을 통해서 변수 값에 조회할 테이블을 선택할 수 있는 경우 입니다.

많이 사용되는 쿼리 중 하나인데요, SP1 + CU5버전에서 RECOMPILE옵션을
추가하면 입력된 변수 값에 따라 실질적으로 읽어야 할 테이블에 대해서만 조회를 하는 것을 볼 수 있습니다.

 

SQL Server 2008 SP1 + CU5

Rows

Executes

StmtText

1

1

SELECT * FROM tbl90 WITH(NOLOCK) WHERE @a IS NOT NULL AND @a = col1  UNION ALL 

SELECT * FROM tbl91 WITH(NOLOCK) WHERE @a1 IS NOT NULL AND @a1 = col1  OPTION(RECOMPILE)

0

0

|--Compute Scalar(DEFINE:([Union1008]=[tempdb].[dbo].[tbl90].[col1], [Union1009]=[tempdb].[dbo].[tbl90].[col2], [Union1010]=[tempdb].[dbo].[tbl90].[col3],

[Union1011]=[tempdb].[dbo].[tbl90].[col4], [Union1012]=[tempdb].[dbo].[tbl90].[col5], [Union1013]=[tempdb].[dbo].[tbl90].[col6]))

1

1

       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))

1

1

            |--Index Seek(OBJECT:([tempdb].[dbo].[tbl90].[ix_tbl90]), SEEK:([tempdb].[dbo].[tbl90].[col1]=(1)) ORDERED FORWARD)

1

1

            |--RID Lookup(OBJECT:([tempdb].[dbo].[tbl90]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

 

SQL Server 2008 SP1

Rows

Executes

StmtText

1

1

SELECT * FROM tbl90 WITH(NOLOCK) WHERE @a IS NOT NULL AND @a = col1  UNION ALL 

SELECT * FROM tbl91 WITH(NOLOCK) WHERE @a1 IS NOT NULL AND @a1 = col1  OPTION(RECOMPILE)

1

1

  |--Concatenation

1

1

       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))

1

1

       |    |--Filter(WHERE:(STARTUP EXPR([@a] IS NOT NULL)))

1

1

       |    |    |--Index Seek(OBJECT:([TEST2].[dbo].[tbl90].[ix_tbl90]), SEEK:([TEST2].[dbo].[tbl90].[col1]=[@a]) ORDERED FORWARD)

1

1

       |    |--RID Lookup(OBJECT:([TEST2].[dbo].[tbl90]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

0

1

       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004]))

0

1

            |--Filter(WHERE:(STARTUP EXPR([@a1] IS NOT NULL)))

0

0

            |    |--Index Seek(OBJECT:([TEST2].[dbo].[tbl91].[ix_tbl91]), SEEK:([TEST2].[dbo].[tbl91].[col1]=[@a1]) ORDERED FORWARD)

0

0

            |--RID Lookup(OBJECT:([TEST2].[dbo].[tbl91]), SEEK:([Bmk1004]=[Bmk1004]) LOOKUP ORDERED FORWARD)

 

DROP TABLE tbl91

CREATE TABLE tbl91 (col1 INT NOT NULL, col2 INT ,col3 INT,col4 INT,col5 INT,col6 INT)

 

INSERT INTO tbl91

SELECT TOP 100000

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1)),

ROW_NUMBER() OVER(ORDER BY(SELECT 1))

FROM sys.sysindexes a,sys.sysindexes a1,sys.sysindexes a2,sys.sysindexes a3

 

CREATE INDEX ix_tbl91 ON tbl91 (col1)

CREATE INDEX ix_tbl912 ON tbl91 (col2)

CREATE INDEX ix_tbl913 ON tbl91 (col3)

CREATE INDEX ix_tbl914 ON tbl91 (col4)

CREATE INDEX ix_tbl915 ON tbl91 (col5)

CREATE INDEX ix_tbl916 ON tbl91 (col6)

GO

CREATE PROC UP_93

@a INT = NULL

,@a1  INT = NULL

AS

SELECT * FROM tbl90 WITH(NOLOCK) WHERE @a IS NOT NULL AND @a = col1

UNION ALL

SELECT * FROM tbl91 WITH(NOLOCK) WHERE @a1 IS NOT NULL AND @a1 = col1

OPTION(RECOMPILE)

GO

EXEC UP_93 @a = 1

 

 

추가적인 정보는 아래 링크를 참조하세요.

http://blogs.msdn.com/grahamk/archive/2009/11/18/changed-behaviour-of-option-recompile-syntax-in-sql-server-2008-sp1-cumulative-update-5.aspx

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=244298

http://support.microsoft.com/kb/976603/

송 혁, SQL Server MVP
sqler.com // sqlleader.com
hyoksong.tistory.com

 

]]> SQL Server 2005 TSQL 2009-11-25T01:17:00.000 <![CDATA[ Excel2003 으로 Analysis Services 9.0(2005) 이상의 큐브 접속시 오류 해결 방법]]> 이재웅 http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSAS&intSeq=3472 Excel 2003 으로 Analysis Services 2005 의 큐브에 접속할 경우
'서버를 찾을 수 없다' 혹은 '서버의 자원 사용률이 높다(?)' 라는 오류가 발생하며 접속할 수가 없습니다.
(오류 스크린샷을 못 찍었네요.)
이에 해결 방법을 알려드립니다.

오류의 원인은 Analysis Services 9.0 OLE DB Provider 가 없어서 오류가 발생합니다.
Excel 2003 에는 Microsoft SQL Server OLAP 과 Microsoft SQL Server OLAP 8.0 provider 를 제공합니다.
SQL Server 2005 이상부터는 Analysis Services 라는 이름으로 공급자를 제공하고 있구요.
따라서 해당 PC에 MS Core XML Services 6.0 과 Analysis Services 9.0 OLE DB Provider 를 설치하시면 바로 해결이 됩니다.

 

관련링크 : http://support.microsoft.com/kb/940167

MSXML(Microsoft Core XML Services 6.0 다운로드 : http://www.microsoft.com/downloads/details.aspx?FamilyId=d21c292c-368b-4ce1-9dab-3e9827b70604&displaylang=ko

Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider 다운로드 : http://download.microsoft.com/download/9/d/a/9da2c9c3-3638-4688-86db-b7eb390e299a/SQLServer2005_ASOLEDB9.msi

 

]]> SQL Server 2005 SSAS 2009-08-31T09:52:00.000 <![CDATA[ SSRS 2008에서 Analysis Services 2000 큐브 이용하기]]> 한대성 http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSRS&intSeq=3459 SSRS 2008에서 Analysis Services 2000 큐브 데이터 이용하기

Microsoft Premier Field Engineer

한대성




 Question
2000 Analysis Server에서 큐브를 만든걸 2008 Report Service에서 데이터 원본 연결후 리포트를 작성하려고 하는데요.
쿼리디자인 기능이 안되는것 같아서요.속도도 무지 느린것 같구요..
2000에서 생성된 큐브는 2008에서 연결하여 사용할수 없나요?




1. 다음과 같이 연결 유형을 Microsoft SQL Server Analysis Services가 아닌 OLE DB로 선택합니다.
Microsoft SQL Server Analysis Services는 SSAS 2005, SSAS 2008용 입니다.




2. Provider에서 아래와 같이 Microsoft OLE DB Provider for Analysis Services 9.0을 선택합니다.



3. 아래와 같이 서버 명을 지정하고 테스트 연결이 되는지 확인합니다. 정상적으로 연결될 경우, 아래와 같이 Initial Catalog가 나옵니다.
만약 정상적으로 나오지 않을 경우, 접근 권한이 있는지 확인해 보세요.. 간단히 시작--> 실행 메뉴에서 \\서버명 으로 네트워크 연결이 되는지, 그리고 사용자 및 암호가 설정되어 있는지 확인



4. 명확히 하기 위해 Advanced 버튼을 누른 후, 아래와 같이 Provider가 MSOLAP.3으로 나타나는지 확인




보고서에서 쿼리는 * (All Columns)을 선택하면 안되고, 필요한 열을 선택하거나 정확한 MDX문을 지정해야 합니다.



완성된 보고서 모습







]]> 2009-08-27T14:51:00.000 <![CDATA[ 패키지 실행 시 매개변수 동적으로 주기]]> 한대성 http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=3446

패키지 실행 시 매개변수 동적으로 주기

Microsoft Premier Field Engineer

한대성

 

 

 Question
job agent에서 패키지(SSIS)를 호출하여 매일 실행시키는 작업을 추가하였습니다.
근데 SSIS에서 매개변수를 받는것이 있거든요.
그 매개변수는 매일 실행하는 월.일(20090817, 200908) 입니다.
이런 매개변수값을 agent등록시 어떻게 지정하여 넣어주어야 하나요?
값 넣는 것은
\package.Variables[User::YYYYYMMDD].Value : 임의의값
이란 것을 알고 있지만 임의의 값이 아닌 동적인 값을 넘기고 싶습니다.


1. SSIS 패키지 내에서 날짜에 대한 값 지정하기

일반적인 방법으로, 입력되는 날짜가 없는 경우에는 자동으로 오늘 날짜 및 이번 달을 지정하도록 만드는 것입니다.

a.     스크립트 작업을 패키지의 가장 처음에 실행될 수 있도록 추가합니다.

b.     스크립트 작업의 ReadWriteVariables 에 User::YYYYMMDD,User:YYYYMM 변수를 입력합니다.

 

c.     다음과 같은 스크립트를 입력합니다. (Main() 안에)

public void Main()

        {

 

            if (Dts.Variables["YYYYMMDD"].Value.ToString() == "")

            {

                Dts.Variables["YYYYMMDD"].Value = DateTime.Today.ToString("yyyyMMdd");

                Dts.Variables["YYYYMM"].Value = DateTime.Today.ToString("yyyyMM");

            }

            Dts.TaskResult = (int)ScriptResults.Success;

        }


 

d.     간단히 각 변수의 값을 출력하는 작업을 하나 추가하고 패키지를 테스트 하면 다음과 같이 오늘 날짜 및 이번 달이 변수 값으로 지정됩니다.

 

e.      SQL Agent에서는 오늘 날짜로 설정할 경우에는 \package.Variables[User::YYYYMMDD].Value의 속성에 빈 값을 넣으면 됩니다.

 

 

2. Shell Script를 이용하여 날짜 값 지정

만약 패키지를 수정할 수 없는 경우에는 다음과 같은 방식을 이용합니다.

a.     SQL Agent에서 실행할 패키지를 지정하고, YYYYMMDD, YYYYMM 변수에 임의의 값을 지정한 후, 명령줄 탭에 나오는 명령줄 부분을 복사합니다.

         

b.     메모장에 위의 명령을 붙여 넣은 다음, 아래와 같이 날짜 정보를 얻어오는 Shell Script를 추가한 후 확장자를 cmd로 저장합니다.

echo off

 

REM Define the TODAY variable

REM Define the THISMONTH variable

 

REM 운영체제가 한글 버전인 경우

for /F "tokens=1-3 delims=/ " %%a in ('date /t') do SET TODAY=%%a%%b%%c

for /F "tokens=1-3 delims=/ " %%a in ('date /t') do SET THISMONTH=%%a%%b

 

REM 운영체제가 영문 버전인 경우 아래의 REM 제거

REM for /F "tokens=1-3 delims=/ " %%a in ('date /t') do SET TODAY=%%c%%a%%b

REM for /F "tokens=1-3 delims=/ " %%a in ('date /t') do SET THISMONTH=%%c%%a%

 

"D:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /FILE "D:\Temp\SSISProject\SSISProject\Package.dtsx" /CHECKPOINTING OFF /SET "\package.Variables[User::YYYYMMDD].Value";%TODAY% /SET "\package.Variables[User::YYYYMM].Value";%THISMONTH% /REPORTING E


주의할 것은 날짜를 가져오는 부분이 OS의 국가별 언어 설정에서 지정한 날짜 방식에 따라 약간 다릅니다. 만약 운영체제가 영문일 경우, 영문 버전에 맞게 수정하세요.

그리고, 가급적 패키지를 실행시키는 dtexec.exe는 전체 경로를 명확히 지정해 주세요. SQL 2005가 같이 설치되어 있을 경우, 단순히 dtexec.exe로 지정하면 SQL 2005 버전이 실행될 수도 있습니다.

 

c. 패키지를 테이블에 저장하도록 약간 수정한 후 테스트 해 보면 다음과 같이 정확히 날짜가 입력됩니다.

 



 


 

d.     다시 SQL Agent 작업으로 돌아가서, 작업 유형을 운영 체제(CmdExec)로 변경한 후, 아래와 같이 명령 부분에 위에서 생성한 파일을 지정합니다.


          

-끝-

]]> SQL Server 2005 SSIS 2009-08-22T00:47:00.000 <![CDATA[ 마스터 패키지로 여러 패키지들의 연결 정보 관리하기]]> 한대성 http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=3432 마스터 패키지로 여러 패키지들의 연결 정보 관리하기

 

Microsoft Premier Field Engineer

한대성

 

다음과 같은 형태의 단순한 자식 패키지를 하나 만듭니다.

a.     Servername 이라는 String 형 변수를 추가합니다.

b.     ChildDBConn이라는 OLE DB 연결을 설정합니다. 본 예제에서는localhost\ss2005 서버를 가리키는 연결을 만듭니다.

c.     이 외에 여러 형태의 연결을 만듭니다. (옵션 사항임.)

d.     SQL 실행 작업을 추가한 후, 다음과 같이 설정합니다.



 

e.     스크립트 작업을 추가한 후, SQL 실행 작업과 연결하고, 속성의 ReadOnlyVariables 부분에 Servername을 입력한 후, 스크립트 편집기를 열어 다음과 같은 간단한 스크립트를 입력합니다.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

 

Public Class ScriptMain

 

            Public Sub Main()

                       

                        MsgBox(Dts.Variables("Servername").Value.ToString)

 

                        Dts.TaskResult = Dts.Results.Success

            End Sub

 

End Class

 

f.      완성된 자식 패키지 모습입니다.

 

g.     패키지를 실행하면 다음과 같이 기본으로 설정된 OLE DB의 서버 명이 출력됩니다.

 

 

다음과 같은 상황을 생각해 봅시다.

1.     위와 같은 형태의 패키지가 아주 많다.

2.     대부분 OLE DB 연결 명은 비슷하거나 동일하다.

3.     패키지를 실행 시킬 때, 다른 DB 서버 연결을 지정하고 싶다. 즉, 위의 예에서 ChildDBConn이라는 연결이 localhost\SS2005로 지정되어 있지만, localhost DB 서버에서 실행되도록 하고 싶다.

4.     SQL Agent나 패키지를 실행시킬 때 Connection 탭에서 지정해주면 되겠지만, 패키지들이 많아서 번거롭다.

5.     또한, DB 서버를 일괄적으로 바꿀 수 있다. 그러면 다시 모든 패키지의 연결 정보를 (Agent 작업이든, 패키지 실행 스크립트이든, SSIS 패키지 자체이든) 바꿔줘야 한다.

6.     자식 패키지들이 많아서 일일이 어떤 기능을 추가하는 것도 어렵다.

 

이런 경우가 가끔씩 있습니다. 제 개인적인 경험도 수백 개의 패키지의 연결 정보를 관리해야 할 때가 있었습니다.

 

몇 가지 운영 방법들이 있겠지만, 다음과 같은 조건을 가지면서 할 수 있는 방법을 생각해 봤습니다.

1.     자식 패키지(=기존 패키지)를 수정하지 않았으면 좋겠다.

2.     연결 정보가 변경되더라도 최소한의 작업으로 이를 반영할 수 있었으면 좋겠다.

 

제가 생각해 본 방법은 일명 『마스터 패키지』라는 패키지 실행을 담당하는 부모 패키지를 이용하는 것입니다.

직접 AAA.dtsx라는 패키지를 실행하는 대신, Master.dtsx라는 마스터 패키지에 AAA.dtsx라는 패키지 이름을 던져주면(외부에서 변수 값을 지정하는 방식으로) 마스터 패키지에서는 연결 정보를 바꿔치기 한 후 이 패키지를 실행하는 방법입니다.

 

 

다음과 같은 형태로 마스터 패키지를 만들어 봅시다.

a.     PackageNameStr이라는 String형 변수를 추가하고, 기본값으로 자식 패키지의 경로를 입력합니다.

b.     ChildDBConn 이라는 String형 변수를 추가하고 다음과 같이 OLE DB의 연결 문자열을 입력합니다. 이 때의 문자열은 자식 패키지의 ChildDBConn 연결의 연결 문자열과는 다르게 지정합니다. 본 예에서는 localhost 서버를 가리키는 DB 연결 문자열을 설정합니다.



c.     스크립트 작업을 하나 추가한 후, 편집기의 ReadOnlyVariables 속성에 PackageNameStr,ChildDBConn을 입력합니다.

 

d.     스크립트 편집기를 연 후, 다음과 같은 스크립트를 입력합니다.

Imports System

Imports System.Data

Imports Microsoft.SqlServer.Dts.Runtime

 

Public Class ScriptMain

 

    Public Sub Main()

 

        Dim pkg As String

        pkg = Dts.Variables("PackageNameStr").Value.ToString

 

        Dim app As Application = New Application()

        Dim p As Package = app.LoadPackage(pkg, Nothing) ' 패키지 개체를 생성

 

        Dim con As ConnectionManager

 

        For Each con In p.Connections  ' 패키지 내의 연결들을 조사

 

            If con.AcquireConnection(Dts.Transaction).GetType.ToString = "System.__ComObject" Then ' OLEDB 연결일 경우

                If con.Name = "ChildDBConn" Then  ' 연결 이름이 ChildDBConn일 경우, 변수값으로 대체

                    con.ConnectionString = Dts.Variables("ChildDBConn").Value.ToString

                End If

            End If

 

        Next

 

        p.Execute() ' 패키지 실행

 

 

          Dim pkgError As DtsError

 

        If (p.Errors.Count > 0) Then ' 자식 패키지에서 에러가 발생한 경우, 이 정보를 전파

            p.Dispose()

            For Each pkgError In p.Errors

                Dts.Events.FireError(pkgError.ErrorCode, pkgError.SubComponent, pkgError.Description, pkgError.HelpFile, pkgError.HelpContext)

            Next

 

            Dts.TaskResult = Dts.Results.Failure

        Else

            p.Dispose()

            Dts.TaskResult = Dts.Results.Success

        End If

    End Sub

 

End Class

 

 

에러 전파 부분을 잠시 설명하겠습니다.

패키지 개체를 만들어 자식 패키지를 실행할 경우, 자식 패키지가 에러가 나더라도 이 정보가 전달이 안됩니다. 즉, 무조건 성공으로 보이지요. 따라서 이를 개선하고자 추가한 부분입니다.

자식 패키지를 실행한 후, Error Count가 0보다 크다면 에러 정보를 현재 패키지에 전달(FireError)하는 것입니다.

e.     패키지를 실행시켜 봅시다.

 

f.      SQL Agent에 패키지를 등록할 경우, 다음과 같이 설정합니다.






값 설정 탭에서

            \Package.Variables[사용자::PackageNameStr].Properties[Value] 라는 속성을 입력하고 값 부분에는

실행 시킬 패키지 명(경로 포함)을 입력함.
                  

 

 

본 예제에서는 단순히 마스터 패키지에 자식 패키지에서 사용할 연결 정보(ChildDBConn)를 변수에다가 저장하는 식으로 구현했지만, 연결 정보가 많거나 연결 정보들을 별도의 파일이나 테이블 등에 관리할 경우, Recordset을 이용하여 ADO 개체에 저장하고 스크립트 작업을 조금 변경해서 매핑 & 변경 되도록 설정하면 되겠지요.

(글로 적으려니까 무지하게 길어지고 무슨 말인지 잘 모르겠네요.@.@)

 

]]> SQL Server 2005 SSIS 2009-08-18T14:55:00.000 <![CDATA[ WMI 객체를 쿼리로 읽어오기]]> 이재웅 http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005CLR&intSeq=3429 DB서버를 관리하다 보면 성능카운터를 수집하거나 모니터링 하는 일이 많습니다.

성능 모니터를 사용해서 수집 및 모니터링을 할 수도 있고 써드파티툴을 사용할 수도 있습니다.

 



SQL 2005 부터는 DMV 등을 통해서 성능 카운터를 모니터링 할 수 있습니다.
  

SELECT  * FROM SYS.DM_OS_PERFORMANCE_COUNTERS

WHERE REPLACE(RIGHT([OBJECT_NAME], PATINDEX('%:%', REVERSE([OBJECT_NAME]))-1), ' ', '') = 'GeneralStatistics'


 




그러나 DMV 도 모든 WMI 카운터를 제공하지는 않습니다.

특히 하드디스크의 용량 같은 물리적인 부분이나 OS 단의 카운터 중 제공 안 하는 것들이 있습니다.

이 부분을 따로 WMI 툴로 만들어서 볼 수도 있습니다만 이걸 CLR로 등록해서 쿼리로 바로 보도록 하겠습니다.

WMI 개발에 관련된 자료는 쉽게 찾아볼 수가 있고 코드까지 만들어주는 툴도 있습니다.

예전에 한대성님이 ‘Scriptomatic2’ 도 소개해주셨습니다만 이번엔 ‘WMICodeCreator’ 이라는 툴로 해보겠습니다. (#파일첨부)

원하는 WMI클래스와 객체를 선택하면 바로 VB 또는 C# 코드를 만들어줍니다. Condition 설정도 가능합니다.

 



이렇게 만들어진 코드를 가지고 CLR로 등록해서 Function으로 만들어 직접 쿼리를 날려보겠습니다.

이 코드를 CLR로 등록하려면 사전에 한가지 설정을 해야 됩니다.

 

WMI 데이터를 읽기 위해선 System.management 네임스페이스의 클래스들을 사용합니다.

그래서 데이터베이스에 사전에 System.Management 어셈블리가 등록되어 있어야 됩니다.
 

USE TEST

GO

 

--CLR ENABLE

EXEC SP_CONFIGURE 'clr enabled', 1

RECONFIGURE WITH OVERRIDE

GO

 

--TRUSTWORTHY ON

ALTER DATABASE TEST SET TRUSTWORTHY ON

GO

 

--System.Management 등록

IF EXISTS(SELECT 1 FROM SYS.ASSEMBLIES WHERE NAME = 'System.Management')

BEGIN

             DROP ASSEMBLY [System.Management]

END

GO

CREATE ASSEMBLY [System.Management]

             AUTHORIZATION [dbo]

             FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'

             WITH PERMISSION_SET = UNSAFE

GO


 

System.Management 어셈블리가 등록되지 않은 상태에서 컴파일을 할 경우 아래와 같은 오류를 만나게 됩니다.

 


이 부분 외에 다른 설정은 보통 CLR 모듈을 등록하는 것과 동일합니다.

김종열님께서 다른 강좌에서 많이 보여주셨기 때문에 생략하도록 하겠습니다..


비쥬얼 스튜디오에서 C# SQL Server 프로젝트를 생성하신 후 데이터베이스 참조를 설정하시고

아래 소스를 붙여 넣으시기 바랍니다.

  

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Management;

using System.Net;

using System.Net.Sockets;

using System.Collections;

 

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="fillTable", TableDefinition="Name nvarchar(20), Value nvarchar(20)")]

    public static IEnumerable UDF_WMI(string srvname, string username, string password, string wmiClass, string wmiName, string wmiValue, string condition)

    {

      

        //서버 연결

        ManagementScope scope = ConnServer(srvname, username, password);

 

        //조회 쿼리(WQL) 생성

        if (condition.Length > 0)

        {

            condition = " WHERE " + condition;

        }

 

        try

        {   

          

            ObjectQuery query = new ObjectQuery("SELECT " + wmiName + ", " + wmiValue + " FROM " + wmiClass + condition);

 

            //개체 컬렉션 검색

            ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);

            ManagementObjectCollection oCollection = searcher.Get();

 

            //return 변수 선언

            string[] retArray = new string[oCollection.Count];

 

            //컬렉션 열거자

            ManagementObjectCollection.ManagementObjectEnumerator

                oEnumerator = oCollection.GetEnumerator();

            oEnumerator.MoveNext();

 

            //return 변수 입력

            for (int i = 0; i < oCollection.Count; i++)

            {

                ManagementObject o = (ManagementObject)oEnumerator.Current;

                retArray[i] = Convert.ToString(o[wmiName]) + "," + Convert.ToString(o[wmiValue]);

                oEnumerator.MoveNext();

            }

            return (retArray);

        }

        catch (ManagementException e)

        {

            Console.WriteLine("An error occurred while querying for WMI data: " + e.Message);

            return "-1";

        }

    }

 

    public static ManagementScope ConnServer(string srvname, string username, string password)

    {

        try

        {

            //로컬인지 확인

    &n