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();
        }
    }
}


April 10, 2008

Resetting Collation in SQL Server 2005

Filed under: SQL Server — Tags: — dfbaskin @ 7:02 am

I accidentally installed a development version of SQL Server 2005 with a case-sensitive collation. This upset other SQL databases I was working with that were not expecting table names and etc. to be case sensitive. In SQL Server 2005, you can reset the collation by using setup with the REBUILDDATABASE parameter.

Details about this process are here.  Details about setting the collation are here.

The following command (one line) worked for me:

start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER
     REINSTALL=SQL_Engine
     REBUILDDATABASE=1
     SAPWD=password
     SQLACCOUNT=doman\user SQLPASSWORD=password
     SQLBROWSERACCOUNT=doman\user SQLBROWSERPASSWORD=password
     SQLAGTACCOUNT=doman\user SQLAGTPASSWORD=password
     SQLCOLLATION=Latin1_General_CI_AS_KS_WS
     ASCOLLATION=Latin1_General_CI_AS_KS_WS

(I originally tried this from the “\Program Files\Microsoft SQL Server\90\Setup Bootstrap” directory, but this generated an error saying an .MSI file was not found. But it worked fine from the original installation media.)

However, there was a problem trying to reapply SP2 (found here). There have been additional updates since SP2 was released (described in detail here), which Windows Update had already applied. Therefore, I could not apply the SP2 updates since components were already at a later version.

From here, I assume you have two options:

  1. Uninstall SQL Server altogether, reinstall, and apply the service pack. This, of course, defeats the purpose of using the REBUILDDATABASE option in the first place.
  2. Request the cumulative service pack update (described here) and apply it.

I ended up going with the first option.

Create a free website or blog at WordPress.com.