Can't run queries

[athanasiusrc]

I have a new development machine I have set up that has the same environment as
my current machine. The database gives an "ok" status in the CF 7 admin and I
can run queries just fine through the query analyzer. However, CF pages won't
execute any queries whether they are procedures or standard queries. The error
message is "Error Executing Database Query"

The ability to run queries and stored procs is set to yes in the datasource
settings.

[Dan Bracuk]

Sounds like a database permissions problem.

[MikerRoo]

Do you log in as SA in QA or as a windows user?

Anyway, attach the FULL error message here. This includes generated SQL, SQL
server error code and the snippet of CFM where the error was detected.

Turn on all error reporting in CF admin.
Clear the cache files in CF admin.

If the above info still does not appear, restart the server and try again.

[athanasiusrc]

I us SA for both in development.

Here is the code. I have tried switching it to cfquery but I get the same
result

<cfquery name="Request.getSystemSettings"
datasource="#request.ds#"
cachedwithin="#CreateTimeSpan(0,1,0,0)#">
EXEC pr_getSystemSettings '#cgi.http_host#'
</cfquery>

Here is the error:
Database Exception - in c:\inetpub\aam\fbx_settings.cfm : line 74
Error Executing Database Query.

I have rebooted the machine and that didn't fix it. The CFadmin tests the
datasource as "okay".

[MikerRoo]

A proper, full error message looks like the attached. Turn on all debug
settings, restart CF and post the FULL error message here.

Also in Query Analyzer press Control-O (open new connection).
When you start QA do you use "Windows authentication" or "SQL Server
authentication"?




Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near 'I
have heard that nothing gives an Author so great Pleasure'.

The error occurred in C:\data\web\test\ForumProbs\ProperErrorDemo.cfm: line 1

1 : <CFQUERY name="qFoo" datasource="BTSOperator">
2 : EXEC [BTS_DB].[dbo].[DELETE_ME] UnexpectedTxt 'I have heard that
nothing gives an Author so great Pleasure'
3 : </CFQUERY>

SQL EXEC [BTS_DB].[dbo].[DELETE_ME] UnexpectedTxt 'I have heard that
nothing gives an Author so great Pleasure'
DATASOURCE BTSOperator
VENDORERRORCODE 170
SQLSTATE HY000
Please try the following:Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near 'I
have heard that nothing gives an Author so great Pleasure'.

The error occurred in C:\fakedir\web\test\ForumProbs\ProperErrorDemo.cfm : line
1

1 : <CFQUERY name="qFoo" datasource="dsnForumFoo">
2 : EXEC [BTS_DB].[dbo].[DELETE_ME] UnexpectedTxt 'I have heard that
nothing gives an Author so great Pleasure'
3 : </CFQUERY>

SQL EXEC [BTS_DB].[dbo].[DELETE_ME] UnexpectedTxt 'I have heard that
nothing gives an Author so great Pleasure'
DATASOURCE BTSOperator
VENDORERRORCODE 170
SQLSTATE HY000
Please try the following:

[athanasiusrc]

All debug settings are on. I have rebooted. The error I provided is the full
message I get. When I use query analyzer I use SQL authentication.

This code works fine on both our production server and another development
machine.

Here is the actual sql code:
CREATE PROCEDURE dbo.pr_getSystemSettings
@DomainName varchar(100)

AS
SELECT
FaxServer,
PDFPrinting,
BarcodePrinting,
BarcodeText,
PDFSpooler,
PDFPath,
PDFOrderPath,
ReceiptPath,
Printing,
BackgroundColor,
BorderColor,
EmailAuth,
CustomerServiceEmail,
WebmasterEmail,
ReturnEmail,
FaxEmail,
CCProcessor,
ProcessorUserName,
ProcessorPassword,
ProcessorURL,
ReceiptMessage,
DomainName,
StoreName,
DifferentShipAddress,
ShipOutOfCountry,
CreditAccounts,
TaxShipping,
ShippingMethod_Fk,
WebRoot,
CFRoot,
ImgRoot,
PrintCCReceipt,
Root,
ShipApartAmount,
User_Fk,
SystemSettings_Pk
FROM
SystemSettings
WHERE
DomainName = @DomainName

GO

[JMGibson3]

Is it really possible to slam query results directly into the Request
scope(<cfquery name="Request.getSystemSettings") at query execution time? Even
if possible, it sounds scary to me.
Have you tried name="myquery" and later on set Request.xxx to myQuery.xxx?.

[MikerRoo]

Request scope is not the problem.

Ok, you say that:[num]
[li] All datasources and yourself are logging in as SA.
[li] NO queries work from coldfusion, using SA, on the bad machine.
[li] The queries do work from query analyzer, running on the bad
machine and talking to the same database.
[li] You are not getting the normal full debugging message as I outlined above.
[/num]

If all the above is true, then the first thing to check is, are you using ODBC
drivers with CF. If so, that is the problem, use CF's native JDBC driver when
you recreate your datasource.

Either way, Create a new datasource in CF and try it.

Is there more than one copy of the database? Point a good machine at the bad
DB and vice versa.

Does the bad machine have a different locale? Or Asian-language support
turned on/off different from good machine?

DB collations or ANSI settings different?

[athanasiusrc]

Great. It works now. Thanks for the help.

[athanasiusrc]

I also deleted the ODBC dsn from the windows dsn manager and after reinstalling
it was able to make a connection with the odbc socket through CF. However, the
queries would cut off all returned variables after the first letter. I have
never seen that before.

[SSandlin]

Just a thought, have you set up a DSN on your web site hosting account?

A database connection from your computer is not the same as the database
connection from your sites connection. You are simply mimicking the DSN
connection that should already exist on your site's server.

[athanasiusrc]

The dsns are set up on the server through the ODBC panel.

[Ian Skinner]

I have not read the entire thread, so this may not be the question, but
setting up DNS on the server with the ODBC panel does not make them
available to ColdFusion, they must also be set up in the ColdFusion
administrator. By the by many DSN's can be set up in the ColdFusion
administrator directory without using the ODBC panel.

athanasiusrc wrote:

> The dsns are set up on the server through the ODBC panel.

[athanasiusrc]

I have set up the dsns in CF. If I do it with JDBC I get an error. If I do it with ODBC it only returns the first character of each result.