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