myLittleAdmin Executes Insert Stored Procedure Twice

Discussion in 'Databases' started by Niyazi, Feb 19, 2010.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. Niyazi

    Niyazi

    Good Days,

    My environment is SQL Server 2008, and I am using the myLittleAdmin provided by Discount ASP.

    I have a simple INSERT stored procedure that says:

    Code:
    SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.spInsertEmail

   @Name varchar(50),
   @Email varchar(100),
   @IsActive bit

AS
BEGIN
   SET NOCOUNT ON;

   UPDATE dbo.tblEmails SET IsActive = 0

   INSERT INTO dbo.tblEmails (Name, Email, IsActive)
   VALUES (@Name, @Email, @IsActive)

END
    When execute the following query through the New Query page in myLittleAdmin, it writes the same record twice to the tblEmails table:

    Code:
    EXEC dbo.spInsertEmail
@Name = 'John Doe',
@Email = '[email protected]',
@IsActive = 1
    whereas if I execute the following query directly in the same place it only writes once as expected:

    Code:
    UPDATE dbo.tblEmails SET IsActive = 0

INSERT INTO dbo.tblEmails (Name, Email, IsActive)
VALUES ('John Doe', '[email protected]', 1)

    Is there a specific reason for that? Are we supposed to not to run stored procedures in New Query page of myLittleAdmin? or am I doing something here?

    Thank you for your help,

    - Niyazi
     
    Niyazi, Feb 19, 2010
    #1
  2. dmitri

    dmitri DiscountASP.NET Staff

    Try to execute your stored procedure inside an explicit transaction as follows:

    Code:
    BEGIN TRANSACTION
   EXEC dbo.spInsertEmail
      @Name = 'John Doe',
      @Email = '[email protected]',
      @IsActive = 1
COMMIT TRANSACTION
    and see if it helps.
     
    dmitri, Feb 19, 2010
    #2
  3. Niyazi

    Niyazi

    Yes, it helps. I appreciate your help Dimitri.

    Do I need to call my stored procs (EXEC dbo.spInsertEmail) in "codebehind" in this fashion, or is it only for the "myLittleAdmin usage"?

    Thanks again,

    - Niyazi
     
    Niyazi, Feb 20, 2010
    #3
  4. dmitri

    dmitri DiscountASP.NET Staff

    As a best practice, it is good to use the explicit transaction inside of your stored procedure for error handling and data integrity maintenance. You can define your business rules in your stored procedure and based on the results you can either commit or roll back the transaction and return error / information message. I am not sure why myLittleAdmin calls store procedure twice, but it turns out that you have to use explicit transaction when use myLittleAdmin interface and you are strongly advise (but not have to) use it in your regular T-SQL code anywhere else.

    The only downside of the explicit transactions is that you need to make sure you either commit or rollback every transaction you open. Uncommitted transaction prevents your transaction log automatic truncation. This T-LOG can grow without the boundaries and crash your database. You can always check if there is any uncommitted transaction by running the following code:
    Code:
    SELECT @@TRANCOUNT
     
    dmitri, Feb 22, 2010
    #4
  5. Niyazi

    Niyazi

    Thanks for your time and help Dmitri.
     
    Niyazi, Feb 23, 2010
    #5
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page