깡민쓰~IT Story

Image -> Text

CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),field))

varbinary -> Text

CONVERT(VARCHAR(MAX),convert(varbinary(4000),field))

XML -> Text

cast(field as xml) as Field_Name

이번엔 WMI(Windows Management Instrument) 를 이용해 SQL서버를 관리하는 방법에 대해 말씀드릴려고 합니다.

SQL를 관리를 위한 WMI Object 를 아래처럼 가져올 수 있습니다.

이 오브젝트는 클래스들의 인스턴스 집합이기 때문에 SQL를 위한 네임스페이스를 사용합니다.

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 –list | Where-Object {-not ($_.Name -like ‘__*’)}

[2005] 의 경우 -namespace root\Microsoft\SqlServer\ComputerManagement

많은 클래스들이 있지만 우선 SQL Service 클래스를 살펴보면

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService | Select-Object ServiceName, DisplayName, SQLServiceType, State, ProcessId | Format-Table -wrap

설치된 SQL 기본 인스턴스들을 확인할 수 있습니다.

여기서 Service States 가 나타내는 숫자는 아래와 같은 의미를 가집니다.

1 Stopped. The service is stopped.

2 Start Pending. The service is waiting to start.

3 Stop Pending. The service is waiting to stop.

4 Running. The service is running.

5 Continue Pending. The service is waiting to continue.

6 Pause Pending. The service is waiting to pause.

7 Paused. The service is paused.

그럼 여기서 사용할 수 있는 메소드들을 확인해 볼까요

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 –class SqlService | Get-Member -MemberType method

여기선 주로 서비스 계정을 설정 하거나 서비스를 시작 중지 시키는 메소드들 뿐이네요.

위의 메소드를 활용해서 MSSQL Server Instance 계정을 local system에서 도메인 계정으로 변경해 볼까요

$strUser = "DOMAIN\account"

$strPass= "비밀번호"

$wSqlservice = Get-WmiObject –namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService –filter "ServiceName='MSSQLSERVER'"

$wSqlservice.SetServiceAccount($strUser, $strPass)

$wSqlservice.StopService()

$wSqlservice.StartService()

그럼 이번엔 SQL Agent Service를 자동으로 실행되게 변경해 볼까요

$sqlservice = Get-WmiObject –namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService –filter "ServiceName='SQLSERVERAGENT'"

$sqlservice.SetStartMode(2)

#2 Service is started automatically

#3 Service is started manually

#4 Service is disabled

변경이 잘 되었네요 ^^

참고로 –computerName 이란 프로퍼티를 이용하면 원격지의 WMI Object를 가져올 수 있습니다.

Get-WmiObject .computerName 컴퓨터이름 -namespace

root\Microsoft\SqlServer\ComputerManagement10 -class SqlService -filter

"ServiceName=’MSSQL`$Instance’"

다음편엔 ServerNetworkProtocolProperty 클래스를 확인해 볼 예정입니다.

참고: SQL Server Administration with Windows PowerShell

원본: http://vstarmanv.tistory.com/entry/MSSQLWMI-for-SQL-Management1

$sqlConnection = New-Object system.data.sqlclient.sqlconnection "server=서버명또는IP;database=DatabaseName;user=계정;비밀번호;trusted_connection=true"
$sqlConnection.Open()
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "Select ResourceId, UserAtHost cnt FROM Resource"
$sqlReader = $sqlCommand.ExecuteReader()
while($sqlReader.Read())
{
$sqlReader["ResourceId"]
$sqlReader["UserAtHost"]
}
$sqlConnection.Close()

SELECT @@VERSION

SELECT
      SERVERPROPERTY('ProductVersion') AS ProductVersion,
      SERVERPROPERTY('ProductLevel') AS ProductLevel,
      SERVERPROPERTY('Edition') AS Edition,
      SERVERPROPERTY('ISClusterd') AS Clusterd;
  GO

  SP_READERRORLOG

  HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names

-- Select Into 사용 방법

1. Select * Into New_Table_Name From Org_Table_Name

2. Select * Into New_Table_Name From Org_Table_Name

       Where Site='한국'

-- Insert Into 사용 방법

1. 두 테이블의 컬럼이 같을때 SELECT INSERT 하기

두개의 테이블간에 컬럼이 table_a 와 table _b 처럼 완전히 일치할 때는 아래와 같이 간단하게 처리할 수 있습니다.

예1) INSERT INTO table_b SELECT * FROM table_a

위의 예1) 에서는 table_a 의 레코드 전체를 table_b 로 삽입(INSERT) 시킵니다. 만약에 조건을 주어 데이터를 삽입(INSERT) 시키려 한다면 아래와 같이 뒤에 WHERE 절만 추가해 주시면 됩니다.

예2) INSERT INTO table_b SELECT * FROM table_a WHERE seq = 2

위의 SQL 문은 table_a 에서 seq 의 값이 2 인 레코드를 table_b 에 INSERT 시킵니다.

2 . 두 테이블간의 컬럼이 일치하지 않을때 SELECT INSERT 하기
두개의 테이블간에 컬럼이 table_a 와 table _c 처럼 일치하지 않을때는 아래와 같이 컬럼명을 지정해 주시면됩니다.

예3) INSERT INTO table_c ( seq, Name, Email, Idate ) SELECT seq, Name, Email, Idate FROM table_a

위의 예3) 에서는 table_a 의 컬럼중 seq, Name, Email, Idate 컬럼을 table_c 에 Insert 시키고 있습니다. 한가지 주의
할 것은 table_c 의 구조가 table_b 와 다른 만큼 INSERT 될 컬럼이 SELECT 되는 컬럼과 일치하지 않으면 아래와 같은
에러가 발생하게 됩니다.

cA_Table
SET A_Column_01 = b.B_Column_01
FROM A_Table a
    INNER JOIN B_Table b
    ON a.A_Key = b.B_Key
WHERE a.A_Key is NOT NULL

예를 들어, A_Table의 A_Column_01의 값을 B_Table의 B_Column_01 값으로 업데이트 하려한다. 단, A_Table의 A_Key와 B_Table의 B_Key가 같은 항목에 한해서만 변경이 이루어져야 한다.
* 위와같이 Join이 필요한 업데이트 구문은 다음과 같다.

Select object_name(id) From syscomments Where text like '%테이블 명 또는 필드명%'
Group by object_name(id)
Order by object_name(id)

SELECT 컬럼1,컬럼2,컬럼3

FROM     테이블
GROUP BY 컬럼1,컬럼2,컬럼3

HAVING COUNT(*) > 1

서버쿼데이터를 비교하여, 결과값을 보고자하는 경우.

ex) A 테이블과 B 테이블을 비교하여 다른 내용이 있는경우 해당 칼럼을 출력.

select * from A where not exists
(select * from B where A.aa = B.aa)

A 테이블의 aa와 B 테이블의 aa가 다른 값인 경우, 데이터가 틀린 칼럼만 select문이 수행되어 보여진다.

SET LANGUAGE KOREAN --//한국어로 설정

GO

SELECT 1/0 --//0으로 나누기 에러 발생

GO

SET LANGUAGE ENGLISH --// 영어로 설정

GO

SELECT 1/0 --//0으로 나누기 에러 발생

GO

설정할 수 언어의 종류는.. 아래 테이블에 저장이 되어 있습니다. 

SELECT * FROM master.dbo.syslanguages

또한 SET LANGUAGE는 기본 적으로 모든 유저가 설정할 수 있도록 되어있습니다.(= 권한 제약이 없음)

SET LANGUAGE {name | alias}

http://msdn.microsoft.com/en-us/library/aa259215(v=sql.80).aspx