'SQL Server'에 해당되는 글 22건

  1. 2012.04.23 오류 5242이(가) 발생했습니다
  2. 2012.03.08 SQL Server에 주석 달기
  3. 2010.09.06 순번 구하기
  4. 2010.07.31 SQL Server 데이터 복구하기
  5. 2010.07.04 데이터베이스 문제점 파악 방법
  6. 2010.06.30 NULL 주의
  7. 2010.06.17 SQL Server 2005 에서 2000 으로 변경하기
  8. 2010.05.18 sp_help에 속았다. - 컬럼 길이
  9. 2010.05.03 SQL Server에서 실행된 쿼리 보기
  10. 2010.01.28 SQL Server 연결된 서버 설정
  11. 2010.01.27 SQL Server 연결된 서버 설정 - 오라클
  12. 2010.01.17 [DBNETLIB][ConnectionOpen (Invalid Instance()).]잘못된 연결입니다. 1
  13. 2010.01.15 SQL Server 원격연결
  14. 2009.12.29 DTC 확인 쿼리
  15. 2009.12.18 잘못된 예외 처리로 인해 엉뚱한 에러 발생
  16. 2009.12.02 서버 'xxxerp'이(가) RPC에 대해 구성되지 않았습니다.
  17. 2009.07.25 SQL Server 2005에서 SchemaSpy 실행하기
  18. 2009.07.09 지정한 트랜잭션 코디네이터에 새 트랜잭션을 기록할 수 없습니다.
  19. 2009.05.22 테이블을 수정할 수 없습니다./제한 시간이 만료되었습니다.
  20. 2009.05.11 Microsoft SQL Server JDBC Driver
  21. 2009.03.23 SQL Server 백업/복원
  22. 2009.02.28 문자열 함수

오류 5242이(가) 발생했습니다

- 개발기에서 갑자기 이런 오류가 발생
경고: 04 23 2012  8:26PM에 오류 5242이(가) 발생했습니다. 오류와 시간을 기록한 다음 시스템 관리자에게 문의하십시오.

- 데이터베이스 엔진 오류의 원인 및 해결 방법 MSSQLSERVER_5242

- DBCC CHECKDB 결과 이상없음.
데이터베이스 서버를 내렸다 올리니깐 제대로 동작함.

[todo] 이벤트 로그 및 디비서버 로그 확인하기
저작자표시 비영리 변경금지

SQL Server에 주석 달기

EXEC sp_addextendedproperty 'MS_Description', 'COMMENT', 'user', dbo, 'table', TABLE_NAME
EXEC sp_addextendedproperty 'MS_Description', 'COMMENT', 'user', dbo, 'table', TABLE_NAME, 'column', COLUMN_NAME

이 확장속성은 2005버전부터 지원될거야.

MS_Description은 변경하지 말고 그대로 사용할 것.
이부분이 변경되면 디자인 - 설명에서 안보이고 확장속성에서만 확인할 수 있음.

[todo] 뷰에서도 가능한가? SP나 Function은? 원격 데이터베이스의 개체는?
[todo] 쿼리로 보는 방법
이걸 작성할때 컬럼 다 뽑아오는 방법...시스템 테이블 쓰면 될거야
저작자표시 비영리 변경금지

순번 구하기

TB_DATA에 pk1, pk2, pk_old 가 기본키로 존재했음.
pk_old는 중복된 값이 들어올 수 있어서 pk_new 가 새로운 키로 들어와서 pk_old와 교체해야 하는 상황이 생겼음.
pk_new는 순번이다.

pk_new를 추가하고 기존 기본키를 삭제.
pk_new는 데이터가 들어가 있지 않으므로 순번을 넣어야 함.
UPDATE TB_DATA --SQL Server 2005
SET pk_new = b.ser
FROM TB_DATA a, (
    SELECT rank() OVER (PARTITION BY pk1, pk2 ORDER BY pk_old) as ser
    , pk1, pk2, pk_old
    FROM TB_DATA
) b
WHERE a.pk1 = b.pk1 and a.pk2 = b.pk2 and a.pk_old = b.pk_old

- 순번을 rank()를 사용하지 않고 구하는 방법도 있음.

- 위 구문에서 SET 절의 pk_new를 a.pk_new로 하면 다음과 같은 에러가 발생한다.
메시지 4104, 수준 16, 상태 1, 줄 2
여러 부분으로 구성된 식별자 "a.pk_new"은(는) 바인딩할 수 없습니다.


저작자표시 비영리 변경금지

SQL Server 데이터 복구하기

UPDATE 구문을 실행시키는데 WHERE 을 빠뜨렸단다.
눈 앞이 깜깜하다.

어떤 데이터? B업무의 시작 시간
윽...이거 민감한 건데.
몇 건이나? 882건

일단 보고를 하고 백업본을 찾아보자.
유지 관리 계획을 보니 매일 백업을 하고 있었다.
다행이다.

백업파일을 찾아서 복원을 해본다.
혹시나 싶어 테스트 서버에서 진행했다.(다른 데이터베이스를 하나 생성해서 거기서 진행하면 될거 같다)
몇 번 실패했는데 다행히 잘 복구가 되었다.

매일 새벽 1시에 백업이 실행되어서 현재 데이터와 비교했다.
2건이 차이가 난다. - SQL Server에서는 minus가 안된다.

오늘 새로 생성된 데이터는 담당자에게 직접 물어보기로 하고 나머지는 백업본의 데이터로 갱신한다.
다행히 일과 시간 이후였고, B업무가 시작 전이라서 데이터의 변화가 거의 없었다.
급박한 경우에는 WAS에서 막고 작업을 해야 될지도 모름.

- 오늘의 교훈
  • 반드시 주기적으로 백업을 해야 한다.
  • 이 백업본을 가지고 복원을 제대로 할 수 있어야 한다.(평소에 연습을 해두어야 한다)
  • 데이터를 조작하기전 쿼리를 반드시 확인한다.
저작자표시 비영리 변경금지

데이터베이스 문제점 파악 방법

어떤 계정으로
어느 시스템에서
어느 데이터베이스를 사용했을때
어떤 프로그램을 통해서
어떤 쿼리를 실행시켰을때
상황이 어떠하며
에러가 발생했는지를 확인하는 것이 중요하다.

SQL Server는 Profiler를 이용하는 것이 가장 좋다.

사례1) SQL Server 에서 락이 발생
app_user 로 로그인해서 : sp_who
dev_server 에서 쿼리를 실행함. : sp_who
ERP 데이터베이스를 사용 : sp_who
알 수 없는 쿼리 : dbcc inputbuffer(spid), 쿼리 확인
X Lock 을 걸고, 다른 프로세스에서 SELECT 를 하는데 락이 걸림 : sp_lock

[todo] 오라클에서는?
저작자표시 비영리 변경금지

NULL 주의

- SQL Server 2000

- NULL과 연산을 하면 NULL이다.


- NULL을 찾을때는 IS를 사용해야 한다.(=이 아님)

저작자표시 비영리 변경금지

SQL Server 2005 에서 2000 으로 변경하기

- row_number() 함수가 2000에서는 지원되지 않으므로 페이징 쿼리를 다시 작성

- 동의어 지원 안됨
원격 뷰, 원격 프로시저에 동의어를 사용했음.
기존 동의어 이름과 동일한 뷰를 만들고 이 뷰가 원격 뷰를 참조하도록 한다.
프로시저도 마찬가지.
저작자표시 비영리 변경금지

sp_help에 속았다. - 컬럼 길이

sp_help로 본 결과를 제대로 봐야 한다.

Length는 데이터 형식의 물리적 길이(바이트)다.
Prec는  전체 자릿수,  Scale은 소수점 이하 자릿수다.

문자형인 경우에는 길이는 Length를 보면 된다.
숫자형인 경우는 잘 봐야 한다.


numeric(1, 0)이나 decimal(1, 0)가 차지하는 길이는 5바이트이다.
그러나 데이터는 -9 ~ 9까지만 입력할 수 있다.

tinyint는 1바이트를 차지하므로 2^8이고 0 ~ 255까지 입력할 수 있다.
자릿수가 3이지만 999는 입력할 수 없다.

int는 4바이트를 차지하므로 -2^31[각주:1] ~ 2^31 - 1까지 입력할 수 있다.
자릿수가 10이지만 2^31(2,147,483,648)을 입력할 수 없다.
  1. 2^(8 * 4) / 2 [본문으로]
저작자표시 비영리 변경금지

SQL Server에서 실행된 쿼리 보기



[todo] spid, host등 추가적인 정보가 있으면 좋겠다.
dbcc inputbuffer 와 차이는?
저작자표시 비영리 변경금지

SQL Server 연결된 서버 설정

여러가지 방법으로 가능함.

1.
연결된 서버명 : IP 주소
공급자 : Microsoft OLE DB Provider for SQL Server

2.
연결된 서버명 : HOST명
공급자 : Microsoft OLE DB Provider for SQL Server

3.
공급자 : SQL Native Client
제품 이름 : 호스트명? 연결된 서버명과 동일
데이터 원본 : IP 주소

4.
공급자 : Microsoft OLE DB Provider for SQL Server
데이터 원본 : IP 주소
카탈로그 : 데이터베이스명

5. SQL Server 2005 에서 MySQL 연결
공급자 : Microsoft OLE DB Provider for ODBC Drivers
제품 이름 : MySQL ODBC 5.1 Driver
데이터 원본 : ODBC 등록한 이름
저작자표시 비영리 변경금지

SQL Server 연결된 서버 설정 - 오라클

SQL Server 2005 Express와 Oracle 10g Express간의 연결된 서버 설정

- 공급자를 Microsoft OLE DB Provider for Oracle 로 지정
제품 이름 : Oracle
데이터 원본 : 10.20.30.40:1521

- 공급자를 Oracle Provider for OLE DB 로 지정
연결이 안됨.
TNS로 설정하거나 추가로 필요한 모듈을 설치해야 된다고 함.(해보지는 않았음)
[todo] 129에 받아둔 거 설치해서 해보자.

L사 SQL Server 2005 와 다른 주소의 오라클 연결
공급자를 Microsoft OLE DB Provider for Oracle 로 지정
제품 이름 : Oracle
데이터 원본 : ERP_DEV (tnsnames.ora에 설정된 이름임)

* 대소문자 주의할 것.
저작자표시 비영리 변경금지

[DBNETLIB][ConnectionOpen (Invalid Instance()).]잘못된 연결입니다.

ERP에서 우리 시스템의 데이터베이스에 연결된 서버로 붙이려고 하는데 한참동안 쿼리가 실행되더니 접속이 되지 않는다.

암호도 바꿔보고
권한도 아예 db_owner로 줬는데도 그러네.

그런데 쿼리를 실행한 결과를 보니 요상한 에러 메세지가 나왔다.
[DBNETLIB][ConnectionOpen (Invalid Instance()).]잘못된 연결입니다.
처음부터 에러메세지를 확인했어야 한다.
쿼리가 너무 오래 걸린다고 에러메세지도 확인하지 않고 부산을 떨었다.

검색해보면 포트번호를 추가하라고 되어 있는데 어디서 해야 하나?
데이터 원본에 아이피를 넣었는데 그 뒤에 ,를 넣고 1433 포트번호를 추가했다.
된다.

헉.
오늘의 교훈 : 에러 메세지부터 확인하라.
저작자표시 비영리 변경금지

SQL Server 원격연결

SQL Server 2005 Express 설치후 원격 연결이 안됨
로컬에서 localhost\sqlexpress, devtest\sqlexpress 는 되지만 127.0.0.1\sqlexpress 는 안됨

- 원격연결을 허용하도록 구성하기
1. SQL Server 노출영역 구성
- 로컬 및 원격 연결 선택
- SQL Server Browser 시작 (이건 안해도 될듯)

2. SQL Server Configuration Manager
IPAll만 설정하면 될듯

- 별칭으로 접근하기
로컬에서 127.0.0.1\sqlexpress 는 되지만 원격지에서 IP\sqlexpress 로는 안됨
(SQL Server Configuration Manager 에서 접속하려는 IP 를 사용하도록 설정하면 되지만 그렇게 하지 않는 경우)

클라이언트 네트워크 유틸리티로 별칭 등록
- 서버 이름 : IP\sqlexpress
- 포트번호 : 1433(동적으로 확인하면 안됨)
저작자표시 비영리 변경금지

DTC 확인 쿼리

어디선가 본 DTC 통신 확인 쿼리
되는 경우 1?
저작자표시 비영리 변경금지

잘못된 예외 처리로 인해 엉뚱한 에러 발생

데이터 형식 nvarchar을(를) numeric(으)로 변환하는 중 오류가 발생했습니다.

갑자기 에러가 발생했다.
이런 에러가 예전부터 많이 나오긴 했었다.

문제가 없는 쿼리인데 에러가 발생한다.

로그를 보니 이상한 로그가 있다.
DEXTUploadException --> 데이터를 읽어오는 데 실패했습니다. 사용자와의 연결이 끊어졌거나 업로드를 중지했습니다.

예외를 잡아서 로그만 남기고 아무런 처리를 하지 않는다.
업로드컴포넌트에서 처리가 되지 않아서 DAO에서 쿼리문을 처리할때 pk2가 빈 문자열이 넘어온거 같다.
(웹요청에 대한 값을 Map에 넣었다가 getString(NAME) 같은 메서드로 처리하는데 NAME으로 된 값이 없으면 빈 문자열로 처리하게 되어 있음)

결국에는 아래와 같은 쿼리가 실행되었다.

업로드컴포넌트에서 에러가 난 이유는 디스크가 다 차서 더이상 용량이 남아 있지 않았다고 한다.

- 2010-02-08
다른 프로그램에서 동일한 에러 발생
전송을 했는데 화면이 정지하고 반응이 없었다고 함.
서버/네트워크 사용량이 많아서인지, 업체 PC 사양이 떨어져서 그런지는 불분명함.
저작자표시 비영리 변경금지

서버 'xxxerp'이(가) RPC에 대해 구성되지 않았습니다.

원격 프로시저가 호출되지 않는 경우 RPC 설정을 바꾼다.


True 로 변경한다.
RPC, RPC 내보내기 모두 해야 함.[각주:1]
  1. 2010-01-14 추가 : 되는듯. 2010-01-17 추가 : 함. [본문으로]
저작자표시 비영리 변경금지

SQL Server 2005에서 SchemaSpy 실행하기

- 데이터베이스 타입을 mssql05로 두고, 드라이버를 sqljdbc.jar를 사용한 경우 아래와 같은 에러가 발생한다.

Failed to retrieve column comments: com.microsoft.sqlserver.jdbc.SQLServerException: variant 데이터 형식은 지원되지 않습니다.
SELECT OBJECT_NAME(c.object_id) AS TABLE_NAME, c.name AS COLUMN_NAME, ex.value AS comments FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 ORDER BY OBJECT_NAME(c.object_id), c.column_id
com.microsoft.sqlserver.jdbc.SQLServerException: 연결이 닫혔습니다.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.checkClosed(Unknown Source)
    ...
    at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:164)
    at net.sourceforge.schemaspy.Main.main(Main.java:21)

- 데이터베이스 타입을 mssql05-jtds로 바꾼다.
저작자표시 비영리 변경금지

지정한 트랜잭션 코디네이터에 새 트랜잭션을 기록할 수 없습니다.

- 갑자기 아래와 같은 에러가 발생함.
메시지 7391, 수준 16, 상태 1, 줄 1
OLE/DB 공급자 'SQLOLEDB'이(가) 분산 트랜잭션을 시작할 수 없으므로 작업을 수행할 수 없습니다.
OLE DB 오류 추적 [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
[OLE/DB provider returned message: 지정한 트랜잭션 코디네이터에 새 트랜잭션을 기록할 수 없습니다. ]

- 사례1
원격지에 있는 SQL Server에 Linked Server를 통해서 연결한뒤 데이터를 가져올때 발생
원격지 DB에 있는 뷰에서 다른 원격지의 테이블을 참조하는데 권한이 없어서 발생한 거 같다.

- 사례2
새로운 서버가 들어온 뒤 이런 에러가 발생함.
각 서버(원격지 서버, 현재 접속한 서버)의 hosts파일에 호스트명을 등록했다.
2012-04-16 서버 IP변경후 발생함. 원격지 서버의 hosts파일에 해당 서버의 IP를 변경함.

- 사례3
MSDTC 보안 설정을 한다.(네트워크 DTC 액세스, 트랜잭션 관리자 통신 인/아웃바운드 허용(둘중 하나 또는 둘다), 인증 필요 없음)

Windows 2000

Windows 2008

- 참고
분산 트랜잭션이 작동하지 않을 때
MS-DTC Setting on Windows 2003
분산트랜잭션, MSDTS 문제 이렇게 해결해 보자.
분산 트랜잭션 설정

DTCPing.exe 활용하여 연결성 체크하기
DTCPing.exe
DTCTester 도구를 사용하는 방법

About RPC... (1)
About RPC... (2)
About RPC... (3)
저작자표시 비영리 변경금지

테이블을 수정할 수 없습니다./제한 시간이 만료되었습니다.

모'TB_TEMP' 테이블
- 테이블을 수정할 수 없습니다.
제한 시간이 만료되었습니다. 작업을 완료하기 전에 제한 시간이 경과되었거나 서버가 응답하지 않았습니다.

SQL Server에서 테이블을 수정하는데 시간이 오래 걸리더니 수정되지 않는다.
데이터가 그렇게 많지도 않았다.

- 확인
sp_lock
sp_who

- 작업모니터(관리)

- 51 번 프로세스를 kill 해도 죽지 않는다.
그냥 야간에 데이터베이스를 재기동시켰음.
저작자표시 비영리 변경금지

Microsoft SQL Server JDBC Driver

2008을 지원한다기 보다 jdbc 4.0을 지원하는 거다.
SQL Server JDBC Driver 2.0 Documentation
http://msdn.microsoft.com/ko-kr/library/bb418447%28v=SQL.10%29.aspx

JDBC 드라이버 시스템 요구 사항
http://msdn.microsoft.com/ko-kr/library/ms378422%28v=SQL.100%29.aspx


- MS Driver


  • 이 드라이버는 아마도 SQL Server 2000 드라이버.
  • com.microsoft.jdbc.sqlserver.SQLServerDriver
  • jdbc:microsoft:sqlserver://ADDRESS:1433
  • mssqlserver.jar 와 com/microsoft/jdbc/base/BaseDriver 을 필요로 한다.

- jTDS
  • http://jtds.sourceforge.net
  • net.sourceforge.jtds.jdbc.Driver
  • jdbc:jtds:sqlserver://ADDRESS:1433/DB_NAME
  • jtds-1.2.2.jar
  • TEXT 컬럼 처리 가능

=-=> ms 드라이버는 text 컬럼처리 안되는게 있을거야.

- Windows 통합인증을 사용할 때는 sqljdbc_auth.dll이 필요 ==???


저작자표시 비영리 변경금지

SQL Server 백업/복원

- 백업 - 복원
  • 복원중 에러가 발생하면 복원될 파일 위치를 확인해본다. : 기본값으로 잡혀 있는데 적당한 경로나 운영 규칙에 맞게 설정한다.(기존의 데이터 파일이나 로그 파일이 아니고 새로 복원된 데이터베이스가 사용할 파일이다.)[각주:1]
  • 기존 데이터베이스 덮어쓰기를 선택한다.
  • 복원된 데이터베이스에 로그인할 계정을 만들고 사용자 매핑에서 복원된 데이터베이스를 선택하고 db_owner 역할을 준다.
- 복원에 걸리는 시간
CPU 3G, RAM 1G : 10분 넘게 소요됨.(bak 파일 821M, data 파일 821M, log 파일 24.5G)
[todo] 위 시스템의 사용, 환경 링크 걸기
  1. 2010-07-31 추가 [본문으로]
저작자표시 비영리 변경금지

문자열 함수


=-=> oracle?


저작자표시 비영리 변경금지

티스토리툴바