CDMantel
asked on
SSRS Report of View Create Script Using Query Message
I would like to create an SSRS report that presents the Create View script for a view the user enters as a parameter. I found this helpful query on this forum to generate the message:
DECLARE @SqlText nvarchar(max)
SET @SqlText = ''
SELECT @SqlText = @SqlText + [Text] FROM sys.syscomments
WHERE id = OBJECT_ID('dbo.Sales')
ORDER BY colid
PRINT @SqlText
I'm struggling with how one accesses the message from a query in SSRS - if that is even possible. Or is there a better way to do it?
Thanks.
DECLARE @SqlText nvarchar(max)
SET @SqlText = ''
SELECT @SqlText = @SqlText + [Text] FROM sys.syscomments
WHERE id = OBJECT_ID('dbo.Sales')
ORDER BY colid
PRINT @SqlText
I'm struggling with how one accesses the message from a query in SSRS - if that is even possible. Or is there a better way to do it?
Thanks.
ASKER
Thanks for responding VelentinoV.
Yes, you are quite right that i'm thinking the user would select from a list of available views in a dropdown.
The query you suggested gathers the needed data. The "struggle" is in formatting the data (the Create View script in this case) so that it is visually appealing (that is, not breaking at a random point for line wrap). The SQL Print command will do that. However, as i understand it, the output of the Print command is as a SQL message. So my struggle is in accessing this output from SSRS OR using some other method to format the script.
Yes, you are quite right that i'm thinking the user would select from a list of available views in a dropdown.
The query you suggested gathers the needed data. The "struggle" is in formatting the data (the Create View script in this case) so that it is visually appealing (that is, not breaking at a random point for line wrap). The SQL Print command will do that. However, as i understand it, the output of the Print command is as a SQL message. So my struggle is in accessing this output from SSRS OR using some other method to format the script.
Okay, understood what you mean now! Let's see, perhaps the sp_helptext system procedure can be a solution here? It returns the object's definition split up into multiple "records". If you use that procedure to retrieve the view's definition and set up a List in your report to show the records, I think it should work.
See http://msdn.microsoft.com/en-us/library/ms176112.aspx
See http://msdn.microsoft.com/en-us/library/ms176112.aspx
ASKER
True enough. sp_helptext splits output into multiple records. Problem is that the location of the splitting seems random. It doesn't follow the same logic that Print uses. I can get all the information needed. it just won't be presented in an easier to read format as Print does. Is my quest in vain?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow. My bad. You're right. Sending the query output to text proves the point. Thanks very much for your help (including the link for custom report item).
To get a list of all views, including their definition and object_id, you can use the query in the snippet.
Note that the query is using sys.sql_modules instead of sys.syscomments (that one is outdated).
But I don't understand what you mean with that last sentence: "I'm struggling with...". Please clarify if still applicable.
Open in new window