Solved

sp_addlogin error from TRIGGER

Posted on 1998-11-19
6
1,337 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
Comment Utility
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
Comment Utility
Also, instead of so many if statements you could use CASE OF statement.
0
 
LVL 2

Expert Comment

by:aliciaam
Comment Utility
begin

case
when language is 1 then ..'ENGLISH'.
when language is 2. then ...
when language is... then ...
.
end
exec sp..addlogin...
end
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:fox_dogg
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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

14 Experts available now in Live!

Get 1:1 Help Now