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.
View 9 Replies
ADVERTISEMENT
Sep 27, 2007
Hi,
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??
~Mohan Babu
View 5 Replies
View Related
Aug 6, 2014
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.
View 5 Replies
View Related
Jan 29, 2015
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],
[Code] ....
View 9 Replies
View Related
Mar 26, 2008
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
View 1 Replies
View Related
Nov 10, 2006
Hi ,
In the below procedure in emp table name column is not null.
so when i try to update null value it should rollback previous transactions and should not commit.
but it is updating the previous queries and raising error on 3rd query. not rolliing back. I need to rollback previous queries.
the xactstate i get here is 1.
Please help me with this.
ALTER PROCEDURE [dbo].[tran]
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
declare @name varchar(50)
set @name = null
update dept set dname='ddd' where id=10
update emp set name='eee' where id=354
update emp set name=@name where id=354
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (XACT_STATE())=-1 ROLLBACK TRANSACTION
END CATCH
END
venp---
Hi ,
View 6 Replies
View Related
Dec 6, 2007
Hello, everyone:
For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.
ZYT
View 14 Replies
View Related
Nov 5, 2014
I received a request to create a stored proc with following:
I have a view with the following columns
(table1 - AppCode, AgencyID, CompnyID, CustCode, CustVal)
I have a proc that will take the following parameters and return all matching rows (AppCode, AgencyID, CompnyID, CustCode(optional))
The trick: Any customcode with the CompnyID should override the AgencyID parameter.
View 3 Replies
View Related
Jul 4, 2015
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
[code]...
View 3 Replies
View Related
Mar 5, 2008
Hi everyone,
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?
cheers
View 1 Replies
View Related
Mar 3, 2008
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
declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
select
@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!
Thanks,
Flea
View 5 Replies
View Related
May 6, 2007
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.
View 3 Replies
View Related
Oct 24, 2014
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#?
View 1 Replies
View Related
Sep 23, 2015
I was trying to create stored proc
[code="create procedure dbo.sp_table1
@idint
as
begin
updatetable1
setisarchived = 1,
[Code] ....
But the query was continuously blocking the query below
updatetable1
setisarchived = 1,
modtime = getdate()
whereid = @id
andisarchived = 0
I was not sure, why the create procedure statement is blocking the update statement.
View 1 Replies
View Related
Aug 17, 2015
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.
View 6 Replies
View Related
Sep 8, 2015
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.
View 8 Replies
View Related
Nov 24, 2014
create proc proc1 (@param1 int)
as
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.
View 3 Replies
View Related
Jun 22, 2015
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.
insert into Table_Test execute sp_who
View 2 Replies
View Related
Oct 6, 2015
Is it possible to find out the last executed date for any stored proc in the database using system tables or writing any other query.
View 2 Replies
View Related
Jan 2, 2015
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
This T-SQL returns what it needs to
Begin
EXEC dbo.qselGetMonthDays '2015-01-01'
End
View 3 Replies
View Related
May 27, 2014
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?
View 6 Replies
View Related
Jul 23, 2014
I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
Now i want to call stored procedure in select statement because stored procedure return a single value.
I search on google and i find openrowset but this generate a meta data error
So how i can resolve it ???
View 7 Replies
View Related
Jan 21, 2015
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]
[code]....
View 2 Replies
View Related
Jun 18, 2014
I have two tables called ECASE and PROJECT
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 need to prevent the deadlock .
View 3 Replies
View Related
Mar 23, 2015
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 .
View 6 Replies
View Related
May 20, 2015
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?
View 2 Replies
View Related
Dec 11, 2014
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?
View 3 Replies
View Related
May 5, 2014
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?
View 3 Replies
View Related
May 6, 2014
we are about to start a logshipping with the secondary server in read only mode.
I just wanted to know the disadvantages of using a read only on the secondary server.
I mean whether the transactional logs will be restored periodically or it won't be restored till users are connected on the secondary server.
View 2 Replies
View Related
May 19, 2008
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
View 10 Replies
View Related
May 21, 2008
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 ?
View 2 Replies
View Related
Sep 25, 2006
Dear all,
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.
Thank you and Best regards,
Chaivat
View 5 Replies
View Related
Jun 25, 2007
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 ?
Thank you.
View 1 Replies
View Related
