Problems with Stored procedures

I am trying to recreate my database from work to my home machines. But use
Sql 2000.
One error I get is that bigint is invalid type - but my tables have bigints
in then
Another is that Scope_Identity is not valid - but it works fine at work.
Here is one Stored procedure (with errors at end).
****************************************
**************
CREATE PROCEDURE AddNewApplicantScreen
(
@.ClientID varChar(20),@.JobID bigInt,@.ApplicantID bigInt,@.PositionID
Int,@.Version Int, @.QuestionUnique Int, @.Answer Int,@.AnswerTime Int
)
AS
if not exists (Select ApplicantID from ftsolutions.dbo.ApplicantScreen
where ClientID = @.ClientID and JobID = @.JobID and ApplicantID =
@.ApplicantID and PositionID = @.PositionID and Version = @.Version
and QuestionUnique = @.QuestionUnique)
insert into
ApplicantScreen(ClientID,JobID,Applicant
ID,PositionID,Version,QuestionUnique
,Answer,AnswerTime)
values(@.ClientID,@.JobID,@.ApplicantID,@.Po
sitionID,@.Version,@.QuestionUnique,@.A
nswer,@.AnswerTime)
else
Update ftsolutions.dbo.ApplicantScreen set Answer=@.Answer,
AnswerTime=@.AnswerTime
where ClientID = @.ClientID and JobID = @.JobID and ApplicantID =
@.ApplicantID and PositionID = @.PositionID and Version = @.Version
and QuestionUnique = @.QuestionUnique
GO
Server: Msg 2715, Level 16, State 3, Procedure AddNewApplicantScreen, Line 0
Column or parameter #2: Cannot find data type bigint.
Server: Msg 2715, Level 16, State 1, Procedure AddNewApplicantScreen, Line 0
Column or parameter #3: Cannot find data type bigint.
Parameter '@.JobID' has an invalid data type.
Parameter '@.ApplicantID' has an invalid data type.
****************************************
************************************
***
Here is another:
****************************************
************************************
***
CREATE PROCEDURE spAddNewResume
(
@.ClientID varChar(20),@.PositionID Int,@.FirstName varChar(30),@.LastName
varChar(30),@.Email varChar(45),@.TicklerPhrase varChar(45),@.ResumeText
text,@.CoverSheet text
)
AS
declare @.ApplicantID int, @.JobID bigInt
if not exists (Select ApplicantID from ftsolutions.dbo.Applicant where
ClientID = @.ClientID and
LastName = @.LastName and FirstName = @.FirstName and Email = @.Email)
begin
begin tran
INSERT INTO Applicant
(ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted)
Select @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant
where ClientID = @.ClientID
Select @.JobID = Scope_Identity()
Select @.ApplicantID=ApplicantID from ftsolutions.dbo.Applicant where JobID
= @.JobID
commit tran
end
else
begin
Select @.ApplicantID = ApplicantID from Applicant where ClientID = @.ClientID
and
LastName = @.LastName and FirstName = @.FirstName and Email = @.Email
INSERT INTO Applicant
(ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted) values
(@.ClientID,@.ApplicantID,@.PositionID,@.Fir
stName,
@.LastName,@.Email,getdate() )
Select @.JobID = Scope_Identity()
end
INSERT INTO ApplicantResume
(ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,TicklerPhrase,Resu
meText,CoverSheet,JobID) values
(
@.ClientID,@.ApplicantID,@.PositionID,@.Firs
tName,@.LastName,@.Email,@.TicklerPhras
e,@.ResumeText,@.CoverSheet,@.JobID)
INSERT INTO JobApplicant (ClientID,ApplicantID,PositionID,JobID,R
esume)
Values(@.ClientID,@.ApplicantID,@.PositionI
D,@.JobID,getdate() )
select @.ApplicantID as ApplicantID,@.JobID as JobID
GO
Server: Msg 195, Level 15, State 10, Procedure spAddNewResume, Line 17
'Scope_Identity' is not a recognized function name.
Server: Msg 195, Level 15, State 1, Procedure spAddNewResume, Line 29
'Scope_Identity' is not a recognized function name.
****************************************
************************************
**
Why would that happen?
Thanks,
TomWhat does the following return on your home machine?
SELECT @.@.VERSION
Hope this helps.
Dan Guzman
SQL Server MVP
"tshad" <tfs@.dslextreme.com> wrote in message
news:OawFG9uGFHA.3156@.TK2MSFTNGP10.phx.gbl...
>I am trying to recreate my database from work to my home machines. But use
> Sql 2000.
> One error I get is that bigint is invalid type - but my tables have
> bigints
> in then
> Another is that Scope_Identity is not valid - but it works fine at work.
> Here is one Stored procedure (with errors at end).
> ****************************************
**************
> CREATE PROCEDURE AddNewApplicantScreen
> (
> @.ClientID varChar(20),@.JobID bigInt,@.ApplicantID bigInt,@.PositionID
> Int,@.Version Int, @.QuestionUnique Int, @.Answer Int,@.AnswerTime Int
> )
> AS
> if not exists (Select ApplicantID from ftsolutions.dbo.ApplicantScreen
> where ClientID = @.ClientID and JobID = @.JobID and ApplicantID =
> @.ApplicantID and PositionID = @.PositionID and Version = @.Version
> and QuestionUnique = @.QuestionUnique)
> insert into
> ApplicantScreen(ClientID,JobID,Applicant
ID,PositionID,Version,QuestionUniq
ue
> ,Answer,AnswerTime)
> values(@.ClientID,@.JobID,@.ApplicantID,@.Po
sitionID,@.Version,@.QuestionUnique,
@.A
> nswer,@.AnswerTime)
> else
> Update ftsolutions.dbo.ApplicantScreen set Answer=@.Answer,
> AnswerTime=@.AnswerTime
> where ClientID = @.ClientID and JobID = @.JobID and ApplicantID =
> @.ApplicantID and PositionID = @.PositionID and Version = @.Version
> and QuestionUnique = @.QuestionUnique
> GO
>
> Server: Msg 2715, Level 16, State 3, Procedure AddNewApplicantScreen, Line
> 0
> Column or parameter #2: Cannot find data type bigint.
> Server: Msg 2715, Level 16, State 1, Procedure AddNewApplicantScreen, Line
> 0
> Column or parameter #3: Cannot find data type bigint.
> Parameter '@.JobID' has an invalid data type.
> Parameter '@.ApplicantID' has an invalid data type.
> ****************************************
**********************************
**
> ***
> Here is another:
> ****************************************
**********************************
**
> ***
> CREATE PROCEDURE spAddNewResume
> (
> @.ClientID varChar(20),@.PositionID Int,@.FirstName varChar(30),@.LastName
> varChar(30),@.Email varChar(45),@.TicklerPhrase varChar(45),@.ResumeText
> text,@.CoverSheet text
> )
> AS
> declare @.ApplicantID int, @.JobID bigInt
> if not exists (Select ApplicantID from ftsolutions.dbo.Applicant where
> ClientID = @.ClientID and
> LastName = @.LastName and FirstName = @.FirstName and Email = @.Email)
> begin
> begin tran
> INSERT INTO Applicant
> (ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted)
> Select
> @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,@.FirstName,
> @.LastName,@.Email,getdate()
> from ftsolutions.dbo.Applicant
> where ClientID = @.ClientID
> Select @.JobID = Scope_Identity()
> Select @.ApplicantID=ApplicantID from ftsolutions.dbo.Applicant where
> JobID
> = @.JobID
> commit tran
> end
> else
> begin
> Select @.ApplicantID = ApplicantID from Applicant where ClientID =
> @.ClientID
> and
> LastName = @.LastName and FirstName = @.FirstName and Email = @.Email
> INSERT INTO Applicant
> (ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted)
> values
> (@.ClientID,@.ApplicantID,@.PositionID,@.Fir
stName,
> @.LastName,@.Email,getdate() )
> Select @.JobID = Scope_Identity()
> end
> INSERT INTO ApplicantResume
> (ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,TicklerPhrase,Re
su
> meText,CoverSheet,JobID) values
> (
> @.ClientID,@.ApplicantID,@.PositionID,@.Firs
tName,@.LastName,@.Email,@.TicklerPhr
as
> e,@.ResumeText,@.CoverSheet,@.JobID)
> INSERT INTO JobApplicant (ClientID,ApplicantID,PositionID,JobID,R
esume)
> Values(@.ClientID,@.ApplicantID,@.PositionI
D,@.JobID,getdate() )
> select @.ApplicantID as ApplicantID,@.JobID as JobID
> GO
>
> Server: Msg 195, Level 15, State 10, Procedure spAddNewResume, Line 17
> 'Scope_Identity' is not a recognized function name.
> Server: Msg 195, Level 15, State 1, Procedure spAddNewResume, Line 29
> 'Scope_Identity' is not a recognized function name.
> ****************************************
**********************************
**
> **
> Why would that happen?
> Thanks,
> Tom
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%233HvNAvGFHA.2360@.TK2MSFTNGP12.phx.gbl...
> What does the following return on your home machine?
> SELECT @.@.VERSION
I couldn't remember how to get the version.
I did have both Sql Server 7 and 2k on my machine, but I now remember I took
the 2k off when testing the trial version and converting to the real version
before I did it at work.
I just reinstalled 2k and it works fine now.
How stupid.
Thanks,
Tom
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "tshad" <tfs@.dslextreme.com> wrote in message
> news:OawFG9uGFHA.3156@.TK2MSFTNGP10.phx.gbl...
use
ApplicantScreen(ClientID,JobID,Applicant
ID,PositionID,Version,QuestionUnique[col
or=darkred]
values(@.ClientID,@.JobID,@.ApplicantID,@.Po
sitionID,@.Version,@.QuestionUnique,@.A[col
or=darkred]
Line
Line
****************************************
************************************[col
or=darkred]
****************************************
************************************[col
or=darkred]
(ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,TicklerPhrase,Resu[col
or=darkred]
@.ClientID,@.ApplicantID,@.PositionID,@.Firs
tName,@.LastName,@.Email,@.TicklerPhras[col
or=darkred]
****************************************
************************************[col
or=darkred]
>