September 17, 2013 at 11:46 am
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
September 17, 2013 at 11:54 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply