When I click on a view or stored procedure to see what code makes up the item. The statement in the window is being cut off allot. Is this a know issue or is there a solution?
Thank you
View/Stored Procedure details being cut off
I experience this too on MS SQL. For example, I have this stored procedure script (from MS SQL Server Management Studio):
If I manually run
and this in the Routine body textbox:
Do you use regex or something to parse the result from sp_helptext ?
CREATE proc [dbo].[sel_NHRdetail_bak] @Scheme_ID as integer, @ValDte datetime = null AS
--declare @SchID as integer, @ValDte datetime; select @SchID = 9
if @ValDte is null
select @ValDte = MAX(ValDte) from vNHR h
select scheme_id, schemename, CloseDte, ValDte, d
, grossup, NetUp, nhr, nhr_nacc, Increase
, nhrcol, Weight, tPrice
, YEPrice, yeclosedte, YEDte, PriceSrc, YEPriceSrc--distinct l.DSSCode, l.DSSFactor, l.NHRCol, 'Valuedate' = @ValDte, v.tPrice, v.Reason, v.PriceSrc, v.CloseDte, v.PrvDte
, GrossUp_, NetUp_, dpsfact, divval, HldVal, divfact, GrossupFact, totalcharges
from vNHR v
where v.ValDte = @ValDte
and v.scheme_id = @Scheme_ID
GO
If I manually run
EXEC sp_helptext 'dbo.sel_NHRdetail_bak'
in HeidiSQL I see the exact above script in the result set, but if I select the stored procedure in HeidiSQL and view the Procedure tab, I get this in the CREATE code tab:CREATE PROCEDURE "sel_NHRdetail_bak"()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
from vNHR h
select scheme_id, schemename, CloseDte, ValDte, d
, grossup, NetUp, nhr, nhr_nacc, Increase
, nhrcol, Weight, tPrice
, YEPrice, yeclosedte, YEDte, PriceSrc, YEPriceSrc--distinct l.DSSCode, l.DSSFactor, l.NHRCol, 'Valuedate' = @ValDte, v.tPrice, v.Reason, v.PriceSrc, v.CloseDte, v.PrvDte
, GrossUp_, NetUp_, dpsfact, divval, HldVal, divfact, GrossupFact, totalcharges
from vNHR v
where v.ValDte = @ValDte
and v.scheme_id = @Scheme_ID
and this in the Routine body textbox:
from vNHR h
select scheme_id, schemename, CloseDte, ValDte, d
, grossup, NetUp, nhr, nhr_nacc, Increase
, nhrcol, Weight, tPrice
, YEPrice, yeclosedte, YEDte, PriceSrc, YEPriceSrc--distinct l.DSSCode, l.DSSFactor, l.NHRCol, 'Valuedate' = @ValDte, v.tPrice, v.Reason, v.PriceSrc, v.CloseDte, v.PrvDte
, GrossUp_, NetUp_, dpsfact, divval, HldVal, divfact, GrossupFact, totalcharges
from vNHR v
where v.ValDte = @ValDte
and v.scheme_id = @Scheme_ID
Do you use regex or something to parse the result from sp_helptext ?
For this script, the Routine body textbox is completely empty, but I get a message saying "You have no privilege to this routine", which is incorrect. My guess is the parsing of the script throws an error?
[code sql]
CREATE procedure [dbo].[sel_RPPOutput]
@Portfolio_ID INT, @Scheme_ID int = null, @ValueDate datetime
AS
select * from RPPOutput
where Portfolio_ID = @Portfolio_ID
and Scheme_ID = @Scheme_ID
and ValueDate = @ValueDate
GO
[/code]
[code sql]
CREATE procedure [dbo].[sel_RPPOutput]
@Portfolio_ID INT, @Scheme_ID int = null, @ValueDate datetime
AS
select * from RPPOutput
where Portfolio_ID = @Portfolio_ID
and Scheme_ID = @Scheme_ID
and ValueDate = @ValueDate
GO
[/code]
Please login to leave a reply, or register at first.