|
This is for SQL Server 2008:
Will the stored procedure be rolled back if the db server gets an error (server crash or performance problem) in the middle of it's operation?
This is the idea:
- stored proc spTest contains 2 insert statements
- exec spTest
- spTest executes 1st insert statement
- stored procedure still running... but server gets some problem that's unrelated to this stored procedure, which causes the stored proc to stop (e.g. server crash)
Will the data in the 1st insert statement be rolled back?
Thanks in advance!
Rafferty
|
|
|
|
|
|
What do you mean?
Currently, these 2 insert statements are not enclosed in try catch blocks, but I didn't think that I need to because the error is caused by something else that is external to this stored procedure.
Or is there some DB configuration-wide that I should set?
Rafferty
|
|
|
|
|
Rafferty Uy wrote: Will the data in the 1st insert statement be rolled back?
How about wrapping them both in a single transaction[^].
I are Troll 
|
|
|
|
|
Hi Eddy, thanks for answering. I understand that it's better to wrap both in a single transaction.
My question is really what happens if i don't. I want to know the normal behavior of sql server in such a case, not how I can improve the code.
I'm investigating a bug and I suspect that this was the cause of the problem... but it seems far fetched because the server didn't crash... it just experienced some performance issues.
Thanks again!
Rafferty
|
|
|
|
|
Rafferty Uy wrote: My question is really what happens if i don't.
It will write up to the point of the crash, and execution will halt. No automatic recovery,
If it crashes midway, the first sproc might be saved, the second might not. If it crashes during a write-operation, your entire database might become corrupt.
I are Troll
|
|
|
|
|
Just the answer I'm looking for! Thanks a lot Eddy! This really helped 
Rafferty
|
|
|
|
|
hello folks,
I got headache to figure out SSIS SQL 2005 error when windows task scheduler is triggered...
I setup the batch file like below to run it every morning,
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /f c:\BAT\SSIS\DATATRAN.dtsx >> c:\BAT\LOG\DATATRAN.log"
it had been working until last month, now I can run this batch file without any problem manually and no problem to run the scheduled task manually, but it shows errors when run the scheduled task every morning... SSIS is stuck in the process 43% completed,
Is there anyone can give me tips?
thanks!!
|
|
|
|
|
MARS (Multiple Active Results Sets), which simplifies the application design process
ALTER rename process for symmetry in renaming databases
Application and Multi-server management for Data-tier Applications which further streamlines application design and enables deployments of database applications directly from SQL Server 2008 R2 and Visual Studio 2010 to SQL Azure for database deployment flexibility
Details in the Blog Post http://blogs.msdn.com/sqlazure/archive/2010/04/16/9997517.aspx
|
|
|
|
|
|
Vista/64
VS2008
I'm to add a SQL Server database object to my project, but when I try, I get this error message:
Connecting to sql server files (*.mdf) require sql server express 2005 to function properly.
Well, it is installed, and when I run express manager studio, everything seems to be fine. I can also create connections in the server explorer in vsiual studio. When I googled the error, there was exactly one hit that I can't browse top (because of the restrictions on our network).
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Can you post either the link you can't get to or the exact search phrase in case someone CAN get to it and then render help?
Tim
|
|
|
|
|
Hi John,
IMO the relevant part of http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/6615124e-5501-4fb2-b498-638d3a7f01c6[^]
is this:
I encountered the same error message while trying to create a data connection from Visual Studio 2005 using the Server Explorer.
This is what I did:
# 1. Started Visual Studio 2005
# 2. Opened the Server Explorer (View | Server Explorer)
# 3. Right-clicked the Data Connections node
# 4. Selected Create New SQL Server Database... from the context menu
# 5. Selected my computer from the Server Name dropdown list
# 6. Selected authentication method (Use Windows Authentication)
# 7. Typed a name in the New Database Name field
# 8. Hit the OK button
That's when I received the following error message
An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error:40 – Could not open a connection to SQL Server)
It made absolutely no sense. I wasn't trying to connect to a remote computer. I was simply trying to connect to the SQL Server 2005 Express Edition which was installed on my local computer during the Visual Studio 2005 installation.
After numerous attempts on several computers, I finally realized what I was doing wrong. By adding the database instance name to the server name (# 5), it actually worked. Instead of selecting <server name=""> from the dropdown list, I entered <server name="">\SQLExpress manually. That was obviously the problem.
SQL Server 2005 Express Edition is obviously installed as a named instance, since one has to provide the instance name in addition to the server name (even when there is only one instance running on the requested server).
|
|
|
|
|
We're not running sqlexpress...
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
|
Does it relate to SQL Express? 
|
|
|
|
|
Luc Pattyn wrote: Does it relate to SQL Express?
|
|
|
|
|
|
CREATE PROCEDURE .InsertTwoTables
@UserName nvarchar(50),
@ShopName nvarchar(50),
@Email nvarchar(300) ,
@time1 datetime,
@CategoryNameE nvarchar(50),
@ItemKey nvarchar(50),
@ItemKeyNameE nvarchar(50),
@CurrentQty nvarchar(50),
@SalesPrice nvarchar(50),
@Quantity nvarchar(50),
@Total nvarchar(50)
AS
BEGIN TRANSACTION
Set Nocount On
DECLARE @OrderID int
//primary key table
INSERT INTO TblOrder(UserName, ShopName, Email,time1) VALUES (@UserName,@ShopName, @Email,@time1)
SELECT @OrderID=@@IDENTITY
//foreign key table
INSERT INTO TblDetails (OrderNo, CategoryNameE , ItemKey ,ItemKeyNameE , CurrentQty, SalesPrice, Quantity , total)
VALUES (@OrderID, @CategoryNameE , @ItemKey, @ItemKeyNameE, @CurrentQty, @SalesPrice, @Quantity , @Total)
COMMIT
when iam inserting values with these procedure
it is saving ordersno also repeated
i want one orderid with multiple values in TblDetails
can you give me example which helps me
|
|
|
|
|
The way the stored procedure is currently there will only be a one to one relationship. I would suggest removing the time from TblOrder then before an insert check to see if the values UserName, ShopName, Email already exist.
Something like (not tested)
DECLARE @there INT
SELECT * FROM TblOrder
WHERE UserName = @username
AND ShopName = @shopname
AND Email = @email
SET @there = @@rowcount
IF @there = 0
BEGIN
-- Your insert
END
Hope this helps
djj
|
|
|
|
|
Try using SCOPE_IDENTITY rather than IDENTITY - check BOL for the (significant) differences.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi guys,
I'm trying to get 2 values from a stored procedure: one telling me the execution was successful and another for telling me what the newly inserted ROW ID is. I currently have the following:
<br />
<br />
USE [xyz]<br />
GO<br />
SET ANSI_NULLS ON<br />
GO<br />
SET QUOTED_IDENTIFIER ON<br />
GO<br />
<br />
ALTER PROCEDURE [dbo].[spName]<br />
(<br />
@vchName VarChar (50),<br />
@intSuccess Numeric(9) OUT<br />
)<br />
AS<br />
SET XACT_ABORT ON<br />
BEGIN TRANSACTION <br />
<br />
INSERT INTO Table<br />
(<br />
vchName <br />
)<br />
VALUES<br />
(<br />
@vchName<br />
)<br />
<br />
IF @@ERROR <> 0<br />
BEGIN<br />
SELECT @intSuccess = 0<br />
ROLLBACK<br />
END<br />
ELSE<br />
SELECT @intSuccess = 1 <br />
<br />
<br />
COMMIT TRANSACTION<br />
SET XACT_ABORT OFF<br />
<br />
RETURN @intSuccess<br />
<br />
Currently it returns the value "@intSuccess" ONLY. I want to add the line "SELECT @@IDENTITY AS inLatestID" right under the insert statement so I can get the latest ID value of the record that was just newly inserted.
I think I will need to return another value alongside @intSuccess, but I'm not sure how that works in the Stored Procedure
|
|
|
|
|
You can have multiple out parameters, but you do not need to return success/failure as an out parameter. You can use standard try/catch (sql 2005/8) or raiseerror for earlier versions to check for errors. Check out BOL for more details
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I suppose that makes sense. Normally I check if the return value is > 0, I suppose if a Stored Procedure executes successfully, then my parameter should be > 0 and I can just check that.
Thanks for your reply.
|
|
|
|
|
A stored proc (or any other sql query) returns zero for success and non-zero for an error
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
