Feed Icon  

Contact

  • Bryant Likes
  • Send mail to the author(s) E-mail
  • twitter
  • View Bryant Likes's profile on LinkedIn
  • del.icio.us
Get Microsoft Silverlight
by clicking "Install Microsoft Silverlight" you accept the
Silverlight license agreement

Hosting By

Hot Topics

Tags

Open Source Projects

Archives

Ads

SQL Server 2005 Xml - Cool feature of the day

Posted in Sql and Xml at Wednesday, July 28, 2004 5:41 AM Pacific Daylight Time

I'm sure that there are going to be a lot of things that I find very cool about the Xml support in SQL Server 2005. But here is one for today:

  1. Create a new for xml query of any type (try select * from Person.Contact for xml auto, root('Persons') in the AdventureWorks sample database).
  2. Run the query.
  3. Notice the results look very similar to the Xml results in SQL Server 2000. However, they are underlined (that means you can click them :)
  4. Click the results and voila, a nice Xml view opens up for you to look at your Xml results. Bravo! (standing up clapping)

It's the simple things that make me happy. :)

While you're at it, you should also see this example of  how explicit mode is much better now.

 

SQL Server 2005 DTS and ODBC Connections

Posted in Sql and Xml at Wednesday, July 28, 2004 3:23 AM Pacific Daylight Time

I ran into while using DTS in SQL Server 2005. If you want to use an ODBC connection you have to do the following:

    1. Pretend to create a ADO.Net SQL connection first (or managed OLEDB,
      whatever works).
    2. Select the connection in the connections tray and manually:
      change ConnectionString to "DSN=myODBC; " (or whatever your connection
      string is)
    3. Change Qualifier to "System.Data.Odbc.OdbcConnection, System.Data,
      Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

[via SQL Server 2005 DTS Newsgroup]

Coder to Developer

Posted in General at Wednesday, July 28, 2004 12:55 AM Pacific Daylight Time
I got my copy of Mike Gunerloy's book Coder to Developer yesterday. I read the first two chapters this morning and really enjoyed them. As a developer who primarily works on his own the book is a perfect fit. I still have a lot more to read but I would highly recommend this book if you work on your own or in a small group.

ComponentArt Web.UI 2.0 Beta

Posted in General | ASP.Net/Web Services at Tuesday, July 27, 2004 5:24 AM Pacific Daylight Time

[via email]

We are pleased to inform you that we have just released ComponentArt Web.UI 2.0 Beta, a significant upgrade to our ASP.NET user interface product line.

ComponentArt Web.UI 2.0 Includes:

- Brand-new Menu control;
- Brand-new NavBar control;
- Significantly enhanced TreeView control;
- Improved Rotator and Snap controls;
- Significantly improved product documentation.

For full release details, please see our Web.UI 2.0 Beta announcement:
http://www.componentart.com/forums/ShowPost.aspx?PostID=1053

This looks like a great upgrade and I'm looking forward to using it. I've been using the ASP.Net menu since version 1.0.

Service Broker "Hello World"

Posted in Sql and Xml at Tuesday, July 27, 2004 5:13 AM Pacific Daylight Time

Pretty interesting hello world example using SQL Server 2005 Service Broker. I'm definately going to dig into this product as I can see some good usage senarios that will leverage my existing SQL skills.

SQL Server 2005 Beta 2 Released

Posted in Sql and Xml at Monday, July 26, 2004 3:53 AM Pacific Daylight Time

[via MSDN SQL Server Developer Center]

If you are an MSDN Subscriber, SQL Server 2005 Beta 2 is now available for you to download. Click here for a link to the subscriber downloads site.   

Go get it!

Update: More SQL Server 2005 stuff can be found here (including a link to download SQL Server 2005 Express Beta 2).

Update2: Bob Beauchemin has a posted some observations about Beta 2 that could save you some time. Read them before you run the install.

Accessing Databases via Custom Code

Posted in Reporting Services | General at Wednesday, July 21, 2004 7:10 AM Pacific Daylight Time

Another good question in response to my Custom Code in Report Services article.

I am trying to access a database through a custom assembly and I already have the following in the rssrvpolicy.config file , but I still get the #Error. [snip] Do I need an associated permission set? And what would that be if I do, I dont know?

The answer that yes you do need an associated permission set. If you're using the System.Data.SqlClient to connection to a SQL Server database then you will need the SqlClientPermission. This would look like:

<PermissionSet
    class="NamedPermissionSet"
    version="1"
    Name="MyDatabasePermissionSet"
    Description="A special permission set that grants sql access.">
  <IPermission
      class="SqlClientPermission"
      version="1"
      Unrestricted="true"
  />
  <IPermission
      class="SecurityPermission"
      version="1"
      Flags="Execution, Assertion"
  />
</PermissionSet>

So where did I get this information from? I already knew that the SqlClientPermission was what I needed from some previous projects and I was able to determine what the IPermission section should look like based on Lamont's post. However, this brings up something that I've been unable to figure out: how do you figure out what should be in the IPermission section?

Maybe I'm just missing it but I read through a few CAS articles and I've searched through the documentation and yet somehow this remains a total mystery to me. Is this documented somewhere? Can you point me to it?

The question (from above) continues:

And do I need to do step 3 and 4 of your article for my custom assembly if I am only accessing a database from my function.
And If I do, what would step 3 and 4 be?

Yes you do need to do steps 3 and 4. Step 4 would be exactly the same. In step 3 you would need to assert the SqlClientPermission on your method as follows:

[VB]
<SqlClientPermission(SecurityAction.Assert)> _
Public Shared Function Foo() as String
...

[C#]
[SqlClientPermission(SecurityAction.Assert)]
public static string Foo()
...

I'm going to go back and read up some more on CAS. It is something every .Net developer should understand.

Update:

For some reason when I was testing this I was getting a security exception even though I was asserting SqlClientPermission on the method call. I found I had to actually assert the permission in my code before it would work. The code I used is posted below. I would really like to understand this better but I'm actually heading out of town for a few days with my wife for our 1st wedding anniversary.

[VB]
Dim perm as SqlClientPermission new SqlClientPermission(PermissionState.Unrestricted)
perm.Assert()
'' SQL CODE HERE
CodeAccessPermission.RevertAssert()


[C#]
SqlClientPermission perm = new SqlClientPermission(PermissionState.Unrestricted);
perm.Assert();
// SQL CODE HERE
CodeAccessPermission.RevertAssert();

 

Instance Based Custom Code in Reporting Services

Posted in Reporting Services at Wednesday, July 21, 2004 4:14 AM Pacific Daylight Time
In response to a question posted in the comments of my last article I've created a Writing Custom Code Part II article. In this article I explain the basic of how to use instance based custom code in SQL Server Reporting Services.

Writing Custom Code in SQL Server Reporting Services - Part II

Posted in Reporting Services at Wednesday, July 21, 2004 4:07 AM Pacific Daylight Time

Writing Custom Code in SQL Server Reporting Services - Part II

In my first article on Custom Code I went through the basics of using custom code. The goal of this article is to answer a question that was asked in the comments of the first article.

Sidney asked:

Could you elaborate on instance based classes and the procedures required to make this work.

So I looked into instance based code to see how it works and it is actually very simple.

Instance Based Code

In the first article we created a simple assembly called MyCustomAssembly that had a class called SayHello. For this article we will add an instance based method to that class and then call it from our report. First let's add the new method to the class. Below is the new code highlighted in bold.

[VB]
Public Class SayHello

  Private counter As Int32 = 0

  Public Function HelloWithCount() As String
    counter += 1
    Return String.Format("Hello! (for the {0} time)", counter)
  End Function

...

[C#]
public class SayHello
{

  private int counter = 0;

  public string HelloWithCount()
  {
    return string.Format("Hello! (for the {0} time)", ++counter);
  }

...

This new method will be instance based and will change the output based on how many times it has been called. So if we call HelloWithCount() three times we would expect the third call to output "Hello! (for the 3 time)".

After you've added the new method re-compile the project. You will need to copy the dll file again to the C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer folder on your computer and overwrite the old version. Next open the report you're using to test the dll and add a reference to the dll (instructions here) if you haven't already done so in the last article. This time we also need to add an entry in the Class section.

In the class section add a new entry by clicking the the class name box and typing "MyCustomAssembly.SayHello" without the quotes. Next click in the instance box next to it and type "hello" without the quotes. This basically adds a variable named hello to the report of the type SayHello. So when the report is run a new instance of this object will be created (even if you don't access the object).

Now that we have an instance of our object we can make calls to it. Add three textboxes to your report and put "=Code.hello.HelloWithCount()" in each one. When you click the preview tab your report should run and you should see a different output in each box (1,2, and 3). You now have used instance based code in your report.

That's it! Hopefully you were able to get everything to work. Now you should have a basic understanding of how to use instance based code with reporting services. If you have a question leave a comment and I will try to address it. Thanks again for the great comments on my last article!

Introduction to Writing Custom Code in RS

Posted in Reporting Services at Friday, July 16, 2004 5:38 AM Pacific Daylight Time

I just published a short introductory article about Writing Custom Code in Reporting Services.

Writing Custom Code in SQL Server Reporting Services

Posted in Reporting Services at Friday, July 16, 2004 3:24 AM Pacific Daylight Time

Writing Custom Code in SQL Server Reporting Services

Someone asked me today how to use a Custom Assembly in Reporting Services. Since I'd never used one before I tried to point them to some useful articles that I found here on MSDN. However, the article didn't seem to help much so I decided to look into it and figure out how to do this since it is an interesting topic.

Let me start by saying that I found the documentation on this to be pretty poor. The docs (which can be found here) do you give enough information to figure it out, but they leave out the information that would make the process as simple as it should be. So that is the goal of this article, to show you how easy it really is to write custom code for SQL Server Reporting Services.

Embedded Code

For our first trick we will write some embedded code. To get started open your browser to this page. On that page under embedded code you will see the following statement.

To use code within a report, you add a code block to the report. This code block can contain multiple methods. Methods in embedded code must be written in Visual Basic .NET and must be instance based.

I'm assuming that "instance based" means the methods don't have to be shared (that's static for you C# coders). However, in my tests I found you can use either instance based or shared methods.

So let's get started and add some embedded code to a report. Fire up Visual Studio and create a new report project and a new report (Right click the project -> Add New Item -> Report). Next click this link for instuctions to add the embedded code to the report.

To add code to a report

  1. On the Report menu, click Report Properties.
  2. Note If the Report menu is not available, click within the report design area.
  3. On the Code tab, in Custom Code, type the code.


So type the code :)

It actually is that easy. So for our example type the following:

Public Function SayHello() as String
  Return "Hello from Embedded Code"
End Function

Next add a textbox to the new report put "=Code.SayHello()" without the quotes in the textbox. You should now be able to preview your report and see "Hello from Embedded Code" on the report. That is as far as we are going to take embedded code (I also found this article which talks a little more about embedded code). At this point you should be able to deploy your report to the server and run it on the server.

Custom Assemblies

The next topic is custom assemblies. This is a much more useful feature since you aren't constrained to writing code in VB.Net on a small dialog in the report designer. You can use Visual Studio and your language of choice. So for our example create a new Visual Studio Class Library project in either C# or VB.Net. Call it MyCustomAssembly. Next rename Class1 to SayHello. To the SayHello class we will add one method which is shown below.

[VB]
Public Shared Function Hello() as String
  Return "Hello from My Custom Assembly!"
End Function

[C#]
public static string Hello()
{
  return "Hello from My Custom Assembly!";
}

Once you're done with the code go ahead and compile the project. Once you've gotten your assembly compiled follow this link for instructions on how to add a reference to your code. Once you've added the reference to your code you will also need to copy the dll file to the C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer folder on your computer. The dll file can be found in your project folder under the bin\debug folder (or bin\release if you compiled a release build).

Note: You only need to add a reference. The Class section will only be used if you're class has instance methods (versus Shared or static methods).

Add another textbox to your report and this time fill it with "=MyCustomAssembly.SayHello.Hello()". You should be able to preview the report and see "Hello from My Custom Assembly" on the report. So you have now used both embedded code and custom assemblies. Before you can deploy the report you need to copy the MyCustomAssembly.dll to the server and put it in the C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin folder on the server. Once you have copied the dll file you should be able to deploy your report to the server.

But what if you want to actually do something interesting like grab values from a database or use a web service? To do that you will need to modify the Code Access Security (CAS) settings for reporting services.

Code Access Security

There is actually a comprehensive article on this topic here. I would recommend you read that article. There is also a good introduction to CAS in general here and here. The goal of this article (the one you're reading) is to give you a quick-and-easy example that can get you started. So we will attempt to read a value from a file and return it.

First create a text file called hello.txt and put it in your c:\temp folder. In the file add the text "Hello from a text file!" and save and close the file. Next add the following method to your SayHello class:

[VB]
Public Shared Function HelloFromFile() as String
  Dim reader as StreamReader = New StreamReader("c:\temp\hello.txt")   Dim hello as String = reader.ReadToEnd()
  reader.Close()
  Return hello
End Function

[C#]
public static string HelloFromFile()
{
  using (StreamReader reader = new StreamReader("c:\\temp\\hello.txt"))
  {
    return reader.ReadToEnd();
  }
}

Note: You will also need to add Imports System.IO in VB and using System.IO in C#.

Next compile you the project. Once the project is compiled you will need to remove the old reference in your report and add the new reference and copy pver the new dll (you will have to close the report project to do this). Add a new textbox to the report and fill it with "=MyCustomAssembly.SayHello.HelloFromFile()". You should be able to preview the report and see the text that you added to the text file.

Next copy the dll and the text file (and put the text file in the c:\temp folder) to the server and deploy the report to the server. When you try to view the report on the server you won't see your message. Instead you should see "#Error".

In order to make this work on the server we need to follow the steps outlined in the CAS article. The first step according to the article is to:

1. Identify the exact permissions that your code needs in order to make the secured call. If this is a method that is part of a .NET Framework library, this information should be included in the method documentation.


Since we are using the StreamReader object we should be able to find this information in the StreamReader documentation but unfortunately I wasn't able to find it (at least I didn't see it). However, I'm going to guess (and I guessed right) it requires the FileIOPermission from the System.Security.Permissions namespace.

Next we need to:

2. Modify the report server policy configuration files in order to grant the custom assembly the required permissions.


So following the example given in the article on CAS we can add the following Xml under the <NamedPermissionSets> node in the rssrvpolicy.config file in the C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer folder.

Note: Be careful when you edit this file. I would highly recommend making a backup copy of the original file before making any changes. This file is an Xml file and you must be sure that what you add is valid Xml.

<PermissionSet
    class="NamedPermissionSet"
    version="1"
    Name="HelloFilePermissionSet"
    Description="A special permission set that grants read access to my hello file.">
  <IPermission
      class="FileIOPermission"
      version="1"
      Read="C:\temp\hello.txt"
  />
  <IPermission
      class="SecurityPermission"
      version="1"
      Flags="Execution, Assertion"
  />
</PermissionSet>

Next we need to add the code group. Again, based on the example in the CAS article we can add the following Xml. We have to put this under the correct CodeGroup node in order for it to work correctly. So you will need to put it under the last code group, but make sure it has the same parent node as the last code group. To do this just insert it before the second to last ending CodeGroup as shown below:

...
      />
      </CodeGroup>
[Insert Here]
    </CodeGroup>
  </CodeGroup>
</PolicyLevel>
...

Here is the code group to add.

<CodeGroup
    class="UnionCodeGroup"
    version="1"
    PermissionSetName="HelloFilePermissionSet"
    Name="MyCustomAssemblyCodeGroup"
    Description="A special code group for my custom assembly.">
  <IMembershipCondition
        class="UrlMembershipCondition"
        version="1"
        Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\MyCustomAssembly.dll"
  />
</CodeGroup>

Once you've added all this you will still need to complete steps 3 & 4 before the custom assembly will work.

3. Assert the required permissions as part of the method in which the secure call is made. This is required because the custom assembly code that is called by the report server is part of the report expression host assembly which runs with Execution by default. The Execution permission set enables code to run (execute), but not to use protected resources.

4. Mark the custom assembly with the AllowPartiallyTrustedCallersAttribute. This is required because custom assemblies are called from a report expression that is a part of the report expression host assembly, which by default is not granted FullTrust, thus is a 'partially trusted' caller. For more information, see "Using Strong Named Custom Assemblies," earlier in this document.


To complete step 3 you will need to first add using System.Security.Permissions or Imports System.Security.Permissions to your SayHello class. Next you will need to assert the FileIOPermission on the HelloFromFile method as shown below:

[VB]
<FileIOPermissionAttribute(SecurityAction.Assert, Read="c:\temp\hello.txt")> _
Public Shared Function HelloFromFile() as String
...

[C#]
[FileIOPermissionAttribute(SecurityAction.Assert, Read="c:\\temp\\hello.txt")]
public static string HelloFromFile()
...

Next in your assembly attribute file (AssemblyInfo.cs in C# or AssemblyInfo.vb in VB), add the following assembly-level attribute (and add Imports System.Security or using System.Security):

[VB]
<assembly:AllowPartiallyTrustedCallers>

[C#]
[assembly:AllowPartiallyTrustedCallers]

Once you've done this, recompile the assembly and copy it to the server. You should now be able to execute the report and see "Hello from a Text File!" instead of "#Error". If you still see the error need to make sure that you click the refesh button on the report toolbar and you may have to restart the reporting service.

That's it! Hopefully you were able to get everything to work. Now you should have a basic understanding of how to use custom code with reporting services. If you have drop leave a comment and I will try to address it.

GotDotNightmare

Posted in SharePoint | General at Tuesday, July 13, 2004 3:46 AM Pacific Daylight Time

Is GDN the worlds slowest website or is it just me? If you were Microsoft, wouldn't you want to at least put a bandaid on it? Sheesh.

I'm digging into FABRIQ and wanted to ask a couple of questions about it so I figured I would hit the workspace. However, it seems that the GDN is having some major issues so I can't access the workspace.

I was already planning on moving the RsWebParts off GDN because it is just too slow. You spend at least 75% of your time waiting for things to load. I'm sure that it is a very complicated application so it must not be an easy fix, but there must be a better way.

 

Good WSE 2.0 Article

Posted in ASP.Net/Web Services at Wednesday, July 07, 2004 4:26 AM Pacific Daylight Time

If you aren't using the WSE 2.0 stuff yet then I would recommend walking through this article:

Web Service Messaging with Web Services Enhancements 2.0

I hadn't explored the WSE stuff until I read this article which provides a nice walkthrough if you code along. My only complaint is that after you add the Reply-To messaging it won't work until you add the config settings. I hadn't scrolled down to the config part and was very confused as to why my subscriber wasn't getting the subscription response message (although maybe this is a good lesson to learn).

Another issue I ran into was the code used to call the web service proxy generated by VS. It seemed to be referencing some of the older code in the article versus where the example is placed. However, it was very easy to figure out the correct code for once I got there.

Overall a great article and well worth the read.

 

As makes things simpler

Posted in Sql and Xml | General at Friday, July 02, 2004 2:59 AM Pacific Daylight Time

Just read Scott's post about how useful the using statement is. I agree 100%. I remember when I “discovered” the using statement and realized how much simplier it makes things. I'm happy to point out that XmlWriters and XmlReaders will support being used in whidbey. (Also using is coming to VB.Net)

I would also like to point out another one of my favorites: as. as is a very useful keyword which I find makes a lot of code simplier. For example, loading up an object with values from a DataReader.

Without as:

private static MyObject LoadMyObjectFromDataReader(IDataReader dr)
{
  MyObject myObj = new MyObject();
  if (dr["Name"] != DBNull.Value)
  {
    myObj.Name = (string)dr["Name"];
  }
  if (dr["Address"] != DBNull.Value)
  {
    myObj.Address = (string)dr["Address"];
  }
  return myObj;
}

With as:

private static MyObject LoadMyObjectFromDataReader(IDataReader dr)
{
  MyObject myObj = new MyObject();
  myObj.Name = dr["Name"] as string;
  myObj.Address = dr["Address"] as string;
  return myObj;
}

Of course this only works with non-value types so you can't do this with ints. So there you go, as simplifies your code.

 

Visual Studio 2005 Enterprise Architect Beta 1 - Now on MSDN

Posted in General at Thursday, July 01, 2004 4:57 AM Pacific Daylight Time

via Early Adopter

It just showed up on MSDN subscriber downloads.

Downloading....

Update: Installing... :)

Update: Running! Just opened my first beta 1 web project.

Virtual Server 2005 and XPSP2 RC2

Posted in Sql and Xml | General at Thursday, July 01, 2004 3:26 AM Pacific Daylight Time

Well with all the new express products just waiting to be tried I figured I would fire up my Virtual Server 2005 and create some new instances of Windows XP to test them out. However, whenever I tried to get into the Virtual Server 2005 administration website I would get an access denied error. Hmmm...

My first guess was to assume it was the firewall blocking my access. However turning off the firewall had no effect.

My next step was to take a look at the event viewer and there I found the first clue as to what the problem was. There was a bunch of error logs with the message:

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID

{DA3111BC-1BD7-4884-A535-8470D36028F7}

to the user [snip]. This security permission can be modified using the Component Services administrative tool.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Ok. Next I opened the Component Services admin tool and looked under the DCOM config. There were quite a few entries there so instead of looking for the component by the GUID I did a quick registry search for the GUID and found the human readable name is “Virtual Server“. :)

Scrolling down in the Component Services admin tool I found the Virtual Server entry, right clicked it, selected Properties, switched to the security tab, edited the Launch and Activation permissions, changed local activation properties to “Allow“, and now everything works!

So, the problem was with XPSP2 which makes COM more secure. Hopefully this will be fixed in the next RC of Virtual Server 2005 so that this blog entry doesn't become a popular topic.

Now, on to my real goal which was to start playing with SQL Server 2005 Express...

 

MSN Search Improves

Posted in General at Thursday, July 01, 2004 2:11 AM Pacific Daylight Time

Scoble just posted that MSN Search has improved. I do like how it looks and it seems to work great. However, I don't see any compelling reason to type search.msn.com (vs. google.com) or to uninstall the Google toolbar and install the MSN Search toolbar.

According to MSN the reason is they give you better answers and cleaner results. So maybe I will try a few searches on MSN over the next few days (if I remember). In the meantime, give me some good reasons to switch.