Solved

vb.net scheduled task to check database data?

Posted on 2009-05-15
21
512 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
 

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 250 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 250 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 250 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 Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Learn about cloud computing and its benefits for small business owners.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now