SQL Server에서 백업 후 다른 인스턴스로 복구한 데이터베이스에 계정을 생성시 발생하는 오류...
해당 데이터베이스 에서 아래처럼 실행하면, 해결됨
exec sp_change_users_login 'UPDATE_ONE', '사용자명', '로그인명'
ex)
exec sp_change_users_login 'UPDATE_ONE', 'uae', 'uae'
간단해서 좋으네...
개발을 하다 보면 1차원 배열을 받아서 테이블 형태로 가공한 뒤 처리해야 할 경우가 발생한다.
이를 처리하는 방법에 대한 정리.
Method 1: Dynamic SQL
CREATE PROC dbo.GetOrderList1
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(600)
SET @SQL =
'SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderID IN (' + @OrderList + ')'
EXEC(@SQL)
END
GO
EXEC dbo.GetOrderList1 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'
GO
Method 2: Pass...
Method 3: Parsing the comma separated values into a table variable and joining the table variable to main table
CREATE PROC dbo.GetOrderList3
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TempList table
(
OrderID int
)
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @TempList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
@TempList t
ON o.OrderID = t.OrderID
END
GO
EXEC dbo.GetOrderList3 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'
GO
Method 4: Using XML
CREATE PROC dbo.GetOrderList4
(
@OrderList varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @DocHandle int
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @OrderList
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
OPENXML (@DocHandle, '/ROOT/Ord',1) WITH (OrderID int) AS x
ON o.OrderID = x.OrderID
EXEC sp_xml_removedocument @DocHandle
END
GO
EXEC dbo.GetOrderList4 '
<ROOT>
<Ord OrderID = "10248"/> <Ord OrderID = "10252"/>
<Ord OrderID = "10256"/> <Ord OrderID = "10261"/>
<Ord OrderID = "10262"/> <Ord OrderID = "10263"/>
<Ord OrderID = "10264"/> <Ord OrderID = "10265"/>
<Ord OrderID = "10300"/> <Ord OrderID = "10311"/>
<Ord OrderID = "11068"/> <Ord OrderID = "11069"/>
<Ord OrderID = "11070"/> <Ord OrderID = "11071"/>
<Ord OrderID = "11072"/> <Ord OrderID = "11073"/>
<Ord OrderID = "11074"/> <Ord OrderID = "11075"/>
<Ord OrderID = "11076"/> <Ord OrderID = "11077"/>
</ROOT>'
GO
Method 5: Using a table of numbers or pivot table, to parse the comma separated list
--Create a table called Numbers
CREATE TABLE dbo.Numbers
(
Number int PRIMARY KEY CLUSTERED
)
GO
--Insert 8000 numbers into this table (from 1 to 8000)
SET NOCOUNT ON
GO
DECLARE @CTR int
SET @CTR = 1
WHILE @CTR < 8001
BEGIN
INSERT INTO dbo.Numbers (Number) VALUES (@CTR)
SET @CTR = @CTR + 1
END
GO
--The above two steps are to be run only once. The following stored procedure uses the number table.
CREATE PROC dbo.GetOrderList5
(
@OrderList varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
(
SELECT LTRIM(RTRIM(SUBSTRING(OrderID, number+1, CHARINDEX(',', OrderID, number+1)-number - 1))) AS OrderID
FROM
(
SELECT ',' + @OrderList + ',' AS OrderID
) AS InnerQuery
JOIN
Numbers n
ON
n.Number < LEN(InnerQuery.OrderID)
WHERE SUBSTRING(OrderID, number, 1) = ','
) as Derived
ON o.OrderID = Derived.OrderID
END
GO
EXEC dbo.GetOrderList5 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'
GO
Method 6: Using a general purpose User Defined Function (UDF) to parse the comma separated OrderIDs
--The following is a general purpose UDF to split comma separated lists into individual items.
--Consider an additional input parameter for the delimiter, so that you can use any delimiter you like.
CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END
GO
CREATE PROC dbo.GetOrderList6
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
dbo.SplitOrderIDs(@OrderList) AS s
ON
o.OrderID = s.OrderID
END
GO
EXEC dbo.GetOrderList6 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'
GO
Best Solution (최상의 성능과 간단한 구현)
: 천재다...
DECLARE @textXML XML
DECLARE @data NVARCHAR(MAX),
@delimiter NVARCHAR(5)
SELECT @data = 'A,B,C',
@delimiter = ','
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML)
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T(split)
참고 URL
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://stackoverflow.com/questions/1922191/passing-array-as-parameter-to-sql-2005-stored-procedure
SQL Server로 Linked Query를 사용하여 작업할 경우가 많다.
Linked Query 자체를 생성하는 것이 힘겨운 경우도 있고... 우여곡절이 많다. ㅠㅠ
이번 기록은 Linked Query를 일단 생성한 후의 활용에 대해서 정리한다.
Query
: SELECT * FROM <Linked Server Name>..<Owner Name>.<Table Name> WHERE ~~;
-- 성능 않좋고... 어딘가에 써먹을 데가 별로 없다.
-- 쿼리 작성이 간단하다.
-- 4-Part 라고 한다.
-- INSERT, UPDATE, DELETE 문장을 보통 쿼리문과 비슷하게 사용한다.
: SELECT * FROM OPENQUERY(<Linked Server Name>, 'SELECT * FROM <Table Name> WHERE ~~');
-- 성능이 나쁘지 않고, 자주 사용.
-- 쿼리를 문자열로 조합하기 귀찮음.
-- OPENQUERY 라고 한다.
-- INSERT의 경우 OPENQUERY 내의 SELECT 문에 WHERE 1<>1 로 UPDATE, DELETE 문은 검색 제한자를 정확히 써서 인덱스를 잘 사용하면 성능이 봐줄만 하다.
-- OPENQUERY 내의 문자열은 완성된 형태의 문자열만 가능하며, 변수는 받을 수 없다. <-- 이게 치명적인 귀찮음...
Procedure 호출
: Oracle Procedure
execute ('call <OracleSchema>.<OracleProcedureName>()') at <Linked Server Name>
: Oracle Package
SELECT *
FROM OPENQUERY(ls_name , '{CALL user1.upk_test.up_test_inpkg(1111,''222'', {resultSet 1, po_RecordSet }}');
"sqlServer에 linked server로 oracle에 연결하고, oracle의 sp를 call한 후, 응답코드에 따라
이후 작업 진행"위와 같은 방법보다는 application에서 control하는 것이 좋을 듯 하지만, 어쩔 수 없이 이렇게
해야만 하는 경우가 있어, 코드 부스러기 주워담아 아래와 같이 해결했다.return값을 table형식이 아닌 number형식으로 받는 방법을 몰라서 응답코드를 table형식으로 return
하도록 처리하였다.(이에 대해서 좀 더 효율적인 방법에 대해서 댓글 부탁합니다.)CASE1) oracle에서 package를 작성
1. oracle에서 패키지 작성
----------------------------------------------------------------
create or replace package upk_test
as
type tbltype is table of number(10)
index by pls_integer;procedure up_test_inpkg(
pi_nParam1 IN NUMBER,
pi_szParam1 IN VARCHAR2,po_RecordSet OUT TblType
);
end upk_test;
/create or replace package body upk_test
as
procedure up_test_inpkg(
pi_nParam1 IN NUMBER,
pi_szParam1 IN VARCHAR2,po_RecordSet OUT TblType
) as
begin/**
business logic 구현
......
**/
--넘겨줄 처리결과 코드 세팅
po_RecordSet(1) := v_nResult;end up_test_inpkg;
end upk_test;
----------------------------------------------------------------2. sqlserver에서 openquery를 통해 query
----------------------------------------------------------------
SELECT *
FROM OPENQUERY(ls_name , '{CALL user1.upk_test.up_test_inpkg(1111,''222'', {resultSet 1, po_RecordSet }}');
----------------------------------------------------------------
CASE2) oracle에서 package에 type선언만 하고 해당 type을 sp에서 참조
----------------------------------------------------------------
create or replace package upk_types
as
type numtbltype is table of number(10)
index by pls_integer;
end upk_types;
/
create or replace procedure up_test(
pi_nParam1 IN NUMBER,
pi_szParam1 IN VARCHAR2,po_RecordSet OUT upk_type.numtbltype
)
as
begin
/**
business logic 구현
......
**/
--넘겨줄 처리결과 코드 세팅
po_RecordSet(1) := v_nResult;
end;----------------------------------------------------------------
관련 link : http://dialup.egloos.com/927560
: PostgreSQL Procedure
SELECT * FROM OPENQUERY(<Linked Server Name>, 'SELECT * FROM <Procedure Name>');
SELECT TOP 9 name, rand() RV FROM sys.objects;
name RV
-------------------------------------
예전에 찾은 자료인데... 가끔 써먹을 때도 있다.
경험상 결과셋을 굳이 Excel로 저장할 필요는 없다. BCP를 이용하여 CSV파일로 저장하면 되는데...
하여간 찾아놓은 고생이 아까워 기록은 해 두는 것이 좋을 듯...
사용할 수 있는 환경이 MSSQL의 보안 권고 사항과 충돌한다.
1. MSSQL 노출 영역 구성대상 / 기능에 대한 노출 영역 구성 / OLE 자동화 사용 : True
2. MSSQL 노출 영역 구성대상 / 기능에 대한 노출 영역 구성 / xp_cmdshell 사용 : True
/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).
This stored procedure can be used to insert the result set of the
particular select statement into Excel file (c:ImportToExcel.xls,
by default).
You can pass the server name, user name, user password, the select
statement to execute, and the file name to store the results set,
as in the example below:
EXEC ExportToExcel @server = '.',
@uname = 'sa',
@QueryText = 'SELECT au_fname FROM pubs..authors',
@filename = 'c:ImportToExcel.xls'
*/
IF OBJECT_ID('ExportToExcel') IS NOT NULL DROP PROC ExportToExcel
GO
CREATE PROCEDURE ExportToExcel (
@server sysname = null,
@uname sysname = null,
@pwd sysname = null,
@QueryText varchar(200) = null,
@filename varchar(200) = 'c:ImportToExcel.xls'
)
AS
DECLARE @SQLServer int,
@QueryResults int,
@CurrentResultSet int,
@object int,
@WorkBooks int,
@WorkBook int,
@Range int,
@hr int,
@Columns int,
@Rows int,
@indColumn int,
@indRow int,
@off_Column int,
@off_Row int,
@code_str varchar(100),
@result_str varchar(255)
IF @QueryText IS NULL
BEGIN
PRINT 'Set the query string'
RETURN
END
-- Sets the server to the local server
IF @server IS NULL SELECT @server = @@servername
-- Sets the username to the current user name
IF @uname IS NULL SELECT @uname = SYSTEM_USER
SET NOCOUNT ON
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT
IF @hr <> 0
BEGIN
PRINT 'error create SQLDMO.SQLServer'
RETURN
END
-- Connect to the SQL Server
IF @pwd IS NULL
BEGIN
EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname
IF @hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
ELSE
BEGIN
EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname, @pwd
IF @hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
SELECT @result_str = 'ExecuteWithResults("' + @QueryText + '")'
EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUT
IF @hr <> 0
BEGIN
PRINT 'error with method ExecuteWithResults'
RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT
IF @hr <> 0
BEGIN
PRINT 'error get CurrentResultSet'
RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT
IF @hr <> 0
BEGIN
PRINT 'error get Columns'
RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT
IF @hr <> 0
BEGIN
PRINT 'error get Rows'
RETURN
END
EXEC @hr = sp_OACreate 'Excel.Application', @object OUT
IF @hr <> 0
BEGIN
PRINT 'error create Excel.Application'
RETURN
END
EXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT
IF @hr <> 0
BEGIN
PRINT 'error create WorkBooks'
RETURN
END
EXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT
IF @hr <> 0
BEGIN
PRINT 'error with method Add'
RETURN
END
EXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT
IF @hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
SELECT @indRow = 1
SELECT @off_Row = 0
SELECT @off_Column = 1
WHILE (@indRow <= @Rows)
BEGIN
SELECT @indColumn = 1
WHILE (@indColumn <= @Columns)
BEGIN
EXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT, @indRow, @indColumn
IF @hr <> 0
BEGIN
PRINT 'error get GetColumnString'
RETURN
END
EXEC @hr = sp_OASetProperty @Range, 'Value', @result_str
IF @hr <> 0
BEGIN
PRINT 'error set Value'
RETURN
END
EXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column
IF @hr <> 0
BEGIN
PRINT 'error get Offset'
RETURN
END
SELECT @indColumn = @indColumn + 1
END
SELECT @indRow = @indRow + 1
SELECT @code_str = 'Range("A' + LTRIM(str(@indRow)) + '")'
EXEC @hr = sp_OAGetProperty @object, @code_str, @Range OUT
IF @hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
END
SELECT @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output'
EXEC(@result_str)
SELECT @result_str = 'SaveAs("' + @filename + '")'
EXEC @hr = sp_OAMethod @WorkBook, @result_str
IF @hr <> 0
BEGIN
PRINT 'error with method SaveAs'
RETURN
END
EXEC @hr = sp_OAMethod @WorkBook, 'Close'
IF @hr <> 0
BEGIN
PRINT 'error with method Close'
RETURN
END
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
PRINT 'error destroy Excel.Application'
RETURN
END
EXEC @hr = sp_OADestroy @SQLServer
IF @hr <> 0
BEGIN
PRINT 'error destroy SQLDMO.SQLServer'
RETURN
END
GO
--Version: SQL Server 7.0/2000
--Created by: Alexander Chigrik
--http://www.MSSQLCity.com/ - all about MS SQL
--(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).
--
--This stored procedure can be used to insert the result set of the particular
--select statement into Excel file (c:ImportToExcel.xls, by default).
--You can pass the server name, user name, user password, the select statement
--to execute, and the file name to store the results set, as in the example below:
EXEC ExportToExcel @server = NULL,
@uname = 'xxxxxx',
@pwd = 'xxxxxxx',
@QueryText = 'SELECT * FROM dbXXX..tbXXX',
@filename = 'c:\xxxxx.xls'
