I am using ColdFusion to construct a SQL Server insert statement, the statement appears as expected in the debugger and if I cut and paste it into the Query Analyzer it works as expected. Yet from my code it produces the following error:
Database error number 105
[Macromedia][SQLServer JDBC Driver][SQLServer]Unclosed quotation mark before the character string 'kellyc'.
It almost seems as if somehow ColdFusion is introducing the problem. The server is MX, I similarly constructed Insert Statements that are working.
Here is the code...
From my Action Page...Its the first query that is failing.
<cftransaction action="begin">
<cftry>
<cfmodule template = "../queries/CaseMgmtQueries.cfm"
queryname = "AddCase"
CaseName = "#form.CaseName#"
Version = "#form.CaseVersion#"
Desc = "#form.CaseDescription#"
Start = "#form.CaseStartDate#"
End = "#form.CaseEndDate#">
<cfmodule template = "../queries/CaseMgmtQueries.cfm"
queryname = "qGetMaxCase"
CaseName = "#form.CaseName#">
<cfmodule template = "../queries/CaseMgmtQueries.cfm"
queryname = "AddCaseStatus"
CaseID = "#qGetMaxCase.NewCaseID#"
Status = "#form.CaseStatus#">
<cfset HaveError = False>
<cfcatch type="Database">
<cftransaction action="rollback">
<cfset HaveError = True>
<cf_dbErrorDisplay errno = #cfcatch.NativeErrorCode# detail = #cfcatch.Detail#>
</cfcatch>
</cftry>
</cftransaction>
And then from my queries page...
<cfif queryname is "AddCase">
<CFQUERY NAME="AddCase" DATASOURCE=#variables.DSN#>
Insert into CaseInfo
(CaseName, CaseDescription, CaseVersion, CaseStartDate, CaseEndDate)
Values ('#PreserveSingleQuotes(attributes.CaseName)#',
'#Attributes.Desc#',
'#Attributes.Version#',
'#Attributes.Start#',
'#Attributes.End#')
</CFQUERY>
</cfif>
This is producing a query that in the debug dump looks like...
Insert into CaseInfo
(CaseName, CaseDescription, CaseVersion, CaseStartDate, CaseEndDate)
Values ('kellyc', 'test', '1.1', '12/10/2003', '12/11/2003')
Which inserts correctly using Query Analyzer.
The main thing that is different about this insert from other inserts that are functioning in the program is the use of transactin processing.
Any help would be most appreciated!
KellyCKellyC,
I'm afraid I don't know a thing about ColdFusion, but here is what I would try: start up SQL Profiler and run a trace on your application. You can probably use the default settings for the trace unless it is a production server and in heavy use (in which case it's a pain to find the specific transaction you're looking for).
You can then identify the format of the T-SQL Statement (or SP) that is being used and test changes in your code to make corrections.
Regards,
hmscott|||i'm betting it's a coldfusion problem
see http://www.dbforums.com/showthread.php?threadid=974731
kelly, please don't cross-post
rudy|||Sorry Rudy,
I didn't realize it was bad form, since not everyone is subsribed to the same forums, and I don't really know if the problem is ColdFusion or SQL related at this point.
hmscott,
I have sent in a request to the DBA to try running the Profiler... I am now in the waiting zone. If it turns up something I will post that here.
Thanks for the suggestion.
KellyC|||Sorry for the crosspost again, but thought I would post my resolution here as well...
OK, as it often turns out, the problem is not always what it seems from the lovely and informative error messages. The unclosed quote was actually in the second query in the transaction. This came to light for me when I started playing with trying to submit a string with a single quote in it and noticed ColdFusion MX does not display the text of an unsucessful query in the debug. (Personally I consider this a deficiency of MX in case any Macromedia folks want my 2 cents).
So... long story short I realized it wasn't the insert query that was throwing the error and fixed the offending query and it now works like a charm.
Thanks again,
KellyC|||Nice of you to post the resolution. This is not meant to be an "I told you so," but it definitely points out an instance where SQL Profiler could have helped to narrow down the cause. Congrats for solving the problem.
Regards,
hmscott
Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts
Wednesday, March 21, 2012
Tuesday, February 14, 2012
Database create error
I have a strange error occuring on one of my sql servers.
When I execute a CREATE DATABASE statement without
specifying any file locations, e.g. "CREATE DATABASE
fred", I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Device
activation error. The physical file name '\fred.mdf' may
be incorrect.
[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
DATABASE failed. Some file names listed could not be
created. Check previous errors.
If I specify a location for the data file the but not the
log file it works ok. I set the default database
locations in the server properties and this then allowed
the original statement to work.
However, I would like to know where the error is coming
from. I assume that the default locations where
calculated based on either master of model, but where is
the file name error being generated?
Any pointers would be great.
Thanks,
Phil.
> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
The default database file locations are stored in the registry and are not
related to the location of the master or model databases. When you set the
default locations using EM, this updates the registry entries that SQL
Server uses for CREATE DATABASE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Phil" <philip@.intex.co.uk> wrote in message
news:4f2901c48067$e34c6d00$a601280a@.phx.gbl...
> I have a strange error occuring on one of my sql servers.
> When I execute a CREATE DATABASE statement without
> specifying any file locations, e.g. "CREATE DATABASE
> fred", I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Device
> activation error. The physical file name '\fred.mdf' may
> be incorrect.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
> DATABASE failed. Some file names listed could not be
> created. Check previous errors.
> If I specify a location for the data file the but not the
> log file it works ok. I set the default database
> locations in the server properties and this then allowed
> the original statement to work.
> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
> Any pointers would be great.
> Thanks,
> Phil.
When I execute a CREATE DATABASE statement without
specifying any file locations, e.g. "CREATE DATABASE
fred", I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Device
activation error. The physical file name '\fred.mdf' may
be incorrect.
[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
DATABASE failed. Some file names listed could not be
created. Check previous errors.
If I specify a location for the data file the but not the
log file it works ok. I set the default database
locations in the server properties and this then allowed
the original statement to work.
However, I would like to know where the error is coming
from. I assume that the default locations where
calculated based on either master of model, but where is
the file name error being generated?
Any pointers would be great.
Thanks,
Phil.
> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
The default database file locations are stored in the registry and are not
related to the location of the master or model databases. When you set the
default locations using EM, this updates the registry entries that SQL
Server uses for CREATE DATABASE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Phil" <philip@.intex.co.uk> wrote in message
news:4f2901c48067$e34c6d00$a601280a@.phx.gbl...
> I have a strange error occuring on one of my sql servers.
> When I execute a CREATE DATABASE statement without
> specifying any file locations, e.g. "CREATE DATABASE
> fred", I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Device
> activation error. The physical file name '\fred.mdf' may
> be incorrect.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
> DATABASE failed. Some file names listed could not be
> created. Check previous errors.
> If I specify a location for the data file the but not the
> log file it works ok. I set the default database
> locations in the server properties and this then allowed
> the original statement to work.
> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
> Any pointers would be great.
> Thanks,
> Phil.
Database create error
I have a strange error occuring on one of my sql servers.
When I execute a CREATE DATABASE statement without
specifying any file locations, e.g. "CREATE DATABASE
fred", I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Device
activation error. The physical file name '\fred.mdf' may
be incorrect.
[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
DATABASE failed. Some file names listed could not be
created. Check previous errors.
If I specify a location for the data file the but not the
log file it works ok. I set the default database
locations in the server properties and this then allowed
the original statement to work.
However, I would like to know where the error is coming
from. I assume that the default locations where
calculated based on either master of model, but where is
the file name error being generated?
Any pointers would be great.
Thanks,
Phil.> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
The default database file locations are stored in the registry and are not
related to the location of the master or model databases. When you set the
default locations using EM, this updates the registry entries that SQL
Server uses for CREATE DATABASE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Phil" <philip@.intex.co.uk> wrote in message
news:4f2901c48067$e34c6d00$a601280a@.phx.gbl...
> I have a strange error occuring on one of my sql servers.
> When I execute a CREATE DATABASE statement without
> specifying any file locations, e.g. "CREATE DATABASE
> fred", I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Device
> activation error. The physical file name '\fred.mdf' may
> be incorrect.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
> DATABASE failed. Some file names listed could not be
> created. Check previous errors.
> If I specify a location for the data file the but not the
> log file it works ok. I set the default database
> locations in the server properties and this then allowed
> the original statement to work.
> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
> Any pointers would be great.
> Thanks,
> Phil.
When I execute a CREATE DATABASE statement without
specifying any file locations, e.g. "CREATE DATABASE
fred", I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Device
activation error. The physical file name '\fred.mdf' may
be incorrect.
[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
DATABASE failed. Some file names listed could not be
created. Check previous errors.
If I specify a location for the data file the but not the
log file it works ok. I set the default database
locations in the server properties and this then allowed
the original statement to work.
However, I would like to know where the error is coming
from. I assume that the default locations where
calculated based on either master of model, but where is
the file name error being generated?
Any pointers would be great.
Thanks,
Phil.> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
The default database file locations are stored in the registry and are not
related to the location of the master or model databases. When you set the
default locations using EM, this updates the registry entries that SQL
Server uses for CREATE DATABASE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Phil" <philip@.intex.co.uk> wrote in message
news:4f2901c48067$e34c6d00$a601280a@.phx.gbl...
> I have a strange error occuring on one of my sql servers.
> When I execute a CREATE DATABASE statement without
> specifying any file locations, e.g. "CREATE DATABASE
> fred", I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Device
> activation error. The physical file name '\fred.mdf' may
> be incorrect.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
> DATABASE failed. Some file names listed could not be
> created. Check previous errors.
> If I specify a location for the data file the but not the
> log file it works ok. I set the default database
> locations in the server properties and this then allowed
> the original statement to work.
> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
> Any pointers would be great.
> Thanks,
> Phil.
Database create error
I have a strange error occuring on one of my sql servers.
When I execute a CREATE DATABASE statement without
specifying any file locations, e.g. "CREATE DATABASE
fred", I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Device
activation error. The physical file name '\fred.mdf' may
be incorrect.
[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
DATABASE failed. Some file names listed could not be
created. Check previous errors.
If I specify a location for the data file the but not the
log file it works ok. I set the default database
locations in the server properties and this then allowed
the original statement to work.
However, I would like to know where the error is coming
from. I assume that the default locations where
calculated based on either master of model, but where is
the file name error being generated?
Any pointers would be great.
Thanks,
Phil.Instead of putting '\fred.mdf' you will need to put (as an
example) C:\fred.mdf.
If you like to know more I would sugest you script a
database by going into EA and
1. Right Click on the DB -> All Tasks -> Generate SQL
Script.
2. Go the options tab and select script database
3. Press ok and choose a location to save the file.
After you have done this load the file in QA and have a
look at it - note - Do not run it.
Peter
>--Original Message--
>I have a strange error occuring on one of my sql servers.
>When I execute a CREATE DATABASE statement without
>specifying any file locations, e.g. "CREATE DATABASE
>fred", I get the following error:
>[Microsoft][ODBC SQL Server Driver][SQL Server]Device
>activation error. The physical file name '\fred.mdf' may
>be incorrect.
>[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
>DATABASE failed. Some file names listed could not be
>created. Check previous errors.
>If I specify a location for the data file the but not the
>log file it works ok. I set the default database
>locations in the server properties and this then allowed
>the original statement to work.
>However, I would like to know where the error is coming
>from. I assume that the default locations where
>calculated based on either master of model, but where is
>the file name error being generated?
>Any pointers would be great.
>Thanks,
>Phil.
>.
>|||> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
The default database file locations are stored in the registry and are not
related to the location of the master or model databases. When you set the
default locations using EM, this updates the registry entries that SQL
Server uses for CREATE DATABASE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Phil" <philip@.intex.co.uk> wrote in message
news:4f2901c48067$e34c6d00$a601280a@.phx.gbl...
> I have a strange error occuring on one of my sql servers.
> When I execute a CREATE DATABASE statement without
> specifying any file locations, e.g. "CREATE DATABASE
> fred", I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Device
> activation error. The physical file name '\fred.mdf' may
> be incorrect.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
> DATABASE failed. Some file names listed could not be
> created. Check previous errors.
> If I specify a location for the data file the but not the
> log file it works ok. I set the default database
> locations in the server properties and this then allowed
> the original statement to work.
> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
> Any pointers would be great.
> Thanks,
> Phil.
When I execute a CREATE DATABASE statement without
specifying any file locations, e.g. "CREATE DATABASE
fred", I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Device
activation error. The physical file name '\fred.mdf' may
be incorrect.
[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
DATABASE failed. Some file names listed could not be
created. Check previous errors.
If I specify a location for the data file the but not the
log file it works ok. I set the default database
locations in the server properties and this then allowed
the original statement to work.
However, I would like to know where the error is coming
from. I assume that the default locations where
calculated based on either master of model, but where is
the file name error being generated?
Any pointers would be great.
Thanks,
Phil.Instead of putting '\fred.mdf' you will need to put (as an
example) C:\fred.mdf.
If you like to know more I would sugest you script a
database by going into EA and
1. Right Click on the DB -> All Tasks -> Generate SQL
Script.
2. Go the options tab and select script database
3. Press ok and choose a location to save the file.
After you have done this load the file in QA and have a
look at it - note - Do not run it.
Peter
>--Original Message--
>I have a strange error occuring on one of my sql servers.
>When I execute a CREATE DATABASE statement without
>specifying any file locations, e.g. "CREATE DATABASE
>fred", I get the following error:
>[Microsoft][ODBC SQL Server Driver][SQL Server]Device
>activation error. The physical file name '\fred.mdf' may
>be incorrect.
>[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
>DATABASE failed. Some file names listed could not be
>created. Check previous errors.
>If I specify a location for the data file the but not the
>log file it works ok. I set the default database
>locations in the server properties and this then allowed
>the original statement to work.
>However, I would like to know where the error is coming
>from. I assume that the default locations where
>calculated based on either master of model, but where is
>the file name error being generated?
>Any pointers would be great.
>Thanks,
>Phil.
>.
>|||> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
The default database file locations are stored in the registry and are not
related to the location of the master or model databases. When you set the
default locations using EM, this updates the registry entries that SQL
Server uses for CREATE DATABASE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Phil" <philip@.intex.co.uk> wrote in message
news:4f2901c48067$e34c6d00$a601280a@.phx.gbl...
> I have a strange error occuring on one of my sql servers.
> When I execute a CREATE DATABASE statement without
> specifying any file locations, e.g. "CREATE DATABASE
> fred", I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Device
> activation error. The physical file name '\fred.mdf' may
> be incorrect.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
> DATABASE failed. Some file names listed could not be
> created. Check previous errors.
> If I specify a location for the data file the but not the
> log file it works ok. I set the default database
> locations in the server properties and this then allowed
> the original statement to work.
> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
> Any pointers would be great.
> Thanks,
> Phil.
Subscribe to:
Posts (Atom)