Stored Procedure 1

[ozzroo]

Hi

I created this stored procedure in SQL Express on my local machine and I am trying to create it on my isp server.
But gives me the follow error

Line 4: Incorrect syntax near 'GO'.

My stored procedure is below

Any help please

Its 1and1 as the host by the way

CREATE PROCEDURE [PROCEDURE NAME] AS
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[DBAuthenticate]

(
@AccountNumber Int,
@Password nchar(10)

)
As

DECLARE @ID INT
DECLARE @actualPassword nchar(10)

SELECT
@ID = AccountNumber,
@actualPassword = Password
FROM dbo.Clients
WHERE AccountNumber = @AccountNumber

IF @ID IS NOT NULL
   IF @Password = @actualPassword
      RETURN @ID
ELSE
RETURN - 2
ELSE
RETURN - 1

 

[gmmastros]

try...

Create Procedure [dbo].[DBAuthenticate]

(
@AccountNumber Int,
@Password nchar(10)

)
As

DECLARE @ID INT
DECLARE @actualPassword nchar(10)

SELECT
@ID = AccountNumber,
@actualPassword = Password
FROM dbo.Clients
WHERE AccountNumber = @AccountNumber

IF @ID IS NOT NULL
   IF @Password = @actualPassword
      RETURN @ID
ELSE
RETURN - 2
ELSE
RETURN - 1

-George

"the screen with the little boxes in the window." - Moron

 

[AlexCuse]

YOu need to change ALTER to CREATE, as you are going to be making this proc for the first time on your other server.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ozzroo]

Thanks for your help

Got it created but now its telling me when i run the page

Server Error in '/' Application.
--------------------------------------------------------------------------------

Invalid object name 'Clients'. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'Clients'.

Source Error: 


Line 27:         cmdSelect.Parameters.Add("@Password", strPassword)
Line 28: 		conMyData.Open()
Line 29:         cmdSelect.ExecuteNonQuery()
Line 30:         IntResult = cmdSelect.Parameters("RETURN_VALUE").Value
Line 31:         conMyData.Close()
 

Source File: e:\kunden\homepages\35\d125189611\login.aspx    Line: 29 

Stack Trace: 


[SqlException: Invalid object name 'Clients'.]
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
   ASP.login_aspx.DBAuthenticate(String strAccountNumber, String strPassword) in e:\kunden\homepages\35\d125189611\login.aspx:29
   ASP.login_aspx.btnLogin_OnClick(Object s, EventArgs e) in e:\kunden\homepages\35\d125189611\login.aspx:7
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1292

But there is a table called 'Clients' on the database

 

[ozzroo]

got it working guys

thanks for your help

much appreciated

 

[LNBruno]

ozzroo -

How about posting the fix to bring the thread to full closure?

< M!ke >
I am not a hamster and life is not a wheel.

 

[ozzroo]

I recreated the stored procedure again with the correct table name. replace dbo.Clients with Clients

 

[SQLSister]

Uh oh. That means some one else is the owner of the Clients table on that database. Personally, I recommend that all tables be owned by dbo. It makes life better when employees leave and their logins go away. It also makes for less problems with permissions.

"NOTHING is more important in a database than integrity." ESquared

 

[LNBruno]

Thanks, ozzroo. As you can see by SQLSister's follow-up, the answer to one question can bring up a related (and much more far reaching) point that can serve to help others who find this thread later.

And, good catch, Sis!

< M!ke >
I am not a hamster and life is not a wheel.

 

[ozzroo]

Isnt dbo.Client just a name for the table or does dbo actually mean/do something?

 

[SQLSister]

dbo is the owner of the table. It is the system owner. If you created the table with a non admin login then it would list you as the owner of the table. Read up about table ownership in BOL as it has implications for maintaining your database.

"NOTHING is more important in a database than integrity." ESquared