rruth
asked on
Coldfusion SQL Server 2005 connectivity issue
Experts,
I'm having troubles getting SQL Server 2005 Express to recognize my CF queries. I had SQL Server and CF working properly but had hard drive issues and had to reformat. I've reinstalled everything but now I'm getting this error message on all queries:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name 'color'.
The error occurred in C:\Inetpub\wwwroot\cfdocs\
9 :
10 : <!--- <cfquery name="getCategories" datasource="#request.db#" username="#request.usernam
11 : <cfquery name="getCategories" datasource="ruthsisters" username="sa" password="xxxxxxx">
12 : SELECT *
13 : FROM color
14 : </cfquery>
"Color" is the name of a column in the database.
The install for both CF and SQL I believe is fine. I'm concerned because usually I've assigned port 1433 to the SQL db in the CF admininistrator. The only way CF will verify the db is if I use port 1259. I've went into Windows administrative tools and established an ODBC connect which is verified. I believe that I have everything as before but something is not working.
Thank you for any help you can give!!!!!
rr
I'm having troubles getting SQL Server 2005 Express to recognize my CF queries. I had SQL Server and CF working properly but had hard drive issues and had to reformat. I've reinstalled everything but now I'm getting this error message on all queries:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name 'color'.
The error occurred in C:\Inetpub\wwwroot\cfdocs\
9 :
10 : <!--- <cfquery name="getCategories" datasource="#request.db#" username="#request.usernam
11 : <cfquery name="getCategories" datasource="ruthsisters" username="sa" password="xxxxxxx">
12 : SELECT *
13 : FROM color
14 : </cfquery>
"Color" is the name of a column in the database.
The install for both CF and SQL I believe is fine. I'm concerned because usually I've assigned port 1433 to the SQL db in the CF admininistrator. The only way CF will verify the db is if I use port 1259. I've went into Windows administrative tools and established an ODBC connect which is verified. I believe that I have everything as before but something is not working.
Thank you for any help you can give!!!!!
rr
ASKER
I'm sorry color is a table in the database not a column.
rr
rr
It sounds to me like you need to change SQL Server's configuration to use port 1433 instead of 1259.
M@
M@
ASKER
ExpertAdmin,
I went into the SQL Server Configuration Manager, clicked on Protocols for SQLEXPRESS, right clicked on TCP/IP and clicked on Properties and changed the port from 1259 to 1433. I then went back into CF Admin/datasource and changed the port there to 1433. I get the same error message: Invalid object name.
I don't know what else to do. I really appreciate your help!!!
rr
I went into the SQL Server Configuration Manager, clicked on Protocols for SQLEXPRESS, right clicked on TCP/IP and clicked on Properties and changed the port from 1259 to 1433. I then went back into CF Admin/datasource and changed the port there to 1433. I get the same error message: Invalid object name.
I don't know what else to do. I really appreciate your help!!!
rr
Why don't you try to use cf administrator to set up a DNS for you. When you finshied setting DNS you can verify the connection. Make use it is working!
ASKER
boy8964,
Again, originally I had SQL 2005 and CF working. After I reformatted by hard drive and re-installed everything, all my queries in the app I was working on through an error saying that the name of my table is invalid.
I've went into the CF Admin and verified the connection to my table. I've went into SQL Server Configuration Manager and changed the port from 1259 to 1433. I've also went into Windows Admin tools and verified the ODBC connection there.
I'm really desparate. I now have reformatted by hard drive twice hoping that I had done something wrong the first time!!!!!!!!!!
I really, no I mean I REALLY appreciate any help you can give!
rr
Again, originally I had SQL 2005 and CF working. After I reformatted by hard drive and re-installed everything, all my queries in the app I was working on through an error saying that the name of my table is invalid.
I've went into the CF Admin and verified the connection to my table. I've went into SQL Server Configuration Manager and changed the port from 1259 to 1433. I've also went into Windows Admin tools and verified the ODBC connection there.
I'm really desparate. I now have reformatted by hard drive twice hoping that I had done something wrong the first time!!!!!!!!!!
I really, no I mean I REALLY appreciate any help you can give!
rr
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ExpertAdmin,
You are going the right direction with this. What I did is create a query that used the DNS for my database (ruthsisters) but actually used a table (spt_values) that is in the master database and it worked, returning every value in a column called "Name". Here is a look at the query:
<cfquery name="getMaster" datasource="ruthsisters" username="sa" password="xxxxxx">
SELECT *
FROM spt_values
</cfquery>
<cfoutput query="getMaster">
#Name#<br />
</cfoutput>
How can I get SQL to find my database?
Again, THANK YOU for you help!
rr
You are going the right direction with this. What I did is create a query that used the DNS for my database (ruthsisters) but actually used a table (spt_values) that is in the master database and it worked, returning every value in a column called "Name". Here is a look at the query:
<cfquery name="getMaster" datasource="ruthsisters" username="sa" password="xxxxxx">
SELECT *
FROM spt_values
</cfquery>
<cfoutput query="getMaster">
#Name#<br />
</cfoutput>
How can I get SQL to find my database?
Again, THANK YOU for you help!
rr
Go back to your DSN and set the Default Database property to your database. There should be a setting that will allow you to choose it from a dropdown list.
M@
M@
ASKER
ExpertAdmin,
I found that if I add the option: dbname="ruthsisters" to my query it finds the correct database. I uploaded the test query to my ISP with the "dbname" option and it functions normally there. I'm not sure why after re-installing SQL Server that I now have to include the "dbname" option. This means that I will do a search and replace on all queries to add this option so they work properly on my local development computer. I can live with this. If you have any insight on why the change I would really appreciate knowing.
<cfquery name="getAdmin" datasource="ruthsisters" username="sa" password="xxxxxxx" dbname="ruthsisters">
SELECT *
FROM admin
</cfquery>
It was your clue that SQL Server was defaulting to the master db that helped me work this out. I really appreciate your help and if you know why this happened I would love to know also!
rr
I found that if I add the option: dbname="ruthsisters" to my query it finds the correct database. I uploaded the test query to my ISP with the "dbname" option and it functions normally there. I'm not sure why after re-installing SQL Server that I now have to include the "dbname" option. This means that I will do a search and replace on all queries to add this option so they work properly on my local development computer. I can live with this. If you have any insight on why the change I would really appreciate knowing.
<cfquery name="getAdmin" datasource="ruthsisters" username="sa" password="xxxxxxx" dbname="ruthsisters">
SELECT *
FROM admin
</cfquery>
It was your clue that SQL Server was defaulting to the master db that helped me work this out. I really appreciate your help and if you know why this happened I would love to know also!
rr
Again, I don't think that SQL Server is the real problem. Were you able to set the default DB in your ODBC connection?
M@
M@
ASKER
ExpertAdmin,
No I was not able to locate the place to make that selection. I'm finding that I'm still having some problems. After adding the "dbname" attribute to the example query above, the query functioned as expected. But even with the dbname attribute added some of my queries are still throwing the same error message. This is really frustrating because these are all queries that functioned properly before the melt down of my hard drive. Do you have any suggestions where I can make this selection?
Again, thanks for your help!
rr
No I was not able to locate the place to make that selection. I'm finding that I'm still having some problems. After adding the "dbname" attribute to the example query above, the query functioned as expected. But even with the dbname attribute added some of my queries are still throwing the same error message. This is really frustrating because these are all queries that functioned properly before the melt down of my hard drive. Do you have any suggestions where I can make this selection?
Again, thanks for your help!
rr
I am assuming that your DSN is set up in Windows. If that is the case, you can go to Control Panel, ODBC Connections, then click on the Machine Data Sources tab. You should see a list of DSNs, and yours should be in that list. If you double click on the name, you should get a dialog that lets you set the username, password, and default database.
Here are the step by step instructions:
http://www.depts.ttu.edu/helpcentral/directions/ODBCSetup.php
M@
Here are the step by step instructions:
http://www.depts.ttu.edu/helpcentral/directions/ODBCSetup.php
M@
ASKER
ExpertAdmin,
I went into the Windows Admin Tools and found the default db option you mentioned. While there I noticed that I had mistakenly setup my database under the System DSN tab instead of the User DSN. I removed it and than re-added it under User. Pretty lame! I never changed my default settings and all my queries are now working. Thank you again for sending me in the right direction and going the extra mile!!!
rr
I went into the Windows Admin Tools and found the default db option you mentioned. While there I noticed that I had mistakenly setup my database under the System DSN tab instead of the User DSN. I removed it and than re-added it under User. Pretty lame! I never changed my default settings and all my queries are now working. Thank you again for sending me in the right direction and going the extra mile!!!
rr
That is great news. You are welcome.
One thing...you should be able to use either a system or a user DSN the same with no problems. The main difference is that a user DSN will only be visible when you are logged in. If you are setting up a server DSN you will need to make sure it is a System, not User DSN because otherwise it will not be available when you are not logged in.
M@
One thing...you should be able to use either a system or a user DSN the same with no problems. The main difference is that a user DSN will only be visible when you are logged in. If you are setting up a server DSN you will need to make sure it is a System, not User DSN because otherwise it will not be available when you are not logged in.
M@
ASKER
ExpertAdmin,
That makes sense. Thank you for all your help!
rr
That makes sense. Thank you for all your help!
rr
Try
SELECT color
FROM tablename