| Author |
Topic |
|
yz
Starting Member
16 Posts |
 Posted - 2005-12-07 : 16:35:59
|
| I am using a sp to insert new records to a MS SQL DB table. The sp gets the input parameters from a C# Winform application using SqlCommand. By calling ExecuteNonQuery(), a new record can be appended that the bottom of the table. The code work fine when saving data infrequently.However, when I try to call this method once a second to save data in the table, sometimes(not always), there is no new record for as long as 5 minutes while my log file shows the program did call the method every second. I try to catch SQLException and genaral Exception in my C# code, could not get any. The CPU usage is less than 10% on the computer.Can anyone tell me how to debug and fix the problem?Does MS SQL have ability to make transaction on once a second base? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-07 : 16:48:12
|
| >>Does MS SQL have ability to make transaction on once a second base?Absolutely.>> I try to catch SQLException and genaral Exception in my C# code, could not get any. Sounds like you may have an issue with your "once a second" c# code. Use Sql Profiler to capture what is actually being sent to sql server from you application.If profiler shows that the call is making it to sql server, try executing the SP from a query analyzer window. How long does it take to execute? If it's longer than a few milliseconds, post the SP code here for us?Be One with the OptimizerTG |
 |
|
|
yz
Starting Member
16 Posts |
Posted - 2005-12-07 : 18:11:33
|
| Thank you very much for your help.I run a Profile to trace the SP. It was executed every 2~3 seconds. Each time took 0~16 ms. Meanwhile I had my C# side of code logged the event. It shows every second being called at least once. So every 2~3 seconds I missed 2 records. I post the c# code and SP as following. Can you see what is wrong?C# codem_Conn = new SqlConnection(m_connectionString);m_Conn.Open();m_cmd = m_Conn.CreateCommand();m_cmd.CommandType = CommandType.StoredProcedure; m_cmd.CommandText = "sp_InsertRecordDish" + iDishNum.ToString()+"DisplayedData";//name of the stored procedure SqlParameter SystemOperatingMode = m_cmd.Parameters.Add("@p_SystemOperatingMode",SqlDbType.TinyInt);SystemOperatingMode.Direction = ParameterDirection.Input;SystemOperatingMode.Value = m_strDisplayedDataiDishNum].SystemOperatingMode;..... //Add the rest of 300 fieldsm_cmd.ExecuteNonQuery();//to log the execution by a text file:TextWriter tw = new StreamWriter("c:\\DataLog.txt",true); //true for appendtw.WriteLine(DateTime.Now + " Dish Number: " + iDishNum.ToString());tw.WriteLine("");tw.Close();SP code:*********************************************************CREATE PROCEDURE sp_InsertRecordDish0DisplayedData(@p_SystemOperatingMode tinyint, ....//the rest of 300 input parameters)ASINSERT INTO Dish0DisplayedData VALUES(getdate(),@p_SystemOperatingMode, ....//the rest of 300 input parameters)GO |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-12-07 : 18:30:57
|
| It sounds like your proc is not getting executed if the Profiler is only seeing a SP call every 2-3 seconds.I noticed the the code you posted does not appear to be making any check to see if it is getting an error when you execute the SP, so you might want to add some error checking.CODO ERGO SUM |
|
|
|
yz
Starting Member
16 Posts |
Posted - 2005-12-09 : 13:44:59
|
| I do have SqlException and a general Exception in my C# code in the same method that I did not post here.They did not catch any error. Normally if I have a wrong SP name or other C# side exceptions, it will catch. Are you saying an error checking of the SP from C# side? Sorry I did not know how to do it. Can you give me an example? Thanks a lot. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-09 : 14:02:44
|
I'm more of a sql guy than a c# guy but...I think what MJV MVJ is saying is that there isn't any try/catch blocks around your database call where you can handle and log the exception gracefully. It's also a good idea to (open, execute, and close) your sql connection together inside a try/catch block. In your catch block, check the status of your connection and close it if it's still open. Don't open your connection as soon as you create it, wait until you're ready to make the call, then close it as soon as you're done with it.Be One with the OptimizerTG |
|
|
|
yz
Starting Member
16 Posts |
Posted - 2005-12-09 : 17:04:21
|
| I do have my DB calls in the try block but did not catch any exception. I am wondering where the missing records go. Anywhere in SQL side can detect and record the calls other than the profile? The C# program and DB is in the same computer. The problem is that sometimes it misses as many as 5 minutes of records. That is 300 of them while my C# side treats it as normal since it did not know the records were not inserted! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-12-09 : 18:36:19
|
| You said:"I run a Profile to trace the SP. It was executed every 2~3 seconds. Each time took 0~16 ms. Meanwhile I had my C# side of code logged the event. It shows every second being called at least once. So every 2~3 seconds I missed 2 records."If the call really isn't showing up in Profiler, then your application is not making the call, so something is wrong.Are you testing to make sure you are really getting a connection to the server? Are you testing the return code from the stored procedure? Does your stored procedure check for errors on the insert? Does the stored procedure check the input parameters to make sure they are valid? Have you tried having the stored procedure return a a result set or output parameter, so that your application could verify that you made a valid call?You application is either making the stored procedure call or it is missing it, so you need to determine that first. If it isn't making the call, the problem is in your application.CODO ERGO SUM |
|
|
|
|
Lost once a second records