asked
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
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.
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)
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
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.
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
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
Wow talk about embarrassed!!!! The code **does** work, but I was re-executing the same test query with the same data - obviously once a user is added, you can't add another with the same username... What a dumb [insert expletive here] ! I've been away from this stuff so long I guess that I don't think like a coder anymore. Thanks again for helping out and especially mjharper - the key was the output parameters. Great community here! Dave
10 People are following this question.
