Rolling Back Transactions For Multiple Stored PRocs

Oct 5, 2007

I have a quick question.

I need to execute some stored procedures in certain steps... all performing some inserts/updates

What i need is, a mechanism, by which i can roll back to the previous state if i encounter an exception (either in the app or SP)

so, if the my first two SP execute fine, perform their functionality like insert/update, and the third one fails...

how can i roll back to the initial state in ASP.NET.

would appreciate any info.
or redirection to the the location where i can look up some info on that.

AJ

View 1 Replies


ADVERTISEMENT

Rolling Back Multiple Transactions?

Apr 4, 2013

I have a stored proc that is executed from within another stored proc, both of these procs run transactions in them that update data.

Now if either of these 2 SPs fail i want to be able to roll back the transactions that have occured so that the data doesn't change.

View 5 Replies View Related

Transactions And Stored Procs

Mar 8, 2004

All,

I'm relatively new to stored procs (not to SQL or SQL Server) and I am trying to get transactions to work within a stored proc. Here is the code:


(
--define the parameters that are needed
@userID char(32),
@userName varchar(50),
@status char(10),
@type char(10),
@password varchar(50),
@firstName varchar(100),
@lastName varchar(100),
@email varchar(200),
@domain varchar(50),
@pwdExpiry int,
@badLogin int,
@lastLogin datetime,
@full varchar(8000),
@read varchar(8000),
@noaccess varchar(8000)
)
AS
BEGIN TRAN tmp1
--First, insert the user into the system
INSERT INTO ptsUsers(UserID, UserName, Status, Type, Password, FirstName, LastName, Email, DomainName, PasswordExpiry, BadLoginAttempts, LastLoginDate)
values(@userID, @userName, @status, @type, @password, @firstName, @lastName, @email, @domain, @pwdExpiry, @badLogin, @lastLogin);

--Now, we need to add the function access edges for the user
declare @arrayValue char(32)
declare @rightID char(32)
declare @sepPos int
while patindex('%,%',@full)<>0
BEGIN
select @sepPos = patindex('%,%' , @full)
select @arrayValue = left(@full, @sepPos - 1)
-- replace the value with an empty string
select @full = stuff(@full, 1, @sepPos, '')

--create and parse the new id
declare @strID char(32),@tmpStr varchar(40)
set @tmpStr = newid();
set @strID = REPLACE(@tmpStr,'-','')

--get the access right id for full control
select @rightID = (Select AccessRightID from ptsAccessRights where Name = 'Write')
if(@rightID IS NOT NULL)
--insert the records that are full access
INSERT INTO ptsFunctionAccessEdges(FunctionAccessEdgeID, FunctionID, AccessRightID, UserID)
VALUES(@strID, @arrayValue, @rightID, @userID)
else
RAISERROR(50100,15,1)
END
COMMIT TRAN tmp1
IF @@TRANCOUNT > 0
RAISERROR(50101,15,1)
ROLLBACK TRAN tmp1


The transaction does not rollback when any errors occur at all. Any advice/help?

View 3 Replies View Related

SQL Server 2014 :: Stored Proc Timing Out Halfway Through - Rolling Back Without Transaction

Jul 7, 2015

We have a high volume database with 1000's of users and 1000's of procs. Our application enforces a 20 second timeout on all connections.

We can't adjust the 20 seconds - this is a business rule.

It sometimes happens that a proc does not complete within 20 seconds and then times out halfway though. This causes data inconsistency where 50% of the code was saved to the DB and 50% was not - seeing that a stored proc is not transactional and therefor does not roll back the code.

We can't put the code in a TRANSACTION in order to roll back when a time out occurs, because this causes exclusive locks on the tables.

So I guess my question is:
Is it possible to undo/rollback all the code in a proc when a timeout occurs - without using a TRANSACTION? And if a TRANSACTION is the only way - how do I avoid the exclusive lock and blocks?

View 4 Replies View Related

Rolling Back SQL Server 2005 Databases Back To SQL Server 2000

Sep 22, 2006

Does anybody know of a way to rollback SQL Server 2005
databases back to SQL Server 2000? Is there a way of doing it without
resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database
to a SS 2000 instance without any issues.



I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I
want to rollback to SS 2000, which was a lot more stable. I am having
several major issues that are affecting my whole company's day-to-day
operations and the managers are not happy. Some of the issues include
night time batch running very sluggish for no apparent reason. This is a
biggest problem because it only occurs once or so a week and causes a disturbance
with the daily activities when the night time processing isn€™t completed on
time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I
don't believe it is a matter of my application needing to be retuned because if
that was the case, then why isn't it running sluggish every night? Also,
it's never the same day that the sluggish behavior occurs. If it was occurring
on the same night, then I would have something to investigate within our
application, but it doesn't. Another issue that I am having involves a
night time job that restores a copy of the production database to the Data
Warehouse server to be used for updating the data warehouse. Again, most
of the time it runs great (~2 1/2 hours), but once or twice a week, it goes
stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening
the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the
database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended
state. What's a suspended state and how can I get it out of suspended
state? I can find no information about suspended state in BOL. I
did a Google and nothing came up. If this suspended state was put
in for security reasons, great, but then tell me how I can remove the suspended
state. I am also not happy with the
fact that I can't get accurate information about the queries that are actively
running at that particular moment. In SS 2000, when I noticed high CPU
usage on the server, I would run the sp_who2 active stored proc and it would
show me all the active thread and how much CPU it was consuming. I would
then find the running threads with the highest CPU numbers and investigate the
query and see if we could improve it. Now in SS 2005, I get in the same
situation and run the sp_who2 stored proc, and there is no smoking gun.
All of the active threads are showing very little CPU usage, which I am very
suspect of. What the heck happen to sp_who2? I looked at some of
the other ways of looking at running processes (i.e... sys.sysprocesses) and
they don't appear to be giving the information that I need.



I am very unhappy and I just want to roll back to SS 2000 and wait a couple of
years before I upgrade to SS 2005.


Dave Brown

View 1 Replies View Related

Rolling-back

Mar 27, 2001

If I issue -
update x set y = 'z'

is there a way to undone/roll-back what was done in SQL 7 ?


Thanks very much.

-FB

View 2 Replies View Related

Rolling Back DTS

Sep 8, 2004

I have created a DTS Package that does the following:
1 Delete all data from table 1 (SQL Task)
2 Import Data from .csv file into table 1 (Data import)
3 insert records from table 1 into table 2 when they dont exist in table 2 (SQl Task)

This all works fine, but now i want a rollback function in step 1 and 2.
So when Step 1 is finished and something goes wrong i want the deleted data back.

Can somebody help me?

Thanks in advance.

View 1 Replies View Related

How To Copy Multiple Stored Procs

May 29, 2008

I would like to copy the following 3 stored procedures from Database1 to database2 on the same server:

USP_UpdateDocRepository

USP_GetUA

USP_GetCN

Is there a copy command available via query window.

Thanks.

View 1 Replies View Related

Rolling Back In SQL Server

Feb 19, 2004

One of our engineers here by mistake deleted some very important data without any begin trans block and we need the data back very badly.. We have not closed the session as of now.. The engineer was logged in as sa to the DB.

Can someone please help soon on urgent basis...

View 6 Replies View Related

Rolling Back Inserts

Feb 16, 2006

I'm performing a stored proc that has 4 inserts. I only want the inserts to complete as a batch. If one fails, I want to rollback the whole transaction. Does anyone know the syntax?? :)

View 4 Replies View Related

Rolling Back Inserts

Mar 3, 2006

I want to insure that each of my insert statements in a stored proc are rolled back if any of the inserts fail. I already have the below statement with error handling but is this correct? It seems to me that all the steps should be made part of an entire transaction so if one part fails then it all fails. Can someone help me w/ the syntax of this??

CREATE PROCEDURE Addrecords AS

--USERS
INSERT INTO [Production].[dbo].[USERS]([LastName], [UserName], [EmailAddress], [Address1], [WorkPhone], [Company], [CompanyWebsite], [pword], [IsAdmin], [IsRestricted],[AdvertiserAccountID])
SELECT dbo.fn_ReplaceTags (convert (varchar (8000),Advertisername)), [AdvertiserEmail], [AdvertiserEmail],[AdvertiserAddress], [AdvertiserPhone], [AdvertiserCompany], [AdvertiserURL], [AccountNumber],'3',0, [AccountNumber]
FROM production
WHERE not exists (select * from users Where users.Username = temp.AdvertiserEmail)
AND validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END


--PROPERTY
INSERT INTO [Production].[dbo].[Property]([ListDate],[CommunityName],[TowerName],[PhaseName],[Unit], [Address1], [City], [State], [Zip],[IsActive],[AdPrintId])
SELECT [FirstInsertDate],[PropertyBuilding],[PropertyStreetAddress],PropertyCity + ' ' + PropertyState + ' ' + PropertyZipCode as PhaseName,[PropertyUnitNumber],[PropertyStreetAddress],[PropertyCity], [PropertyState], [PropertyZipCode],'0',[AdPrintId]
FROM [Production].[dbo].[Temp]
WHERE AdvertiserEmail IS NOT NULL
AND validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END

View 2 Replies View Related

Transaction Not Rolling Back

Mar 19, 2007

I have a stored procedure that calls another stored procedure with thefirst stored procedure opening a transaction:BEGINSET XACT_ABORT ONBEGIN TRANSACTIONdoes various updates/insertscalls 2nd stored procedure to proccess updates/inserts common to manyother stored proceduresdoes more various updates/insertscommitENDThe problem I'm having is that within the 2nd stored procedure is thatif it encounters an error, it does not roll back the entiretransaction and I finish up with missing records in the database. Amusing this in the 2nd stored procedure:if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendWhat could the problem be? From what I've read, it seems as thoughyou can't have an open transaction within one sp that calls another spand it maintains the same transactoin? Is this corrrect?I tired the following too, and I still couldn't get it to work. Anyideas anyone?************ sp 1 ***********Declare @AddressError char(3)SET XACT_ABORT ONBEGIN TRANSACTIONexec Sp2@AddressError OUTPUT,@variable1,@variable2,etc. etc************** sp 2 *****************@AddressError char(3) OUTPUT,if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendSET XACT_ABORT ONBEGIN TRANSACTIONprocess updates/insertsSet @AddressError = 'no'Commit******** back to sp 1************If @AddressError <'no'BEGINrollback transactionENDcontinue doing updates/insertscommit

View 1 Replies View Related

Rolling Back Transaction

Jun 16, 2007

Hi,



I have a case where I read from SQL Server DB and write to a flat file.

I have one Data Flow Task inside which I have a OLEDB source component that feeds rows to a script component that writes to a flat file. I have set the txn attributes for the container to "Required" and "Read committed" . But I find that rows are written to flat file even when I throw an exception from my script component. Question is how do I prevent rows from being written to the flat file if error/exception happens. I want the whole process to be in a single transaction.



Thanks

View 3 Replies View Related

Granting Permission On Multiple Stored Procs

May 25, 2007

Is there a better way of granting permission to a stored proc for a selected user other than (enterprise manager) select sp then accessing propertys then permissions, then user?

View 4 Replies View Related

Rolling Back SQL Server 2000 SP 4 To SP 3

Feb 15, 2006

We have installed SQL Server 2000 Service Pack 4 recently and have had various issues with some of our Stored Procedures. (which we didn't have with SP 3)

We are looking at our options regarding rolling back to Service Pack 3.

Can this be done ?
&
If so, is there a method documented on how to do this ?

Any advice would be appreciated.

View 3 Replies View Related

My Transaction Isnt Rolling Back When It Should

Apr 9, 2008

Ive got an insert statement that fails, and below that I have code like the following:


IF @@ERROR <> 0
BEGIN
-- Roll back the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error INSERT INTO Address.', 16, 1)
print 'test was here'
RETURN
END


However, there is now rollback and the inserts below it are going through.

what do i have wrong ?

View 5 Replies View Related

Rolling Back Security For Some Dba's Using Sql 2005

Mar 29, 2007

Don't know if this is possible or not:



Goal: set security on running specific stored procedures based upon user login and databse access

I have some DBA's who want to retain full control of databses / stored procedures as they now have but I want to restrict or rollback some of the changes that were implemented when the sql 2005 was set up. The sql 2005 EE is in a clustered system and uses Mixed Mode Authentication.

An example of what I want to restrict: The DBA's want to be able to view and kill processes for the different databases that are installed under their instance. The problem is other customer databases are also under the same instance.

Is their a way I can combine or have the stored procedure sp_lock only show the processes for the databases they have access to based upon their login? My concern is they will kill a process and affect the other customers.



Thanks for any info or pointers.



carl

View 1 Replies View Related

Mirroring And Rolling Back Transaction Log

Mar 17, 2008

hi,
i want to create a disaster recovery site, to which i can fail over (not automatically),
and also to have the option to return the database to a point in time?
for example,
if my principal server fails in 17:00,
i want to have the option to make the mirror server available for users from 17:00 (or at least close to that time),
and also to be able the return to the data from 16:00 (in the mirror site).
Is it possible, and what is the best way to do it?

Thanks.

View 1 Replies View Related

Exporting Multiple Stored Procs To Separate Files

Jan 16, 2008

We just upgraded from SQL 2000 to 2005. Under 2000, I could export multiple stored procs to separate windows files.
Is there a way to do this under 2005 without exporting 1 proc at a time?

View 5 Replies View Related

Rolling Back Sql 2000 To A Point In Time

Aug 9, 2005

I would appreciate a bit of advice here. There is a largish complaint here regarding a cutomer who has entered data in 1 of our online forms, but we suspect this was then overwritten by a cached form she also had open.
Anyway to cut a long story short i need to roll back the database to a point in time.

Not something i have ever had to do.

The row where the id field is equal to 3352, and this would have been written to the database at 14:58:36 on 08-aug-2005. This was over written by the data in row with id 3380 at about 11am this morning, now is this is a live database on a webserver so i cant compromise its uptime as it get written to about 3 times a minute, so how should i go about this?


Anyway, what do you think?


Cheers

View 2 Replies View Related

Rolling Back Transaction Logs Database

Feb 3, 2004

I have a Development database and I want to roll it back to Monday morning. I backed up the database and used the command:

RESTORE DATABASE ITTEST
FROM ITTEST20040203
WITH NORECOVERY
GO

RESTORE LOG MyNwind
FROM ITTEST20040203
WITH RECOVERY, STOPAT = 'FEBRUARY 2, 2004 09:00 AM'
GO

The transaction logs have never been truncated.

But it does not seem to have worked. It this the best way to do a roll back the database or have I missed out something.

thanks.

View 2 Replies View Related

Rolling Back Transaction After .Commit() Runs Successfully

Jun 10, 2007

I have a page that runs a transaction correctly after a button click.
I want to allow someone to click a button that rolls back the transaction, after the transaction runs on the first button click.
I can also successfully roll back within the first button click.
 I'm getting a NullReference error when trying to access SqlTransaction.Rollback() outside the button click.
 If SqlTransaction.Commit() completes without error can SqlTransaction.Rollback() be called after?
I tried making 'trans' a more global variable and it still gave me the error.
Button Click 1:
Dim trans As SqlTransaction
trans = connection.BeginTransaction()
try
'run SQL Statement
trans.Commit()
Catch e As Exception
 trans.Rollback()
throw e
end try
 
Button Click 2:
trans.Rollback()
 

View 4 Replies View Related

Multiple Stored Procedures And Transactions

Dec 7, 2007

I have a simple question: Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?
In other words, I have a stored procedure that performs some legacy stuff.  I do not want to rewrite that logice, execpt I am putting it into a Stored Procedure since it currently is embedded SQL.
To support newer functionality, I am writing data to additional new tables.  However, I do not want the *new* things to occur if there is an error.  This is easy enough, I dont call the Stored Procedure for the new functionality if there was an error. However, if there was not an error and the newer stored procedure is called AND there is an error in the new stored procedure, I want to ROLLBACK the changes from the proceeding stored procedures.
To my understanding, I can name transactions but that is only to indicate in the logs what transactions have failed.
I thought about not using transactions for any of the individual stored procedures and calling them from a main stored procedure.  The main stored procedure will have a BEGIN TRY and an END TRY (I am using SQL Server 2005) and at the top (right after the try) I will have a BEGIN TRANSACTION.  In the BEGIN CATCH I will have a ROLLBACK and at the end I will have a COMMIT.  If any of the stored procedures fail at any point it will cause the catch to occur thus rolling back all of my transactions.  This would be the easiest way but I still need to deal with the question of, "What happens if any single stored procedure is called?"  I guess I could have try and catches in each individual stored procedure for that.
I just want to make sure I am not overlooking something simple.  I think I have the right idea by doing it this way I mentioned above.  Because this is critical billing processing I just want to have some reassurance that this would work.
Thank

View 4 Replies View Related

Stored Procs: Is There A Way To Divide Up (or Namespace) Groups Of Stored Procs

Jan 15, 2008

Is there a way to namespace groups of stored procs to reduce confusion when using them?

For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.

View 1 Replies View Related

Transactions - Back To Basics

Oct 30, 2006

Hi all,

I am having no end of trouble with transactions in the package which i am building. I now just want to go back to basics and see if someone can tell me where i should set specific transaction options.

Firstly, my package runs a for each loop which loops through a directory of directories. In each of the sub directories there are 2 files. The first steps in the loop are to check if a folder has been processed previously, if so then it moves it to a specified directory. The reason that this is done first is that i cannot move the directory whilst it is being read in the foreach loop, so i pass the path to the next iteration of the loop. There is another file system move directory task outwith the foreach loop to deal with the last directory.

Once this has been done, i parse the file name of the xls file within the directory to get a serial number which is assigned to a variable.

The next step is where i envisage that the transactions should be implemented:
I have a sequence container which contains 2 data flow tasks to be run in parallel. each of these reads data from a seperate work sheet in the xls file. and writes it to a database table. Each dataflow task consists of an excel source task, derived column task, look up task (used to derive an ID from the serial number stored in the variable), and an oledb destination.

Upon completion, if the sequence container fails i want to set the destination folder path to the qurantine location. If it succeeds i want to copy the csv file contained in the same directory to a seperate location and then set the out put folder to the archive location.

What i need to know is where do i set the transaction option if i want to roll back the data that has been inserted into the database if either data flow task fails?

Please somebody help, as this is not working at all.

Many thanks,

Grant

View 18 Replies View Related

Is There Any Way I Can Roll Back In Sql Server Without Using Transactions ?

May 5, 2005

Hi,
i wanna know how to use rollback in sql server without actually using transaction...
is there any way i can do that?
thanx in advance
 

View 3 Replies View Related

Problem With Using Stored Procs As I/p To Another Stored Procs

May 7, 2004

HI,

CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID

END

IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?

CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1


ST......1,
ST......2,


END.

BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-


CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID

END

SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END

WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .



CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC MY_PROC


ST......1,
ST......2,

END.

BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?

THANKS.

View 4 Replies View Related

Nonqualified Transactions Are Being Rolled Back. Estimated Rollback Completion: 100%.

Apr 30, 2007

Hi all,



Sometimes when I do "alter database ABCD set partner failover" I get the following message: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.



In 99 percent of the cases after such message the first attempt to use an open connection would also raise an error such as "Exception: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"



After the first error all subsequent queries would run perfectly.



What am I missing?



Avi

View 4 Replies View Related

How Do You Roll Back Transactions For A Single Table From The Current Transaction Log?

Nov 1, 2007



Goofed up and ran an update query. It messed up all the data in a single table. I'm trying not to restore the table from a previous backup since the backup is more than 20 GB. It's going to take forever to restore it. Any advice would be much appreciated!

Thanks!
Charles.

View 3 Replies View Related

SQL 2012 :: Rolling Up Multiple Row And Columns Into A Single Cell NULL Error

Jul 28, 2015

So I have a problem with combining cells: some of the cells are blank and some of the cells have NULL value.

The combined blank cells return perfect.

But the combined cells in which 1 or more contains NULL will return as NULL

0-4139-00-01OPTIONAL EQUIPMENT 139 - A -
0-4139-00-02NVG EQUIPMENT 139 - A -
0-4139-00-03NVG EQUIPMENT 139 - A -
0139-01-01BRAKE RESERVOIR NULL

A part of the script which i use is:

STUFF((SELECT ', ' + i.ac_type + ' - '
+ i.range_or_single+ ' ' + ' - ' + i.serialno_to
FROM msc_item_effectivity i
inner JOINmsc_item h
ONb.taskcard_verno_i = h.taskcard_verno_i
WHERE h.itemno_i = i.itemno_i
FOR XML PATH('')), 1, 1, '')

i.ac_type = 139
i.range_or_single = A
i.serialno_to = blank or NULL

I want the result of the blank value to be the same as the NULL value.

0139-01-01BRAKE RESERVOIR 139 - A -

View 2 Replies View Related

Executing Multiple Procs At One Go

Jan 12, 2004

Hi,

I would like to execute multiple stored procs by calling a single script say A. I want to A to execute on a nightly basis. The multiple stored procs are bulk inserts into separate tables. I want to be notified of the error if one among the many stored procs fail when I call A. But if one fails, I don't want A to fail. The remaining stored procs should execute. Does anyone have a script wriiten for A? I guess A is just not about -

EXEC SP1
EXEC SP2
EXEC SP3
EXEC SP4
EXEC SP5

Thanks

View 4 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Stored Procs Are BAD!! BAD, I Tell You!!!

Feb 5, 2005

almost choked when i read the following recent post on The Daily WTF (http://thedailywtf.com/) the other day --

Logical Tiers? That Makes No Sense! (http://thedailywtf.com/ShowPost.aspx?PostID=28959)

i don't think i can do justice to how utterly stupid that stored procedure is

read the comments and have a laugh

one of the points made was that stored procedures are bad

this twigged something in my memory, so i dug around in my bookmarks, and sure enough, here's another decent discussion about stored procs --

Stored procedures are bad, m'kay? (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx)

enjoy!

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved