SQL Server 2014 :: Using (Try-Catch) And (Rollback) On Read Only Stored Procedure?
Apr 30, 2015
In general as understand if we have a stored procedure that does operations like inserts or updates, it makes perfect sense to use a rollback operation within a transaction.
So, if something goes wrong and the transaction does not complete, all changes will be reverted and an error description will be thrown for example.
Nevertheless, does using a rollback within a try catch statement, make sense in a read only stored procedure, that practically executes some dynamic sql just to select data from some tables?
I have around 100 Stored procedures, all of them read only. Today a colleague suggested adding try-catch blocks with rollback to all of them. But since they are just selecting data, I don't see a clear benefit of doing so, compared to the hassle of changing such a big number of SP's.
I am using sql2k5. I just wanted to throw an error from stored procedure with some message to C# to rollback my transaction. Here is how i wnated to do ( in sequence )
C# ===== Open a connection Begin the transaction Execute the command
In the Stored Proc =========== do multiple operations one by one if error stop processing further Throw the error C# ======== if exception rollback the transaction else commit the transaction
I have tried using raise error in stored proc but never thrown exception
Can any one let me know how to achieve this scenario??
I am curious what other people have done to implement read-only routing for a large number of procedures.
Basically figuring out when to call procedures that are read-only with read-only intent.
We have a user application that passes an encrypted string to a web service that directs it to our SQL Servers.
I've been tasked with finding a way to make this happen without changing the application.
The only thing I have been able to come up with is writing something (which I did) that will identify whether something is read-only or not and storing a big list.
Then having the web service look up the given procedure and adding the intent where needed.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
The following two stored procedures works fine. I need to add try catch blocks in the stored procedures. How to do it? Pls modify my stored procedure with try and catch blocks and in the catch block i need to call the Procedure called ErrorMsg which contains error severity. Procedure 1------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo -- =============================================-- Author: C.R.P. RAJAN-- Create date: March 26, 2008-- Description: PROCEDURE FOR IMPLEMENTING SOFT PURGE-- =============================================ALTER PROCEDURE [dbo].[SP_SOFTPURGE] @POID INTASBEGINUPDATE POMASTER SET FLAG=1 WHERE POID=@POIDSELECT * FROM POMASTER WHERE FLAG=0END Procedure 2-----------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo -- =============================================-- Author: C.R.P. RAJAN-- Create date: March 26, 2008-- Description: Stored Procedure for Hard Purge-- =============================================ALTER PROCEDURE [dbo].[SP_HARDPURGE] @PoId INTAS BEGIN TRAN INSERT INTO ARCHIVE SELECT * FROM POMASTER WHERE POID = @POID IF @@ERROR != 0 BEGIN ROLLBACK TRAN RETURN END DELETE FROM POMASTER WHERE POID = @POID IF @@ERROR != 0 BEGIN ROLLBACK TRAN RETURN ENDCOMMIT TRANSELECT * FROM POMASTER
The other day I was asked to build a SQL Server process to terminate blocking sessions that could be safely destroyed in order not to drain necessary instance resources. The solution is made of below stored procedure and by a SQL Server Agent jobs that runs every 3 minutes just to invoke the sproc. The T-SQL code should be easy to read and has plenty of remarks.
USE [<<yourDBAgoodStuffDatabase...>>] GO SET ANSI_NULLS ON GO
I don't specify the following statements in SP. begin transaction commit transaction rollback
In sp, there are a lot of insert and update. If i execute SP and stop it in half way. will it rollback all update and insert statement? also, will it rollback if it fails?
Hello, I have stored procedure that when executed it will check to see if a given name is found in the database, if the name is found, I would like to have it continue on to do its work, however if the name is not found, I would like it to raise an error and then stop execution at that point, however, the way it is currently working is, if the name is not found, it catches the error, raises it and then continues on and tries to do its work, which then it bombs out because it can't. I wasn't sure if there was a way to stop the execution of the procedure in the catch statement. I don't think I want to raise the error level to 20-25 because I don't want to drop the connection to the database per say, I just want to halt execution.
Here is a simple example of what I have:
Code Snippet begin try
if not exists (select * from sys.database_principals where [name] = 'flea')
raiserror('flea not found', 16, 1) end try begin catch
@ErrorMessage = error_message(), @ErrorSeverity = error_severity(); raiserror(@ErrorMessage, @ErrorSeverity, 1); end catch go
begin
print 'hello world' end
At this point, if the user name (flea) is not found, I don't want it ever to get to the point of 'Hello World', I would like the error raised and the procedure to exit at this point. Any advice would be appreciated on how to best handle my situation!
Hi all, I have a program that needs to delete records, then re-insert new records to a table. But I need to rollback the transaction IF the insert is not success (error occured). The delete and insert are in 2 difference stored procedure (which have rollback transaction) that calling from 1 stored procedure. My problem is that if Insert is not successful, but the records already deleted previously. How can we rollback the delete transaction when insert is not successful? Note: if possible, I don't want to delete the records AFTER the insert is successful, or create a temp table to stored the deleted records ======================================= create stored procedure combine_sp as begin call delete_sp -- have rollback transaction in the delete_sp -- what to do if following has error occured, but we already deleted the records above? call insert_sp -- have rollback transaction in the insert_sp end go ======================================= Thanks a lot.
I'm updating some tables in a subscriber database with a stored procedure. After the tables get updated I'd like to sync them with the other subscriber dbs and the publisher db in that same stored procedure.I can do it manually in SSMS with the View Synchronization method. Are my only alternatives a batch job or C#?
We are collecting values in a string format with delimeteres and sending to DB .We would like to insert the data in Bulk insert format rather than splitting the same and then inserting..
In sql 2014 can we archive the same..sample format currently we are getting the client is like this is
Saleid$ salename$month$year$totalsale#Saleid$salename$month$year$totalsale# has a dataset.
I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:
create TRIGGER [trg_connection_MyServer] ON ALL SERVER WITH EXECUTE AS 'Smith' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'Smith' begin if exists (SELECT 1 FROM sys.dm_exec_sessions WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') ) ROLLBACK; end
I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.
begin try declare @param2 int begin transaction exec proc2 @param2 commit transaction end try begin catch if @@trancount > 0 rollback transaction end catch
i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.
I have a need to insert stored procedure output a table and in addition to that add a datetimestamp column.. For example, Below is the process to get sp_who output into Table_Test table. But I want to add one additional column in Table_test table with datetimestamp when the procedure was executed.
I've managed to get my Instance connected to the internet and I can query it using PHP and SQL, I can also look at views with no problem.
I have it working as an "ADODB.Connection" and like I said it connects and I can query data and display results.
Now I have coded a Stored Proc "GetMonthDays" in Sql Server:
Which returns days 1 through xxx in a given month and also returns the Day name eg... Sat for each date
2014-01-01 Thurs 2014-01-02 Fri etc...
It works perfectly and very fast so All cool with that side BUT... I want to be able to query the Database through a Stored Proc, I've spent all day trying to find a way to get this to work and I've hit a wall
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
I am trying to execute a stored procedure to update a table and I am getting Invalid Object Name. I am create a cte named Darin_Import_With_Key and I am trying to update table [dbo].[Darin_Address_File]. If I remove one of the update statements it works fine it just doesn't like trying to execute both. The message I am getting is Msg 208, Level 16, State 1, Line 58 Invalid object name 'Darin_Import_With_Key'.
BEGIN SET NOCOUNT ON; WITH Darin_Import_With_Key AS ( SELECT [pra_id] ,[pra_ClientPracID]
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
I have simple query which creates tables by passing database name as parameter from a parameter table .
SP1 --> creates databases and calls SP2--> which creates tables . I can run it fine via SSMS but when I run it using SSIS it fails with below error .The issue gets more interesting when it fails randomly on some database creation and some creates just fine .
Note** I am not passing any database of name '20'
Exception handler error :
ERROR :: 615 :: Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ---------------------------------------------------------------------------------------------------- SPID: 111 Origin: SQL Stored Procedure (SP1) ---------------------------------------------------------------------------------------------------- Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ----------------------------------------------------------------------------------------------------
Error in SSIS
[Execute SQL Task] Error: Executing the query "EXEC SP1" failed with the following error: "Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.I have sysadmin permission .
Is there a way using a stored procedure in a local database to add a record to a database executing in a cloud environment when both entities reside in different domains?
What is the best approach for a read only copy of a database that is ~ 1TB. The primary database is fed nightly with an ETL process. We are currently trying to duplicate the ETL to read only server but that process is not going well. So we are looking at other options to let SQL make the copy.
The primary database is on a Win12R2 with SQL 12 or 14, a 2 node A/P failover cluster.
The read only copy will be on a Win12R2 with SQL 12 or 14. It is not a requirement to fail over to the read only copy if the primary should go down.
What would best the approach to accomplish the end result?
Assume if i have a connection(Application intent readonly) starts with reading, writing and again reading data for a report. how it will works in SQL 2014 Always availability on?
Hi,Using Chandu Thota's book Mappoint.net I attempted to set up a findnearby query using his example Implementing Spatial Search Using SQL Server. The book uses C# I have attempted to convert the code to call the stored procedure as follows: TryDim units As Int16 = 0 Dim cmd As SqlCommand = New SqlCommand("FindNearby") 'Assign input values to the sql command cmd.CommandType = CommandType.StoredProcedurecmd.Parameters.AddWithValue("@CenterLat", latitude) cmd.Parameters.AddWithValue("@CenterLon", longitude)cmd.Parameters.AddWithValue("@SearchDistance", distance) cmd.Parameters.AddWithValue("@Units", units)Dim con As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("VehicleMarshallConnectionString").ConnectionString) If con.State = ConnectionState.Closed Then con.Open() If con.State = ConnectionState.Open Then cmd.Connection = conDim dreader As SqlDataReader = cmd.ExecuteReader() If dreader.Read() Then If Not dreader.IsDBNull(1) ThenDim ordnum As String = dreader(1).ToString End If End If End IfIf con.State = ConnectionState.Open Then con.Close() Catch ex As Exception Finally End Try
The code returns no values but when I execute the stored procedure from the server explorer in visual studio I get the records I would expect in the output window. How would I read the returned values from my code. Output Window Running [dbo].[FindNearby] ( @CenterLat = 53.10531, @CenterLon = -2.4769, @SearchDistance = 100, @Units = 0 ). ID ordnum ProxDistance ----------- ------------------------- ------------------------- 1 009999/USP 0 2 109999/USP 14.5971373147639 3 119999/USP 57.7144756947325 No rows affected. (3 row(s) returned) @RETURN_VALUE = 0 Finished running [dbo].[FindNearby]. Regards, JoeBo
Hi I have decided to approach the problem here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103310
by using a Stored procedure, someone mentioned it's possible for a SP to read the contents of a text file ?
IS it ?
For example i have a DB table with fields to,from,subject,body
problem is the body field references a file (full path) and i need to extract all info into a txt file the full txt body, so can i perform some sort of inner operation on the select body .. part of the SP ?
I am using SQL 2000 and would like to dynamically assign ODBC data source to transform data task. Do you have a stored procedure to perform read/write from/to ODBC data source? I would like to input data source and table name.
I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?
