Use SSRS ReportViewer with PowerShell, use parameters and catch navigate event

Today I will just show a small extension of code from @sqlbelle which you find at SQL Server PowerShell : How to View your SSRS Reports (rdl) Using PowerShell and ReportViewer

Thanks belle, your code gave me a great start to  build a prototyp and evaluate the ReportViewer Control. I had to add an eventhandler for the hyperlink event and to provide reportparameters by code.

As I didn't find these features in PowerShell Examples and I found the translating of C# not trivial, I present here my code.

Thanks go to James Kovacs for the C# example for Reportparameters

James Brundage how showed me how to write an eventhandler in PowerShell

and somewhere in Bruce Payette's Windows PowerShell in Action I found the solution for defining the array of .Net Objects, which I need here for  Report Parameters.

hope it helps

Bernd

Edited:
Oops I had still formating difficulties in this blog.
You find the same code at http://poshcode.org/1977.

Generating the column list for an SQL-Server Table

How often did you use in query analyzer extra | options | results | userdefined delimiter| ,
to the column list of a table in a form ready to replace the '*' in

Select * from mytable

by the complete column list?

Well I definitely did it too often. It is so easy to write a stored procedure for the same purpose:

if object_id('build_columnlist') > 0
    drop procedure build_columnlist
go

create procedure dbo.build_columnlist @table_name sysname
as
begin
declare @cols varchar(max)  -- varchar(8000) for sql server 2000

select @cols = case when @cols  is null then c.name else @cols + ', ' + c.name end
from syscolumns c join sysobjects o on c.id = o.id
where o.name = @table_name order by colorder

select @cols
end

go

now just type

exec build_columnlist 'mytable'

and when you display the result as text, it is there ready to copy and past.

Oops did you change extra | options | max characters pro column to 8000 ?
That was for Query Analyzer, SQL-Managemantstudio has a similar setting. 

And note the trick of updating a variable using a select statement.

defaults are always wrong.

Bernd

About the use of SQL-Server Print Statement

In the old days, when I did my ad-hoc SQL using T-SQL in the Query-Analyzer, it was natural to use print statements, when to give back some feedback to the user.

Today using SQL-Server Management Studio, half the time I display the results in grid and as long as it looks reasonable, I won't switch to the message tab.

With SQL-Server the use  print in T-SQL is rather easy. While in former days you could only emit one string or variable, with todays versions you can use expressions.

I'm not going to speak about Orcales DBMS_OUTPUT package in length here. Remember SET server output on. And OK with 10 g they even got SET SERVEROUTPUT ON SIZE UNLIMITED, i.e. it became an option to use.

But acessing your database from applications or a PowerShell module like SQLPSX,it becomes more difficult to collect the print output.
For SQL-Server it would be possible, that means I know code to get the output.
While with Oracle I have only some vague ideas, how to do it (requiring to use connections) .

Conculsion: I'm starting to rewrite my scripts, using select @msg msg instead of print @msg.

Bernd