getting text output from stored procedure
-
I am trying to get the output from stored procedure to sql reporting
service. But the problem is sql reporting is taking only the grid
output and not the text messages or text output. grid output is not
giving me the correct result as it's a recursive data retrieval
procedure. When i run the procedure in query analyzer, the messages
gives me the correct result and i want to pass the text result to sql
reporting. This is my stored procedure which gets the list of assets
dependent on an asset.
CREATE PROCEDURE [dbo].[rec_idependon] @assetid varchar(50)
AS
set nocount on
declare @level int,
@childassetid int,
@parentassetid int,
@assetname varchar(50)
select @level = @@NESTLEVEL
if @@nestlevel = 1
begin
select dbo.astDependencies_dep.depFK_astID_Dependancy,
dbo.vwAssetProperties_ALL.astName
from astDependencies_dep, vwAssetProperties_ALL
where dbo.vwAssetProperties_ALL.astID =
dbo.astDependencies_dep.depFK_astID_Dependancy and
dbo.astDependencies_dep.depFK_astID = @assetid
print 'I Depend on list for ' + @assetid
end
if @@NESTLEVEL < 10
begin
declare c1 cursor local for
select dbo.astDependencies_dep.depFK_astID_Dependancy,
dbo.vwAssetProperties_ALL.astName
from astDependencies_dep, vwAssetProperties_ALL
where dbo.vwAssetProperties_ALL.astID =
dbo.astDependencies_dep.depFK_astID_Dependancy
and dbo.astDependencies_dep.depFK_astID = @assetid
open c1
fetch c1 into @childassetid, @assetname
while @@fetch_Status = 0
begin
print replicate (' - ' , @level * 3 ) + '> '
+ ltrim (str(@childassetid) + ', '+@assetname)
exec rec_idependon @childassetid
fetch c1 into @childassetid, @assetname
end
close c1
deallocate c1
end
else
begin
print 'Nesting level reached its limit. Cannot expand tree further.'
end
return
GO
Is there a way i can get messages to show up in sql reporting or the
other solution could be to show the recursive data in grid itself.?
Is there any other easy way to do it?
Thanks in advance for any help
-
You could try creating a new procedure based on your existing one, and instead of the print statements change them to inserts (into a temp table) and then at th end of the sproc do a select * from the table. As long as this select was the only part of the stored procedure that returns a resultset then this is what will be seen by RS.
Steve.
-
First statement could end with "else" for consistancy... also keep upper & lower case same for readabilty like "@@NESTLEVEL" ...
Each select statement can be converted to same format on return like "CONVERT(varchar(100),value1)+' '+CONVERT(varchar(100),value1)+' '+CONVERT(varchar(100),value1)"...
=== RETURN ONLY TEXT ===
This way your not getting an internal error on sending differing function value to the report utility from within SQL engine, (this passing is done through C++ which does not like parameters to be wrong type on input), and throws out the error...
Regards,Coach James
-
Steve,
I can create a temp table and insert the data into it but how would i differentiate the recursive data. To clarify, let me show the text output
I Depend on list for 569
- - - > 568, Physicians Forum
- - - - - - > 567, McKesson EMR
- - - - - - - - - > 146, EGATE1
- - - - - - - - - > 147, EGATE2
- - - - - - - - - > 159, FCHAP011
- - - - - - - - - > 565, eGate1
- - - - - - - - - > 566, eGate2
That shows that 567 depends on 568 and 568 depends on 569 and bottom five depends on 567.
Is it possible to get the output like this in grid format because in grid in query analyser, i am only getting 568 i.e. the first level. I understand temp table can have all records but i'm just wondering how it will identify the hierarchy level.
Is it possible to get the output as text output instead of grid in sql reporting? Either option would work.
Thanks for your help,
kitkat
-
Hey kitkat,
I'm not sure if the following will work for you but in general it does what you're looking to achieve.
The table control (and possibly the matrix?) allows for grouping, and in doing this it natively supports parent child relationships. A simple example can be seen here (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_objects_v1_3cok.asp ). For my testing I modified it slightly (add a group to the table as well as having the details section, inthe group row have the firstname+lastname as per the details section). My mods resulted in having a single indent but the hierrchies could have been (I haven't checked) more than just Level1->level2.
Where I see this not satisfying your requirements is the amount of stepping/tabbing in, but it does simpify the stored proc required to get the data out (ie you just supply the parent and child id fields).
I don't think that RS will take the text output as a data source, I think under the covers because of ADO etc it is looking for the dataset returned from the sp, but I can't say for sure. One of the authorities on this is Brian Wecker who has a blog (i think on msdn blogs) but also tends to answer the newsgroups, you may want to check with them. But, if you're still keen on using your original stored procedure, you could try creating a large varchar field in the temp table, I assume you're using a function to add in tabs or arrows for the indentation, so keep this in place and then just return the results of this single column table back to RS putting the result into a single table column/field. The biggest downside I see to this is that there will be no grouping (ie + / - buttons) to allow your users to show hide the children of the current item they're looking at.
steve.
Steve.
-
Hi Steve,
I tried to use the parent child recursice feature but for some reason, i was not getting the required result and also the indentation. That was the reason i moved to stored procedures to see if sql can also the problem for me. I'll try more and see if i can get this work, otherwise i always have option to program it. But i wonder why in grid result it only stops at level 1 in query analyser. Will let you know if find a solution.
Thanks for your help!
kitkat
-
Hey kitkat,
Moving back to a simpler approach, have you tried the following:
1. Create a new stored proc. In this proc, create a global temp table with two fields, the first an integer with identity set (ie auto increments on inserts) and the second a varchar (4000 should cover it).
2. In the new stored proc, call your existing stored proc (rec_idependon) and let it run with recursion.
3. in the rec_idependon procedure, change the print statement to be an insert statement to the global temp table.
the result of this should be a table that looks like
Col1 Col2
1 I Depend on list for 569
2 --->568,Physicians Forum
3 ------>567,McKesson EMR
4 --------->146,EGATE1
5 --------->147,EGATE2
6 --------->159,FCHAP011
7 --------->565,eGate1
8 --------->566,eGate2
You should then be able to do a select from this table and have the result come back (to RS) as two columns. Use the first to sort the data and the second as the only displayed field in a table control in an RS report.
Upsides: you can use your existing stored procedure with minor mods and the report prints the expected out put.
Downsides: this isn't "grouped" in RS, therefore if you were hoping/looking for the little +/- signs to show/hide sub levels of dependence, this won't do it for you.
HTH,
Steve.
-
That works...Great!!
Ya, i don't have the +/- signs but atleast this solves the purpose. I wonder why reporting service didn't give me the recursive hierarchy with parent-child grouping. But this works, so no sweat.
Thanks,
kitkat
-
Hey Steve,
I am posting the code here for anyone having similar problem.
CREATE PROCEDURE [dbo].[sp_exec_idependon] @Asset varchar(50)
AS
begin
if exists(select 1 from tempdb..sysobjects where name = '##temp_idependson' and type = 'U')
drop table ##temp_idependon
create table ##temp_idependon (
ID INT primary key identity,
Asset ntext )
exec dbo.rec_idependon @Asset
select Asset from ##temp_idependonme
end
GO
This all works fine but i have a small problem.
In SQL reporting when i mention this stored procedure as my dataset, it doesn't show any fields and therefore i'm unable to use the fields in layout or preview mode. When i run the dataset alone, it works fine but how do i specify the fields in layout mode. I don't know if i am able to explain it clearly but basically problem is to grab the Asset field from global temporary table in layout mode.
Thanks,
Amit
-
That's an annoying 'feature'. The way i usually get around it is to write a similar stored proc that returns the anticipated column names. It's obviously 'cheaper' to just write a statement like SELECT 'a value' AS Asset and then hit the refresh button (this refreshes the column names for the query). Then change the query back. Oviously if you chnage the field names in the stored proc, you'd have to go back and do this again.
Steve.
-
Steve,
I am afraid i didn't understand your point. I noticed that SQL reporting only takes the felds which a stored procedure points towards the first select statement. That's why when i'm executing the rec_idependon procedure and then doing a select on temp table, it shows me the fields defined in rec_idependon and not of temp table.
Also, how can i refresh the data because sql reporting is not showing the current data(showing one query old data). Now, if i put select from temp table first then execution of sp_exec_idependon, then i get old data. If i put execution first than i don't get the temp field.
Let me know your thoughts on showing and refreshing column as you mentioned (in a simple language
Thanks,
kitkat
-
Hey Kitkat,
if your current query for the dataset is a call to a stored procedure ( in Generic Query Designer mode) the drop down at the top right is set to 'Stored procedure' and the text of the query is --> rec_idependon
Then change the query type to 'text' and type in a validly formed but 'dummy' query -->
SELECT 'empty' AS Asset
then while still on the 'Data' tab of your report, click the 'Refresh' button only (this button is the one on the right of the 'delete dataset'). This should put the field name 'Asset' into the field list for the dataset.
Now change the command type back to Stored Procedure and type in the procedure name but make sure you don't hit the 'refresh' button again (ie you can execute your query for testing but don't refresh the fieldnames.) This should leave the field list to be only the field 'Asset' which you can click and drag intot he report designer as you would with any other field.
Steve.
-
Nice procedure to pass on to someone else, hopefully this person will be working under you, so he/she will not have to explain the reason for such an archaic and error prone method for producing reports. Should find a better and completed method, which requires no programmer support…
Important question to ask yourself, is how often is this report required and how much time will be spent in processing it on a daily, weekly, monthly, and yearly basis?
Important Foot Note: Reporting should be kept simple and easy, so it's handled by just a few clicks for choosing selection parameters and the output; this way any user or operator can produce reports on demand (programmer not required).
Regards,Coach James
-
Hey Steve,
I have got another urgent project to finish so i'm going to give it a break for a week atleast. But i'm so close and also learning a lot about SQL reporting, i'll definately finish this and keep you posted how i got it work (i hope i will
Thanks for your advice and effort. Stay tuned for my update.
kitkat
Note for Coach James: Your comments are welcome but it would be better if you have post any solution to the problem.
-
Kitkat -> hope you get it to work.
Coach James -> Don't flame the messenger
Just so you're sure it isn't just me that's advocating this approach, take a quick look in BOL for Dynamic Queries (Using Dynamic Queries) and you'll see the following text, so this is actually a Microsoft endorsed approach to populating field names.Using Dynamic Queries
.... Also, if you use an expression for a query, Report Designer cannot automatically derive fields and parameters from the query. You must manually define all of the fields that the query is expected to return. You must also manually define report and query parameters. One way to simplify this process is to write a normal query that returns the same fields and uses the same parameters that the query expression does. Report Designer can use this query to automatically derive the fields and parameters. After this is done, you can change the query to an expression.....
You're right, personally I'd look for a more robust way to get the column names pre-read by RS but in certain cases this isn't possible. May not be true in this case but with the limited info provided, this is one way that will work.
I'm not sure I follow your comments regarding the frequency of production or the non-requirement for a programmer - I would be certain that Kitkats idea of putting the report into RS is surely to avoid requiring programmers or DBA's to custom write reports on an ad-hoc basis?
Steve.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply