Getting SQL Server Deadlock Error - How Do I Work Around?

Mar 27, 2007

I have some ASP.NET C# code which executes a stored procedure in SQL Server via the SqlCommand and SqlConnection classes.

One of the stored procedures that gets executed is giving the error: "Transaction (Process ID 272) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." This only happens occassionally.

 Is there a way to get around this in my ASP.Net application? One thing I tried is ensuring that no 2 users entered the stored procedure concurrently:object synclock = new object() ;
lock (synclock) {
// execute SQL stored procedure
...
} This did not solve the problem, and I'm not even sure if that is the correct implementation to ensure sequential execution of the stored procedure.

View 1 Replies


ADVERTISEMENT

Trace Falg 1204, 1205 (DeadLock) Doesn't Work URGENT

Aug 20, 2001

Hi, I have no idea what i'm doing wrong: Tried to gather more detail information about Deadlock (error # 1205) set the trace flag 1204 - ON (tried 1205 - ON as well) nothing happend, still the same message outgoing "Your transaction (process ID #13) was deadlocked with...." with no any detail.
If anybody met the same problem before, HELP please.

Maybe the information goes to some place other than Error Log file?

Thanks,
Dima

View 2 Replies View Related

Which Sql Server Error Number Is Used When A Table Has A Deadlock?

Jul 23, 2005

I want to set an alert for a specific table whenever an event hascaused a deadlock to occur on the table.I understand how to set up an alert. But I don't know which errornumber to use for the New Alert error number property for a deadlock.Or how to specify a deadlock on a specific table.Thanks,DW

View 1 Replies View Related

Maintenance Job And Deadlock Error (SQL Server 2005)

Oct 3, 2007

Hi there,

We have lately experianced a strange problem with our SQL Server 2005 x64 (SP2) that is NOT consistent but when it happens it happens on the same time.

Almost every night at 03:30 one of our databases (not all) seems to be down or locked. When i have a look at the order table in this database I can see that we have stopped recieving orders after 03:30. Two hours later (05:30) I can see the following error each minute in the error log until we reboot the server:


All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on LCK_M_IS. Process Utilization 0%%.


As we have a maintenance job running at 03:30 it feels like this is the problem. The job performs the following tasks: "Check Database Integrity -> Rebuild Index -> Reorganize Index"

When i look at the history of the job it looks like it's not completed and only the "Check Database Integrity" task was runned. No error message here either.

Also when i look in the error log i can see that the Maintenance job is started but never ended. Worth to notice is that I get the follwoing info in the log after the start-message:

Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.

Also, when i run this job manually daytime it works great!

Anyone having any idees on this? Is it possible to track this even more? I'm tired of restarting the server 03:30 in the morning =)

Thanks
Jon

View 4 Replies View Related

Mirroring :: Email Deadlock Information When A Deadlock Occurs

Nov 10, 2015

Is there a way to send out an email woth deadlock information (victim query, winner query, process id's and resources on which the deadlock occurred) as soon as a deadlock occurs in a database or at instance level?I currently has trace flag 1222 turned on. And also created an alert that send me an email whenever a deadlock occurs. but it just says that a deadlock occurred and I log into sql server error log and review the information.

View 5 Replies View Related

Deadlock Error

Aug 9, 2007

I received the following error message when run the query,


Server: Msg 1205, Level 13, State 61, Line 1
Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

how can solve the deadlock error ?

regards
Martin

View 2 Replies View Related

Deadlock Error

Jul 20, 2005

I am getting quite a few deadlock errors where both sessions aretrying to execute sp_execsql according to the the trace information inthe error log (see below). The database is being asscessed by anapplication written in .NET, as well as a few people using QueryAnalyzer. This seems to be happening relative randomly - can't pin itto any specific circumstances. Any thoughts would be appreciated.RID: 8:1:617:37 CleanCnt:1 Mode: X Flags: 0x2Grant List 1::Owner:0x3738dbe0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:55ECID:0SPID: 55 ECID: 0 Statement Type: CONDITIONAL Line #: 47Input Buf: RPC Event: sp_executesql;1Requested By:ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec:(0x4AC4D570)Value:0x23297b80 Cost:(0/12C)Node:2RID: 8:1:267:91 CleanCnt:1 Mode: X Flags: 0x2Grant List 0::Owner:0x3efae340 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:52ECID:0SPID: 52 ECID: 0 Statement Type: CONDITIONAL Line #: 115Input Buf: RPC Event: sp_executesql;1Requested By:ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x483FB570)Value:0x37c0e060 Cost:(0/138)Victim Resource Owner:ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec:(0x4AC4D570)Value:0x23297b80 Cost:(0/12C)

View 1 Replies View Related

Upgraded To SQL Server 2005. Updates No Longer Work. Error Could Not Find Server 'DEVDB' In Sysservers. Execute Sp_addlinkedse

Feb 28, 2006

Hello!

We just upgraded to SQL Server 2005 from SQL Server 2000. The DB was backed up using Enterprise Manager and restored with SQL Server Management Studio Express CTP. Everything went as expected (no errors, warnings, or any other indicator of problems).

The DB resides in a DB Server (Server1) and the application we are running is a Client/Server system where the AppServer resides on Server2.

During the application's operation all read, create, and delete transactions work fine but no update works. When viewing details in Trace Log I see this message after attempting any update:

Could not find server 'Server1' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. (7202)

Any help is greatly appreciated,

Lucio Gayosso

View 19 Replies View Related

Process DeadLock -- Frustating Error

Mar 24, 2008

guys,
I have a stored procedure which gets called by ASPX page and it inserts, updates data into different tables. originally, I had a issue that if error occured, it would not rollback all the data so i used transaction around it. now, once in a while I am getting this error "System.Data.SqlClient.SqlException: Transaction (Process ID 181) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction".
I don't know how this error occurs and how do I prevent it. please help.
transaction is as follow.
BEGIN TRY 
  BEGIN TRANSACTION
 // t-sql codes to insert update multiple tables
 COMMITEND TRY
BEGIN CATCH
if (@@TRANCOUNT > 0) --error
ROLLBACK
declare @errSeverity intselect
@errMsg = ERROR_MESSAGE(),
@errSeverity = ERROR_SEVERITY()
RAISERROR(@errMsg, @errSeverity, 1)
END CATCH

View 7 Replies View Related

How To Solve The Deadlock Error During Backup?

Jan 24, 2005

I used veritas to backup MsSQL 2000 database server but I often encounter backup fail error due to the deadlock in the database. May I know how to reduce this problem?

I heard there is a method to control the database deadlock timeout. If we set a lock timeoutvalue, will it reduce the chance of deadlock to happen? Is there any disadvantage of doing that?

If I were not wrong , we can set the lock_timeout value using the syntax "SET LOCK_TIMEOUT milliseconds" Is that true? But Do we have to have to run this command for every database? What is the normal milliseconds value?

View 3 Replies View Related

Stored Proc And Deadlock Error Handling

Jun 4, 2004

I have a Stored Proc that is called by a SQL Job in SQL Server 2000. This stored proc deadlocks once every couple of days. I'm looking into using the @@error and try to doing a waitfor .5 sec and try the transaction again. While looking around google I've come across a few articles stating that a deadlock inside a Stored Proc will stop all execution of the stored proc so I will not be able doing any error handling. Is this true? Does anyone have any experience that could help me out?

I know the best solution would be to resolve why I get a deadlock. We are currently looking into that but until we can resolve those issues I would like to get some type of error handling in place if possible.

Thank you,
DMW

View 8 Replies View Related

Integrated Security Doesn't Work - Not Associated With A Trusted SQL Server Connection. Error

Oct 20, 2006

 

Hi,

I have a piece of Java code that needs to connect to SQL 2000 (SP4) using Windows Authentication. It's running on Windows Server 2003 SP1.

I tried JDBC v1.1 and followed the code from the following blog:

http://blogs.msdn.com/angelsb/default.aspx?p=1

But still get this error as shown below. Any help appreciated.

I am using JDK1.4.2, "sqljdbc_auth.dll" is located under "E:SQL2005JDBCDrvsqljdbc_1.1enuauthx86", also made a copy under "E:JavaTest" and "C:WindowsSystem32" but still won't work.

Cheers

Allan

 

===========================================================

E:JavaTest>javac -classpath ".;E:JavaTestsqljdbc.jar" TestW2.java

E:JavaTest>java -classpath ".;E:JavaTestsqljdbc.jar" -Djava.library.path=E:S
QL2005JDBCDrvsqljdbc_1.1enuauthx86  TestW2
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '(null)'.
 Reason: Not associated with a trusted SQL Server connection.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError
(Unknown Source)
        at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.processLogon(Unknown
 Source)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source
)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknow
n Source)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover
(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Sour
ce)
        at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at TestW2.main(TestW2.java:7)
===========================================================

The code is simple (TestW2.java):

import java.sql.*;

public class TestW2 {
 public static void main(String[] args) {
  try {
   java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   Connection conn = java.sql.DriverManager.getConnection("jdbc:sqlserver://VMW2k3ENT003.TESTCBFPOC.COM.AU;integratedSecurity=true");
   System.out.println("Connected!");
   conn.close();

  } catch (Exception ex) {
   ex.printStackTrace();
  }
 }
}

==============================================================

View 27 Replies View Related

The Error Is (17058): SQL Server 2005 Express Edition 9.00.1399.06 Install Work.

Feb 25, 2006

I have encountered an error (as shown below) when attempting to install the SQL Server 2005 Express Edition 9.00.1399.06 on on my XP SP2 pc.

the file: "C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSummary.txt"

show the following messages:

Machine : DUMBO
Product : SQL Server Database Services
Error : The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."

and ...

Machine : DUMBO
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.00.1399.06
Install : Failed
Log File : C:ProgrammiMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0015_DELL8400_SQL.log
Last Action : InstallFinalize
Error String : The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
The error is (17058) .
Error Number : 29503

Also, the file: "C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_CBXP_Core.log"

show this message:

Error: Action "LaunchLocalBootstrapAction" threw an exception during execution. Error information reported during run:
"c:ProgrammiMicrosoft SQL Server90Setup Bootstrapsetup.exe" finished and returned: 17058
Aborting queue processing as nested installer has completed
Message pump returning: 17058

Maybe that's not a fatal error, but the SQLExpress service does not get installed.

Someone can help me?

Thanks!

View 9 Replies View Related

SQL Server Deadlock?

Feb 14, 2008

Hi Guys.
I just want to ask some insights on SQL Server Deadlock and what is the best way to handle deadlock in asp.net?
Or something like a Try... Catch.. statement to handle the error?
Please advice. Thanks in advance.

View 6 Replies View Related

SQL Server Deadlock On Resources??

May 30, 2007

We've got a 3rd party application that periodically runs SQL commands throughout the day. We've been getting issues with this application showing a sql error:

Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In checking the processes on SQL Server, there were a lot, and process ID 71 was actually hitting a completely different database.

Is there a way to streamline how SQL Server handles processes, and what's the limit at any given time?

View 4 Replies View Related

SQL Server Profiler: Deadlock Graph

Aug 10, 2007

I'm investigating my production server because there appears a deadlock every day. So, in SQL Profiler, I use the the Deadlock graph to capture the trace in a file.

When I click on the textdata to see the graph an error appears:
- Failed to initialize deadlock control.
Cannot find process victim in process list.

I also get the following error from another deadlock:
- Failed to initialize deadlock control.
Object reference not set to an instance of an object.

I stopped my trace, so this can not be the problem.

Does anybody knows why I can't see the graph?


I also captured the trace in a table and then used the following query to see it in xml:
select convert(xml,textdata)
from TableName

This works fine.

View 2 Replies View Related

Transaction Deadlock In SQL Server 2005

Mar 3, 2007

HI,

I have a framewrok that runs tests and keeps updating the status of the tests to the DB. They are approx 20 tests whose status will be updated simultaneously. Recently i have seen the follwoing error


{"Transaction (Process ID 84) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."}

I am using SQL server 2005. Any suggestions?

Thank you



View 1 Replies View Related

Where DeadLock Details Are Stored In SQL Server

Jan 24, 2008

Hi Guys,

I have one situation. I need to find out the processes which are all in deadlock state. I my figure out that SQL server maintains the process details in sysprocesses table.

Can any one please help me, Whether this is the table which contains deadlock details. If not where are they stored and How to kill that process ?

Thanks & Regards,
Senthil

View 38 Replies View Related

SQL Server 2008 :: DeadLock In SubQuery With Selfjoin

Oct 3, 2015

Below query. its getting more time to exec and got deadlock. So, query to avoid deadlock.

SELECT m1.Value AS InterfaceName, m1.MessageDateTime, m2.GroupId, COUNT(mError.Id) AS ErrorCount
FROM (
SELECT m1.Value, MAX(m1.MessageDateTime) as MessageDateTime FROM Message m1
WHERE m1.TypeId = 9 AND (m1.Value LIKE 'F02' )
GROUP BY m1.Value

[Code] ....

View 2 Replies View Related

Error: A Deadlock Was Detected While Trying To Lock Variable X For Read Access. A Lock Could Not Be Acquired After 16 Attempts

Feb 2, 2007

I simply made my script task (or any other task) fail

In my package error handler i have a Exec SQL task - for Stored Proc

SP statement is set in following expression (works fine in design time):

"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"

From progress:

Error: The Script returned a failure result.
Task SCR REIL Data failed

OnError - Task SQL Insert Error Msg
Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.

Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

And how did I get 4 errors? - I only set my script task result to failure

View 11 Replies View Related

SQL Server Admin 2014 :: Deadlock Because Of Non-Cluster Index

Jun 11, 2015

Dead lock is coming in select query in application because of index. It is identified after enabling trace in database and identified by reading deadlock xml file. After index removal, deadlock is not coming in same query. But it is affecting query's performance slightly. Is it correct way to remove index if dead lock is coming because of index?

View 3 Replies View Related

SQL Server 2000 Deadlock Concurrent Select Transcations

Oct 14, 2007

There are a series of traces of transactions for essentially the same units of work. Each includes a retrieving the same table "Select" from the database. The first 4 transactions ran concurrently. The last one ran about 7 minutes later with no other concurrent transactions.

The elapsed times for the database accesses for each of the first 4 is significantly higher than the last one.
Can you think of any database optimazation that might improve this?


Are there any utilities available than can be used for stand-alone testing of SQL Server database performance?
E.G. they would measure response time for reading/writing large/small amounts of canned data once/multiple times with concurrent/nonconcurrent access.

SQL Server Database is on 2000.I am not sure about the ISOLATION level in the Java code.

Basically I am new to SQL Sever....Please ans the above questions and provide me the direction to proceed on these questions

View 5 Replies View Related

SQL Server 2008 :: Getting DeadLock On Production Server

Mar 9, 2015

I am getting following deadlock message. What is happening in below deadlock?? Also what can I do to get rid of this deadlock?

View 7 Replies View Related

SQL Server 2012 :: Stored Procedure With Insert Causing Deadlock

Aug 31, 2015

We have around 5 SP’s which are inserting data into Table A,and these will run in parallel.From the temp tables in the SP,data will be loaded to Table A. We are getting deadlock here.No Begin and End Transaction used in the stored procedure.

What could be done to avoid deadlock.

View 5 Replies View Related

Restore Won't Work (Database In Use Error)

Dec 3, 1999

I'm trying to use schedulled task to automate restore and i keep getting the error database in use, it is set to dbo only and single user, and it has me as the user that has the lock, i have tried to kill it but it won't go, i have even started and stopped the server. any ideas?

View 2 Replies View Related

Alerts Do Not Work For Error Numbers - Why

Aug 6, 2007

I'm unable to get an alert to fire for Error 2627, Primary Key Violation. I have followed BOL's instructions for creating an alert, which appears to be nearly identical to 2000, however the alert will not work. I can see the error number in a Profiler trace along with the Severity number. I've also tried creating alerts for other error numbers and had the same result. The BOL instructions are as follows:

To create an alert using an error number

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent
3. Right-click Alerts and then click New Alert
4. In the Name box, enter a name for this alert.
5. Check the Enable check box to enable the alert to run. By default, Enable is checked
6. In the Type box, click SQL Server event alert.
7. In the Database name list, select a database to restrict the alert to a specific database
8. Click Error number, and then type a valid error number for the alert
9. To restrict the alert to a particular character sequence, check the box corresponding to Raise alert when message contains, and enter a keyword or character string for the Message text. The maximum number of characters is 100

What am I doing wrong?

Thanks, Dave

View 4 Replies View Related

Adventure Work Database Error

Jan 11, 2007

i followed all the instruction to add the database of the adventure works to the database server. but when i attach the database from the managment sql server this error comes

Couldnt find row in sysindexes for database ID 8, object ID1 ..........etc

and couldnt open new database 'adventureworks'. create database is aborted.

please i need a qucik help. cuz i tried the msdn and i failed.

thanks

View 1 Replies View Related

SSIS Error Output -- How Is It Supposed To Work?

May 29, 2006

I€™m importing a Flat file (delimited text) into a sql server database table and trying to trap any import errors in another output Flat file.

I create a Flat File Source task. After tweaking the task, the Source €śError Output€? tab shows all the input columns and all €śError€? and €śTruncation€? values are set to €śRedirect row€?

Next I add a €śSQL Server Destination€? task and connect the Source green arrow to it.

Finally I add a Flat File Destination task (error Output task), connect the Source red arrow to it, click €śOK€? in the €śConfigure Error Output€?, and finally add a connection manager to the Error output task. When I look at the Mappings tab of the Error Output file it shows only three available input columns: €śFlat File Error Output Column€?, ErrorCode, and ErrorColumn

I€™m not sure where they came from but that info is not very useful to me. I want to know which line the error occurred on as well as the bad column(s). If nothing else, I need to see at least the actual row that was bad. How can I get that information?



Barkingdog

View 8 Replies View Related

Error 26 - Followed The Guidelines, Still Unable To Make It Work

Nov 7, 2007



Good Morning, I've been searching through all the tutorials and questions, have tried many things. I am still getting "[SqlException (0x80131904): An error has occurred 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]"

as an error. This is what I've got:

I€™m using SQL Server Express 2005, installed with the default settings. Had not touched this program for anything until I started to follow directions to fix Error 26. Visual Web Developer Express ASP.NET is what I€™ve used to build the webpage. I was using the ASP.NET web configuration to add users to the database, which is set to use the provider ASPSqlServerProvider.
SQL Server 2005 Surface Area Configuration
Database Engine Remote Connections €“ set to €śLocal and Remote Connections €“ Using TCP/IP Only€?
SQL Server Browser is Enabled and Running
- is set to Active, under Built In Account €“ Network Service
I have created Windows firewall exceptions for:
sqlservr.exe
sqlbrowser.exe
udp port 1434
SQL Server Configuration Manager
both SQL Server and SQL Server Browser are running.
Under SQL Server 2005 Network Configuration
Shared Memory and TCP/IP are enabled only.

SQL Native Client Configuration
Shared Memory 1
TCP/IP 2
Named Pipes 3
all enabled

I read through the post at http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx but as I'm new to SqlServer I do not know how to check on the first three items.

I'm getting extremely frustrated, and would just like the login portion of this website to start working before my boot ends up through the computer. Please help, lol, thank you.

View 9 Replies View Related

Error 'Cannot Create A Row Of Size Xxxx' - My Fix Doesn't Work

Jul 12, 2007

Been doing some research after getting this error:
Microsoft OLE DB Provider for SQL Server error '80040e14'

Cannot create a row of size 8297 which is greater than the allowable maximum of 8060.


And realised that my nice responsive varchars had a maximum total size. so I changed them for 'slower' text data types. but my DB still won't allow any more input.

has the limit been reached now regardless of what I change or can I rebuild the DB to recover the space or something?

View 14 Replies View Related

Trouble With Trigger: Doesn't Seem To Work At All And No Error Messages.

Oct 3, 2007

Hi Everyone,
I'm having some trouble with the below trigger. When I add a row of data either manually or using an INSERT query, it just doesn't do anything. It doesn't provide any error messages either. This makes me think that it's aborting the operation because rowsAffected are 0 or some other simple error. My row manipulation code could be suspect also. This is my first time writing a trigger or using T-SQL for that matter.
What I'm trying to do is to have the trigger add +1 to the Iter field of all rows where BouleID is equal to the BouleID of the row being inserted. So let's say I have the following table:


BouleID CurrentLocation Iter
A01 Inventory 1
A01 Cutting 0
A01 WIP 2
B01 WIP 0
B02 WIP 1
B02 Inventory 0

Now, if I insert a row with BouleID = A01 and Current Location = Polishing, I want the Iter field of all previous rows to iterate by +1 and this new row to have Iter = 0.

I am using SQL Management Studio Express, and SQL Server Express.

Any thoughts on anything wrong with my selection code and iteration code? Could I adapt this to handle more than one row by using a GROUP BY BouleID somehow?






Code Block

SET ANSI_NULLS ON


GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: <this trigger will iterate the Iter field by one for each rows that has the BouleID matching the one of
-- the row being inserted>
-- =============================================
CREATE TRIGGER dbo.trgCG_DispoIterate$InsertTrigger
ON dbo.dbCG_Disposition
AFTER INSERT AS
BEGIN

DECLARE @rowsAffected int,
@msg varchar(2000), --for error message
@BouleID varchar(6) -- and do I need to enter more than one?

SET @rowsAffected = @@rowcount

IF (@rowsAffected = 0 or @rowsAffected > 1 ) RETURN --don't continue if no rows changed or doing more than one at a time.
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
SET ROWCOUNT 0

BEGIN TRY

--VALIDATION BLOCK Leave this alone for now

SELECT @BouleID = BouleID FROM Inserted --sets @bouleID equal to the BouleID of the row being inserted.

UPDATE dbo.dbCG_Disposition --This block sets the Iter field to it's previous value +1
SET Iter = ( Iter + 1 ) --
WHERE BouleID = @BouleID --


END TRY
BEGIN CATCH
IF @@TRANCOUNT >0
ROLLBACK TRANSACTION

--or this will get rolled back
EXECUTE dbo.ErrorLog$Insert -- this function creates an errorlog table which gets filled up if there is an error in the try block.

DECLARE @ERROR_MESSAGE nvarchar(4000)
SET @ERROR_MESSAGE = ERROR_MESSAGE()
RAISERROR (@ERROR_MESSAGE, 16, 1)
END CATCH

View 6 Replies View Related

SQL ERROR - I Need DISTINCT But Can Use It With Image,ntext, Text - How To Work Aroud???

Oct 10, 2006

SQL ERROR - I need DISTINCT but can use it with image,ntext, text - How To work around???  ! more - How to Work around - MIN() I cant use it when having text in SELECT statement  The text, ntext, or image data type cannot be selected as DISTINCT.



Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The text, ntext, or image data type cannot be selected as DISTINCT.

Source Error:





An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below. 

View 2 Replies View Related

SQL 2005 SP2 Error 29506 Workaround Doesn't Work - Data On LUNs

May 13, 2008

I have had the same Error 29506 that a lot of people are having when installing SP2 for SQL 2005. I've tried the install with myself (a Domain Admin), local Administrator, cascaded full rights down the entire file system structure and still not luck. One thing I'm wondering if it is hanging me up is that all of my databases and logs are not on C:. They are on LUNS on a NetApp SAN (Data is on M: and Logs is on L:). Even the system databases (Master, Model, etc.) are on the LUNs. The error logs referenced permissions to the data directory under the default installation path on C:. Anyone else have this problem? Got a fix? I really don't want to migrate all of my data back to the local machine, apply the patch, them migrate back. Surely this SP should be able to read the data location from the SQL engine. And surely others have their databases on SANs.... I'm at a loss.

Thanks.

View 3 Replies View Related







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