asked on

sp_addlogin error from TRIGGER

I get the error:
Server: Msg 15002, Level 16, State 1
The procedure 'sp_addlogin' cannot be executed within a transaction.

(1 row(s) affected)
When I insert.

What does this mean? WHat can I do?
When I insert, update, delete from the users table I want to add, delete, update the user for SQL server.

Here's my TRIGGER proc..
I want to manage my users via inserts and deletes from
a foxpro app.

CREATE TRIGGER CREATEUSER ON USERS
FOR INSERT
AS
DECLARE @login varchar(40), @pwd varchar(40), @language int,
@exec_sql varchar(200), @langtext varchar(40)

select @login = username, @pwd = password, @language = language
from users

--1=English
if @language=1
begin
select @langtext='English'
end
--2=Danish
if @language=2
begin
select @langtext='Danish'
end
--3=German
if @language=3
begin
select @langtext='German'
end
--4=Dutch
if @language=4
begin
select @langtext='Dutch'
end
--5=Italian
if @language=5
begin
select @langtext='Italian'
end
--6=French
if @language=6
begin
select @langtext='French'
end
--7=Spanish
if @language=7
begin
select @langtext='spanish'
end

select @exec_sql='sp_addlogin ' +@login+','+@pwd+','+@langtext
EXEC(@exec_sql)

på forhånd tak!

aliciaam

just use:

exec master..sp_addlogin @login, @pwd, @langtext
you don't have to force so many variables to run a stored procedure with variables inside a trigger.

aliciaam

Also, instead of so many if statements you could use CASE OF statement.

aliciaam

begin

case
when language is 1 then ..'ENGLISH'.
when language is 2. then ...
when language is... then ...
.
end
exec sp..addlogin...
end

fox_dogg

ASKER

Thanks for the help!
But I still get the same error.

insert into users (userno,username,password,init,pwchgdate,pwchgwho,
language,company,lastedit1,lastedit2) values(
1.0,
'test',
'sss',
'sss',
GETDATE(),
'sss',
2,
'øsss',
'ssss',
'ssss')

Gives..
Server: Msg 15002, Level 16, State 1
The procedure 'sp_addlogin' cannot be executed within a transaction.
Server: Msg 15002, Level 16, State 1, Procedure sp_addalias, Line 25
The procedure 'sp_addalias' EXEC(@exec_sql)

Here is the stored proc as you said....

CREATE TRIGGER CREATEUSER ON USERS
FOR INSERT
AS
DECLARE @login varchar(40), @pwd varchar(40), @language int,
@exec_sql varchar(200), @langtext varchar(40)

select @login = username, @pwd = password, @language = language
from users

--1=English
select @langtext=
case language
when 1 then 'English'
when 2 then 'Danish'
when 3 then 'German'
when 4 then 'Dutch'
when 5 then 'Italian'
when 6 then 'French'
when 7 then 'Spanish'
end from users

select @exec_sql='master..sp_addlogin ' +@login+','+@pwd+','+@langtext
EXEC(@exec_sql)
select @exec_sql='master..sp_addalias '+ @login+ ",'quest'"
EXEC(@exec_sql)

I used 'master..sp_addalias '+ @login+ ",'quest'"
as you said

ASKER CERTIFIED SOLUTION

tchalkov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fox_dogg

ASKER

Thanks, that worked.