SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 ActiveX Script + ADODB.connection cant execute
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

5 Posts

Posted - 09/17/2007 :  05:59:12  Show Profile  Visit veasnamuch's Homepage  Reply with Quote
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 = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004

dim 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, adOpenKeyset

If 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&") "


Main = DTSTaskExecResult_Success

End If

End Function

Best regards,

Veasna Much
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000