?
Solved

vb.net scheduled task to check database data?

Posted on 2009-05-15
21
Medium Priority
?
545 Views
Last Modified: 2012-05-07
I have written some code that checks my database for duplicates rows of data and deletes the oldest records. I now need to set this aspx page to run every night at midnight.

Now I know I can create a scheduled task and run this script that way using Internet Explorer, BUT as far as I know this would cause hundreds of windows to open up over the period of a year.

Is there a way to force close the IE window after the script has executed OR is there a better way to run this schedule task that doesn't leave orphanned IE windows behind?
0
Comment
Question by:clearchannel
  • 11
  • 9
21 Comments
 
LVL 8

Expert Comment

by:Faheem Shaikh
ID: 24393602
I would suggest using batch script or an executable to be used here instead of using the web based approach. Create a batch file to call your sql. You can use the windows scheduler to run the batch every night. You will have logs created for your reference. That will be the best approach rather than using the web based approach.
0
 

Author Comment

by:clearchannel
ID: 24393644
Ok, when you say batch script or executable what type of exe/batch script are we tlaking about?
Are these created in Visual Studio?
Do I have to create them another way and if so can you provide some info or links?
Thanks
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24393679
If its the server where you want to run the script then i think you should use some other approach. May be a windows service or an executeable.

You can close the IE window with javascript
window.close();
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:clearchannel
ID: 24393694
"Window.close" requests the user click OK to close the window and thus is no good.
I aim to run the stored procedures or aspx page from the server i a scheduled task, I am not an application developer I don't deal with exe's and windows service is surely overkill?
A batch file shoul do the trick but I have no idea how to start one.
 
 
0
 
LVL 8

Accepted Solution

by:
Faheem Shaikh earned 1000 total points
ID: 24393724
Ok, you will need to create two files:
1. A Batch file: Named yourbatch.bat

@echo off
setlocal

sqlplus DBUSERNAME/DBPASSWORD@DBSERVER @Your_SQL.sql
endlocal  
2. SQL script file: Named Your_SQL.sql
spool SQL_Log.log
Your sql code here
spool off
exit
 Once you do this, schedule the yourbatch.bat file to run every night using windows scheduler.
0
 

Author Comment

by:clearchannel
ID: 24393771
Ok thats cool and I undertsand, but the path for the sql file is relative to the batch file...is this correct you presumely save them together in the same place ?
0
 
LVL 8

Expert Comment

by:Faheem Shaikh
ID: 24393798
That's right, if thats not the case for you then you can specify the full paths.
0
 

Author Comment

by:clearchannel
ID: 24393951
Cool. thanks. Once I have finalised my sql code I'll give it a go.
 
0
 

Author Comment

by:clearchannel
ID: 24430490
Ok i am using your code but I DOS is telling me it can not find SQLPLUS/OSQL...
Now the bat and sql files are running fomr my webserver, BUT there is no SQL on this server - is this the issue and if so how do I fix it? Do I have to copy the bat and SQl files to the SQL 2000 Server?
Thanks
0
 
LVL 8

Expert Comment

by:Faheem Shaikh
ID: 24430512
You need to install sqlplus on the server where the bat and sql are residing.
0
 

Author Comment

by:clearchannel
ID: 24430582
sqlplus is oracle only as far as I am aware though?
0
 
LVL 8

Assisted Solution

by:Faheem Shaikh
Faheem Shaikh earned 1000 total points
ID: 24430776
Ooops! my assumption was for Oracle.
Ok, never mind, u will need to change the bat file slightly.
Instead of this: sqlplus DBUSERNAME/DBPASSWORD@DBSERVER @Your_SQL.sql
Use this: sqlcmd -S ServerName\InstanceName  -U UserName -P Password -i Your_SQL.sql -o Output.txt

Bascally use SQLCMD utility instead of SQLPLUS.
0
 
LVL 8

Expert Comment

by:Faheem Shaikh
ID: 24430826
This will help: http://www.sqlcmd.org/?p=17
 
0
 

Author Comment

by:clearchannel
ID: 24432089
Ok I seem to have hit a snag.  :(
I know my SQL queries work as I have tested these thoroughly over the last few days on different data and servers, but for some reason the sql is not being executed properly; rows are not being deleted.
Any ideas? (output.txt is empty)

.BAT FILE
---------
@echo off
setlocal 
sqlcmd -S localhost -d POPView  -U Web_user -P Internet -i "E:\SQL Data\Scripts\deleteduplicates.sql" -o Output.txt
endlocal
 
 
.SQL FILE
---------------
spool SQL_Log.log 
 
DELETE FROM dbo.MOMsImages WHERE ID NOT IN
(
        SELECT ID FROM
        (
                SELECT MAX(ID) as ID, count(ID) as IDCou, PictureName, Country from
                (
                SELECT img.ID AS ID, img.PictureName as PictureName, img.Country as Country FROM dbo.MOMsImages AS img
                ) AS tab GROUP BY PictureName, Country
        ) outerTab
)
UPDATE dbo.SearchLog SET LastRecordID = (SELECT MAX(ID) FROM dbo.MOMSImages) WHERE SearchLog.TableName = 'MOMSImages'
 
 
DELETE FROM dbo.ImageLibrary WHERE ID NOT IN
(
        SELECT ID FROM
        (
                SELECT MAX(ID) as ID, count(ID) as IDCou, PictureName, Country from
                (
                SELECT img.ID AS ID, img.PictureName as PictureName, img.Country as Country FROM dbo.ImageLibrary AS img
                ) AS tab GROUP BY PictureName, Country
        ) outerTab
)
UPDATE dbo.SearchLog SET LastRecordID = (SELECT MAX(ID) FROM dbo.ImageLibrary) WHERE SearchLog.TableName = 'ImageLibrary'
 
spool off
exit 

Open in new window

0
 
LVL 8

Expert Comment

by:Faheem Shaikh
ID: 24432139
Are you getting any errors? If you can post the error, may be i can suggest.
0
 
LVL 8

Expert Comment

by:Faheem Shaikh
ID: 24432152
Run the batch file from the command prompt, that way you will know if the batch file is breaking some where.
0
 

Author Comment

by:clearchannel
ID: 24432190
When I run it in a doc command prompt, it runs, and no errors are reported...
Ia m guessing if it couldn't find my SQL file it would say so?
And if it errored on the sql it would report that too?
 
0
 
LVL 8

Expert Comment

by:Faheem Shaikh
ID: 24432255
You are right....so that means your sql is fine, bat is fine, but the sql logic is such that it doesn't find any records to be deleted or your statement is not getting committed. You need to investigate on that lines.
0
 

Author Comment

by:clearchannel
ID: 24432671
Thing is I have run the logic against the SQL Tables in question using Query Analyzer and it returns the correct return, but when run the bat file calls the sql file it doesn't seem to execture the code or something.
Is there any other way I can check what is going on, like a trace?
0
 
LVL 8

Assisted Solution

by:Faheem Shaikh
Faheem Shaikh earned 1000 total points
ID: 24432719
Ok, from your sql file remove the below lines and try again:
spool SQL_Log.log
spool off
exit

 
0
 
LVL 8

Expert Comment

by:Faheem Shaikh
ID: 24432738
There is no spool command in SQL server. Its only in Oracle. My bad. Sorry about that.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question