ActiveX Script Problem
-
Hi everyone,
I have this ActiveX script that I'm using in a Transform Task in one of the DTS package.
I have 2 Tables on 2 different servers. I'm using one to update the other.
What happens is this:
1. If the record already exists in the table - update it.
2. If it doesn't exist - Insert it.
Here is my code:
mySourceSQL = "SELECT * FROM source"
myDestSQL = "SELECT * FROM dest"
mySourceRecordset.open mySourceSQL, mySourceConn, adOpenKeyset
myDestRecordset.open myDestSQL, myDestConn, adOpenKeyset
for counter = 1 to mySourceRecordset.RecordCount
for counter2 = 1 to myDestRecordset.RecordCount
if (myDestRecordset.Fields("ID").value = mySourceRecordset.Fields("ID").value) then
found = 1
MsgBox myDestRecordset.Fields("ID").value
id = mySourceRecordset.Fields("ID").value
blank = mySourceRecordset.Fields("Blank").value
blankVal = "'" & blank & "'"
updateSQL = "UPDATE dest SET Blank = " & blankVal & " WHERE ForeignID = " & id & " "
myDestConn.Execute updateSQL
'move on to the next record because this one was found!
Exit for
Main = DTSTransformStat_OK
else
found = 0
Main = DTSTransformStat_OK
end if
myDestRecordset.MoveNext
Next
if found = 0 then
MsgBox counter2
'insert the record if it doesn't already exist
id2 = mySourceRecordset.Fields("ID").value
MsgBox mySourceRecordset.Fields("ID").value
blank2 = mySourceRecordset.Fields("Blank").value
MsgBox mySourceRecordset.Fields("Blank").value
blankVal2 = "'" & blank2 & "'"
insertSQL = "INSERT INTO dest(ID, Blank) VALUES(" & id2 & ", " & blankVal2 & ")"
myDestConn.Execute insertSQL
Main = DTSTransformStat_OK
end if
mySourceRecordset.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
It seems like my FOR loops are not right...The script only inserts one record and then crashes with this error:
Either BOF or EOF is true, or the current record has been deleted.
Any Ideas??
-
I can't tell you what's wrong wit your script... but I can give you another way of doing this which would be much faster than ado..
I assume you can use this to do an inner join to do your updates and a left join to find to missing lines and then insert them ( I never did this from sqlserver to sqlserver but from sqlserver to dbase IV).
Search for OPENROWSET in BOL.
This is to connect on the other SQL server :
B. Use OPENROWSET with an object and the OLE DB Provider for ODBC
This example uses the OLE DB Provider for ODBC and the SQL Server ODBC driver to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized with a provider_string specified in the ODBC syntax used by the ODBC provider, and the catalog.schema.object syntax is used to define the row set returned.
USE pubs
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=seattle1;UID=manager;PWD=MyPass',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
GO
This is how t do the inner join... msg me if you need help to do the update and insert statements.
D. Use OPENROWSET and another table in an INNER JOIN
This example selects all data from the customers table from the local SQL Server Northwind database and from the orders table from the Access Northwind database stored on the same computer.
Note This example assumes that Access is installed.
USE pubs
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c INNER JOIN
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS o
ON c.CustomerID = o.CustomerID
GO
-
Thanks Remi,
I will definately try this. And I would like your help on the Update and Insert...
Also I was wondering, what do you think is the best way to have this task executed (I want it to be a nightly routine )? As a stored procedure? Or maybe a SQL task in a DTS package?
--> I've made changes to my code and now it works but very slowly...it takes it about 5 minutes to find ONE record that exists in both tables and update/insert....
-
That's why I was suggesting the openrowset, because what you do is force sqlserver to make the inner join instead of coding it yourself (which is exponentially slower as you add records in the tables... and even slower because ado is object based and not sql based).
As for the execution method I'm really no expert in this field... I've just made a few dts to transfer databases objects and I never did a job. But I assume than once the sp or dts works it won't make much difference as it would be the same code executed by the same sql engine... (unless the compilation of the sp is more prepared than the dts compilation... but that would make for only a few miliseconds difference per execution). And I think the only to make it a nightly thing is to use a job to fire up the sp or the dts.
As for the update and insert query if you have a lot of rows to check for you'll have to do some gymnastics to make it fast.
first you'll need to create a table variable which is an exact replica (without the constraints) of the remote table like this :
Declare @RQ table (
PkObjSQL int NOT NULL primary key clustered,
xtype varchar (3) COLLATE French_CI_AS NOT NULL ,
id int NOT NULL ,
name varchar (128) COLLATE French_CI_AS NOT NULL ,
FkParent int NOT NULL ,
FkDB int NOT NULL ,
Colid int NOT NULL,
keyno smallint NOT NULL,
RefDate datetime NOT NULL,
Deleted bit NOT NULL,
IsPK bit NOT NULL,
Owner varchar (256),
indid smallint NOT NULL,
Length smallint NOT NULL,
isOutParam bit NOT NULL,
ColPrecision smallint NOT NULL,
VarTypeName varchar (16) NULL,
DefaultValue varchar (4000) NULL,
FkParentOBJ int NULL
)
--print 'insert the remote data to the table variable'
Insert into @RQ (PkObjSQL, XType, id, name, FkParent, FkDB, Colid, Keyno, RefDate, Deleted, IsPK, Owner, indid, Length, IsOutParam, Colprecision, VarTypeName, DefaultValue, FkParentOBJ)
(Select PkObjSQL, XType, id, name, FkParent, FkDB, Colid, Keyno, RefDate, Deleted, IsPK, Owner, indid, Length, IsOutParam, Colprecision, VarTypeName, DefaultValue, FkParentOBJ from OPENROWSET ('MSDASQL','DRIVER={SQL Server};SERVER=SERVEUR4'/*remote server name.. must be a linked server*/,'Select * from Documentation.dbo.ObjSQL' /*query*/) RQ)
--print 'check selected lines'
Select * from @RQ
--print 'Update statement'
Update dbo.ObjSQL set XType = RQ.XType, id = RQ.id, name = RQ.Name, FkParent = RQ.FkParent, FkDB = RQ.FkDB, Colid = RQ.FkDB, Keyno = RQ.Keyno, RefDate = RQ.RefDate, Deleted = Rq.Deleted, IsPK = Rq.IsPK, Owner = RQ.Owner, indid = RQ.indid, Length = RQ.Length, IsOutParam = RQ.IsOutParam, Colprecision = RQ.ColPrecision, VarTypeName = RQ.VarTypeName, DefaultValue = RQ.DefaultValue, FkParentOBJ = RQ.FkParentOBJ from dbo.ObjSQL UPD inner join @RQ RQ on UPD.PkObjSQL = RQ.PkObjSQL
/*this will only rewrite the lines that needs to be updated.. anywhere from 2 to 50 times faster depending on the data you are working with*/
and (UPD.XType RQ.XType or UPD.id RQ.id or UPD.name RQ.Name or UPD.FkParent RQ.FkParent or UPD.FkDB RQ.FkDB or UPD.Colid RQ.FkDB or UPD.Keyno RQ.Keyno or UPD.RefDate RQ.RefDate or UPD.Deleted Rq.Deleted or UPD.IsPK Rq.IsPK or UPD.Owner RQ.Owner or UPD.indid RQ.indid or UPD.Length RQ.Length or UPD.IsOutParam RQ.IsOutParam or UPD.Colprecision RQ.ColPrecision or UPD.VarTypeName RQ.VarTypeName or UPD.DefaultValue RQ.DefaultValue or UPD.FkParentOBJ RQ.FkParentOBJ)
--print 'Insert the new lines'
Insert into dbo.ObjSQL (XType, id, name, FkParent, FkDB, Colid, Keyno, RefDate, Deleted, IsPK, Owner, indid, Length, IsOutParam, Colprecision, VarTypeName, DefaultValue, FkParentOBJ) (Select XType, id, name, FkParent, FkDB, Colid, Keyno, RefDate, Deleted, IsPK, Owner, indid, Length, IsOutParam, Colprecision, VarTypeName, DefaultValue, FkParentOBJ from @RQ RQ
/*this checks that the line doesn't exists.. I use the Primary key but you can use whatever you need*/
where not Exists (Select PkObjSQL from dbo.ObjSQL COR where COR.PkObjSQL = RQ.PkObjSQL))
--print 'check the results'
--Select * from dbo.ObjSQL
I tested this script on 7000+ lines and it took less than 1 sec (somewhat fast server.. P4 2.4 ghz 512 mb of ram each).
However if all you need is to have one table to be the exact match of another table you can research replication (can't help you there.. but many other can help here), or you can also check log shipping (more for the whole database) but I never did that either.
Hope this helps.
-
Thanks a lot Remi!
This helps a lot! I will definately go over everything and try it out.
-
btw if you are planning on using this on huge tables (1 M + rows), you can also disable the triggers, constraint, foreign keys and indexes to do the bulk insert/update (if you know the data cannot be wrong and that nobody is gonna be using the table). That can speed up the things a lot more... but you'll have to do the tests on your own to see what's the fastest... and actually how fast you need this to be ; by extrapolation this script would update 1 M lines in less than 2 minutes... maybe less than 30 secs on a very powerfull server)
-
My database table are hardly this big...a few 1000's maybe....maybe even 200,000 but not near the 1M range.
But I'll keep your advice in mind for sure.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply