?
Solved

sp_addlogin error from TRIGGER

Posted on 1998-11-19
6
Medium Priority
?
1,365 Views
Last Modified: 2012-06-21
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!

0
Comment
Question by:fox_dogg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 2

Expert Comment

by:aliciaam
ID: 1091881
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.
0
 
LVL 2

Expert Comment

by:aliciaam
ID: 1091882
Also, instead of so many if statements you could use CASE OF statement.
0
 
LVL 2

Expert Comment

by:aliciaam
ID: 1091883
begin

case
when language is 1 then ..'ENGLISH'.
when language is 2. then ...
when language is... then ...
.
end
exec sp..addlogin...
end
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:fox_dogg
ID: 1091884
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
0
 
LVL 7

Accepted Solution

by:
tchalkov earned 150 total points
ID: 1091885
You cannot run sp_addlogin from within a transaction and every trigger has implicit 'begin transaction' executed. however if you need this see the T-SQL statemnet for the sp_addlogin stored procedure, copy it to another stored procedure and remove the lines which checks whether you are in transaction or not.
These are the lines which you have to remove:
IF (@@trancount > 0)
   begin
   RaisError(15002,-1,-1,'sp_addlogin')
   GOTO LABEL_86RETURN
   end

0
 

Author Comment

by:fox_dogg
ID: 1091886
Thanks, that worked.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question