Sunday, November 08, 2009

SQL Server - Car crash queries. - MTUG Cork




Simon Sabin is coming to Cork on the 8th Dec to give a talk to the MTUG user group on Car Crash Queries



Details of the talk are below please register on www.mtug.ie if you wish to attend


SQL Server - Car crash queries.
What happens when a query goes wrong. We’ve all had the  occasion where a query was running fine and then suddenly it doesn’t. You then get the query from profiler and run it in management studio and it runs fine, but your app is still running slowly. What is happening? In this session we will be looking what causes the query to fail and why you get odd results when you run in management studio. Importantly you will find out what to do avoid this happening on your system.

 

Speaker
Simon Sabin is director of his own company that provides training and consultancy around SQL Server development and business intelligence.



He has a particular expertise in the areas of search, distributed architectures, business intelligence and application development. He has worked with SQL Server since 1998 and has always focused on high performance reliable systems.



Monday, November 02, 2009

Office Accounting is no more

I thought Office Accounting was a great little product and having been launched recently in the UK I was looking forward to its release in Ireland and using its SDK.

I have just discovered that Microsoft have pulled Office Accounting http://www.msofficeaccounting.co.uk/ and after Nov 16 it will be supported Mamut. Microsoft they have decided that the Excel templates are a better option for a small business.
 

I suspect this may be more to do with avoiding trouble with the EU as the single user version was free.  Sage have lobbied the EU previously about Microsoft’s moved into the accountancy area

Sunday, October 25, 2009

Windows Server 2008 R2

I found a handy free eBook on Windows Server R2 from Microsoft here link

Thursday, October 22, 2009

Reporting Services - Data -Driven Subscription

 In Reporting Services data driven subscriptions are used to run a query on a scheduled basis which returns a list of recipients to receive a report.  What I have tried to do here is to show a simple example which will use this functionality to monitor an application and email out a report when any alerts that have been triggered.

 
You need the enterprise version of reporting services to use data driven subscriptions. In addition SQLAgent needs to be running and the data source must use stored credentials.
 

The example will use a simple table to hold details any events that need to be reported on. The application will insert rows into this table for each event that needs reporting on

create table ReportTriggers(
    TriggerID int IDENTITY(1,1) NOT NULL,
    TriggerDate smalldatetime NULL,
    TriggerDescription varchar(50) NOT NULL
)

 
The stored procedure prGetReportTriggers will be queried and it will only populate the email recipient list if new records have been added to the ReportTriggers table that have not already been reported based on the fact that the triggerdate is null
 

Create procedure prGetReportTriggers
as
begin
    if (select COUNT(*) from ReportTriggers
        where TriggerDate is null) > 0
        begin
                select 'yourname@yourdomain.com' as Email
                update ReportTriggers set TriggerDate = getdate()
                where TriggerDate is null
        end
                select NULL as Email
end       
        

The report that will be called by the subscription should use the following SQL to get those records that have just been updated by the prGetReportTriggers stored procedure.

select * from ReportTriggers
where TriggerDate = (select max(TriggerDate) from ReportTriggers)

 
Follow the following basic steps to create the data driven subscription 



 
Call the stored procedure 




Populate the To value with the returned email list


Set up the subscrption schedule to run as frequently as you require

Tuesday, September 29, 2009

SQL Server Express and Reporting Services

A common question that gets asked is if SQL Serve Express supports Reporting Services. Yes it does however you need to install the version Advanced Services which can be downloaded from here.


In addition to the usual restrictions you get with the Express version you can only use Reporting Services against you local express database i.e. you cannot install SQL Express and then use it’s Reporting Service against a database on another server.

Wednesday, September 23, 2009

XRM - What is it?

Just in case you stumbled across the term XRM and was wondering what is this new buzzword. It is the new brand name for developing applications using Microsoft Dynamics CRM. I believe Microsoft have coined this term because customers can be confused when you tell them you are going to build their line of business application using CRM. By the way I think CRM it is a great platform which gets very little recognition especially in comparison to SharePoint.

CRM in built on a .NET application development platform which can easily be extended by developers using the same SDK as the Microsoft CRM development team. Some people have suggested that CRM was only developed to show the power of the development platform.

Going forward I don’t XRM will mention CRM and with the multi tenant support added in version 4.0 I think it will become an important piece in Microsoft cloud development platform. Stay tuned.

Monday, September 14, 2009

30th Sept - Windows 7 Technical Community Launch Cork

The community launch of Windows 7 takes place on Sept 30th at 2pm in the Rochestown Park Hotel. Each lucky attendee will get a copy of Windows 7 Ultimate and you can register for attendence here event

Places are limited however if you are i t he MTUG user group you will have received a promotion code that will give you priority.

In the evening there will be a separate event focussing on Windows 7 and home server with demo with XBox and Microsoft Live which will be worth a look register here event

Monday, September 07, 2009

Auto update columns in Dynamic Data

Lots of people have a requirement to have some columns automatically updated when using Dynamic Data in conjunction with the Entity Framework. These are typically audit type columns such as the ModifiedDate column found in the AdventureWorks database.

The typical solution is to create a handler to trap the event so that you can change the data before it is saved to the database.


public partial class AdventureWorksEntities : global::System.Data.Objects.ObjectContext
    {
        partial void OnContextCreated()
        {
            this.SavingChanges += new EventHandler(OnSavingChanges);
 
        }
        private static void OnSavingChanges(object sender, EventArgs e)
        {
            foreach (ObjectStateEntry entry in ((ObjectContext)sender).ObjectStateManager.GetObjectStateEntries(
                 EntityState.Added | EntityState.Modified))
            {
                int i = entry.CurrentValues.GetOrdinal("ModifiedDate");
                entry.CurrentValues.SetValue(i, DateTime.Now);
            }

This can give you problems if you want a generic version for all tables but not all the tables have the ModifiedDate column and so GetOrdinal throws an exception. An alternative is to use some reflection to check if the Property exists and update accordingly and avoids exceptions being thrown.

foreach (ObjectStateEntry entry in ((ObjectContext)sender).ObjectStateManager.GetObjectStateEntries(
                EntityState.Added | EntityState.Modified))
            {
                // Ignore relationships
                if (!entry.IsRelationship)
                {
                    foreach (PropertyInfo pi in entry.Entity.GetType().GetProperties())
                    {
                        if (pi.Name == "ModifiedDate")
                            pi.SetValue(entry.Entity, DateTime.Now, null);
                    }
                }
            }

Wednesday, July 29, 2009

Conditional Formatting in Reporting Services using Common Code

The lack of support for CSS style sheets in reporting services can make life difficult when you want to have common formatting across reports. There are also occasions where you may want to share common conditional formatting logic across reports. This example shows how common code can be used to help in these scenarios.

Whilst a .NET assembly would probably be more efficient not everybody is in a position to write and deploy .NET assemblies.

The first example simply sets the background color of a textbox based on the value of a variable.


In this example the textbox background is set to red if the value is less than 50 otherwise it is set to green. This logic may be required in multiple textboxes in a single report or across multiple reports.
Open your report and under properties add the following to the code

function StopGoColor(d as decimal) as string
StopGoColor = iif(d < style="font-family:arial;">end function







Select the textbox where you want apply the conditional formatting and enter the following expression for the fill color using your own value to pass to the function


=Code.StopGoColor(Fields!SalesPercent.Value)


This function can be used through out your report to ensure consistant formatting.


Once you have written the functions you require you can create a report template by copy the code into an empty report and saving this report into the C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject direcory.

Now when you select Add New you will get the option to base any new report on the newly created template report which already contains your common formatting code.






Monday, July 13, 2009

Kathleen Dollard's Slides

The slides from Kathleen's talk can be found on her blog

Friday, July 10, 2009

Mobile Express for Microsoft CRM 4.0 released

Microsoft have just released mobile express for CRM 4.0 which allows you to access your CRM data on mobile devices including iPhones http://blogs.msdn.com/crm/archive/2009/07/09/product-release-mobile-express-for-microsoft-dynamics-crm-4-0.aspx

It's a shame it has taken this long to get it out and it will be interesting to see what happens when version 5.0 of CRM is released - due this year I believe. The mobile version should really be part of the core product release.

Friday, June 26, 2009

Kathleen Dollard - Great Event



What a great event!. On a lovely sunny evening we had over 60 people turned up to hear Kathleen talk. The talk was great with plenty of good questions.

Shame the air con decided to break on one of the hotest days of the year with a packed room!

Many thanks to Kathleen for coming to Cork.

Tuesday, June 23, 2009

MCT - Microsoft Certified Trainer




I am now a Microsoft Certified Trainer!!








Tuesday, June 09, 2009

Xbox cannot read external drive formatted as NTFS

Just recently I was trying out a media box and decided to plug in my usb drive into my son's Xbox to see how it performed as a media player.

Guess what I discovered Xbox's cannot read drives formatted as NTFS so I had to reformat it as FAT however Vista, or XP, cannot format drives over 32gig.

If you want to format a big drive as FAT I found the best tool to use was Fat32Format whcih you can download from
download

Friday, May 22, 2009

MTUG Cork Talks June 2009 - Bob Duffy and Kathleen Dollard

We are fortunate to have two great speakers coming to the Cork in June.

Bob Duffy will be here on on June 4th and will cover Reporting Services 2008. Bob is a sql expert one of only eighteen Microsoft Certified Database Architects Globally

Kathleen is a well know expert from the US and will cover the new language features in .NET 4.0. Kathleen wrote a great book a while back on code generation which I found particulary useful. book

The talks are free to attend as usual however you must register on http://www.cork.mtug.ie/

Friday, May 08, 2009

Entity Framework - Pain Continued

Following on from previous post. If you are converting an existing project and database with lots of stored procedures to EF then you have a big job. Support for returning procedures is limited and involves hand editing the EDML file. If you then refresh from your database all you hand codes stuff is deleted. Nor does it support returning scalers.

With Linq To SQL this is a doddle and when you import returning stored procedures it creates the results entities. I appreciate L2S is designed as a simple ORM designed solely for SQL server and the intention of EF is to be used for enterprise applications however I think more work needs to be done on EF. Hopefully the 4.0 version will solve some of the existing problems.

I guess it could be possible to use L2S in a project now and then convert to EF when better support for stored procedures comes in. The same LINQ should work assuming you can recreate the model. Found it http://blogs.msdn.com/adonet/archive/2008/12/02/migrating-from-linq-to-sql-to-the-entity-framework-stored-procedures-for-data-retrieval.aspx

Tuesday, May 05, 2009

Entity Framework - Functions are a pain

As part of my on going project to use and learn the Entity Framework I have come across a bit of a problem. The Entity Framwork does not easily map database functions onto the model. At present there does not seem to be an easy way around this


Here are some of the links and tips I found
http://blogs.msdn.com/efdesign/archive/2008/10/08/edm-and-store-functions-exposed-in-linq.aspx

http://www.thedatafarm.com/blog/2007/12/19/ImplementSELECTStoredProceduresThatReturnMiscellaneousDataInCTP2OfEFDesigner.aspx

http://forums.asp.net/t/1363046.aspx

Tuesday, April 21, 2009

Dynamic Data - The ContextType property of control 'EntityDataSource1' must specify a data context.

If you ever get this type of error using Dynamic Data when creating you own Dynamic Data pages then you need to add to the markup the ContextTypeName with the fully qualified namesapace and class name of your entity context object eg.

asp:EntityDataSource ContextTypeName="YourModelNameSpace.YourEntitiesContext"

Monday, April 06, 2009

Cork talk on using CLR inside SQL Server

PJ Naughter, a Microsoft MVP, is coming to Cork on April 27 to do a talk on using .NET Common Language Runtime (CLR) inside SQL Server.

Register on the mtug web site register

Monday, March 30, 2009

ASP.NET - Dynamic Data - Read Only Column

This is a simple way to configure columns as Read Only when using Dynamic Data

Make two copies of the Text.ascx field template and name one ReadOnly.ascx and the other ReadOnly_Edit.ascx then annotate the field in your metadata partial class as follows

[UIHint("ReadOnly")]
public object UpdateDate;


This works fine as the UIHint uses the ReadOnly field template to display the column in display mode and the ReadOnly_edit template when in in edit mode. Both simply display the column using the aps:Literal control

Thursday, March 26, 2009

Dynamic Data Partial ClassGenerator

If you have ever done any .NET remoting in the past you would would have come across
Ingo Rammer part of the ThinkTecture Team. Ingo really knows his stuff and is a great speaker.

Anyway Ingo has created a small project which generates your metadata partial classes from your EF/L2S generated model. link

Its pretty handy. Give it a go

Thursday, March 19, 2009

Dynamic Data Templates in VS 2008

The Dynamic Data templates option in VS 2008 can be a bit confusing so here is an overview.

The “Dynamic Data Entities Web Site” template create an empty Entity Framework project and you use Add -> New -> ADO.NET Entity Data Model to add your data model to the project. The Dynamic Data routing is configured in the global.asax file.

If you want to control how Dynamic Data creates the UI and validates data input then add a new class using the System.ComponentModel.DataAnnotations name space. Example below

[MetadataType(typeof(Account_Metadata))]
public partial class Account
{
}

public partial class Account_Metadata
{

[ScaffoldColumn(false)]
public object UpdateDate { get; set; }

[StringLength(30)]
[Required(ErrorMessage = "Account name is required.")]
public object Description { get; set; }

The “Dynamic Data Web Site Wizard” template creates a Linq to Sql based project and the wizard guides you through the creation of the dd web site. As part of this you will get an option to create specific input forms for each entity.

It automatically creates a class named file YourModelDataContext.partial.cs which contains all the MetaData classes.

The routing info is in the class DynamicData.cs

Tuesday, March 10, 2009

Win an Xbox Elite or TechEd Plus subscription

If you attend the Microsoft community tech day this Thursday in Cork you will be in with the chance to win an Xbox Elite or one of two TechEd Plus subscription. The talks should be pretty good and will cover the following

Windows 7 & Windows Server 2008 R2 working Great Together and WS 2008 R2 Virtualisation
SharePoint - The Implementation Journey..from WSS to MOSS
Unified Communications - Using it Today and Preparing for the Future

Register on the Microsoft site below register

Thursday, February 12, 2009

Silverlight Materials

Richard Costall and Chris Hay have provided the source code from the Silverlight talks on the following link

http://silverlightuk.blogspot.com/2009/01/silverlight-assault-course-dublin-cork.html

Many thanks to Richard and Chris for coming to Cork - the feedback was great

Tuesday, February 10, 2009

Visual Studio Tips - Sara Ford

If you want some tips on using Visual Studio 2005 and 2008 visit Sara Ford's blog Sara Ford Blog

Tuesday, January 27, 2009

Entity Framework - Linq To SQL

There are lots of posts which seem to hint that Linq to SQL has gone down a cul de sac and will no longer be developed and that the Entity Framework is now the way to go. Article However I think L2S is great for small Sql Server based projects and I will probably still use it.

Here is a great Entity Framework FAQ page with lots of links Entity Framework FAQ

Tuesday, January 06, 2009

Silverlight Sunday 18th Jan MTUG Cork

MTUG Cork are having a full day of talks on Silverlight on Sunday 18th January. Richard Costall and Chris Hay are the speakers and will cover the following topics

XAP File Explored, Layout, Silverlight Sockets, WCF, Syling/Templating, Brushes, Transforms + Animation, DataBinding, Isolated Stored, Gaming, User Controls
Silverlight Encryption + Compression

This will be a great event if you are interested in learning about Silverlight

Attendance is free but please only register if you definitely attending
register