JKUN

Welcome To The Jkun.net

Blog

OPENQUERY 를 이용한 INSERT/SELECT/UPDATE

2016.01.12 18:54 DataBase/MS-SQL



INSERT INTO OPENQUERY([LINKED SERVER], '  SELECT [COLUMNS] FROM [TABLE]')
VALUES ~ SELECT [ '이건 입맛대로~' ]

* INSERT, UPDATE, DELETE, SELECT 모두 OPENQUERY(연결된 서버, 'SQL문자열') 로
동일합니다.

저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

excel linked server 메시지 7303, 수준 16, 상태 1, 줄 31

2016.01.12 18:46 DataBase/MS-SQL


출처 : http://sqlsql.tistory.com/391



64bit sql server 에 excel 2013을 링크드 서버나 직접쿼리를 할 때 

일단 드라이버를 깔아야 한다. http://www.microsoft.com/en-us/download/details.aspx?id=13255


그런 후, 드라이버의 inprocess 설정을 한다. 

USE [master]

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

GO


계정설정 

혹시 NT Service\MSSQL$SQL2012 이런식으로 되어 있으면 -> 로컬시스템으로 바꾼다. ㅠ.,ㅠ

아니면 해당 폴더에 위 계정이 액세스 가능하게 하면 될 듯 하다. appdata\local\temp 역시 권한이 필요한듯 

이런거 다 귀찮으면 로컬 시스템으로 변경~


NT Service\MSSQL$SQL2012 였을때 에러 메시지 

연결된 서버 "(null)"의 OLE DB 공급자 "Microsoft.ACE.OLEDB.12.0"이(가) 메시지 "지정되지 않은 오류입니다."을(를) 반환했습니다.

메시지 7303, 수준 16, 상태 1, 줄 31

연결된 서버 "(null)"에 대한 OLE DB 공급자 "Microsoft.ACE.OLEDB.12.0"의 데이터 원본 개체를 초기화할 수 없습니다.


링크드 서버 연결 명령어 

/****** Object:  LinkedServer [bb]    Script Date: 2015-04-23 오전 11:08:34 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'bb', @srvproduct=N'ACE', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'c:\sqltag\Book1.xlsx', @provstr=N'Excel 12.0; HDR=Yes'

 /* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'bb',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO


인프로세스 쿼리 방법 

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=c:\sqltag\Book1.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]


SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; HDR=YES; IMEX=1; Database=F:\cmdb\fault티켓\Book1.xlsx', 'SELECT * FROM [Sheet1$]')


SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; 

HDR=YES; IMEX=1; Database=\\127.0.0.1\f$\cmdb\fault티켓\FQ261_2011.xlsx', 'SELECT * FROM [sheet1$]')


SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; 
HDR=NO; IMEX=1; Database=c:\sqltag\Book1.xlsx', 'SELECT * FROM [Sheet1$]')

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=\\127.0.0.1\c$\sqltag\Book1.xlsx;Extended Properties=EXCEL 12.0')...[Sheet1$] ;


-- 읽어보기 귀찮다.

-- 스타트 계정 로컬시스템으로 변경 후
exec sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure with override

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; HDR=YES; IMEX=1; Database=C:\event\faultPrco_2010q1.xls', 'SELECT * FROM [data$]')

저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

MSSQL 메시지 7347 - 예상 데이터 길이와 일치하지 않는 데이터를 반환했습니다.

2016.01.12 18:44 DataBase/MS-SQL


메시지 7347, 수준 16

LINKED SERVER (연결된 서버) 를 통하여 쿼리시 (OPENROWSET, OPENQUERY)


오라클이나 MySQL 에서  "예상 데이터 길이와 일치하지 않는 데이터를 반환했습니다." 라는

메세지는 지금까지의 경험(짧은^^;;) 으로는 숫자형의 데이터를 문자형으로 전환하면

되었던 것 같다.

저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

Code Formatter - 코드정렬

2013.07.03 10:08 DataBase/MS-SQL




SQL Server 2008 R2.


SQL Server Management Studio 쿼리를 작성하다 보면, 토드나 SQL Gate 등 코드정렬(포맷) 기능이 너무

아쉽다. 이에 구글링해서 좋은 툴을 찾았다. MSI 파일 형태로 제공된다.

아래 파일로 다운로드 받아도 됩니다. 확장자 zip 를 지워주시고 msi 파일형태로 바꿔 실행시키시면 됩니다.



SqlFormatterSSMSAddIn.Setup.1.4.4.msi.zip



링크 : http://architectshack.com/PoorMansTSqlFormatter.ashx

그런데.. SQLER 에.. 이미 윤선식님이 깔끔하게 정리를 해놓으셨었음.ㅡㅡ;;


링크 : http://www.sqler.com/504675

저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

스키마 바인딩시 에러;;;

2012.10.31 14:19 DataBase/MS-SQL


스키마 바인딩할 수 없습니다. 이름은 두 부분으로 구성되어야 하며 개체는 자체 참조할 수 없습니다.


CREATE VIEW HAN_RECOM_POINT_GIVE_LIST

WITH SCHEMABINDING

AS

 셀렉트 어쩌고 저쩌고 하는데 위와 같은 오류가 발생한다. 디지게 짜증나서 한참을 삽질하다

 데이터베이스 사랑넷에서 관련 글이 있었다.

 테이블이나 오브젝트에 열심히 dbo. 을 붙여주라고 해서, 붙여봤더니 정상적으로 생성.


그런데 짜증나는건 이유를 모르겠다. 왜 dbo. 를 넣었는데 된건지;;;

그냥 그런가보다 숙지하기에는 기분이 별로다.


혹시 아시는 분들 지나가시다 보시면 답글좀 부탁드립니다.^^;

저작자 표시
신고

'DataBase > MS-SQL' 카테고리의 다른 글

OPENROWSET  (0) 2013.05.29
CHARINDEX 를 이용한 문자열 필터링  (0) 2013.05.29
스키마 바인딩시 에러;;;  (0) 2012.10.31
VIEW 인덱스 생성시 스키마바인딩  (0) 2012.10.31
실행된 쿼리내역 조회  (0) 2012.10.24
MS-SQL 임시테이블  (0) 2012.09.19
현재 0 개의 댓글이 있습니다.
Comment

Blog

MS-SQL 2008 에서 IDENTITY 경고 풀기

2012.03.06 13:07 DataBase/MS-SQL



SQL Server 2008 에서 IDENTITY 를 적용된 사항을 풀거나, 테이블의 옵션을 변경하는 경우
경고가 나타나며 안된다고 한다.
그럼 옵션에 위 항목을 확인해 보자. 
저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

MySQL 에서 MS-SQL 로 데이터 이관 (MySQL ODBC Connector)

2012.01.10 17:04 DataBase/MS-SQL


이미 많이들 알고 있겠지만 난 개인적으로 처음이어서 포스팅을 한다.
MySQL 에서 MS-SQL 로 데이터 이관하는 작업이다.

테스트 환경은 다음과 같다.

* 원본 위치 환경
OS : Windows 2008 R2 Enterprise 64bit
DBMS : MS-SQL Server 2008 R2

* 이관 위치 환경
OS : Windows 2008 R2 Enterprise 64bit
DBMS : MySQL 5.1

이관될 위치 환경은 APMSETUP7 으로 설정된 환경이다.

순서는 아래와 같다.

먼저 MySQL ODBC Connector 를 다운로드 받는다. (다운로드 링크를 누른다.)


 
다운로드 받은 파일을 설치하고, 시작 → 관리도구  →  데이터 원본(ODBC) 를 실행한다.


그리고 사용자 DSN 탭에 추가 버튼을 누른다. (난 이미 추가되있어서..)


그럼 다음과 같은 새 데이터 원본 만들기 창이 나타난다.



저 상태에서 MySQL ODBC 5.1 Driver 항목을 선택하고 더블클릭 한다.

그럼 아래와 같은 ODBC Connector 창이 나타난다. 그럼 MySQL 계정정보를 입력하자.
여기서 Data Source Name 은 MySQL 에서 사용할 이름이다. 입맛에 맞게 설정하자.
 
r


여기까지 입력이 완료하고 OK 버튼을 누르고 마침 버튼을 누른다.
그럼 ODBC에 사용자 DSN 항목에 작성한 이름이 추가되어 나타나있는것을 확인할 수 있다.

[##_http://www.jkun.net/script/powerEditor/pages/1C%7Ccfile10.uf@191352334F0BE8E432AC5B.PNG%7Cwidth=%22533%22%20height=%22379%22%20alt=%22%22%20filename=%224.PNG%22%20filemime=%22image/jpeg%22%7C_##]
 그리고 이제 부터 MS-SQL 2008 에서 적용하는 과정이다.
 이미 Linked Server (연결된 서버) 를 사용해 본 사람들이 쉽게 할 수 있다. 
 아래와 같이 연결된 서버를 실행한다.


그리고 새 연결된 서버 창이 나타나면 정보를 입력한다.
공급자 : Microsoft OLE DB Provider for ODBC Driver
제품이름 : MySQL
데이터 원본 : 사용할 이름
공급자 문자열 : Driver={MySQL ODBC 5.1 Driver}; Server=서버; Database=데이터베이스;
위치 : 사용할 이름 


그리고 보안 탭에 MySQL 계정정보를 입력한다.

 
그리고 확인버튼을 누른다. 이에 만일 데이터액세스가 안되는 경우 서버 옵션 탭에
RPC 항목을 True 로 바꾸어 준다.
 


그리고 연결된 서버공급자 항목MSDASQL 항목에 속성을 활성화한다.
 


그리고 공급자 옵션 창이 나타나면 다음 항목에 체크해 준다.
중첩쿼리, 0 수준만, Inprocess 허용, 트랜잭션되지 않은 업데이트, LIKE 연산자를 지원합니다.


그리고 확인하고 마무리 지으면 다음처럼 활성화된 항목을 확인하며 MySQL 쪽에 데이터베이스 정보가
나오는 것을 확인.
 


그리고 조낸 캡쳐를 떠가며 작성한 사람을 위해서 댓글을 달아주는 센스까지 하면
완전 마무리가 된다.

MySQL 에서 MS-SQL 로 데이터 이관, 
MySQL 에서 MS-SQL 로 데이터 옮기기,

저작자 표시
신고
현재 5 개의 댓글이 있습니다.

김도현 2012.01.26 11:30 신고

링크드로 연결 시키는 방법인가요?
찾고 있는 자료였는데 감사합니다.
적용시켜 보겠습니다.


김도현 2012.01.26 12:15 신고

그런데 혹시 반대의 경우도 적용 가능 할까요?


나그네 2012.02.23 19:33 신고

자세한 설명 감사합니다..
다른 자료들은 그림도 없고 비슷한 내용을 두리뭉실하게 설명해놓았는데..
여기는 설명이 자세해서 따라하기 쉬웠습니다..

작성자 최고.. !!


csy3596 2013.02.20 11:46 신고

감사합니다.
MSSQL에 MYSQL을 연결시킬때 마다 MSSQL쪽이 죽어버려서 곤란한 상황에 있었는데 작성자님 덕분에 해결할 수 있었습니다.


  • JKUN Dev. Jkun 2013.02.20 12:25 신고

    아. ㅎㅎ 도움이 되셨다니 다행입니다.
    센스댓글 감사합니다~ ^^

Comment

Blog

SQL Server 2008 설치 오류 [역할관리도구]

2011.12.13 08:59 OS/Windows


한동안 SQL Server 를 쓰지 않다보니.. 어처구니 없는...
오류메세지가.. 나타났는데 도저히 기억이 안난다. 그냥 닷넷 프레임워크가 설치가 잘 안되서
그런줄 알았뜨만.. 짜증나게..

 
아어!
서버관리자에  기능추가를 해주자.

서버관리자에 기능추가를 선택하면 .NET Framework 3.51 기능
이란 선택란이 있다. 요걸 선택해서 활성화 해주자.



뭐.. 간단하게 설치순서를 요약하자면..
1. .NET Framework 설치
2. IIS 설치, 기능추가에 닷넷프레임워크 기능 활성화
3. 설치.

뭐 OS 2008 에는 따로 Windows Installer 를 설치하지 않았던 것 같다. 그리고 마이크로소프트에서
다운로드 받으려 하면 Windows Installer 는 XP와 2003 까지만 지원하는 것 같다.
그 이후버전에는.. 왠지 OS 설치때부터 지원하는 것 같다.
 
저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

SQL Server 와 MySQL 문자열 함수 비교 10 가지

2011.09.21 01:52 DataBase/MS-SQL


출처 : http://www.sqler.com/bColumn/332980

안녕하세요
이호엽 입니다.

 

SQL Server TSQL 문법과 MySQL 문법은 유사한 점이 많습니다.

MySQL 사용자가 SQL Server 사용 혹은 반대로 SQL Server 사용자가 MySQL 사용 

  편하게 접근   있도록 도움이 될까 해서 비교 자료를 만들어 보았습니다.

테이블프로시저트리거, DB 운영에 관련된 스크립트 사용법에 대해서 비교하여 지속적으로

글을 적어볼까 합니다.

 

우선 이번에는 문자열 함수에 대해 10가지 정도 적어 보았습니다.

구분

SQL Server

MySQL

설명

가장 왼쪽 문자에 해당하는 ASCII 코드  출력

함수

ASCII

ASCII

예제

SELECT ASCII('A')

SELECT ASCII('A')

결과

65

65

 

구분

SQL Server

MySQL

설명

문자열에서 지정한 문자의 시작 위치 값을 반환 (왼쪽부터  문자씩 영어한글숫자기호상관없이  글자  1 차지 하며 위치값은 부터 시작)

함수

CHARINDEX

INSTR

예제

SELECT CHARINDEX('크리', '나홀로크리스마스')

SELECT INSTR('나홀로크리스마스', '크리')

결과

4

4

 

구분

SQL Server

MySQL

설명

공백 제거 – SQL Server  선행후행 공백을 제거해주는 LTRIM, RTRIM 혼용 사용해야 하며 MySQL  Trim 이란 함수가 후행 공백을 제거해 줍니다.

LTRIM(str), RTRIM(str) 함수도 따로 존재합니다.

함수

LTRIM() – 선행공백 제거

RTRIM() – 후행공백 제거

LTRIM() – 선행공백 제거

RTRIM() – 후행공백 제거

TRIM() – 양쪽공백 제거

예제

SELECT LTRIM( RTRIM( 이제 빠져버렸어 '))

SELECT TRIM( 이제 빠져 버렸어 ')

결과

 이제 빠져 버렸어

 이제 빠져 버렸어

 

구분

SQL Server

MySQL

설명

문자열의 왼쪽부터 지정된 수만큼 문자를 반환 (사용법 동일)

함수

LEFT

LEFT

예제

SELECT LEFT('메리크리스마스', 2)

SELECT LEFT('메리크리스마스', 2)

결과

메리

메리

 

구분

SQL Server

MySQL

설명

문자열의 오른쪽부터 지정된 수만큼 문자를 반환 (사용법 동일)

함수

RIGHT

RIGHT

예제

SELECT RIGHT('메리크리스마스', 2)

SELECT RIGHT('메리크리스마스', 2)

결과

마스

마스

 

구분

SQL Server

MySQL

설명

시작 위치에서 지정한 문자 길이를 삭제하고 다른 문자 집합을 삽입

함수명만 다르고 인자 순서사용법이 동일합니다.

함수

STUFF

INSERT

예제

SELECT STUFF('sad chritmas' , 1,3, 'happy')

SELECT INSERT('sad chritmas' , 1, 3,'happy')

결과

“happy chirtmas”

“happy chirtmas”

 

구분

SQL Server

MySQL

설명

지정한 사이즈가 문자열 길이 보다 적은 만큼 문자열 왼쪽부터 지정한 문자열을 채우는 처리를 합니다. MySQL 에서는 LPAD 기본함수가 제공 됩니다.

SQL Server 기본 함수가 제공되지 않으므로 필요에 따라 사용자 정의 함수를 만들어 사용해야 합니다.

함수

기본 제공 함수 없음

LPAD

예제

SELECT dbo.LPAD("ke some noise!!", 17, "!!")

SELECT LPAD("ke some noise!!", 17, "!!")

결과

make some noise!!

make some noise!!

 

-- LPAD 사용자 정의 함수 생성

-- WHILE 문을 이용 처리하는 방법도 있으나 len  Replicate 사용법 명시

CREATE FUNCTION dbo.LPAD( @str AS VARCHAR(100), @len AS INT, @padstr ASVARCHAR(100) = '')

 RETURNS VARCHAR(2000)

AS

BEGIN

 RETURN

  CASE WHEN LEN(@str) >= @len THEN SUBSTRING(@str, 1, @len)

   ELSE SUBSTRING(REPLICATE( @padstr, @len), 1, @len - LEN(@str)) + @str END

END

 

구분

SQL Server

MySQL

설명

위에 LPAD  반대로 지정한 문자열을 오른쪽부터 채워줍니다.

MySQL 에서는 RPAD 기본 함수가 제공됩니다.

마찬가지로 SQL Server 에서는 필요에 따라 사용자 정의 함수를 만들어 사용해야 합니다.

함수

기본 제공 함수 없음

RPAD

예제

SELECT dbo.RPAD("make some noise!!", 17, "!!")

SELECT RPAD("make some noise!!", 17, "!!")

결과

make some noise!!

make some noise!!

 

-- RPAD 사용자 정의 함수 생성

-- WHILE 문을 이용 처리하는 방법도 있으나 len  Replicate 사용법 명시

CREATE FUNCTION dbo.RPAD( @str AS VARCHAR(100), @len AS INT, @padstr ASVARCHAR(100) = '')

 RETURNS VARCHAR(2000)

AS

BEGIN

 RETURN

  CASE WHEN LEN(@str) >= @len THEN SUBSTRING(@str, 1, @len)

   ELSE @str + SUBSTRING(REPLICATE( @padstr, @len), 1, @len - LEN(@str)) END

END

 

구분

SQL Server

MySQL

설명

지정한 문자열의 길이를 반환 합니다.

SQL Server 후행 공백을 무시하지만 MySQL CHAR_LENGTH 함수는 공백도

포함하여 길이를 반환합니다.

함수

기본 제공 함수 없음

RPAD

예제

select LEN('abc  ')

SELECT CHAR_LENGTH('abc  ')

결과

3

5

 

구분

SQL Server

MySQL

설명

문자열을 합하여 결과를 출력

함수

연산자를 통해 처리

CONCAT

예제

SELECT 'SQLER Members' + ' Victory!!'

SELECT CONCAT('SQLER Members',' Victory!!')

결과

SQLER Members Victory!!

SQLER Members Victory!!


저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

MSSQL 2005 Server 데이터베이스 복원 (Restore)

2011.04.08 17:21 DataBase/MS-SQL








OK 버튼을 누르면 완료 (Success!!)
저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment