May 17, 2005 at 12:24 pm
One of our vendor supplied applications uses the following sp to add logins for new users. How can I access the value of @status from this sp? The closest thing I've been able to figure out is to capture the result in a table by using the execute_statement option of the INSERT command. The probelm with that approach is that executing the sp as a part of the INSERT command is generating a transaction and the sp_addlogin sp included in the AddEZLogin sp can not be executed within a transaction.
Example of calling AddEZLogin within INSERT command:
create table #Status (Status char(1))
select @SQL = 'exec AddEZPLogin @UserName, @Password, ''master'' '
insert #Status (Status) exec sp_executesql @SQL, N'@UserName varchar(30), @Password varchar(128), @UserName, @Password
Any suggestions on how to call AddEZLogin as is and be able to access @status?
Thanks
CREATE Procedure AddEZLogin
@login varchar(128),
@pwd varchar(128),
@defdb varchar(128)
As
Declare @status char(1)
IF EXISTS( Select name From master.dbo.sysxlogins Where name = @login ) Begin
Set @status = 'F'
Select @status
return
End
exec sp_addlogin @login, @pwd, @defdb
Set @status = 'S'
Select @status
GO
May 17, 2005 at 12:35 pm
CREATE Procedure dbo.AddEZLogin
@login varchar(128),
@pwd varchar(128),
@defdb varchar(128)
As
IF EXISTS( Select name From master.dbo.sysxlogins Where name = @login ) Begin
return 1 --failed
End
exec sp_addlogin @login, @pwd, @defdb
return 0 --success
GO
--try this part in query analyser
Declare @Return as int
exec @Return = dbo.AddEZLogin 'log', 'pass', 'dbname'
Select @Return
May 17, 2005 at 12:43 pm
Thanks for the response. I really need to be able to call the vendor supplied sp without modifying it.
May 17, 2005 at 12:49 pm
What are you really trying to do? That is, take a step back and explain a little more, you may be asking the wrong question.
May 17, 2005 at 1:01 pm
I'm trying to write a sp that will, among other things, call AddEZLogin and return to me the value of @status. Calling the procedure is not a problem, it's trying to determine the value of @status to verify that the procedure completed successfully. I would really like to use this vendor supplied procedure without making modifications since it is the one that the vendor uses.
With this application we have 10 databases and the vendor supplied interface for adding new users requires logging in and out of each database. I'm trying to create a solution so that I can manage users in all databases from a single application.
Thanks
May 17, 2005 at 1:02 pm
I'll have to have you ask it again too.. because if you can't modify the sp then you're screwed. The only other way around would be to have you write your won little sp that does the same check than the vendor's sp, then call the vendor's sp and then return your return value.
May 17, 2005 at 1:34 pm
if you're writing your own little utility that is only for you and your process (that is you're not passing it on as an aftermarket addon product to this vendor's product), then you can see what their proc does, so just add it.
That is, what is your utility proc for? If you want it to check to see if the users exist, the source of the vendor proc tells you how to do that, and if you want to add a user to multiple databases, then you can see the vender is only using the built in SQL server proc to do that, so do it yourself...
And if you really insist on using the vendor proc, take a look at using xp_cmdshell to call ISQL or OSQL with the -Q option to come back in and exec the proc, capture output to a text file, and examine that at your leisure...
But I'd do the simple sp_addlogin myself (or query against syslogins).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply