one of the reasons i've had the opportunity to write some python is because of ironpython. no matter how much diversity i would like in my professional development the truth is i work in a microsoft shop. it's just that simple. because ironpython lives in the .net runtime i only have to be slightly creative to get some professional use out of it.

with the advent of .net 3.5 microsoft (thanks in part to john lam) has introduced ironruby. much like ironpython the idea is simple. marry the .net runtime with the ruby programming language and its dynamic nature.

here's a simple example of creating a simple wpf interface in ironruby:

# load wpf
require 'PresentationFramework, Version=3.0.0.0, Culture=neutral, 
  PublicKeyToken=31bf3856ad364e35'
require 'PresentationCore, Version=3.0.0.0, 
  Culture=neutral, PublicKeyToken=31bf3856ad364e35'

# make a window
wnd = System::Windows::Window.new()
# make a button
btn = System::Windows::Controls::Button.new()
btn.Content = "Click me"

# assign a ruby block to a buttons click event
btn.click do |sender, evt_args|
	System::Windows::MessageBox.Show("Hello from WPF in ruby.")
end

# slap the button on the window
wnd.Content = btn

# start the application
app = System::Windows::Application.new()
app.run wnd

SSRS provides an interesting scripting facility, RS.EXE, which allows you to run VB .Net code against the management web service of SSRS.

I encountered a situation where I had to add a user to the policy as the "Browser" role for all folders in my SSRS environment. Because I didn't want to inherit parent item security to all objects I had no clean way of accomplishing that other than programmatically. I wrote up a quick script and this task will be easy as pie from now on.

Here's the basics of the code. It can just be slapped into the text editor of your choice. It's just a script, not a proper VB app.

Public Sub Main()
   Dim items() as CatalogItem
   Dim item As CatalogItem
   Dim newPolicy as Policy
   Dim role as new Role()

   'returns EVERY CatalogItem in our reporting services instance
   items = rs.ListChildren("/", True)
   role.Name = "Browser"
    
   newPolicy = new Policy()

   ' the UserGroup variable will be passed in from the command line.  
   ' see remarks later
   newPolicy.GroupUserName = UserGroup
   newPolicy.Roles = new Role() { role }    
    
   Console.WriteLine("Adding Browser Role for user or group: " & _
      newPolicy.GroupUserName )
    
   For Each item In items
      ' Ensure we're only setting the policy on Folders and not User's 
      ' special folders
      If item.Type = ItemTypeEnum.Folder And _
         Not item.Name.Equals("My Reports") And _
            Not item.Name.StartsWith("MYDOMAINNAME")  Then
         SetNewPolicy(item.Path, newPolicy)
         
         Console.WriteLine(item.Path)
      End If
   Next
End Sub

Note that the CatalogItem, Policy and Role classes are all provided by the Reporting Services Web Services API for us to use. The rs object is a global instance of the ReportingServices class the engine provides us. Our Main() method here simply iterates all items we want to set permissions on and calls a SetNewPolicy routine which we have yet to write. The UserGroup global string object is going be passed in as an argument from the command line and will be demonstrated towards the end of this article.

Now we have to define our SetNewPolicy routine that will add our policy to a catalog item.

Private Sub SetNewPolicy(itemPath As String, newPolicy As Policy)
   Dim inherit as Boolean
   Dim existingPolicies() As Policy 

   ' ask the web service for the current policies of the item in question
   existingPolicies = rs.GetPolicies(itemPath, inherit)

   If Not (existingPolicies Is Nothing) Then
      Dim policy As Policy
     
        Try
         For Each policy In existingPolicies
            If policy.GroupUserName = newPolicy.GroupUserName Then
               Throw New Exception("Policy Already exists, skipping")
            End If
         Next

         Dim policyList As New _
            System.Collections.ArrayList(existingPolicies)
         policyList.Add(newPolicy)
         
         ' instruct the web service to save our new policies
         rs.SetPolicies(itemPath, policyList.ToArray(GetType(Policy)))
      Catch Ex As Exception
         Console.WriteLine(Ex.Message)
      End Try
   End If
End Sub

The SetNewPolicy function basically grabs the existing policies of a specified item and adds our new policy to it assuming it doesn't conflict with any existing policies via the rs.GetPolicies and rs.SetPolicies respectively.

Save this script as SetPermissions.rss and it can now be executed from the command line as such:

rs -i SetPermissions.rss -s http://MYSSRSBOX/reportserver 
   -v UserGroup="MYDOMAINNAME\kilnaar"

See the '-v UserGroup="MYDOMAINNAME\kilnaar"' argument? That's what is fed into the global UserGroup variable used in our Main() function.

Pretty easy all in all. From here you should check out this reference to see what all is exposed to you.

Happy SSRS administration!

over the years people have attempted to put large amounts of non relational data in SQL. images strike me as the most popular case. generally people either stored the data directly in SQL or stored it on the filesystem with the path in SQL. neither of these were terrific options. the former was difficult to manage and frequently resulted in poor performance while the later tended to become inconsistent very quickly because SQL had no way of understanding how the filesytem was modified behind its back.

enter SQL 2008's FILESTREAM storage which allows you to store LOB fields directly on the filesystem but still function as columns in a regular old SQL table. this allows your LOBs to be limited only by the size of the filesystem and also guarantees consistency with SQL.

this post won't cover configuring the storage engine to use FILESTREAM. that information can be found here. in RC0 there's currently a few problems you may encounter with the setup. this post will help you solve them.

now... let's get started.

take, for example, an existing database named "Sandbox". the first thing we have to do to allow SQL to talk to FILESTREAM storage is to create a filegroup specifically for that purpose:

alter database Sandbox 
add FILEGROUP Sandbox_FS CONTAINS FILESTREAM

alter database Sandbox 
add file 
( NAME = Sandbox_FS,
FILENAME = 'C:\DATA\Sandbox_FS')
to filegroup Sandbox_FS

now that we have our database ready to play with FILESTREAM we'll create a table named "Things" we can use for testing:

create table Things (
	Thing_GUID uniqueidentifier rowguidcol 
 not null primary key default newid(),
	filedata varbinary(max) FILESTREAM
)

note the rowguidcol, it's imperitive that a rowguid not null column exist in order for any sibling columns to be stored in FILESTREAM storage. also note that our LOB field is decorated with the FILESTREAM attribute.

just to give us something to work with i'm going to create a row in here with an empty LOB we can play with:

insert into Things (filedata) values (0x)

from here on in we'll switch over to C# where we can manipulate this row's filedata column. slap all the following code in the main class for a console application for easiest use. we're going to need to talk directly to the SQL native client because at this point there's nothing within .net to access the sql LOB streams. a few constants and an external function call are necessary (Update 2009/12/07: The constants may no longer be necessary. 2k8 was still in beta at the time of this post and it's in sore need of an update.):

        const UInt32 DESIRED_ACCESS_READ = 0x00000000;
        const UInt32 DESIRED_ACCESS_WRITE = 0x00000001;
        const UInt32 DESIRED_ACCESS_READWRITE = 0x00000002;

        const UInt32 SQL_FILESTREAM_OPEN_NO_FLAGS = 0x00000000;
        const UInt32 SQL_FILESTREAM_OPEN_FLAG_ASYNC = 0x00000001;
        const UInt32 SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING = 
 0x00000002;
        const UInt32 SQL_FILESTREAM_OPEN_FLAG_NO_WRITE_THROUGH = 
 0x00000004;
        const UInt32 SQL_FILESTREAM_OPEN_FLAG_SEQUENTIAL_SCAN = 
 0x00000008;
        const UInt32 SQL_FILESTREAM_OPEN_FLAG_RANDOM_ACCESS = 0x00000010;

        [DllImport("sqlncli10.dll", SetLastError = true,
              CharSet = CharSet.Unicode)]
        static extern SafeFileHandle OpenSqlFilestream(
                string FilestreamPath,
                UInt32 DesiredAccess,
                UInt32 OpenOptions,
                byte[] FilestreamTransactionContext,
                UInt32 FilestreamTransactionContextLength,
                Int64 AllocationSize);

since the necessary interop is ready we can do the typical plumbing and get our connection set up.

        static void Main(string[] args)
        {
            using (SqlConnection conn = 
                new SqlConnection(@"Data Source=(local)\TWOKEIGHT;
                Initial Catalog=Sandbox;Integrated Security=SSPI")) 
            {
                conn.Open();

we'll call the PathName function on the filedata we just created. this will return a UNC-path-looking-thing that is the identifier we'll pass into OpenSqlFilestream.

                SqlCommand pathCmd = 
                    new SqlCommand("
 SELECT TOP 1 filedata.PathName() FROM dbo.Things", conn);
                String path = pathCmd.ExecuteScalar().ToString();

all direct FILESTREAM manipulation must occur in a transaction. GET_FILESTREAM_TRANSACTION_CONTEXT gets the context from the transaction we just started so we can supply it to the native client.

                SqlTransaction tran = conn.BeginTransaction();

                SqlCommand transCmd = 
                    new SqlCommand("
 SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", conn, tran);
                byte[] transContext = (byte[])transCmd.ExecuteScalar();

here we're going to open the LOB using OpenSqlFilestream to get a handle so we can treat it like any old stream in managed code.

                SafeFileHandle handle = OpenSqlFilestream(
                         path,
                         DESIRED_ACCESS_READWRITE,
                         SQL_FILESTREAM_OPEN_NO_FLAGS,
                         transContext,
                         (UInt32)transContext.Length,
                         0);

                byte[] buff = new byte[512];
                using(FileStream stream = new FileStream(
                        handle,
                        FileAccess.ReadWrite,
                        buff.Length,
                        false)){
                    StreamWriter writer = new StreamWriter(stream);

to complete the illustration we'll write some data and commit up our transaction.

                    writer.Write("abc");
                }

                tran.Commit();
            }
        }

pretty cool eh, we opened a LOB in SQL that actually lived on the filesystem and treated it like a regular filestream within the context of a sql transaction.

For an example I'm essentially going to combine a table of words into sentences. Consider this structure:

declare @words table (
	ID int identity,
	Word_Text varchar(1024),
	Sentence int
)

With this data:

insert into @words
select 'i', 1
insert into @words
select 'am', 1
insert into @words
select 'concatenated', 1

insert into @words
select 'you', 2
insert into @words
select 'are', 2
insert into @words
select 'not', 2

So the idea is that we should get two resultant sentences, "i am concatenated" and "you are not".

So POW, here it is:

;with CTE_Sentence (Num, Word_Text, Sentence) as (
	select distinct cast(1 as int), cast('' as varchar(max)), 
	Sentence
	from @words
		
	union all
	
	select cast(T.Num + 1 as int), 
		CTE.Word_Text  + ' ' + T.Word_Text,
		T.Sentence
	from CTE_Sentence CTE
		inner join 
		(
			select row_number() over (
	partition by Sentence order by ID ) AS Num, 
			Word_Text,
			Sentence
			from @words
		) T
			on T.Num = CTE.Num
				and T.Sentence = CTE.Sentence
)
select Sentence, (
		select top 1 Word_Text
		from CTE_Sentence CTE
		where CTE.Sentence = W.Sentence
		order by Num desc
	) Words
from @words W
group by Sentence

Which results in :

Sentence 	Words
1	 	i am concatenated
2	 	you are not

POW.