如何將使用Access數據庫文件的ASP程序轉向SQL數據庫

如何將使用access數據庫文件的asp程序轉向sql數據庫

因為blog的access數據庫文件經常出問題, 於是昨晚將blog轉向了sql數據庫. 網上找了一些文章, 現在經合自己的經驗, 把實際操作要點寫下來.

一.數據庫轉換問題
1.用SQL Server新建一個數據庫, 然後將mdb文件中的access數據導入(import)這個新建的數據庫.
2.access數據庫原來自動增長的ID在SQL中要重新設置,設置為“標識”(identity).
3.access表內數據類型"是/否"，到了sql server的表內就是 bit (not null),有可能一些true/false類型不能使用，要變為1/0。
4.原來的默認值都丟失了.主要是數字類型和日期類型.
5.轉化時，跟日期有關的字段，SQL SERVER默認為smalldatetime型，我們最好將它變為datetime型，因為datetime型的範圍比smalldatetime型大。有時用smalldatetime型時，轉化失敗，而用datetime型時，轉化成功。
6.在mssql server中，有許多保留字。mssql在導入的時候，會自動給這些字段（包括數據庫中的表名）加上[字段名]，因此，你必須修改你的腳本，把相應的字段名字（或者表名字）加上中括號，或改變字段名字為不是mssql的保留字.最好在有可能和系統關鍵字的地方使用［和］將他包圍起來，以避免在移植過程中出現的運行錯誤問題  
7.CursorType要改成1,也就是打開數據庫時要給出第一個數字參數為1，否則記錄可能 顯示不完整 
8.備註類型要通過cast(column as varchar)來使用 

二.asp數據庫連接語句修改
以下假設strConn為連接字符串變量名.
由原來strConn="Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(數據庫文件路徑)
改為:strConn="Provider=SQLOLEDB;Data Source=’數據庫所在服務器IP(本地為127.0.0.1)’;Initial Catalog=’數據庫名’;User ID=’數據庫用戶名’;Password=’數據庫用戶密碼’;" 

三.查尋語句問題(注意以下適用的是sql查尋語句中的修改原則, 不要修改查尋語句外的asp語句)
1.所有的sql語句中的now()，time()，date()必須換成getdate()。
2.isnull(rowname)要改成rowname = null 
3.所有datediff(’d’, time1, time2)要改成datediff(day, time1, time2) ,所有datediff(’ww’, time1, time2)要改成datediff(week, time1, time2)  (或datediff("d", time1, time2)要改成datediff(day, time1, time2) ,datediff("ww", time1, time2)要改成datediff(week, time1, time2)  )
4.“DELETE * FROM ……”要改為“DELETE FROM ……” 例如：在對ACCESS數據庫進行刪除紀錄時用："delete * from user where id=10",而對SQL SERVER數據庫進行刪除是用："delete user where id=10". 
5.access裡面除法可以使用""或者"/"，MSSQL裡面只能使用"/"
6.SQL語句中Like後面字符串的通配符’*'要改成’%'。
7.access查詢通過cdate構造日期，sql server內是convert(datetime,…), convert使用方法見下文.
8.在對ACCESS數據庫處理中,sql語句中直接可以用一些VB的函數，像instr()函數，而對SQL SERVER數據庫處理中不能使用這類函數(instr()可用charindex函數代替,方法見下文)。 
9.在access的sql語句中的時間使用變量查詢的時候，一般使用"select * from aaaa while time=#"&變量名&"#"，在mssql中的語法是“select * from aaaa while time=’"&變量名&"’"”。不然有可能rs.update失敗，修改成update 表名 set 字段=‘值’ 這樣通過（遇到的情況，提示為： Microsoft OLE DB Provider for SQL Server 錯誤 ’80040e38’  )

SQL中CONVERT轉化函數的用法

CONVERT的使用方法:

////////////////////////////////////////////////////////////////////////////////////////

格式:
CONVERT(data_type,expression[,style])

說明:
此樣式一般在時間類型(datetime,smalldatetime)與字符串類型(nchar,nvarchar,char,varchar)
相互轉換的時候才用到.

例子:
SELECT CONVERT(varchar(30),getdate(),101) now
結果為
now
—————————————
09/15/2001

/////////////////////////////////////////////////////////////////////////////////////

style數字在轉換時間時的含義如下

————————————————————————————————-
Style(2位表示年份) | Style(4位表示年份) | 輸入輸出格式 
————————————————————————————————-
- | 0 or 100 | mon dd yyyy hh:miAM(或PM) 
————————————————————————————————-
1 | 101 | mm/dd/yy 
————————————————————————————————-
2 | 102 | yy-mm-dd 
————————————————————————————————-
3 | 103 | dd/mm/yy 
————————————————————————————————-
4 | 104 | dd-mm-yy 
————————————————————————————————-
5 | 105 | dd-mm-yy 
————————————————————————————————-
6 | 106 | dd mon yy 
————————————————————————————————-
7 | 107 | mon dd,yy 
————————————————————————————————-
8 | 108 | hh:mm:ss 
————————————————————————————————-
- | 9 or 109 | mon dd yyyy hh:mi:ss:mmmmAM(或PM)
————————————————————————————————-
10 | 110 | mm-dd-yy 
————————————————————————————————-
11 | 111 | yy/mm/dd 
————————————————————————————————-
12 | 112 | yymmdd 
————————————————————————————————-
- | 13 or 113 | dd mon yyyy hh:mi:ss:mmm(24小時制) 
————————————————————————————————-
14 | 114 | hh:mi:ss:mmm(24小時制) 
————————————————————————————————-
- | 20 or 120 | yyyy-mm-dd hh:mi:ss(24小時制, 應該和now()生成的日期格式相同) 
————————————————————————————————-
- | 21 or 121 | yyyy-mm-dd hh:mi:ss:mmm(24小時制) 
————————————————————————————————-

使用charindex函數代替   
    
  CHARINDEX   
  返回字符串中指定表達式的起始位置。     
    
  語法   
  CHARINDEX   (   expression1   ,   expression2   [   ,   start_location   ]   )     
    
  參數   
  expression1   
    
  一個表達式，其中包含要尋找的字符的次序。expression1   是一個短字符數據類型分類的表達式。   
    
  expression2   
    
  一個表達式，通常是一個用於搜索指定序列的列。expression2   屬於字符串數據類型分類。   
    
  start_location   
    
  在   expression2   中搜索   expression1   時的起始字符位置。如果沒有給定   start_location，而是一個負數或零，則將從   expression2   的起始位置開始搜索。   
    
  返回類型   
  int   
    
  注釋   
  如果   expression1   或   expression2   之一屬於   Unicode   數據類型（nvarchar   或   nchar）而另一個不屬於，則將另一個轉換為   Unicode   數據類型。   
    
  如果   expression1   或   expression2   之一為   NULL   值，則當數據庫兼容級別為   70   或更大時，CHARINDEX   返回   NULL   值。當數據庫兼容級別為   65   或更小時，CHARINDEX   僅在   expression1   和   expression2   都為   NULL   時返回   NULL   值。     
    
  如果在   expression2   內沒有找到   expression1，則   CHARINDEX   返回   0。