Archive
An invalid schema or catalog was specified for the provider “SQLNCLI10” for linked server
ERROR: while retrieving data over linked server … you receive below error
Msg 7313, Level 16, State 1, Line 2
An invalid schema or catalog was specified for the provider “SQLNCLI10” for linked server “LinkedServername.DBName .dbo.tablename”
Cause ::
the database named DBName on target server .. that is the server to which your linked server is pointing contains a SPACE in its name
so in reality the database name is ” DBNAME ” that is it has leading or trailing spaces in its name …
steps to resolve the same
1>USE DBNAME;
GO
2> ALTER database DBNAME set single_user with rollback immediate
go
3> EXEC sp_renamedb ‘DBNAME ‘, ‘DBNAME123‘
go
4> EXEC sp_renamedb ‘DBNAME123’, ‘DBNAME‘
above script will resolve the linked server error :
if you try to run below script and by pass step no. 3 .. you will receive an error saying database named DBNAME already exist
EXEC sp_renamedb ‘DBNAME ‘, ‘DBNAME‘ (SQL need the new name to be different from the old name )
so follow step 1 to 4 .. to resolve error similar to below :: enjoy 🙂
An invalid schema or catalog was specified for the provider “SQLNCLI10” for linked server
Linked server works locally but not remotely
i was not able to use linked server remotely (from my desktop) or from SQL agent jobs on an instance INST1
English: M in blue square (similar to seen on ) (Photo credit: Wikipedia)
linked server point to INST2 .. i was able to view objects in linked server tab (in SSMS) and runs select from the server locally (INST1)
even below query worked fine
begin distributed tran
select * from [remoteservername].master.sys.sysprocesses
commit tran
but i was not able to view or make jobs (INST1) run fine … getting below error
Named Pipes Provider: Could not open a connection to SQL Server [5].
OLE DB provider “SQLNCLI10” for linked server “[remoteservername” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI10” for linked server “[remoteservername” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”. (Microsoft SQL Server, Error: 5)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.5500&EvtSrc=MSSQLServer&EvtID=5&LinkId=20476
SOLUTION
after checking everything .. from firewall to bouncing SQL browser service on destination server .. we figured out below ..
an alias using named pipes was created to the INST2 ..on INST1 as soon as that alias was dropped .. all jobs ran fine and linked server TAB
worked fine remotely fro
m SSMS .. it seems the alias was the default configuration used by linked server .. for remote requests VIA SSMS and
SQL agent jobs …
**** so just save the alias configurations and deleting it .. and rerun the jobs and remote queries *****
try the above solution .. if nothing works for you
SQL DBA learning curve 