ODBC Socket DSN not working in CF7

[bamboo]

I'm trying to connect to the default Northwind database in SQL2000. The DSN connection in CF Admin connects fine. However, when I try running my page (locally) I get the following error:

Error Executing Database Query
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Region'.

I setup the DSN on my work laptop just fine and the page comes up. I am running the same version of CF 7 (7,0,2,142559) as well as SQL on both machines.

Is it something in SQL that is causing the problem?

 

[ecobb]

Invalid object name 'Region'.

You're trying to call something that's not in the database. Check your spelling, and make sure that the object is spelled correctly in the database as well.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[bamboo]

Thanks for the reply ECAR.

Yes, everything is spelled correctly. I'm pulling from the Region table in the Northwind (default) database. I see the table in my (local) database and everything.

<cfquery datasource="Northwind" name="qryTest">
SELECT RegionDescription
FROM Region
</cfquery>

<cfoutput query="qryTest">
#RegionDescription#<br />
</cfoutput>

 

[ecobb]

Ok, next step.

Try adding an owner name prefix, such as "dbo".

<cfquery datasource="Northwind" name="qryTest">
SELECT RegionDescription
FROM [red]dbo.[/red]Region
</cfquery>

I wouldn't think this would be the case, as the owner is already specified in the CF datasource connection in the CF Admin, but it doesn't hurt to try.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[bamboo]

Ya, I'm still getting the same error. I don't get it? I've been searching online for any type of troubleshooting. I'll double check the Adobe site again to make sure I've got all the correct patches/updates, but I'm sure I do.

My work laptop that is working fine is running 7,0,2,142317 and my home machine is running 7,0,2,142559.

I just unchecked 'trusted connection' and put back in system as the username. It verified ok, but still the same problem.

Would you recommend uninstalling/re-installing CF 7?

 

[philhege]

Does the CF service account have rights to the NW database?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[bamboo]

Where would I check for that exactly?

 

[philhege]

The CF account should have a login to the SQL Server. Open Enterprise Manager, expand the instance, and expand Security -> Logins. The account should be listed there. Right-click and select Properties, then choose the "Database Access" tab. There should be a checked box next to the Northwind DB for this user.

If you don't have EM, you can issue sp_helpuser in the NW database to see if the account is there, and sp_adduser if it isn't.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[bamboo]

Thanks for the help Phil. In the Logins I see BUILTIN/Administrators and sa. Would I just right click Logins and add 'new login'?

 

[philhege]

There are a number of ways to connect. You'd be best off reading SQL Books Online for connection and security strategies; I can't recommend what's best for your situation. The key, as I said before, is that the account running the CF service must have access to the SQL database.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[ecobb]

You have to specify the user when setting up the CF datasource for SQL Server, you don't have the option of using the CF service account. I don't see how you could successfully create a datasource and it NOT have permissions on the database. If your user doesn't have permissions, then it's going to throw an error in the CF Admin when trying to set it up.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher