|
Which is what I was trying to tell him. Play the SQL parser, read the date in the table.
9 times out of ten when you compare dates there is something with the full timestamp in the field that is messed up. Or maybe the field doesn't have a full date saved which causes comparisons to fail when you think they won't.
For example 22/04/2014 10:00:00 is greater than 22/04/2014 00:01:00 but depending on the way SQL comparisons work (I'm reaching into ancient history) it is not greater than 22/04/2014 because the parser interprets 22/04/2014 into 22/04/2014 12:00:00
sometimes you have to use tochar function to compare dates
|
|
|
|
|
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
|
|
|
|
|
|