Hi there,
I've profiled a simple query (Query: Select * from northwind.dbo.orders; row limit: 107; read Uncommitted: true) and interestingly it opens up a cursor if you connect via ODBC or SQL Native; but not by OleDB - full configuration below:
However - in some circumstances a cursor is not usable - e.g. https://community.alteryx.com/t5/Data-Sources/Error-SQL-Execute-Cursors-Not-supported-on-Clustered-C...
Is there any way (even if we need to manually adjust via the XML settings) to ask Alteryx not to create the cursor and execute directly on the server as written?
Thank you
Sean
XML setup of this component (the ODBC version so that you can see the parameters set):
<Configuration>
<Passwords />
<File RecordLimit="107" FileFormat="23">odbc:DSN=DSNNAME|||SELECT * FROM "Northwind"."dbo"."Orders"</File>
<FormatSpecificOptions>
<PreSQL />
<NoProgress>True</NoProgress>
<CacheData>False</CacheData>
<TableStyle>Quoted</TableStyle>
<ReadUncommitted>True</ReadUncommitted>
<PostSQL />
</FormatSpecificOptions>
</Configuration>
Actual server workload for Direct SQL connection:
--Direct native SQL version
-- you can see that it first prepares the base query; then opens the cursor on the query and runs through its
exec [sys].sp_datatype_info_100 -4,@ODBCVer=4
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
exec [Northwind].[sys].sp_pkeys N'Orders',N'dbo',N'Northwind'
declare @p1 int
--set @p1=1
exec sp_prepare @p1 output,NULL,N'SELECT * FROM "Northwind"."dbo"."Orders"',1
select @p1
--declare @p1 int
--set @p1=1073741826
declare @p2 int
set @p2=180150003
declare @p5 int
set @p5=4
declare @p6 int
set @p6=1
declare @p7 int
set @p7=-1
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'SELECT * FROM "Northwind"."dbo"."Orders"',@p5 output,@p6 output,@p7 output
select @p1, @p2, @p5, @p6, @p7
exec sp_cursorfetch @p2,2,1,1575
exec sp_cursorunprepare @p1
go
--OLEDB version
-- Here you can see that with OleDB there is no cursor opened
exec sp_oledb_ro_usrname
go
select collationname(0x0904D00000)
go
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
select * from "Northwind"."dbo"."Orders"
go
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
exec sp_oledb_ro_usrname
go
select collationname(0x0904D00000)
go
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
select * from "Northwind"."dbo"."Orders"
go
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
--ODBC version
-- You can see that the ODBC version reads a lot more like the direct SQL edition, except with more checks to understand the version of hte database engine in play
-- Here we see the query being prepared, and the cursor being prepared with results being returned via cursor read
declare @p1 int
set @p1=1
exec sp_prepare @p1 output,NULL,N'SELECT SERVERPROPERTY(''EngineEdition'')',1
select @p1
go
exec sp_execute 1
go
declare @p1 int
set @p1=2
exec sp_prepare @p1 output,NULL,N'SELECT SERVERPROPERTY(''Edition'')',1
select @p1
go
exec sp_execute 2
go
exec sp_unprepare 2
go
exec sp_datatype_info -4,@ODBCVer=3
go
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
exec Northwind..sp_pkeys N'Orders',N'dbo',N'Northwind'
go
declare @p1 int
set @p1=3
exec sp_prepare @p1 output,NULL,N'SELECT * FROM "Northwind"."dbo"."Orders"',1
select @p1
go
exec sp_unprepare 3
go
declare @p1 int
set @p1=1073741828
declare @p2 int
set @p2=180150003
declare @p5 int
set @p5=4
declare @p6 int
set @p6=1
declare @p7 int
set @p7=-1
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'SELECT * FROM "Northwind"."dbo"."Orders"',@p5 output,@p6 output,@p7 output
select @p1, @p2, @p5, @p6, @p7
go
exec sp_cursorfetch 180150003,2,1,1575
go
exec sp_cursorunprepare 1073741828
go
