Link to home
Start Free TrialLog in
Avatar of clearchannel
clearchannel

asked on

vb.net scheduled task to check database data?

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?
Avatar of Faheem Shaikh
Faheem Shaikh
Flag of India image

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.
Avatar of clearchannel
clearchannel

ASKER

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
Avatar of Nasir Razzaq
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();
"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.
 
 
ASKER CERTIFIED SOLUTION
Avatar of Faheem Shaikh
Faheem Shaikh
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ?
That's right, if thats not the case for you then you can specify the full paths.
Cool. thanks. Once I have finalised my sql code I'll give it a go.
 
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
You need to install sqlplus on the server where the bat and sql are residing.
sqlplus is oracle only as far as I am aware though?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Are you getting any errors? If you can post the error, may be i can suggest.
Run the batch file from the command prompt, that way you will know if the batch file is breaking some where.
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?
 
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.
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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
There is no spool command in SQL server. Its only in Oracle. My bad. Sorry about that.