July 29, 2010 at 1:03 pm
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.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior
runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at
System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
July 29, 2010 at 1:26 pm
Is there a question here?
The error means exactly what it says. The login does not have permission to insert into that table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2010 at 6:38 am
Hi Shaw,
The prob here is I execute the script , it gives me this error and the stack trace says me this.
and then the insert happens after 10 or 15 mins.
Not able to find out what the exact prob 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.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior
runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at
System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
July 30, 2010 at 6:44 am
The exact problem is that the login you are using does not have insert permission on the table mentioned in the error message. Ask the DBA to grant the necessary permissions to the login that you are using.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2010 at 8:05 am
Thanx Shaw,
The permission is there and insertion happens but not able to know wether is it the table is locked or there is any connection problem.
July 30, 2010 at 8:38 am
Neither a lock nor a connection problem is going to give you a permission denied error. The only thing that's going to give you a permission denied error is exactly that. Lack of permissions.
Try the insert from management studio using that login. Check that there are no explicit denies. Check that you are using the login you're supposed to be using, are connected to the right database and that the permissions are as they should be. Check that the permission denied doesn't come from a trigger.
If need, trace what's happening with profiler. The statement started event and the error events should show you exactly what statement is failing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2010 at 8:52 am
Sorry Shaw,
To post on the other thread.
I have used management studio to insert into the table and it inserts.
There is abackground thread running in my .net code which call the stored procedure to insert to the table and the insertion happens but it takes a long time i.e 10 - 15 mins.
August 2, 2010 at 5:18 am
vatha.meba (7/30/2010)
I have used management studio to insert into the table and it inserts.
On average how much time it took ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 3, 2010 at 3:21 am
The scenario is there is a thread that works in the background in vb.net code which calls a stored procedure which does the insert.
Earlier it use to take 1 min. but now it is taking 10- 15 mins.
August 3, 2010 at 3:30 am
vatha.meba (8/3/2010)
The scenario is there is a thread that works in the background in vb.net code which calls a stored procedure which does the insert.Earlier it use to take 1 min. but now it is taking 10- 15 mins.
and what about the direct run at mgmt studio ? is it faster ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 3, 2010 at 4:03 am
vatha.meba (8/3/2010)
The scenario is there is a thread that works in the background in vb.net code which calls a stored procedure which does the insert.Earlier it use to take 1 min. but now it is taking 10- 15 mins.
Have you run Profiler to see exactly which part of the procedure is slow and what, if any, errors are occurring? I don't think the slowness is related to the permission denied errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2010 at 4:13 am
In development server it takes less than 1 min. but in Testing server it takes more than 10 mins and in testing server we don't have permission to use profiler.
August 3, 2010 at 4:25 am
vatha.meba (8/3/2010)
In development server it takes less than 1 min. but in Testing server it takes more than 10 mins and in testing server we don't have permission to use profiler.
take the help of persons (dba) who is having that privledge.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 3, 2010 at 4:25 am
So ask the DBA to run it.
Difference could be related to data volumes, stats that haven't been updated, difference in schema, etc.
Have you run the procedure from management studio in testing? Examined the execution plan and query statistics/ Do they differ from dev, if so how?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2010 at 5:57 am
GilaMonster (8/3/2010)
So ask the DBA to run it.Difference could be related to data volumes, stats that haven't been updated, difference in schema, etc.
Have you run the procedure from management studio in testing? Examined the execution plan and query statistics/ Do they differ from dev, if so how?
my take would be statistics outdated.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply