Dual usage of a openquery in a select statemnet

[chosse]

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''')

 

[kavin]

I m not sure about ur problem but can u use linked server instead of Openquery, as i have found sometimes Openquery behavior is undetermistic.

 

[chosse]

It work fine and deterministic on MS SQL 7.0, under MS SQL 2000 it is ok, when aj use openquery only one time in select.
Try it on your server please and send me info.

You must only enable data access, create stored procedure and run select statement.

Thank's

 

[maswien]

I think you don't actually need openquery() in this case,

try following in your local database:


EXEC JHURNANSKY.pw.dbo.z_j_test_x 'test1', 10

 

[chosse]

It work fine with
EXEC JH.pw.dbo.z_j_test_x 'test1', 10
and with
EXEC JH.pw.dbo.z_j_test_x 'test1', '10'
too.

But i need use result (rowset) from stored procedure in select and more times. There it isn't work.

Try it on your SQL server please.

Thank's

 

[maswien]

I ran following SQL in a local SQL2k database to a SQL 7
linked server, or ran it in a local SQL 7 database to a
SQL2k linked server, both work fine.

select * from openquery
(sgao2, 'exec tektips.dbo.z_j_test_x ''test1'', 10')

 

[chosse]

Thak's for reply.
I need run this statement from SQL2k to same SQL2k linked server, because I need return rowset from stored procedure in select statement.

When I run this (like you):
select * from openquery
(sgao2, 'exec tektips.dbo.z_j_test_x ''test1'', 10')
it run well.

But when I run this :
select * from
openquery(sgao2, 'exec tektips.dbo.z_j_test_x ''test1'', 10')
,openquery(sgao2, 'exec tektips.dbo.z_j_test_x ''test2'', 100')

it doesn't run well under sql2k.

It's my problem.

 

[maswien]

Why not use SP in local to deal with the reselts from linked server too?

insert into #temp1
EXEC JHURNANSKY.pw.dbo.z_j_test_x 'test1', 10

insert into #temp2
EXEC JHURNANSKY.pw.dbo.z_j_test_x 'test1', 100

select * from #temp1 inner join #temp2 on ...
where ...

 

[chosse]

I need to use result from SP in select, therefore I use openquery from same SQL server.
I use complex SP, wich I need use more times in select statement.
Therefore I want to find way for doing it.
I think, that i must forbit use cursor. That is in SQL 7.0. Under SQL 2k is cursor use internali to join both openquery results.

Have you same suggestion ?

 

[maswien]

You mean the SP in remote server is complex, right? I don't think that's a issue as long as it returns a resultset as the SP in the example, insert the result to a temparory table and then you can do whatever you like.

 

[chosse]

Thank's for answer.

Yes, it si one way use temporary table.

But for me is easier write 1 select statement where i can use for example 3 times openquery function for some subselect then do only refresh, when it is needed, on select and all work easy
otherwise
I must befor refresh build temporary table and manage their name and other association.

Have you some suggestion, how forbit use cursor in my case ?

 

[SQLSister]

Temp tables are built once in the sp that uses them, no need to manage names etc as they are local to the scope of each user running the sp.

Remember that easy programming often results in poor application performance so it should not be a consideration in determining how to perform a task. If a more complex programming task will perform better (or in this case at all since you can't get your method to work.) then you should take the time to learn it. And in any event I never ever would use a cursor to do this task. I lke mjia's idea.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[chosse]

Thank's, I think that it's true.

But in my case I built a SP to improve performance.

Sometimes the data was fetched from SQL server utilize select statement, on the client was processed and written to some temp table (from 1 to 3 tabels) to SQL server and than was used in main selecet statement. The temp tables are unique and they are dependent on user's input values.
When user change some input value, the data are refreshed. Then I must rebuild temp table and refersh the main select.
The same tool may the user use some times at this time. Therefore I must manage the names of the temp table.

In other way I can built only the 1 main select with using the openquery and the SP, without using the temp table, with similar performance and with smaller time for development.
In my applications I can use similar reason hundred times.

Therefor I want use the openquery (with calling the SP) more times in the select statement.
And why do I use the SP ? Because I must process a data in compliance with user's input values, which I cann't do in the main select statement or in same case I can do it but with worst performance and with bigger development time.

And the cursor ? I don't want to use cursor. I wrote about processing select statement as :
SELECT * FROM
OPENQUERY(JH, 'EXEC pw.dbo.z_j_test_x ''test1'', 10')
,OPENQUERY(JH, 'EXEC pw.dbo.z_j_test_x ''test2'', 100')
In this case is using cursor for join tabels and therefore it doesn't run under SQL 2000. Under SQL 7.0 cursor isn't use. You can see it in SQL Profiler. And therefore I ask you :
how can I forbid using cursor in processing this select statement ?

 

[maswien]

It's not necessary to use cursor in your case, so I don't know what you mean about forbid using cursor. As SQL sister explained to you, temporary tables are local to the scope of each user, you can imagine each connection is a session which has a unique session id, the temporary table will have a name like sessionId.temporaryTableName, different users won't have table name conflict issue. Temporary tables are meant to be temporary, so they won't exist after each run of the SP, so you don't need worry about the data refresh issue eithor.

 

[chosse]

OK, I know manage temporary table. I wanted to tell only, that i must find way for it.

And what about cursor use ?
The cursor is use internaly for process select statement :
SELECT * FROM
OPENQUERY(JH, 'EXEC pw.dbo.z_j_test_x ''test1'', 10')
,OPENQUERY(JH, 'EXEC pw.dbo.z_j_test_x ''test2'', 100')
to join table from the first and the secand openquery function, which return rowset. You can see it in SQL Profiler when you run select above. And I want to find the to tell, in the select statement, to don't use cursor for join table, but only under SQL 2000.
SQL 7.00 don't use cursor for join that tables and therefore it work well there.

 

[maswien]

Why do you think this query use cursor? Under no circumstances that SQL Server will use a cursor internal for a query if the user doesn't define a cursor. Can you paste the trace you got in the SQL Profiler to this thread?

 

[chosse]

In my first subscription I wrote SQL Profiler trace from SQL 7.0 and SQL 2000 too. From it is comprehensible, that internally is use cursor under SQL 2000 and therefore an error occurred.
Therefore I want to find way for forbit to use cursor.

 

[SQLSister]

I guess I just dont understand your object to using the linked table referncing method. BOL thinks that linked tables are better than OPenquery and you should use it if possible.

Neither OPENDATASOURCE nor OPENROWSET provide all of the functionality of linked server definitions

Since OpenQuery isn't doing what you want, why are you unwilling to try using linked tables instead?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[chosse]

Hi SQLSister

The openquery is only one way for return a rowset from a SP, where i have my t-sql code under SQL 7.
It is what I need.
But I need use an openquery in a select statement more times and it doesn't work well under SQL 2000 because is used a cursor (internaly by OLE DB provider for joining output from openquery functions).