Hi!!
I have 2 tables in a report that runs against the same dataset. And
when I try to filter on a field called T0650_CONDITION_TYPE_ID, I get
an error.
"The processing of filter expression for the table 'table2' cannot be
performed. The comparison failed. Please check the data type retured
by filter expression."
The filter is =Fields!T0650_CONDITION_TYPE_ID.Value = 2
I've searched the internet and found different suggestions to
solutions but none of them works.
I've tried to =2 in the value-field, I have tried to convert, but I
doesn't get it to work.
And I need this filter to just show T0650_CONDITION_TYPE_ID.Value = 1
in one table and T0650_CONDITION_TYPE_ID.Value = 2 in the other.
I have tried to hide, but then I get empty spaces, and it doesn't look
good.
T0650_CONDITION_TYPE_ID is a tinyint.
I'm grateful for any help
BR LottaCould you try
=(CInt(Fields!T0650_CONDITION_TYPE_ID.Value) = 2) ?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lotta" <lotta.svensson@.europeiska.com> wrote in message
news:7ec6061a.0503020012.eb2f8f0@.posting.google.com...
> Hi!!
> I have 2 tables in a report that runs against the same dataset. And
> when I try to filter on a field called T0650_CONDITION_TYPE_ID, I get
> an error.
> "The processing of filter expression for the table 'table2' cannot be
> performed. The comparison failed. Please check the data type retured
> by filter expression."
> The filter is =Fields!T0650_CONDITION_TYPE_ID.Value = 2
> I've searched the internet and found different suggestions to
> solutions but none of them works.
> I've tried to =2 in the value-field, I have tried to convert, but I
> doesn't get it to work.
> And I need this filter to just show T0650_CONDITION_TYPE_ID.Value = 1
> in one table and T0650_CONDITION_TYPE_ID.Value = 2 in the other.
> I have tried to hide, but then I get empty spaces, and it doesn't look
> good.
> T0650_CONDITION_TYPE_ID is a tinyint.
> I'm grateful for any help
> BR Lotta
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
Thursday, March 29, 2012
Monday, March 26, 2012
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
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
Wednesday, March 21, 2012
Error when file is saved to PDF
I am calling a procedure that will save a file to PDF. It runs successfully. When I go to look at the PDF, I get a message, "Acrobat reader could not open 'Doc.pdf' because it is either not a support file or because the file has been damaged."
This is how I'm calling the procedure:
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?/TestReports/MyReport&rs:Command=Render&rs:Format=PDF", "C:\" + "MyReport.pdf")
Ifaber,
Is this a SSIS ralated issue? If so, please provide more details about the error, which task, etc...
|||I'm with Rafael. I cannot find a SaveFile function when searching in an SSIS script component.|||Yes, this is a SSIS issue. I put this script in a Script Task.
See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1173906&SiteID=1&PageID=1 for reference. I have jumped through a lot of errors, and this is my last problem.
|||ifaber wrote:
Yes, this is a SSIS issue. I put this script in a Script Task.
See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1173906&SiteID=1&PageID=1 for reference. I have jumped through a lot of errors, and this is my last problem.
In that case, you're going to have to provide your script for us to look at. The link you provided doesn't serve as a valid foundation for your question.
My question though, is where is the PDF data stream? You can't create a PDF file by merely adding a .pdf extension. The data going into SaveFile needs to be a PDF binary stream, I would think.|||
I don't know anything about a PDF data stream. I read somewhere else on the net that 'rs:Command=Render&rs:Format=PDF' would work.
This is the sample that I got and used:
//Sample call
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=PDF", outpath + "FILENAME.pdf")
//Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format
//The get & save file method
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
Dim loRequest As System.Net.HttpWebRequest
Dim loResponse As System.Net.HttpWebResponse
Dim loResponseStream As System.IO.Stream
Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim laBytes(256) As Byte
Dim liCount As Integer = 1
Try
loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
loRequest.Method = "GET"
loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
loResponseStream = loResponse.GetResponseStream
Do While liCount > 0
liCount = loResponseStream.Read(laBytes, 0, 256)
loFileStream.Write(laBytes, 0, liCount)
Loop
loFileStream.Flush()
loFileStream.Close()
Catch ex As Exception
End Try
End Sub
|||Unless you're having issues with the variables, this looks like it's an SSRS issue....I think the thread already going in the SSRS forum would be more beneficial.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1199641&SiteID=1|||I had issues with the variables because I had a typo. Now it just won't create the PDF.|||
I had issues with the variables because I had a typo. Now it just won't create the PDF.
Yep, then we might want to look at moving this back to the SSRS forum for starters. The code you are building isn't specific to SSIS (though you might be using SSIS variables) so we are likely not going to be able to help you any further.|||Ok...thanks|||
Ok...thanks
Moved to the SSRS Forum to see if anyone over here has any ideas for this custom code.
ifaber is trying to build a PDF file using a reporting services call. The user is trying to perform this in an SSIS task, but the code the user has built isn't SSIS specific.
Thanks,
Phil Brammer
SSIS Moderator
Error when file is saved to PDF
I am calling a procedure that will save a file to PDF. It runs successfully. When I go to look at the PDF, I get a message, "Acrobat reader could not open 'Doc.pdf' because it is either not a support file or because the file has been damaged."
This is how I'm calling the procedure:
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?/TestReports/MyReport&rs:Command=Render&rs:Format=PDF", "C:\" + "MyReport.pdf")
Ifaber,
Is this a SSIS ralated issue? If so, please provide more details about the error, which task, etc...
|||I'm with Rafael. I cannot find a SaveFile function when searching in an SSIS script component.|||Yes, this is a SSIS issue. I put this script in a Script Task.
See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1173906&SiteID=1&PageID=1 for reference. I have jumped through a lot of errors, and this is my last problem.
|||ifaber wrote:
Yes, this is a SSIS issue. I put this script in a Script Task.
See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1173906&SiteID=1&PageID=1 for reference. I have jumped through a lot of errors, and this is my last problem.
In that case, you're going to have to provide your script for us to look at. The link you provided doesn't serve as a valid foundation for your question.
My question though, is where is the PDF data stream? You can't create a PDF file by merely adding a .pdf extension. The data going into SaveFile needs to be a PDF binary stream, I would think.|||
I don't know anything about a PDF data stream. I read somewhere else on the net that 'rs:Command=Render&rs:Format=PDF' would work.
This is the sample that I got and used:
//Sample call
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=PDF", outpath + "FILENAME.pdf")
//Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format
//The get & save file method
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
Dim loRequest As System.Net.HttpWebRequest
Dim loResponse As System.Net.HttpWebResponse
Dim loResponseStream As System.IO.Stream
Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim laBytes(256) As Byte
Dim liCount As Integer = 1
Try
loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
loRequest.Method = "GET"
loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
loResponseStream = loResponse.GetResponseStream
Do While liCount > 0
liCount = loResponseStream.Read(laBytes, 0, 256)
loFileStream.Write(laBytes, 0, liCount)
Loop
loFileStream.Flush()
loFileStream.Close()
Catch ex As Exception
End Try
End Sub
|||Unless you're having issues with the variables, this looks like it's an SSRS issue....I think the thread already going in the SSRS forum would be more beneficial.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1199641&SiteID=1|||I had issues with the variables because I had a typo. Now it just won't create the PDF.|||
I had issues with the variables because I had a typo. Now it just won't create the PDF.
Yep, then we might want to look at moving this back to the SSRS forum for starters. The code you are building isn't specific to SSIS (though you might be using SSIS variables) so we are likely not going to be able to help you any further.|||Ok...thanks|||
Ok...thanks
Moved to the SSRS Forum to see if anyone over here has any ideas for this custom code.
ifaber is trying to build a PDF file using a reporting services call. The user is trying to perform this in an SSIS task, but the code the user has built isn't SSIS specific.
Thanks,
Phil Brammer
SSIS Moderator
Monday, March 19, 2012
error when connecting trhough enterprise manager
hi all ,
i've got an application with sql databse. it runs correctly on the server.
when i go to windows Xp and installed the appliaction and run the sql
enterprise manager to connect to the server, i get the following error:
message =: ExecuteReader requires an open and available connection. the
connection's current state is closed.
i've checked network connection ..it is ok.
the same application we've tested at office ...its working fine.
plz advise
kryshzt
Hi
Are you sure? This error's message is pretty clear, isn't it?
"kryshzt" <kryshzt@.discussions.microsoft.com> wrote in message
news:8636B1D3-80F7-40C7-A384-3DC1E6E9C8ED@.microsoft.com...
> hi all ,
> i've got an application with sql databse. it runs correctly on the server.
> when i go to windows Xp and installed the appliaction and run the sql
> enterprise manager to connect to the server, i get the following error:
> message =: ExecuteReader requires an open and available connection. the
> connection's current state is closed.
> i've checked network connection ..it is ok.
> the same application we've tested at office ...its working fine.
> plz advise
> kryshzt
i've got an application with sql databse. it runs correctly on the server.
when i go to windows Xp and installed the appliaction and run the sql
enterprise manager to connect to the server, i get the following error:
message =: ExecuteReader requires an open and available connection. the
connection's current state is closed.
i've checked network connection ..it is ok.
the same application we've tested at office ...its working fine.
plz advise
kryshzt
Hi
Are you sure? This error's message is pretty clear, isn't it?
"kryshzt" <kryshzt@.discussions.microsoft.com> wrote in message
news:8636B1D3-80F7-40C7-A384-3DC1E6E9C8ED@.microsoft.com...
> hi all ,
> i've got an application with sql databse. it runs correctly on the server.
> when i go to windows Xp and installed the appliaction and run the sql
> enterprise manager to connect to the server, i get the following error:
> message =: ExecuteReader requires an open and available connection. the
> connection's current state is closed.
> i've checked network connection ..it is ok.
> the same application we've tested at office ...its working fine.
> plz advise
> kryshzt
error when connecting trhough enterprise manager
hi all ,
i've got an application with sql databse. it runs correctly on the server.
when i go to Windows XP and installed the appliaction and run the sql
enterprise manager to connect to the server, i get the following error:
message =: ExecuteReader requires an open and available connection. the
connection's current state is closed.
i've checked network connection ..it is ok.
the same application we've tested at office ...its working fine.
plz advise
kryshztHi
Are you sure? This error's message is pretty clear, isn't it?
"kryshzt" <kryshzt@.discussions.microsoft.com> wrote in message
news:8636B1D3-80F7-40C7-A384-3DC1E6E9C8ED@.microsoft.com...
> hi all ,
> i've got an application with sql databse. it runs correctly on the server.
> when i go to Windows XP and installed the appliaction and run the sql
> enterprise manager to connect to the server, i get the following error:
> message =: ExecuteReader requires an open and available connection. the
> connection's current state is closed.
> i've checked network connection ..it is ok.
> the same application we've tested at office ...its working fine.
> plz advise
> kryshzt
i've got an application with sql databse. it runs correctly on the server.
when i go to Windows XP and installed the appliaction and run the sql
enterprise manager to connect to the server, i get the following error:
message =: ExecuteReader requires an open and available connection. the
connection's current state is closed.
i've checked network connection ..it is ok.
the same application we've tested at office ...its working fine.
plz advise
kryshztHi
Are you sure? This error's message is pretty clear, isn't it?
"kryshzt" <kryshzt@.discussions.microsoft.com> wrote in message
news:8636B1D3-80F7-40C7-A384-3DC1E6E9C8ED@.microsoft.com...
> hi all ,
> i've got an application with sql databse. it runs correctly on the server.
> when i go to Windows XP and installed the appliaction and run the sql
> enterprise manager to connect to the server, i get the following error:
> message =: ExecuteReader requires an open and available connection. the
> connection's current state is closed.
> i've checked network connection ..it is ok.
> the same application we've tested at office ...its working fine.
> plz advise
> kryshzt
error when connecting trhough enterprise manager
hi all ,
i've got an application with sql databse. it runs correctly on the server.
when i go to windows Xp and installed the appliaction and run the sql
enterprise manager to connect to the server, i get the following error:
message =: ExecuteReader requires an open and available connection. the
connection's current state is closed.
i've checked network connection ..it is ok.
the same application we've tested at office ...its working fine.
plz advise
kryshztHi
Are you sure? This error's message is pretty clear, isn't it?
"kryshzt" <kryshzt@.discussions.microsoft.com> wrote in message
news:8636B1D3-80F7-40C7-A384-3DC1E6E9C8ED@.microsoft.com...
> hi all ,
> i've got an application with sql databse. it runs correctly on the server.
> when i go to windows Xp and installed the appliaction and run the sql
> enterprise manager to connect to the server, i get the following error:
> message =: ExecuteReader requires an open and available connection. the
> connection's current state is closed.
> i've checked network connection ..it is ok.
> the same application we've tested at office ...its working fine.
> plz advise
> kryshzt
i've got an application with sql databse. it runs correctly on the server.
when i go to windows Xp and installed the appliaction and run the sql
enterprise manager to connect to the server, i get the following error:
message =: ExecuteReader requires an open and available connection. the
connection's current state is closed.
i've checked network connection ..it is ok.
the same application we've tested at office ...its working fine.
plz advise
kryshztHi
Are you sure? This error's message is pretty clear, isn't it?
"kryshzt" <kryshzt@.discussions.microsoft.com> wrote in message
news:8636B1D3-80F7-40C7-A384-3DC1E6E9C8ED@.microsoft.com...
> hi all ,
> i've got an application with sql databse. it runs correctly on the server.
> when i go to windows Xp and installed the appliaction and run the sql
> enterprise manager to connect to the server, i get the following error:
> message =: ExecuteReader requires an open and available connection. the
> connection's current state is closed.
> i've checked network connection ..it is ok.
> the same application we've tested at office ...its working fine.
> plz advise
> kryshzt
Wednesday, February 15, 2012
Error running report after deploying
I have created a function that queries the database for a value if conditions
are met. IE the query in the function runs less than 2% of report. In preview
mode in VS.net, the report works correctly. When I use Report Manager to
upload the report, when the query runs, it places #Error in the report field.
This is the begining of the function:
Public Function SKU(rootSerial as String, _
currentSKU as Guid)as String
Dim currentSKUStr as String
currentSKUStr=currentSKU.ToString()
If rootSerial = "" Then
Else
Dim arset As Guid
Dim oConn as New System.Data.SqlClient.SqlConnection
oConn.ConnectionString = "Data Source=PPNS1;Initial
Catalog=Persyst_Development_MSCRM;Integrated Security=SSPI;"
oConn.Open()
Dim oCmd as New System.Data.SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandText = "SELECT New_ProductId FROM
New_PersystSerialNumberExtensionBase WHERE (New_SerialNumber = N'4320401')"
arset = oCmd.ExecuteScalar()
currentSKUStr = arset.ToString()
oConn.Close()
End IF
There is additional code after End If, but that code is run for each data
row. I also have verified in the IDE via msgbox, that the correct value is
being returned from the query.
1. Is it possible to deploy a function that contains its own query as I have
done?
2. What could be the cause of it working in the IDE but not when deployed?
TIA, JimUpdate:
I have traced the problem to:
Request for the permission of type
System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
How do I set security for embeded code?
"JHPArizona" wrote:
> I have created a function that queries the database for a value if conditions
> are met. IE the query in the function runs less than 2% of report. In preview
> mode in VS.net, the report works correctly. When I use Report Manager to
> upload the report, when the query runs, it places #Error in the report field.
> This is the begining of the function:
> Public Function SKU(rootSerial as String, _
> currentSKU as Guid)as String
> Dim currentSKUStr as String
> currentSKUStr=currentSKU.ToString()
> If rootSerial = "" Then
> Else
> Dim arset As Guid
> Dim oConn as New System.Data.SqlClient.SqlConnection
> oConn.ConnectionString = "Data Source=PPNS1;Initial
> Catalog=Persyst_Development_MSCRM;Integrated Security=SSPI;"
> oConn.Open()
> Dim oCmd as New System.Data.SqlClient.SqlCommand
> oCmd.Connection = oConn
> oCmd.CommandText = "SELECT New_ProductId FROM
> New_PersystSerialNumberExtensionBase WHERE (New_SerialNumber = N'4320401')"
> arset = oCmd.ExecuteScalar()
> currentSKUStr = arset.ToString()
> oConn.Close()
> End IF
>
> There is additional code after End If, but that code is run for each data
> row. I also have verified in the IDE via msgbox, that the correct value is
> being returned from the query.
> 1. Is it possible to deploy a function that contains its own query as I have
> done?
> 2. What could be the cause of it working in the IDE but not when deployed?
> TIA, Jim|||Update 2:
I solved the problem and will post again after I have refined it. I am not
happy at this time with the solution as I have set the default permission
behavior from "Nothing" to "FullTrust". This is OK for my use as the web
server is private so I am less concerned about malicious code. However, I
would like to have it working with tighter permissions
"JHPArizona" wrote:
> Update:
> I have traced the problem to:
> Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
> How do I set security for embeded code?
> "JHPArizona" wrote:
> > I have created a function that queries the database for a value if conditions
> > are met. IE the query in the function runs less than 2% of report. In preview
> > mode in VS.net, the report works correctly. When I use Report Manager to
> > upload the report, when the query runs, it places #Error in the report field.
> >
> > This is the begining of the function:
> >
> > Public Function SKU(rootSerial as String, _
> > currentSKU as Guid)as String
> > Dim currentSKUStr as String
> > currentSKUStr=currentSKU.ToString()
> > If rootSerial = "" Then
> > Else
> > Dim arset As Guid
> > Dim oConn as New System.Data.SqlClient.SqlConnection
> > oConn.ConnectionString = "Data Source=PPNS1;Initial
> > Catalog=Persyst_Development_MSCRM;Integrated Security=SSPI;"
> > oConn.Open()
> >
> > Dim oCmd as New System.Data.SqlClient.SqlCommand
> > oCmd.Connection = oConn
> > oCmd.CommandText = "SELECT New_ProductId FROM
> > New_PersystSerialNumberExtensionBase WHERE (New_SerialNumber = N'4320401')"
> > arset = oCmd.ExecuteScalar()
> > currentSKUStr = arset.ToString()
> > oConn.Close()
> > End IF
> >
> >
> > There is additional code after End If, but that code is run for each data
> > row. I also have verified in the IDE via msgbox, that the correct value is
> > being returned from the query.
> >
> > 1. Is it possible to deploy a function that contains its own query as I have
> > done?
> > 2. What could be the cause of it working in the IDE but not when deployed?
> >
> > TIA, Jim
are met. IE the query in the function runs less than 2% of report. In preview
mode in VS.net, the report works correctly. When I use Report Manager to
upload the report, when the query runs, it places #Error in the report field.
This is the begining of the function:
Public Function SKU(rootSerial as String, _
currentSKU as Guid)as String
Dim currentSKUStr as String
currentSKUStr=currentSKU.ToString()
If rootSerial = "" Then
Else
Dim arset As Guid
Dim oConn as New System.Data.SqlClient.SqlConnection
oConn.ConnectionString = "Data Source=PPNS1;Initial
Catalog=Persyst_Development_MSCRM;Integrated Security=SSPI;"
oConn.Open()
Dim oCmd as New System.Data.SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandText = "SELECT New_ProductId FROM
New_PersystSerialNumberExtensionBase WHERE (New_SerialNumber = N'4320401')"
arset = oCmd.ExecuteScalar()
currentSKUStr = arset.ToString()
oConn.Close()
End IF
There is additional code after End If, but that code is run for each data
row. I also have verified in the IDE via msgbox, that the correct value is
being returned from the query.
1. Is it possible to deploy a function that contains its own query as I have
done?
2. What could be the cause of it working in the IDE but not when deployed?
TIA, JimUpdate:
I have traced the problem to:
Request for the permission of type
System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
How do I set security for embeded code?
"JHPArizona" wrote:
> I have created a function that queries the database for a value if conditions
> are met. IE the query in the function runs less than 2% of report. In preview
> mode in VS.net, the report works correctly. When I use Report Manager to
> upload the report, when the query runs, it places #Error in the report field.
> This is the begining of the function:
> Public Function SKU(rootSerial as String, _
> currentSKU as Guid)as String
> Dim currentSKUStr as String
> currentSKUStr=currentSKU.ToString()
> If rootSerial = "" Then
> Else
> Dim arset As Guid
> Dim oConn as New System.Data.SqlClient.SqlConnection
> oConn.ConnectionString = "Data Source=PPNS1;Initial
> Catalog=Persyst_Development_MSCRM;Integrated Security=SSPI;"
> oConn.Open()
> Dim oCmd as New System.Data.SqlClient.SqlCommand
> oCmd.Connection = oConn
> oCmd.CommandText = "SELECT New_ProductId FROM
> New_PersystSerialNumberExtensionBase WHERE (New_SerialNumber = N'4320401')"
> arset = oCmd.ExecuteScalar()
> currentSKUStr = arset.ToString()
> oConn.Close()
> End IF
>
> There is additional code after End If, but that code is run for each data
> row. I also have verified in the IDE via msgbox, that the correct value is
> being returned from the query.
> 1. Is it possible to deploy a function that contains its own query as I have
> done?
> 2. What could be the cause of it working in the IDE but not when deployed?
> TIA, Jim|||Update 2:
I solved the problem and will post again after I have refined it. I am not
happy at this time with the solution as I have set the default permission
behavior from "Nothing" to "FullTrust". This is OK for my use as the web
server is private so I am less concerned about malicious code. However, I
would like to have it working with tighter permissions
"JHPArizona" wrote:
> Update:
> I have traced the problem to:
> Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
> How do I set security for embeded code?
> "JHPArizona" wrote:
> > I have created a function that queries the database for a value if conditions
> > are met. IE the query in the function runs less than 2% of report. In preview
> > mode in VS.net, the report works correctly. When I use Report Manager to
> > upload the report, when the query runs, it places #Error in the report field.
> >
> > This is the begining of the function:
> >
> > Public Function SKU(rootSerial as String, _
> > currentSKU as Guid)as String
> > Dim currentSKUStr as String
> > currentSKUStr=currentSKU.ToString()
> > If rootSerial = "" Then
> > Else
> > Dim arset As Guid
> > Dim oConn as New System.Data.SqlClient.SqlConnection
> > oConn.ConnectionString = "Data Source=PPNS1;Initial
> > Catalog=Persyst_Development_MSCRM;Integrated Security=SSPI;"
> > oConn.Open()
> >
> > Dim oCmd as New System.Data.SqlClient.SqlCommand
> > oCmd.Connection = oConn
> > oCmd.CommandText = "SELECT New_ProductId FROM
> > New_PersystSerialNumberExtensionBase WHERE (New_SerialNumber = N'4320401')"
> > arset = oCmd.ExecuteScalar()
> > currentSKUStr = arset.ToString()
> > oConn.Close()
> > End IF
> >
> >
> > There is additional code after End If, but that code is run for each data
> > row. I also have verified in the IDE via msgbox, that the correct value is
> > being returned from the query.
> >
> > 1. Is it possible to deploy a function that contains its own query as I have
> > done?
> > 2. What could be the cause of it working in the IDE but not when deployed?
> >
> > TIA, Jim
Subscribe to:
Posts (Atom)