Second Stanza

October 23, 2008

SQL Server 2005 Installation Problem

Filed under: SQL Server — Tags: — dfbaskin @ 11:36 am

For some reason, when running the SQL Server 2005 Upgrade Advisor (either the stand-alone version or the version that runs within the installation), I was getting the following error:

SQL BPA command line has encountered a problem and needs to close.

The problem, it turns out, is that BPACMD.EXE was not able to load BPAClient.dll for some reason. The work around was to copy the BPAClient.dll to a location that allowed BPACMD.EXE to load it. The file resides in a “bin” directory below a “BPA” directory. I simply created a “BPA\BPAClient” directory and copied BPAClient.dll into this directory. (The reason this works is that the .NET runtime will search this directory when trying to load BPAClient.dll.)

Note that you must copy this file after the SQL Server support files have been installed (usually to the Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA directory).

A forum discussion of this topic is here.

I am not sure the reason this intervention is required, but I suspect it has something to do with the installation of the .NET 3.5 SP1 runtime.

October 19, 2008

Using the Report Viewer Component with .RDL Files

Filed under: .NET Examples, SQL Server — Tags: , — dfbaskin @ 9:30 pm

You can use Visual Studio to create Report Definition Language (.RDL) files that can be used within SQL Server Reporting Services. The editor for .RDL files allows you to put together the SQL Query, design the report layout, and preview the report all within Visual Studio.

Microsoft has also provided a Report Viewer control that allows you to display reports within your application. This control is not tied to SQL Server since you can use your own data sources as input to the report (though it can also display reports that originate from SQL Server Reporting Services). Reports are defined using the .RDLC format, which is the same format as .RDL files, but ignores certains aspects of the definitions (more details here).

Of course, since a data source can be defined within a Visual Studio project, you lose the ability to preview your report easily. Generally, you have to compile and run the application in order to view your report and verify that it is correct. Being able to quickly view your report output was a nice feature of working with the SQL Server Reporting Services version of the report editor.

However, since the .RDL and .RDLC formats are the same, you can use an .RDL specification as input to the Report Viewer control. All you need to do is to implement some code that runs the query embedded in the .RDL file. By doing so, you may use the Visual Studio 2005 version of the Reporting Services editor (with its handy preview mode) but still use the Report Viewer component within your application.

Here’s source code that shows this technique:



using System;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.XPath;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Reporting.WinForms;

namespace ReportView
{
    static class Program
    {
        [STAThread]
        static void Main( string[] argList )
        {
            try
            {
                if( argList.Length != 1 )
                    throw new ArgumentException( "Must specify name of .RDL file." );
                string rdlFileName = argList[0];
                if( !File.Exists( rdlFileName ) )
                    throw new FileNotFoundException( "Could not find .RDL file." );

                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault( false );
                Application.Run( new ReportViewForm( rdlFileName ) );
            }
            catch( Exception ex )
            {
                StringBuilder errMsg = new StringBuilder();
                errMsg.Append( "Application Error generated:\n" );
                for( Exception x = ex; x != null; x = x.InnerException )
                    errMsg.AppendFormat( "   {0} - {1}\n", x.GetType().FullName, x.Message );
                errMsg.AppendFormat( "Stack Trace:\n{0}", ex.StackTrace );
                MessageBox.Show( errMsg.ToString(), "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
            }
        }
    }

    class ReportViewForm : Form
    {
        private Microsoft.Reporting.WinForms.ReportViewer rptView;
        private string reportFileName;

        public ReportViewForm( string fileName )
        {
            reportFileName = fileName;
            rptView = new Microsoft.Reporting.WinForms.ReportViewer();

            SuspendLayout();

            rptView.Dock = System.Windows.Forms.DockStyle.Fill;
            rptView.Name = "rptView";
            rptView.TabIndex = 0;
            Controls.Add( rptView );

            const int border = 20;
            System.Drawing.Size winSize = Screen.PrimaryScreen.WorkingArea.Size;
            Location = new System.Drawing.Point( border, border );
            Size = new System.Drawing.Size( winSize.Width - ( 2 * border ), winSize.Height - ( 2 * border ) );
            StartPosition = FormStartPosition.Manual;
            Name = "ReportViewForm";
            Text = Path.GetFileName( reportFileName );

            ResumeLayout( false );
        }

        const string ReportDefinitionNS = "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition";
        const string ReportDesignerNS = "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";

        protected override void OnLoad( EventArgs e )
        {
            base.OnLoad( e );

            rptView.ProcessingMode = ProcessingMode.Local;
            using( Stream readStream = new FileStream( reportFileName, FileMode.Open, FileAccess.Read, FileShare.Read ) )
                rptView.LocalReport.LoadReportDefinition( readStream );

            XPathDocument xpDoc;
            using( Stream readStream = new FileStream( reportFileName, FileMode.Open, FileAccess.Read, FileShare.Read ) )
                xpDoc = new XPathDocument( readStream );

            ConnectionStringSettings connSett = ConfigurationManager.ConnectionStrings["Default"];
            using( SqlConnection sqlConn = new SqlConnection( connSett.ConnectionString ) )
            {
                sqlConn.Open();

                XPathNavigator xpNav = xpDoc.CreateNavigator();
                XPathExpression xpe = xpNav.Compile( "/r:Report/r:DataSets/r:DataSet" );
                XmlNamespaceManager nsMgr = new XmlNamespaceManager( xpNav.NameTable );
                nsMgr.AddNamespace( "r", ReportDefinitionNS );
                nsMgr.AddNamespace( "rd", ReportDesignerNS );
                xpe.SetContext( nsMgr );
                foreach( XPathNavigator xpNode in xpNav.Select( xpe ) )
                {
                    string dsName = Convert.ToString( xpNode.Evaluate( "string( @Name )", nsMgr ) );
                    string cmdTxt = Convert.ToString( xpNode.Evaluate( "string( r:Query/r:CommandText )", nsMgr ) );
                    if( String.IsNullOrEmpty( dsName ) || String.IsNullOrEmpty( cmdTxt ) )
                        throw new Exception( "Could not obtain query for report definition." );

                    SqlCommand sqlCmd = sqlConn.CreateCommand();
                    sqlCmd.CommandType = CommandType.Text;
                    sqlCmd.CommandText = cmdTxt;

                    DataTable dataTable = new DataTable();
                    SqlDataAdapter sqlAdapter = new SqlDataAdapter( sqlCmd );
                    sqlAdapter.Fill( dataTable );
                    rptView.LocalReport.DataSources.Add( new ReportDataSource( dsName, dataTable ) );
                }
            }

            rptView.RefreshReport();
        }
    }
}


October 13, 2008

Reset Windows TCP/IP Stack

Filed under: Windows OS — Tags: , — dfbaskin @ 3:49 pm

A Norton 360 installation hosed the TCP/IP stack on a Windows XP machine, disabling any network activity. After some research, I found that running the following commands and rebooting the machine fixed the problem:

netsh int ip reset resetlog.txt
netsh winsock reset

Blog at WordPress.com.