Microsoft OLE DB Provider for SQL Server (0x80040E37)

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?
n4narikAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nschaferCommented:
Hi n4narik,

Verify that the table Problems exsists in the Build_Summary Database.


Hope this helps,
Neal.
n4narikAuthor Commented:
Yes, the table exists.

Thanks.
nschaferCommented:
n4narik,

Which line does the error occur on?

Neal.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

n4narikAuthor Commented:
Line 17.

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


n4narikAuthor Commented:
Ok, the line 17 is now this line:  set rs=conn.execute(mQuery)
nschaferCommented:
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.
n4narikAuthor Commented:
No. It says cannot connect.
nschaferCommented:
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.
n4narikAuthor Commented:
I see the tables Neal.
The Problems table is in the list.

I've increased the points to 500.
n4narikAuthor 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?
nschaferCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
n4narikAuthor Commented:
For your time and effort!
nschaferCommented:
No, problem.  Did that solve the problem?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.