|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-22 : 11:35:18
|
santosh shotemba writes "Hi SQLTEAM,
I’ve run into a strange problem of not being able to get USERID through IDENTITY. Why is it strange ? The following stored procedure runs perfectly well on my development server but not on my live server. The strangest thing is that it is not returning the value of USERID but the value of nMemberID, another output variable.
Tried everything from exporting of tables to creating a new ones on the live server, yet I failed to receive the new USERID returned through IDENTITY. I need to insert this USERID into another table.
Here is my stored procedure and let me explain briefly what it does. It registers users to the central database using extended stored procedure and then inserts the user info on the local table, and finally returns the USERID using SELECT @USERID = @@IDENTITY.
CREATE PROCEDURE up_InsertContestants
(
@firstname varchar(50),
@lastname varchar(50),
@address varchar(100),
@city varchar(20),
@state varchar(20),
@zipcode varchar(10),
@country char(4), -- stores country id
@phone varchar(20),
@email varchar(50),
@q1 char(10),
@q2 char(10),
@website varchar(50)=null,
@software varchar(20)=null,
@userid int OUTPT
)
AS
Begin
set nocount on
declare @rtn int, @szServerName varchar(25), @szDBName varchar(25), @szCmdText varchar(80), @dtDate datetime, @sztmp varchar(25),@nServerFK int,@nMemberID int
select @dtDate = getdate()
/* Get server name, database name and sp name from DBInfo */
select @szServername=szServerName, @szDBName=szDBName from tb_DBInfo where nID=1
select @szCmdText = ( @szServerName + '.' + @szDBname + '..up_Register ' )
/* Update OR Database with member info */
exec @rtn =@szCmdText @nServerFK OUTPUT, @nMemberID OUTPUT,
@szFirstName =@FirstName,
@szLastName =@LastName,
@szEmail =@Email,
@szAddress =@Address,
@szCity =@City,
@szState =@State,
@szZip =@Zipcode,
@szPhoneNumber=@Phone,
@nCountryRes =@Country,
@nRegSource =3,
@dtCreated =@dtDate
if ( @rtn = 0 )
begin
insert into tb_Contestants
(nMemberID,nServerFk,szFirstName,szLastName,szAddress,szCity,szState,szZipCode,szCountry,szPhone,szEmail,szQ1,szQ2,szWebSite,szsoftware)
values(@nServerFK,@nMemberID,@firstname,@lastname,@address,@city,@state,@zipcode,@country,@phone,@email,@q1,@q2,@website,@software)
select @userid = @@identity /* this statement is returning the value of @nMemberID */
end
End
Any help from you guys is greatly appreciated.
cheers,
santosh." |
|
IDENTITY CRISIS