Ken Haynes
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.
The exception details for the Microsoft.SqlServer.Connec tionInfo are:
************** Exception Text **************
System.IO.FileLoadExceptio n: Could not load file or assembly 'Microsoft.SqlServer.Conne ctionInfo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd808 0cc91' or one of its dependencies. Invalid pointer (Exception from HRESULT: 0x80004003 (E_POINTER))
File name: 'Microsoft.SqlServer.Conne ctionInfo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd808 0cc91' ---> System.ArgumentNullExcepti on: String reference not set to an instance of a String.
at System.Reflection.RuntimeA ssembly.Ge tResource( RuntimeAss embly assembly, String resourceName, UInt64& length, StackCrawlMarkHandle stackMark, Boolean skipSecurityCheck)
at System.Reflection.RuntimeA ssembly.Ge tManifestR esourceStr eam(String name, StackCrawlMark& stackMark, Boolean skipSecurityCheck)
at System.Reflection.RuntimeA ssembly.Ge tManifestR esourceStr eam(String name)
at NewClientInstall.fmMain.<. ctor>b__3_ 0(Object sender, ResolveEventArgs args)
at System.AppDomain.OnAssembl yResolveEv ent(Runtim eAssembly assembly, String assemblyFullName)
at NewClientInstall.fmMain.re storeDatab ase()
at NewClientInstall.fmMain.bt nStart_Cli ck(Object sender, EventArgs e)
at System.Windows.Forms.Contr ol.OnClick (EventArgs e)
at System.Windows.Forms.Butto n.OnClick( EventArgs e)
at System.Windows.Forms.Butto n.OnMouseU p(MouseEve ntArgs mevent)
at System.Windows.Forms.Contr ol.WmMouse Up(Message & m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Contr ol.WndProc (Message& m)
at System.Windows.Forms.Butto nBase.WndP roc(Messag e& m)
at System.Windows.Forms.Butto n.WndProc( Message& m)
at System.Windows.Forms.Nativ eWindow.Ca llback(Int Ptr 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/Microso ft.NET/Fra mework64/v 4.0.30319/ mscorlib.d ll
-------------------------- ---------- ----
NewClientInstall
Assembly Version: 1.0.0.0
Win32 Version: 1.0.0.0
CodeBase: file:///D:/temp/Root/NewCl ientInstal l.exe
-------------------------- ---------- ----
System.Windows.Forms
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso ft.Net/ass embly/GAC_ MSIL/Syste m.Windows. Forms/v4.0 _4.0.0.0__ b77a5c5619 34e089/Sys tem.Window s.Forms.dl l
-------------------------- ---------- ----
System
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso ft.Net/ass embly/GAC_ MSIL/Syste m/v4.0_4.0 .0.0__b77a 5c561934e0 89/System. dll
-------------------------- ---------- ----
System.Drawing
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso ft.Net/ass embly/GAC_ MSIL/Syste m.Drawing/ v4.0_4.0.0 .0__b03f5f 7f11d50a3a /System.Dr awing.dll
-------------------------- ---------- ----
System.Configuration
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso ft.Net/ass embly/GAC_ MSIL/Syste m.Configur ation/v4.0 _4.0.0.0__ b03f5f7f11 d50a3a/Sys tem.Config uration.dl l
-------------------------- ---------- ----
System.Core
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso ft.Net/ass embly/GAC_ MSIL/Syste m.Core/v4. 0_4.0.0.0_ _b77a5c561 934e089/Sy stem.Core. dll
-------------------------- ---------- ----
System.Xml
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso ft.Net/ass embly/GAC_ MSIL/Syste m.Xml/v4.0 _4.0.0.0__ b77a5c5619 34e089/Sys tem.Xml.dl l
-------------------------- ---------- ----
System.Data
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso ft.Net/ass embly/GAC_ 64/System. Data/v4.0_ 4.0.0.0__b 77a5c56193 4e089/Syst em.Data.dl l
-------------------------- ---------- ----
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;
}
The exception details for the Microsoft.SqlServer.Connec
************** Exception Text **************
System.IO.FileLoadExceptio
File name: 'Microsoft.SqlServer.Conne
at System.Reflection.RuntimeA
at System.Reflection.RuntimeA
at System.Reflection.RuntimeA
at NewClientInstall.fmMain.<.
at System.AppDomain.OnAssembl
at NewClientInstall.fmMain.re
at NewClientInstall.fmMain.bt
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Nativ
************** Loaded Assemblies **************
mscorlib
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso
--------------------------
NewClientInstall
Assembly Version: 1.0.0.0
Win32 Version: 1.0.0.0
CodeBase: file:///D:/temp/Root/NewCl
--------------------------
System.Windows.Forms
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso
--------------------------
System
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso
--------------------------
System.Drawing
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso
--------------------------
System.Configuration
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso
--------------------------
System.Core
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso
--------------------------
System.Xml
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso
--------------------------
System.Data
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microso
--------------------------
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>
within Web.config/app.config file in your project, to ensure the project will work fine with the database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.