Sunday, February 19, 2012
ERROR - Subscription Failure (Please Help!)
How should I resolve this issue?
However, according to the Job executed, the process was successful but the
report manager is displaying the following.
ERROR:
Failure sending mail: The report server has encountered a configuration
error. See the report server log files for more information.
Also, the event viewer is displaying the following error message well.Also, the event viewer is displaying the following error message well.
User: NT AUTHORITY\SYSTEM
Computer: REPORTSERV
Event Type: Failure Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 529
Date: 11/30/2005
Time: 10:49:03 AM
User: NT AUTHORITY\SYSTEM
Computer: REPORTSERV
Description:
Logon Failure:
Reason: Unknown user name or bad password
User Name:
Domain:
Logon Type: 3
Logon Process: Authz
Authentication Package: Kerberos
Workstation Name: REPORTSERV
Caller User Name: NETWORK SERVICE
Caller Domain: NT AUTHORITY
Caller Logon ID: (0x0,0x3E4)
Caller Process ID: 5928
Transited Services: -
Source Network Address: -
Source Port: -
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
"Terry" wrote:
> The report subscription is returning the following error message.
> How should I resolve this issue?
> However, according to the Job executed, the process was successful but the
> report manager is displaying the following.
> ERROR:
> Failure sending mail: The report server has encountered a configuration
> error. See the report server log files for more information.
> Also, the event viewer is displaying the following error message well.
>
>
Error - subquery returning more than one value
I have a trigger that monitors changes to my table fields but I get an error saying subquery returned more than one value.Below is the code for my trigger, hope you will figure out whats happening..
CREATE TRIGGER trgmyTableAuditFields ON myTable
WITH ENCRYPTION
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @.col VARCHAR(200)
DECLARE @.primarykeycol VARCHAR(200)
DECLARE @.sql VARCHAR(2000)
DECLARE @.action VARCHAR(200)
DECLARE @.AuditInsert BIT
DECLARE @.AuditUpdate BIT
DECLARE @.AuditDelete BIT
SELECT @.action =
CASE
WHEN (SELECT COUNT(*) FROM deleted) = 0 THEN 'INSERTION'
WHEN (SELECT COUNT(*) FROM inserted) = 0 THEN 'DELETION'
ELSE 'UPDATE'
END
DECLARE curAuditFields CURSOR FOR
SELECT c.[name], ac.[AuditInsert], ac.[AuditUpdate], ac.[AuditDelete]
FROM syscolumns c
INNER JOIN sysobjects o ON o.[id] = c.[id]
INNER JOIN aucAuditColumns ac ON ac.[Column] = c.[name] AND ac.[Table] = o.[name]
WHERE o.[name] = 'myTable'
AND c.[xtype] NOT IN (35, 99, 34)
OPEN curAuditFields
FETCH NEXT FROM curAuditFields INTO @.col, @.AuditInsert, @.AuditUpdate, @.AuditDelete
SELECT [EffectiveDate],[LowerThresholdAmount],[ThresholdAmount],[Percentage],[ID] INTO #new FROM inserted
SELECT [EffectiveDate],[LowerThresholdAmount],[ThresholdAmount],[Percentage],[ID] INTO #old FROM deleted
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF (@.action = 'INSERTION' AND @.AuditInsert = 1) OR
(@.action = 'UPDATE' AND @.AuditUpdate = 1) OR
(@.action = 'DELETION' AND @.AuditDelete = 1)
BEGIN
SET @.sql = 'IF (SELECT [' + @.col + '] FROM #new) <> (SELECT [' + @.col + '] FROM #old) OR (SELECT COUNT(*) FROM #old) = 0 OR (SELECT COUNT(*) FROM #new) = 0 BEGIN' + CHAR(10) +
'DECLARE @.old VARCHAR(50)' + CHAR(10) +
'DECLARE @.new VARCHAR(50)' + CHAR(10) +
' DECLARE @.RecordVal VARCHAR(50)' + CHAR(10) +
' SELECT @.new = CAST([' + @.col + '] AS VARCHAR(50)) FROM #new' + CHAR(10) +
' SELECT @.old = CAST([' + @.col + '] AS VARCHAR(50)) FROM #old' + CHAR(10) +
' SELECT @.RecordVal = CAST([ID] AS VARCHAR(50)) FROM #old' + CHAR(10) +
'IF @.RecordVal IS NULL BEGIN' + CHAR(10) +
'SELECT @.RecordVal = CAST([ID] AS VARCHAR(50)) FROM #new' + CHAR(10) +
'END' + CHAR(10) +
'INSERT INTO AuditTable([Username], ModificationDate, SourceTable, ModifiedField, OldValue, NewValue, [Action], [RecordID])' + CHAR(10) +
'VALUES (''' + SUSER_SNAME(SUSER_SID()) + ''',''' + CAST(GETDATE() AS VARCHAR(20)) + ''', ''myTable'', ''' + @.col + ''', @.old, @.new, ''' + @.action + ''', @.RecordVal)' + CHAR(10) +
'END'
EXEC (@.sql)
END
FETCH NEXT FROM curAuditFields INTO @.col, @.AuditInsert, @.AuditUpdate, @.AuditDelete
END
DROP TABLE #new
DROP TABLE #old
CLOSE curAuditFields
DEALLOCATE curAuditFields
END
Quote:
Originally Posted by Aleck
Hie.
I have a trigger that monitors changes to my table fields but I get an error saying subquery returned more than one value.Below is the code for my trigger, hope you will figure out whats happening..
CREATE TRIGGER trgmyTableAuditFields ON myTable
WITH ENCRYPTION
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @.col VARCHAR(200)
DECLARE @.primarykeycol VARCHAR(200)
DECLARE @.sql VARCHAR(2000)
DECLARE @.action VARCHAR(200)
DECLARE @.AuditInsert BIT
DECLARE @.AuditUpdate BIT
DECLARE @.AuditDelete BIT
SELECT @.action =
CASE
WHEN (SELECT COUNT(*) FROM deleted) = 0 THEN 'INSERTION'
WHEN (SELECT COUNT(*) FROM inserted) = 0 THEN 'DELETION'
ELSE 'UPDATE'
END
DECLARE curAuditFields CURSOR FOR
SELECT c.[name], ac.[AuditInsert], ac.[AuditUpdate], ac.[AuditDelete]
FROM syscolumns c
INNER JOIN sysobjects o ON o.[id] = c.[id]
INNER JOIN aucAuditColumns ac ON ac.[Column] = c.[name] AND ac.[Table] = o.[name]
WHERE o.[name] = 'myTable'
AND c.[xtype] NOT IN (35, 99, 34)
OPEN curAuditFields
FETCH NEXT FROM curAuditFields INTO @.col, @.AuditInsert, @.AuditUpdate, @.AuditDelete
SELECT [EffectiveDate],[LowerThresholdAmount],[ThresholdAmount],[Percentage],[ID] INTO #new FROM inserted
SELECT [EffectiveDate],[LowerThresholdAmount],[ThresholdAmount],[Percentage],[ID] INTO #old FROM deleted
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF (@.action = 'INSERTION' AND @.AuditInsert = 1) OR
(@.action = 'UPDATE' AND @.AuditUpdate = 1) OR
(@.action = 'DELETION' AND @.AuditDelete = 1)
BEGIN
SET @.sql = 'IF (SELECT [' + @.col + '] FROM #new) <> (SELECT [' + @.col + '] FROM #old) OR (SELECT COUNT(*) FROM #old) = 0 OR (SELECT COUNT(*) FROM #new) = 0 BEGIN' + CHAR(10) +
'DECLARE @.old VARCHAR(50)' + CHAR(10) +
'DECLARE @.new VARCHAR(50)' + CHAR(10) +
' DECLARE @.RecordVal VARCHAR(50)' + CHAR(10) +
' SELECT @.new = CAST([' + @.col + '] AS VARCHAR(50)) FROM #new' + CHAR(10) +
' SELECT @.old = CAST([' + @.col + '] AS VARCHAR(50)) FROM #old' + CHAR(10) +
' SELECT @.RecordVal = CAST([ID] AS VARCHAR(50)) FROM #old' + CHAR(10) +
'IF @.RecordVal IS NULL BEGIN' + CHAR(10) +
'SELECT @.RecordVal = CAST([ID] AS VARCHAR(50)) FROM #new' + CHAR(10) +
'END' + CHAR(10) +
'INSERT INTO AuditTable([Username], ModificationDate, SourceTable, ModifiedField, OldValue, NewValue, [Action], [RecordID])' + CHAR(10) +
'VALUES (''' + SUSER_SNAME(SUSER_SID()) + ''',''' + CAST(GETDATE() AS VARCHAR(20)) + ''', ''myTable'', ''' + @.col + ''', @.old, @.new, ''' + @.action + ''', @.RecordVal)' + CHAR(10) +
'END'
EXEC (@.sql)
END
FETCH NEXT FROM curAuditFields INTO @.col, @.AuditInsert, @.AuditUpdate, @.AuditDelete
END
DROP TABLE #new
DROP TABLE #old
CLOSE curAuditFields
DEALLOCATE curAuditFields
END
i did not continue reading your code...am not sure how efficient it is to put a CUSOR inside a trigger. maybe you would want to find another way of doing this