<% 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
%>