[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2017-02-22
4
Medium Priority
?
164 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
4 Comments
 
LVL 84

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 37

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
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.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

834 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