Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Removing cursor rom report procedure Expand / Collapse
Author
Message
Posted Tuesday, September 17, 2013 11:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 5:22 AM
Points: 300, Visits: 1,070
Hi,

IS here any way hat I can get rid of the cursor from the procedure given below ? When I run this procedure in SSMS it retuns result in less tan 2 secs . The report locally takes 10 secs . However when I deploy the report into the report sever I get he error
"Query execution failed for dataset 'dsTestReport'. (rsErrorExecutingCommand)"

Code given below


ALTER PROCEDURE [dbo].[CheckReport]

@pStartDate varchar(20) , -- Change this
@pEndDate varchar(20)-- , -- Change only if you want less than today.
-- @pPostingCompany varchar(50) -- Change this to required posting company

WITH RECOMPILE

AS


BEGIN


Declare @StartDate varchar(20) = @pStartDate -- Change this
Declare @EndDate varchar(20) = @pEndDate -- Change only if you want less than today.
declare @PostingCompany varchar(50) --= 'TGS-UK' -- Change this to required posting company

---------
declare @sql varchar(max)
declare @LocalDatabase varchar(30)
declare @LocalCompany varchar(50)
declare @WSAddress varchar(500)

CREATE TABLE #TEst ( ID INT Identity(1,1),PostingCompany VARCHAR(100),G_LAccountNo VARCHAR(40),GLAccountName VARCHAR(500), Docno VARCHAR(200), Amount FLOAT , LC_Amount FLOAT )


DECLARE cursor1 CURSOR FOR

select Code,[Trans_ Dist_ WS Address] from [dbo].[Master Company$IC Partner]

SET NOCOUNT ON
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @PostingCompany, @WSAddress

WHILE @@FETCH_STATUS = 0
BEGIN

--WS Address values looks like below. Parse this to get database and company name
--http://localhost:7049/TGS_NO/WS/TGS%20Nopec%20ASA/Codeunit/Integration_Receive_Trans_Distrib

set @WSAddress = substring(@WSAddress, charindex('/TGS',@WSAddress)+1, 1000)
set @LocalDatabase = substring(@WSAddress, 1, charindex('/',@WSAddress)-1)
set @WSAddress = substring(@WSAddress, charindex('/',@WSAddress)+1, 1000)
set @WSAddress = substring(@WSAddress, charindex('/',@WSAddress)+1, 1000)
set @LocalCompany = replace(substring(@WSAddress, 1, charindex('/',@WSAddress)-1), '%20', ' ')


-- contract query and execute
--where [Posting Company] = ''' + @pPostingCompany + '''' +
set @sql = ' INSERT INTO #TEst ( PostingCompany ,G_LAccountNo ,GLAccountName , Docno , Amount , LC_Amount )
select MasterCompany.*, LocalCompany.Amount AS [LC Amount]
from (
select GLE.[Posting Company],GLE.[G_L Account No_], GLA.Name AS [GL Account Name], [Document No_], sum(Amount) Amount
from [dbo].[Master Company$G_L Entry] GLE
LEFT JOIN [dbo].[Master Company$G_L Account] GLA ON GLE.[G_L Account No_] = GLA.[No_]
where [Posting Company] = ''' + @PostingCompany + '''' +
' AND [Posting Date] between ''' + @pStartDate + ''' and ''' + @pEndDate + '''' +
' group by [Posting Company],[G_L Account No_], GLA.Name, [Document No_]
) MasterCompany
LEFT JOIN (
select [G_L Account No_], [Document No_], sum(Amount) Amount
from ' + @LocalDatabase + '.dbo.[' + @LocalCompany + '$G_L Entry]
where [Posting Date] between ''' + @pStartDate + ''' and ''' + @pEndDate + '''' +
' group by [G_L Account No_], [Document No_]
) LocalCompany ON MasterCompany.[G_L Account No_] = LocalCompany.[G_L Account No_] AND
MasterCompany.[Document No_] = LocalCompany.[Document No_]
where (LocalCompany.[G_L Account No_] is null OR MasterCompany.Amount <> LocalCompany.Amount)
'

--print @sql

exec (@sql)


FETCH NEXT FROM cursor1 INTO @PostingCompany, @WSAddress
END

CLOSE cursor1
DEALLOCATE cursor1
SET NOCOUNT OFF



SELECT PostingCompany ,G_LAccountNo ,GLAccountName , Docno , ISNULL(Amount,0) As Amount, ISNULL(LC_Amount,0) AS LC_Amount FROM #TEst Order By PostingCompany
DROP TABLE #TEst


END
Post #1495609
Posted Tuesday, September 17, 2013 11:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:56 PM
Points: 13,078, Visits: 12,529
PSB (9/17/2013)

IS here any way hat I can get rid of the cursor from the procedure given below ?


Yes.

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1495617
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse