• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

Stored Procedure in SQL Server via ASP

I receive the following error message when trying to execute my code:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Syntax error or access violation

/loginaction.asp, line 125

My global.asa file:

<SCRIPT LANGUAGE=VBScript RUNAT=Server>

Sub Application_OnStart
      application("DBConn")="Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=carpentersfund;Data Source=BILLS-SERVER;PASSWORD=;"
      application("SSLPath")="c:\cf_wip\members"
End Sub

</SCRIPT>      

The problem portion of my ASP script:

<%
dim RequestUserDataSource
dim Cmd
dim RS
dim UsrParm
dim PwdParm
dim sSQL

Set RequestUserDataSource=Server.CreateObject("ADODB.connection")
RequestUserDataSource.Open(application("DBConn"))

sSQL="[NoZeroes}"

Set Cmd=Server.CreateObject("ADODB.Command")
Set Cmd.ActiveConnection = RequestUserDataSource
Cmd.CommandText = sSQL

Set UsrParm=Cmd.CreateParameter("username",adChar,adParamInput,50)
Set PwdParm=Cmd.CreateParameter("password",adChar,adParamInput,50)

UsrParm.Value = Request.Form("Username")
PwdParm.Value = Request.Form("Password")

Cmd.Parameters.Append UsrParm
Cmd.Parameters.Append PwdParm

Set RS=Cmd.Execute(,,adCmdStoredProc)

(Line 125 is the above line)

The Stored Procedure:

CREATE PROCEDURE  NoZeroes
@username char,
@password char
 AS
SELECT Username, Password
FROM Users
WHERE Username = Convert (numeric, @username) AND Password = @password
GO

I have included adovbs.inc at the top of the page.

Chips, dip, and beer, plus 500 points to the winner.

I noticed that I had a syntax error in the sSQL= "[NoZeroes}" line above.  It now reads:

sSQL="[NoZeroes]"

Now I get the following error:

Microsoft OLE DB Provider for SQL Server error '80040e09'

EXECUTE permission denied on object 'NoZeroes', database 'carpentersfund', owner 'dbo'

/loginaction.asp, line 125

AARRRGGGHHH!!!
0
thewyzzard
Asked:
thewyzzard
1 Solution
 
LlamaJoeCommented:
you are using integrated security in your connection string, so if you have security enabled (basic or windows authentication) it will pass those securities to SQL.  Otherwise it is trying to connect as the anonymous iusr_machinename account.  

Change your connection string to a sql username and password and try again.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now