getting text output from stored procedure
-
Steve:
Sorry for lecturing, and Kitkat is fortunate that you share your knowledge and expertise with this site!
I'm old school and avoid wizards like the plague. I encourage all programmers to understand the actual code which the wizards are depending on for accomplishing its output.
Also, I ask that they consider this as their goal while using a wizard, generator, or designer; so that quick does not always replace the robust.
kitkat: As time allows, please look into SQL system stored procedures calls for sp_help, sp_texthelp, sp_tables, sp_columns, etc...
This is helpful in building your SQL knowledge and borrowing from its syntax. Also you can list all "column names" from any new table within your database by "table name" using:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='mytable'
ORDER BY ORDINAL_POSITION
Display all non-system "stored procedures":
SELECT NAME
FROM SYSOBJECTS
WHERE XTYPE='P'
Viewing your "sp's" with sp_helptext 'name':
SP_HELPTEXT 'my_procedure'
NOTE: procedure call ( to view self )
USE MASTER;
EXEC SP_HELPTEXT 'sp_helptext'
Regards,Coach James
-
Steve,
I got the report to work in SQL reporting finally
....Instead of manipulating in data in reporting...i modify the calling stored procedure so that it gets AssetID in its first select statement. This is what i did
CREATE PROCEDURE [dbo].[sp_exec_dependsonme] @Asset varchar(50)
AS
begin
set nocount on
declare @statflag int
set @statflag=1
if @statflag=0 --never true, just to show AssetID field in reporting services data grid
begin
select AssetID from ##temp_dependsonme
end
if exists(select 1 from tempdb..sysobjects where name = '##temp_dependsonme' and type = 'U')
begin
drop table ##temp_dependsonme
end
create table ##temp_dependsonme (
ID INT primary key identity,
AssetID ntext )
exec dbo.rec_dependsonme @Asset
select AssetID from ##temp_dependsonme order by ID asc
end
GO
It worked just fine in sql reports. Thanks for all your help. I did learn a good deal in this report.
ENJOY!
kitkat
-
Does it work if you do this :
if 1=0 --never true, just to show AssetID field in reporting services data grid
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply