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 HelpThe 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|||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|||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.|||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.|||If you aren't married to your original code, I would suggest:CREATE PROCEDURE dbo.PopulateArchive2
@.CustomerID int,
@.Comment varchar(100),
AS

BEGIN TRANSACTION

INSERT INTO into Archive
SELECT customerId, employeeId, firstName
, lastname, birth, InnDate
, Garanti, @.comment, GetDate()
FROM dbo.Customer
WHERE CustomerID = @.CustomerID

IF 0 <> @.@.error GOTO bail

DELETE FROM Customer
WHERE CustomerID = @.CustomerID

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
RETURN

bail:
ROLLBACK TRANSACTION
RAISERROR ('PopulateArchive failed!', 13, 1) -- 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|||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.|||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)|||Hmmm... Just for debugging, right before the COMMIT let's try addingSELECT * FROM Archive WHERE CustomerID = @.CustomerIDI'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|||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|||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|||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|||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|||My first guess would be that your Customer.InnDate column is a character, and the Archive.InnDate is a SMALLDATETIME or a DATETIME.

-PatP|||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|||Those darned computers... They keep doing what I tell them to instead of what I meant for them to do!

Anyway, I'm glad that you are on your way!

-PatP