Martyn Bullerwell's blog

SQL Server 2012 Running Totals

Martyn Bullerwell — Wed, 16 Nov 2011 15:48:19 GMT

SQL server has sometimes come under fire with the Oracle vs SQL Server debate because of the lack of some of the more advanced (and less used) functionality that is outlined in the SQL ANSI standards. An example of this is Window Functions, which became an ANSI standard under the ANSI:2003 revision. A Window function is an aggregate function that can be applied to a subset of a full set of data. Now this can be achieved in current versions of SQL Server (2008 R2 and its predecessors), but not using Window Functions and therefore has a performance implication. There are about 3 or 4 approaches to achieving a running total in SQL Server prior to the 2012 version, however none particularly elegant.

For my following example I will outline how to perform a running total using AdvertureWorks2008 sample data. I will remind us of 1 of the method's (probably the most common) of how we used to do running totals prior to SQL Server 2012, and then show how to do the same running total using a SQL Server 2012 Window Function. To set the scene, we will be looking for a running total of Line Items for a given Order. This may be kind of query you may wish to write to generate an invoice with a running total on it.

To begin with lets look at the more traditional query:

SELECT
        A.SalesOrderID,
        A.SalesOrderDetailID,
        A.LineTotal,
        SUM(B.LineTotal)
FROM
Sales.SalesOrderDetail AS A
    CROSS JOIN Sales.SalesOrderDetail AS B
WHERE
    B.SalesOrderDetailID <= A.SalesOrderDetailID
        AND
    A.SalesOrderID = B.SalesOrderID
GROUP BY
    A.SalesOrderID,
    A.SalesOrderDetailID,
    A.LineTotal
ORDER BY
    A.SalesOrderID,
    A.SalesOrderDetailID,
    A.LineTotal

This simply works by self joining up to certain point, so works fine for any data that can be ordered easily, as running totals usually are this method usually suffices.

The following query uses the new Window function in SQL Server 2012:

SELECT
    SalesOrderID,
    SalesOrderDetailID,
    LineTotal,
    SUM(LineTotal)
        OVER     (PARTITION BY
                SalesOrderID
            ORDER BY
                SalesOrderDetailID) AS OrderRunningTotal
FROM
    Sales.SalesOrderDetail
ORDER BY
    SalesOrderID,
    SalesOrderDetailID,
    LineTotal

You will instantly notice that the second query is far more elegant, and more simplistic to understand. In short, in this query, we are telling SQL server to Sum the “Line Total” over an ordered partition of the data. Both queries return the same result, however it becomes interesting when we look at the execution plans, relative to each other. This is shown below (apologies for the size of these):

What is important to note that the second (bottom) plan is smaller, and simpler. the other, very significant point is that the Query Cost (relative to batch) is a whopping 97% for the old method of running totals, meaning that the new Windowing Functions are far more efficient.

In summary, I believe that this is an example of where SQL server is becoming a firm competitor to some of its perceived rivals, its one of the few points that can be raised as a valid point in the argument of SQL Server vs Oracle, but not any more!

Installing SharePoint 2007 on Windows Server 2008 R2

Martyn Bullerwell — Sat, 10 Oct 2009 22:07:42 GMT

Obviously when we get a new development machine, we install the latest Microsoft software in order to test the latest applications. All in all Windows Server 2008 R2, is basically the server version on Windows 7, therefore it shares many drivers, and many common issues with its counterpart. Therefore its no surprise that SharePoint 2007 does not install from disk to a Windows 7, or Windows Server 2008 R2 installation.

In order to install SharePoint, you need to create what is called a “Slipstream” version, which in essence means rolling up all the latest service packs into the installation. Something that Microsoft I'm sure will get round to releasing in due course. However in the meantime you can follow these simple instructions.

Download the following Service packs:

Copy all installation files from the installation disc to your local hard disk.
Remove EVERYTHING from the Updates Folder, from within the following directory: [ExtractedPath]\X64 or [ExtractedPath]\X32
Run the following from a command prompt:

wssv3sp2-kb953338-x64-fullfile-en-us.exe /extract:[ExtractedPath]\Updates /quiet
officeserver2007sp2-kb953334-x64-fullfile-en-us.exe /extract:[ExtractedPath]\Updates /quiet

Now Install SharePoint 2007 as you would have done before.

I would advise burning that back to a disk, so you don't have to do it in the future. You may still get a few compatibility issues, but it seems to install and run as expected.

PerformancePoint 2007 SP2 and SQL 2008 (Continued)

Martyn Bullerwell — Mon, 19 Jan 2009 23:36:04 GMT

Following on from my previous post prior to Christmas – it looks like I have been beaten to it. For a pure installation of SQL 2008 and PPS have a look here, it details the installation process to ensure a smooth installation. I have now tried this and it seems that installation works seamlessly, and all set up on SQL 2008. Hopefully I will get chance to continue testing.

Performance Point SP2 and SQL Server 2008

Martyn Bullerwell — Wed, 31 Dec 2008 11:39:57 GMT

Well, given the break over Christmas I have finally managed to get round to some testing. This has begun with getting PPS SP2 set up on our development environment, running solely on SQL Server 2008 and Windows 2008.

To test it fully I have removed everything from our test environment and started from scratch. Note that to install the latest version of SQL Server 2008, you must have Visual Studio SP1 and .NET 3.5 SP1 installed. If however, you do not have .NET 3.5 SP1 installed, the installation process will attempt to install it.

To install PerformancePoint you may need to use the SKIPREQCHECK command to ensure it will install. I installed all the planning components to ensure I could test all aspects fully. DO NOT run the configuration of any of these planning components until you have applied SP2.

Now install PPS SP2, ensuring you run all of the installers contained within the executable. Unfortunately the prerequisites check still does not seem to recognize later versions of the SQL components that are required, I had difficulty with ADOMD.NET, which still requires version 9 SP2, and even after installing version 10 from the SQL Server 2008 feature pack, it still didn't work. This is an issue as it is required for two of the main components of PerformancePoint Planning:

Planning Process Service
Planning Web Service

I found that the December 2008 SQL Server 2005 Feature Pack did the trick (can be found here), and allowed configuration of all four planning components.

Having installed PPS SP2 however, it still seems as though I can not get PerformancePoint to connect to Analysis Services 2008. The connection information does not fill me with joy, when is specifically mentions Analysis Services 2005.

It is Christmas, so I will do some more investigation and see if I can get past this issue, before the new year. More to come soon.

Reporting Services Parameters and Oracle

Martyn Bullerwell — Tue, 30 Dec 2008 15:03:35 GMT

Recently a colleague of mine needed to access Oracle to build a report but did not have the rights to create a stored procedure, which would be the obvious method of choice. We therefore need to pass parameters from Reporting Services to Oracle using the "Query Type" of text. To do this in SQL Server it is relatively intuitive.

Create a shared Data Source - I have used Adventure Works.

Then add a DataSet:

This query will return all records from the Adventure Works 'people' table, showing the following fields; FirstName, MiddleName, LastName and Email Address. If you wanted to restrict this list using a parameter, all you would need to do is add a parameter to the report.

In this instance I have added a parameter titled "EndsWith", so we can use a WHERE with any character or string to filter by the last characters of the FirstName. I have also added a parameter called "BeginsWith". Now you can edit the DataSet to add the where statement as follows:

SELECT     FirstName, MiddleName, LastName, EmailAddress
FROM         Person.Contact
WHERE     (FirstName LIKE @BeginsWith +'%' + @EndsWith)

Now this will work perfectly with SQL Server, however should you wish to connect to Oracle, you will need to make a few simple, but not obvious, changes. These are as follows:

SELECT     FirstName, MiddleName, LastName, EmailAddress
FROM         Person.Contact
WHERE     (FirstName LIKE :BeginsWith +'%' + :EndsWith)

So in short all you have to do is change the @ sign to a : (colon), easy when you know how, a real pain when you don't!

This report will appear as follows when connected to Oracle, or SQL (as long as there are the same databases).

Adventure Works Samples Update and FILESTREAM

Martyn Bullerwell — Sun, 30 Nov 2008 23:58:25 GMT

A bit late (released at the beginning of the month), but a follow up to post I made earlier this year, there is a new release of the Adventure Works Databases. These can now be found here. They are simplified into just 4 downloads, which makes things a lot easier! Also the install is a lot more straight forward, although for the install to complete you must have full text search and FILESTREAM installed and enabled. It is good to see that FILESTREAM is being used already, and Adventure Works can be used to test and demonstrate this new functionality.

What is FILESTREAM?

Prior to SQL Server 2008 if you needed to store images, videos, PDF's, or any other binary data we used a blob data type and it was in-line with relational data. Now SQL Server 2008 can store blobs in its own private namespace on the file system, which simplifies the storage of blob data. Adventure Works examples have now used it to store instructions in word doc files. (See table Production.Documents)

Enable FILESTREAM

Start -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.

In the list of services, right-click SQL Server Services, and then click Open.

Locate instance of SQL Server Right-click and click Properties.

Click the FILESTREAM tab.

Select the Enable FILESTREAM for Transact-SQL access check box.

If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

Click Apply.

In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Using FILESTREAM Databases

Create a new database is SSMS:

Under the "Filegroups" Tab add a new FILESTREAM:

Once set up click OK. This should now have added a new folder as follows under your SQL installation:

From here we can see that we have our new FileStreamTestData directory as well as documents folder, which has been added from the install of the SQL2008 AdventureWorks samples. So now we want to test the storage of some files in SQL. So lets try and store some Pictures for example. To begin with we will need to set up a new table. It is worth noting here that we must add a ROWGUIDCOL column, preferably the primary key, and the unique identifier.

CREATE TABLE [dbo].[Media] (
    [mediaId] [uniqueidentifier] NOT NULL ROWGUIDCOL PRIMARY KEY,
    [dateCreated] [datetime] NOT NULL DEFAULT(GETDATE()),
    [fileName] [nvarchar](256) NOT NULL,
    [location] [geometry] NULL,
    [file] [varbinary](max) FILESTREAM);
GO

This will give us a new table, that we can insert into. So If we insert an dummy row and keep an eye on the folders created when creating this new filestream. Execute the following statement:

INSERT INTO [FileStreamTest].[dbo].[Media]
    (
        [mediaId]
        ,[fileName]
        ,[file]
    )
    VALUES
        (NEWID(),
        'NewFile.jpg',
        convert(varbinary(max), 'Not a real Picture, but proves a point'))

You will now notice that a new file has been added to the file system.

The more rows we insert, the more files are added to this directory. You will also notice that the date time stamps on the files are also reflected by when the items were added to the database. If a row is updated with a new image, the new image is created, however the old image remains. This also applies when the row is deleted. However, these directories are cleaned up periodically by the FILESTREAM garbage collector.

So, in short it seems that this will become the way forward for storing all sorts of files in your database that can be centrally stored, managed and backed up. A Simple application could be easily written to save, update and retrieve images through SQL Server 2008, using file stream data.

Technorati Tags: SQL Server 2008,AdventureWorks

, FILESTREAM

So where is Microsoft Master Data Management (MDM)?

Martyn Bullerwell — Tue, 25 Nov 2008 22:17:57 GMT

Well, back in June 2007, Microsoft acquired Stratature, who had a product named +EDM. +EDM, was a master data hub with a web UI, business rules, simple human workflow and notification, entity and hierarchy management, versioning, transaction logging, and an open subscription interface. Microsoft has not continued a release of +EDM, but has continued support for existing customers. In November a technology preview was released to those who applied and were accepted onto the program. Well since then we have seen a CTP on Connect, which Jamie explored here. This Blog was written back in March 2008, and since then Microsoft has acquired yet another company called Zoomix, who focuses on MDM data quality. Information on this acquisition, and what Zoomix is can be found here. The hope is that all of this will be integrated into Microsoft MDM.

The official release plan was as follows:

Technology Preview - February, 2008 to a select group of customers. Near identical functionality to Stratature release (actually released earlier than that, in November 2007).
TAP - Program commenced first half of 2008.
CTP - First CTP release available Q3, 2008.

The codename for Microsoft MDM is "Bulldog", and it seems that Microsoft MDM will ship in Microsoft SharePoint - Office 14 Release which is currently due for release late 2009, early 2010, around the same time as Windows 7. The codename for the subsequent release of the Microsoft MDM product is "Greenwich". Apparently major new features will be released in the "Greenwich" timeframe, including integration, analytical & transactional capabilities. But for the mean time let’s see how “Bulldog” does.

I will continue to keep an eye on MDM, from here on in, although it might be a long wait until you all get your hands on it.

PerformancePoint Server SP2

Martyn Bullerwell — Wed, 12 Nov 2008 21:54:50 GMT

A good few months ago I wrote about SP1 for PerformancePoint and getting this working on SQL Server 2008. There were a number of attempts, and with a great deal of hacking, I got an untested environment set up, however this was not very stable and never fully tested. SP2 is due for release in December, and there are mentions of a Beta available on the web, although unavailable through MSDN, or Connect. It seems that the two promises of SP2 are Hyper-V compatibility and SQL Server 2008 integration.

A few good resources for this are as follows:

http://hmorgenstern.spaces.live.com/Blog/cns!28A6BE83102A0EB3!419.entry and http://performancepointing.blogspot.com/2008/11/performance-point-server-sp2-pre.html

SQL Server 2005 Service Pack 3

Martyn Bullerwell — Fri, 31 Oct 2008 15:51:26 GMT

SQL Server 2005 SP3 is just a Beta at this stage, however it is available for download here, and it includes all the patches released to date:

http://www.microsoft.com/downloads/details.aspx?FamilyId=D22317E1-BC64-4936-A14B-7A632B50A4CA&displaylang=en

Not a huge amount of fixes, but a few for Reporting Services that caught my eye:

Optimised Sharepoint Integration, to make reports respond faster.
Connector to Teradata
Enhanced PDF rendering

On the subject of service packs, it has now been released that PerformancePoint Server SP2 "should" support SQL Server 2008, lets hope we get our hands on this soon.

Connection Strings in PPS Monitoring

Martyn Bullerwell — Fri, 31 Oct 2008 01:45:25 GMT

ODBC connection string in PPS Monitoring can become a little awkward, therefore I thought it wise to write a quick blog post with various connection strings in for a quick reference.

Using ODBC connection strings, you can connect to many data sources including: SQL Server, Access, Oracle, Excel etc. Lets remember that PPS monitoring is best used when connection to multidimensional data sources, and the built in reports can use only Multidimensional sources. KPI's can however use these connection strings and therefore, it is often a requirement to connect to relational stores. Here are a few examples of connections strings

SQL Server: Driver = {SQL Server}; Server=SERVERNAME; Database=ADVENTUREWORKS
Access: Driver= {Microsoft Access Driver (*.mdb)}; dbq=c:\SourceLocation\SourceAccessDB.mdb; uid=USERNAME; pwd=PASSWORD;
Oracle: Driver= {Microsoft ODBC for Oracle}; server=SERVER; uid=USERNAME; pwd=PASSWORD;
Excel: Driver={Microsoft Excel Driver (*.xls)}; dbq=C:\SOURCELOCATION\WORKBOOK.xls; ReadOnly=True;

Slow report in Reporting Services, even though the stored procedure runs so quickly?

Martyn Bullerwell — Wed, 27 Aug 2008 16:52:03 GMT

Apologies for the long title, however it had to be said. I have been working with Reporting Services recently, with very complex reports, not from a reporting services point of view, but from a T-SQL stored procedure aspect. The query in question is over 1500 lines long, and needs to perform well when the report is run. So with a little bit of performance tuning we managed to get said stored procedure to run in under 4 seconds (on a fairly powerful box). However as soon as this was run through Reporting Services, the performance ground to a halt. We are talking about 1 minute 30 seconds to run this report. So after a little help from my friends (thanks to Tim for finding this blog) we found that this was the exact problem.

So - its all down to "Parameter Sniffing" !! But what on earth is parameter sniffing, and how does it help/hinder us?

As taken from a Microsoft white paper:

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation.

Just a bit of "Googling" will get you many more resources on the subject, and well worth reading.

SQL Server 2008 Intellisense

Martyn Bullerwell — Tue, 26 Aug 2008 20:44:32 GMT

One of the under mentioned, but long awaited feature of SQL Server 2008 is intellisense. Up to now it has always been add on available from companies like RedGate, called SQL Prompt, or SQL Assist. To get it working is SQL 2008; you pretty much have to do nothing, however sometimes it doesn't come up without pressing Ctrl + Space. So it works well with T-SQL, however does it work with MDX? In short - not really, when writing an MDX query in SSMS, you do get some intellisense prompts, however none of which are representations of the data within the cube. However you do get keyword prompts, which could be useful. So next stop, full intellisense for MDX queries - hopefully.

For those of you who haven't yet used SQL 2008, here are some screen shot of intellisense in action.

Technorati Tags: SQL Server 2008,Intellisense

Exploring the PerformancePoint Planning Web Service Part 2

Martyn Bullerwell — Sat, 23 Aug 2008 13:38:02 GMT

Sorry for those who have been waiting for this, but here it finally is, Part 2 of using the planning web service. In Part 1 we looked at the Web services exposed by PerformancePoint Planning. We also looked at the difficulties of de-serializing the XML, into the the object that are generated from the WSDL. Today we will look into how to get the BizSystem information from the web service.

Firstly lets remind ourselves what we are looking to get from the web service.

As we can see form XML document we have a node called Applications which contains an ArrayOfBizApplications, which contains, in this case 2 BizApplications. In turn if we try and use the object created from the WSDL in code, we find that we have an object called BizSystem. Lets have a look at this object.

public partial class BizSystem : MetadataObjectBase {
     
     private System.Data.DataSet applicationsField;
     
     /// <remarks/>
     public System.Data.DataSet Applications {
         get {
             return this.applicationsField;
         }
         set {
             this.applicationsField = value;
         }
     }
 }

public abstract partial class MetadataObjectBase {
       
       private string nameField;
       
       private string descriptionField;
       
       private IdRef bizTypeIdRefField;
       
       private System.Guid idField;
       
       private string labelField;
       
       private System.Guid parentIdField;
       
       /// <remarks/>
       public string Name {
           get {
               return this.nameField;
           }
           set {
               this.nameField = value;
           }
       }
       
       /// <remarks/>
       public string Description {
           get {
               return this.descriptionField;
           }
           set {
               this.descriptionField = value;
           }
       }
       
       /// <remarks/>
       public IdRef BizTypeIdRef {
           get {
               return this.bizTypeIdRefField;
           }
           set {
               this.bizTypeIdRefField = value;
           }
       }
       
       /// <remarks/>
       [System.Xml.Serialization.XmlAttributeAttribute()]
       public System.Guid Id {
           get {
               return this.idField;
           }
           set {
               this.idField = value;
           }
       }
       
       /// <remarks/>
       [System.Xml.Serialization.XmlAttributeAttribute()]
       public string Label {
           get {
               return this.labelField;
           }
           set {
               this.labelField = value;
           }
       }
       
       /// <remarks/>
       [System.Xml.Serialization.XmlAttributeAttribute()]
       public System.Guid ParentId {
           get {
               return this.parentIdField;
           }
           set {
               this.parentIdField = value;
           }
       }
   }

As we can see, the BizSystem contains one property, that is BizApplication, which is quite simply a DataSet. We can see from the XML that the Biz Application is unlikely to be able to be de-serialized into a dataset, and when we do try and access the dataset, we do not get any data tables contained within the dataset, nor any data from the XML.

private void LoadBizSystem()
      {
          MetaDataManager.MetadataManagerWebService ppsMDM = new MetaDataManager.MetadataManagerWebService();

          System.Net.CredentialCache myCredentials = new System.Net.CredentialCache();
          NetworkCredential netCred = new NetworkCredential("Administrator", "LetMeIn", "AServer");
          myCredentials.Add(new Uri(ppsMDM.Url), "Basic", netCred);
          ppsMDM.Credentials = myCredentials;

          MetaDataManager.BizSystem bizSystem = ppsMDM.GetSystem(false);

          DataSet ds = bizSystem.Applications;
          DataTable dt = ds.Tables[0];
      }

Although not necessarily the best way, what we can do is write a custom class that the XML can be de-serialized to, thus giving us the data from the Biz System. To begin with we need to create an object that will allow us to de-serialize the BizSystem into it, and therefore allowing us to access the properties of the BizSystem. From the XML we can work out that there are a number of attributes, and to keep this simple we will just prove that we can get the data from the BizSystem; these are:

Name
Id
Label
ParentId

Creating this object will allow us to retrieve the the attributes of a BizApplication. The object can be created as follows:

[XmlRoot("BizApplication")]
    [Serializable]
    public class BizApplication
    {
        private string element_Name;
        private string attribute_id;
        private string attribute_Label;
        private string attribute_ParentId;

        [XmlElement("Name")]
        public string Name
        {
            get { return element_Name; }
            set { element_Name = value; }
        }

        [XmlAttribute("Id")]
        public string Id
        {
            get { return attribute_id; }
            set { attribute_id = value; }
        }

        [XmlAttribute("Label")]
        public string Label
        {
            get { return attribute_Label; }
            set { attribute_Label = value; }
        }

        [XmlAttribute("ParentId")]
        public string ParentId
        {
            get { return attribute_ParentId; }
            set { attribute_ParentId = value; }
        }

    }

This object will allow us to gain access the the 4 attributes named above, however, the BizApplication are is contained in an array of applications, so we need to create another object that contains an array of BizApplications. Also in this object we can embed the de-serialization routine, and create a method that takes an XML Document of BizSystem, to de-serialize. So we need to get the XML returned from the web service, as opposed to the attempted de-serialized version. So we need to alter what the call to biz system returns. Now this is a bit of a hack, however I can assure you that it does work. We need to change the object that is returned from the web service, so if we go to the definition of Biz System, and alter the reference so it returns an XmlNode[].

[System.CodeDom.Compiler.GeneratedCodeAttribute("System.Xml", "2.0.50727.3031")]
   [System.SerializableAttribute()]
   [System.Diagnostics.DebuggerStepThroughAttribute()]
   [System.ComponentModel.DesignerCategoryAttribute("code")]
   [System.Xml.Serialization.XmlTypeAttribute(Namespace="http://www.microsoft.com/PerformancePoint/MetadataManager")]
   public partial class BizSystem : MetadataObjectBase {

       private System.Xml.XmlNode[] applicationsField;
       
       /// <remarks/>
       public System.Xml.XmlNode[] Applications
       {
           get {
               return this.applicationsField;
           }
           set {
               this.applicationsField = value;
           }
       }
   }

So now we have the object returned as an XmlNode, so its time to write the ArrayOfBizApplications object, and the de-serialize method.

[Serializable]
    [XmlRoot("ArrayOfBizApplication")]
    public class ArrayOfBizApplication
    {
        private BizApplication[] element_BizApplicationList;
        
        [XmlElement("BizApplication")]
        public BizApplication[] BizApplication
        {
            get { return element_BizApplicationList; }
            set { element_BizApplicationList = value; }
        }

        public static ArrayOfBizApplication LoadApplications(BizSystem bizSystem)
        {
            //Get the Applicaiotn list as an object. 
                Object bizApplication = null;
                ArrayOfBizApplication arrayOfBizApplicaion = null;
                try
                {
                    bizApplication = bizSystem.Applications;
                }
                catch (Exception err)
                {
                    //Log Exceltion here.. 
                    //Try and Cast it! (Untested)
                    bizApplication = (Object)bizSystem.Applications;
                }

                //We know this is an XML object - it has come back from the webservice
                XmlNode[] xmlNodes = (XmlNode[])bizApplication;

                StringBuilder sb = new StringBuilder();

                sb.Append("<ArrayOfBizApplication>");
                //We Only want an array of BizApplicaiotoins
                foreach (XmlNode xmlNode in xmlNodes)
                {
                    if (xmlNode.Name.ToLower() == "arrayofbizapplication")
                    {
                        sb.Append(xmlNode.InnerXml.ToString());
                    }
                }
                sb.Append("</ArrayOfBizApplication>");

                XmlSerializer serializer;

                System.IO.StringReader sr = new System.IO.StringReader(sb.ToString());
                XmlTextReader xmlTReader = new XmlTextReader(sr);

                try
                {
                    serializer = new XmlSerializer(typeof(ArrayOfBizApplication));
                    arrayOfBizApplicaion = (ArrayOfBizApplication)serializer.Deserialize(xmlTReader);
                }
                catch (Exception err)
                {
                    string test = err.Message;
                    throw err;
                }

                finally
                {
                    //Close the reader. 
                    xmlTReader.Close();
                }
                // return the list of BizApplication.
            return arrayOfBizApplicaion;
            }
    }

We finally have some data from the Get System, we can browse our objects, which has some basic data within them.

Upon more investigation and examination of the XML that is returned you can easily extend these objects to return as much data as you require from Biz System. If you have any questions, or have had more success with the Planning Web service please contact me. For now, I hope to make more progress with the web service over the coming months.

Reporting Services 2008 - Connection Problems

Martyn Bullerwell — Wed, 30 Jul 2008 23:05:37 GMT

Having recently rebuilt a server entirely with "2008" technology I have a small problem with reporting services. When trying to look at the Reports, I received the error:

"The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel."

I found, that even though we are not trying to look at the secure site, the certificate causes issues. For development and testing purposes I found it easiest to remove the certificate. To do this perform the following steps:

1. Open Reporting Services Configuration Manager

2. Click Web Service URL and click "Advanced"

3. Remove the SSL certificates, both for IPv4 and IPv6 and click ok, and then Apply. This will remove the SSL certificates, you should now be able to use Reporting Services 2008.

AdventureWorks for SQL Server 2008

Martyn Bullerwell — Mon, 28 Jul 2008 22:55:20 GMT

For those of you who wish to learn SQL Server 2008, The Adventure Works databases are available for download here for some of the same data we are familiar with: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=14274

There are a number of downloads available, so choose carefully. There are 4 main versions, however I would recommend that you download the full Zipped version, which will give you all four of the AdventureWorks databases, it is around an 85mb download. If you download the MSI version, it does not install and configure the databases for you; however the files are placed here: C:\Program Files\Microsoft SQL Server\100\Tools\Samples. Once downloaded, there are a few ways to load the samples, the easiest being restoring the .BAK file to SQL, remember to do this for both DW and the OLTP AdventureWorks Databases. For the Cube, you get a project that opens in SQL Server 2008, and from my experience simply deploys (as long as you have set up security correctly).

SQL2008.AdventureWorks_DB_Scripts_v [2005|2008].[ia64.msi|x64.msi|x86.msi|zip]: T-SQL scripts and CSV files for loading all four databases in the AdventureWorks family of databases and diagrams for one year-model, either v2005 or v2008.

SQL2008.AdventureWorks_DW_BI_v[2005|2008].[ia64.msi|x64.msi|x86.msi|zip]: T-SQL scripts, CSV files and database backups for loading the AdventureWorks Data Warehouse database, diagrams and Visual Studio/BIDS projects for Analysis Services projects for business intelligence samples for one year-model, either v2005 or v2008.

SQL2008.AdventureWorks_LT_DB_v[2005|2008].[ia64.msi|x64.msi|x86.msi|zip]: T-SQL scripts and diagrams for the AdventureWorks "Lite" database for one year-model, either v2005 or v2008.

SQL2008.AdventureWorks_OLTP_DB_v[2005|2008].[ia64.msi|x64.msi|x86.msi|zip]: T-SQL scripts, CSV files and database backups for loading the AdventureWorks OLTP database and diagrams for one year-model, either v2005 or v2008.

Technorati Tags: SQL Server 2008,AdventureWorks