u***t
发帖数: 3986 | 1 【 以下文字转载自 Programming 讨论区 】
发信人: usatt (Easy Go Lucky), 信区: Programming
标 题: DW BI ETL 问题 - Data Validation and Recordset Comparison
发信站: BBS 未名空间站 (Fri Jul 15 16:08:29 2011, 美东)
我目前在某Forture 100公司主持data migration的自动化测试, 坑爹的offshore把简单
的问题搞大搞复杂来多要钱, 弄得新的数据库妖精一样...
!! 问题: 我的approach还能再好点吗?
Source: SQL Server 2005
Target: DB2
如何测试Data Migration? 两种方法:
1. Sequential comparison
从Source DB得出个recordset A;
从Target DB的出个recordset B;
从A取第一个record, 到B用loop来找, 找到看B中的相关data是否正确.
(* 我枪弊了这个方案... 阅读全帖 |
|
u***t
发帖数: 3986 | 2 【 以下文字转载自 Programming 讨论区 】
发信人: usatt (Easy Go Lucky), 信区: Programming
标 题: DW BI ETL 问题 - Data Validation and Recordset Comparison
发信站: BBS 未名空间站 (Fri Jul 15 16:08:29 2011, 美东)
我目前在某Forture 100公司主持data migration的接收测试, 坑爹的offshore把简单
的问题搞大搞复杂来多要钱, 弄得新的数据库妖精一样...
问题: 我的approach还能再好点吗?
Source: SQL Server 2005
Target: DB2
如何测试Data Migration? 两种方法:
1. Sequential comparison
从Source DB得出个recordset A;
从Target DB的出个recordset B;
从A取第一个record, 到B用loop来找, 找到看B中的相关data是否正确.
(* 我枪弊了这个方案! Mi... 阅读全帖 |
|
u***t
发帖数: 3986 | 3 I have a huge recordset (50 millions rows x N columns)
I am going to break the recordset into 100 smaller sub-recordsets with 500,
000 rows each, by setting pagesize:
rs.PageSize = 500,000
Now, I got 100 pages of this recordsets.
Questions:
How do I export all these 100 pages into 100 excel files? Or say, I do I
treat these 100 pages as 100 sub-recordsets?
I am using below methods to load the data to excel:
ojbExcelWorkSheet.Range.CopyFromRecordset rs
Anybody?
* I don't want to treat record... 阅读全帖 |
|
u***t
发帖数: 3986 | 4 我目前在某Forture 100公司主持data migration的接收测试, 坑爹的offshore把简单
的问题搞大搞复杂来多要钱, 弄得新的数据库妖精一样...
问题: 我的approach还能再好点吗?
Source: SQL Server 2005
Target: DB2
如何测试Data Migration? 两种方法:
1. Sequential comparison
从Source DB得出个recordset A;
从Target DB的出个recordset B;
从A取第一个record, 到B用loop来找, 找到看B中的相关data是否正确.
(* 我枪弊了这个方案! Millions of rows, dozens of millions of fields. 一
个table 都要用无穷无尽的时间来execute. 而且很可能就out of memory. 除非是spot
check, 否则不可行)
2. Parallel comparison (*我的方案)
从Source DB得出个recordset A;
... 阅读全帖 |
|
h**********r
发帖数: 174 | 5 Using ADO's RecordSet in Access2000
I have a very simple table with only two fields(byte, text). I want
to use RecordSet to add a row into it by using the AddNew methods. But
it seems the Recordset opened with this table doesn't support the
AddNew method.
Could anyone tell me any possible reason of this?
Thanks a lot. |
|
u***t
发帖数: 3986 | 6 Excel is used to store data from records using query provided by offshore
resource, after writing the recordset to file, buffer is released.
Sequential looping is far slower during execution. That's why I want to use
process the comparison using recordset.
The reason I don't export directly from DB to file, is that only data with
certain criteria is needed from source DB...
bottomline, looping isn't efficient... |
|
g****j
发帖数: 6 | 7 谢谢楼上的二位大哥了，虽然还是没能完全解决问题，但是也至少找到了突破口，the
problem is Excel 的每个cell不能hold 超过911 char的text, 看来这个限制不好突破
。变通了一下，
create a public function as workaround of CopyFromRecordset，then it works all the way.
Function CopyRecordsetToArray(rsData As ADODB.Recordset) As Variant()
'Create an array where # rows = # records and # columns = # fields
'in the recordset.
Dim TempArray() As Variant
Dim r As Integer, c As Integer
ReDim TempArray(1 To rsData.RecordCount, 1 To rsData.Field |
|
u***t
发帖数: 3986 | 8 忘了第三种直接从recordset A中拿record直接到target database tables 中run
query检查
这样一个loop, 上面第一种方法两个loop(nested) |
|
j*****o
发帖数: 320 | 9 ' 一段asp程序如下：
set myConnection=Server.CreateObject("ADODB.Connection")
set myRecordset=Server.CreateObject ("ADODB.Recordset")
myConnection.Open "DSN","username","password"
myRecordset.ActiveConnection =myConnection
myRecordset.Source="select * from public where name='"+request("name")+"'"
myRecordset.open
Response.write(request("name")) '输出内容正确。
Response.write(myRecordset("name")) '输出内容也正确
Response.Write(myRecordset.RecordCount) '不管数据库有多少记录，总是返回-1
根据MSDN的说明：
使用 RecordCount 属性可确定 Records |
|
j*****o
发帖数: 320 | 10 但是，在Recordset被打开前加入：
myRecordset.CursorType = adOpenStatic
还是不行呀。 困惑，困惑。 |
|
g****j
发帖数: 6 | 11 最近有一个棘手的project,就快完工了，可是却遇上一个不可逾越的障碍，作了好些
research，好像microsoft目前还没有解决这个问题。
任务很简单，把一个SQL Stored Procedure的return results 放到Excel spreadsheet
。下面是简单的一段code
Set cmdCommand = New ADODB.Command
cmdCommand.ActiveConnection = CurrentProject.Connection
cmdCommand.CommandType = adCmdStoredProc
cmdCommand.CommandText = "strDTPastDue"
Set rsData = New ADODB.Recordset
rsData.Open cmdCommand.Execute
objExcel.ActiveSheet.Range("A5").CopyFromRecordset rsData
一般情况下运行良好，可是只要database的某一个field (text) 太长（超过250char) |
|
u***t
发帖数: 3986 | 12 第三种就是只取一个recordset (source) 放buffer里, 然后一个一个的从source的
record中loop下去, 直接用SQL STATEMENT到target的table中找相应的内容
不过这样(target)中的garbage data就不得而知了 |
|
g***l
发帖数: 18555 | 13 RECORDSET就是临时储存的东西，CREATE RECORDSET的时候就要加上ID,这样你才能取
1000-2000，否则你就要从头LOOP一遍。还是那句话，RECORDSET是个临时性的，最好数
据存TABLE里，多少RECORD，要哪几个COLUMNS,做好了，最后全读出来完事，
MANIPULATE RECORDSET又笨又慢，而且很容易出错。 |
|
w******i
发帖数: 1476 | 14 i tried it and it could NOT work fine as b4...
the invalid operation msg shows as
"Invalid operation. (Error 3219)
Possible causes:
1. You tried to write to a read-only property. See the Help topic for the
property to determine whether it is read/write.
2. You tried to use a method or property on a type of Recordset object that
the method or property does not apply to. See the Recordset object summary
topic to determine which methods and properties apply to a given type of
Recordset object.
3. Y |
|
u***t
发帖数: 3986 | 15 breaking up a huge recordset into multiple smaller recordsets so I can use
Union All with Count(*) to compare the source and target recordsets.
No keys, can't process the comparison records by records. |
|
u***t
发帖数: 3986 | 16 你不知道我干什么...source的东西是标准, target里的东西是要看是否migrate 好的.
所以我break up source 的 recordset, 那每个recordset PK的上下限到target里去拿
相应recordset做对比 (...union all...count(*) = 1...是mismatched)
我不做ETL, 我自动测试ETL...
我看你糊涂...
MATCH |
|
l*****a
发帖数: 14598 | 17 basically,
you combined a SQL statement to do query based on your query conditions.
then u can use some kinds of mechanism such as ADO/DAO and etc to connect to
DB
then you can get a recordset which contain all the results you want.
the u just fetch record from the recordset one by one.
BTW,C#就是想做成白吃都可以用的。。。
该有的方法，函数都定义了。直接用旧可以了
里， |
|
S*******0
发帖数: 208 | 18 Can someone help me with the code, simply put is : find a cell in excel, and
copy the sql reusult dataset starting from the cell found, my code is as
follows, it did not indicate any error, but could not run, please help me to
identify where the problems, many thanks
Sub reshnepoolmonthValues()
Worksheets(2).Activate
Dim valuedt As String
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String
Dim wrk As Worksheet
Dim inRng1 As Long
Dim inrng2 As Long
D... 阅读全帖 |
|
n*****n
发帖数: 4 | 19 在 DAO中 好 用 的 SQL："select * from db where field like
'我要查的*'"
在 ADO中 没 法 用 ， 查 不 到 。
DAO我 用 的 语 句 生 成 的 recordset， ADO用 的 是 AdoDB的 控 件 。
用 完 完 全 全 同 样 的 语 句 ，
在 DAO生 成 的 recordset中 查 到 三 条 ，
而 ADO中 是 一 条 也 查 不 到 。
也 不 要 说 我 数 据 连 接 有 问 题 ， 我 不 会 出 这 样 的 错 。
adodb.recordsource="select * from xx where field like 'xx*'"
我 知 道 问 题 出 在 *上 面 ，因 为 我 用 等 号 时 没 有 问 题 ，
可 这 样 一 来 就 实 现 不 了 ADO下 的 模 糊 查 询 。
怎 么 ADO比 DAO要 新 ， 为 什 么 就 实 现 不 了 like的 带 *的 查 询
了 呢 ？
还 |
|
w*r
发帖数: 2421 | 20 I doubt about whether the nt OS can handle the file
larger than 2G.
Why not tune your program to make the recordset smaller
in the practically way, handling such big recordset
consumes all your system resource and
it is not a practical way to have the program
use ODBC as client to retrive so much data
by one transaction |
|
r****e
发帖数: 9 | 21 在用access做数据库时，想一次删掉多个数据。用的是access 2000
里的vb环境。我用sql已经选出了recordset，并且设了filter参数，然后就用recordset.
delete adAffectGroup.结果总是说 adAffectGroup 不能用在这儿，不知道怎么回事？请
大家看看哪儿有问题？ |
|
g***l
发帖数: 18555 | 22 两个TABLE之间的比较用VB，妈也，打开SOURCE的RECORDSET，一个记录一个记录的走，
然后再打开TARGET的RECORDSET，一个记录一个记录，两个LOOP套一起，这可是最笨的
办法了。数据库两个表比较不难的啊。 |
|
u***t
发帖数: 3986 | 23 就是不要single record的processing.
要recordset.
用recordset.delete()如何? |
|
c****y
发帖数: 3592 | 24 我的程序可以跑但是没结果。
rs.Field.Count是有结果的
rs.RecordCount结果是-1(肯定不对)
rs.GetRow或者掉rs结果都是error type mismatch.
我感觉是没有调出东西来？procedure肯定是好的，直接在excel上用connection有结果的
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Cmd As ADODB.Command
Dim strConn As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set Cmd = New ADODB.Command
strConn = "Provider=MSDASQL.1;Persist Security Info=True;DSN=pmw;Description
=PMW;UID=pmwreader;Trusted_Connection=Yes;APP=2007 Microsoft Office system;
WSID=BG-R... 阅读全帖 |
|
d********e
发帖数: 19 | 25 I have a question about using ADO to access database. When I am using
recordset.recordcount function, it always return -1, even I can retrieve data
from the Access database. the recordset.adsoluteposition is -3. Does anyone
know the trick? Thanks a lot. |
|
d******t
发帖数: 1242 | 26 ADO/DAO 是不是支持vb?想当年vb的recordset用的是那么的熟手。。。
to |
|
M**4
发帖数: 731 | 27 在你的第一个方案里面搞个hash tree就解决了。
简单 |
|
u***t
发帖数: 3986 | 28 OK target里的search快了, 但也是要loop过source里的millions行的records... |
|
|
b******e
发帖数: 3348 | 30 一直以为U大已经退休不工作了。。。。。。
简单 |
|
|
j*****o
发帖数: 320 | 32 这样行了，但是那位能不能点解？
SSS="select * from clients where name='"+Request("name")+"'"
myRecordset.open SSS,myConnection,1,3 |
|
E********r
发帖数: 99 | 33 我用win nt下的PWS和MS ACCESS组合写ASP。
(WIN NT WORKSTATION)
我设置了ODBC数据源，比如叫test.
然后使用如下语句：
set cn = ....("ADODB.Connection")
set rs = ............Recordset...
cn.Open "test"
至此按理应该可以打开数据库访问。
但是实际上是被拒绝访问。
同样的语句在win 95下是可以的。
不知道NT WORKSTATION下需要设置什么才能访问？
感到很迷惑，请指教，谢谢！
能否给\出一个示例？ |
|
p******e
发帖数: 31 | 34 我想用ASP添加数据库record时，总是报错。
请问是怎么回事？ 谢谢！
我的数据库并没有设为只读啊。
程序如下：
<%
dim db
dim strCon
db="access"
strCon="DRIVER={Microsoft Access Driver (*.mdb)};
DBQ=c:/Inetpub/wwwroot/name/name.mdb"
set db=Server.CreateObject("ADODB.Connection")
db.open strCon
%>
set res=Server.CreateObject("ADODB.Recordset")
res.Open "select * from
user",strCon,adOpenKeyset,adLockOptimistic
res.AddNew
res("pass")=pass
res("fname")=fname
res("lname")=lname
if city<>"" then
res("city")=city
....... |
|
h*****l
发帖数: 184 | 35 我用ASP处理ACCESS数据库。
但运行INSERT时，出来错：
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Operation must use
an updateable query.
百思不得其解， 请帮我看看， 错误出在SQLInsert那句上：
Set corConn = Server.CreateObject("ADODB.Connection")
dbpath = Server.mappath("fpdb\mbaalumni.mdb")
corConn.open "driver={Microsoft Access Driver (*.mdb)}; dbq=
"&dbpath&""
RS = Server.CreateObject("ADODB.Recordset")
SQLSel = "Select * from mbaalumni Where fname = '"&
Request.Form("fname") |
|
o***z
发帖数: 133 | 36 what do you mean by connecting a pull down menu?
if you want to populate the menu items with the contents returned by
a recordset, i think it is not hard |
|
B*******n
发帖数: 58 | 37 平台：IIS/WinXP/ASP/Access
问题：注册登陆根据用户行为更新数据库表a后，再用一个asp页面x查询a时，
有些用户返回正常，有些像是进入了死循环，一直无法返回。
屏蔽掉x中循环输出语句，结果还是无法返回。
怀疑问题是connection/recordset没有正常关闭。
重新改写全部asp，增加关闭conn/rec语句，结果一样。
很是古怪，关键是有些用户正常，有些用户不正常。
当前数据库中330用户左右，目前发现332、333工作不正常，
而331、334工作却正常。
谁遇到过这种情况？或是知道那里能查到相关信息？先谢了。 |
|
k*****y
发帖数: 221 | 38 Not sure I understand your question.
If you were trying to make a classic ASP page with paging function, there are
two ways: 1. use recordset's build-in paging 2. write your own code to count &
select records
for each page.
If messages are losing line breaks, you might need to do
Replace(messageBodyString,vbcrlf,")
one |
|
T*****y
发帖数: 225 | 39 来自主题: BuildingWeb版 - 真心求教 您用过的哪个development tool 可以
1. easily display recordsets on a webpage?
2. easily make forms to allow inserts and updates?
花钱不花钱都行。多谢。 |
|
E**c
发帖数: 713 | 40 我用IIS做Web Server,想用一台Novell上面的foxpro的数据库.
在本机上我己经建立了数据源,用visdata也可以打开并修改,当
在Interdev中建立一个Dataenvironment并建立Design time object
Recordset 和Grid.上述过程如果用SQL Server一切良好，但是为什么
Foxpro(Foxpro2.5)就不行了呢，每次运行页面，IE就会说
不能找到文件****.dbf
但是该文件的确存在而且在VI里面还可以打开编辑.
是登录novell需密码？还是foxpor本身不能这样做？还是???
如果您有更好的办法，能否告知? |
|
l***s
发帖数: 259 | 41 I am using VB6 to manage database.
I have two database in different format ACCESS97 and 2000
so i cannot merge them into one for other users are using them too.
So, can I open two connections in my vb program,
and using SELECT to build a new recordset to join the two table? and how?
Thanks, guys. |
|
S****n
发帖数: 46 | 42 i don't like access.
wrote a vb/asp application on access.
we even cannot use RecordCount to get the size of the recordset.
i don't know how to set cursor backwords as well as forward.
anyway, there are many restrictions in access.
it is better client than server. |
|
d**t
发帖数: 14 | 43 Hi,
When I use the follow statement in Visual Basic, the error message said it is
User-defined datatype. I don't know how to make them work. :-(
dim mydb as database
dim myTb as recordset
dim myWs as workspace
Could anybody give me any hints?
Thank you very much!
dgyt |
|
x****g
发帖数: 6597 | 44 各位大虾：
我的硬件配置
PIII－700，256MRAM，100GB HD GATEWAY原装机
WINNT平台，ORACLE数据库有250，000条记录，大小3GB
用VC＋ODBC查询，SQL 语句SELECT
当用SELECT获得RECORDSET很大时如50，0000条
移动记录MOVENEX，总在C盘根目录(或TEMP)产生临时文件
msorclr.x 较小，几十M
msorclr.x+1 巨大，几百M到2G
x为一整数，如2或19等，每次运行不一样
原来我C盘空间不够，产生“General Error”,后来整理后
有10个G剩余空间，但是，当临时文件msorclr.x+1涨大到
2个GB的时候，就不再增加了，也出现“General Error”
运行完成后，临时文件自动删除。
这是啥毛病？难道我要扩内存，老板到是批准1G。
谢谢！
x********[email protected] |
|
g******p
发帖数: 18 | 45
Add this line after the above:
name=replace(recordset("Name"),"'","''")
What it does is, it escapes the ' character so your value
indeed can contain apostrophe.
For details see:
http://www.aspfaq.com/faq/faqShow.asp?fid=4
HTH. |
|
S*******s
发帖数: 13043 | 46 what I want to is like this:
declare @acc int,@id int,@count int
decalre my_cursor cursor for
select id, count from sometable
set @acc=0
open my_cursor
fetch next from my_cursor
into @id,@count
while @@fetch_status=0
begin
@acc=@acc+@count
-- i want to insert a new raw to the recordest returned, but how?
insert_to_recorset @id,@acc
fetch next from my_cursor
into @id,@count
end
close my_cursor
deallocate my_cursor |
|
|
S*******s
发帖数: 13043 | 48 trigger? i can't understand. can you specify more? |
|
g***o
发帖数: 297 | 49 I think these two queries get different recordsets.
for instance, we have following table
county company
2 A
3 A
4 A
2 B
3 B
4 B
5 B
2 C
your query get this result:
county count
2 3
3 2
4 2
5 1
my query get the result like:
count
4
the reason is that I get the count number of distinct county that exists in
this table,
while you get the count numb |
|
