I need return a table from a stored procedures in MS SQL.
It must run in MS SQL 7.0 and MS SQL 2000 too.
Therefore I use for it an openquery function, where is executing a stored procedure, which return a table.
Openquery function calls stored procedures in the same SQL server,
in which the select statement with opequerys clause is runing.
Linked server is configured to use SQLOLEDB provider as default.
In the select statement I need call the openquery more times.
In MS SQL 7.0 it run ok.
Under MS SQL 2000 it doesn't run, because the server-side cursor is used, i think.
Below is example of my problem. The stored procedure is only for describe of a problem.
Help me, how can i turn off usage of a server-side cursor or advise me another way for doing it.
~~~~~~~~~~~~~~~~
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
~~~~~~~~~~~~~~~~~~~~~~
Behavior on MS SQL 7.0 (NEPTUNE - name of server)
~~~~~~~~~~~~~~~~~~~~~~
SQL Query Analyzer - Input
~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM
OPENQUERY(NEPTUNE, 'EXEC pw.dbo.z_j_test_x ''test1'', ''10''')
,OPENQUERY(NEPTUNE, 'EXEC pw.dbo.z_j_test_x ''test2'', ''100''')
SQL Query Analyzer - Output
~~~~~~~~~~~~~~~~~~~~~~
s_pk_1 s_pk_2 s_pk_1 s_pk_2
-------------------- ----------- -------------------- -----------
test1 10 test2 100
test1 11 test2 100
test1 10 test2 101
test1 11 test2 101
(4 row(s) affected)
SQL Profiler - Output
~~~~~~~~~~~~~~~~~~~~~~
EXEC pw.dbo.z_j_test_x 'test2', '100'
EXEC pw.dbo.z_j_test_x 'test1', '10'
sp_reset_connection
sp_reset_connection
SELECT * FROM
OPENQUERY(NEPTUNE, 'EXEC pw.dbo.z_j_test_x ''test1'', ''10''')
,OPENQUERY(NEPTUNE, 'EXEC pw.dbo.z_j_test_x ''test2'', ''100''')
~~~~~~~~~~~~~~~~~~~~~~
Behavior on MS SQL 2000 (JH - name of server)
~~~~~~~~~~~~~~~~~~~~~~
SQL Query Analyzer - Input
~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM
OPENQUERY(JH, 'EXEC pw.dbo.z_j_test_x ''test1'', ''10''')
,OPENQUERY(JH, 'EXEC pw.dbo.z_j_test_x ''test2'', ''100''')
SQL Query Analyzer - Output
~~~~~~~~~~~~~~~~~~~~~~
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'SQLOLEDB'. The provider could not support a required property. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
SQL Profiler - Trace Preferences
~~~~~~~~~~~~~~~~~~~~~~
Default setting +
Add Events / Error and warnings / Exception
Add Events / Error and warnings / OLEDB Errors
SQL Profiler - Output
~~~~~~~~~~~~~~~~~~~~~~
SET NO_BROWSETABLE ON
declare @P1 int
set @P1=3
exec sp_prepare @P1 output, NULL, N'EXEC pw.dbo.z_j_test_x ''test2'', ''100''', 1
select @P1
SET FMTONLY ON exec sp_execute 3 SET FMTONLY OFF
exec sp_unprepare 3
SET NO_BROWSETABLE OFF
exec sp_reset_connection
SET NO_BROWSETABLE ON
declare @P1 int
set @P1=4
exec sp_prepare @P1 output, NULL, N'EXEC pw.dbo.z_j_test_x ''test2'', ''100''', 1
select @P1
SET FMTONLY ON exec sp_execute 4 SET FMTONLY OFF
exec sp_unprepare 4
SET NO_BROWSETABLE OFF
exec sp_reset_connection
set implicit_transactions on
Error: 16937, Severity: 16, State: 1
Error: 16945, Severity: 16, State: 1
declare @P1 int
set @P1=0
declare @P2 int
set @P2=557064
declare @P3 int
set @P3=98305
declare @P4 int
set @P4=0
exec sp_cursoropen @P1 output, N'EXEC pw.dbo.z_j_test_x ''test2'', ''100''', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e21: EXEC pw.dbo.z_j_test_x 'test2', '100'[PROPID=DBPROP_SERVERCURSOR VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]
Error: 7320, Severity: 16, State: 2
IF @@TRANCOUNT > 0 ROLLBACK TRAN
set implicit_transactions off
exec sp_reset_connection
SELECT * FROM
OPENQUERY(JHURNANSKY, 'EXEC pw.dbo.z_j_test_x ''test1'', ''10''')
,OPENQUERY(JHURNANSKY, 'EXEC pw.dbo.z_j_test_x ''test2'', ''100''')
It must run in MS SQL 7.0 and MS SQL 2000 too.
Therefore I use for it an openquery function, where is executing a stored procedure, which return a table.
Openquery function calls stored procedures in the same SQL server,
in which the select statement with opequerys clause is runing.
Linked server is configured to use SQLOLEDB provider as default.
In the select statement I need call the openquery more times.
In MS SQL 7.0 it run ok.
Under MS SQL 2000 it doesn't run, because the server-side cursor is used, i think.
Below is example of my problem. The stored procedure is only for describe of a problem.
Help me, how can i turn off usage of a server-side cursor or advise me another way for doing it.
~~~~~~~~~~~~~~~~
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
~~~~~~~~~~~~~~~~~~~~~~
Behavior on MS SQL 7.0 (NEPTUNE - name of server)
~~~~~~~~~~~~~~~~~~~~~~
SQL Query Analyzer - Input
~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM
OPENQUERY(NEPTUNE, 'EXEC pw.dbo.z_j_test_x ''test1'', ''10''')
,OPENQUERY(NEPTUNE, 'EXEC pw.dbo.z_j_test_x ''test2'', ''100''')
SQL Query Analyzer - Output
~~~~~~~~~~~~~~~~~~~~~~
s_pk_1 s_pk_2 s_pk_1 s_pk_2
-------------------- ----------- -------------------- -----------
test1 10 test2 100
test1 11 test2 100
test1 10 test2 101
test1 11 test2 101
(4 row(s) affected)
SQL Profiler - Output
~~~~~~~~~~~~~~~~~~~~~~
EXEC pw.dbo.z_j_test_x 'test2', '100'
EXEC pw.dbo.z_j_test_x 'test1', '10'
sp_reset_connection
sp_reset_connection
SELECT * FROM
OPENQUERY(NEPTUNE, 'EXEC pw.dbo.z_j_test_x ''test1'', ''10''')
,OPENQUERY(NEPTUNE, 'EXEC pw.dbo.z_j_test_x ''test2'', ''100''')
~~~~~~~~~~~~~~~~~~~~~~
Behavior on MS SQL 2000 (JH - name of server)
~~~~~~~~~~~~~~~~~~~~~~
SQL Query Analyzer - Input
~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM
OPENQUERY(JH, 'EXEC pw.dbo.z_j_test_x ''test1'', ''10''')
,OPENQUERY(JH, 'EXEC pw.dbo.z_j_test_x ''test2'', ''100''')
SQL Query Analyzer - Output
~~~~~~~~~~~~~~~~~~~~~~
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'SQLOLEDB'. The provider could not support a required property. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
SQL Profiler - Trace Preferences
~~~~~~~~~~~~~~~~~~~~~~
Default setting +
Add Events / Error and warnings / Exception
Add Events / Error and warnings / OLEDB Errors
SQL Profiler - Output
~~~~~~~~~~~~~~~~~~~~~~
SET NO_BROWSETABLE ON
declare @P1 int
set @P1=3
exec sp_prepare @P1 output, NULL, N'EXEC pw.dbo.z_j_test_x ''test2'', ''100''', 1
select @P1
SET FMTONLY ON exec sp_execute 3 SET FMTONLY OFF
exec sp_unprepare 3
SET NO_BROWSETABLE OFF
exec sp_reset_connection
SET NO_BROWSETABLE ON
declare @P1 int
set @P1=4
exec sp_prepare @P1 output, NULL, N'EXEC pw.dbo.z_j_test_x ''test2'', ''100''', 1
select @P1
SET FMTONLY ON exec sp_execute 4 SET FMTONLY OFF
exec sp_unprepare 4
SET NO_BROWSETABLE OFF
exec sp_reset_connection
set implicit_transactions on
Error: 16937, Severity: 16, State: 1
Error: 16945, Severity: 16, State: 1
declare @P1 int
set @P1=0
declare @P2 int
set @P2=557064
declare @P3 int
set @P3=98305
declare @P4 int
set @P4=0
exec sp_cursoropen @P1 output, N'EXEC pw.dbo.z_j_test_x ''test2'', ''100''', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e21: EXEC pw.dbo.z_j_test_x 'test2', '100'[PROPID=DBPROP_SERVERCURSOR VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]
Error: 7320, Severity: 16, State: 2
IF @@TRANCOUNT > 0 ROLLBACK TRAN
set implicit_transactions off
exec sp_reset_connection
SELECT * FROM
OPENQUERY(JHURNANSKY, 'EXEC pw.dbo.z_j_test_x ''test1'', ''10''')
,OPENQUERY(JHURNANSKY, 'EXEC pw.dbo.z_j_test_x ''test2'', ''100''')