?
Solved

vb.net scheduled task to check database data?

Posted on 2009-05-15
21
Medium Priority
?
537 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
While rebooting windows server 2003 server , it's showing "active directory rebuilding indices please wait" at startup. It took a little while for this process to complete and once we logged on not all the services were started so another reboot is …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

741 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