December 30, 2008 at 1:18 am
Dear All,
I have one issue with my stored proc,
My sp is having dynamic sql like at the end of the sp i execute
exec(@sql)
but size of my @sql is more then 8200 bytes so not able to execute it it is giving error , i cant use text datatype in sp then what to do i dnt know. I have one solution of breaking this string into 2 varibales and executing botht the variables but before that i want to try with one variable onyl..
so now can u suggest the way out for me?
thanks
mithun
December 30, 2008 at 1:59 am
You can use VARCHAR(MAX) - but I have to ask why you are using dynamic SQL in the first place, and especially a dynamic SQL string that exceeds 8000 characters.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2008 at 2:22 am
sir, thanks for the reply
its a long sp of balance sheet and earlier it is using dynamic sql now we need to do some modification and needs to add two line to the exising
@sql dynamic sql....
and i have put that @sql st the end of the sp..
i tried with varchar(max) but giving the sma eerror.....
pls if any other solution is there?
thanks
mithun
December 30, 2008 at 2:31 am
mithun.gite (12/30/2008)
sir, thanks for the replyits a long sp of balance sheet and earlier it is using dynamic sql now we need to do some modification and needs to add two line to the exising
@sql dynamic sql....
and i have put that @sql st the end of the sp..
i tried with varchar(max) but giving the sma eerror.....
pls if any other solution is there?
thanks
mithun
What is the error?
Failing to plan is Planning to fail
December 30, 2008 at 9:58 pm
hi,
The Error comes bcoz , see
my @STR varibale is having more then 8000 bytes characters ok,
now when i executes it excutes upto 8000 bytes only so it is somewhere in the middle bcoz my query is upto 9000 bytes.
so it gives error like incorrect syntax near where , somethign like that bocz my 8000 bytes are getting over near where.
i hope i m able to explain u
thanks
December 30, 2008 at 10:23 pm
Can u tell us the specific error that you are getting. Did you try declaring the variable as Varchar(MAX). Incorrect syntax does not always mean variable size issue, it could be something to do the query.
Print the @STR variable and see whether the query is written correctly.
"Keep Trying"
December 30, 2008 at 10:38 pm
mithun.gite (12/30/2008)
Dear All,I have one issue with my stored proc,
My sp is having dynamic sql like at the end of the sp i execute
exec(@sql)
but size of my @sql is more then 8200 bytes so not able to execute it it is giving error , i cant use text datatype in sp then what to do i dnt know. I have one solution of breaking this string into 2 varibales and executing botht the variables but before that i want to try with one variable onyl..
so now can u suggest the way out for me?
thanks
mithun
optimize the dynamic query inside the variable
like
store the scalar values outseide the dynamic query and use those variables in the dyamic query
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
December 30, 2008 at 10:53 pm
Below is the exact error i m getting , here i m printing my dynamic SQL.
and i have calculated its lenght its more then 8000 bytes. and with this
i m attacing the sp also....
SELECT * FROM (
SELECT UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,TRANS_ID,SNO,POSTING_DATE,DOCUMENT_TYPE,DOCUMENT_NO,DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,DR_AMT,CR_AMT,BALANCE,ACT_DR_AMT,ACT_CR_AMT,INST_NO,INST_DATE,NARRATION,REMARKS,CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,TRANS_TYPE,HEADER_AC_ID,HEADER_AC_CODE,HEADER_AC_NAME,POPULATED_DATE FROM #FINANCEDATA200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE Between '2007-11-24' and '2008-01-30'
UNION ALL
SELECT 'OpenBal' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'OPENTRANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'OpenBal'DOCUMENT_TYPE,'OpenBal'DOCUMENT_NO,NULL DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,SUM(DR_AMT),SUM(CR_AMT),SUM(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM #FINANCEDATA200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE<'2007-11-24' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,GROUP_CODE,GROUP_NAME,ACCOUNT_TYPE,TRANS_TYPE,POPULATED_DATE
UNION ALL
SELECT UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,TRANS_ID,SNO,POSTING_DATE,DOCUMENT_TYPE,DOCUMENT_NO,DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,DR_AMT,CR_AMT,BALANCE,ACT_DR_AMT,ACT_CR_AMT,INST_NO,INST_DATE,NARRATION,REMARKS,CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,TRANS_TYPE,HEADER_AC_ID,HEADER_AC_CODE,HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE Between '2007-11-24' and '2008-01-30'
UNION ALL
SELECT 'OpenBal' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'OPENTRANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'OpenBal'DOCUMENT_TYPE,'OpenBal'DOCUMENT_NO,NULL DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,SUM(DR_AMT),SUM(CR_AMT),SUM(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE<'2007-11-24' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,GROUP_CODE,GROUP_NAME,ACCOUNT_TYPE,TRANS_TYPE,POPULATED_DATE
UNION ALL
SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P<RANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'P&L'DOCUMENT_TYPE,'P&L'DOCUMENT_NO,NULL DOCUMENT_DATE,'P&L'ACCOUNT_ID,'P&L'ACCOUNT_CODE,'Profit and Loss'ACCOUNT_NAME,CASE WHEN SUM(BALANCE)>0 THEN ABS(SUM(BALANCE)) ELSE 0 END DR_AMT,CASE WHEN SUM(BALANCE)>0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT,Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,1 TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Expense','Income') AND POSTING_DATE<'2007-11-24' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,ACCOUNT_ID,ACCOUNT_CODE,ACCOUNT_NAME,POPULATED_DATE
UNION ALL
SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P<RANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'P&L'DOCUMENT_TYPE,'P&L'DOCUMENT_NO,NULL DOCUMENT_DATE,'P&L'ACCOUNT_ID,'P&L'ACCOUNT_CODE,'Profit and Loss'ACCOUNT_NAME,CASE WHEN SUM(BALANCE)>0 THEN ABS(SUM(BALANCE)) ELSE 0 END DR_AMT,CASE WHEN SUM(BALANCE)>0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT, Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,1 TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Expense','Income') AND POSTING_DATE Between '2007-11-24' and '2008-01-30' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,ACCOUNT_ID,ACCOUNT_CODE,ACCOUNT_NAME,POPULATED_DATE
UNION ALL
SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P<RANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'P&L'DOCUMENT_TYPE,'P&L'DOCUMENT_NO,NULL DOCUMENT_DATE,'P&L'ACCOUNT_ID,'P&L'ACCOUNT_CODE,'Profit and Loss'ACCOUNT_NAME,CASE WHEN SUM(BALANCE)>0 THEN ABS(SUM(BALANCE)) ELSE 0 END DR_AMT,CASE WHEN SUM(BALANCE)>0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT,Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,1 TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM #FINANCEDATA200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Expense','Income') AND POSTING_DATE Between '2007-11-24' and '2008-01-30' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,ACCOUNT_ID,ACCOUNT_CODE,ACCOUNT_NAME,POPULATED_DATE
UNION ALL
SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P<RANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'P&L'DOCUMENT_TYPE,'P&L'DOCUMENT_NO,NULL DOCUMENT_DATE,'P&L'ACCOUNT_ID,'P&L'ACCOUNT_CODE,'Profit and Loss'ACCOUNT_NAME,CASE WHEN SUM(BALANCE)>0 THEN ABS(SUM(BALANCE)) ELSE 0 END DR_AMT,CASE WHEN SUM(BALANCE)>0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT,Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACC
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near 'ACC'.[/b]
December 30, 2008 at 11:28 pm
hi, I dont know the permenant or a good solution. I think you can split the sql into two vars. and try to execute them like.
declare @sql varchar(max)
set @sql='somequery'
declare @sql1 varchar(max)
set @sql1='somequery'
exec(@sql+' union '+ @sql1)
Hope its useful.
December 31, 2008 at 3:11 am
hi,
as i have said in my first post i have already used two paramateters and its working fine with it , but i want to knwo if any good solution is there , but thatnks for this sollution, thanks
January 1, 2009 at 1:53 am
Check if this works...
Declare @sql VARCHAR(MAX)
SET @sql = 'SELECT * FROM (
SELECT UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,TRANS_ID,SNO,POSTING_DATE,DOCUMENT_TYPE,DOCUMENT_NO,DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,DR_AMT,CR_AMT,BALANCE,ACT_DR_AMT,ACT_CR_AMT,INST_NO,INST_DATE,NARRATION,REMARKS,CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,TRANS_TYPE,HEADER_AC_ID,HEADER_AC_CODE,HEADER_AC_NAME,POPULATED_DATE FROM #FINANCEDATA200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE Between '2007-11-24' and '2008-01-30'
UNION ALL
SELECT 'OpenBal' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'OPENTRANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'OpenBal'DOCUMENT_TYPE,'OpenBal'DOCUMENT_NO,NULL DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,SUM(DR_AMT),SUM(CR_AMT),SUM(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM #FINANCEDATA200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE<'2007-11-24' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,GROUP_CODE,GROUP_NAME,ACCOUNT_TYPE,TRANS_TYPE,POPULATED_DATE
UNION ALL
SELECT UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,TRANS_ID,SNO,POSTING_DATE,DOCUMENT_TYPE,DOCUMENT_NO,DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,DR_AMT,CR_AMT,BALANCE,ACT_DR_AMT,ACT_CR_AMT,INST_NO,INST_DATE,NARRATION,REMARKS,CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,TRANS_TYPE,HEADER_AC_ID,HEADER_AC_CODE,HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE Between '2007-11-24' and '2008-01-30'
UNION ALL
SELECT 'OpenBal' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'OPENTRANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'OpenBal'DOCUMENT_TYPE,'OpenBal'DOCUMENT_NO,NULL DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,SUM(DR_AMT),SUM(CR_AMT),SUM(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE<'2007-11-24' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,GROUP_CODE,GROUP_NAME,ACCOUNT_TYPE,TRANS_TYPE,POPULATED_DATE
UNION ALL
SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P 0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT,Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,1 TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Expense','Income') AND POSTING_DATE<'2007-11-24' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,ACCOUNT_ID,ACCOUNT_CODE,ACCOUNT_NAME,POPULATED_DATE
UNION ALL
SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P 0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT, Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,1 TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Expense','Income') AND POSTING_DATE Between '2007-11-24' and '2008-01-30' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,ACCOUNT_ID,ACCOUNT_CODE,ACCOUNT_NAME,POPULATED_DATE
UNION ALL
SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P 0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT,Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,1 TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM #FINANCEDATA200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Expense','Income') AND POSTING_DATE Between '2007-11-24' and '2008-01-30' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,ACCOUNT_ID,ACCOUNT_CODE,ACCOUNT_NAME,POPULATED_DATE
UNION ALL
SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P 0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT,Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACC'
Execute (@sql)
"Keep Trying"
January 1, 2009 at 1:48 pm
EXEC (@SQL1+@SQL2+@SQL3...+@SQLn)
--Jeff Moden
Change is inevitable... Change for the better is not.
January 1, 2009 at 9:16 pm
Dear All,
Its Really Nice of u guysssssss for replying me some fruitful solutions...
First i tried with Varchar(max) but giving same problem as it can store upto 8060 bytes and finally i had opted for exec(@sql1+@sql2+@sql3) and its working very fine so .......
Thanks for all ur time and knowledge u givne me thanksssss again to all(Jeff Moden,Chirag,Jeffrey Williams,Madhivanan,krayknot ,yogesh.balasubramanian
)......
And Wish U all a very Happy New Year !!!!!!!
Take Care
Mithun
January 2, 2009 at 2:15 pm
I suspect that database is running in Comptibility 80 mode, because I've run scripts substantially larger than 8K in a nvarchar(max) variable with NO issues (130K was the biggest one I can recall).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply