I suggest that you log the changed data, instead of the commands to update it. You can find the old data in the deleted table and the new data in...

The timeout can be adjusted, but it depends on how you are executing the procedure.

If you are using Query Analyzer you can find it in Tools / Options /...

Read an excellent article about error handling, by SQL Server MVP Erland Sommarskog:

http://www.sommarskog.se/error-handling-II.html#SP-check

Razvan

Please ignore my last post. I just saw that you have problems also when you run the queries from the SQL Query Analyser, so it is not an MS Access...

If you build the queries from an Access MDB with linked tables to SQL Server there is one important thing to know: some complex queries may get executed on the...

Read this fine article by Erland Sommarskog, SQL Server MVP:

http://www.sommarskog.se/share_data.html

You will find in this article some other ways share data between stored procedures.

Razvan

This is a trick question: If you use "SET XACT_ABORT ON" the batch will be aborted in case of an error, therefore there isn't any return value to be checked....

CREATE TABLE YourTable (
 YourID int IDENTITY PRIMARY KEY,
 Something varchar(50) NOT NULL UNIQUE,
 IsDeleted char(1) NOT NULL DEFAULT ('N') CHECK (IsDeleted='N' or IsDeleted='Y'),
 DateDeleted datetime NULL
)
GO
INSERT INTO YourTable(Something) VALUES ('Razvan')
INSERT INTO YourTable(Something)...

Winash, your solution won't work. The user would still get the error "The current user is not the database or object owner of table 'dbo.Products'. Cannot perform SET operation.".

I see...

I think that you mean that "the tables in question have identity columns" (normal primary keys should not be a problem). In this case, you might consider using SET IDENTITY_INSERT.

Razvan

PS....

Another solution that may be a little better (in terms of performance) would be:

SELECT p.PersonID, p.PersonName, a.ApartmentNo, 
 r1.RentAmount as LatestRent, r1.DateOfRentTakingEffect
FROM Rent r1 
INNER JOIN Apartment a ON r1.ApartmentID=a.ApartmentID
INNER JOIN...

Another solution is to create the temporary table outside the dynamic SQL and insert the data with INSERT (instead of SELECT INTO), like this:

Select * into #tmpClient...

If you want cannot change the code, but you want to ignore divide by zero errors (to get null instead), you can use the ARITHABORT and ANSI_WARNINGS settings, like this:

SET...

You cannot fetch into a table variable because (1) the type of the variables in a fetch statement must be compatible with the type of the columns from the select statement...

If you wrote the procedure (or if you can modify it), an easier approach would be to write something in a table when the procedure starts and delete it when...