Solved

sp_addlogin error from TRIGGER

Posted on 1998-11-19
6
1,347 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
backup job space check 4 44
Connect to an SQL server ActiveX 10 25
Solar Winds can't see SQL Server Express 17 33
SSMS Opening Mode 9 19
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

860 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