Solved

sp_addlogin error from TRIGGER

Posted on 1998-11-19
6
1,353 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 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