x

Error on executing SP from inside SP

My MSSQL skills are a little rusty. I've created a query to create a DNN user. The code works perfectly when called directly, but when I call it as part of another SP I get the error:

 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.ZCloneStandardUser", or the name is ambiguous.

The ZCloneStandardUser SP I am calling is :

SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO

 -- =======================================================================================================
 -- Author:        Larry Eisenstein
 -- Create date: 2/15/2011
 -- Description:    Creates a DNN User by copying an existing user.
 -- This can be used to script a single account creation or migrating users from another system.
 -- This works by creating the NewUser from an existing user.
 -- The script was pulled from Mitchel Sellers website.  He has a great blog, so you should visit it.
 -- http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/84/creating-a-standard-dotnetnuke-user-via-sql.aspx
 -- 
 -- 
 -- Req: 
 -- 1. Know the Username/Password of an existing user. The password for the user you create will be the password 
 -- of this user
 -- 2. If you are assigning Roles, the RoleName MUST existing in the DotNetNuke site
 -- 
 -- Defaults:  I set up some defaults, but these can be changed via params or just change the defaults in the script.
 -- 
 -- Notes: 
 -- Stored Procs used: aspnet_Membership_CreateUser
 -- Tables Updated: users, Roles, UserPortals
 
 -- The password works by copying the encrypted password, passwordsalt of the ExistingUser to your new user.  Then, you can
 -- just login with that user's password.
 -- ==========================================================================================================
 
 ALTER PROCEDURE dbo.ZCloneStandardUser
 @ApplicationName varchar(255) = 'DotNetNuke',  -- Search for applicationName in your web.config
 @ExistingUserName varchar(255) = 'TestUser',   -- This must be an existing DNN user
 @FirstName varchar(255) = 'Migrated',            
 @LastName varchar(255) = 'UserAccount',
 @DisplayName varchar(255) = 'Migrated UserAccount',
 @NewUserName varchar(255),
 @RoleName varchar(255) = 'Registered Users',
 @PortalId int = 0,
 @Email nvarchar(256) = 'TestUser@email.org'
 AS
 BEGIN
 
 DECLARE @PasswordQuestion varchar(256),
 @PasswordAnswer varchar(256),
 @Pw varchar(255),
 @PasswordSalt varchar(255),
 @PasswordFormat int,
 @IsApproved bit,
 @CurrentTimeUtc datetime,
 @CreateDate datetime,
 @UniqueEmail int,
 @UserId uniqueidentifier,
 @DNNUserId int,
 @NumUsers int
 
 SELECT    @PasswordQuestion = '',
 @PasswordAnswer = '',
 @IsApproved = 1,
 @CurrentTimeUtc = GETDATE(),
 @CreateDate = @CurrentTimeUtc,
 @UniqueEmail = 0
 
 SELECT @NumUsers = COUNT(*)
 FROM aspnet_Users
 WHERE UserName = @NewUserName
 
 IF(@NumUsers != 0)
 return -1
 
 SELECT    @Pw = m.password,
 @PasswordSalt = m.passwordsalt,
 @PasswordFormat = m.passwordformat
 FROM aspnet_users u
 INNER JOIN aspnet_membership m  ON (u.userid = m.userid)
 WHERE u.UserName = @ExistingUserName
 
 -- Make the stored procedure call
 EXEC dbo.aspnet_Membership_CreateUser @ApplicationName, @NewUserName, @Pw,
 @PasswordSalt, @email, @passwordquestion, @PasswordAnswer, 
 @IsApproved, @CurrentTimeUtc, @CreateDate, @UniqueEmail,
 @PasswordFormat, @UserId
 
 -- Insert the record into the DotNetNuke users table
 INSERT INTO users (Username, FirstName, LastName, IsSuperUser, Email,
 DisplayName, UpdatePassword)
 VALUES(@NewUserName, @FirstName, @LastName, 0, @Email, @DisplayName, 0)
 
 -- Get the new userid, from the DNN users table
 SELECT @dnnuserid = userid
 FROM Users
 WHERE username = @NewUserName
 
 -- Now, insert the record into the user portals table
 INSERT INTO UserPortals (userId, PortalId, CreatedDate)
 VALUES(@dnnuserid, @PortalId, GETDATE()) 
 
 -- Now Give the user permissions to the User Group you specified
 IF(@RoleName != 'Registered Users' and @RoleName IS NOT NULL)
 BEGIN
 INSERT INTO UserRoles (userId, roleId)
 SELECT @dnnuserid,
 roleId
 FROM Roles
 WHERE RoleName = @RoleName
 END
 
 -- Now Give the user permissions to the REGISTERED Users group
 INSERT INTO UserRoles (userId, roleId)
 SELECT @dnnuserid,
 roleId
 FROM Roles
 WHERE RoleName = 'Registered Users'
 
 SELECT @UserID
  
 END

The calling SP is:

 SET ANSI_NULLS ON
 SET QUOTED_IDENTIFIER ON
 GO
 
 
 -- ================================
 -- Created 
 --    by: ZR1netregistry_dnn_usr (Dave Kelly)
 --    on: Saturday, November 24, 2012 7:20 PM
 -- Description: Create a new member record
 -- ================================
 ALTER PROCEDURE dbo.ZRegCreateNewMember
    -- Add the parameters for the procedure here
  @Username nvarchar(100),
  @FirstName nvarchar(50),
  @LastName nvarchar(50),
  @Email          nvarchar(256),
  @DisplayName    nvarchar(100),
  @Street nvarchar (50),
  @Address2 nvarchar(50),
  @City nvarchar(50),
  @Region nvarchar(50),
  @Country int,
  @PostalCode nvarchar(50),
  @Telephone nvarchar(50),
  @Cell nvarchar(50),
  @MagazinePref int, 
  @MemberYear nvarchar(4),
  @PaymentDate DateTime,
  @AmountPaid int,
  @Newsletter int,
  @MembershipExpiry DateTime
  
 AS
 BEGIN
    DECLARE @UserID int
    DECLARE @NewMemberID nvarchar(4)
    DECLARE @MYear int
    DECLARE @MDate DateTime
    DECLARE @MemberID nvarchar(50)
    SET NOCOUNT ON;
 
    -- Add the new user core record and get the new UserID
    SET @UserID =  dbo.ZRegAddUser (0, @UserName, @FirstName, @LastName, 0, 0, @Email, @DisplayName, 1, 1, 1)
    SeT @MDate = GetDate()
    
    -- Set the profile fields
    INSERT INTO dbo.UserProfile VALUES (@UserID, 23, @FirstName, '', 0, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 25, @LastName, '', 0, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 28, @Street, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 76, @Address2, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 29, @City, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 30, @Region, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 31, @Country, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 32, @PostalCode, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 33, @Telephone, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 34, @Cell, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 58, @Newsletter, '', 2, @MDate, '')
 
    -- This is a new member so set the Registry Member Number
    SEt @NewMemberID = dbo.ZRegGetNewMemberID()
    
    -- Update the Member No.
    EXEC dbo.ZRegSetMemberNumber @UserID, @NewMemberID
    
    -- Set Billing Data
    EXEC dbo.ZRegAddNewBilling @NewMemberID, @PaymentDate, @AmountPaid, @MemberYear
  
    -- Add Member Role and expiry
    EXEC dbo.ZRegAddUserRole 0, UserID, 4, 1, FALSE, @MDate, @MembershipExpiry, 1
 
   SELECT @NewMemberID
    
 END

The code is running on SQL Server 2005. Thanks for any help.

Dave

more ▼

asked Nov 28, 2012 at 05:29 PM in Default

avatar image

davekelly
10 1 1 1

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

It's the SET @UserId = dbo.ZCloneStandardUser that it doesn't like. You can do that with a function, but not a stored procedure.

You need to pass the @UserId variable into the procedure as an OUTPUT parmeter instead.

So you'll alter dbo.ZCloneStandardUser to accept an extra parameter @UserId so it begins like this:

 ALTER PROCEDURE dbo.ZRegCreateNewMember
    -- Add the parameters for the procedure here
  @Username nvarchar(100),
  @FirstName nvarchar(50),
  @LastName nvarchar(50),
  @Email          nvarchar(256),
  @DisplayName    nvarchar(100),
  @Street nvarchar (50),
  @Address2 nvarchar(50),
  @City nvarchar(50),
  @Region nvarchar(50),
  @Country int,
  @PostalCode nvarchar(50),
  @Telephone nvarchar(50),
  @Cell nvarchar(50),
  @MagazinePref int, 
  @MemberYear nvarchar(4),
  @PaymentDate DateTime,
  @AmountPaid int,
  @Newsletter int,
  @MembershipExpiry DateTime,
  @UserId uniqueidentifier OUTPUT

and remove it from the DECLARE section. You can also then remove the final SELECT @UserId statment.

Then when calling the procedure from the other one first declare the variable and then pass it into (again specifying it as OUTPUT). It will then return the populated @UserId variable.

Hope that makes sense.

more ▼

answered Nov 28, 2012 at 07:10 PM

avatar image

mjharper
2.2k 3 8 14

(comments are locked)
10|1200 characters needed characters left

The error implies that the procedure dbo.ZCloneStandardUser doesn't exist in your database. Are you sure you created it in the correct database? Also, the calling procedure doesn't seem to reference dbo.ZCloneStandardUser at all.

more ▼

answered Nov 28, 2012 at 06:02 PM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

(comments are locked)
10|1200 characters needed characters left

I've seen this error when you try and SELECT the procedure rather than EXEC it...so for example if you do this:

 SELECT [dbo].[ZCloneStandardUser](@ApplicationName, @ExistingUserName, @FirstName, @LastName, @DisplayName, @NewUserName, @RoleName, @PortalId,  @Email)

instead of this:

 EXEC [dbo].[ZCloneStandardUser](@ApplicationName, @ExistingUserName, @FirstName, @LastName, @DisplayName, @NewUserName, @RoleName, @PortalId,  @Email)
more ▼

answered Nov 28, 2012 at 06:08 PM

avatar image

mjharper
2.2k 3 8 14

(comments are locked)
10|1200 characters needed characters left

Thanks for the replies. My apologies, I've been working on several variants to try figure out how to solve this and pasted the wrong calling SP :-/

Here's the correct one:

 SET ANSI_NULLS ON
 SET QUOTED_IDENTIFIER ON
 GO
 
 
 -- ================================
 -- Created 
 --    by: ZR1netregistry_dnn_usr (Dave Kelly)
 --    on: Saturday, November 24, 2012 7:20 PM
 -- Description: Create a new member record
 -- ================================
 ALTER PROCEDURE dbo.ZRegCreateNewMember
    -- Add the parameters for the procedure here
  @Username nvarchar(100),
  @FirstName nvarchar(50),
  @LastName nvarchar(50),
  @Email          nvarchar(256),
  @DisplayName    nvarchar(100),
  @Street nvarchar (50),
  @Address2 nvarchar(50),
  @City nvarchar(50),
  @Region nvarchar(50),
  @Country int,
  @PostalCode nvarchar(50),
  @Telephone nvarchar(50),
  @Cell nvarchar(50),
  @MagazinePref int, 
  @MemberYear nvarchar(4),
  @PaymentDate DateTime,
  @AmountPaid int,
  @Newsletter int,
  @MembershipExpiry DateTime
  
 AS
 BEGIN
    DECLARE @UserID int
    DECLARE @NewMemberID nvarchar(4)
    DECLARE @MYear int
    DECLARE @MDate DateTime
    DECLARE @MemberID nvarchar(50)
    SET NOCOUNT ON;
 
    -- Add the new user core record and get the new UserID
    -- SET @UserID =  dbo.ZRegAddUser (0, @UserName, @FirstName, @LastName, 0, 0, @Email, @DisplayName, 1, 1, 1)
 
    SET @UserID =  dbo.ZCloneStandardUser ( 'DotNetNuke','dummymember', @FirstName, @LastName, @DisplayName, @FirstName+@LastName, 'ZR-1 Net Member', 0, @Email )
 
 
    SeT @MDate = GetDate()
    
    -- Set the profile fields
    INSERT INTO dbo.UserProfile VALUES (@UserID, 23, @FirstName, '', 0, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 25, @LastName, '', 0, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 28, @Street, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 76, @Address2, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 29, @City, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 30, @Region, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 31, @Country, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 32, @PostalCode, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 33, @Telephone, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 34, @Cell, '', 2, @MDate, '')
    INSERT INTO dbo.UserProfile VALUES (@UserID, 58, @Newsletter, '', 2, @MDate, '')
 
    -- This is a new member so set the Registry Member Number
    SEt @NewMemberID = dbo.ZRegGetNewMemberID()
    
    -- Update the Member No.
    EXEC dbo.ZRegSetMemberNumber @UserID, @NewMemberID
    
    -- Set Billing Data
    EXEC dbo.ZRegAddNewBilling @NewMemberID, @PaymentDate, @AmountPaid, @MemberYear
  
    -- Add Member Role and expiry
    EXEC dbo.ZRegAddUserRole 0, UserID, 4, 1, FALSE, @MDate, @MembershipExpiry, 1
 
   SELECT @NewMemberID
    
 END
more ▼

answered Nov 28, 2012 at 06:43 PM

avatar image

davekelly
10 1 1 1

(comments are locked)
10|1200 characters needed characters left

Thanks for the tips, the changes by mjharper worked and I don't get the original error now. I had to modify them a little as I realized I needed the DNNUserID returning and not the UserID.

Problem is that when I call it it doesn't throw an error, but it doesn't return the DNNUSerID either.

The called procedure is now:

 SET ANSI_NULLS ON
 SET QUOTED_IDENTIFIER ON
 GO
 
 
 -- =======================================================================================================
 -- Author:        Larry Eisenstein
 -- Create date: 2/15/2011
 -- Description:    Creates a DNN User by copying an existing user.
 -- This can be used to script a single account creation or migrating users from another system.
 -- This works by creating the NewUser from an existing user.
 -- The script was pulled from Mitchel Sellers website.  He has a great blog, so you should visit it.
 -- http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/84/creating-a-standard-dotnetnuke-user-via-sql.aspx
 -- 
 -- 
 -- Req: 
 -- 1. Know the Username/Password of an existing user. The password for the user you create will be the password 
 -- of this user
 -- 2. If you are assigning Roles, the RoleName MUST existing in the DotNetNuke site
 -- 
 -- Defaults:  I set up some defaults, but these can be changed via params or just change the defaults in the script.
 -- 
 -- Notes: 
 -- Stored Procs used: aspnet_Membership_CreateUser
 -- Tables Updated: users, Roles, UserPortals
 
 -- The password works by copying the encrypted password, passwordsalt of the ExistingUser to your new user.  Then, you can
 -- just login with that user's password.
 -- ==========================================================================================================
 
 ALTER PROCEDURE dbo.ZCloneStandardUser
 @ApplicationName varchar(255) = 'DotNetNuke',  -- Search for applicationName in your web.config
 @ExistingUserName varchar(255) = 'TestUser',   -- This must be an existing DNN user
 @FirstName varchar(255) = 'Migrated',            
 @LastName varchar(255) = 'UserAccount',
 @DisplayName varchar(255) = 'Migrated UserAccount',
 @NewUserName varchar(255),
 @RoleName varchar(255) = 'Registered Users',
 @PortalId int = 0,
 @Email nvarchar(256) = 'TestUser@email.org',
 @MyUserId int OUTPUT
 
 AS
 BEGIN
 
 DECLARE @PasswordQuestion varchar(256),
 @PasswordAnswer varchar(256),
 @Pw varchar(255),
 @PasswordSalt varchar(255),
 @PasswordFormat int,
 @IsApproved bit,
 @CurrentTimeUtc datetime,
 @CreateDate datetime,
 @UniqueEmail int,
 @UserID uniqueidentifier,
 @NumUsers int,
 @DNNUserID int
 
 SELECT    @PasswordQuestion = '',
 @PasswordAnswer = '',
 @IsApproved = 1,
 @CurrentTimeUtc = GETDATE(),
 @CreateDate = @CurrentTimeUtc,
 @UniqueEmail = 0
 
 SELECT @NumUsers = COUNT(*)
 FROM aspnet_Users
 WHERE UserName = @NewUserName
 
 IF(@NumUsers != 0)
 return -1
 
 SELECT    @Pw = m.password,
 @PasswordSalt = m.passwordsalt,
 @PasswordFormat = m.passwordformat
 FROM aspnet_users u
 INNER JOIN aspnet_membership m  ON (u.userid = m.userid)
 WHERE u.UserName = @ExistingUserName
 
 -- Make the stored procedure call
 EXEC dbo.aspnet_Membership_CreateUser @ApplicationName, @NewUserName, @Pw,
 @PasswordSalt, @email, @passwordquestion, @PasswordAnswer, 
 @IsApproved, @CurrentTimeUtc, @CreateDate, @UniqueEmail,
 @PasswordFormat, @UserId
 
 -- Insert the record into the DotNetNuke users table
 INSERT INTO users (Username, FirstName, LastName, IsSuperUser, Email,
 DisplayName, UpdatePassword)
 VALUES(@NewUserName, @FirstName, @LastName, 0, @Email, @DisplayName, 0)
 
 -- Get the new userid, from the DNN users table
 SELECT @DNNUserID = userid
 FROM Users
 WHERE username = @NewUserName
 
 SELECT @MyUserID = @DNNUserID
 
 -- Now, insert the record into the user portals table
 INSERT INTO UserPortals (userId, PortalId, CreatedDate)
 VALUES(@dnnuserid, @PortalId, GETDATE()) 
 
 -- Now Give the user permissions to the User Group you specified
 IF(@RoleName != 'Registered Users' and @RoleName IS NOT NULL)
 BEGIN
 INSERT INTO UserRoles (userId, roleId)
 SELECT @dnnuserid,
 roleId
 FROM Roles
 WHERE RoleName = @RoleName
 END
 
 -- Now Give the user permissions to the REGISTERED Users group
 INSERT INTO UserRoles (userId, roleId)
 SELECT @DNNUserID,
 roleId
 FROM Roles
 WHERE RoleName = 'Registered Users'
 
 END

And the calling procedure (missing all the later stuff) is:

 ALTER PROCEDURE dbo.ZRegCreateNewMember
    -- Add the parameters for the procedure here
  @Username nvarchar(100),
  @FirstName nvarchar(50),
  @LastName nvarchar(50),
  @Email          nvarchar(256),
  @DisplayName    nvarchar(100),
  @Street nvarchar (50),
  @Address2 nvarchar(50),
  @City nvarchar(50),
  @Region nvarchar(50),
  @Country int,
  @PostalCode nvarchar(50),
  @Telephone nvarchar(50),
  @Cell nvarchar(50),
  @MagazinePref int, 
  @MemberYear nvarchar(4),
  @PaymentDate DateTime,
  @AmountPaid int,
  @Newsletter int,
  @MembershipExpiry DateTime
  
 AS
 BEGIN
    DECLARE @NewMemberID nvarchar(4)
    DECLARE @MYear int
    DECLARE @MDate DateTime
    DECLARE @MemberID nvarchar(50)
    DECLARE @UserID int
 
    SET NOCOUNT ON;
 
    -- Add the new user core record and get the new UserID
    -- SET @UserID =  dbo.ZRegAddUser (0, @UserName, @FirstName, @LastName, 0, 0, @Email, @DisplayName, 1, 1, 1)
 
 PRINT 'Not set ' 
 Print cast(@UserID as varchar(11))
 
    EXEC dbo.ZCloneStandardUser 'DotNetNuke','dummymember', @FirstName, @LastName, @DisplayName, @Username, 'ZR-1 Net Member', 0, @Email, @MyUserID = @UserID OUTPUT
 
 PRINT 'Set '
 PRINT cast(@UserID as varchar(11))


Nothing gets printed after the exec.

Thanks so much for the help!

Dave

more ▼

answered Nov 29, 2012 at 04:42 PM

avatar image

davekelly
10 1 1 1

The code looks ok to me. Does @MyUserID get populated in the Clone procedure? Can you add a PRINT in the Clone procedure? So after: SELECT @MyUserID = @DNNUserID add: PRINT @MyUserID

I'm just trying to work out if it's not getting passed out correctly or if it's not populated to start with. Cheers

Nov 29, 2012 at 04:58 PM mjharper
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2030
x476

asked: Nov 28, 2012 at 05:29 PM

Seen: 1364 times

Last Updated: Nov 30, 2012 at 02:31 AM

Copyright 2017 Redgate Software. Privacy Policy