I know that this is a long shot, but I have a problem which someone
reading this group *may* just be able to shed some light on.
We have a new internal personnel planner/attendance system in place. It
uses a web interface to allow members of staff to select their site
location for any week, request leave and record absences.
The server-side scripting is composed of VB/ASP and Javascript which
ultimately queries & writes to an MS SQL Server 2000 database via an
ODBC connection between webserver (IIS6.0).
Here is the problem and I *know* that it sounds unlikely/impossible but
we carried out exhaustive tests.
-> I open a web browser on my local PC (XP Pro SP2)
-> Login to the web-based planning system
-> Update information
-> Submit Changes
At this point I get a script error:
--error message--
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near ')'.
/staff/wfp/Whereabouts/writeAbsence_v2_5.asp, line 108
------
Which is fair enough as it probably points to a problem with the SQL
statement being passed by the .asp page.
***BUT*** I _only_ get this message when I access the page via a browser
from certain PCs!
I have tried 3 browsers: MSIE6, Firefox1.5, Opera8.5.
I have obviously ensured that it isn't any kind of browser caching issue.
I have no issues using scripted-based web pages in general from the PC
in question.
I have checked the regional settings to ensure that I am using a
standard character set.
I *can* submit database changes via web browsers from most other PCs on
our network, without incurring the error message.
Even when I get the error message, the values are written to the db and
the information is updated.
The other PCs from which I am experiencing the same difficulty are
servers running Win2000 & Win2003 respectively.
Is there any kind of OS/User Profile setting which would mean that
scripts running on a remote webserver would fall over when trying to
perform an SQL write?
Could it be some kind of character encoding issue, which means that the
parameters that are received by the .asp script and then written via the
SQL statement are mangled in any way?
I can't see that there is any kind of local setting that would influence
whether remote scripts would or would not cause an error such as this.
If anyone has any ideas, has encountered anything similar or can shed
light on a system-specific setting or feature that may cause this,
please post your thoughts.
Yours Gratefully
GuyIf the server-side code path is identical, I suggest you focus on the client
browser settings. My guess is that you have client-side script that doesn't
execute under certain configurations so data isn't posted back as expected.
This causes your server-side script to generate an invalid SQL statement
like 'INSERT INTO MyTable VALUES()' and results in the error you describe.
There are other clues in your post that you aren't following Best Practices
so you might want to revisit the items below.
Rather than ODBC, consider using the OLE DB provider directly. For Windows
Authentication:
Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated
Security=SSPI"
For SQL Authentication:
Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;User
Id=MyLogin;Password=myPassword1"
Consider using parameterized SQL Statements rather than build SQL statement
strings. This is more secure, provides better performance and eliminates
the need to double-up embedded quotes and format dates. For example:
myCommand.CommandText = "INSERT INTO MyTable VALUES(?)"
Set myIntegerParameter = myCommand.CreateParameter( _
"@.MyIntegerParameter", 3, 1)
myCommand.Parameters.Append myIntegerParameter
myIntegerParameter.Value = Request("UserValue")
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Guy Debord" <laugh@.life.org> wrote in message
news:tyDIf.45673$Rw6.23215@.newsfe7-gui.ntli.net...
> Hello all,
> I know that this is a long shot, but I have a problem which someone
> reading this group *may* just be able to shed some light on.
> We have a new internal personnel planner/attendance system in place. It
> uses a web interface to allow members of staff to select their site
> location for any week, request leave and record absences.
> The server-side scripting is composed of VB/ASP and Javascript which
> ultimately queries & writes to an MS SQL Server 2000 database via an
> ODBC connection between webserver (IIS6.0).
> Here is the problem and I *know* that it sounds unlikely/impossible but
> we carried out exhaustive tests.
> -> I open a web browser on my local PC (XP Pro SP2)
> -> Login to the web-based planning system
> -> Update information
> -> Submit Changes
> At this point I get a script error:
> --error message--
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near ')'.
> /staff/wfp/Whereabouts/writeAbsence_v2_5.asp, line 108
> ------
> Which is fair enough as it probably points to a problem with the SQL
> statement being passed by the .asp page.
> ***BUT*** I _only_ get this message when I access the page via a browser
> from certain PCs!
> I have tried 3 browsers: MSIE6, Firefox1.5, Opera8.5.
> I have obviously ensured that it isn't any kind of browser caching issue.
> I have no issues using scripted-based web pages in general from the PC
> in question.
> I have checked the regional settings to ensure that I am using a
> standard character set.
> I *can* submit database changes via web browsers from most other PCs on
> our network, without incurring the error message.
> Even when I get the error message, the values are written to the db and
> the information is updated.
> The other PCs from which I am experiencing the same difficulty are
> servers running Win2000 & Win2003 respectively.
> Is there any kind of OS/User Profile setting which would mean that
> scripts running on a remote webserver would fall over when trying to
> perform an SQL write?
> Could it be some kind of character encoding issue, which means that the
> parameters that are received by the .asp script and then written via the
> SQL statement are mangled in any way?
> I can't see that there is any kind of local setting that would influence
> whether remote scripts would or would not cause an error such as this.
> If anyone has any ideas, has encountered anything similar or can shed
> light on a system-specific setting or feature that may cause this,
> please post your thoughts.
> Yours Gratefully
> Guy|||Dan,
Thanks for taking the time to offer up your suggestions.
Using parameterized SQL statements looks like it may well help to
eliminate some of the errors which are easy to make using lengthy
statement strings.
Thanks for the tip.
The OLE DB versus ODBC issue is one that I have one that I will address.
As for the the client-side script suggestion, I'll dig deep and let you
know if I can find any likely suspects.
Thanks again,
Guy|||Guy Debord (laugh@.life.org) writes:
> At this point I get a script error:
> --error message--
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near ')'.
> /staff/wfp/Whereabouts/writeAbsence_v2_5.asp, line 108
I've seen plenty of those pages on the web!
> ***BUT*** I _only_ get this message when I access the page via a browser
> from certain PCs!
Well, rather than looking at the PCs, look at the SQL instead. Set up a
trace with Profiler, and see what is being sent to SQL Server. Include
the error events and StmtStarting.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment