Bulk Copy Program

Hi Techies,

I have a bcp which generates .txt file perfectly. I just wanted to know how can i generate a text file in distributed environment.

Assuming that my Sql Server is running in machine A. I wanted the bcp to generate in Machine B. What are the permission's i should give in order to generate it in Machine B.

Regards
--Tanveerthe user who executes the bcp-command would need write access. read access is usually handy to validate the file has been written but I don't think its required. In case machine B only has a filesystem, that user obviously needs filesystem write access. In case machine B is a sqlserver that user would need write access on the table the file is insert into.|||Thanks for the reply... I guess i have not mentioned that the above program would be executed through stored procedure. In this scenario what are the permission i should give and to which users.

--Tanveer|||Assuming bcp and the copy is executed using xp_cmdshell, the user is the user configured to run sqlserver (exec master..xp_cmdshell 'set'). If it's local user (only known to the server) you'll find it difficult to do the windows-copy. If it's a domain user it'll be easier to grant the write access. Do you have difficulty creating the .txt file using the stored procedure?

Bulk Copy not with MSDE ?

With the following I try to save the content of an excel-sheet to a sql table. This works perfectly with SQL Server Express but not with MSDE, which I would need also. Here the code:

String rootPath1 = Request.MapPath("~/Kontoauszug.xls");

String strConn ="Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=" + rootPath1 +";Extended Properties=Excel 8.0;";

OleDbDataAdapter da =newOleDbDataAdapter

("SELECT * FROM [Mappe1$]", strConn);DataTable dtCustomers =newDataTable();

da.Fill(dtCustomers);

string ConnectionString =ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();using (SqlConnection destinationConnection =newSqlConnection(ConnectionString))

{

// open the connection

destinationConnection.Open();

using (SqlBulkCopy bulkCopy =newSqlBulkCopy(destinationConnection.ConnectionString))

{

bulkCopy.BatchSize = 500;

bulkCopy.BulkCopyTimeout = 90;

bulkCopy.DestinationTableName ="dbo.Auszug";

bulkCopy.WriteToServer(dtCustomers);

bulkCopy.Close();

}

}

With MSDE I see the following error-message

Fehler bei der Anmeldung für den Benutzer 'sa'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Fehler bei der Anmeldung für den Benutzer 'sa'.

Source Error:

Line 148: Line 149: // Write from the source to the destination.Line 150: bulkCopy.WriteToServer(dtCustomers);Line 151: Line 152:

Source File:d:\Inetpub\Www_root\XXXXXXXXXXXXXXXXXXXXXXXX.cs Line:150

Stack Trace:

[SqlException (0x80131904): Fehler bei der Anmeldung für den Benutzer 'sa'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.SqlClient.SqlBulkCopy.CreateOrValidateConnection(String method) +64 System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) +42 System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) +176 System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) +6 admin_Default.Button4_Click(Object sender, EventArgs e) in d:\Inetpub\Www_root\netzregister.de\consumeropinion\admin\Default.aspx.cs:150 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

The connection to the database works perfect also on MSDE on all other parts of the project.

Can anyone help me wit a solution or an idea?

Perhaps I should add, that when I change the password in the web config to a wrong entry I get the same error message, but in another line:

Line 127: destinationConnection.Open();