informix dateadd

informix 11.5模擬sql server dateadd, 但傳入及傳出皆為國曆日期數字

CREATE FUNCTION ifx_f_dateadd
(in_type varchar(5),in_date integer, in_diff integer )
RETURNING integer ;
DEFINE p_date date;
DEFINE p_day integer;
--轉為西元日期
SELECT (((substr((in_date+19110000)::char(8),1,4))||'-'||
               (substr((in_date+19110000)::char(8),5,2))||'-'||
               (substr((in_date+19110000)::char(8),7,2)))::date)
  INTO p_date
  FROM empty;
--改成以下方式來轉換
--因為上述方式會隨著DB版本或onconfig中日期格式指定而變,可能會有錯誤.

SELECT (TO_DATE((in_date+19110000)::char(8),'%Y%m%d'))
  INTO p_date  FROM empty;

IF UPPER(in_type) IN ('DAY','D') THEN
   SELECT p_date  + in_diff into p_date from empty;
ELSE IF UPPER(in_type) in ('MONTH','M') THEN
   SELECT p_date  + in_diff units MONTH into p_date from empty;
  --改成以下方式,因為1011031 取下月時會有The result of a datetime computation is out of range.的錯誤,
  --以下方法閏年也適用
 SELECT add_months(p_date,in_diff ) into p_date from empty;
ELSE IF UPPER(in_type) IN ('YEAR','Y' ) THEN
   SELECT p_date + in_diff units YEAR  into p_date from empty;
  --以下方法閏年也適用 

SELECT add_months(p_date,in_diff*12) into p_date from empty;

END IF; END IF; END IF;  --這一串END IF; 搞了我一個小時才知道要這樣寫....

  SELECT ((substr(p_date,1,4)::integer)-1911)||
           (substr(p_date,6,2))||(substr(p_date,9,2))
     INTO p_day
     FROM empty;
RETURN p_day;

SELECT TO_CHAR(p_date ,'%Y%m%d')::INTEGER - 19110000
  INTO p_day
  FROM empty;
END FUNCTION; 


測試,

CREATE TABLE empty (col1 INT);
INSERT into empty VALUES(0);

select ifx_f_dateadd('y',1010814,-1)  FROM empty;

回傳1000814

select sr_f_dateadd('m',1011031,1)  FROM empty

回傳1011130

select sr_f_dateadd('y',1010229,-1)  FROM empty

回傳1000228


ps.2012/08/14寫第一版,3個月後,知識長進,大改一番.

sql cluster firewall應開哪些port?

人客的資安開始規定,所有主機都要開啟Windows 防火牆,所有輸入都要以例外的方式來設定.

雖然網管已有一層防火牆,但敵人就在你身邊,自已人也不可靠啊...

主機間有二台主機做sql server cluster (db server name = sql-cluster) , 在開啟了防火牆後, 另一台主機無法以sql-cluster進行連線.
但使用IP連線卻是可以的

試了半天,除了在二台sql cluster db主機要開1434,1433的port之外
還得要加開137,138,139,445 四個port  才能使用server name 來連線

這些port在例外中已有定義,為檔案及印表機共用,只要勾選即可

另外,應在變更領域中加入IP自訂清單比較保險.

IIS Worker Process問題

在一台windows 2003 R2標準版環境,建立了一個.net 4.0的網站.
結果每隔一段期間, 就會出以下的錯誤,

szAppName : w3wp.exe     szAppVer : 6.0.3790.3959     szModName : unknown    
szModVer : 0.0.0.0     offset : 62cbb9ce    

C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\1\WERdaef.dir00\w3wp.exe.mdmp
C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\1\WERdaef.dir00\appcompat.txt


1.重啟W3SVC (Net Stop W3SVC and Net Start W3SVC)
2.清除C:Documents and Settings\Administrator\Local Settings\Temp
3.重啟iis (iisreset)

iisreset後,一切又都正常

後來建立一個新的應用程式集區,將網站原來使用的DefaultAppPool改成新的集區後,目前看來正常了...

 我想應該是預設應用程式集區原本是.net 2.0網站使用, 而我的網站是.net 4.0, 二個錯亂了吧??

Informix catalogs 讀取資料表及欄位資訊

 --資料表
SELECT * FROM systables WHERE tabid > 99
;
--資料表及欄位
SELECT * FROM systables a INNER JOIN syscolumns b
    ON a.tabid = b.tabid
WHERE a.tabid > 99
;
--資料表欄位及類型 (參考此dbform此篇討論 )
SELECT t.tabname,c.colno, c.colname[1,20] column,                          
    CASE coltype                                                              
        WHEN 0 THEN 'char(' || TRIM(CAST (c.collength AS CHAR(5))) || ')'     
        WHEN 1 THEN 'smallint'                                                
        WHEN 2 THEN 'integer'                                                 
        WHEN 3 THEN 'float'                                                   
        WHEN 4 THEN 'smallfloat'                                              
        WHEN 5 THEN 'decimal(' ||                                             
            TRIM(CAST(TRUNC(c.collength/256) AS VARCHAR(8)) || ',' ||         
            CAST(c.collength - TRUNC(c.collength/256)*256 AS VARCHAR(8))) || ')'
        WHEN 6 THEN 'serial'                                                  
        WHEN 7 THEN 'date'                                                    
        WHEN 8 THEN 'money(' ||                                               
            TRIM(CAST(TRUNC(c.collength/256) AS VARCHAR(8)) || ',' ||         
            CAST(c.collength - TRUNC(c.collength/256)*256 AS VARCHAR(8))) || ')'
        WHEN 9 THEN 'null'                                                    
        WHEN 10 THEN 'datetime'                                               
        WHEN 11 THEN 'byte'                                                   
        WHEN 12 THEN 'text'                                                   
        WHEN 13 THEN 'varchar(' || TRIM(CAST(c.collength AS CHAR(5))) || ')'  
        WHEN 14 THEN 'interval'                                               
        WHEN 15 THEN 'nchar(' || TRIM(CAST(c.collength AS CHAR(5))) || ')'    
        WHEN 16 THEN 'nvarchar(' || TRIM(CAST(c.collength AS CHAR(5))) || ')' 
        WHEN 17 THEN 'int8'                                                   
        WHEN 18 THEN 'serial8'                                                
        WHEN 19 THEN 'set'                                                    
        WHEN 20 THEN 'multiset'                                               
        WHEN 21 THEN 'list'                                                   
        WHEN 22 THEN 'row'                                                    
        WHEN 23 THEN 'collection'                                             
        WHEN 24 THEN 'rowdef'        
        WHEN 40 THEN 'lvarchar'                                                
        WHEN 256 THEN 'char(' || TRIM(CAST(c.collength AS CHAR(5))) ||        
            ') not null'                                                      
        WHEN 257 THEN 'smallint not null'                                     
        WHEN 258 THEN 'integer not null'                                      
        WHEN 259 THEN 'float not null'                                        
        WHEN 260 THEN 'smallfloat not null'                                   
        WHEN 261 THEN 'decimal('||                                            
            TRIM(CAST(TRUNC(c.collength/256) AS VARCHAR(8)) || ',' ||         
            CAST(c.collength - TRUNC(c.collength/256)*256 AS VARCHAR(8))) ||  
            ') not null'                                                      
        WHEN 262 THEN 'serial not null'                                       
        WHEN 263 THEN 'date not null'                                         
        WHEN 264 THEN 'money(' ||                                             
            TRIM(CAST(TRUNC(c.collength/256) AS VARCHAR(8)) || ',' ||         
            CAST(c.collength - TRUNC(c.collength/256)*256 AS VARCHAR(8))) ||  
            ') not null'                                                      
        WHEN 265 THEN 'null not null'                                         
        WHEN 266 THEN 'datetime not null'                                     
        WHEN 267 THEN 'byte not null'                                         
        WHEN 268 THEN 'text not null'                                         
        WHEN 269 THEN 'varchar(' || TRIM(CAST(c.collength AS CHAR(5))) ||     
            ') not null'                                                      
        WHEN 270 THEN 'interval not null'                                     
        WHEN 271 THEN 'nchar(' || TRIM(CAST(c.collength AS CHAR(5))) ||       
            ') not null'                                                      
        WHEN 272 THEN 'nvarchar(' || TRIM(CAST(c.collength AS CHAR(5))) ||    
            ') not null'                                                      
        WHEN 273 THEN 'int8 not null'                                         
        WHEN 274 THEN 'serial8 not null'                                      
        WHEN 275 THEN 'set not null'                                          
        WHEN 276 THEN 'multiset not null'                                     
        WHEN 277 THEN 'list not null'                                         
        WHEN 278 THEN 'row not null'                                          
        WHEN 279 THEN 'collection not null'                                   
        WHEN 280 THEN 'rowdef not null'                                       
        ELSE CAST(coltype AS CHAR(10))                                        
    END datatype                                                              
FROM systables t INNER JOIN syscolumns c
  ON t.tabid = c.tabid                                        
WHERE t.tabid > 99                                    
ORDER BY 1,2                 
;

Excel openrowset 奇怪的問題(再續)

一段寫在stored procedure 的excel openrowset 今天又出問題了.

無法初始化連結伺服器'(null)'的OLE DB提供者 'Microsoft.Jet.OLEDB.4.0'的資料來源物件

這個問題困擾了好久, 有時重啟sql server 就好, 有時得要重開機.

很困擾.

查到這篇文章

OPENROWSET (T-SQL) – Sometimes You Have to Stop and Smell the Roses
講到權限問題, SQL Server service accounts必需要有TEMP directory的讀寫權限.

查看了一下, SQL SERVER是使用本機帳(local system) 號啟動的.

於是乎,再參考一下這篇文章Location of user account temp dir for service accounts

找到使用者對應

• Local Service (NT AUTHORITY\LOCAL SERVICE)
• Network Service (NT AUTHORITY\NETWORK SERVICE)
• Local System (NT AUTHORITY\SYSTEM)

接著 在C:\Users\user.name\AppData\Local\Temp 安全性的檢查讀寫權限是否都已開放.

相關的討論在napierm 的回文中,也有說明.

So, the fix is to do one of the following:

 - Change the SQL Server's service Log On to "Local System account" OR

 - Provide full access to the "\Documents and Settings\NetworkService\Local Settings\Temp" for any account that needs to run the queries.

 希望這是最後一次搞這個問題了.