A question on SQLBulkCopy.

  • Thread starter Thread starter George Shui
  • Start date Start date
G

George Shui

The database I use is SQL Server 2005.

The user to login database is 'PG_TestUser', it has 'dbo' schema, and it has
datareader and datawriter permission.

I have .NET Code like this.

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(dbConnStr,
SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction))
{
bulkCopy.DestinationTableName = tableName.Trim();

DataTable dt = CSVFileHelper.Read(dataFile);

bulkCopy.BulkCopyTimeout = Const.TIMEOUT;

bulkCopy.WriteToServer(dt);
}

But I have got the exception "Cannot find the object "SVSReportDetail"
because it does not exist or you do not have permissions"

The stackTrace is:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32
columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table,
DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
at DBArchiveManager.AbstractImportPorcessor.BulkImport(String dbConnStr,
String tableName, FileInfo dataFile) in
D:\WebRoot\PGS\Tool\DBArchiveManager.root\DBArchiveManager\DBArchiveManager\Processor\ImportProcessor\AbstractImportPorcessor.cs:line 56

If I gave the 'PG_TestUser' the 'sysadmin' schema, the code works.
Also I have tried that the 'PG_TestUser' do have the select, delete, insert
permision.

AnyOne can help me?

Thank you for your help in advance.
 
Sure. This table do exist. And The user can do Select, Insert, Delete on this
table.

Another point is If I gave the user the 'sysadmin', my code can work.

Thanks.
 
Assigning ddladmin or db_owner database role should fix issue.



GeorgeShu wrote:

A question on SQLBulkCopy.
11-May-08

The database I use is SQL Server 2005.

The user to login database is 'PG_TestUser', it has 'dbo' schema, and it has
datareader and datawriter permission

I have .NET Code like this

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(dbConnStr,
SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction)

bulkCopy.DestinationTableName = tableName.Trim()

DataTable dt = CSVFileHelper.Read(dataFile)

bulkCopy.BulkCopyTimeout = Const.TIMEOUT

bulkCopy.WriteToServer(dt)


But I have got the exception "Cannot find the object "SVSReportDetail"
because it does not exist or you do not have permissions

The stackTrace is
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal(
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32
columnCount
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table,
DataRowState rowState
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table
at DBArchiveManager.AbstractImportPorcessor.BulkImport(String dbConnStr,
String tableName, FileInfo dataFile) in
D:\WebRoot\PGS\Tool\DBArchiveManager.root\DBArchiveManager\DBArchiveManager\Processor\ImportProcessor\AbstractImportPorcessor.cs:line 5

If I gave the 'PG_TestUser' the 'sysadmin' schema, the code works
Also I have tried that the 'PG_TestUser' do have the select, delete, insert
permision

AnyOne can help me

Thank you for your help in advance.

Previous Posts In This Thread:

A question on SQLBulkCopy.
The database I use is SQL Server 2005.

The user to login database is 'PG_TestUser', it has 'dbo' schema, and it has
datareader and datawriter permission

I have .NET Code like this

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(dbConnStr,
SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction)

bulkCopy.DestinationTableName = tableName.Trim()

DataTable dt = CSVFileHelper.Read(dataFile)

bulkCopy.BulkCopyTimeout = Const.TIMEOUT

bulkCopy.WriteToServer(dt)


But I have got the exception "Cannot find the object "SVSReportDetail"
because it does not exist or you do not have permissions

The stackTrace is
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal(
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32
columnCount
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table,
DataRowState rowState
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table
at DBArchiveManager.AbstractImportPorcessor.BulkImport(String dbConnStr,
String tableName, FileInfo dataFile) in
D:\WebRoot\PGS\Tool\DBArchiveManager.root\DBArchiveManager\DBArchiveManager\Processor\ImportProcessor\AbstractImportPorcessor.cs:line 5

If I gave the 'PG_TestUser' the 'sysadmin' schema, the code works
Also I have tried that the 'PG_TestUser' do have the select, delete, insert
permision.

AnyOne can help me?

Thank you for your help in advance.

Don't tell me, the table SVSReportDetail does not exist on the target server.
Don't tell me, the table SVSReportDetail does not exist on the target
server. It must exist and the user must have IO rights.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker???s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

Sure. This table do exist.
Sure. This table do exist. And The user can do Select, Insert, Delete on this
table.

Another point is If I gave the user the 'sysadmin', my code can work.

Thanks.

Then it's simply a rights issue.
Then it's simply a rights issue. I would start adding rights until it works
(staring with control).

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker???s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

EggHeadCafe - Software Developer Portal of Choice
C# .NET Extension Methods 3.0
http://www.eggheadcafe.com/tutorial...2cf-3344eff69311/c-net-extension-methods.aspx
 
Back
Top