Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2017-02-22
4
Medium Priority
?
135 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 83

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 36

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this Micro Tutorial viewers will learn how they can get their files copied out from their unbootable system without need to use recovery services. As an example non-bootable Windows 2012R2 installation is used which has boot problems.
In this Micro Tutorial viewers will learn how to restore their server from Bare Metal Backup image created with Windows Server Backup feature. As an example Windows 2012R2 is used.

650 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