Error when running a Query

In query analyzer, when I have a cursor that runs through some code
enclosed in the begin and end statements. But, if the cursor dataset is
larger then 20 records, on the 20th record, it will give me this error.
The first 19 records will process fine.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
What would cause this error?
That's pretty tough to answer without actually seeing the code.
Andrew J. Kelly SQL MVP
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:CFB808F4-DA2A-4C88-97C8-083B2EBCBEA0@.microsoft.com...
> In query analyzer, when I have a cursor that runs through some code
> enclosed in the begin and end statements. But, if the cursor dataset is
> larger then 20 records, on the 20th record, it will give me this error.
> The first 19 records will process fine.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (WrapperRead()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> What would cause this error?
|||DECLARE Cust_Cursor CURSOR FOR
select distinct tp, tpprimnetwork from #pricesheetNCustWithAddr
OPEN Cust_Cursor
FETCH NEXT FROM Cust_Cursor
INTO @.CustNo,@.TPNetwork
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute sql4.dx_custom.dbo.dx_COM_RetrieveInvoiceToCustome r @.CustNo,
@.TPNetwork, @.InvcToTP = @.InvcToCust output
update #pricesheetNCustWithAddr
set InvcToTP = @.InvcToCust
where tp = @.CustNo
FETCH NEXT FROM Cust_Cursor
INTO @.CustNo,@.TPNetwork
END
CLOSE Cust_Cursor
DEALLOCATE Cust_Cursor
###########################################
CREATE procedure dx_COM_RetrieveInvoiceToCustomer (@.CustNo varchar(8),
@.TPNetwork varchar(100),
@.InvcToTP varchar(8) output)
as
--************************************************** *************
--* DateWhoDescription
--*----
--* 02/17/2005rhsiehNew
--* 02/22/2005rhsiehNetwork Nodes
--************************************************** *************
--attempts to go 9 layers down the tradingpartner network
--retrieve all the child nodes
select cnode.objectid, cnode.parentobjectid, cnode.tp, cnode.tpobjectid,
isnull(cnodeTP.hasinvoicetorole,null) as InvcToRole
into #NetworkNodes
from erp_p..coNetworkNode cnode
join erp_p..fdtradingpartne cnodeTP on cnodeTP.objectid = cnode.tpobjectid
where parentclassid = 10664 and collectionid = 1
--get the tp network and root node of the tradingpartner network
select ctn.tpnetworktpnname as TPNetwork,
rnode.objectid as RootNodeOID,
rnode.tp as RootNodeTP,
rnodeTP.hasinvoicetorole as RootNodeTPInvcToRole
into #Network1
from erp_p..coTPNetwork ctn
join erp_p..coVersionNode cvn on cvn.parentobjectid = ctn.objectid
join erp_p..coNetworkNode rnode on rnode.objectid = cvn.rootnodeobjectid
join erp_p..fdtradingpartne rnodeTP on rnodeTP.objectid = rnode.tpobjectid
where ctn.tpnetworktpnname = @.TPNetwork
and cvn.parentclassid = 11274 and cvn.collectionid = 1
and ctn.tpnetworkinsttype = 0
--get the child node(s) for the root node of the tradingparter network
select n1.*,
cnode.objectid as NodeOID1,
cnode.tp as TP1,
cnode.InvcToRole as TPInvcToRole1
into #network2
from #network1 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.RootNodeOID
--get the child node(s) for the first child node of the tradingparter network
select n1.*,
cnode.objectid as NodeOID2,
cnode.tp as TP2,
cnode.InvcToRole as TPInvcToRole2
into #network3
from #network2 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID1
--get the child node(s) for the previous child node of the tradingparter
network
select n1.*,
cnode.objectid as NodeOID3,
cnode.tp as TP3,
cnode.InvcToRole as TPInvcToRole3
into #network4
from #network3 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID2
--get the child node(s) for the previous child node of the tradingparter
network
select n1.*,
cnode.objectid as NodeOID4,
cnode.tp as TP4,
cnode.InvcToRole as TPInvcToRole4
into #network5
from #network4 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID3
--get the child node(s) for the previous child node of the tradingparter
network
select n1.*,
cnode.objectid as NodeOID5,
cnode.tp as TP5,
cnode.InvcToRole as TPInvcToRole5
into #network6
from #network5 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID4
/* ##COMMENTED OUT FOR NOT CAUSE A NETWORK WOULD NOT GO DOWN THIS FAR, BUT
LEFT IN IF IT DOES IN THE FUTURE##
--get the child node(s) for the previous child node of the tradingparter
network
select n1.*,
cnode.objectid as NodeOID6,
cnode.tp as TP6,
cnode.InvcToRole as TPInvcToRole6
into #network7
from #network6 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID5
--get the child node(s) for the previous child node of the tradingparter
network
select n1.*,
cnode.objectid as NodeOID7,
cnode.tp as TP7,
cnode.InvcToRole as TPInvcToRole7
into #network8
from #network7 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID6
--get the child node(s) for the previous child node of the tradingparter
network
select n1.*,
cnode.objectid as NodeOID8,
cnode.tp as TP8,
cnode.InvcToRole as TPInvcToRole8
into #network9
from #network8 n1
left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID7
*/
declare @.RootTP varchar(8), @.RootInvcToRole int,
@.TP1 varchar(8), @.TPInvcToRole1 int,
@.TP2 varchar(8), @.TPInvcToRole2 int,
@.TP3 varchar(8), @.TPInvcToRole3 int,
@.TP4 varchar(8), @.TPInvcToRole4 int,
@.TP5 varchar(8), @.TPInvcToRole5 int
DECLARE Customer_Cursor CURSOR FOR
select RootNodeTP, RootNodeTPInvcToRole,
TP1, TPInvcToRole1,
TP2, TPInvcToRole2,
TP3, TPInvcToRole3,
TP4, TPInvcToRole4,
TP5, TPInvcToRole5
from #network6
where tp5 = @.CustNo
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2,
@.TP3, @.TPInvcToRole3,
@.TP4, @.TPInvcToRole4,
@.TP5, @.TPInvcToRole5
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.TPInvcToRole5 = 1
begin
set @.InvcToTP = @.TP5
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole4 = 1
begin
set @.InvcToTP = @.TP4
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole3 = 1
begin
set @.InvcToTP = @.TP3
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole2 = 1
begin
set @.InvcToTP = @.TP2
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole1 = 1
begin
set @.InvcToTP = @.TP1
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.RootInvcToRole = 1
begin
set @.InvcToTP = @.RootTP
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
FETCH NEXT FROM Employee_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2,
@.TP3, @.TPInvcToRole3,
@.TP4, @.TPInvcToRole4,
@.TP5, @.TPInvcToRole5
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
DECLARE Customer_Cursor CURSOR FOR
select RootNodeTP, RootNodeTPInvcToRole,
TP1, TPInvcToRole1,
TP2, TPInvcToRole2,
TP3, TPInvcToRole3,
TP4, TPInvcToRole4
from #network6
where tp4 = @.CustNo
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2,
@.TP3, @.TPInvcToRole3,
@.TP4, @.TPInvcToRole4
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.TPInvcToRole4 = 1
begin
set @.InvcToTP = @.TP4
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole3 = 1
begin
set @.InvcToTP = @.TP3
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole2 = 1
begin
set @.InvcToTP = @.TP2
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole1 = 1
begin
set @.InvcToTP = @.TP1
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.RootInvcToRole = 1
begin
set @.InvcToTP = @.RootTP
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
FETCH NEXT FROM Employee_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2,
@.TP3, @.TPInvcToRole3,
@.TP4, @.TPInvcToRole4
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
DECLARE Customer_Cursor CURSOR FOR
select RootNodeTP, RootNodeTPInvcToRole,
TP1, TPInvcToRole1,
TP2, TPInvcToRole2,
TP3, TPInvcToRole3
from #network6
where tp3 = @.CustNo
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2,
@.TP3, @.TPInvcToRole3
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.TPInvcToRole3 = 1
begin
set @.InvcToTP = @.TP3
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole2 = 1
begin
set @.InvcToTP = @.TP2
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole1 = 1
begin
set @.InvcToTP = @.TP1
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.RootInvcToRole = 1
begin
set @.InvcToTP = @.RootTP
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
FETCH NEXT FROM Employee_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2,
@.TP3, @.TPInvcToRole3
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
DECLARE Customer_Cursor CURSOR FOR
select RootNodeTP, RootNodeTPInvcToRole,
TP1, TPInvcToRole1,
TP2, TPInvcToRole2
from #network6
where tp2 = @.CustNo
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.TPInvcToRole2 = 1
begin
set @.InvcToTP = @.TP2
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.TPInvcToRole1 = 1
begin
set @.InvcToTP = @.TP1
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.RootInvcToRole = 1
begin
set @.InvcToTP = @.RootTP
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
FETCH NEXT FROM Employee_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1,
@.TP2, @.TPInvcToRole2
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
DECLARE Customer_Cursor CURSOR FOR
select RootNodeTP, RootNodeTPInvcToRole,
TP1, TPInvcToRole1
from #network6
where tp1 = @.CustNo
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.TPInvcToRole1 = 1
begin
set @.InvcToTP = @.TP1
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
if @.RootInvcToRole = 1
begin
set @.InvcToTP = @.RootTP
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
FETCH NEXT FROM Employee_Cursor
INTO @.RootTP, @.RootInvcToRole,
@.TP1, @.TPInvcToRole1
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
DECLARE Customer_Cursor CURSOR FOR
select RootNodeTP, RootNodeTPInvcToRole
from #network6
where RootNodeTP = @.CustNo
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
INTO @.RootTP, @.RootInvcToRole
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.RootInvcToRole = 1
begin
set @.InvcToTP = @.RootTP
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
return 0
end
FETCH NEXT FROM Employee_Cursor
INTO @.RootTP, @.RootInvcToRole
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
GO
###########################################
"Andrew J. Kelly" wrote:

> That's pretty tough to answer without actually seeing the code.
> --
> Andrew J. Kelly SQL MVP
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:CFB808F4-DA2A-4C88-97C8-083B2EBCBEA0@.microsoft.com...
>
>
|||You have a &*^% load of cursors in the sp that you call fromt he first
cursor. It can take hours to go through that mess<g> of which unfortunately
I don't have the time at the moment. But I can say I can see that much of
it can probably be removed and a SET based method put in place. One thing
to try is change the cursors to STATIC and see if that helps some. I would
seriously think about cleaning up and optimizing that code though.
Andrew J. Kelly SQL MVP
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:97AE40F4-4C18-446D-8CAE-FB421432125A@.microsoft.com...[vbcol=seagreen]
> DECLARE Cust_Cursor CURSOR FOR
> select distinct tp, tpprimnetwork from #pricesheetNCustWithAddr
> OPEN Cust_Cursor
> FETCH NEXT FROM Cust_Cursor
> INTO @.CustNo,@.TPNetwork
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute sql4.dx_custom.dbo.dx_COM_RetrieveInvoiceToCustome r @.CustNo,
> @.TPNetwork, @.InvcToTP = @.InvcToCust output
> update #pricesheetNCustWithAddr
> set InvcToTP = @.InvcToCust
> where tp = @.CustNo
> FETCH NEXT FROM Cust_Cursor
> INTO @.CustNo,@.TPNetwork
> END
> CLOSE Cust_Cursor
> DEALLOCATE Cust_Cursor
>
> ###########################################
> CREATE procedure dx_COM_RetrieveInvoiceToCustomer (@.CustNo varchar(8),
> @.TPNetwork varchar(100),
> @.InvcToTP varchar(8) output)
> as
> --************************************************** *************
> --* Date Who Description
> --*----
> --* 02/17/2005 rhsieh New
> --* 02/22/2005 rhsieh Network Nodes
> --************************************************** *************
> --attempts to go 9 layers down the tradingpartner network
> --retrieve all the child nodes
> select cnode.objectid, cnode.parentobjectid, cnode.tp, cnode.tpobjectid,
> isnull(cnodeTP.hasinvoicetorole,null) as InvcToRole
> into #NetworkNodes
> from erp_p..coNetworkNode cnode
> join erp_p..fdtradingpartne cnodeTP on cnodeTP.objectid = cnode.tpobjectid
> where parentclassid = 10664 and collectionid = 1
> --get the tp network and root node of the tradingpartner network
> select ctn.tpnetworktpnname as TPNetwork,
> rnode.objectid as RootNodeOID,
> rnode.tp as RootNodeTP,
> rnodeTP.hasinvoicetorole as RootNodeTPInvcToRole
> into #Network1
> from erp_p..coTPNetwork ctn
> join erp_p..coVersionNode cvn on cvn.parentobjectid = ctn.objectid
> join erp_p..coNetworkNode rnode on rnode.objectid = cvn.rootnodeobjectid
> join erp_p..fdtradingpartne rnodeTP on rnodeTP.objectid = rnode.tpobjectid
> where ctn.tpnetworktpnname = @.TPNetwork
> and cvn.parentclassid = 11274 and cvn.collectionid = 1
> and ctn.tpnetworkinsttype = 0
> --get the child node(s) for the root node of the tradingparter network
> select n1.*,
> cnode.objectid as NodeOID1,
> cnode.tp as TP1,
> cnode.InvcToRole as TPInvcToRole1
> into #network2
> from #network1 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.RootNodeOID
> --get the child node(s) for the first child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID2,
> cnode.tp as TP2,
> cnode.InvcToRole as TPInvcToRole2
> into #network3
> from #network2 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID1
> --get the child node(s) for the previous child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID3,
> cnode.tp as TP3,
> cnode.InvcToRole as TPInvcToRole3
> into #network4
> from #network3 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID2
> --get the child node(s) for the previous child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID4,
> cnode.tp as TP4,
> cnode.InvcToRole as TPInvcToRole4
> into #network5
> from #network4 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID3
> --get the child node(s) for the previous child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID5,
> cnode.tp as TP5,
> cnode.InvcToRole as TPInvcToRole5
> into #network6
> from #network5 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID4
> /* ##COMMENTED OUT FOR NOT CAUSE A NETWORK WOULD NOT GO DOWN THIS FAR, BUT
> LEFT IN IF IT DOES IN THE FUTURE##
> --get the child node(s) for the previous child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID6,
> cnode.tp as TP6,
> cnode.InvcToRole as TPInvcToRole6
> into #network7
> from #network6 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID5
> --get the child node(s) for the previous child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID7,
> cnode.tp as TP7,
> cnode.InvcToRole as TPInvcToRole7
> into #network8
> from #network7 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID6
> --get the child node(s) for the previous child node of the tradingparter
> network
> select n1.*,
> cnode.objectid as NodeOID8,
> cnode.tp as TP8,
> cnode.InvcToRole as TPInvcToRole8
> into #network9
> from #network8 n1
> left join #NetworkNodes cnode on cnode.parentobjectid = n1.NodeOID7
> */
> declare @.RootTP varchar(8), @.RootInvcToRole int,
> @.TP1 varchar(8), @.TPInvcToRole1 int,
> @.TP2 varchar(8), @.TPInvcToRole2 int,
> @.TP3 varchar(8), @.TPInvcToRole3 int,
> @.TP4 varchar(8), @.TPInvcToRole4 int,
> @.TP5 varchar(8), @.TPInvcToRole5 int
> DECLARE Customer_Cursor CURSOR FOR
> select RootNodeTP, RootNodeTPInvcToRole,
> TP1, TPInvcToRole1,
> TP2, TPInvcToRole2,
> TP3, TPInvcToRole3,
> TP4, TPInvcToRole4,
> TP5, TPInvcToRole5
> from #network6
> where tp5 = @.CustNo
> OPEN Customer_Cursor
> FETCH NEXT FROM Customer_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2,
> @.TP3, @.TPInvcToRole3,
> @.TP4, @.TPInvcToRole4,
> @.TP5, @.TPInvcToRole5
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.TPInvcToRole5 = 1
> begin
> set @.InvcToTP = @.TP5
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole4 = 1
> begin
> set @.InvcToTP = @.TP4
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole3 = 1
> begin
> set @.InvcToTP = @.TP3
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole2 = 1
> begin
> set @.InvcToTP = @.TP2
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole1 = 1
> begin
> set @.InvcToTP = @.TP1
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.RootInvcToRole = 1
> begin
> set @.InvcToTP = @.RootTP
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> FETCH NEXT FROM Employee_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2,
> @.TP3, @.TPInvcToRole3,
> @.TP4, @.TPInvcToRole4,
> @.TP5, @.TPInvcToRole5
> END
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> DECLARE Customer_Cursor CURSOR FOR
> select RootNodeTP, RootNodeTPInvcToRole,
> TP1, TPInvcToRole1,
> TP2, TPInvcToRole2,
> TP3, TPInvcToRole3,
> TP4, TPInvcToRole4
> from #network6
> where tp4 = @.CustNo
> OPEN Customer_Cursor
> FETCH NEXT FROM Customer_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2,
> @.TP3, @.TPInvcToRole3,
> @.TP4, @.TPInvcToRole4
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.TPInvcToRole4 = 1
> begin
> set @.InvcToTP = @.TP4
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole3 = 1
> begin
> set @.InvcToTP = @.TP3
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole2 = 1
> begin
> set @.InvcToTP = @.TP2
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole1 = 1
> begin
> set @.InvcToTP = @.TP1
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.RootInvcToRole = 1
> begin
> set @.InvcToTP = @.RootTP
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> FETCH NEXT FROM Employee_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2,
> @.TP3, @.TPInvcToRole3,
> @.TP4, @.TPInvcToRole4
> END
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> DECLARE Customer_Cursor CURSOR FOR
> select RootNodeTP, RootNodeTPInvcToRole,
> TP1, TPInvcToRole1,
> TP2, TPInvcToRole2,
> TP3, TPInvcToRole3
> from #network6
> where tp3 = @.CustNo
> OPEN Customer_Cursor
> FETCH NEXT FROM Customer_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2,
> @.TP3, @.TPInvcToRole3
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.TPInvcToRole3 = 1
> begin
> set @.InvcToTP = @.TP3
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole2 = 1
> begin
> set @.InvcToTP = @.TP2
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole1 = 1
> begin
> set @.InvcToTP = @.TP1
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.RootInvcToRole = 1
> begin
> set @.InvcToTP = @.RootTP
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> FETCH NEXT FROM Employee_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2,
> @.TP3, @.TPInvcToRole3
> END
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> DECLARE Customer_Cursor CURSOR FOR
> select RootNodeTP, RootNodeTPInvcToRole,
> TP1, TPInvcToRole1,
> TP2, TPInvcToRole2
> from #network6
> where tp2 = @.CustNo
> OPEN Customer_Cursor
> FETCH NEXT FROM Customer_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.TPInvcToRole2 = 1
> begin
> set @.InvcToTP = @.TP2
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.TPInvcToRole1 = 1
> begin
> set @.InvcToTP = @.TP1
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.RootInvcToRole = 1
> begin
> set @.InvcToTP = @.RootTP
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> FETCH NEXT FROM Employee_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1,
> @.TP2, @.TPInvcToRole2
> END
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> DECLARE Customer_Cursor CURSOR FOR
> select RootNodeTP, RootNodeTPInvcToRole,
> TP1, TPInvcToRole1
> from #network6
> where tp1 = @.CustNo
> OPEN Customer_Cursor
> FETCH NEXT FROM Customer_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.TPInvcToRole1 = 1
> begin
> set @.InvcToTP = @.TP1
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> if @.RootInvcToRole = 1
> begin
> set @.InvcToTP = @.RootTP
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> FETCH NEXT FROM Employee_Cursor
> INTO @.RootTP, @.RootInvcToRole,
> @.TP1, @.TPInvcToRole1
> END
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> DECLARE Customer_Cursor CURSOR FOR
> select RootNodeTP, RootNodeTPInvcToRole
> from #network6
> where RootNodeTP = @.CustNo
> OPEN Customer_Cursor
> FETCH NEXT FROM Customer_Cursor
> INTO @.RootTP, @.RootInvcToRole
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.RootInvcToRole = 1
> begin
> set @.InvcToTP = @.RootTP
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> return 0
> end
> FETCH NEXT FROM Employee_Cursor
> INTO @.RootTP, @.RootInvcToRole
> END
> CLOSE Customer_Cursor
> DEALLOCATE Customer_Cursor
> GO
> ###########################################
> "Andrew J. Kelly" wrote:
|||I did already. I changed it, so I am not using cursors but using temporary
tables to get the info I needed. But, I responded just to figure out why I
was getting the error message so, in the future I would know why.
"Andrew J. Kelly" wrote:

> You have a &*^% load of cursors in the sp that you call fromt he first
> cursor. It can take hours to go through that mess<g> of which unfortunately
> I don't have the time at the moment. But I can say I can see that much of
> it can probably be removed and a SET based method put in place. One thing
> to try is change the cursors to STATIC and see if that helps some. I would
> seriously think about cleaning up and optimizing that code though.
> --
> Andrew J. Kelly SQL MVP
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:97AE40F4-4C18-446D-8CAE-FB421432125A@.microsoft.com...
>
>
sql

error using cursor

Hi,
I create a sp using the cursor, but I got"
"Remote tables are not updatable. Updatable keyset-driven
cursors on remote tables require a transaction with the
REPEATABLE_READ or SERIALIZABLE isolation level spanning
the cursor."
How can I use cursor? Thanks
Declare myCursor Cursor For
select id, date1
from linkedserver1.import.user.table1
Open myCursor
Fetch Next From myCursor
Into @.ID, @.date1
While @.@.FETCH_STATUS = 0
Begin
Set @.CommandText = 'Delete From temp_table Where' +
' id = ' + Cast(@.ID as varchar(100)) +
' and dt < ''' + Convert(varchar(50),
@.date1, 121) + ''''
Print @.CommandText
-- Exec (@.CommandText)
Fetch Next From myCursor
Into @.ID, @.date1
End
Close myCursor
Deallocate myCursor
Return 1Jen (anonymous@.discussions.microsoft.com) writes:
> I create a sp using the cursor, but I got"
> "Remote tables are not updatable. Updatable keyset-driven
> cursors on remote tables require a transaction with the
> REPEATABLE_READ or SERIALIZABLE isolation level spanning
> the cursor."
> How can I use cursor? Thanks
It is all in the error message. You have an updatable cursor on a linked
server. This requires a higher transaction isolation level than the
standard READ COMMITTED. You can change this with the command
SET TRANSACTION ISOLATION LEVEL.
Now, if the cursor code you have is authentic, there are alternatives.
You are not really using the updatability of the cursor, so you could
add FOR READ ONLY at the end of the cursor declaration.
I am also allergic against keyset-driven cursors. They often causes
nasty surprises. Add INSENSITIVE before the CURSOR keyword, and SQL
Server will copy the data into a temp table and work from there.
Finally:
> Set @.CommandText => 'Delete From temp_table Where' +
> ' id = ' + Cast(@.ID as varchar(100)) +
> ' and dt < ''' + Convert(varchar(50),
> @.date1, 121) + ''''
> Print @.CommandText
> -- Exec (@.CommandText)
I can't see any reason to use dynamic SQL here. Just run the delete
statement.
Or maybe even better: don't use a cursor at all, but just say:
DELETE temp_table
FROM temp_table t
WHERE EXISTS (SELECT * linkedserver1.import.user.table1 l
WHERE t.i = cast(l.id as varchar(100)
AND dt.date < convert(varchar(29), l.date, 126)
(Not also the change of format code. 126 works with all dateformat
settings; 121 does not.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Thanks.
I am not quite familiar with cursor. I just use thebasic
syntax as the BOL example. why the default to be ketset,
updateable?
From BOL declare cursor, mine matches both sql92 and T-SQL
syntax, I don't have any option wors specified, should it
be read-only?
>--Original Message--
>Jen (anonymous@.discussions.microsoft.com) writes:
>> I create a sp using the cursor, but I got"
>> "Remote tables are not updatable. Updatable keyset-
driven
>> cursors on remote tables require a transaction with the
>> REPEATABLE_READ or SERIALIZABLE isolation level
spanning
>> the cursor."
>> How can I use cursor? Thanks
>It is all in the error message. You have an updatable
cursor on a linked
>server. This requires a higher transaction isolation
level than the
>standard READ COMMITTED. You can change this with the
command
>SET TRANSACTION ISOLATION LEVEL.
>Now, if the cursor code you have is authentic, there are
alternatives.
>You are not really using the updatability of the cursor,
so you could
>add FOR READ ONLY at the end of the cursor declaration.
>I am also allergic against keyset-driven cursors. They
often causes
>nasty surprises. Add INSENSITIVE before the CURSOR
keyword, and SQL
>Server will copy the data into a temp table and work from
there.
>Finally:
>> Set @.CommandText =>> 'Delete From temp_table Where' +
>> ' id = ' + Cast(@.ID as varchar(100)) +
>> ' and dt < ''' + Convert(varchar(50),
>> @.date1, 121) + ''''
>> Print @.CommandText
>> -- Exec (@.CommandText)
>I can't see any reason to use dynamic SQL here. Just run
the delete
>statement.
>Or maybe even better: don't use a cursor at all, but just
say:
> DELETE temp_table
> FROM temp_table t
> WHERE EXISTS (SELECT *
linkedserver1.import.user.table1 l
> WHERE t.i = cast(l.id as varchar(100)
> AND dt.date < convert(varchar(29),
l.date, 126)
>(Not also the change of format code. 126 works with all
dateformat
>settings; 121 does not.)
>--
>Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/book
s.asp
>.
>|||Jen (anonymous@.discussions.microsoft.com) writes:
> I am not quite familiar with cursor. I just use thebasic
> syntax as the BOL example. why the default to be ketset,
> updateable?
Good question. Had it been to me, the default would have been read-only
and insensitive. Updatable is bad, because in practice you very rarely
use this feature. I might update data in the table I'm iterating over,
but in such case I use keys and not WHERE CURRENT OF. And keyset is bad,
because sometimes due to the rules of keyset cursors, SQL Server might
need to resort to a real ugly query plan. (At least this happened in
SQL 6.5. I might not have seen in in SQL 2000, but I try to remember to
always use insensitive cursors.)
> From BOL declare cursor, mine matches both sql92 and T-SQL
> syntax, I don't have any option wors specified, should it
> be read-only?
I believe both INSENSITIVE and FOR READ ONLY conforms with SQL-92.
And, yes, using these are probably your second-best solution.
Your best solution is not use cursors at all, but use a set based
solution.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp