나의 보딩패스~

▼ 예약하기

https://mars.nasa.gov/participate/send-your-name/mars2020

-- 1) 테이블 생성 및 데이터 복사
SELECT * INTO 생성할_테이블명
FROM 원본_테이블명
WHERE 복사할_데이터_조건 
--└ 응용: 1=2 조건으로 실행하면 테이블 스키마만 복사된 새 테이블이 생성된다.

-- 2) 새 테이블을 이미 생성한 경우 데이터만 복사
INSERT INTO 생성할_테이블명
SELECT *
FROM 원본_테이블명
WHERE 복사할_데이터_조건 

SELECT실행시 아래 에러 발생.

equal to 작업에서의 "Korean_Wansung_CI_AS"과(와) "Korean_Wansung_CS_AS" 간의 데이터 정렬 충돌을 해결할 수 없습니다.

원인:) 비교하는 두 컬럼의 데이터 정렬값이 달라서 발생.

해결1:) 비교컬럼에 강제캐스팅해서 비교 COLLATE

  SELECT A.UsrID, A.UsrName, B.UsrGroupCd
    FROM dbo.USR_USER A, dbo.USR_GROUP B    
  WHERE A.UsrIDCOLLATE Korean_Wansung_CI_AS 
                      = B.userid  COLLATE Korean_Wansung_CI_AS

해결2:) 데이터 정렬값을 일치 시킨다. ALTER

-- DB별 디폴트Collation조회
SELECT NAME, COLLATION_NAME, *
  FROM sys.databases

-- 컬럼의 Collation 설정값 조회
SELECT * 
  FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_NAME = 'TABLE_NAME'

-- 컬럼의 Collation설정값 변경
ALTER TABLE FORMS_USERGROUP_RANGE_TBL
ALTER COLUMN userid varchar(30)
COLLATE Korean_Wansung_CS_AS NOT NULL

Korean_Wansung_CS_AS vs Korean_Wansung_CI_AS

Korean_Wansung_CS_AS(대소문자 구분O): 양쪽 비교항에 UPPER() 사용하면 구분안함.
Korean_Wansung_CI_AS(대소문자 구분X)

▼TRY..CATCH 

출처&상세: TRY...CATCH(Transact-SQL)

-- TRY..CATCH 사용예
BEGIN TRY  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
        -- 프로시저 파라미터 사용 가능
 	DECLARE @ParamInfoStr varchar(1000)
	= '@ResultDate='+CASE WHEN @ResultDate IS NULL THEN 'null' ELSE CONVERT(varchar(19), @ResultDate, 120) END
		+', @ModelID='++CASE WHEN @ModelID IS NULL THEN 'null' ELSE CAST(@ModelID as varchar) END

	-- error retrieval .  
	SELECT  
		 ERROR_NUMBER() AS ErrorNumber  
		,ERROR_SEVERITY() AS ErrorSeverity  
		,ERROR_STATE() AS ErrorState  
		,ERROR_PROCEDURE() AS ErrorProcedure  
		,ERROR_LINE() AS ErrorLine  
		,ERROR_MESSAGE() AS ErrorMessage; 

	--출력
	PRINT
	   'ERROR_NUMBER = ' + CAST(ERROR_NUMBER() AS VARCHAR)
	 +'ERROR_SEVERITY = ' + CAST(ERROR_SEVERITY() AS VARCHAR)
	 +'ERROR_STATE = ' + CAST(ERROR_STATE() AS VARCHAR)
	 +'ERROR_PROCEDURE = ' + ERROR_PROCEDURE()
	 +'ERROR_LINE = ' + CAST(ERROR_LINE() AS VARCHAR)
	 +'ERROR_MESSAGE = ' + ERROR_MESSAGE();
END

포트상태 감시자 만드는 중 간단히 Listen포트정보와 Tcp연결포트 정보를 가져오는 API를 발견하여 정리해둔다.
단, IPv4정보만 출력된다.
netstat 명령어와 같이 모든 정보를 얻고 싶으면 아래 링크의 샘플코드를 참조할 것▼
C# Sample to list all the active TCP and UDP connections using Windows Form appl | 2016-01-20

using System.Net.NetworkInformation; //IPAddress, IPGlobalProperties
using System.Net;//IPEndPoint

static void Main(string[] args)
{
    _Print("IPAddress.Any = " + IPAddress.Any.ToString());
    _Print("IPAddress.None = " + IPAddress.None.ToString());
    _Print("IPAddress.Broadcast = " + IPAddress.Broadcast.ToString());
    _Print("IPAddress.Loopback = " + IPAddress.Loopback.ToString());

    _Print("IPAddress.IPv6Any = " + IPAddress.IPv6Any.ToString());
    _Print("IPAddress.IPv6Loopback = " + IPAddress.IPv6Loopback.ToString());
    _Print("IPAddress.IPv6None = " + IPAddress.IPv6None.ToString());

    Console.WriteLine("\r\npress any key to exit..");
    Console.ReadKey();

    PortList();

    Console.WriteLine("\r\npress any key to exit..");
    Console.ReadKey();
}
public static void PortList()
{
    IPGlobalProperties ipProperties = IPGlobalProperties.GetIPGlobalProperties();
    IPEndPoint[] ipEndPoints = ipProperties.GetActiveTcpListeners();
    _Print("▼ GetActiveTcpListeners ----------------");
    foreach (IPEndPoint endPoint in ipEndPoints)
    {
        _Print(string.Format("AddressFamily={0}, IP={1}, PORT={2}", endPoint.AddressFamily.ToString(), endPoint.Address.ToString(), endPoint.Port));
    }

    TcpConnectionInformation[] tcpConnInfoArray = ipProperties.GetActiveTcpConnections();

    _Print("▼ GetActiveTcpConnections ----------------");
    _Print("Local\t\t\t ┃Remote\t\t\t ┃State");
    foreach (TcpConnectionInformation tcpConnInfo in tcpConnInfoArray)
    {
        TcpState tcpState = tcpConnInfo.State;
        IPEndPoint localEndPoint = tcpConnInfo.LocalEndPoint;
        IPEndPoint remotrEndPoint = tcpConnInfo.RemoteEndPoint;

        _Print(string.Format("{0}:{1}\t ┃{2}:{3}\t ┃{4}",
            tcpConnInfo.LocalEndPoint.Address, tcpConnInfo.LocalEndPoint.Port,
            tcpConnInfo.RemoteEndPoint.Address, tcpConnInfo.RemoteEndPoint.Port,
            tcpConnInfo.State.ToString()));
    }
}

static public void _Print(string aMsg)
{
    Console.WriteLine(aMsg);
    System.Diagnostics.Debug.WriteLine(aMsg);
}

 

IPAddress.Any = 0.0.0.0
IPAddress.None = 255.255.255.255
IPAddress.Broadcast = 255.255.255.255
IPAddress.Loopback = 127.0.0.1
IPAddress.IPv6Any = ::
IPAddress.IPv6Loopback = ::1
IPAddress.IPv6None = ::

▼ GetActiveTcpListeners ----------------
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=80
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=135
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=623
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=1025
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=5357
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=10004
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=31234
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=55920
AddressFamily=InterNetwork, IP=14.9.217.129, PORT=139
AddressFamily=InterNetwork, IP=127.0.0.1, PORT=1065
AddressFamily=InterNetwork, IP=127.0.0.1, PORT=16105
AddressFamily=InterNetwork, IP=127.0.0.1, PORT=55501

▼ GetActiveTcpConnections ----------------
Local		 	 ┃Remote				 ┃State
14.9.217.129:1045	 ┃12.4.11.221:9992	 	┃Established
14.9.217.129:1049	 ┃12.4.11.221:9991	 	┃Established
14.9.217.129:2029	 ┃64.233.188.188:5228	 ┃Established
14.9.217.129:23449	 ┃211.115.106.205:80	 ┃CloseWait
14.9.217.129:24345	 ┃104.17.129.217:443	 ┃Established
14.9.217.129:24675	 ┃121.156.118.102:80	 ┃CloseWait
127.0.0.1:24836	 ┃127.0.0.1:9229		 ┃SynSent
127.0.0.1:24837	 ┃127.0.0.1:9229		 ┃SynSent

해당포트의 Process ID값까지 필요하다면 - 예를 들어 kill process로 포트를 닫아야할 때 - 아래 샘플 소스 사용가능

.NET Framework 4.0 이상 / iphlpapi.dll 활용

C# Sample to list all the active TCP and UDP connections using Windows Form appl