Showing posts with label home. Show all posts
Showing posts with label home. Show all posts

Wednesday, March 28, 2012

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]
>

Monday, February 20, 2012

Problems using SQL 2005

I'm going to move this onto a server with SQL 2005 installed. I also happen to have SQL 2005 installed on my home machine, so it seems silly to me to install SQL Express.

I'm following the

Video Series: Visual Web Developer for Beginners video to use login controls. I click on Website-> ASP.NET Configuration, then on the Security tab. I get the following message: "There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the buttom below to be redirected to a page where you can choose a new data store."

"The following message may help in diagnosing the problem: Unable to connect to SQL Server database."

I clicked on the button to choose data store, selected "Select a single provider for all site management data", clicked the radiobutton for AspNetSqlProvider, then clicked test.

I then got the following message: "Could not establish a connection to the database. If you have not yet created the SQL Server database, exit the web site administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider."

I did that. I did it with windows authentication, and with SQL server authentication (as the administrator). I did it with the default database, and with one I created. At some point, a new database was created, aspnetdb, but I can't get the setting right, I can't connect to it.

When I run aspnet_regsql, I get the following message: "You should now configure the provider for membership, profiles, role, personalization and SQL Web event provider to reflect the changes you made in this wizard" and so on...

So what could I be doing wrong? How do I configure this for SQL 2005? Also, the database that's created. Can this be used by all the websites I develop? Or do I need a separate database per website? What are my other options besides SQL or SQL Express? Can I use Access? Anything else?

I would appreciate any help you can give me!

More than likely your server won't accept remote connections. Start here:

http://www.teratrax.com/articles/connecting_sql_server_express.html

Buck Woody

|||http://forums.microsoft.com/msdn/ShowPost.aspx?postid=1117787&siteid=1

Problems using SQL 2005

I'm going to move this onto a server with SQL 2005 installed. I also happen to have SQL 2005 installed on my home machine, so it seems silly to me to install SQL Express.

I'm following the

Video Series: Visual Web Developer for Beginners video to use login controls. I click on Website-> ASP.NET Configuration, then on the Security tab. I get the following message: "There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the buttom below to be redirected to a page where you can choose a new data store."

"The following message may help in diagnosing the problem: Unable to connect to SQL Server database."

I clicked on the button to choose data store, selected "Select a single provider for all site management data", clicked the radiobutton for AspNetSqlProvider, then clicked test.

I then got the following message: "Could not establish a connection to the database. If you have not yet created the SQL Server database, exit the web site administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider."

I did that. I did it with windows authentication, and with SQL server authentication (as the administrator). I did it with the default database, and with one I created. At some point, a new database was created, aspnetdb, but I can't get the setting right, I can't connect to it.

When I run aspnet_regsql, I get the following message: "You should now configure the provider for membership, profiles, role, personalization and SQL Web event provider to reflect the changes you made in this wizard" and so on...

So what could I be doing wrong? How do I configure this for SQL 2005? Also, the database that's created. Can this be used by all the websites I develop? Or do I need a separate database per website? What are my other options besides SQL or SQL Express? Can I use Access? Anything else?

I would appreciate any help you can give me!

More than likely your server won't accept remote connections. Start here:

http://www.teratrax.com/articles/connecting_sql_server_express.html

Buck Woody

|||http://forums.microsoft.com/msdn/ShowPost.aspx?postid=1117787&siteid=1

Problems using SQL 2005

I'm going to move this onto a server with SQL 2005 installed. I also happen to have SQL 2005 installed on my home machine, so it seems silly to me to install SQL Express.

I'm following the

Video Series: Visual Web Developer for Beginners video to use login controls. I click on Website-> ASP.NET Configuration, then on the Security tab. I get the following message: "There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the buttom below to be redirected to a page where you can choose a new data store."

"The following message may help in diagnosing the problem: Unable to connect to SQL Server database."

I clicked on the button to choose data store, selected "Select a single provider for all site management data", clicked the radiobutton for AspNetSqlProvider, then clicked test.

I then got the following message: "Could not establish a connection to the database. If you have not yet created the SQL Server database, exit the web site administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider."

I did that. I did it with windows authentication, and with SQL server authentication (as the administrator). I did it with the default database, and with one I created. At some point, a new database was created, aspnetdb, but I can't get the setting right, I can't connect to it.

When I run aspnet_regsql, I get the following message: "You should now configure the provider for membership, profiles, role, personalization and SQL Web event provider to reflect the changes you made in this wizard" and so on...

So what could I be doing wrong? How do I configure this for SQL 2005? Also, the database that's created. Can this be used by all the websites I develop? Or do I need a separate database per website? What are my other options besides SQL or SQL Express? Can I use Access? Anything else?

I would appreciate any help you can give me!

More than likely your server won't accept remote connections. Start here:

http://www.teratrax.com/articles/connecting_sql_server_express.html

Buck Woody

|||http://forums.microsoft.com/msdn/ShowPost.aspx?postid=1117787&siteid=1