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
----------------------------------------
Ken HaynesSoftware Developer/Systems AnalystAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Shaun VermaakConnect With a Mentor Technical Specialist/DeveloperCommented:
System.IO.FileLoadException: Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo, Version=12.0.0.0,

Download and install SharedManagementObjects.msi and DB2OLEDBV5_x64.msi from http://www.microsoft.com/en-us/download/details.aspx?id=42295
1
 
David Johnson, CD, MVPOwnerCommented:
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
0
 
HuaMinChenBusiness AnalystCommented:
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.
0
 
Ken HaynesSoftware Developer/Systems AnalystAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.