Solved

sp_addlogin error from TRIGGER

Posted on 1998-11-19
6
1,342 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 50 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

912 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now