SQLNCLI for linked server My_Server does not contain table My_Table

There is a db (My_Database) on a SQL Server 2000 server (My_Server) that has a table I need to access from a proc on my 2005 server.  A linked server exists on the 2005 server to the 2000 server and My_Database and My_Table are visible in the linked server.  This had all been working.  I thought we no longer needed My_Database so backed it up then dropped it.  Now I find out I was wrong so I have restored My_Database from .bak file to the 2000 server.  Re-ran job that ran the proc on 2005 server to pull data from My_Table on the 2000 server and now get error "SQLNCLI" for linked server My_Server does not contain talbe My_Table...

I've checked permissions for the user My_User on the 2000 server and it has DBO privileges on the My_Database db.

Ideas?
LVL 2
Eric3141Asked:
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.

TempDBACommented:
try following procedures to test the connection and accessiblity properties for linkserver

sp_tables_ex linkservername

sp_catalogs linkservername
0

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
Eric3141Author Commented:
sp_catalogs shows My_Database in the list.

sp_tables_ex only shows tables from the Trooper db and not from any of the other databases (including My_Database) on the 2000 server.
0
TempDBACommented:
that is because by default the link server is connecting to that database. Check the following query:-
EXEC sp_tables_ex @table_server = '<linkservername>',
@table_catalog = '<dbname>',
@table_schema = '<schemaname>',
@table_type = '<tablename>';
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Eric3141Author Commented:
When I run it with all but the last line I get results that include the table My_Table.
When I run it with all of the lines above I get nothing in the result set.
0
Eric3141Author Commented:
PS:  I ran the query you gave me and changed the last line to be:

@table_type = 'TABLE'

and then My_Table does appear in the result set.
0
Eric3141Author Commented:
The userid on the 2000 server has sysadmin rights as well.
0
TempDBACommented:
oops. yes it should be TABLE only. I did a typo.

seems its able to detect the object. So, there is not a problem.
Try selecting top 10 * from the linkserver.dbname.dbo.tablename.
If you are succeeded, try dropping and creating the stored procedure again.
0
Eric3141Author Commented:
When executing this from 2005 query window I get same error as original.

select count(*) from SQL2000_Server.My_Database.dbo.My_Table.

The same query executed against other tables in other databases on the 2000 server works.
0
Eric3141Author Commented:
Earlier I had detached this db instead of dropping it.  I've tried restoring it from backup .bak and dropping it again and reattaching it.  Same errors.  The problem started when I detached it / dropped it.  The same job ran fine the night before I did this.
0
TempDBACommented:
Do the login has permission to read the data from the database? I m not sure what is it in sql server 2000. In 2005 it is user and role should be properly assigned. Role concept should be there in 2000 too. Can you check from that perspective?
0
Eric3141Author Commented:
userid on 2000 server has db_owner rights on My_Database and sysadmin server role.

userid on 2005 server has sysadmin rights but (obviously) no direct rights to the
0
Eric3141Author Commented:
OMG!!!

I just figured out what the problem is.  The previous DBA that created that db could not spell correctly.  The db holds a list of Caselog exceptions.  She named the db CaseLogExeptions instead of CaseLogExceptions.  When I restored the db I named it CaseLogExceptions with the "c" instead of CaseLogExeptions.  Of course the query on 2005 server could not find a CaseLogExeptions db.  And when I looked in the list of databases the linked server could see and saw CaseLogExceptions then I did not know what the problem was.

Thanks a bunch for your attempts to help.  
0
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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.