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?
 
TempDBACommented:
try following procedures to test the connection and accessiblity properties for linkserver

sp_tables_ex linkservername

sp_catalogs linkservername
0
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.