error '80040e37' object not found...after changing table owner

[waiterm]

Hi,

I'm getting the following error when I try and access any table on our test server:

Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tbl_AdminUsers'.

/admn/login.asp, line 24

I can access the tables fine via Enterprise Manager and the table is present and named correctly.

The problem has only arrisen since the website went live and in order for the live version of the database to operate correctly I had to change the table owner to match the live version prior to copying the database over. Since changing the table owner for each table I am getting the error message above.

The database name and owner have remained the same and I can still create a connection without any trouble.

Any suggestions would be gratefully received.



Rob Waite

http://www.hostpipe.co.uk?src=TT

 

[onpnt]

We'll need to see what your ASP script is doing probably to help. Are you referencing the old owner anywhere?

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[waiterm]

Code is very simple,

Set Con = Server.CreateObject("ADODB.Connection")
Con.Open "DRIVER={SQL SERVER};SERVER=t4pserver;DATABASE=mycroscope;Uid=sa;Pwd=********;language=british"
SET RS = Server.CreateObject("ADODB.Recordset")
userSQL = "SELECT * FROM tbl_AdminUsers WHERE US_Username = '" & sUser & "'"
RS.open userSQL, Con
IF RS.EOF THEN
...

It's struggling on RS.Open

Database name hasn't changed, only the owners of the tables from 'mycroscope' to 'myscope'



Rob Waite

http://www.hostpipe.co.uk?src=TT

 

[onpnt]

Reference the table as the myscope.tbl_AdminUsers

See if that works

btw... bad idea (well, horrible) to use sa in your connection string

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[onpnt]

Also run exec sp_tables to make sure your owner is correct.

Did you use sp_changeobjectowner to make the change? or EM?

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[ca8msm]

btw... bad idea (well, horrible) to use sa in your connection string

Coupled with the possibility of SQL Injection in their statement it could be very dangerous.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[waiterm]

Noted regarding using 'sa', luckily it's just on our testing server so will change ASAP.

Many thanks for your help.

Rob Waite

http://www.hostpipe.co.uk?src=TT

 

[onpnt]

I noticed that. Probably the worst place to NOT prepare yourself for that. Seeing as the word Admins and user logins is obviously part of that statement ;-)

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[onpnt]

Many thanks for your help

Did the reference change work?

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[waiterm]

Yes reference changes worked. Although, rather than changing the ASP code throughout the site, I was rather hoping I could simply change the database owner to match the live version of the site, that way I don't have to trawl through the entirety of my code changing table and stored procedure references!

This will be fine for testing but, I'll see if I can find a more efficient work around and post if I do so.

Rob Waite

http://www.hostpipe.co.uk?src=TT