Re: Time out error in my activex script that updates records.

#1

Why do you need to do this in an Active Script task?

This will work

--Set to 0 where datecolumn date is yesterday or before

UPDATE Table SET MemberCircle = 0 where DATEDIFF(day,getdate(),DateColumn)
<0

--Set to 0 where datecolumn date is Less than NOW (even a second)

UPDATE Table SET MemberCircle = 0 where DateColumn < Getdate()

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Teresa" <tmeadows (AT) arizonatc (DOT) com> wrote

Quote:

Hello i'm new to using DTS, but now new to vb, vb script and database
programming. I'm getting this weird time out error when i try to up a

record. what this script does it gets a recordset of all the customers then
checkes their expiration date. if the expiration date has pass it will then
update that customers member cirlce to 0. on the very first update is where
i'm getting the time out error. Can some please shed some light into why
this is happenning and how i can fix it. Here is my code

Quote:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

set myConn = CreateObject("ADODB.Connection")

set myRecordset = CreateObject("ADODB.Recordset")

myConn.Open = "Provider=SQLOLEDB.1;Data Source=(local);
Initial Catalog=Forté;user id = 'forteDBuser';password='userpassword'"

strSelect = "SELECT * FROM Customers"

set myRecordset = myConn.Execute(strSelect)

while not myRecordset.EOF

if not isNull(myRecordSet("CircleExpirationDate")) then

if DateDiff("d",
myRecordSet("CircleExpirationDate"), Date) > 366 then

strUpdate = "UPDATE Customers SET
MemberCircle = 0 WHERE CustomerId = " & myRecordset.Fields("CustomerId")

msgbox strUpdate

myConn.execute strUpdate

msgbox "got here"

end if

end if

myRecordset.MoveNext

wend

Main = DTSTaskExecResult_Success

End Function

#2

Thank you very. I tired that and it worked. Thank you again
But do you or anybody know why i was getting that timeout error. I would just like to know for the future
I guess i was trying to make this problem a little to difficult. Thanks again, allan.

#3

You could try setting a larger timeout value on the connection

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Teresa" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

Thank you very. I tired that and it worked. Thank you again.
But do you or anybody know why i was getting that timeout error. I would
just like to know for the future.
I guess i was trying to make this problem a little to difficult. Thanks
again, allan.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Time out error in my activex script that updates records.

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Time out error in my activex script that updates records. - 01-22-2004 , 02:55 PM

Re: Time out error in my activex script that updates records. - 01-22-2004 , 08:11 PM

Re: Time out error in my activex script that updates records. - 01-23-2004 , 12:07 AM