远程MS SQL数据库和本地MS SQL数据库 WEB管理（1）-- 表结构和数据类型

•     <% Option Explicit %> <% '远程MS SQL数据库和本地MS SQL数据库 WEB管理（1）-- 表结构和数据类型对比 '远程MS SQL数据库和本地MS SQL数据库 WEB管理（2）-- 数据手动同步 '远程MS SQL数据库和本地MS SQL数据库 WEB管理（3）-- 数据自动同步 'landman MSN:szyjj@hotmail.com '兰州百科信息网 www.ailz.net '兰州东泰信息科技有限公司 Dim Conn1, ConnStr1,i ConnStr1 = "Provider=SQLOLEDB.1;Password=12345;Persist Security Info=True;User ID=remotedatabaseUID;Initial Catalog=remotedatabase_XXXX;Data Source=remotehost;Connect Timeout=15" Set Conn1 = server.CreateObject("ADODB.Connection") Conn1.Open ConnStr1 Dim ConnStr2,Conn2 ConnStr2 = "Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=localdatabaseUID;Initial Catalog=Local_database_XXXX;Data Source=local;Connect Timeout=15" Set Conn2 = server.CreateObject("ADODB.Connection") Conn2.Open ConnStr2 Dim Rs1,Sql1,Rs2,Sql2,sql0,rs01,rs02 sql0="SELECT name FROM sysobjects WHERE (xtype = 'u') " Response.Write "<h3>远程数据库</h3>" set rs01=Conn1.Execute(sql0) Do while not rs01.eof sql1="SELECT * FROM ["&rs01("name")&"]" set Rs1=Conn1.execute(Sql1) Response.Write "<table border=1 cellpadding=1 cellspace=1 bordercolor=blue><th colspan="&rs1.Fields.Count&" align=left>"&rs01("name")&"</th><tr>" for i=0 to rs1.Fields.Count-1 Response.Write "<td>"&rs1(i).Name&"</td>" Next Response.Write "</tr><tr>" for i=0 to rs1.Fields.Count-1 Response.Write "<td>"&FieldType(rs1(i).Type)&"</td>" Next Response.Write "</tr><tr>" ' 此处为列出各字段的所有数据 'do while not rs1.eof ' i=0 ' for i=0 to rs1.Fields.Count-1 ' Response.Write "<td>"&Rs1(i)&"</td>" ' Next ' Response.Write "</tr><tr>" ' Rs1.movenext 'loop Response.Write "</table>" rs01.movenext loop Response.Write "<div style='color:red'><h3>本地数据库www.topwaysoft.com</h3>" set rs02=Conn2.Execute(sql0) Do while not rs02.eof sql2="SELECT * FROM ["&rs02("name")&"]" set Rs2=conn2.execute(Sql2) Response.Write "<table border=1 cellpadding=1 cellspace=1 bordercolor=green><th colspan="&rs2.Fields.Count&" align=left>"&rs02("name")&"</th><tr>" for i=0 to rs2.Fields.Count-1 Response.Write "<td >"&rs2(i).Name&"</td>" Next Response.Write "</tr><tr>" for i=0 to rs2.Fields.Count-1 Response.Write "<td>"&FieldType(rs2(i).Type)&"</td>" Next Response.Write "</tr><tr>" ' 此处为列出各字段的所有数据 ' do while not rs2.eof ' i=0 ' for i=0 to rs2.Fields.Count-1 ' Response.Write "<td>"&Rs2(i)&"</td>" ' next ' Response.Write "</tr><tr>" ' Rs2.movenext ' loop Response.Write "</table>" rs02.movenext loop Rs1.close set rs1=nothing Rs2.close set rs2=nothing Set Conn1 = Nothing Set Conn2 = Nothing '********************************************************* '* 名称：FieldType '* 功能：返回字段类型 '* 用法：FieldType(nType as integer) '********************************************************* Function FieldType(nType) Select Case nType Case 128 FieldType = "BINARY" Case 11 FieldType = "BIT" Case 129 FieldType = "CHAR" Case 135 FieldType = "DATETIME" Case 131 FieldType = "DECIMAL" Case 5 FieldType = "FLOAT" Case 205 FieldType = "IMAGE" Case 3 FieldType = "INT" Case 6 FieldType = "MONEY" Case 130 FieldType = "NCHAR" Case 203 FieldType = "NTEXT" Case 131 FieldType = "NUMERIC" Case 202 FieldType = "NVARCHAR" Case 4 FieldType = "REAL" Case 135 FieldType = "SMALLDATETIME" Case 2 FieldType = "SMALLMONEY" Case 6 FieldType = "TEXT" Case 201 FieldType = "TIMESTAMP" Case 128 FieldType = "TINYINT" Case 17 FieldType = "UNIQUEIDENTIFIER" Case 72 FieldType = "VARBINARY" Case 204 FieldType = "VARCHAR" Case 200 FieldType = "" End Select End Function %>

<% Option Explicit %>
<%
'landman MSN:szyjj@hotmail.com
'兰州百科信息网 www.ailz.net
'兰州东泰信息科技有限公司
'远程MS SQL数据库和本地MS SQL数据库 WEB管理（1）-- 表结构和数据类型对比
'远程MS SQL数据库和本地MS SQL数据库 WEB管理（2）-- 数据手动同步
'远程MS SQL数据库和本地MS SQL数据库 WEB管理（3）-- 数据自动同步
Dim Conn1, ConnStr1,i
 ConnStr1  =  "Provider=SQLOLEDB.1;Password=12345;Persist Security Info=True;User ID=xxxx;Initial Catalog=xxx;Data Source=xx;Connect Timeout=15"
Set Conn1 = server.CreateObject("ADODB.Connection")
Conn1.Open ConnStr1 
Dim ConnStr2,Conn2
 
ConnStr2  =   "Provider=SQLOLEDB.1;Password=12345;Persist Security Info=True;User ID=xxxxxx;Initial Catalog=xxxx;Data Source=xxxxxxx;Connect Timeout=15"
Set Conn2  = server.CreateObject("ADODB.Connection")
 Conn2.Open ConnStr2
 Dim Rs1,Sql1,Rs2,Sql2,sql0,rs01,rs02
'sql0="SELECT name FROM sysobjects WHERE (xtype = 'u') AND (status > 0) "

'4月14日修正 此处有一个小Bug

sql0="SELECT name FROM sysobjects WHERE (xtype = 'u') AND (status > 0) "

Response.Write "<h3>远程数据库www.ailz.net</h3>"
set rs01=Conn1.Execute(sql0)
Do while not rs01.eof
 sql1="SELECT *   FROM  ["&rs01("name")&"]"
 set Rs1=Conn1.execute(Sql1)

 Response.Write "<table border=1 cellpadding=1 cellspace=1 bordercolor=blue><th colspan="&rs1.Fields.Count&" align=left>"&rs01("name")&"</th><tr>"
  for i=0 to  rs1.Fields.Count-1
   Response.Write "<td>"&rs1(i).Name&"</td>"
  Next
   Response.Write "</tr><tr>"
  for i=0 to  rs1.Fields.Count-1
   Response.Write "<td>"&rs1(i).Type&":"&FieldType(rs1(i).Type)&"</td>"
  Next
   Response.Write "</tr><tr>"
  '   此处为列出各字段的所有数据
  'do while not rs1.eof
  ' i=0
  ' for i=0 to  rs1.Fields.Count-1
  ' Response.Write  "<td>"&Rs1(i)&"</td>"
  ' Next
  ' Response.Write "</tr><tr>"
  ' Rs1.movenext
  'loop
 Response.Write "</table>"
rs01.movenext
loop

Response.Write "<div style='color:red'><h3>本地数据库xbka</h3>"
set rs02=Conn2.Execute(sql0)
Do while not rs02.eof
 sql2="SELECT *  FROM ["&rs02("name")&"]"
 set Rs2=conn2.execute(Sql2)
 Response.Write "<table border=1 cellpadding=1 cellspace=1 bordercolor=green><th colspan="&rs2.Fields.Count&" align=left>"&rs02("name")&"</th><tr>"
 for i=0 to  rs2.Fields.Count-1
  Response.Write "<td >"&rs2(i).Name&"</td>"
 Next
 Response.Write "</tr><tr>"
 for i=0 to  rs2.Fields.Count-1
  Response.Write "<td>"&FieldType(rs2(i).Type)&"</td>"
 Next
 Response.Write "</tr><tr>"
 '   此处为列出各字段的所有数据
 ' do while not rs2.eof
 '   i=0
 '  for i=0 to  rs2.Fields.Count-1
 '  Response.Write  "<td>"&Rs2(i)&"</td>"
 '  next
 '  Response.Write "</tr><tr>"
 '  Rs2.movenext
 ' loop
 Response.Write "</table>"
rs02.movenext
loop

Rs1.close
set rs1=nothing
Rs2.close
set rs2=nothing
Set Conn1 = Nothing
Set Conn2 = Nothing

'*********************************************************
'* 名称：FieldType
'* 功能：返回字段类型
'* 用法：FieldType(nType as integer)
'*********************************************************
 Function FieldType(nType)
    Select Case nType
        Case 128
            FieldType = "BINARY"
        Case 11
            FieldType = "BIT"
        Case 129
            FieldType = "CHAR"
        Case 135
            FieldType = "DATETIME"
        Case 131
            FieldType = "DECIMAL"
        Case 5
            FieldType = "FLOAT"
        Case 205
            FieldType = "IMAGE"
        Case 3
            FieldType = "INT"
        Case 6
            FieldType = "MONEY"
        Case 130
            FieldType = "NCHAR"
        Case 203
            FieldType = "NTEXT"
        Case 131
            FieldType = "NUMERIC"
        Case 202
            FieldType = "NVARCHAR"
        Case 4
            FieldType = "REAL"
        Case 135
            FieldType = "SMALLDATETIME"
        Case 2
            FieldType = "SMALLMONEY"
        Case 6
            FieldType = "TEXT"
        Case 201
            FieldType = "TIMESTAMP"
        Case 128
            FieldType = "TINYINT"
        Case 17
            FieldType = "UNIQUEIDENTIFIER"
        Case 72
            FieldType = "VARBINARY"
        Case 204
            FieldType = "VARCHAR"
        Case 200
            FieldType = "VARCHAR"
    End Select
End Function

%>