Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Unanswered: Help_with_Store_Procedure

    create Proc PopulateArchive
    @CustomerID int,
    @EmployeeID int,
    @Firstname varchar(50),
    @Lastname varchar (50),
    @birth smalldatetime,
    @Inndate smalldatetime,
    @Garanti smalldatetime,
    @Comment varchar(100),
    @ArchiveDate smalldatetime
    as Insert into Archive
    values (@CustomerID@employeeId,@Firstname,@Lastname,@Birt h,@Inndate,@Garanti,@Comment,@ArchiveDate)
    select CustomerID,Firstname,Lastname,birth,Inndate,Garant i from Customer where Customer=@ CustomerID
    Delete from Customer where CustomerID = @CustomerID

    exec PopulateArchive 1,2,'Geir','Sam','1956/03/10','2004/03/17','2001/03/25','b,k,0','Your account have been inactive ','2004/03/17'

    When I run this query I get this error Server: Msg 208, Level 16, State 1, Procedure PopulateArchive, Line 12
    Invalid object name 'Archieve'.

    What I want to achieved with this query is that when I delete a customer the deleted data will be moved to the Archieve Table. Please Help

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The error message shows one spelling (Archieve), while the stored procedure source code shows another (Archive). This makes me suspicious that either: a) something went wrong in the cut/paste, or b) some other code (a trigger?) is causing the error message.

    There is also no RETURN statement in your stored procedure, and no obvious batch marker (GO). This could cause a weird side-effect if you executed this script verbatim.

    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    18
    That's true PatP. I just drop the Store Procedure and then recreate it.I could run the query successfully . The customer ais deleted, But the query does not insert the deleted columns into the Archive Table.
    I suspect my query might be wrong

  4. #4
    Join Date
    Mar 2004
    Posts
    80
    Hi pat,
    Originally posted by Pat Phelan
    There is also no RETURN statement in your stored procedure, and no obvious batch marker (GO). This could cause a weird side-effect if you executed this script verbatim.
    -PatP
    intresting.
    "This could cause a weird side-effect if you executed this script verbatim."
    I am unaware of these things.
    could u please expalin here what actually those side effects are or do i need to start a new thread for that.

  5. #5
    Join Date
    Oct 2003
    Posts
    18
    Based upon the information supplied with the Script for my stored Procedure if you know where the error is, you can help to correct the script. All I need is a query to that delete a customer based on the customerID supplied and the deleted data inserted into the Archive Table.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you aren't married to your original code, I would suggest:
    PHP Code:
    CREATE PROCEDURE dbo.PopulateArchive2
    @CustomerID int,
    @
    Comment varchar(100),
    AS

    BEGIN TRANSACTION

    INSERT INTO into Archive
       SELECT customerId
    employeeIdfirstName
    ,     lastnamebirthInnDate
    ,     Garanti, @commentGetDate()
          
    FROM dbo.Customer
          WHERE  CustomerID 
    = @CustomerID

    IF <> @@error GOTO bail

    DELETE FROM Customer
       WHERE  CustomerID 
    = @CustomerID

    IF <> @@error GOTO bail

    COMMIT TRANSACTION
    RETURN

       
    bail:
    ROLLBACK TRANSACTION
    RAISERROR 
    ('PopulateArchive failed!'131)  -- Season to taste
    RETURN
    GO

    --  Demo the stored procedure
    EXECUTE dbo
    .PopulateArchive2 1'Your account have been inactive ' 
    Note that you need to test this in your environment. I made some assumptions, but I'd be surprised if those assumption cause any problems.

    This procedure will get the customer information from the customer table, avoid unnecessary recompilations (because it uses two part names), insure that either the whole process succeeds or fails as a unit (because of the transaction management), and log errors (due to the RAISERROR statement).

    -PatP

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The 'Invalid object name" error sometimes occurs because the table is not owned by dbo, and the owner name is not specified in the code.

    Check the ownership on "Archive" (I assume you just made a cut and paste error with the message). Also check it's permissions and make sure the login executing the procedure has rights to it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Oct 2003
    Posts
    18
    Originally posted by newbies
    That's true PatP. I just drop the Store Procedure and then recreate it.I could run the query successfully . The customer ais deleted, But the query does not insert the deleted columns into the Archive Table.
    I suspect my query might be wrong
    I used the Script that u supplied, the stored procedure run successfully. but the deleted Customer data is not inserted into the Archive Table:

    I get this two result when I ran the query using query analyzer. I suspect that something is missing in the Insert statement

    (1 row(s) affected)


    (1 row(s) affected)

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Hmmm.... Just for debugging, right before the COMMIT let's try adding
    PHP Code:
    SELECT FROM Archive WHERE  CustomerID = @CustomerID 
    I'd like to see what the stored procedure sees there. I'm wondering if you might have more than one archive table (one for DBO, one for your current user) or more than one transaction (one we created in the stored procedure, one that contains it that gets rolled back), or something else that is causing this behavior.

    Based on the query output that you posted, it sure looks like everything is running correctly. I have to suspect that we aren't looking at the right piece of this puzzle.

    -PatP

  10. #10
    Join Date
    Oct 2003
    Posts
    18
    I have modified the Stored Procedure by placing the Select * b/w the if and commit
    IF 0 <> @@error GOTO bail
    SELECT * FROM Archive WHERE CustomerID= @CustomerID
    COMMIT TRANSACTION

    I exec the SP and it return this error
    Server: Msg 295, Level 16, State 3, Procedure PopulateArchive, Line 10
    Syntax error converting character string to smalldatetime data type.

    I am trying to figure out what this error means, seems to be the columns that have date, I have smalldatetime data-type for all columns that have something to do with date
    Last edited by newbies; 03-28-04 at 11:24.

  11. #11
    Join Date
    Oct 2003
    Posts
    18
    Originally posted by newbies
    I have modified the Stored Procedure by placing the Select * b/w the if and commit
    IF 0 <> @@error GOTO bail
    SELECT * FROM Archive WHERE CustomerID= @CustomerID
    COMMIT TRANSACTION

    I exec the SP and it return this error
    Server: Msg 295, Level 16, State 3, Procedure PopulateArchive, Line 10
    Syntax error converting character string to smalldatetime data type.

    I am trying to figure out what this error means, seems to be the columns that have date, I have smalldatetime data-type for all columns that have something to do with date
    I have checked the datatype they all match I am still getting a syntax eror. the procedure can not be exec

  12. #12
    Join Date
    Oct 2003
    Posts
    18
    I have fixed the error regarding the datatype error, the problem was due to columns placement within the select staement. The problem is not yet fixed namely the deleted data are not inserted into the Archive table.
    Need help thanks for the input so far

  13. #13
    Join Date
    Oct 2003
    Posts
    18
    The stored Procedure is ok now. Because FK constraint between these tables prevent the stored procedure to run successfully, when I removed the FK, the deleted data are moved to the archive table. That's what it should do. mission Accomplished. Use this opportunity to thank Pat Phelan
    for the help rendered
    Last edited by newbies; 03-28-04 at 14:21.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first guess would be that your Customer.InnDate column is a character, and the Archive.InnDate is a SMALLDATETIME or a DATETIME.

    -PatP

  15. #15
    Join Date
    Oct 2003
    Posts
    18
    Yes I just check it out that was true. The query is wonderful, I am so impressed. I now move on to the next stage, developing the front end using VB.Net
    Thank u for the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •