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 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.

William
Sun May 11 16:37:46 CDT 2008

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)
____________________________________________________________________________________________
"George Shui" <GeorgeShui@discussions.microsoft.com> wrote in message
news:7D7FCDA9-956B-430E-B3A3-216B41F0490F@microsoft.com...
> 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.
>
>
>
>
>

GeorgeShui
Sun May 11 16:43:01 CDT 2008

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.

William
Mon May 12 05:38:50 CDT 2008

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)
____________________________________________________________________________________________
"George Shui" <GeorgeShui@discussions.microsoft.com> wrote in message
news:47B38F5D-B49B-4C0D-92A6-6753288CE5E0@microsoft.com...
> 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.
>