The OLE DB provider find way for join table in distributed query.
In some case it use cursor for join table dependent from capability of target servers.
My question is - May I tell to the OLE DB provider to prohibit use te cursor for join tables by set
some parameter (for example provider string in the linkedserver) ?
In some case OLE DB provider use cursor and it cause error. You can see it in SQL Profiler.
It is example which
- run well under SQL 7.0 (target server for distibuted query), where OLE DB provider doesn't use cursor
- doesn't run wel under SQL 2000 (target server for distibuted query), where OLE DB provider use cursor
~~~~~~~~~~~~~~~~
Declaration part
~~~~~~~~~~~~~~~~
sp_serveroption @@servername, 'data access', 'true'
- this option enables a linked server for distributed query access
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE dbo.z_j_test_x
@as_param_1 VARCHAR(20),
@ai_param_2 INTEGER
AS
SET FMTONLY OFF
SET NOCOUNT ON
DECLARE @li_i INTEGER
CREATE TABLE #lt_hniezdo (
s_pk_1 VARCHAR(20) NOT NULL,
s_pk_2 INTEGER NOT NULL
)
SET @li_i = 0
WHILE (@li_i < 2) BEGIN
INSERT INTO #lt_hniezdo (s_pk_1, s_pk_2)
SELECT @as_param_1, (@ai_param_2 + @li_i)
SET @li_i = @li_i + 1
END
SELECT * FROM #lt_hniezdo ORDER BY s_pk_1, s_pk_2
DROP TABLE #lt_hniezdo
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM
OPENQUERY(<SERVER NAME>, 'EXEC <schema name>.dbo.z_j_test_x ''test1'', 10')
,OPENQUERY(<SERVER NAME>, 'EXEC <schema name>.dbo.z_j_test_x ''test2'', 100')
In some case it use cursor for join table dependent from capability of target servers.
My question is - May I tell to the OLE DB provider to prohibit use te cursor for join tables by set
some parameter (for example provider string in the linkedserver) ?
In some case OLE DB provider use cursor and it cause error. You can see it in SQL Profiler.
It is example which
- run well under SQL 7.0 (target server for distibuted query), where OLE DB provider doesn't use cursor
- doesn't run wel under SQL 2000 (target server for distibuted query), where OLE DB provider use cursor
~~~~~~~~~~~~~~~~
Declaration part
~~~~~~~~~~~~~~~~
sp_serveroption @@servername, 'data access', 'true'
- this option enables a linked server for distributed query access
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE dbo.z_j_test_x
@as_param_1 VARCHAR(20),
@ai_param_2 INTEGER
AS
SET FMTONLY OFF
SET NOCOUNT ON
DECLARE @li_i INTEGER
CREATE TABLE #lt_hniezdo (
s_pk_1 VARCHAR(20) NOT NULL,
s_pk_2 INTEGER NOT NULL
)
SET @li_i = 0
WHILE (@li_i < 2) BEGIN
INSERT INTO #lt_hniezdo (s_pk_1, s_pk_2)
SELECT @as_param_1, (@ai_param_2 + @li_i)
SET @li_i = @li_i + 1
END
SELECT * FROM #lt_hniezdo ORDER BY s_pk_1, s_pk_2
DROP TABLE #lt_hniezdo
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM
OPENQUERY(<SERVER NAME>, 'EXEC <schema name>.dbo.z_j_test_x ''test1'', 10')
,OPENQUERY(<SERVER NAME>, 'EXEC <schema name>.dbo.z_j_test_x ''test2'', 100')