Tag: Linked server

Querying Linked Server with OPENQUERY

If you try to invoke a user-defined function (UDF) through a linked server in SQL Server by using a “four-part naming” convention (SQLserver.SQLdatabase.dbo.SQLfunction), you may receive error message.

 

Linked Server Error

The reason is User-defined function calls inside a four-part linked server query are not supported in SQL Server. That’s why error message indicates that the syntax of a Transact-SQL statement is incorrect.  

To work around this problem, use the OPENQUERY function instead of the four-part naming convention.

What is OPENQUERY?

OPENQUERY executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Principles

OPENQUERY ( linked_server ,'query' )

Arguments

linked_server: the name of the linked server.

 query ‘: string executed in the linked server. The maximum length of the string is 8 KB.

Note

OPENQUERY does not accept variables for its arguments.

OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name

Permissions

Any user can execute OPENQUERY. The permissions that are used to connect to the remote server are obtained from the settings defined for the linked server.

Usage

    • pass-through UPDATE query against the linked server
UPDATE OPENQUERY (Link_Server, 'SELECT address FROM person.addresses WHERE id = 101') SET city = 'Melbourne';
    • pass-through INSERT query against the linked server 
INSERT OPENQUERY (Link_Server, 'SELECT address FROM person.addresses ') VALUES ('NewAddress');
    • pass-through DELETE query 
DELETE OPENQUERY (Link_Server, 'SELECT address FROM person.addresses WHERE address = ''NewAddress''');
    • SELECT pass-through query
SELECT * FROM OPENQUERY (Link_Server, 'SELECT SQLdatabase.dbo.SQLfunction(functionParam1, functionParam2)');



Linked Server Error “Cannot obtain the schema rowset”

I come across the Error Message while trying to run a linked server query for SQL Server 2000 on SQL Server 2008  & SQL server 2008 R2.

The error stated “Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10″ for linked server ‘LinkedServerName’”

After little Google search I came across two possible solution to fix this error message. Apparently that is well know issue when you try to run distributed queries from a 64-bit SQL Server client to a linked 32-bit SQL Server.

 

Main Cause of this issue:

  • Problem may occur because the system stored procedures were not upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4.
  • May not have manually upgraded the system stored procedures after installing SQL Server 2000 SP3 or SQL Server 2000 SP4.
  • Whenever MDAC is upgraded to a newer version during SQL Server SP3 upgrade, one needs to run INSTCAT.SQL on all SQL Servers to which it connects to, including the Server that is being upgraded. I don’t think I ever done this.

Possible resolution Solution 1:

  1. Log on to the computer (SQL server 2000 server) by using a Windows account, which is a member of the SQL Server sysadmin fixed server role.
  2. Back up the master database
  3. Click Run, type cmd.exe, and then click OK.
  4. At the command prompt, type one of the following commands, and then press ENTER
 osql -E -S <LinkedServerName> -i <Location>\instcat.sql (For windows account with default SQL server) OR osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName > -i <Location>\instcat.sql (For SQL account with default SQL server) OR osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql (For windows account with Named Instance of SQL server) OR osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName >\<InstanceName> -i <Location>\instcat.sql(For SQL account with Named Instance of SQL server) 
Linked server error
  • Test to see that, you can run all distributed queries

Reference Links

http://support.microsoft.com/default.aspx?scid=kb;en-us;906954#appliesto

http://technet.microsoft.com/en-us/library/aa215459%28SQL.80%29.aspx

 

Possible resolution Solution 2:

  1. Create the following stored procedure on your linked SQL Server 2000 instance
USE master

CREATE PROCEDURE sp_tables_info_rowset_64
@table_name SYSNAME,
@table_schema SYSNAME = NULL,
@table_type nvarchar(255) = NULL
AS
DECLARE @Result INT SET @Result = 0
EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
GO

2. GRANT EXECUTE the procedure to the Public in the MASTER database

USE master
GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

Reference Links

http://blogs.xtivia.com/home/-/blogs/21924

I actually tried both suggested resolutions, however only Solution 2 successfully worked for me

fixing link server problem for SQL Server 2000