Error 18456: Login failed for user 'Distributor_admin'

Hi,
When going through the replication wizard to setup transactional replication
the following error is returned when it starts to setup replication.
SQL Server Enterprise Manager could not configure '<four part servername>'
as the distributor for '<four part servername>'
Error 18456: Login failed for user 'Distributor_admin'
Can someone please help. Is it because of the four part server name?
Thanks
It could be. What happens when you create an alias using the client network
utility. After doing this de-register your server in EM, and then reregister
it with the new one part alias.
Is the distributor local?
"mm" <postto@.news.com> wrote in message
news:eIM2gdb$EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Hi,
> When going through the replication wizard to setup transactional
> replication
> the following error is returned when it starts to setup replication.
> SQL Server Enterprise Manager could not configure '<four part servername>'
> as the distributor for '<four part servername>'
> Error 18456: Login failed for user 'Distributor_admin'
>
> Can someone please help. Is it because of the four part server name?
> Thanks
>
>
|||Hi Hilary,
There is no alisa defined in the the client network utility.
This happens when trying to setup a local distributor. However there is a
database on the SQL server that subscribes to a remote distributor.
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eiLeJZi$EHA.3472@.TK2MSFTNGP14.phx.gbl...
> It could be. What happens when you create an alias using the client
network
> utility. After doing this de-register your server in EM, and then
reregister[vbcol=seagreen]
> it with the new one part alias.
> Is the distributor local?
>
> "mm" <postto@.news.com> wrote in message
> news:eIM2gdb$EHA.2196@.TK2MSFTNGP14.phx.gbl...
servername>'
>

Error - subquery returning more than one value

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

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