We help IT Professionals succeed at work.

SQLNCLI for linked server My_Server does not contain table My_Table

Eric3141
Eric3141 asked
on
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?
Comment
Watch Question

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

sp_tables_ex linkservername

sp_catalogs linkservername

Author

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.

Commented:
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>';

Author

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.

Author

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.

Author

Commented:
The userid on the 2000 server has sysadmin rights as well.

Commented:
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.

Author

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.

Author

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.
Commented:
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?

Author

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

Author

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.