Multiple update statement is crashing SQL Server.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple update statement is crashing SQL Server.

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2006-07-19 : 02:13:27

Hi there,
I have a developer who's application is crashing the sql process causing an Unhandled Exception Error and dumps out to the log. He has identified the problem as being when mutiple updates are executed against one table in the same sql statement:

UPDATE TABLE ApplicationSettings SET SettingValue = ‘test1’ WHERE SettingKey = ‘ServiceEmail’
UPDATE TABLE ApplicationSettings SET SettingValue = ‘test1’ WHERE SettingKey = ‘MapEmail’
UPDATE TABLE ApplicationSettings SET SettingValue = ‘test1’ WHERE SettingKey = ‘TestEmail’
UPDATE TABLE ApplicationSettings SET SettingValue = ‘test1’ WHERE SettingKey = ‘SupportEmail’

CREATE TABLE [dbo].[ApplicationSettings] (
[ApplicationSettingsId] [int] IDENTITY (1, 1) NOT NULL ,
[SettingKey] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
[SettingValue] [text] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Not really sure why this would be so?
We run SQL 2000 sp3a

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-19 : 02:26:11

And the problem doesn't appear when running them one by one?

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2006-07-19 : 05:28:55

nope. Neither does the problem occur when running it from Query ANalyser.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-19 : 05:37:20

Can you post the error that you are receiving?

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-19 : 05:59:22

Do you get the same error when using any of the following examples which does the same job?

UPDATE	ApplicationSettings
SET	SettingValue = 'test1'
WHERE	SettingKey IN ('ServiceEmail', 'MapEmail', 'TestEmail', 'SupportEmail')


UPDATE	ApplicationSettings
SET	SettingValue = 'test1'
WHERE	SettingKey = 'ServiceEmail'
	OR SettingKey = 'MapEmail'
	OR SettingKey = 'TestEmail'
	OR SettingKey = 'SupportEmail'

Are you using ADO and Command Object to execute the queries? Post the VB code too.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources