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?
I've checked permissions for the user My_User on the 2000 server and it has DBO privileges on the My_Database db.
Ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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>';
EXEC sp_tables_ex @table_server = '<linkservername>',
@table_catalog = '<dbname>',
@table_schema = '<schemaname>',
@table_type = '<tablename>';
ASKER
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.
When I run it with all of the lines above I get nothing in the result set.
ASKER
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.
@table_type = 'TABLE'
and then My_Table does appear in the result set.
ASKER
The userid on the 2000 server has sysadmin rights as well.
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.tabl
If you are succeeded, try dropping and creating the stored procedure again.
seems its able to detect the object. So, there is not a problem.
Try selecting top 10 * from the linkserver.dbname.dbo.tabl
If you are succeeded, try dropping and creating the stored procedure again.
ASKER
When executing this from 2005 query window I get same error as original.
select count(*) from SQL2000_Server.My_Database
The same query executed against other tables in other databases on the 2000 server works.
select count(*) from SQL2000_Server.My_Database
The same query executed against other tables in other databases on the 2000 server works.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
userid on 2005 server has sysadmin rights but (obviously) no direct rights to the
ASKER
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.
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.
ASKER
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.