Spaceclown
|
| Posted: 03/22/2002, 8:03 AM |
|
My dev box was Access 2000 and IIS 5. My production enviornment is SQL7 and IIS4. I am doing something wrong with the connection and can't figure it out. I Transfered all files from my system to the webserver, I imported the Access database from the SQL server, setup the system DSN and tested the connection succesfully. Oh ya I changed the connection in CC in the properties/database tab. I selected provider is SQL and ODBC use NT security or the SQL sa account and they always connect but when I view the published site I get errors like the following.
This is the index.asp
Microsoft OLE DB Provider for SQL Server error '80040e37'
Invalid object name 'sys_articles'.
/bss/032102/Common.asp, line 34
Same thing on any other page execpt it shows a different table name(the one needed for the page)
Any ideas appreciated.
Thanks
|
|
|
|
Nicole
|
| Posted: 03/23/2002, 1:27 AM |
|
Hello,
if 'sys_articles' is name of table owner try to use custom sql queries and prefix table name with table owner.
If it is table name please check dsn settings to be set up correctly.
|
|
|
|
spaceclown
|
| Posted: 03/26/2002, 12:48 PM |
|
sys_articles is the table in SQL i am attempting to read. On any page I get the same type of error, the error is always the table i am trying to reference. I have checked the DSN over and over and it looks right and tests successfully.
I am using a SystemDSN using SQL server authenication and am Suing the SA account, I have the default database selected correct
This is the settings.
Microsoft SQL Server ODBC Driver Version 03.80.0194
Data Source Name: frfntrsm003
Data Source Description: frfntrsm003
Server: frfntrsm003
Database: BSSHome
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: No
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No
This is the test result.
Microsoft SQL Server ODBC Driver Version 03.80.0194
Running connectivity tests...
Attempting connection
Connection established
Verifying option settings
Disconnecting from server
TESTS COMPLETED SUCCESSFULLY!
This is line 34 from the error.
Line 34 marked with --> below
'-------------------------------
' Create forward only recordset using current database and passed SQL statement
'-------------------------------
sub openrs(rs, sql)
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseServer
-->rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
end sub
If I comment line 34 out I get a new error.
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/testsite/index.asp, line 172
Index is the page I am trying to view and line 172 is as follows:
'-------------------------------
' Process empty recordset
'-------------------------------
--> if rs.eof then
set rs = nothing
SetVar "DListArticle", ""
Parse "ArticleNoRecords", False
Parse "FormArticle", False
exit sub
end if
'-------------------------------
Only thing outside of CC that I have done is edit the outputted html code (asp
w/templates).
|
|
|
|
Nicole
|
| Posted: 03/27/2002, 1:08 AM |
|
What db provider have you selected within CC?
Try to create custom connection string. In Data Link properties window on Providers tab select 'Microsoft OLE DB Provider for SQL server', on Connection tab enter server name and select "Use NT Integrated security" according to your SQL server Login settings. Test connection. Is it successful?
|
|
|
|
Spaceclown
|
| Posted: 03/27/2002, 9:45 AM |
|
The test comes back successful but I receive a new error.
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user ''.
/testsite/Common.asp, line 25
|
|
|
|
Nicole
|
| Posted: 03/28/2002, 12:33 AM |
|
I'm far from being guru in it, but I've established connection to SQL server successfully using NT authentication or login/password to access db. It depends on db Login settings. Check (or ask administrator to do it) your own MSSQL db Login settings. What Authentication type is selected for it:
Windows NT authentication
or
SQL Server authentication
?
|
|
|
|
Spaceclown
|
| Posted: 03/29/2002, 6:41 AM |
|
I found the problem. In every page I have to edit the SQL statement to fully qualified names.
This is the before statement
sSQL = "select * from passemails where " & sWhere
openrs rs, sSQL
bIsUpdateMode = (bPK and not(sAction = "insert" and sForm = "Forgot") and not rs.eof)
This is what works
sSQL = "select * from database.tableowner.table where " & sWhere
openrs rs, sSQL
bIsUpdateMode = (bPK and not(sAction = "insert" and sForm = "Forgot") and not rs.eof)
Any ideas how to fix this without changing the qualified in all pages properties?
I am using 1 of many databases on this server and the DSN default database is set my database and in my connection string hase the default database set to mine.
Thnks
|
|
|
|
spaceclown
|
| Posted: 03/29/2002, 8:35 AM |
|
Also, where in CC can I change this, I can't find it using custom show, on the page or form. I can do it if I open the page in an html editor but like to keep it within CC.
|
|
|
|
Nicole
|
| Posted: 04/01/2002, 12:42 AM |
|
Hi,
I'm very glad to hear you have found solution!
To replace table name with full name use the code like below in Open event of each form that is based on table:
sSQL = replace(sSQL "table_name", "db_name.table_owner.table_name")
|
|
|
|
Brandon
|
| Posted: 03/20/2003, 9:25 AM |
|
I ran into this error also. It turned out that I had specified the wrong database in my Connection String Initial Catalog variable.
|
|
|
|
runprn
|
| Posted: 05/20/2003, 10:44 AM |
|
Thanks for the helpful thread. The fully qualified table name solved my issue, as well. Now, I have about a hundred queries to modify;). In the past, when just upsizing from Access to MSDE, I didn't have to modify my queries. Must be something about SQL Server 7?
Thanks, again.
|
|
|
|
|
|
