"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,ApplicantID,PositionID,Version,QuestionUnique
> ,Answer,AnswerTime)
>
> values(@ClientID,@JobID,@ApplicantID,@PositionID,@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,FirstName,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,FirstName,LastName,Email,DatePosted)
> values
> (@ClientID,@ApplicantID,@PositionID,@FirstName,
> @LastName,@Email,getdate() )
>
> Select @JobID = Scope_Identity()
> end
>
> INSERT INTO ApplicantResume
> (ClientID,ApplicantID,PositionID,FirstName,LastName,Email,TicklerPhrase,Resu
> meText,CoverSheet,JobID) values
> (
> @ClientID,@ApplicantID,@PositionID,@FirstName,@LastName,@Email,@TicklerPhras
> e,@ResumeText,@CoverSheet,@JobID)
>
> INSERT INTO JobApplicant (ClientID,ApplicantID,PositionID,JobID,Resume)
> Values(@ClientID,@ApplicantID,@PositionID,@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

>
> --
> 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,ApplicantID,PositionID,Version,QuestionUnique
> > ,Answer,AnswerTime)
> >
> >
values(@ClientID,@JobID,@ApplicantID,@PositionID,@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,FirstName,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,FirstName,LastName,Email,DatePosted)
> > values
> > (@ClientID,@ApplicantID,@PositionID,@FirstName,
> > @LastName,@Email,getdate() )
> >
> > Select @JobID = Scope_Identity()
> > end
> >
> > INSERT INTO ApplicantResume
> >
(ClientID,ApplicantID,PositionID,FirstName,LastName,Email,TicklerPhrase,Resu
> > meText,CoverSheet,JobID) values
> > (
> >
@ClientID,@ApplicantID,@PositionID,@FirstName,@LastName,@Email,@TicklerPhras
> > e,@ResumeText,@CoverSheet,@JobID)
> >
> > INSERT INTO JobApplicant (ClientID,ApplicantID,PositionID,JobID,Resume)
> > Values(@ClientID,@ApplicantID,@PositionID,@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
> >
> >
>
>