Link to home
Start Free TrialLog in
Avatar of Ken Haynes
Ken HaynesFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Unable to connect C# program to an SQL database - Exception occurs.

Hi,

I am working on a program which runs on Windows Server 2012 R2. The programming language is C#, using the .NET 4.5 environment. I am having an issue getting the program to communicate with Microsoft SQL Express 2012.

The section of code shown below works perfectly on our test server, but when I run it on our production server, I get an error (see code and search for ***). I have researched this for hours, but to no avail. Does anyone here have any ideas?

I am not sure if this provides any clues, but when this code is run on a different test server, running Windows Server 2008 R2 Standard SP1, utilizing SQL Express 2008, I get a different message (see code and search for ***).

As you can see by my debug code, I have checked to ensure that all of the strings I use, are formatted correctly.

Many thanks for taking the time to look at this issue for me.

Regards,
Ken.


       private bool restoreDatabase()
        {
            string databaseName = sysInfo.client;
            string backupFile = sysInfo.backupDrive + ":\\" + Constants.PATH_BAK + "\\";

            string latestFile = getLatestFile(backupFile, Constants.RESTORE_FILENAME_DESIGNATION);

            if (latestFile == null)
            {
                writeToLog("No restore (.bak) files were found in the directory '" + backupFile + "'.", Constants.LOG_ERROR, false);

                lblRestoreDatabaseResult.ForeColor = Color.Red;
                lblRestoreDatabaseResult.Text = "Error Restoring Database";

                return false;
            }

            backupFile += latestFile;

            writeToLog("The backup file used for the database restore is '" + latestFile + "'.", Constants.LOG_INFORMATION, false);

            // Add latest .bat file to backupFile

            ServerConnection connection = null;
            Restore rstDatabase = null;
            BackupDeviceItem bkpDevice = null;
            Server sqlServer = null;

            try
            {
                string connectionString = "Server=" + sysInfo.server_server_name + ";Integrated security=SSPI;database=" + sysInfo.client + ";";

                debug("Connection String: " + connectionString);

                SqlConnection sqlConnection = new SqlConnection(connectionString);

                debug("sqlConnection OK");

                connection = new ServerConnection(sqlConnection);

                debug("ServerConnection OK");

				
				//
				// THE SQL EXPRESS 2008 ERROR OCCURRS HERE, AND READS: "System.NullReferenceException: Object reference not set to an instance of an object."
				//
				// THE ERROR WHICH PRODUCES THE Microsoft.SqlServer.ConnectionInfo MESSAGE, OCCURS SOMEWHERE IN THIS REGEON.
				//
				
				
                sqlServer = new Server(connection); // *** SQL EXPRESS 2008 ERROR ***

				
				
                debug("New Server created"); // *** THIS LINE IS NEVER REACHED ***
				
				

				// sqlServer.ConnectionContext.NonPooledConnection = true;

                rstDatabase = new Restore();

                rstDatabase.Action = RestoreActionType.Database;

                debug("Database Name: " + databaseName);
                debug("Backup File: " + sysInfo.server_user_password);

                rstDatabase.Database = databaseName;

                bkpDevice = new BackupDeviceItem(backupFile, DeviceType.File);

                rstDatabase.Devices.Add(bkpDevice);

                rstDatabase.ReplaceDatabase = true;

                rstDatabase.SqlRestore(sqlServer);
            }
            catch (Exception ex)
            {
                lblRestoreDatabaseResult.ForeColor = Color.Red;
                lblRestoreDatabaseResult.Text = "Error Restoring Database";

                writeToLog("An exception occurred during the database restore procedure. The exception reads: " + ex.ToString(), Constants.LOG_ERROR, false);

                return false;
            }
            finally
            {
                sqlServer.ConnectionContext.Disconnect();
                connection.Disconnect();
            }

            lblRestoreDatabaseResult.ForeColor = Color.DarkGreen;
            lblRestoreDatabaseResult.Text = "Database successfully restored";

            return true;
        }

Open in new window


The exception details for the Microsoft.SqlServer.ConnectionInfo are:

************** Exception Text **************

System.IO.FileLoadException: Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Invalid pointer (Exception from HRESULT: 0x80004003 (E_POINTER))
File name: 'Microsoft.SqlServer.ConnectionInfo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' ---> System.ArgumentNullException: String reference not set to an instance of a String.
   at System.Reflection.RuntimeAssembly.GetResource(RuntimeAssembly assembly, String resourceName, UInt64& length, StackCrawlMarkHandle stackMark, Boolean skipSecurityCheck)
   at System.Reflection.RuntimeAssembly.GetManifestResourceStream(String name, StackCrawlMark& stackMark, Boolean skipSecurityCheck)
   at System.Reflection.RuntimeAssembly.GetManifestResourceStream(String name)
   at NewClientInstall.fmMain.<.ctor>b__3_0(Object sender, ResolveEventArgs args)
   at System.AppDomain.OnAssemblyResolveEvent(RuntimeAssembly assembly, String assemblyFullName)
   at NewClientInstall.fmMain.restoreDatabase()
   at NewClientInstall.fmMain.btnStart_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.NET/Framework64/v4.0.30319/mscorlib.dll
----------------------------------------
NewClientInstall
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///D:/temp/Root/NewClientInstall.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Configuration
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Core
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
----------------------------------------
System.Xml
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Data
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_64/System.Data/v4.0_4.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

if you are not using a domain account to connect to the other server then you have to use SQL Server authentication. Check your connection strings. It is not connecting so it returns a NULL
You can try to put this
	<connectionStrings>
		<add name="conn2" connectionString="Data Source=192.168.?.?\your_db_instance;Initial Catalog=your_db_schema;Integrated Security=False;User ID=your_db_login;Password=??????" providerName="System.Data.SqlClient"/>
	</connectionStrings>

Open in new window

within Web.config/app.config file in your project, to ensure the project will work fine with the database.
ASKER CERTIFIED SOLUTION
Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ken Haynes

ASKER

Thank you for your input guys. It was Shaun's suggestion which did the trick.

There were a couple of prerequisites required for the two packages, so for completeness, I will list all of the required packages below.

In order of installation:

vcredist_x64.exe
DB2OLEDBV5_x64.msi
SQLSysClrTypes.msi
SharedManagementObjects - 4.msi

All available from microsoft.com.

Thanks for taking the time to make these suggestions, it is very much appreciated.

Regards,
Ken.