Solved

vb.net scheduled task to check database data?

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

 

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
 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Visual Studio hangs on running project 6 64
c#, case, if 4 22
powershell try catch  and $ErrorActionPreference = continue 6 32
aspx ascx, c# 7 21
Setting up a Microsoft WSUS update system is free relatively speaking if you have hard disk space and processor capacity.   However, WSUS can be a blessing and a curse. For example, there is nothing worse than approving updates and they just have…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 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