SQL Server Performance

stored procedure not able to insert rows

Discussion in 'SQL Server 2005 General Developer Questions' started by mrkcse, Sep 5, 2008.

  1. mrkcse New Member

    Iam writing the following procedure for inserting phone call details into sqlserver.Iam using c# language on aspnet2.0 platform and sqlserver as backend. In the debugging the code everything is fine and showing that record has inserted into procedure and cmd.ExecuteNonqQuery also returning value 1 each time. While accessing sqlserver phonedetails table it is not showing any records which are inserted.previosly this application was running fine and now also procedure running fine.dont know what the problem is.Here is the procedure...help me
    CREATE PROCEDURE [dbo].[Ph_Insert]
    @Extension char(10),
    @CallDate datetime,
    @CallTime Datetime,
    @Phone char(20),
    @Duration smallmoney,
    @Trunk int,
    @calls int,
    @CallType char(20),
    @cost smallmoney
    AS
    declare @type char(1)
    IF @CallType = 'Inc Call'
    SET @type = 'I'
    ELSE IF @CallType = 'Missed Call'
    SET @type = 'M'
    ELSE SET @type = 'O'
    select * from PhoneCalls where Extension = @Extension and CallDate = @CallDate and CallTime = @CallTime
    IF @@rowcount = 0
    BEGIN
    INSERT INTO PhoneCalls
    (
    Extension,
    CallDate,
    CallTime,
    Phone,
    Duration,
    Trunk,
    calls,
    CallType,
    Flag,
    InsertedOn,
    DtandTime
    )
    VALUES
    (
    @Extension,
    @CallDate,
    @CallTime,
    @Phone,
    @Duration,
    @Trunk,
    @calls,
    @cost,
    @CallType,
    @type,
    Getdate(),
    convert(char(11),@CallDate,106) + ' ' + convert(char(10),@CallTime,108)
    )
    END
    ELSE
    BEGIN
    UPDATE PhoneCalls SET
    Extension = @Extension,
    CallDate = @CallDate,
    CallTime = @CallTime,
    Phone = @Phone,
    Duration = @Duration,
    Trunk = @Trunk,
    calls = @calls,
    cost = @cost,
    CallType = @CallType,
    Flag = @type,
    UpdatedOn = GetDate(),
    DtandTime = convert(char(11),@CallDate,106) + ' ' + convert(char(10),@CallTime,108)
    WHERE Extension = @Extension and
    CallDate = @CallDate and
    CallTime = @CallTime
    END
  2. mrkcse New Member

    Is there any way to forcefully insert records using stored procedure? help me
  3. preethi Member

    A common issue with your stored procedure is it is multi statement and no eror handling is maintained. ALso keep SET NOCOUNT ON as the first statement so that you will not be creating unnecessary roundtrips.
    when you do these changes, you may be able to view the problem.
    To answer your second question, there is no way on inserting a row against the existing primary keys unique keys foreign keys etc. Either you need to delete them or diable them inorder to make a forcefull insert.
  4. preethi Member

    BTW, welcome to the forum!
  5. mrkcse New Member

    Thanks preethviraj for welcoming.I havent used stored procedures more.Now i have to use in my project, could you please tell me sample error handling of stored procedure if possible.Iam trying the suggestion u have given meanwhile
  6. FrankKalis Moderator

    Can you please post the code you use to access the table?
    Also, error handling differs depending on which SQL Server version you use. In SQL Server 2005 you can use a TRY-CATCH construct similar to C#, in SQL Server 2000 have a look at @@ERROR.
  7. mrkcse New Member

    here is the sql profiler trace
    exec Phone_Insert @Extension='901 ',
    @CallDate=''2008-02-09 00:00:00:000'',
    @Calltime=''1900-01-01 16:25:28:000'',
    @Phone='9869313128',
    @Duration=$0.1000,
    @Trunk=702,
    @calls=1,
    @cost=$0.8500,
    @CallType='Local'
    if we execute in the sqlserver query window it is showing error as
    Incorrect syntax near '2008'.
    even in my dotnet coding if i substring also iam getting same trace as calldate ''2008-02-09 00:00:00:000''
    using the dotnet coding iam importing .csv file of phone calls history and inserting using phone_insert stored procedure
    string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" +
    System.IO.Path.GetDirectoryName(FileName) + ";" + "Extended Properties="Text;HDR=NO;FMT=Delimited"";
    System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConnectionString);
    System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("SELECT * FROM "
    +System.IO.Path.GetFileName(FileName), conn);
    System.Data.OleDb.OleDbDataReader dr;
    conn.Open();
    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    SqlConnection Sconn = new SqlConnection("data source=192.168.1.12; initial catalog=website; user id=sa;
    password=password");
    SqlCommand Scmd = new SqlCommand("Phone_Insert", Sconn);
    //in while loop
    Extension = dr[11].ToString().Trim();
    CallDate = dr[21].ToString().Trim();
    CallDate = CallDate.Substring(0, 20);
    help me
  8. mrkcse New Member

    thanks for the support, i got the solution.
    actually in the import file list the date format is the conflict.It is going to update statement and updating
    09-feb-2008 instead of inserting actuall 02-sep-2008.I have been checking 02-sep-2008 records only till i got
    the solution.
    thanks once again.
    ramakrishna
  9. FrankKalis Moderator

    You're welcome! [:)]
  10. bhuvnesh_dba New Member

    hi preethi ...cau provide me notes of MCDBA 2005 online certificati0on ..i will be greatful to you for this.....
    my id : bhuvnesh.dogra@gmail.com
  11. preethi Member

    Hi,
    I am clueless about your question. DId I mention anything about MCDBA or 2005 in my reply? Is there any relationship for that to this topic?
    If you want to do a Certification (BTW, MCDBA is no more available. The new certification is called MCITP) The best method is studying. I have done my certificates almost an year ago using the books available (and books online). I can't share them online.
    What do you mean by notes? The notes I take are useful for my job and not limited to the certification. I write them in note books (not computers!) I am not is a position to share them.
    If you are asking me to provide the questions, I ma sorry. We should not share the questions, you should not relay on questions to take an exam.
    Hope this helps!

Share This Page