asked on

VBScript to query SQL Databse and runs as a service

We have an SQL 2005 DB having automated time and attendance for workers in a factory. It is required to print automatically the name of anyone that spent more than 13 hours inside the plant.
The query itself is very simple, but how to automate it?
I thought of creating a vbscript for that and keep it running (as a service preferably) but do not know how.

any suggestions?

Please note that I have no experience in vb scripting, and any other suggestions (like some triggers on the database itself) is appreciated.

Thank you.

SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ehab Salem

ASKER

I believe this way is far better than what I thought of, but is it possible to let SQL server print on a network printer?
I know how to do this is an Oracle DB, but have little experience in SQL Server.

Would you give more guidance?

SOLUTION

Bhavesh Shah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RiteshShah

>>I believe this way is far better than what I thought of, but is it possible to let SQL server print on a network printer?
I know how to do this is an Oracle DB, but have little experience in SQL Server.<<

I don't think so there is something like that in SQL Server. you can't take print out directly from SQL Server, not at least from any native way. I don't know whether any 3rd party tool is available for that or not.

Ehab Salem

ASKER

RiteshShah;

Can you guide me how to make it send an email?

Brichsoft: I prefer the job method and will give it a try, then I will check your links. Thank you for your help.

RiteshShah

here with I am giving you few links as a tutorial.

http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

above link will show you how to configure database email, once you configure, you can set SQL Server agent job, in which you can call SP "EXEC sp_send_dbmail " to send details.

few more links.

http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/

http://weblogs.sqlteam.com/randyp/archive/2009/03/04/60863.aspx

http://classicasp.aspfaq.com/email/how-do-i-send-e-mail-from-sql-server.html

with VBScript.

http://www.mssqltips.com/tip.asp?tip=1625

Ehab Salem

ASKER

OK, I created the report and configured it in the reports server and it is sending emails.
But, I still have one problem:
this report usually is empty and I want it to be sent only when there is data. How can I do that?

RiteshShah

you have to call that report and email stuff only if there is any record. put everything in IF condition.

Ehab Salem

ASKER

I could not find anyplace to put that IF!
I used the reports manager to make this job.

ASKER CERTIFIED SOLUTION

Ehab Salem

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial