Internal SQL Server Error- HElp Urgent!!!!! SQL 2000/Win3K
-
Hi all Experts,
Over the last weekend we did migration from SQL 7.0 ENT edition to SQL 2000 ENT edition. I have applied SP4 and all is set. But we are getting :
Internal SQL Server Error on couple databases while trying to run updates(Trigger based). The issue is there are about 25 databases that have the same tables and triggers. This is happeneing with only 3 databases. Have checked the databases/tables for corruption and they are all fine. Any ideas would help. I have seen through lot of MSFT articles and all point to get the latest SP which is SP4 and it has been applied. Will assign another 200 points for a quick solution.
Appreciate your help in advance,
Avb
-
It just throws the error 'Internal SQL Server Error':
Server: Msg 8624, Level 16, State 1, Procedure Org_UTrig, Line 198
Internal SQL Server error.
-
Here is the code where it blows up, though we have similar code in another 15 db's they all work fine. Not sure why its acting up:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER dbo.Org_UTrig ON dbo.Org FOR UPDATE AS
SET NOCOUNT ON
/* Replicates field
as OrgID */
if update(
) or update(OrgID)
begin
update Org set OrgID = inserted.[Table] from inserted, Org where inserted.
= Org.
end
/** Prevents both Orgname and [Table] from being changed at once
if (select count(*) from inserted,deleted where deleted.
<> inserted.
and deleted.OrgName <> inserted.Orgname) >0
BEGIN
RAISERROR 44446 'The record can''t be updated. You can not change both Org and Table at the same time.'
ROLLBACK TRANSACTION RETURN
END
*/
/* * PREVENT UPDATES IN TABLE Org IF NO MATCHING KEY IN ltbContact */
IF UPDATE(Admin) or update(archive)
BEGIN
if (select count(*) from inserted where archive=1) != (select count(*) from inserted where Admin is null)
BEGIN
update Org set admin = null from Org inner join inserted on org.
=inserted.
where inserted.archive=1
END
IF (SELECT COUNT(*) FROM inserted where Archive=0 and admin is not null) != (SELECT COUNT(*) FROM ltbContact , inserted WHERE archive=0 and (ltbContact.Contact_Name = inserted.Admin ))
BEGIN RAISERROR 44446 'CC Org_UTrig: The record can''t be added or changed. Rules require a related record in table ltbContact.'
ROLLBACK TRANSACTION RETURN
END
END
/* Check for null SubBusiness in any record if at least one record for the same org is not null, 3/3/03 */
If update(subBusiness)
Begin
update org set subbusiness=null where subbusiness = ''
If (Select count(*) from inserted , Org where Org.business = inserted.business and ((inserted.subbusiness is null and org.subbusiness is not null) or (inserted.subbusiness is not null and org.subbusiness is null) ))>0
Begin
Raiserror 44447 'CC Org_UTrig: SubBusiness has to be assigned to all Orgs or assigned null to all Orgs for this business.'
Rollback transaction return
End
End
/* * CASCADE UPDATES TO 'SiteDetail' */
IF UPDATE(
)
begin
IF (SELECT COUNT(*) FROM inserted,deleted WHERE inserted.
<>deleted.
) > 0
BEGIN
UPDATE SiteDetail
SET [SiteDetail].[Org] = [inserted].
FROM SiteDetail, deleted, inserted
WHERE [deleted].
= [SiteDetail].[Org]
/** CASCADE UPDATES TO 'SiteQtrField' */
UPDATE SiteQtrField
SET SiteQtrField.org=inserted.
FROM SiteQtrField, deleted, inserted
WHERE (SiteQtrField.org = deleted.
) AND (SiteQtrField.location is null) AND (SiteQtrField.SubOrg is null)
/** CASCADE UPDATES TO 'GroupSiteList' */
UPDATE GroupSiteList
SET GroupSiteList.org=inserted.
FROM GroupSiteList, deleted, inserted
WHERE (GroupSiteList.org = deleted.
) AND (GroupSiteList.location is null)
END
end
If update(Business)
begin
IF (SELECT COUNT(*) FROM inserted,deleted WHERE inserted.Business<>deleted.Business) > 0
begin
UPDATE ltbcontact
SET ltbcontact.contact_business = inserted.business
FROM ltbcontact, deleted, inserted
WHERE deleted.business = ltbcontact.contact_business
UPDATE ltbcontact_permissions
SET ltbcontact_Permissions.business = inserted.business
FROM ltbcontact_permissions, deleted, inserted
WHERE deleted.business = ltbcontact_permissions.business
End
end
IF UPDATE(ORGNAME)
Begin
IF (SELECT COUNT(*) FROM inserted,deleted WHERE inserted.ORGNAME<>deleted.ORGNAME) > 0
BEGIN
/** Prevent updates to Orgname if there are double quotes or single quotes in it */
if (select count(Orgname) from inserted where Orgname like '%''%' or Orgname like '%"%' or Orgname like '%.%') >0
BEGIN
RAISERROR 44446 'CC Org_UTrig: Sorry, you may not change Orgname in table Org to an Orgname with a single or a double quote or period in it.'
ROLLBACK TRANSACTION RETURN
END
/* * CASCADE UPDATES TO 'SubOrg' */
UPDATE SubOrg
SET SubOrg.ORGNAME = inserted.ORGNAME
FROM SubOrg, deleted, inserted
WHERE deleted.ORGNAME = SubOrg.ORGNAME
UPDATE Site
SET Site.ORGNAME = inserted.ORGNAME
FROM Site, deleted, inserted
WHERE deleted.ORGNAME = Site.ORGNAME
/** CASCADE UPDATES TO 'ltbContact_Permissions' */
UPDATE ltbContact_Permissions
SET ltbContact_Permissions.org=inserted.orgname
FROM ltbContact_Permissions, deleted, inserted
WHERE (ltbContact_Permissions.org = deleted.orgname)
/** Org CASCADE UPDATES TO ltbScorecardQn_Ans */
UPDATE ltbScorecardQn_Ans
SET ltbScorecardQn_Ans.OrgName = inserted.OrgName FROM ltbScorecardQn_Ans, deleted, inserted
WHERE deleted.OrgName = ltbScorecardQn_Ans.OrgName and ltbScorecardQn_Ans.SubOrg is null
/** Orgname casacade update to TASK_TEMPLATE , */
UPDATE TASK_TEMPLATE
SET TASK_TEMPLATE.ORGNAME = inserted.OrgName FROM TASK_TEMPLATE, deleted, inserted
WHERE deleted.OrgName = TASK_TEMPLATE.ORGNAME
/** Org CASCADE UPDATES TO Supervisor */
UPDATE Supervisor
SET Supervisor.Orgname = inserted.Orgname
FROM Supervisor, deleted, inserted
WHERE deleted.Orgname = Supervisor.Orgname
/** Org CASCADE UPDATES TO SiteApplicModule */
UPDATE SiteApplicmodule
SET SiteApplicmodule.Orgname = inserted.Orgname
FROM SiteApplicmodule, deleted, inserted
WHERE deleted.Orgname = SiteApplicmodule.Orgname
/** CASCADE UPDATES TO 'Profile_Itemscope' */
UPDATE Profile_Itemscope
SET Profile_Itemscope.Filterorg=inserted.orgname
FROM Profile_Itemscope, deleted, inserted
WHERE (Profile_Itemscope.FilterOrg = deleted.orgname) AND (Profile_Itemscope.FilterSite is null) AND (Profile_Itemscope.FilterSubOrg is null)
/** CASCADE UPDATES TO 'Profile_Itemscope' */
UPDATE Profile_Item
SET Profile_Item.Filterorg=inserted.orgname
FROM Profile_Item, deleted, inserted
WHERE (Profile_Item.FilterOrg = deleted.orgname) AND (Profile_Item.FilterSite is null) AND (Profile_Item.FilterSubOrg is null)
/** CASCADE UPDATES TO 'Profile_Data' */
UPDATE Profile_Data
SET Profile_Data.Dataorg=inserted.orgname
FROM Profile_Data, deleted, inserted
WHERE (Profile_Data.DataOrg = deleted.orgname) AND (Profile_Data.DataSite is null) AND (Profile_Data.DataSubOrg is null)
/** CASCADE UPDATES TO 'ltbBuilding' */
UPDATE ltbBuilding
SET ltbBuilding.orgname=inserted.orgname
FROM ltbBuilding, deleted, inserted
WHERE (ltbBuilding.orgname = deleted.orgname)
/** CASCADE UPDATES TO ' ltbCOE' */
UPDATE ltbCOE
SET ltbCOE.orgname=inserted.orgname
FROM ltbCOE, deleted, inserted
WHERE ( ltbCOE.orgname = deleted.orgname)
UPDATE TASK
SET TASK.orgname=inserted.orgname
FROM TASK, deleted, inserted
WHERE (TASK.orgname = deleted.orgname)
/** Cascade update Orgname and Location to Task_Reminder */
UPDATE Task_Reminder
SET Task_Reminder.OrgName=inserted.OrgName
FROM Task_Reminder, deleted, inserted
WHERE (Task_Reminder.OrgName = deleted.OrgName)
/** Cascade update Orgname and Location to Task_Reminder_Archive */
UPDATE Task_Reminder_Archive
SET Task_Reminder_Archive.OrgName=inserted.OrgName
FROM Task_Reminder_Archive, deleted, inserted
WHERE (Task_Reminder_Archive.OrgName = deleted.OrgName)
UPDATE tblAudit
SET tblAudit.orgname=inserted.orgname
FROM tblAudit, deleted, inserted
WHERE (tblAudit.orgname = deleted.orgname)
UPDATE TblAudit_Step
SET TblAudit_Step.OrgName=inserted.OrgName
FROM TblAudit_Step, deleted, inserted
WHERE (TblAudit_Step.OrgName = deleted.OrgName)
UPDATE ltbScorecardOrg
SET ltbScorecardOrg.org=inserted.orgname
FROM ltbScorecardOrg, deleted, inserted
WHERE (ltbScorecardOrg.org = deleted.orgname)
UPDATE ltbScorecardOrg
SET ltbScorecardOrg.orgname=inserted.orgname
FROM ltbScorecardOrg, deleted, inserted
WHERE (ltbScorecardOrg.orgname = deleted.orgname)
UPDATE Scorecard
SET scorecard.orgname=inserted.orgname
FROM scorecard, deleted, inserted
WHERE (scorecard.orgname = deleted.orgname)
UPDATE supervisor
SET supervisor.orgname=inserted.orgname
FROM supervisor, deleted, inserted
WHERE (supervisor.orgname = deleted.orgname)
UPDATE SiteFindingModule
SET SiteFindingModule.orgname=inserted.orgname
FROM SiteFindingModule, deleted, inserted
WHERE (SiteFindingModule.orgname = deleted.orgname)
UPDATE SiteApplicModule
SET SiteApplicModule.Orgname=inserted.Orgname
FROM SiteApplicModule, deleted, inserted
WHERE (SiteApplicModule.OrgName = deleted.OrgName)
UPDATE Supervisor
SET Supervisor.OrgName=inserted.OrgName
FROM Supervisor, deleted, inserted
WHERE (Supervisor.OrgName = deleted.OrgName)
UPDATE Project
SET Project.orgname=inserted.orgname
FROM project, deleted, inserted
WHERE (project.orgname = deleted.orgname)
END
end
/** This auto-updates suborg field in Org table */
If UPDATE(suborg)
begin
if ( (select count(*) from inserted)=1)
BEGIN
DECLARE @SUBORGLIST AS NVARCHAR(1000)
DECLARE @ORG_IN AS NVARCHAR(50)
DECLARE @SUBORGS AS NVARCHAR(50)
Set @ORG_IN=(Select Orgname from inserted)
Set @SUBORGLIST=''
DECLARE orgSuborg_Cursor CURSOR FOR
SELECT Suborg FROM SubOrg where orgname=@ORG_IN
OPEN orgSubOrg_Cursor
FETCH NEXT FROM orgSuborg_Cursor into @SUBORGS
WHILE @@FETCH_STATUS =0
BEGIN
IF (@SUBORGLIST='' )
BEGIN
Set @SUBORGLIST = @SUBORGS
END
ELSE
BEGIN
Set @SUBORGLIST = @SUBORGLIST +',' + @SUBORGS
END
FETCH NEXT FROM orgSuborg_Cursor into @SUBORGS
END
IF (@SUBORGLIST='' ) UPDATE Org Set Suborg=null where <A href="mailto
rgname=@ORG_IN">Orgname=@ORG_IN Else UPDATE Org Set Suborg=@SUBORGLIST where <A href="mailtorgname=@ORG_IN">Orgname=@ORG_INClose orgSubORg_Cursor
DEALLOCATE orgSuborg_Cursor
END
end
/*Records editing history into Org_History table*/
Insert into Org_history (Orgname,
, Orgpassword, Admin, Suborg, Business, Archive, subbusiness) Select Orgname,
, Orgpassword, Admin, Suborg, Business, Archive, subbusiness from deleted
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Could you indicate which line is Line 198 in your trigger?
-
Look with the profiler to see the exact SQL command generating the error. Try to do the SQL command out of the trigger to reproduce the error . This would close at least the detection part of this 'feature'
Insert in the trigger some log commands use a temporary table to write info inside before the lines with error.Good luck !
-
Another possibility. There is a bug in SP4.
Execute below statement:
ALTER TABLE “table” DISABLE TRIGGER ALL
and run updates(Trigger based).
If you are able to run update without “Internal SQL Server Error”; try following:
Restore the same database on SQL Server 2000 with SP3 and run updates(Trigger based). If you don’t get the “Internal SQL Server Error”, please contact Microsoft.
There is a bug in SP4 and Microsoft will provide you a patch. After installing the patch, you would no more getting such error.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply