방화벽을 통해 SQL Server와 통신하는 데 필요한 TCP 포트

방화벽을 사용하고 있습니다.
MS-SQL을 외부 에서 접근할때 열어 주어야할 포트가
어떻게 되나요?

MSSQL에서 동적SQL을 구현(OUTPUT파라미터 있을때)

sp_executesql 라는 스토어드 프러시져를 사용하면 됩니다.

밑에는 실제 프로젝트에 사용했던 프로시져입니다...^^

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-


CREATE      procedure p_GetValue (
 @tagName as nvarchar(20),
 @mode as real output,
 @sp as real output,
 @pv as real output,
 @ivp as real output,
 @alm_a as real output,
 @alm_b as real output,
 @alm_c as real output,
 @alm_dbnd as real output,
 @mvp as real output,
 @out as real output,
 @in as real output,
 @ao as real output
)

as


 declare  @tableName                  nvarchar(30)
 declare  @FieldName_mode        nvarchar(30)
 declare  @FieldName_sp                 nvarchar(30)
 declare  @FieldName_pv                 nvarchar(30)
 declare  @FieldName_ivp                nvarchar(30)
 declare  @FieldName_alm_a         nvarchar(30)
 declare  @FieldName_alm_b         nvarchar(30)
 declare  @FieldName_alm_c        nvarchar(30)
 declare  @FieldName_alm_dbnd         nvarchar(30)
 declare  @FieldName_mvp        nvarchar(30)
 declare  @FieldName_out         nvarchar(30)
 declare  @FieldName_in                 nvarchar(30)
 declare  @FieldName_ao                 nvarchar(30)
 declare  @strSQL                 nvarchar(500)
 declare  @strFieldValue                 nvarchar(500)

begin

 set @strSQL = N'select @tableName = tableName, @FieldName_mode = mode, @FieldName_sp = sp, '
 set @strSQL = @strSQL +  N'@FieldName_pv = pv, @FieldName_ivp = ivp, @FieldName_alm_a = alm_a, '
 set @strSQL = @strSQL +  N'@FieldName_alm_b = alm_b, @FieldName_alm_c = alm_c, @FieldName_alm_dbnd = alm_dbnd, '
 set @strSQL = @strSQL + N'@FieldName_mvp = mvp, @FieldName_out = out, @FieldName_in = [in], @FieldName_ao =ao   '

 set @strSQL = @strSQL + N'from tblTagList where TagList_TagName like ''' + @tagName + ''''

 set  @strFieldValue =  N'@tableName nvarchar(30) output, @FieldName_mode nvarchar(30) output, @FieldName_sp nvarchar(30) output, '
 set  @strFieldValue = @strFieldValue + N'@FieldName_pv  nvarchar(30) output,  @FieldName_ivp nvarchar(30) output, @FieldName_alm_a nvarchar(30) output, '
 set  @strFieldValue = @strFieldValue + N'@FieldName_alm_b nvarchar(30) output,   @FieldName_alm_c nvarchar(30) output, @FieldName_alm_dbnd nvarchar(30) output, '
 set  @strFieldValue = @strFieldValue + N'@FieldName_mvp nvarchar(30) output,@FieldName_out nvarchar(30) output, @FieldName_in nvarchar(30) output,  @FieldName_ao nvarchar(30) output '

 
 exec sp_executesql @strSQL, @strFieldValue,  
               @tableName output, @FieldName_mode output, @FieldName_sp output, @FieldName_pv output, @FieldName_ivp output, @FieldName_alm_a output,
               @FieldName_alm_b output, @FieldName_alm_c output, @FieldName_alm_dbnd output, @FieldName_mvp output,  @FieldName_out output,  
               @FieldName_in output,  @FieldName_ao output


 if @tableName is null  return 0

  set @strSQL =  N'select '

 if @FieldName_mode IS Null
     set @strSQL = @strSQL + N' @mode=null'
 else
     set @strSQL = @strSQL + N' @mode= ' + @FieldName_mode

 if @FieldName_sp IS Null
     set @strSQL = @strSQL + N', @sp=null'
 else
     set @strSQL =  @strSQL + N', @sp=' + @FieldName_sp

 if @FieldName_pv IS Null
    set @strSQL =  @strSQL + N', @pv=null'
 else
    set @strSQL =  @strSQL + N', @pv=' + @FieldName_pv

 if @FieldName_ivp IS Null
    set @strSQL = @strSQL +  N', @ivp=null'
 else
    set @strSQL =  @strSQL + N', @ivp=' + @FieldName_ivp

 if @FieldName_alm_a IS  Null
   set @strSQL =  @strSQL + N', @alm_a=null'
 else
    set @strSQL =  @strSQL + N', @alm_a=' + @FieldName_alm_a

 if @FieldName_alm_b IS  Null
    set @strSQL =  @strSQL + N', @alm_b=null'
 else
    set @strSQL =  @strSQL + N', @alm_b=' + @FieldName_alm_b
 
 if @FieldName_alm_c IS  Null
    set @strSQL =  @strSQL + N', @alm_c=null'
 else
    set @strSQL =  @strSQL + N', @alm_c=' + @FieldName_alm_c

 if @FieldName_alm_dbnd  IS  Null
    set @strSQL =  @strSQL + N', @alm_dbnd=null'
 else
    set @strSQL =  @strSQL + N', @alm_dbnd=' +@FieldName_alm_dbnd

 if @FieldName_mvp IS  Null
    set @strSQL =  @strSQL + N', @mvp=null'
 else
    set @strSQL =  @strSQL + N', @mvp=' + @FieldName_mvp

 if @FieldName_out IS  Null
    set @strSQL =  @strSQL + N', @out =null'
 else
    set @strSQL =  @strSQL + N', @out =' + @FieldName_out

 if @FieldName_in IS  Null
     set @strSQL =  @strSQL + N', @in =null'
 else
     set @strSQL =  @strSQL + N', @in =' + @FieldName_in

 if @FieldName_ao IS  Null
     set @strSQL =  @strSQL + N', @ao =null'
 else
     set @strSQL =  @strSQL + N', @ao =' + @FieldName_ao

   set @strSQL = @strSQL + N' from ' + @tableName

 print @strSQL


 exec sp_executesql @strSQL,
              N'@mode real output, @sp real output, @pv real output, @ivp real output, @alm_a real output,  @alm_b real output,  @alm_c real output, @alm_dbnd real output,  @mvp real output,  @out real output,  @in real output,  @ao real output',
              @mode output, @sp output, @pv output, @ivp output, @alm_a output,  @alm_b output,  @alm_c output, @alm_dbnd output,  @mvp output,  @out output,  @in output,  @ao output
end

MSSQL에서 동적SQL을 구현(OUTPUT파라미터 없을때)

exec 라는 명령어를 사용하면 됩니다.

아래는 간단한 예제 입니다.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



CREATE    procedure p_GetFieldValue
(
       @table   NVARCHAR(100),
       @value1 NVARCHAR(200),
       @value2 NVARCHAR(200) = null,
       @value3 NVARCHAR(200) = null,
       @value4 NVARCHAR(200) = null
)
as
begin

       declare @fields varchar(500)        
       declare @sql varchar(500)

       set @fields = @value1 + N' as a'

       if ( @value2 is not null        )
       begin
               set @fields = @fields + N', ' + @value2 + N' as b'
       end

       if ( @value3 is not null        )
       begin
               set @fields = @fields + N', ' + @value3 + N' as c'
       end

       if ( @value4 is not null        )
       begin
               set @fields = @fields + N', ' + @value4 + N' as d'
       end

               
       set @sql =  N'SELECT ' + @fields + N' FROM ' + @table


       exec ( @sql )

end

isql.exe를 이용한 sql문서를 실행해 보자

sql을 설치한 폴더에서 80\Tools\Binn에 보면..
( 저의 경우는 C:\Program Files\Microsoft SQL Server\80\Tools\Binn 더 군요)
isql.exe라는 파일이 있습니다.

이 파일을 이용한면 sql문서를 실행 할 수가 있는데요.

sql문서는 일반 text 파일이어야 하구요.
use문을 통해서 작업할
DB를 정해주어야 정상 작동 합니다.

아래는 배치파일을 이용해 이번 프로젝트때 사용한
파일입니다.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
[CODE type=dos]
@echo off
echo.
echo Genex Application Update Autobatch File
echo.
echo by newtype
echo write 2003/7/18
echo.
echo.
echo.
echo.
echo Start Update File Coping....
echo.
copy MobileManager.exe .\Mobilemanager\Mobilemanager\Mobilemanager.exe /Y copy DLLAlarm.dll .\Presentation\DLLAlarm.dll /Y
copy Presentation.exe .\Presentation\Presentation.exe /Y
echo.
echo Start DB Update....
echo.
isql -U sa -P sa -i update.sql > result.txt
echo.
echo Delete Template file...
echo.
del MobileManager.exe
del DLLAlarm.dll
del Presentation.exe
del isql.exe
del update.sql
echo.
echo Complite Update!.
echo.
echo Please Delete this "Update.bat" in this folder
echo.
pause
[/CODE]