problem with before insert/update trigger

hey guys!
i'm not able to understand the problem. the situation is like this,
i've a table that has before insert/update trigger which checks for
some data in some tables and if the data is not found it suppose to
throw an error.
to get the data, that is inserted into the table, i'm using this query
select @.ResourceID=ResourceID from inserted
but this returns me nothing and so the rest of the process is failing.
the error threw was this :
Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
30
and i've no clue about this error. even not able to think any other
solution.
please guys help me out here, i'm tired of this Database thing,
thanks,
Luckylucky wrote:
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
What happens when multiple rows are inserted? Your trigger isn't
written to properly handle multi-row inserts.

> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In order for us to 'see' your issue, please provide the table DDL, the
entire Trigger code, and perhaps a few rows of sample data in the form of
INSERT statements.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164899748.814785.48970@.l39g2000cwd.googlegroups.com...
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>|||Hi Tracy ,
your guess is correct. i'm only checking for a single ID in the
trigger. i dont know how can i handle the situation where i would have
mulitple rows.
i thought the trigger will be triggered each time row is getting
inserted. i dont know how the trigger behaves in bulk insert/update.
by the way here is the code for the trigger. please give it a look and
advise me how can i modify it to handle the bulk insert/update.
alter TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
declare @.ResourceID uniqueidentifier
declare @.msg varchar(255)
declare @.flag bit
set @.flag=0
select @.ResourceID=ResourceID from inserted
IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
set @.flag=1
if(@.flag=0)
begin
set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
)
RAISERROR ( @.msg,16, 1 )
rollback transaction
end
----
--
Please let me know if u need some more information on it.
thanks,
Lucky|||lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> ----
--
> Please let me know if u need some more information on it.
> thanks,
> Lucky
>
Untested, but I think something like this will work better for you:
IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
BEGIN
DROP TRIGGER Table10.trigg_insert_row
END
GO
CREATE TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
DECLARE @.ResourceID UNIQUEIDENTIFIER
DECLARE @.msg VARCHAR(255)
SELECT TOP 1 @.ResourceID = ResourceID
FROM
(
SELECT ResourceID
FROM inserted
LEFT JOIN Table1
ON inserted.ResourceID = Table1.ID
WHERE Table1.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table2
ON inserted.ResourceID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table3
ON inserted.ResourceID = Table3.ID
WHERE Table3.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table4
ON inserted.ResourceID = Table4.ID
WHERE Table4.ID IS NULL
) AS MissingIDs
IF @.ResourceID IS NULL
BEGIN
SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
RAISERROR (@.msg, 16, 1)
ROLLBACK TRANSACTION
END
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi guys,
i was doing some R&D and i found that when i wrote a query :
select * from inserted
into the trigger, the query returned me no rows and that is why the
rest of the code failed. it is very very streng that before insert
trigger is getting triggered but it is not able to find any data in the
INSERTED table.
data i'm inserting into the table using Stored Procedure. i also check
into the procedure that it is getting the correct data and it showed me
that it is getting the correct data but when it is firing the INSERT
STATEMENT, the trigger on the table is not able to find the data.
Man! i'm not able to understand anything about this.
guys, deadline is very near and i need to finish this as soon as
possible. please help me out.
thanks,
Lucky
lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> ----
--
> Please let me know if u need some more information on it.
> thanks,
> Lucky|||Hi Tracy,
Thanks for you great help. the solution u provided worked very well.
another problem i posted was not in the Trigger but it was in the SP.
i'm still working on it but the problem of bulk insertion is solve,
thanks to you.
Lucky
Tracy McKibben wrote:
> lucky wrote:
> Untested, but I think something like this will work better for you:
> IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
> BEGIN
> DROP TRIGGER Table10.trigg_insert_row
> END
> GO
> CREATE TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> DECLARE @.ResourceID UNIQUEIDENTIFIER
> DECLARE @.msg VARCHAR(255)
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table2
> ON inserted.ResourceID = Table2.ID
> WHERE Table2.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table3
> ON inserted.ResourceID = Table3.ID
> WHERE Table3.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table4
> ON inserted.ResourceID = Table4.ID
> WHERE Table4.ID IS NULL
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
> END
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Tracy McKibben wrote:

> Untested, but I think something like this will work better for you:
[snip]
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
[snip]
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
*scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?|||Ed Murphy wrote:
> Tracy McKibben wrote:
>
> [snip]
> [snip]
> *scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
Yep, it should be. I did say "untested"... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com

problem with before insert/update trigger

hey guys!
i'm not able to understand the problem. the situation is like this,
i've a table that has before insert/update trigger which checks for
some data in some tables and if the data is not found it suppose to
throw an error.
to get the data, that is inserted into the table, i'm using this query
select @.ResourceID=ResourceID from inserted
but this returns me nothing and so the rest of the process is failing.
the error threw was this :
Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
30
and i've no clue about this error. even not able to think any other
solution.
please guys help me out here, i'm tired of this Database thing,
thanks,
Luckylucky wrote:
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
What happens when multiple rows are inserted? Your trigger isn't
written to properly handle multi-row inserts.
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In order for us to 'see' your issue, please provide the table DDL, the
entire Trigger code, and perhaps a few rows of sample data in the form of
INSERT statements.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164899748.814785.48970@.l39g2000cwd.googlegroups.com...
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>|||Hi Tracy ,
your guess is correct. i'm only checking for a single ID in the
trigger. i dont know how can i handle the situation where i would have
mulitple rows.
i thought the trigger will be triggered each time row is getting
inserted. i dont know how the trigger behaves in bulk insert/update.
by the way here is the code for the trigger. please give it a look and
advise me how can i modify it to handle the bulk insert/update.
alter TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
declare @.ResourceID uniqueidentifier
declare @.msg varchar(255)
declare @.flag bit
set @.flag=0
select @.ResourceID=ResourceID from inserted
IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
set @.flag=1
if(@.flag=0)
begin
set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
)
RAISERROR ( @.msg,16, 1 )
rollback transaction
end
-----
Please let me know if u need some more information on it.
thanks,
Lucky|||lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky
>
Untested, but I think something like this will work better for you:
IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
BEGIN
DROP TRIGGER Table10.trigg_insert_row
END
GO
CREATE TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
DECLARE @.ResourceID UNIQUEIDENTIFIER
DECLARE @.msg VARCHAR(255)
SELECT TOP 1 @.ResourceID = ResourceID
FROM
(
SELECT ResourceID
FROM inserted
LEFT JOIN Table1
ON inserted.ResourceID = Table1.ID
WHERE Table1.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table2
ON inserted.ResourceID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table3
ON inserted.ResourceID = Table3.ID
WHERE Table3.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table4
ON inserted.ResourceID = Table4.ID
WHERE Table4.ID IS NULL
) AS MissingIDs
IF @.ResourceID IS NULL
BEGIN
SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
RAISERROR (@.msg, 16, 1)
ROLLBACK TRANSACTION
END
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi guys,
i was doing some R&D and i found that when i wrote a query :
select * from inserted
into the trigger, the query returned me no rows and that is why the
rest of the code failed. it is very very streng that before insert
trigger is getting triggered but it is not able to find any data in the
INSERTED table.
data i'm inserting into the table using Stored Procedure. i also check
into the procedure that it is getting the correct data and it showed me
that it is getting the correct data but when it is firing the INSERT
STATEMENT, the trigger on the table is not able to find the data.
Man! i'm not able to understand anything about this.
guys, deadline is very near and i need to finish this as soon as
possible. please help me out.
thanks,
Lucky
lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky|||Hi Tracy,
Thanks for you great help. the solution u provided worked very well.
another problem i posted was not in the Trigger but it was in the SP.
i'm still working on it but the problem of bulk insertion is solve,
thanks to you.
Lucky
Tracy McKibben wrote:
> lucky wrote:
> > Hi Tracy ,
> > your guess is correct. i'm only checking for a single ID in the
> > trigger. i dont know how can i handle the situation where i would have
> > mulitple rows.
> > i thought the trigger will be triggered each time row is getting
> > inserted. i dont know how the trigger behaves in bulk insert/update.
> >
> > by the way here is the code for the trigger. please give it a look and
> > advise me how can i modify it to handle the bulk insert/update.
> >
> > alter TRIGGER trigg_insert_row
> > ON [Table10]
> > FOR INSERT, UPDATE
> > AS
> >
> > declare @.ResourceID uniqueidentifier
> > declare @.msg varchar(255)
> > declare @.flag bit
> > set @.flag=0
> >
> > select @.ResourceID=ResourceID from inserted
> >
> > IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > if(@.flag=0)
> > begin
> > set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> > )
> > RAISERROR ( @.msg,16, 1 )
> > rollback transaction
> > end
> >
> >
> > -----
> > Please let me know if u need some more information on it.
> >
> > thanks,
> >
> > Lucky
> >
> Untested, but I think something like this will work better for you:
> IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
> BEGIN
> DROP TRIGGER Table10.trigg_insert_row
> END
> GO
> CREATE TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> DECLARE @.ResourceID UNIQUEIDENTIFIER
> DECLARE @.msg VARCHAR(255)
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table2
> ON inserted.ResourceID = Table2.ID
> WHERE Table2.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table3
> ON inserted.ResourceID = Table3.ID
> WHERE Table3.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table4
> ON inserted.ResourceID = Table4.ID
> WHERE Table4.ID IS NULL
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
> END
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Tracy McKibben wrote:
> Untested, but I think something like this will work better for you:
[snip]
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
[snip]
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
*scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?|||Ed Murphy wrote:
> Tracy McKibben wrote:
>> Untested, but I think something like this will work better for you:
> [snip]
>> SELECT TOP 1 @.ResourceID = ResourceID
>> FROM
>> (
>> SELECT ResourceID
>> FROM inserted
>> LEFT JOIN Table1
>> ON inserted.ResourceID = Table1.ID
>> WHERE Table1.ID IS NULL
>> UNION
> [snip]
>> ) AS MissingIDs
>> IF @.ResourceID IS NULL
>> BEGIN
>> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
>> RAISERROR (@.msg, 16, 1)
>> ROLLBACK TRANSACTION
> *scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
Yep, it should be. I did say "untested"... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

problem with before insert/update trigger

hey guys!
i'm not able to understand the problem. the situation is like this,
i've a table that has before insert/update trigger which checks for
some data in some tables and if the data is not found it suppose to
throw an error.
to get the data, that is inserted into the table, i'm using this query
select @.ResourceID=ResourceID from inserted
but this returns me nothing and so the rest of the process is failing.
the error threw was this :
Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
30
and i've no clue about this error. even not able to think any other
solution.
please guys help me out here, i'm tired of this Database thing,
thanks,
Lucky
lucky wrote:
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
What happens when multiple rows are inserted? Your trigger isn't
written to properly handle multi-row inserts.

> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||In order for us to 'see' your issue, please provide the table DDL, the
entire Trigger code, and perhaps a few rows of sample data in the form of
INSERT statements.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164899748.814785.48970@.l39g2000cwd.googlegro ups.com...
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
|||Hi Tracy ,
your guess is correct. i'm only checking for a single ID in the
trigger. i dont know how can i handle the situation where i would have
mulitple rows.
i thought the trigger will be triggered each time row is getting
inserted. i dont know how the trigger behaves in bulk insert/update.
by the way here is the code for the trigger. please give it a look and
advise me how can i modify it to handle the bulk insert/update.
alter TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
declare @.ResourceID uniqueidentifier
declare @.msg varchar(255)
declare @.flag bit
set @.flag=0
select @.ResourceID=ResourceID from inserted
IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
set @.flag=1
if(@.flag=0)
begin
set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
)
RAISERROR ( @.msg,16, 1 )
rollback transaction
end
-----
Please let me know if u need some more information on it.
thanks,
Lucky
|||lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky
>
Untested, but I think something like this will work better for you:
IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
BEGIN
DROP TRIGGER Table10.trigg_insert_row
END
GO
CREATE TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
DECLARE @.ResourceID UNIQUEIDENTIFIER
DECLARE @.msg VARCHAR(255)
SELECT TOP 1 @.ResourceID = ResourceID
FROM
(
SELECT ResourceID
FROM inserted
LEFT JOIN Table1
ON inserted.ResourceID = Table1.ID
WHERE Table1.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table2
ON inserted.ResourceID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table3
ON inserted.ResourceID = Table3.ID
WHERE Table3.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table4
ON inserted.ResourceID = Table4.ID
WHERE Table4.ID IS NULL
) AS MissingIDs
IF @.ResourceID IS NULL
BEGIN
SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
RAISERROR (@.msg, 16, 1)
ROLLBACK TRANSACTION
END
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi guys,
i was doing some R&D and i found that when i wrote a query :
select * from inserted
into the trigger, the query returned me no rows and that is why the
rest of the code failed. it is very very streng that before insert
trigger is getting triggered but it is not able to find any data in the
INSERTED table.
data i'm inserting into the table using Stored Procedure. i also check
into the procedure that it is getting the correct data and it showed me
that it is getting the correct data but when it is firing the INSERT
STATEMENT, the trigger on the table is not able to find the data.
Man! i'm not able to understand anything about this.
guys, deadline is very near and i need to finish this as soon as
possible. please help me out.
thanks,
Lucky
lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky
|||Hi Tracy,
Thanks for you great help. the solution u provided worked very well.
another problem i posted was not in the Trigger but it was in the SP.
i'm still working on it but the problem of bulk insertion is solve,
thanks to you.
Lucky
Tracy McKibben wrote:
> lucky wrote:
> Untested, but I think something like this will work better for you:
> IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
> BEGIN
> DROP TRIGGER Table10.trigg_insert_row
> END
> GO
> CREATE TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> DECLARE @.ResourceID UNIQUEIDENTIFIER
> DECLARE @.msg VARCHAR(255)
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table2
> ON inserted.ResourceID = Table2.ID
> WHERE Table2.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table3
> ON inserted.ResourceID = Table3.ID
> WHERE Table3.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table4
> ON inserted.ResourceID = Table4.ID
> WHERE Table4.ID IS NULL
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
> END
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Tracy McKibben wrote:

> Untested, but I think something like this will work better for you:
[snip]
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
[snip]
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
*scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
|||Ed Murphy wrote:
> Tracy McKibben wrote:
> [snip]
> [snip]
> *scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
Yep, it should be. I did say "untested"... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com

problem with ##xp_cmdshell_proxy_account##

Hi,
I was trying to debug a situation where we need to use xp_cmdshell in
SQL2005 SP2 build 3175. I had created the ##xp_cmdshell_proxy_account##
proxy credential and wanted to delete it to see that I was getting a message
about the credential missing. After running the sp_xp_cmdshell_proxy_account
stored proc with NULL I ran it again to re-create the proxy credential but
this time I got message: -
Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
An error occurred during the execution of sp_xp_cmdshell_proxy_account.
Possible reasons: the provided account was invalid or the
'##xp_cmdshell_proxy_account##' credential could not be created. Error code:
'997'.
This is on a clustered server so do I need to flip the cluster for the
credential to be completed deleted before trying to add it again?
Thanks
ChrisI am having the same issue. I am simply trying to assign xp_cmdshell a proxy
account and its not allowing me to. I know that the username/password is a
valid one.
Any help would be appreciated. Thanks Amir
"Chris Wood" wrote:
> Hi,
> I was trying to debug a situation where we need to use xp_cmdshell in
> SQL2005 SP2 build 3175. I had created the ##xp_cmdshell_proxy_account##
> proxy credential and wanted to delete it to see that I was getting a message
> about the credential missing. After running the sp_xp_cmdshell_proxy_account
> stored proc with NULL I ran it again to re-create the proxy credential but
> this time I got message: -
> Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
> An error occurred during the execution of sp_xp_cmdshell_proxy_account.
> Possible reasons: the provided account was invalid or the
> '##xp_cmdshell_proxy_account##' credential could not be created. Error code:
> '997'.
> This is on a clustered server so do I need to flip the cluster for the
> credential to be completed deleted before trying to add it again?
> Thanks
> Chris
>
>
>
>|||Amir,
Rather than run the stored proc I just created the proxy credential by using
the create credential T-SQL statement.
Chris
"Amir" <Amir@.discussions.microsoft.com> wrote in message
news:61B89673-4894-419A-9D82-6D99C7559878@.microsoft.com...
>I am having the same issue. I am simply trying to assign xp_cmdshell a
>proxy
> account and its not allowing me to. I know that the username/password is a
> valid one.
> Any help would be appreciated. Thanks Amir
> "Chris Wood" wrote:
>> Hi,
>> I was trying to debug a situation where we need to use xp_cmdshell in
>> SQL2005 SP2 build 3175. I had created the ##xp_cmdshell_proxy_account##
>> proxy credential and wanted to delete it to see that I was getting a
>> message
>> about the credential missing. After running the
>> sp_xp_cmdshell_proxy_account
>> stored proc with NULL I ran it again to re-create the proxy credential
>> but
>> this time I got message: -
>> Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account,
>> Line 1
>> An error occurred during the execution of sp_xp_cmdshell_proxy_account.
>> Possible reasons: the provided account was invalid or the
>> '##xp_cmdshell_proxy_account##' credential could not be created. Error
>> code:
>> '997'.
>> This is on a clustered server so do I need to flip the cluster for the
>> credential to be completed deleted before trying to add it again?
>> Thanks
>> Chris
>>
>>
>>

Problem with "Delivering Replicated Transactions"

Greetings! Any help with the following situation would=20
be greatly appreciated. We have a push transactional=20
replication of a subset of the tables in a production=20
database involving 3 machines; the production db machine, =20
a distribution db machine, and a subscriber db machine to=20
which this table subset is replicated. The subscriber is=20
used for complex searches and has 6 indexed views resident=20
on it. All replication agents are set to run continuously.=20
The Distribution Agent profiles have been left at the=20
defaults except for QueryTimeout, which has been set to=20
3600.
Each morning recently we have seen the distribution=20
agent showing "Delivering Replicated Transactions", a=20
state which lasts approximately 1=BD hours. Since our search=20
volume is minimal in the wee hours we would like to shift=20
this state to occur at 2 or 3 AM. Is there any way to=20
eliminate or control the timing of this condition by=20
further adjusting Agent parameters, etc. Thank you.
Not by adjusting parameters. You can control when the agent runs through
scheduling. You would essentially go into the job running your distribution
agent and change the frequency or even the hours during which it will run.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Thanks for your reply, but it's necessary for us to run
the Distribution Agent continously to keep latency to a
minimum. Any other suggestions, especially in light of the
indexed views that must be continuously updated on the
subscriber?
>--Original Message--
>Not by adjusting parameters. You can control when the
agent runs through
>scheduling. You would essentially go into the job
running your distribution
>agent and change the frequency or even the hours during
which it will run.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
|||It seems there must be some batch operation occurring on your Publisher
which causes this "delivering replicated transactions" message.
See if you can isolate it using profiler on the publisher, distributor or
subscriber.
Then see if you can't change when this job kicks off.
Also try to replication the execution of a stored procedure to minimize the
impact of this process on your publisher/distributor.
"Fundster" <anonymous@.discussions.microsoft.com> wrote in message
news:2e9701c4288e$52687120$a001280a@.phx.gbl...
Greetings! Any help with the following situation would
be greatly appreciated. We have a push transactional
replication of a subset of the tables in a production
database involving 3 machines; the production db machine,
a distribution db machine, and a subscriber db machine to
which this table subset is replicated. The subscriber is
used for complex searches and has 6 indexed views resident
on it. All replication agents are set to run continuously.
The Distribution Agent profiles have been left at the
defaults except for QueryTimeout, which has been set to
3600.
Each morning recently we have seen the distribution
agent showing "Delivering Replicated Transactions", a
state which lasts approximately 1 hours. Since our search
volume is minimal in the wee hours we would like to shift
this state to occur at 2 or 3 AM. Is there any way to
eliminate or control the timing of this condition by
further adjusting Agent parameters, etc. Thank you.

Problem when creating FK

Hello everyone,

I'm created a new SQL Server 2005 database for a new project and I'm facing a little situation regarding FKs. I guess that's what you get when you move from MySql 4 to Sql Server 2005 ;)

Here's the situation: I have a table called contact, assigned to the schema person, and that table handles all the base information regarding users. Informations such as FirstName, LastName, etc, are stored in the [Person].[Contact]. So far so good.

As a design decision I've decided to keep track of the nick names (Alias) that a given member had over-time. In order to do this I've designed the [Person].[GlobalAlias] table like this:

Id uniqueidentifier [NOT NULL] [PK]
ContactId uniqueidentifier [NOT NULL] [FK Person.Contact Id]
Alias nvarchar(30) [NOT NULL]
AddedByContactId uniqueidentifier [NOT NULL] [FK Person.Contact Id]
AddedDate datetime [NOT NULL]
AddedObs nvarchar(MAX) [NULL]
RemovedByContactId uniqueidentifier [NULL] [FK Person.Contact Id]
RemovedDate datetime [NULL]
RemovedObs nvarchar(MAX) [NULL]
ModifiedDate datetime [NOT NULL]
Status bit [NOT NULL]

This table seems to work great dispite the fact that it generates an error when I try to set the FKs. For the column [Person].[GlobalAlias] ContactId I set a FK to the column [Person].[Contact] Id with cascade on delete and on update. Now, when I go to the [Person].[GlobalAlias] AddedByMembershipId and try to add a FK to the column [Person].[Contact] Id it works as long as I define NO ACTION on delete and on update. Obviously, this could originate rows where the [Person].[Contact] that added a [Person].[GlobalAlias] was deleted and there's still a reference to that ID in the [Person].[GlobalAlias] AddedByMembershipId

Here's the error that I'm getting from the database:

'Contact (Person)' table saved successfully
'GlobalAlias (Person)' table
- Unable to create relationship 'FK_GlobalAlias_AddedByContactId_Contact_Id'.
Introducing FOREIGN KEY constraint 'FK_GlobalAlias_AddedByContactId_Contact_Id' on table 'GlobalAlias' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

How could I solve this? Perhaps the only way is to normalize it even further and create a history table?

Best regards,
DBA

That is pretty much what you appear to have now. Just that you are trying to set up foreign key relationships into it, which you probably shouldn't.

With a cascading delete, if you deleted someone, the delete would want to then cascade to every record in your GlobalAlias table that referenced them (Every alias they ever had, and any alias's they had ever created for someone else, or removed from someone else). That probably isn't what you wanted. I could see possibly wanting the updates to cascade, but you normally don't want users to be able to change their uid, so the FK really isn't all that useful for cascading updates.

problem w/ sp_change_users_login on 64bit SQL Server 2005?

Here's the situation: I just restored a full backup of a SQL Server 2K
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen
> How can it work on one and not the other?
Because these are different instances and might not have the same logins.

> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>
|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

problem w/ sp_change_users_login on 64bit SQL Server 2005?

Here's the situation: I just restored a full backup of a SQL Server 2K
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen> How can it work on one and not the other?
Because these are different instances and might not have the same logins.

> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo

problem w/ sp_change_users_login on 64bit SQL Server 2005?

Here's the situation: I just restored a full backup of a SQL Server 2K
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen> How can it work on one and not the other?
Because these are different instances and might not have the same logins.
> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo