OPENQUERY INSERT/UPDATE/DELETE

-- update
UPDATE OPENQUERY(LINKED1, 'SELECT SSN FROM TESTLINKED WHERE SSN=2')
SET SSN=SSN + 1

-- insert
INSERT OPENQUERY(LINKED1, 'SELECT SSN FROM TESTLINKED WHERE 1=0') VALUES (1000)

-- delete
DELETE OPENQUERY(LINKED1, 'SELECT SSN FROM TESTLINKED WHERE SSN>100')

SQL Server 에 접속되어 있는 IP 찾기

DMBS에서 CMD 명령어를 사용하기 위해 cmdshell을 활성화 하는 것은 보안상 취약 할 수 있으므로

주의가 필요함.

 

 
 

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

-- cmdshell Option Enable

sp_configure 'show advanced options', 1

RECONFIGURE

sp_configure 'xp_cmdshell', 1

RECONFIGURE

 

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

--- Create Procedure

CREATE PROCEDURE usp_getboundAddresses

AS

BEGIN

 

SET NOCOUNT ON

 

IF OBJECT_ID('tempdb..#SomeTable') IS NULL

CREATE TABLE #SomeTable

(

ShellOutput VARCHAR(500)

)

 

INSERT INTO #SomeTable

EXEC xp_cmdshell 'nbtstat -a .'

 

SELECT

SUBSTRING( ShellOutput,

CHARINDEX('[',ShellOutput)+1,

-- LEN(ShellOutput) - CHARINDEX(']',ShellOutput) -2 --(EN)

 

LEN(ShellOutput) - CHARINDEX(']',ShellOutput) + 1 --(KR)

 

) AS BoundAddress

from #SomeTable

-- WHERE ShellOutput LIKE '%Node IpAddress%' --the important lines(EN)

 

WHERE ShellOutput LIKE '%노드 IpAddress%' --the important lines(KR)

AND ShellOutput NOT LIKE '%0.0.0.0%' --Unassigned addresses

 

DROP TABLE #SomeTable

 

END

 

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

-- Execute Procedure

 

exec usp_getboundAddresses

 

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

-- Result

 

 

Could not continue scan with NOLOCK due to data movement

에러 메세지 : 

Msg 601, Level 12, State 2, Line 8

데이터 이동으로 인해 NOLOCK으로 계속 검색할 수 없습니다.

 

영문 : Could not continue scan with NOLOCK due to data movement 

 

해결 : Index Rebuild 후 다시 쿼리 실행.

SQL Server 버전 정보 확인

SSMS UI 내에서도 확인 가능하겠지만,
쿼리로도 조회 가능.


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

제품버전        제품수준     에디션

결과 : 10.0.1600.22       RTM      Enterprise Edition

SQL Server 에러번호로 에러메세지 확인하기

 

 

SELECT * FROM MASTER.SYS.MESSAGES WHERE MESSAGE_ID = 8630 AND LANGUAGE_ID = 1042

결과 : 8630 1042 17 0 내부 쿼리 프로세서 오류: 쿼리 프로세서 실행 중 오류가 발생했습니다.

Windows Server 2008에 SQL Server 2008 설치시 에러

증상

.net Framework 3.5 설치관련 에러 메세지

 

해결 

 따로 3.5를 설치할 필요 없으며 (할려고 해도 에러) 

서버 로그인시 초기 구성 작업 창이 뜨면 기능 추가 를 통해 설치가 가능하다. 

(실행 > ServerManagerLauncher 로도 실행가능함) 

[ERWin] Revers Engineer to SQL Server 2005

증상

ERWin에서 Reverse Engineer를 하기위해 SQL Server 2005에 접속시도중

DAX000: Unable to locate client connectivity software sqlncli.dll(Native ODBC Connectivity)

위와 같은 에러를 내면서 접속조차 되지 않음.


해결
Client 버전 문제이겠구나.. 생각하고 SQL Server 2005 Client 설치, 그래도 동일한 오류.
MS Download Center에서 SQL Server 2005용 기능팩을 다운로드 할 수있고 이 기능팩 설치후 정상 동작.

http://www.microsoft.com/downloads/ko-kr/details.aspx?FamilyId=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=ko

증상2.
접속은 되는데.. 테이블을 긁어오는 도중 ERWin이 뻗어버리는 증상이 발견.

해결.
정확히 어떤 옵션 때문에 뻗어버리는지는 찾지 못했지만 (일일이 체크하며 찾기 귀찮..)
Reverse Engineer시 기본적으로 옵션이 모두 체크되어있는데 단순히 테이블만 긁어오는 용도라면 다른 옵션을 모두 제거하고 테이블만 선택하면 에러없이 정상적으로 Reverse Engineer가 완료됨.

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