SqlBulkCopy instance in Visual C# 2005 Express: Can not have remote connections to SQL Server Express

Question

Post reply

SqlBulkCopy instance in Visual C# 2005 Express: Can not have remote connections to SQL Server Express

Scott H. Chang

SSCommitted

Points: 1794
More actions
August 9, 2007 at 5:59 am

#180262

Hi all,

I have Visual C# 2005 and SQL Server Express in my Microsoft Windows XP Pro PC. I got "SqlException was unhandled: An error has occurred while establishing a connection to the server, when connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections" pointing to the code statement "connection.Open();", when I executed the following project:

using

System;

using

System.Collections.Generic;

using

System.ComponentModel;

using

System.Data;

using

System.Drawing;

using

System.Text;

using

System.Windows.Forms;

using

System.Data.SqlClient;

namespace

SqlBulkCopySample

{

public partial class frmMain : Form

{

public frmMain()

{

InitializeComponent();

}

private void btnStart_Click(object sender, EventArgs e)

{

String sourceConnectionString =

"Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

String destinationConnectionString =

"Data Source=.SQLEXPRESS;Initial Catalog=SqlBulkCopySample;Integrated Security=True";

DataTable data = SelectDataFromSource(sourceConnectionString);

CopyDataToDestination(destinationConnectionString, data);

}

private DataTable SelectDataFromSource(String connectionString)

{

DataTable data = new DataTable();

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand command = new SqlCommand("SelectOrders", connection);

command.CommandType =

CommandType.StoredProcedure;

connection.Open();

SqlDataReader reader = command.ExecuteReader();

data.Load(reader);

}

return data;

}

private void CopyDataToDestination(String connectionString, DataTable table)

{

SqlBulkCopyColumnMapping mapping1 =

new SqlBulkCopyColumnMapping("OrderID", "ID");

SqlBulkCopyColumnMapping mapping2 =

new SqlBulkCopyColumnMapping("ShipName", "Name");

SqlBulkCopyColumnMapping mapping3 =

new SqlBulkCopyColumnMapping("ShipAddress", "Address");

SqlBulkCopyColumnMapping mapping4 =

new SqlBulkCopyColumnMapping("ShipCity", "City");

SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);

bulkCopy.BatchSize = 100;

bulkCopy.BulkCopyTimeout = 5;

bulkCopy.ColumnMappings.Add(mapping1);

bulkCopy.ColumnMappings.Add(mapping2);

bulkCopy.ColumnMappings.Add(mapping3);

bulkCopy.ColumnMappings.Add(mapping4);

bulkCopy.DestinationTableName =

"DataMySqlBC1";

bulkCopy.SqlRowsCopied +=

new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);

bulkCopy.NotifyAfter = 200;

bulkCopy.WriteToServer(table);

}

void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)

{

MessageBox.Show

(

String.Format("{0} Rows have been copied.", e.RowsCopied.ToString()));

}

}

}

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I copied this set of Visual C# 2005 Express codes for SqlBulkCopy from a website. I have no ideas how to fix it.

Please help and tell me kindly what and how I can do to make this project work.

Thanks in advance,

Scott Chang

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 1

Scott -

Try changing your connection string from .SQLEXPRESS to .\SQLEXPRESS and let us know the results. SQL Express is typically installed as a named instance for which the naming convention is server\instancename, if .\sqlxpress doesn't work try localhost\sqlexpress or server\sqlexpress.

By default SQL Express does not allow remote connections (configured via the SuL Server Configuration Manager) but that should not prevent you from connecting to a local SQL Server.

Joe

Scott H. Chang SSCommitted Points: 1794 More actions · Answer 2

Hi Joe, Thanks for your response.

I tried all the options you asked me to do/change the connection string and nothing worked for me. Let me show you what errors I got:

1) If I used localhost\.SQLEXPRESS or myServerName\.SQLEXPRESS, I got the following: There were build errors. Would you like to have and run the last successful build? I pressed "Yes" to run. Then I got: Unrecognized escape sequnce in the "Main.cs" file.

2) If I used localhost\SQLEXPRESS or myServerName\SQLEXPRESS, I got the following: Source file: C:\SqlBulkCopy\SqlBulkCopySample\Main.cs

Module:

Process:

The source file is different from when the module was built. Would you like the debugger to use it anyway? If I pressed "Yes" to debug, then I got the error of "SqlException was unhandled" I said in my last post. As I said before, I copied this project from a website - the original project was written for the regular "Visual C# 2005" and "SQL Server 2005". (I think.) I copied that project and run it in my "Visual C# 2005 Express" and "SQL Server Express 2005". Is it an issue? Please help me again and let me know what I can do to solve this strange problem.

Many thanks again,

Scott Chang

P. S. Should I create this SqlBulkCopy project in my Visual C# 2005 Express from the ground up? If so, please tell me how. I am new in the C# programming and I will try it.

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 3

Joe Clifford

SSCrazy Eights

Points: 8739

August 11, 2007 at 11:56 am

#726293

Scott -

Stick with localhost\SQLEXPRESS and rebuild your project once you've made the change.

Joe

Scott H. Chang SSCommitted Points: 1794 More actions · Answer 4

Hi Joe, Thanks for your response.

I used myComputerSQLServerName without "SQLEXPRESS" to run the old project that was copied from the website. It ran and it passes through the code statement "connection.Open()". But I got a new error "SqlException was unhandled. Could not find stored procedure "SelectOrders" that is pointing to the code statement "SqlDataReader reader = command.ExecuteReader();". I am glad to see it entered myComputerSQLServer. But I have a new problem of setting up the stored procedure "SelectOrders" in this project. I saw the author of that article mentioned the following:

Create a Stored Procedure to select the desired columns from source Orders table in

Northwind database:

CREATE PROCEDURE dbo.SelectOrders

AS

SELECT OrderID, ShipName, ShipAddress, ShipCity

FROM Orders

I am new in the Stored Procedure thing - I do not know where in my SQLSERVER Express I should create the stored procedure "SelectOrders". Could you please help me in this matter and tell me in detail where exactly I should create the stored procedure "SelectOrders"?

Thanks,

Scott Chang

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 5

Joe Clifford

SSCrazy Eights

Points: 8739

August 13, 2007 at 8:17 pm

#726650

Scott -

Make sure that the stored procedure exists in your default database/initial catalog - in your connection string you are probably specifying the "initial catalog=", that's where your stored procedure needs to be, OR you need to change your initial catalog setting to connect to the appropriate database.

Joe