We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Microsoft OLE DB Provider for SQL Server (0x80040E37)

n4narik
n4narik asked
on
Medium Priority
995 Views
Last Modified: 2012-05-05
I have an ASP page to test the connection and pull details from a small table. I get this error when I try to print the contents of that table. When I remove that line, I can see that the connection workd fine.

<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")
conn.Open "Provider=SQLOLEDB; Data Source = mlvv1qba; Initial Catalog = Build_Summary; User Id = Builder; Password=Builder"
If conn.errors.count = 0 Then
Response.Write "Connected OK"
End If
mQuery = "SELECT * FROM Problems"
set rs=conn.execute(mQuery)
response.write(rs(0))
%>

Any suggestions?
Comment
Watch Question

Commented:
Hi n4narik,

Verify that the table Problems exsists in the Build_Summary Database.


Hope this helps,
Neal.

Author

Commented:
Yes, the table exists.

Thanks.

Commented:
n4narik,

Which line does the error occur on?

Neal.

Author

Commented:
Line 17.

Thanks.

Author

Commented:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E37)
Invalid object name 'Problems'.
/ravi/sql2000test.asp, line 17


Author

Commented:
Ok, the line 17 is now this line:  set rs=conn.execute(mQuery)

Commented:
Try changing the connection

Conn.open("Provider=sqloledb;Server=YourServerName;Database=Build_Summary;uid=youruserid;password=yourpassword;")

Make sure you put in your server name, UserId and Password and see if this makes a difference.  

Neal.

Author

Commented:
No. It says cannot connect.

Commented:
OK, lets put your connection string back the way it was and try running this SQL query in place of your current one.

mQuery = "SELECT T.TABLE_NAME AS [Table] FROM INFORMATION_SCHEMA.Tables T " &_
                 "WHERE T.TABLE_NAME NOT LIKE 'sys%' AND T.TABLE_NAME <> 'dtproperties' " &_
                 "AND T.TABLE_SCHEMA <> 'INFORMATION_SCHEMA' ORDER BY T.TABLE_NAME "

This should display a list of all the tables in your current database.  Verify that Problems is in the list.  

The error message you originally posted is that the object doesn't exist, so for some reason it is not seeing the Problems table.  This might help us find out why.

Neal.

Author

Commented:
I see the tables Neal.
The Problems table is in the list.

I've increased the points to 500.

Author

Commented:
Ok, I think I've figured out the problem. When I get into the View of the table from SQL2000 client, I see [myID].[TableName]
So, I used that name to pull data and it worked fine.
How can I stop using those names, and use the regular table name to get the data?
Commented:
I was just writing this when your post came in:
-----------------------------------------
Just for fun let's try something simple
change
  mQuery = "Select * from Problems"
to
  mQuery = "Select * from [Problems]"

Sometimes you run across the strangest reserved words
-------------------------------------------

[myID].[TableName] is standard SQL nomenclature for a table.  This references the user and tablename together.  This is sometimes required if the table name is a reserved word or has spaces.  The UserID part may be dropped which leaves you with what I have above.

Neal.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
For your time and effort!

Commented:
No, problem.  Did that solve the problem?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.