|
Hi All,
Hope everyone is doing great!!!.
I am new to this group so any mistake in posting, i am sorry.
I have a scenario, We have 5000+ scheduled jobs in the scheduler tool and the back end for that tool is sql server. We are manually monitring the job status and failuers which is taking huge efforts.
For automating the monitoring process, we want to have Stored procedure which can be triggered when there is failure or status of jobs ran for every hour. I need help as how to have body of the message for reporting the job status and run times.
I got the SP, but how to get the body of the message is what i am stuck with. Below is link from where i check the code.
Please let me know if you need any information.
Thanks a lot for your help in advance.
Regards,
Deepa
http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/[^]
|
|
|
|
|
Member 10752578 wrote: but how to get the body of the message is what i am stuck with
It is one of the parameters to the stored proc.
Member 10752578 wrote: which can be triggered when there is failure
Might want to be careful with that. One failure is that it just didn't run. If it didn't run there is no way to generate a failure. Safer is to base results on missing successes.
|
|
|
|
|
Hi,
I am trying to insert recurring (bulk) insert into MySQL table but it's getting timedout.
I increased the connection timeout even to 800 but still having the same problem.
I noticed it's timing out at about 30-32 seconds and only approx 300 records will be inserted.
what could be the problem please?
here is my stored procedure.. try current date as current date and max date as same day next year or after two years.
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_new_job_order`(IN param_customer_id int, IN param_cleaner_id int, IN param_job_order_date date, IN param_job_order_date_to date, IN param_start_time time, IN param_end_time time, IN param_job_order_note text, IN param_is_recurring bit, IN param_max_recurring_date date, IN param_is_contract bit, IN param_contract_id int, IN param_total_hours decimal(11, 6), IN param_created_user int, OUT param_record_identity int)
BEGIN
UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;
INSERT INTO job_orders (history_status, status_user, status_date, customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_recurring, max_recurring_date, is_contract, contract_id, total_hours, created_date, created_user) VALUES ('New', param_created_user, NOW(), param_customer_id, param_cleaner_id, param_job_order_date, param_job_order_date_to, param_start_time, param_end_time, param_job_order_note, param_is_recurring, param_max_recurring_date, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
SET param_record_identity = LAST_INSERT_ID();
IF (param_is_contract = TRUE) THEN
BEGIN
UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_record_identity;
UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
END;
END IF;
IF (param_is_recurring = TRUE) THEN
BEGIN
REPEAT
SET param_job_order_date = DATE_ADD(param_job_order_date, INTERVAL 7 DAY);
INSERT INTO job_orders (history_status, status_user, status_date, customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_recurring, max_recurring_date, is_contract, contract_id, total_hours, created_date, created_user) VALUES ('New', param_created_user, NOW(), param_customer_id, param_cleaner_id, param_job_order_date, param_job_order_date_to, param_start_time, param_end_time, param_job_order_note, param_is_recurring, param_max_recurring_date, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
SET param_record_identity = LAST_INSERT_ID();
IF (param_is_contract = TRUE) THEN
BEGIN
UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_record_identity;
UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
END;
END IF;
CALL sp_add_event_log("JOBORDER", param_record_identity, param_created_user, "Job order was created.");
CALL sp_add_event_log("CUSTOMER", param_customer_id, param_created_user, "Job order was created.");
UNTIL param_job_order_date >= param_max_recurring_date
END REPEAT;
END;
END IF;
END
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: I increased the connection timeout even to 800 but still having the same problem.
The ConnectionTimeout[^] property specifies how long to wait while trying to establish the initial connection to the database.
Your code isn't timing out waiting to establish a connection to the database; it's timing out trying to execute the command. That means you need to change the CommandTimeout[^] property.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
|
No, that's a different error message.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
appreciate if you update that post if you have a solution,
Thank you so much
Technology News @ www.JassimRahma.com
|
|
|
|
|
Hi,
I am having the above error on my .net application. It only shows on customer screen only when I added 1600 customers to the customers but all other screens are fine
My connection timeout is 360 and I even tried to increase it to 800!
what could be the problem?
Thanks,
Jassim
Technology News @ www.JassimRahma.com
|
|
|
|
|
can anyone help please...
Technology News @ www.JassimRahma.com
|
|
|
|
|
hi
looking for someone the table with a database convert from one SQL to another example, user, forum and other
|
|
|
|
|
I suggest you get someone to help with your english first. The question needs some interpreting.
The forum does not supply service so the someone is not going to happen.
Converting between databases will depend on the vendor of the database so know which source and target database is going to help us help you.
http://www.sqlservercentral.com/[^] is another good resource for SQL Server and all the big vendors have similar support sites.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm looking for a database where I can directly send / receive TCP/IP commands to do manipulate the database with SQL commands. I doubt that there is anything like that out there since my Google searches didn't turn anything up, but I'm asking here in case I'm missing something...
I will never again mention that Dalek Dave was the poster of the One Millionth Lounge Post, nor that it was complete drivel.
How to ask a question
|
|
|
|
|
You'll have to write one. Most database-servers will not allow access without authentication. I think you should not be wanting it either.
If you want the same *with* authentication, you'd end up with Sql Server.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: I think you should not be wanting it either.
And I don't want - I was thinking about something that needs a previous authentication, and then SQL commands can be sent...
Eddy Vluggen wrote: If you want the same *with* authentication, you'd end up with Sql Server.
I don't like SQL Server
Seriously though, a cross-platform DB would be better...
Thanks for the answers, Eddy!
I will never again mention that Dalek Dave was the poster of the One Millionth Lounge Post, nor that it was complete drivel.
How to ask a question
|
|
|
|
|
Marco Bertschi wrote: And I don't want - I was thinking about something that needs a previous authentication, and then SQL commands can be sent... Aah, SSH/VNC to the server (a secure, authenticated connection) and see if you can execute sqlcmd[^].
It'd be a VERY basic UI, but it should work.
Marco Bertschi wrote: I don't like SQL Server
Seriously though, a cross-platform DB would be better... Cross-platform SQL is better; SQL92 is still a nice standard.
You're welcome
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Marco Bertschi wrote: I was thinking about something that needs a previous authentication, and then SQL commands can be sent...
Which is how every database that uses SQL works.
The MS SQL Server Management Studio manages the server which is not and cannot be the same as managing a database. Same is true of tools like sqlplus, Toad, MySQL Workbench, etc. One can use those to process SQL but other functionality cannot be controlled via SQL (because that isn't what SQL does.)
|
|
|
|
|
Hello,
Is there any impact if the log file auto-growth setting change from unrestricted to restricted?
Thankfully appreciated for the reply in advance!!
Regards,
Kyi Kyi
|
|
|
|
|
|
Thank you
|
|
|
|
|
Hi,
we r developing visitor tracking system,when avisitor come giving a card and he swips and enters inside ,when he releaving handovers the card to watchmen.after that there is a chance same card can be given to someother visitor.
mysql table is not having primary key.
table is like this:
cardid name intime outtime
--------------------------------------------
123 raj 1/2/2014 10:12 1/2/2014 11:00
234 rajee 1/2/2014 10:34
123 vani 1/2/2014 12:10
then here how can i get the last inserted row of id(123) to update the outtime
am using c# following code:
<pre lang="c#"> String intime = "";
String outtime = "";
String indatetime=System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
String outdatetime = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
using (MySqlConnection con = new MySqlConnection(ConnectionString))
{
String query = "select * from visitor where card_id=@id ORDER BY card_id DESC LIMIT 1";
MySqlCommand command = new MySqlCommand(query, con);
command.Parameters.AddWithValue("@id", txtAddVisitorCardId.Text);
con.Open();
using (MySqlDataReader rdr = command.ExecuteReader())
{
if (rdr.Read())
{
intime = rdr["in_time"].ToString();
outtime = rdr["out_time"].ToString();
}
}
}
if (((intime == "") && (outtime == "")) || ((intime!="")&&(outtime !="")))
{
using (MySqlConnection con = new MySqlConnection(ConnectionString))
{
String query = "insert into visitor(card_id,name,age,address,id_proof,contact_person,purpose,in_time) values(@id,@name,@age,@address,@idproof,@contact,@purpose,@in)";
MySqlCommand command = new MySqlCommand(query, con);
command.Parameters.AddWithValue("@id", txtAddVisitorCardId.Text);
command.Parameters.AddWithValue("@name", txtAddVisitorName.Text);
command.Parameters.AddWithValue("@age", txtAddVisitorAge.Text);
command.Parameters.AddWithValue("@address", txtAddVisitorAddress.Text);
command.Parameters.AddWithValue("@idproof", txtAddVisitorIDProof.Text);
command.Parameters.AddWithValue("@contact", txtAddVisitorContactPerson.Text);
command.Parameters.AddWithValue("@purpose", txtAddVisitorPurpose.Text);
command.Parameters.AddWithValue("@in", indatetime);
con.Open();
command.ExecuteNonQuery();
MessageBox.Show("Visitor Added Successfully");
}
}
else if ((intime != "") && (outtime == ""))
{
using (MySqlConnection con = new MySqlConnection(ConnectionString))
{
String query = "update visitor set out_time=@out where card_id=@id ORDER BY card_id DESC LIMIT 1";
MySqlCommand command = new MySqlCommand(query, con);
command.Parameters.AddWithValue("@id", txtAddVisitorCardId.Text);
command.Parameters.AddWithValue("@out", outdatetime);
con.Open();
command.ExecuteNonQuery();
}
}
}
|
|
|
|
|
|
i want the query plz help
|
|
|
|
|
Write your own code!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
On the last project I was on, we utilized MS Access and while I was for the most part able to get around in Access, I found Access performed very, very slowly for even simple tasks once the DB grew to a certain size, and I was not at all impressed with the Report builder as I found it was almost always easier to export the data to Excel to generate my reports.
Fast forward a few months and I am about to start a new project and I have been given quite a bit of say in how we set up our database. I do have a vague idea of how I would implement it but I am very curious to see others opinions as well to make sure I have all bases covered.
Here are the 'contraints' if you will for how the database will need to perform.
1. There will be a few technologically savvy employees (approx 4-5) on site and the remainder of the workforce will be fairly illiterate, technologically speaking (they will be able to surf the web, do data entry in spreadsheets, type documents, but not much else). All employees will need to be able to access the data from reports easily and quickly without having to memorize commands or know how to navigate a database etc. My fellow techies have experience mostly with MS Access, and a couple know SQL, I am more familiar with NoSQL type frameworks, but have a basic understanding of SQL and can get by in such a system.
2. Data will come from 4 sources.
a.) Primarily, most data will come via spreadsheets. These spreadsheets will often be fairly complex with multiple tables, hidden columns and rows, formulas, reference cells, etc.
b.) The next largest chunk of data will come from field reports hand written by the foreman on site.
c.) The next largest chunk will come from simple verbal communication, or emails, word documents, or even typed letters.
3. Data will need to auto-generate reports. The more autonomous this can be made, the better. We might have as many as 10 different daily reports as well as hundreds of weekly, monthly, and yearly reports that we will have to generate. Ideally, this could be performed by non-techie users simply by providing date ranges or other simple constraints, and having templates scoop the required data from the database.
4. Data will need to be backed up often (at least once maybe more per day, to prevent data loss or erroneous operations, and preferably will not interfere with it's usage at all. It needs to scale well, be reliable and not be subject to data loss, or easily let data fall between the cracks.
In terms of importance I believe it should be arranged in the following hierarchy-
1. Reliability (no data loss, works as expected, no crashes, bugs, or unexplainable quirks)
2. Ease of Use (both in terms of non-techies accessing the data and power users overseeing the data and building report templates)
3. Performance (scales well, doesn't take minutes to perform simple queries)
In my head I see a few potential ways of allowing this to happen but invariably I see potential pitfalls in any of my strategies. I would be thrilled to see a high-level concept of how someone with more DB experience than myself would tackle this situation.
|
|
|
|
|