August 19, 2009 at 11:55 pm
Dear Frnds
I change collation in my database.
My application is developed in .net framework 1.1 and using .net crystal report
Now few report is not running
problem is that in only one charector is printing in report.
like is procedure return 'Hello' but report print only 'H'
Please help
Thanks
KD
August 20, 2009 at 4:31 am
What was your default collation?
To which collation you changed?
August 20, 2009 at 5:11 am
Old Collation was---SQL_Latin1_General_CP1_CI_AS
and new of MY DB and Server is--Latin1_General_CI_AI
I notice something meanwhile describe with ex. as below
Suppose I had a proc attached with a report
Create Proc ProcName
@PAr1 varchar(4)
@PAr2 varchar(4)
@PAr3 varchar(4)
as
if @Par1=''
begin
select '' Par1,''Par2,''Par3
return
end
Craete #Temp
(
PAr1 varchar(4)
PAr2 varchar(4)
PAr3 varchar(4)
)
Now some cursor runs and Data insert into #Temp
Select * from #Temp
---Till Now report was printing only one chartor of each return dataset
I change proc as below and it working fine
Create Proc ProcName
@PAr1 varchar(4)
@PAr2 varchar(4)
@PAr3 varchar(4)
as
if @Par1=''
begin
select ' ' Par1,' 'Par2,' 'Par3----Notice paas blank space as per rowset column
return
end
Craete #Temp
(
PAr1 varchar(4)
PAr2 varchar(4)
PAr3 varchar(4)
)
Now some cursor runs and Data insert into #Temp
Select * from #Temp
August 20, 2009 at 11:50 pm
waitinf for reply
August 21, 2009 at 6:18 am
Please post table definitions, sample data and desired output, also the full definition of the proc and the parameters that you're calling it with. Read this to see the best way to post this to get quick responses.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2009 at 6:30 am
This is My Proc It execute correctly and return some data but report just pick one charcter of each char and varchar datatype
-- MMS_R_PY_Payroll_Summ_CS_Rep_1 'Y','01','','','R016','R016','0461','0461','CA','','SL','22/11/2008','21/12/2008'
Create PROCEDURE MMS_R_PY_Payroll_Summ_CS_Rep_1(
@a_c_execute CHAR(1),
@a_c_from_shed CHAR(2),
@a_c_from_area CHAR(4),
@a_c_to_area CHAR(4),
@a_c_from_sub_area CHAR(6),
@a_c_to_sub_area CHAR(6),
@a_c_from_cs_code CHAR(6),
@a_c_to_cs_code CHAR(6),
@a_c_milk_src_type CHAR(3),
@a_c_pay_mode CHAR(2),
@a_c_pay_type CHAR(2),
@a_d_from_date VARCHAR(10),
@a_d_end_date VARCHAR(10))
AS
IF @a_c_from_shed = ''
BEGIN
SELECT 0 AS sl_no,
'' AS shed_code,
'' AS area_code,
'' AS area_name,
'' AS sub_area_code,
'' AS sub_area_name,
'' AS loc_code,
'' AS loc_name,
'' AS cs_code,
'' AS cs_name ,
CONVERT(DATETIME, NULL) AS from_date,
CONVERT(DATETIME, NULL) AS end_date,
0.0 AS earn_amt,
0.0 AS ded_amt,
0.0 AS net_amt,
0.0 AS tot_qty,
'' AS calc_type,
'' AS cssumm_print_flg,
'' AS anml_type,
'' AS qlty_type,
'' AS qlty_msr_code,
0.00 AS qty_dlvrd,
0.00 AS pay_qlty,
0.0 AS in_kg,
'' AS narration,
'' AS anml_type_2,
'' AS grp_desc,
0.0 AS pay_amt,
'' AS milk_src_type_desc,
'' AS pay_type_desc,
'' AS CNTRY_CODE
RETURN
END
DECLARE
@FETCH_STATUS_3 Integer,
@FETCH_STATUS_2 Integer,
@FETCH_STATUS_1 Integer,
@ROWCOUNT Integer,
@l_i_cntr INTEGER,
@l_c_shed_code CHAR(2),
@l_c_area_code CHAR(2),
@l_c_sub_area_code CHAR(4),
@l_c_loc_code CHAR(4),
@l_d_from_date DATETIME,
@l_d_end_date DATETIME,
@l_d_sp_from_date DATETIME,
@l_d_sp_end_date DATETIME,
@l_c_cs_code CHAR(6),
@l_i_earn_amt NUMERIC(16,5),
@l_i_ded_amt NUMERIC(16,5),
@l_i_net_amt NUMERIC(16,5),
@l_i_tot_qty NUMERIC(16,5),
@l_c_calc_type VARCHAR(20),
@l_c_cssumm_print_flg VARCHAR(20),
@l_c_anml_type CHAR(3),
@l_c_qlty_type CHAR(3),
@l_c_qlty_msr_code CHAR(3),
@l_i_pay_qlty NUMERIC(16,5),
@l_i_in_kg NUMERIC(16,5),
@l_i_qty_dlvrd NUMERIC(16,5),
@l_c_narration VARCHAR(20),
@l_c_anml_type_2 CHAR(3),
@l_c_grp_desc VARCHAR(30),
@l_i_pay_amt NUMERIC(16,5),
@a_c_pay_type_desc VARCHAR(30),
@l_c_shed_code_temp CHAR(2),
@l_c_loc_code_temp CHAR(4),
@l_c_cs_code_temp CHAR(6),
@l_d_from_date_temp DATETIME,
@l_d_end_date_temp DATETIME,
@l_i_tot_qty_temp NUMERIC(16,5),
@FETCH_STATUS_4 Integer,
@CNTRY_CODE varCHAR(100)
BEGIN -- 0
-- If pay_mode is blank then set it NULL
if @a_c_pay_mode = '' set @a_c_pay_mode = NULL
-- START OF INITIALIZING WITH DEFAULT VALUES
SET @a_c_from_area = DBO.MMS_F_Common_DefaultValues(@a_c_from_area, 'Area_Code', 'F')
SET @a_c_to_area = DBO.MMS_F_Common_DefaultValues(@a_c_to_area, 'Area_Code', 'T')
SET @a_c_from_sub_area = DBO.MMS_F_Common_DefaultValues(@a_c_from_sub_area, 'Sub_Area_Code', 'F')
SET @a_c_to_sub_area = DBO.MMS_F_Common_DefaultValues(@a_c_to_sub_area, 'Sub_Area_Code', 'T')
SET @a_c_from_cs_code = DBO.MMS_F_Common_DefaultValues(@a_c_from_cs_code, 'Cs_Code', 'F')
SET @a_c_to_cs_code = DBO.MMS_F_Common_DefaultValues(@a_c_to_cs_code, 'Cs_Code', 'T')
-- END OF INITIALIZING WITH DEFAULT VALUES
SET @l_d_from_date = CONVERT(DATETIME, @a_d_from_date, 103)
SET @l_d_end_date = CONVERT(DATETIME, @a_d_end_date, 103)
CREATE TABLE #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1(
sl_no INTEGER,
shed_code CHAR(2),
area_code CHAR(2),
sub_area_code CHAR(4),
loc_code CHAR(4),
-- from_date VARCHAR(11),
-- end_date VARCHAR(11),
from_date DATETIME,
end_date DATETIME,
cs_code CHAR(6),
earn_amt NUMERIC(16,5),
ded_amt NUMERIC(16,5),
net_amt NUMERIC(16,5),
tot_qty NUMERIC(16,5),
calc_type VARCHAR(20),
cssumm_print_flg VARCHAR(20),
anml_type CHAR(3),
qlty_type CHAR(3),
qlty_msr_code CHAR(3),
qty_dlvrd NUMERIC(16,5),
pay_qlty NUMERIC(16,5),
in_kg NUMERIC(16,5),
narration VARCHAR(20),
anml_type_2 CHAR(3),
grp_desc VARCHAR(30),
pay_amt NUMERIC(16,5))
CREATE TABLE #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1_1(
shed_code CHAR(2),
sub_area_code CHAR(4),
anml_type_3 CHAR(4),
gross_amt NUMERIC(16,5),
tot_qty_3 NUMERIC(16,5))
CREATE TABLE #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1_2(
shed_code CHAR(2),
sub_area_code CHAR(4),
ded_code CHAR(4),
tot_ded_amt NUMERIC(16,5))
IF @a_c_execute='Y'
BEGIN
DECLARE CUR1 CURSOR FOR
SELECT A.shed_code, C.area_code, B.sub_area_code, A.loc_code, A.from_date,
A.end_date, A.cs_code, A.earn_amt, A.ded_amt, A.net_amt, A.tot_qty,
CASE A.calc_type When 'P' Then 'PROVISIONAL' When 'I' THEN 'ITERATIVE' When 'F' Then 'FINAL' END AS calc_type,
CASE A.cssumm_print_flg When 'P' Then ' - DUPLICATE' Else NULL END AS cssumm_print_flg
FROM MMS_CS_PAY_HDR A, MMS_COLL_SRC B, MMS_SUB_AREA C
WHERE A.shed_code = B.shed_code
AND A.cs_code = B.cs_code
AND B.shed_code = C.shed_code
AND B.sub_area_code = C.sub_area_code
AND A.shed_code = @a_c_from_shed
AND C.area_code BETWEEN @a_c_from_area AND @a_c_to_area
AND B.sub_area_code BETWEEN @a_c_from_sub_area AND @a_c_to_sub_area
AND A.cs_code BETWEEN @a_c_from_cs_code AND @a_c_to_cs_code
AND B.pay_mode = ISNULL(@a_c_pay_mode, B.pay_mode)
/* changed by kaushal on 21052004 for pakistan rqmnt of handling DO,PF,SA etc */
/* Milk src type is selected on AND and OR basis considering if the value is returned as LF,PF,DO etc (CS pyrll smry report)
then AND will be activated and if milk src type is 03 (LF payrl Smry report) then OR will be activated for . */
AND ( B.milk_src_type = @a_c_milk_src_type --- original AND
OR B.milk_src_type = ( select milk_src_type --- Added OR for pakistan
from mms_milk_src_type
where coll_pt_flg = @a_c_milk_src_type
and milk_src_type = (select milk_src_type
from mms_coll_src mcs
where mcs.cs_code = b.cs_code
and mcs.milk_src_type = (select milk_src_type from mms_coll_src where cs_code = mcs.cs_code)
)
)
)
/* change ends */
-- AND A.from_date = @l_d_from_date
-- AND A.end_date = @l_d_end_date
AND CONVERT(DATETIME, A.from_date, 103) = CONVERT(DATETIME, @l_d_from_date, 103)
AND CONVERT(DATETIME, A.end_date, 103) = CONVERT(DATETIME, @l_d_end_date, 103)
ORDER BY A.shed_code, C.area_code, B.sub_area_code, A.loc_code, A.cs_code
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @l_c_shed_code, @l_c_area_code, @l_c_sub_area_code, @l_c_loc_code,
@l_d_sp_from_date, @l_d_sp_end_date, @l_c_cs_code, @l_i_earn_amt, @l_i_ded_amt,
@l_i_net_amt, @l_i_tot_qty, @l_c_calc_type, @l_c_cssumm_print_flg
SET @FETCH_STATUS_1 = @@FETCH_STATUS
--PRINT @FETCH_STATUS_1
WHILE @FETCH_STATUS_1 = 0
BEGIN
/*Added the code for fixing the cost per kg and 27/11/2003 Ankur*/
set @l_i_tot_qty = 0
DECLARE CUR4 CURSOR FOR
SELECT distinct a.shed_code,a.loc_code,a.cs_code,a.from_date,a.end_date,
--,A.tot_qty AS qty_dlvrd -- Commented by Jatinder Singh Virk
-- Addition Sterted by Jatinder Singh Virk
CASE A.anml_type WHEN 'C' THEN A.tot_qty
ELSE
CASE A.qlty_type WHEN 'SNF' THEN 0
ELSE A.tot_qty END
END AS qty_dlvrd
-- Addition Ended
FROM MMS_CS_PAY_QLTY A
WHERE A.shed_code = @l_c_shed_code
AND A.loc_code = @l_c_loc_code
AND A.cs_code = @l_c_cs_code
AND CONVERT(DATETIME, A.from_date, 103) = CONVERT(DATETIME, @l_d_from_date, 103)
AND CONVERT(DATETIME, A.end_date, 103) = CONVERT(DATETIME, @l_d_end_date, 103)
OPEN CUR4
FETCH NEXT FROM CUR4 INTO @l_c_shed_code_temp,@l_c_loc_code_temp,@l_c_cs_code_temp,@l_d_from_date_temp,@l_d_end_date_temp,@l_i_tot_qty_temp
SET @FETCH_STATUS_4 = @@FETCH_STATUS
WHILE @FETCH_STATUS_4 = 0
BEGIN
set @l_i_tot_qty = @l_i_tot_qty + @l_i_tot_qty_temp
FETCH NEXT FROM CUR4 INTO @l_c_shed_code_temp,@l_c_loc_code_temp,@l_c_cs_code_temp,@l_d_from_date_temp,@l_d_end_date_temp,@l_i_tot_qty_temp
SET @FETCH_STATUS_4 = @@FETCH_STATUS
END
CLOSE CUR4
DEALLOCATE CUR4
/*Added the code for fixing the cost per kg and */
SET @l_i_cntr = 0
DECLARE CUR2 CURSOR FOR
SELECT A.anml_type, A.qlty_type, A.qlty_msr_code, A.pay_qlty,
CASE A.qlty_msr_code When '%' Then ((A.tot_qty * ROUND(A.pay_qlty, 2))/100) END AS in_kg,
-- A.tot_qty AS qty_dlvrd Commented By Jatinder SIngh Virk
CASE A.anml_type WHEN 'C' THEN A.tot_qty
ELSE
CASE A.qlty_type WHEN 'SNF' THEN Null
ELSE A.tot_qty END
END AS qty_dlvrd
FROM MMS_CS_PAY_QLTY A
WHERE A.shed_code = @l_c_shed_code
AND A.loc_code = @l_c_loc_code
AND A.cs_code = @l_c_cs_code
-- AND A.from_date = @l_d_from_date
-- AND A.end_date = @l_d_end_date
AND CONVERT(DATETIME, A.from_date, 103) = CONVERT(DATETIME, @l_d_from_date, 103)
AND CONVERT(DATETIME, A.end_date, 103) = CONVERT(DATETIME, @l_d_end_date, 103)
--AND NOT (A.ANML_TYPE = 'B' and A.QLTY_TYPE = 'SNF') --Added by Jatinder Singh Virk
OPEN CUR2
FETCH NEXT FROM CUR2 INTO @l_c_anml_type, @l_c_qlty_type, @l_c_qlty_msr_code,
@l_i_pay_qlty, @l_i_in_kg, @l_i_qty_dlvrd
SET @FETCH_STATUS_2 = @@FETCH_STATUS
WHILE @FETCH_STATUS_2 = 0
BEGIN
SET @l_i_cntr = @l_i_cntr + 1
INSERT INTO #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1
(sl_no, shed_code, area_code, sub_area_code, loc_code,
from_date, end_date, cs_code, earn_amt, ded_amt, net_amt,
tot_qty, calc_type, cssumm_print_flg, anml_type, qlty_type,
qlty_msr_code, pay_qlty, in_kg, qty_dlvrd)
VALUES
(@l_i_cntr, @l_c_shed_code, @l_c_area_code, @l_c_sub_area_code, @l_c_loc_code,
@l_d_from_date, @l_d_end_date, @l_c_cs_code, @l_i_earn_amt, @l_i_ded_amt, @l_i_net_amt,
@l_i_tot_qty, @l_c_calc_type, @l_c_cssumm_print_flg, @l_c_anml_type, @l_c_qlty_type,
@l_c_qlty_msr_code, @l_i_pay_qlty, @l_i_in_kg, @l_i_qty_dlvrd)
FETCH NEXT FROM CUR2 INTO @l_c_anml_type, @l_c_qlty_type, @l_c_qlty_msr_code,
@l_i_pay_qlty, @l_i_in_kg, @l_i_qty_dlvrd
SET @FETCH_STATUS_2 = @@FETCH_STATUS
END
CLOSE CUR2
DEALLOCATE CUR2
SET @l_i_cntr = 0
DECLARE CUR3 CURSOR FOR
SELECT CUR3.narration, CUR3.grp_desc, CUR3.pay_amt
FROM ( SELECT 'EARNINGS' AS narration,
CASE ISNULL(C.grp_desc, '0') When '0' Then 'OTHERS' Else C.grp_desc END AS grp_desc,
SUM(A.pay_amt) pay_amt
FROM MMS_CS_PAY_DET A LEFT OUTER JOIN (MMS_TKN_GRP B LEFT OUTER JOIN MMS_GRP C
ON B.shed_code = C.shed_code
AND B.grp_code = C.grp_code)
ON A.shed_code = B.shed_code
AND A.tkn_code = B.tkn_code
AND A.set_no = B.set_no
WHERE A.shed_code = @l_c_shed_code
AND A.loc_code = @l_c_loc_code
AND A.cs_code = @l_c_cs_code
-- AND A.from_date = @l_d_from_date
-- AND A.end_date = @l_d_end_date
AND CONVERT(DATETIME, A.from_date, 103) = CONVERT(DATETIME, @l_d_from_date, 103)
AND CONVERT(DATETIME, A.end_date, 103) = CONVERT(DATETIME, @l_d_end_date, 103)
AND ( B.pay_type = @a_c_pay_type or b.pay_type is null )
AND NOT (A.ANML_TYPE = 'B' and A.TKN_CODE = 'SNF') -- Added by Jatinder as Payment should not be made of SNF for Animal type 'B'
--select top 10 * from MMS_CS_PAY_DET
/* changed by kaushal on 21052004 for pakistan rqmnt of handling DO,PF,SA etc */
/* Milk src type is selected on AND and OR basis considering if the value is returned as LF,PF,DO etc (CS pyrll smry report)
then AND will be activated and if milk src type is 03 (LF payrl Smry report) then OR will be activated for . */
AND (( B.milk_src_type = @a_c_milk_src_type or b.milk_src_type is null ) --- original AND
OR ( B.milk_src_type = ( select milk_src_type --- Added OR for Pakistan
from mms_milk_src_type
where coll_pt_flg = @a_c_milk_src_type
and milk_src_type = ( select milk_src_type
from mms_coll_src mcs
where mcs.cs_code = A.cs_code
and mcs.milk_src_type = (select milk_src_type from mms_coll_src where cs_code = mcs.cs_code)
)
)
or b.milk_src_type is null
)
)
/* change ends */
GROUP BY C.grp_desc
HAVING SUM(A.PAY_AMT) > 0
UNION
SELECT 'ADJUSTMENTS' AS narration,
A.ded_grp_desc, SUM(B.act_ded_amt) act_ded_amt
FROM MMS_DED_TYPE A, MMS_CS_ACT_DED B
WHERE B.shed_code = A.shed_code
AND B.ded_code = A.ded_code
AND B.shed_code = @l_c_shed_code
AND B.cs_code = @l_c_cs_code
-- AND B.from_date = @l_d_from_date
-- AND B.end_date = @l_d_end_date
AND CONVERT(DATETIME, B.from_date, 103) = CONVERT(DATETIME, @l_d_from_date, 103)
AND CONVERT(DATETIME, B.end_date, 103) = CONVERT(DATETIME, @l_d_end_date, 103)
GROUP BY A.ded_grp_desc) CUR3
ORDER BY CUR3.narration desc
OPEN CUR3
FETCH NEXT FROM CUR3 INTO @l_c_narration, @l_c_grp_desc, @l_i_pay_amt
SET @FETCH_STATUS_3 = @@FETCH_STATUS
WHILE @FETCH_STATUS_3 = 0
BEGIN
SET @l_i_cntr = @l_i_cntr + 1
UPDATE #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1
SET narration = @l_c_narration,
grp_desc = @l_c_grp_desc,
pay_amt = @l_i_pay_amt
WHERE sl_no = @l_i_cntr
AND cs_code = @l_c_cs_code
SET @ROWCOUNT = @@ROWCOUNT
IF @ROWCOUNT = 0
BEGIN
INSERT INTO #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1
(sl_no, shed_code, area_code, sub_area_code, loc_code,
from_date, end_date, cs_code, earn_amt, ded_amt, net_amt,
tot_qty, calc_type, cssumm_print_flg, anml_type, qlty_type,
qlty_msr_code, pay_qlty, in_kg, qty_dlvrd,
narration, grp_desc, pay_amt)
VALUES
(@l_i_cntr, @l_c_shed_code, @l_c_area_code, @l_c_sub_area_code, @l_c_loc_code,
@l_d_from_date, @l_d_end_date, @l_c_cs_code, @l_i_earn_amt, @l_i_ded_amt, @l_i_net_amt,
@l_i_tot_qty, @l_c_calc_type, @l_c_cssumm_print_flg, NULL, NULL,
NULL, NULL, NULL, NULL,
@l_c_narration, @l_c_grp_desc, @l_i_pay_amt)
END
FETCH FROM CUR3 INTO @l_c_narration, @l_c_grp_desc, @l_i_pay_amt
SET @FETCH_STATUS_3 = @@FETCH_STATUS
END
CLOSE CUR3
DEALLOCATE CUR3
FETCH NEXT FROM CUR1 INTO @l_c_shed_code, @l_c_area_code, @l_c_sub_area_code, @l_c_loc_code,
@l_d_sp_from_date, @l_d_sp_end_date, @l_c_cs_code, @l_i_earn_amt, @l_i_ded_amt,
@l_i_net_amt, @l_i_tot_qty, @l_c_calc_type, @l_c_cssumm_print_flg
SET @FETCH_STATUS_1 = @@FETCH_STATUS
END
CLOSE CUR1
DEALLOCATE CUR1
SELECT @a_c_pay_type_desc = parm_desc
FROM MMS_CNTRY_PARM
WHERE parm_type = 'PAY_TYPE'
AND parm_code = 'SL'
AND valid_flg = 'A'
END
SELECT @CNTRY_CODE = CNTRY_CODE
FROM MMS_INSTL_PARM
SELECT A.sl_no, A.shed_code, A.area_code, C.area_name, A.sub_area_code,
B.sub_area_name, A.loc_code, E.loc_name, A.cs_code, D.cs_name,
A.from_date, A.end_date, A.earn_amt, A.ded_amt, A.net_amt, A.tot_qty, A.calc_type,
A.cssumm_print_flg, A.anml_type, A.qlty_type, A.qlty_msr_code,
A.qty_dlvrd, A.pay_qlty, A.in_kg, A.narration, A.anml_type_2,
A.grp_desc, A.pay_amt, F.milk_src_type_desc, @a_c_pay_type_desc AS pay_type_desc,
--@CNTRY_CODE AS CNTRY_CODE
RTRIM(LTRIM(case @CNTRY_CODE when 'PAK' THEN STR(dbo.MMS_F_ADDA(A.cs_code, @l_d_from_date,@l_d_end_date,@CNTRY_CODE))
ELSE @CNTRY_CODE end)) AS CNTRY_CODE
FROM #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1 A,
MMS_SUB_AREA B,
MMS_AREA C,
MMS_COLL_SRC D,
MMS_LOC E,
MMS_MILK_SRC_TYPE F
WHERE A.sub_area_code = B.sub_area_code
AND A.shed_code = B.shed_code
AND A.shed_code = C.shed_code
AND A.area_code = C.area_code
AND A.shed_code = D.shed_code
AND A.cs_code = D.cs_code
AND A.shed_code = E.shed_code
AND A.loc_code = E.loc_code
AND A.shed_code = F.shed_code
/* changed by kaushal on 21052004 for pakistan rqmnt of handling DO,PF,SA etc */
/* Milk src type is selected on AND and OR basis considering if the value is returned as LF,PF,DO etc (CS pyrll smry report)
then AND will be activated and if milk src type is 03 (LF payrl Smry report) then OR will be activated for . */
AND ( F.milk_src_type = @a_c_milk_src_type ---- Original AND
OR F.milk_src_type = ( select milk_src_type ---- Added OR for Pakistan
from mms_milk_src_type
where coll_pt_flg = @a_c_milk_src_type
and milk_src_type = ( select milk_src_type
from mms_coll_src mcs
where mcs.cs_code = D.cs_code
and mcs.milk_src_type = (select milk_src_type from mms_coll_src where cs_code = mcs.cs_code)
)
)
)
/* change ends */
AND F.valid_flg = 'A'
ORDER BY A.cs_code, A.sl_no,A.qlty_type
END
In next Post I will write comment what make this correct output in report
August 21, 2009 at 6:34 am
-- sp_helptext mms_r_py_payroll_summ_cs_rep_1
-- MMS_R_PY_Payroll_Summ_CS_Rep_1 'Y','01','','','R016','R016','0461','0461','CA','','SL','22/11/2008','21/12/2008'
Create PROCEDURE MMS_R_PY_Payroll_Summ_CS_Rep_1(
@a_c_execute CHAR(1),
@a_c_from_shed CHAR(2),
@a_c_from_area CHAR(4),
@a_c_to_area CHAR(4),
@a_c_from_sub_area CHAR(6),
@a_c_to_sub_area CHAR(6),
@a_c_from_cs_code CHAR(6),
@a_c_to_cs_code CHAR(6),
@a_c_milk_src_type CHAR(3),
@a_c_pay_mode CHAR(2),
@a_c_pay_type CHAR(2),
@a_d_from_date VARCHAR(10),
@a_d_end_date VARCHAR(10))
AS
[red]Start commenting from here upto If ends and report working fine
and This IF condition never comes true
IF @a_c_from_shed = ''
BEGIN
SELECT 0 AS sl_no,
'' AS shed_code,
'' AS area_code,
'' AS area_name,
'' AS sub_area_code, [Blue] or change as cast('' as varchar(30)) AS sub_area_code, [/Blue]
'' AS sub_area_name,
'' AS loc_code,
'' AS loc_name,
'' AS cs_code,
'' AS cs_name ,
CONVERT(DATETIME, NULL) AS from_date,
CONVERT(DATETIME, NULL) AS end_date,
0.0 AS earn_amt,
0.0 AS ded_amt,
0.0 AS net_amt,
0.0 AS tot_qty,
'' AS calc_type,
'' AS cssumm_print_flg,
'' AS anml_type,
'' AS qlty_type,
'' AS qlty_msr_code,
0.00 AS qty_dlvrd,
0.00 AS pay_qlty,
0.0 AS in_kg,
'' AS narration,
'' AS anml_type_2,
'' AS grp_desc,
0.0 AS pay_amt,
'' AS milk_src_type_desc,
'' AS pay_type_desc,
'' AS CNTRY_CODE
RETURN
END
[/red]
DECLARE
@FETCH_STATUS_3 Integer,
@FETCH_STATUS_2 Integer,
@FETCH_STATUS_1 Integer,
@ROWCOUNT Integer,
@l_i_cntr INTEGER,
@l_c_shed_code CHAR(2),
@l_c_area_code CHAR(2),
@l_c_sub_area_code CHAR(4),
@l_c_loc_code CHAR(4),
@l_d_from_date DATETIME,
@l_d_end_date DATETIME,
@l_d_sp_from_date DATETIME,
@l_d_sp_end_date DATETIME,
@l_c_cs_code CHAR(6),
@l_i_earn_amt NUMERIC(16,5),
@l_i_ded_amt NUMERIC(16,5),
@l_i_net_amt NUMERIC(16,5),
@l_i_tot_qty NUMERIC(16,5),
@l_c_calc_type VARCHAR(20),
@l_c_cssumm_print_flg VARCHAR(20),
@l_c_anml_type CHAR(3),
@l_c_qlty_type CHAR(3),
@l_c_qlty_msr_code CHAR(3),
@l_i_pay_qlty NUMERIC(16,5),
@l_i_in_kg NUMERIC(16,5),
@l_i_qty_dlvrd NUMERIC(16,5),
@l_c_narration VARCHAR(20),
@l_c_anml_type_2 CHAR(3),
@l_c_grp_desc VARCHAR(30),
@l_i_pay_amt NUMERIC(16,5),
@a_c_pay_type_desc VARCHAR(30),
@l_c_shed_code_temp CHAR(2),
@l_c_loc_code_temp CHAR(4),
@l_c_cs_code_temp CHAR(6),
@l_d_from_date_temp DATETIME,
@l_d_end_date_temp DATETIME,
@l_i_tot_qty_temp NUMERIC(16,5),
@FETCH_STATUS_4 Integer,
@CNTRY_CODE varCHAR(100)
BEGIN -- 0
-- If pay_mode is blank then set it NULL
if @a_c_pay_mode = '' set @a_c_pay_mode = NULL
-- START OF INITIALIZING WITH DEFAULT VALUES
SET @a_c_from_area = DBO.MMS_F_Common_DefaultValues(@a_c_from_area, 'Area_Code', 'F')
SET @a_c_to_area = DBO.MMS_F_Common_DefaultValues(@a_c_to_area, 'Area_Code', 'T')
SET @a_c_from_sub_area = DBO.MMS_F_Common_DefaultValues(@a_c_from_sub_area, 'Sub_Area_Code', 'F')
SET @a_c_to_sub_area = DBO.MMS_F_Common_DefaultValues(@a_c_to_sub_area, 'Sub_Area_Code', 'T')
SET @a_c_from_cs_code = DBO.MMS_F_Common_DefaultValues(@a_c_from_cs_code, 'Cs_Code', 'F')
SET @a_c_to_cs_code = DBO.MMS_F_Common_DefaultValues(@a_c_to_cs_code, 'Cs_Code', 'T')
-- END OF INITIALIZING WITH DEFAULT VALUES
SET @l_d_from_date = CONVERT(DATETIME, @a_d_from_date, 103)
SET @l_d_end_date = CONVERT(DATETIME, @a_d_end_date, 103)
CREATE TABLE #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1(
sl_no INTEGER,
shed_code CHAR(2),
area_code CHAR(2),
sub_area_code CHAR(4),
loc_code CHAR(4),
-- from_date VARCHAR(11),
-- end_date VARCHAR(11),
from_date DATETIME,
end_date DATETIME,
cs_code CHAR(6),
earn_amt NUMERIC(16,5),
ded_amt NUMERIC(16,5),
net_amt NUMERIC(16,5),
tot_qty NUMERIC(16,5),
calc_type VARCHAR(20),
cssumm_print_flg VARCHAR(20),
anml_type CHAR(3),
qlty_type CHAR(3),
qlty_msr_code CHAR(3),
qty_dlvrd NUMERIC(16,5),
pay_qlty NUMERIC(16,5),
in_kg NUMERIC(16,5),
narration VARCHAR(20),
anml_type_2 CHAR(3),
grp_desc VARCHAR(30),
pay_amt NUMERIC(16,5))
CREATE TABLE #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1_1(
shed_code CHAR(2),
sub_area_code CHAR(4),
anml_type_3 CHAR(4),
gross_amt NUMERIC(16,5),
tot_qty_3 NUMERIC(16,5))
CREATE TABLE #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1_2(
shed_code CHAR(2),
sub_area_code CHAR(4),
ded_code CHAR(4),
tot_ded_amt NUMERIC(16,5))
IF @a_c_execute='Y'
BEGIN
DECLARE CUR1 CURSOR FOR
SELECT A.shed_code, C.area_code, B.sub_area_code, A.loc_code, A.from_date,
A.end_date, A.cs_code, A.earn_amt, A.ded_amt, A.net_amt, A.tot_qty,
CASE A.calc_type When 'P' Then 'PROVISIONAL' When 'I' THEN 'ITERATIVE' When 'F' Then 'FINAL' END AS calc_type,
CASE A.cssumm_print_flg When 'P' Then ' - DUPLICATE' Else NULL END AS cssumm_print_flg
FROM MMS_CS_PAY_HDR A, MMS_COLL_SRC B, MMS_SUB_AREA C
WHERE A.shed_code = B.shed_code
AND A.cs_code = B.cs_code
AND B.shed_code = C.shed_code
AND B.sub_area_code = C.sub_area_code
AND A.shed_code = @a_c_from_shed
AND C.area_code BETWEEN @a_c_from_area AND @a_c_to_area
AND B.sub_area_code BETWEEN @a_c_from_sub_area AND @a_c_to_sub_area
AND A.cs_code BETWEEN @a_c_from_cs_code AND @a_c_to_cs_code
AND B.pay_mode = ISNULL(@a_c_pay_mode, B.pay_mode)
/* changed by kaushal on 21052004 for pakistan rqmnt of handling DO,PF,SA etc */
/* Milk src type is selected on AND and OR basis considering if the value is returned as LF,PF,DO etc (CS pyrll smry report)
then AND will be activated and if milk src type is 03 (LF payrl Smry report) then OR will be activated for . */
AND ( B.milk_src_type = @a_c_milk_src_type --- original AND
OR B.milk_src_type = ( select milk_src_type --- Added OR for pakistan
from mms_milk_src_type
where coll_pt_flg = @a_c_milk_src_type
and milk_src_type = (select milk_src_type
from mms_coll_src mcs
where mcs.cs_code = b.cs_code
and mcs.milk_src_type = (select milk_src_type from mms_coll_src where cs_code = mcs.cs_code)
)
)
)
/* change ends */
-- AND A.from_date = @l_d_from_date
-- AND A.end_date = @l_d_end_date
AND CONVERT(DATETIME, A.from_date, 103) = CONVERT(DATETIME, @l_d_from_date, 103)
AND CONVERT(DATETIME, A.end_date, 103) = CONVERT(DATETIME, @l_d_end_date, 103)
ORDER BY A.shed_code, C.area_code, B.sub_area_code, A.loc_code, A.cs_code
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @l_c_shed_code, @l_c_area_code, @l_c_sub_area_code, @l_c_loc_code,
@l_d_sp_from_date, @l_d_sp_end_date, @l_c_cs_code, @l_i_earn_amt, @l_i_ded_amt,
@l_i_net_amt, @l_i_tot_qty, @l_c_calc_type, @l_c_cssumm_print_flg
SET @FETCH_STATUS_1 = @@FETCH_STATUS
--PRINT @FETCH_STATUS_1
WHILE @FETCH_STATUS_1 = 0
BEGIN
/*Added the code for fixing the cost per kg and 27/11/2003 Ankur*/
set @l_i_tot_qty = 0
DECLARE CUR4 CURSOR FOR
SELECT distinct a.shed_code,a.loc_code,a.cs_code,a.from_date,a.end_date,
--,A.tot_qty AS qty_dlvrd -- Commented by Jatinder Singh Virk
-- Addition Sterted by Jatinder Singh Virk
CASE A.anml_type WHEN 'C' THEN A.tot_qty
ELSE
CASE A.qlty_type WHEN 'SNF' THEN 0
ELSE A.tot_qty END
END AS qty_dlvrd
-- Addition Ended
FROM MMS_CS_PAY_QLTY A
WHERE A.shed_code = @l_c_shed_code
AND A.loc_code = @l_c_loc_code
AND A.cs_code = @l_c_cs_code
AND CONVERT(DATETIME, A.from_date, 103) = CONVERT(DATETIME, @l_d_from_date, 103)
AND CONVERT(DATETIME, A.end_date, 103) = CONVERT(DATETIME, @l_d_end_date, 103)
OPEN CUR4
FETCH NEXT FROM CUR4 INTO @l_c_shed_code_temp,@l_c_loc_code_temp,@l_c_cs_code_temp,@l_d_from_date_temp,@l_d_end_date_temp,@l_i_tot_qty_temp
SET @FETCH_STATUS_4 = @@FETCH_STATUS
WHILE @FETCH_STATUS_4 = 0
BEGIN
set @l_i_tot_qty = @l_i_tot_qty + @l_i_tot_qty_temp
FETCH NEXT FROM CUR4 INTO @l_c_shed_code_temp,@l_c_loc_code_temp,@l_c_cs_code_temp,@l_d_from_date_temp,@l_d_end_date_temp,@l_i_tot_qty_temp
SET @FETCH_STATUS_4 = @@FETCH_STATUS
END
CLOSE CUR4
DEALLOCATE CUR4
/*Added the code for fixing the cost per kg and */
SET @l_i_cntr = 0
DECLARE CUR2 CURSOR FOR
SELECT A.anml_type, A.qlty_type, A.qlty_msr_code, A.pay_qlty,
CASE A.qlty_msr_code When '%' Then ((A.tot_qty * ROUND(A.pay_qlty, 2))/100) END AS in_kg,
-- A.tot_qty AS qty_dlvrd Commented By Jatinder SIngh Virk
CASE A.anml_type WHEN 'C' THEN A.tot_qty
ELSE
CASE A.qlty_type WHEN 'SNF' THEN Null
ELSE A.tot_qty END
END AS qty_dlvrd
FROM MMS_CS_PAY_QLTY A
WHERE A.shed_code = @l_c_shed_code
AND A.loc_code = @l_c_loc_code
AND A.cs_code = @l_c_cs_code
-- AND A.from_date = @l_d_from_date
-- AND A.end_date = @l_d_end_date
AND CONVERT(DATETIME, A.from_date, 103) = CONVERT(DATETIME, @l_d_from_date, 103)
AND CONVERT(DATETIME, A.end_date, 103) = CONVERT(DATETIME, @l_d_end_date, 103)
--AND NOT (A.ANML_TYPE = 'B' and A.QLTY_TYPE = 'SNF') --Added by Jatinder Singh Virk
OPEN CUR2
FETCH NEXT FROM CUR2 INTO @l_c_anml_type, @l_c_qlty_type, @l_c_qlty_msr_code,
@l_i_pay_qlty, @l_i_in_kg, @l_i_qty_dlvrd
SET @FETCH_STATUS_2 = @@FETCH_STATUS
WHILE @FETCH_STATUS_2 = 0
BEGIN
SET @l_i_cntr = @l_i_cntr + 1
INSERT INTO #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1
(sl_no, shed_code, area_code, sub_area_code, loc_code,
from_date, end_date, cs_code, earn_amt, ded_amt, net_amt,
tot_qty, calc_type, cssumm_print_flg, anml_type, qlty_type,
qlty_msr_code, pay_qlty, in_kg, qty_dlvrd)
VALUES
(@l_i_cntr, @l_c_shed_code, @l_c_area_code, @l_c_sub_area_code, @l_c_loc_code,
@l_d_from_date, @l_d_end_date, @l_c_cs_code, @l_i_earn_amt, @l_i_ded_amt, @l_i_net_amt,
@l_i_tot_qty, @l_c_calc_type, @l_c_cssumm_print_flg, @l_c_anml_type, @l_c_qlty_type,
@l_c_qlty_msr_code, @l_i_pay_qlty, @l_i_in_kg, @l_i_qty_dlvrd)
FETCH NEXT FROM CUR2 INTO @l_c_anml_type, @l_c_qlty_type, @l_c_qlty_msr_code,
@l_i_pay_qlty, @l_i_in_kg, @l_i_qty_dlvrd
SET @FETCH_STATUS_2 = @@FETCH_STATUS
END
CLOSE CUR2
DEALLOCATE CUR2
SET @l_i_cntr = 0
DECLARE CUR3 CURSOR FOR
SELECT CUR3.narration, CUR3.grp_desc, CUR3.pay_amt
FROM ( SELECT 'EARNINGS' AS narration,
CASE ISNULL(C.grp_desc, '0') When '0' Then 'OTHERS' Else C.grp_desc END AS grp_desc,
SUM(A.pay_amt) pay_amt
FROM MMS_CS_PAY_DET A LEFT OUTER JOIN (MMS_TKN_GRP B LEFT OUTER JOIN MMS_GRP C
ON B.shed_code = C.shed_code
AND B.grp_code = C.grp_code)
ON A.shed_code = B.shed_code
AND A.tkn_code = B.tkn_code
AND A.set_no = B.set_no
WHERE A.shed_code = @l_c_shed_code
AND A.loc_code = @l_c_loc_code
AND A.cs_code = @l_c_cs_code
-- AND A.from_date = @l_d_from_date
-- AND A.end_date = @l_d_end_date
AND CONVERT(DATETIME, A.from_date, 103) = CONVERT(DATETIME, @l_d_from_date, 103)
AND CONVERT(DATETIME, A.end_date, 103) = CONVERT(DATETIME, @l_d_end_date, 103)
AND ( B.pay_type = @a_c_pay_type or b.pay_type is null )
AND NOT (A.ANML_TYPE = 'B' and A.TKN_CODE = 'SNF') -- Added by Jatinder as Payment should not be made of SNF for Animal type 'B'
--select top 10 * from MMS_CS_PAY_DET
/* changed by kaushal on 21052004 for pakistan rqmnt of handling DO,PF,SA etc */
/* Milk src type is selected on AND and OR basis considering if the value is returned as LF,PF,DO etc (CS pyrll smry report)
then AND will be activated and if milk src type is 03 (LF payrl Smry report) then OR will be activated for . */
AND (( B.milk_src_type = @a_c_milk_src_type or b.milk_src_type is null ) --- original AND
OR ( B.milk_src_type = ( select milk_src_type --- Added OR for Pakistan
from mms_milk_src_type
where coll_pt_flg = @a_c_milk_src_type
and milk_src_type = ( select milk_src_type
from mms_coll_src mcs
where mcs.cs_code = A.cs_code
and mcs.milk_src_type = (select milk_src_type from mms_coll_src where cs_code = mcs.cs_code)
)
)
or b.milk_src_type is null
)
)
/* change ends */
GROUP BY C.grp_desc
HAVING SUM(A.PAY_AMT) > 0
UNION
SELECT 'ADJUSTMENTS' AS narration,
A.ded_grp_desc, SUM(B.act_ded_amt) act_ded_amt
FROM MMS_DED_TYPE A, MMS_CS_ACT_DED B
WHERE B.shed_code = A.shed_code
AND B.ded_code = A.ded_code
AND B.shed_code = @l_c_shed_code
AND B.cs_code = @l_c_cs_code
-- AND B.from_date = @l_d_from_date
-- AND B.end_date = @l_d_end_date
AND CONVERT(DATETIME, B.from_date, 103) = CONVERT(DATETIME, @l_d_from_date, 103)
AND CONVERT(DATETIME, B.end_date, 103) = CONVERT(DATETIME, @l_d_end_date, 103)
GROUP BY A.ded_grp_desc) CUR3
ORDER BY CUR3.narration desc
OPEN CUR3
FETCH NEXT FROM CUR3 INTO @l_c_narration, @l_c_grp_desc, @l_i_pay_amt
SET @FETCH_STATUS_3 = @@FETCH_STATUS
WHILE @FETCH_STATUS_3 = 0
BEGIN
SET @l_i_cntr = @l_i_cntr + 1
UPDATE #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1
SET narration = @l_c_narration,
grp_desc = @l_c_grp_desc,
pay_amt = @l_i_pay_amt
WHERE sl_no = @l_i_cntr
AND cs_code = @l_c_cs_code
SET @ROWCOUNT = @@ROWCOUNT
IF @ROWCOUNT = 0
BEGIN
INSERT INTO #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1
(sl_no, shed_code, area_code, sub_area_code, loc_code,
from_date, end_date, cs_code, earn_amt, ded_amt, net_amt,
tot_qty, calc_type, cssumm_print_flg, anml_type, qlty_type,
qlty_msr_code, pay_qlty, in_kg, qty_dlvrd,
narration, grp_desc, pay_amt)
VALUES
(@l_i_cntr, @l_c_shed_code, @l_c_area_code, @l_c_sub_area_code, @l_c_loc_code,
@l_d_from_date, @l_d_end_date, @l_c_cs_code, @l_i_earn_amt, @l_i_ded_amt, @l_i_net_amt,
@l_i_tot_qty, @l_c_calc_type, @l_c_cssumm_print_flg, NULL, NULL,
NULL, NULL, NULL, NULL,
@l_c_narration, @l_c_grp_desc, @l_i_pay_amt)
END
FETCH FROM CUR3 INTO @l_c_narration, @l_c_grp_desc, @l_i_pay_amt
SET @FETCH_STATUS_3 = @@FETCH_STATUS
END
CLOSE CUR3
DEALLOCATE CUR3
FETCH NEXT FROM CUR1 INTO @l_c_shed_code, @l_c_area_code, @l_c_sub_area_code, @l_c_loc_code,
@l_d_sp_from_date, @l_d_sp_end_date, @l_c_cs_code, @l_i_earn_amt, @l_i_ded_amt,
@l_i_net_amt, @l_i_tot_qty, @l_c_calc_type, @l_c_cssumm_print_flg
SET @FETCH_STATUS_1 = @@FETCH_STATUS
END
CLOSE CUR1
DEALLOCATE CUR1
SELECT @a_c_pay_type_desc = parm_desc
FROM MMS_CNTRY_PARM
WHERE parm_type = 'PAY_TYPE'
AND parm_code = 'SL'
AND valid_flg = 'A'
END
SELECT @CNTRY_CODE = CNTRY_CODE
FROM MMS_INSTL_PARM
SELECT A.sl_no, A.shed_code, A.area_code, C.area_name, A.sub_area_code,
B.sub_area_name, A.loc_code, E.loc_name, A.cs_code, D.cs_name,
A.from_date, A.end_date, A.earn_amt, A.ded_amt, A.net_amt, A.tot_qty, A.calc_type,
A.cssumm_print_flg, A.anml_type, A.qlty_type, A.qlty_msr_code,
A.qty_dlvrd, A.pay_qlty, A.in_kg, A.narration, A.anml_type_2,
A.grp_desc, A.pay_amt, F.milk_src_type_desc, @a_c_pay_type_desc AS pay_type_desc,
--@CNTRY_CODE AS CNTRY_CODE
RTRIM(LTRIM(case @CNTRY_CODE when 'PAK' THEN STR(dbo.MMS_F_ADDA(A.cs_code, @l_d_from_date,@l_d_end_date,@CNTRY_CODE))
ELSE @CNTRY_CODE end)) AS CNTRY_CODE
FROM #TMP_MMS_R_PY_Payroll_Summ_CS_Rep_1 A,
MMS_SUB_AREA B,
MMS_AREA C,
MMS_COLL_SRC D,
MMS_LOC E,
MMS_MILK_SRC_TYPE F
WHERE A.sub_area_code = B.sub_area_code
AND A.shed_code = B.shed_code
AND A.shed_code = C.shed_code
AND A.area_code = C.area_code
AND A.shed_code = D.shed_code
AND A.cs_code = D.cs_code
AND A.shed_code = E.shed_code
AND A.loc_code = E.loc_code
AND A.shed_code = F.shed_code
/* changed by kaushal on 21052004 for pakistan rqmnt of handling DO,PF,SA etc */
/* Milk src type is selected on AND and OR basis considering if the value is returned as LF,PF,DO etc (CS pyrll smry report)
then AND will be activated and if milk src type is 03 (LF payrl Smry report) then OR will be activated for . */
AND ( F.milk_src_type = @a_c_milk_src_type ---- Original AND
OR F.milk_src_type = ( select milk_src_type ---- Added OR for Pakistan
from mms_milk_src_type
where coll_pt_flg = @a_c_milk_src_type
and milk_src_type = ( select milk_src_type
from mms_coll_src mcs
where mcs.cs_code = D.cs_code
and mcs.milk_src_type = (select milk_src_type from mms_coll_src where cs_code = mcs.cs_code)
)
)
)
/* change ends */
AND F.valid_flg = 'A'
ORDER BY A.cs_code, A.sl_no,A.qlty_type
END
August 21, 2009 at 8:17 am
So let me see if I can understand this issue a bit better.
You have a stored procedure thus:
Create Proc ProcName
@PAr1 varchar(4)
@PAr2 varchar(4)
@PAr3 varchar(4)
as
if @Par1=''
begin
select '' Par1,'' Par2,'' Par3
return
end
You create the table:
Create #Temp
(
PAr1 varchar(4)
PAr2 varchar(4)
PAr3 varchar(4)
)
Now you run this proc in a cursor and insert this into #Temp
So it seems to only include one character into each column?
And when you change the proc to the below it works fine?
Create Proc ProcName
@PAr1 varchar(4)
@PAr2 varchar(4)
@PAr3 varchar(4)
as
if @Par1=''
begin
select ' ' Par1,' 'Par2,' ' Par3----Notice paas blank space and report work fine
end
(what is a "paas"?)
Am I understanding this correctly?
And you say this occurred after you changed the database and server collation? If so, can you clarify how you did that?
August 23, 2009 at 10:41 pm
Yes If I just pass blank space report run correctly.
I like to remind once again that this problem comes only in report display if i run this proc in query analyzer it run perfectly.
I change collation of DB only.
August 23, 2009 at 11:56 pm
panwar.jt (8/23/2009)
I like to remind once again that this problem comes only in report display if i run this proc in query analyzer it run perfectly.
In that case, I'd personally be investigating the reporting tool, not the SQL. If it runs fine in Query Analyser (SQL 2000 or SQL 2005?), then it should run fine anywhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2009 at 1:34 am
But this report is running fine with blnk space and with sql server 2000
August 24, 2009 at 1:51 am
GilaMonster (8/23/2009)
panwar.jt (8/23/2009)
I like to remind once again that this problem comes only in report display if i run this proc in query analyzer it run perfectly.In that case, I'd personally be investigating the reporting tool, not the SQL. If it runs fine in Query Analyser (SQL 2000 or SQL 2005?), then it should run fine anywhere.
Agreed.
August 24, 2009 at 1:56 am
panwar.jt (8/24/2009)
But this report is running fine with blnk space and with sql server 2000
Hi Panwar,
If the query is running correctly in SQL Server 2000's query analyzer in both forms, but it doesn't seem to come out correctly, why do you feel that it is a problem with the database?
It seems to me, and please correct me if I'm wrong, that the results that are being returned by the reporting tool differ to the results being returned by the query tool... in which case I would have to agree with GilaMonster in that the likelihood of an issue with a. the query, or b. the query tool is unlikely.
I know some reporting engines can take the data returned and then do further manipulation to it in the report application or presentation layer. Could it not be a reporting feature or bug that is causing this problem? What is the reporting tool that you are using?
August 24, 2009 at 3:10 am
My complete analysis is
1-- This report is running in sql server 2000
2-- I restore a copy of databse to Sql Sever 2005
3-- Report stops due to different collation between my databse and server
4-- Change the collation of my database to alien with server collation
5-- Then report start malfunctioning(just displaying one charecter of char and varchar type columns numeric value is displaying correct)
6-- Then I put few blank space as mentioned above or comment that select stament report start working fine.
In this case I never touch report, All things I did in database so I think that there is something in databse.
Is there any setting in database where I can set default size of blank as 10 byte???????
August 25, 2009 at 8:02 am
Hi,
I suppect the ANSI_DEFAULTS
(ex. ansi_nulls and/or quoted_identifier)
Script the live SP and check how ansi_nulls and/or quoted_identifier are setted
Also check how ansi_nulls and/or quoted_identifier are setted in you quey analyser tool.
Are those ANSI_DEFAULTS are setted the same ?
May be this can explain why it work fine in query analyser and doesn't work with ou reporting tool.
Calico
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply