Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-03 : 09:16:50
|
Andrew writes "I'm wanting to schedule a job in SQL (to clean the database each day). I want it to select certain values from a table and delete tables (refering to those values) in the same database. It will also modify a column of values. This would be the VBscript for an asp page:
' --------------------------------------------------
' ------------ set up connection to DB -------------
' --------------------------------------------------
Dim conn1
set conn1 = server.createobject("adodb.connection")
conn1.ConnectionString = "Provider=SQLOLEDB; Data Source=zooty; Initial Catalog=ITSI; User ID=sa; Password="
conn1.open
strSQL="SELECT Actual_ExamID FROM Actual_Exam_Details WHERE DATEDIFF(d, time, GETDATE()) > 1 AND temptbl = 1 ORDER BY actual_examid"
set rs1=conn1.execute(strSQL)
strSQL3="UPDATE Actual_Exam_Details SET TempTbl = 0 WHERE DATEDIFF(d, time, GETDATE()) > 1 AND temptbl = 1"
strSQL2="DROP TABLE "
Do until rs1.eof
AexamID=rs1("Actual_ExamID")
strSQL2=strSQL2&"tempq"&AexamID
rs1.movenext
if rs1.eof then
exit do
else
strSQL2=strSQL2&", "
end if
loop
application.lock
set rs2=conn1.execute(strSQL2)
set rs3=conn1.execute(strSQL3)
application.unlock
How should it be written in SQL, (I presume using job schedular and VBScript).
I would really appreciate your help,
Thank you" |
|
|
|
|
|
Scheduling a job in SQL using VBScript