如何将使用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。