?
Solved

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

Posted on 2017-02-22
4
Medium Priority
?
114 Views
Last Modified: 2017-02-23
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
----------------------------------------
0
Comment
Question by:Ken Haynes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 82

Expert Comment

by:David Johnson, CD, MVP
ID: 42020349
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
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 42020398
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
 
LVL 34

Accepted Solution

by:
Shaun Vermaak earned 2000 total points
ID: 42020449
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
 

Author Comment

by:Ken Haynes
ID: 42021629
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

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this Micro Tutorial viewers will learn how to use Boot Corrector from Paragon Rescue Kit Free to identify and fix the boot problems of Windows 7/8/2012R2 etc. As an example is used Windows 2012R2 which lost its active partition flag (often happen…
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question