Problem with stored proc, migrating from MS SQL 2000 to MS SQL 2005

I've been migrating some DB from MS SQL 2000 to MS SQL 2005 all works fine but I have one problem with stored procedures.

Even if there is actually no errors I get the message "The transaction ended in the trigger. The batch has been aborted.".

The record is succesfully deleted but still I get the message above. I know MS SQL 2005 added the try/catch block did they change something else that cause compatibility issues with 2000?

Here's a sample :
 ***

	DECLARE @Error bit
	DECLARE @ErrorMsg varchar (100)

	DECLARE @ContactIDToDelete int
	SELECT @ContactIDToDelete  = ContactId FROM deleted
		

	Select @Error = 0


	If (SELECT Count(CallID) FROM Support WHERE ContactId = @ContactIDToDelete ) > 0
	begin
		Select @Error = 1
		Select @ErrorMsg ='myerrormsghere'
	end


	if (@Error = 0)
	begin
		commit transaction
	end
	else
	begin
		rollback transaction
		raiserror (@ErrorMsg, 16,1)
	end

 ***

 Thanks!

 

0
outshined
2/16/2009 9:09:00 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
808 Views

Similar Articles

[PageSpeed] 32

Few observations.

1. Is it the whole SP code? I don't see where did you start transaction, e.g. where is BEGIN TRANSACTION?

2. If (select count(...) ) > 0 is essentially the same as

IF EXISTS (select 1 from ...)

but the later performs quicker if you only need to perform a check and not interested in the actual count.

 


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
2/17/2009 1:43:37 AM

Check also http://www.sql-server-performance.com/articles/per/deadlock_sql_2005_p2.aspx


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
2/17/2009 2:22:26 AM

Hi, thank you for your quick answers.

This is the whole SP, I didn't write it. I guess the problem is in the conception but I'm mostly looking for a quick fix because I have many of them.

I doubt it is a deadlock problem because there cannot be simultaneous connections. Apart from the "IF EXISTS" statement is there any other way you'd write this SP?

0
outshined
2/17/2009 4:15:52 AM
Reply:

Similar Artilces:

MS-SQL 7 to MS-SQL 2000
With Enterprise Manager, how can I convert a database from MS-SQL 7 to MS-SQL 2000 Thanks. Do you have Books Online? There is a topic in there called "Upgrading Databases from SQL Server 7.0 (Copy Database Wizard)" which will tell you how to do it. Terri Terri MortonEngagement Manager, NeudesicHow to ask a question Create a staging database to deal with issues before moving to the destination database. We still have some 7.0 and 2000 running. The process is test a lot then deploy. Hope this helps. Kind regards, Gift PeddieKind regards,Gift Peddie thanks guys...

How to Upgrade MS-SQL 2005 Express Edition To MS-SQL 2005 Developers Edition
Hey, i am using VS2008 for development. I have already installed MS-SQL2005 Express Edition on my machine. I would like to upgrade it to Developer Edition. I have a few databases in Express Edition which i want to migrate to developer edition. So i was thinking if anyone could help me with upgrading my express edition to developer edition. I would highly appreciate it. ThanksJeff The simplest way is to just back your database(s) up, then restore them onto the Developer Edition instance of SQL Server....

MS Access SQL migration to MS SQL Server TSQL
got some MS Access SQL Code that needs converting into TSQL: SELECT dbo_qryMyServices.FormsServiceID, dbo_qryMyServices.ServiceName, Sum(IIf(IsNull([CompletionDate]),0,1)) AS Completed, Count([pkServiceID])-Sum(IIf(IsNull([CompletionDate]),0,1)) AS Uncompleted, Count(dbo_MyServiceRequests.pkServiceID) AS TotalCount FROM dbo_qryMyServices LEFT JOIN dbo_MyServiceRequests ON dbo_qryMyServices.FormsServiceID = dbo_MyServiceRequests.PostType GROUP BY dbo_qryMyServices.FormsServiceID, dbo_qryMyServices.ServiceName ORDER BY dbo_qryMyServices.ServiceName; because it's Access and got V...

FYI - Interesting difference between Oracle SQL and MS SQL
While converting some code from T-SQL to PL/SQL, I ran across an interesting bit of MS SQL code.  The code looked odd to me so I did a quick experiment to prove what it did.  I was quite surprised by the MS SQL results! In ORACLE: create table testunion (aaa number); insert into testunionselect 2 from dualunion select aaa * aaa from testunion;  select * from testunion;  -- Returns one record, as it should, "2" delete testunion; insert into testunionselect 2 from dualunion allselect aaa * aaa from testunion; select * from testunion; -- Returns one record, ...

Migrating VS2003 datasource from SQL 2000 to SQL 2005
Is it necessary to convert my VS2003 project to VS2005 before converting the datasource from SQL 2000 to SQL 2005? Or can I just change the datasource without converting the project? Hi delossan, For the first question, the answer is no. Both SQL Server versions can work with Visual Studio 2003 and Visual Studio 2005. For the second question, the answer is yes. Visual Studio 2003 can work with SQL Server 2005.    Sincerely,Benson YuMicrosoft Online Community Support Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can ...

MS SQL 2005 Locking problem with direct SQL
HI all I am using IDM 3.5.1 connecting to a MS 2005 SQL Database with the JDBC 1.2 Third party drivers. On the command trnsform channel I have the following rules. My problem is this. I can do UPDATE and INSERT commands BEFORE the current operation, i.e. I can add a record using the /nds/input/ instead of AFTER the current operation using ../ as in the rules below. However when I use th ../ as below it seems as if the table is being held open after the update (or insert), as you can not access it unles you restart the SQL server service, where the update and insert have...

MS SQL 7 and MS SQL 2000
Situation. We have a client/server application written in PB 7 Build 6012. It works fine with MS SQL Server 7 When connecting to MS SQL Server 2000 in multiuser environment (more than 6 concurrent users) - numerous deadlocks and detrimental preformance. Does anybody have similar problems? Are there any changes in lock behaivour between MS SQL 7 and MS SQL 2000? On Thu, 06 Jun 2002 13:05:58 -0700, vovchik <Vladimir.Mylnikov@acs-inc.com> wrote: >Situation. We have a client/server application written in PB 7 Build >6012. It works fine with MS SQL Server 7 >When conn...

MS SQL Server 6.5 to MS SQL Server 2000
We are migrating from MS SQL Server 6.5 to MS SQL Server 2000. We are using a Native connections and would like to know if there are any issues that we should be aware to do this correctly. TIA Adam Shepherd Are you sure about that? I have not experienced (or heard of) that kind of problem w/ 7.0. There are many other reasons why a table san is performed, but I don't think a numeric data type would cause it... Or are you talking about the PB problem of sending an Integer as 1. -- Kim Berghall Sisu Group, Inc. remove no_spam. no_spam.kberghall@sisugrp.com www.sisugrp...

how i will convert MS SQL Express Edition to MS SQL 2005
hi friend now i am using VS 2005 and MS SQL express edition as Database. i want to host the application. at server it will not support sql express edition. how i will convert .mdf file to MS SQL server 2005 please help me friends.abdul manzoor AOA Manzoor, There is no special requirement to convert MS SQL Express Database to SQL Server 2005 Database.. You can attach the SQL Express 2005 Database File (.mdf ) with the same database attachment procedure that we follow on SQL Server 2005 Database attachment , All you need is 1. Open the SQL Server 2005 Management Studio, Right Cl...

PIPELINE - PB10
Hello All, We are converting our PB apps to version 10. Thanks to the posts on this site we have been successful setting OLE DB connections and everything appeared to be working fine. I just noticed that at least one of my pipes is not working correctly. It is truncating 1 character off of a field. This worked fine when we were going from sql 7 to sql 7 but when I connect to one server running ms sql 7 and the other using ms sql 2000 using a data pipe in PB 10 it is cutting off a char??? This one blows my mind because there is no script??? We had to make changes to trim s...

MS Access, MS SQL Express, MS SQL when you should use wich version?
Hi, I'm planning to make a website for a in first part a small user group. There needs to displayed some data in the database.But because the hostings party advices Acces.I like MS SQL Express more, because the size of the visitors is getting very big. The meaning of the project is to make a big website for the complete country. But i can use Access, it works, but does anyone know till how much visitors you can use access, and MS SQL Express ?Of course MS SQL is very nice, but its also very heavy to get a licence.The website is made for free and we need to run it in spare time, also for...

Return vals from MS-SQL-Server stored procs in embedded SQL
hi ... how do i get the return value from executing a stored procedure declared in a MS-SQL-Server database within Powerbuilder 5.0.04 embedded SQL statements? the return value i want is *not* the result-set nor an output parameter -- it is the return value specified by [return xxx] within the stored proc in the database. any help is appreciated! thanks, andrew The return value is returned as the first value in the SECOND result set. Any output args are returned, in order, after the return value in the same (second) result set. In embedded SQL, after you execute the ...

Return vals from MS-SQL-Server stored procs in embedded SQL
hi ... how do i get the return value from executing a stored procedure declared in a MS-SQL-Server database within Powerbuilder 5.0.04 embedded SQL statements? the return value i want is *not* the result-set nor an output parameter -- it is the return value specified by [return xxx] within the stored proc in the database. any help is appreciated! thanks, andrew The return value is returned as the first value in the SECOND result set. Any output args are returned, in order, after the return value in the same (second) result set. In embedded SQL, after you execute the ...

Return vals from MS-SQL-Server stored procs in embedded SQL
hi ... how do i get the return value from executing a stored procedure declared in a MS-SQL-Server database within Powerbuilder 5.0.04 embedded SQL statements? the return value i want is *not* the result-set nor an output parameter -- it is the return value specified by [return xxx] within the stored proc in the database. any help is appreciated! thanks, andrew The return value is returned as the first value in the SECOND result set. Any output args are returned, in order, after the return value in the same (second) result set. In embedded SQL, after you execute the ...

Web resources about - Problem with stored proc, migrating from MS SQL 2000 to MS SQL 2005 - asp.net.sql-datasource

Thunderclap Migrating From Twitter to Facebook
Thunderclap, a mass-messaging service that gained popularity on Twitter, has set its sights on Facebook . Twitter recently booted Thunderclap ...

Facebook Migrating Unofficial Page Fans to Official Pages at Brand Owner’s Request
Rupesh Mandal is such an avid fan of the Opera Mini web browser that he set up a Page on Facebook totally devoted to celebrating it, and then ...

Teens Migrating From Facebook To Comments Section Of Slow-Motion Deer Video - YouTube
Subscribe to The Onion on YouTube: http://bit.ly/xzrBUA Trendwatchers say more and more teens are leaving Facebook and Twitter in favor of the ...

UAE is the global leader in attracting migrating professionals, study finds
... with 28 per cent, was the leading source of professionals, according to the LinkedIn study. The UAE is the global leader in attracting migrating ...

Record Numbers of New Zealanders Migrating to Australia
Acting on impulse: why the Kiwis keep coming ... and coming and coming

The krilling season: Whales keep tourists migrating south
Earlybird whale watchers in Eden have been rewarded with astounding acrobatic displays in the bay.

Migrating Swans were bound for glory, says great
Migrating Swans were bound for glory, says great

Researchers say Irukandji jellyfish migrating further south along Qld coast
Researchers say climate change could be altering the migration patterns of the dangerous Irukandji jellyfish along Qld's east coast.

Apple chip supplier TSMC announces record profits, migrating to more advanced technology
... Company , iPhone , and iPad continue reading at 9to5Mac . What do you think? Discuss "Apple chip supplier TSMC announces record profits, migrating ...

More eBay Merchants Migrating to Amazon in Search of Sales Growth
EBay's once-loyal merchants are moving more of their business to Amazon, saying they get more for their money by selling merchandise via the ...

Resources last updated: 12/4/2015 8:14:46 PM