|
veasnamuch
Starting Member
5 Posts |
 Posted - 2007-09-17 : 05:59:12
|
| I have a table that record of all transaction(SQL statements) that user done in day time and I record it in to a table so that I can use it later. I want to execute those statement to another server but when I run it, it seems that my statement can not have more then 255 characters but if I do it manually by copy the statement from table and pasted to myDestConn.Execute ...( where ... is the sql statement that i manually pasted it), it can execute without any problem. but if I do a loop to get those statement from table and past it as argument to myDestConn.Execute, it does not work.How could I do? Your help is very important to me.I have my data in table [tblLog] like this-----------------------------------------------------------------LogID|LogDate |UserId |UserTransaction:varchar(1000)-----------------------------------------------------------------575 |6/29/2007 8:31:47 AM |43 |UPDATE tblEXCG SET EXCGID=96500, EXCGDelivDate=6/29/2007, ConsigneeID=11703, FAID=10417, PersonID=1915, EXCGDesc='ACT:178/1602KGS', SDG=0, SCS=0, SLA=0, SVS=0, SeeAWB=1, SeeCR=1, SeeWS=1, PreparedBy=4, ApprovedBy=37, cHARGEaGAIN=0, Cashier=52, CDN='6346/290607', ExPrintDate='' WHERE EXCGID=96500-----------------------------------------------------------------576 |6/29/2007 8:34:49 AM |43 |UPDATE tblEXCGD SET EXCGID=96500, ArrivalDate=6/28/2007, EXCGDAWB='21716971780', EXCGDHAWB='2002-1808', CountryCode='US', GoodsId=2824, FlightID=297, EXCGDNNormal=178, EXCGDWNormal=1602, EXCGDNCold=0, EXCGDWCold=0, EXCGDNValuable=0, EXCGDWValuable=0, EXCGDNLive=0, EXCGDWLive=0, EXCGDNDangerous=0, EXCGDWDangerous=0 WHERE EXCGDID=86947-----------------------------------------------------------------577 |6/29/2007 8:34:49 AM |43 |INSERT INTO tblEXCGD(EXCGID, ArrivalDate, EXCGDAWB, EXCGDHAWB, CountryCode, GoodsId , FlightID, EXCGDNNormal, EXCGDWNormal, EXCGDNCold, EXCGDWCold, EXCGDNValuable , EXCGDWValuable, EXCGDNLive, EXCGDWLive, EXCGDNDangerous, EXCGDWDangerous) VALUES(96500,'6/28/2007','21716971780','2002-1808','US',2824,297,178,1602,0,0,0,0,0,0,0,0)and my script is like that'**********************************************************************' Visual Basic ActiveX Script'************************************************************************Function Main()Const adOpenForwardOnly = 0Const adOpenKeyset = 1Const adOpenDynamic = 2Const adOpenStatic = 3'---- CommandTypeEnum Values ----Const adCmdUnknown = &H0008Const adCmdText = &H0001Const adCmdTable = &H0002Const adCmdStoredProc = &H0004dim countr' Instantiate the ADO objects.set mySourceConn = CreateObject("ADODB.Connection")set mySourceRecordset = CreateObject("ADODB.Recordset")set myDescRecordset = CreateObject("ADODB.Recordset")mySourceConn.Open = "Driver={SQL Server};Server=VEASNA-MUCH;Database=PNHWH;Uid=sa;Pwd=sa01;"mySQLCmdText = "Select * from tblLog order by LogID"mySourceRecordset.Open mySQLCmdText, mySourceConn, adOpenKeysetIf mySourceRecordset.RecordCount > 1 Then dim LogID, UserID, UserTransaction set myDestConn = CreateObject("ADODB.Connection") myDestConn.Open = "Driver={SQL Server};Server=VEASNA-MUCH;Database=XPNHWH;Uid=sa;Pwd=sa01;" for countr = 1 to mySourceRecordset.RecordCount LogID = mySourceRecordset.Fields("LogID").value UserID = mySourceRecordset.Fields("UserID").value UserTransaction = mySourceRecordset.Fields("UserTransaction").value UserTransaction = "'"& UserTransaction & "'" 'Commit to remote server myDestConn.Execute UserTransaction 'Clear from local database myDestConn.Execute "DELETE FROM tblLogWeb WHERE (LogID="&LogID&") AND (UserID="&UserID&") " mySourceRecordset.MoveNext Next Main = DTSTaskExecResult_SuccessEnd IfEnd FunctionBest regards,Veasna Much |
|
ActiveX Script + ADODB.connection cant execute