|
cmdSQL.CommandTimeout = 30
is by default.
Try setting it to 60.
|
|
|
|
|
First load up your query in query analyzer and check the execution plan to make sure it is not doing a table scan. If it is check to see if there is an index on [File_Name], if not then add one. If there is then try rebuilding the statistics on that table.
Increasing the timeout value should be the approach of last resort.
|
|
|
|
|
Agreed.
But this is just not the execution plan and table statistics but sometimes the network traffic is high and the default CommandTimeout is just not sufficient. I have encountered this before.
|
|
|
|
|
It depends how high the timeout is and how much traffic you need to support. If you set your timeout too high, the entire site can be brought down by a few poorly performing pages. The timeout exists for a very important reason, to allow the server to recover when it is overloaded. I have encountered this a few times myself.
IMHO. Exceution plan and table statistics are the most common reasons for an update to run slow especially when there are a number of records in the table and should be the first solutions considered since they have very little cost. Next, I'd evaluate contention with other queries by loading up the profiler and looking for long running operations.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
by default the SqlConnection object sets pooling = true which means there is a limited ammount of time that connection is allowed in the pool.
Clicky[^]
I have ran into the same situation where no matter how good my execution plan was or how normalized my data was I would still get time outs. After adding pooling = false to my connection strings the time outs stopped..
hope this helps
Will
|
|
|
|
|
This is true,
but there are two aspects of it, command timeout and connection timeout.
Do you think they are related?
|
|
|
|
|
I think it is a mix of several settings.
You could set your command timeout to 4 hours if you wanted to but if the connection is pooling then
you are stuck.
Clicky[^]
Question 4:
The ConnectionTimeout property cannot be reset on a pooled connection
I may be wrong but this worked for me...
Will
|
|
|
|
|
|
Thanks guys for ur immediate response but i have tried the things u have pointed out. I ran the query through query analyzer and it seems to give me timeout their as well. I think this seems to be an issue with SQL server not with connection. My database only have 120000 records but i am storing PDF its size is over 10 GB. When i ran select query on a particular table it runs fine but when i try to update a row or record or delete a record it gives timeout using query analyzer. Any one who can point out this issue why is this happening.
Once again thnx for ur immediate response.
Jaffer
|
|
|
|
|
Then this is totally a data problem. You could try to partition your table...
Here is some light reading on the how and why.
Clcky[^]
hope this helps
Will
-- modified at 12:33 Monday 12th September, 2005
|
|
|
|
|
First of all the image data type can only hold 2GB of data so even if your operation didn't time out, it would fail. If you have to store documents which are that large, you are going to have to increase your command timeout. The connection timeout should only increase if the server is getting overloaded.
120,000 records is a relatively small amount for sql server to handle if indexes are setup properly. Make sure the indexes are on the table to support all your queries. If that doesn't fix the problem, then start looking to see if SQL server is bound up with disk or memory access from the large files, or if it is just load from the tables in the database.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Hi everybody !
I have a marvelous problem in deletion of a DataRow from a dataset table.I want my application to delete rows virtually.To aim that,I handeled the 'RowDeleted' event of the DataTable and in that event handler set the "Deleted" column of row (A bit column in SQLServer) to 1 and reject the changes of row to avoidance of row deletion after Updating the dataset to database.(I don't want the rows be deleted REALLY for undo capability of deletions).
See the code :
<code>
this.dataSet.Tables["tablename"].RowDeleted += new DataRowChangeEventHandle(RowDeleted);
private void RowDeleted(object sender, DataRowChangeEventArgs e)
{
e.Row.RejectChanges();
e.Row["Deleted"] = 1;
}
But when the 'e.Row.RejectChanges();' Executes I receive the following Exception :
System.Data.DeletedRowInaccessibleException : Deleted row information cannot be accessed through the row.
Wonderful thing is that when I replace the above code with the following code, everything is ok and the RowState is changed to UnChanged after Catch block !!!!
<code>
private void RowDeleted(object sender, DataRowChangeEventArgs e)
{
try
{
e.Row.RejectChanges();
}
catch{}
e.Row["Deleted"] = 1;
}
How can I do with this problem ?
Sorry for bad english.Thanx alot !
[ _ Always there is another way _ ]
|
|
|
|
|
Assuming your DataTable is bounded to a DataGrid, I think this is not the best way to do it.
To do virtual deletes, you can use the default DataView and add a RowFilter with the condition "Deleted != 1".
It will display only the Rows with "Deleted != 1".
I hope this helps.
Thanks
-- modified at 13:56 Friday 9th September, 2005
|
|
|
|
|
Thanx for your help.That is true.I used the dataview with
<code>RowFilter = "Deleted != 1"
But this is just a Mask for VIEWING. I have problem when I want to set the Dleted column to 1 !! After setting this column everything will masked with a dataview RowFilter.
<code>
this.dataSet.Tables["tablename"].RowDeleted += new DataRowChangeEventHandle(RowDeleted);
private void RowDeleted(object sender, DataRowChangeEventArgs e)
{
<font color=red>My problem is Here</font>
<font color=green>
</font>
e.Row.RejectChanges();
e.Row["Deleted"] = 1;
}
Thanx for your attention
[ _ Always there is another way _ ]
-- modified at 0:44 Saturday 10th September, 2005
|
|
|
|
|
I think I understand your problem correctly now.
My bet will be to subclass DataGrid that you are using and add the following method
public override bool PreProcessMessage( ref Message msg )
{
Keys keyCode = (Keys)(int)msg.WParam & Keys.KeyCode;
if(msg.Msg == WM_KEYDOWN
&& keyCode == Keys.Delete
&& ((DataView) this.DataSource).AllowDelete)
{
//Do something here(like row["Deleted"] = 1);
// return true will make sure the Row is not deleted.
return true;
}
return base.PreProcessMessage(ref msg);
}
|
|
|
|
|
How can i hide a database to be listed in sql enterprise manager else how can i prevent data from being deleted in a table through enterprise manager
|
|
|
|
|
Make sure only authorized users have rights to make changes to the database. If users have access to their own SQL user names and passwords for an application then you can't prevent them from using other access tools.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
smita_roy wrote:
How can i hide a database to be listed in sql enterprise manager
=> No u cannot hide ur DB in SQL Enterprise Manager, I think. Because this GUI used to manage Databases
smita_roy wrote:
how can i prevent data from being deleted in a table through enterprise manager
=> u need to ensure that there's no one knows ur DB password except u
<< >>
|
|
|
|
|
(1) How can i hide a database to be listed in sql enterprise manager ?
Just Detach the Database in the Enterprise Manager.
(2) How can i prevent data from being deleted in a table through enterprise manager ?
Remove the permission from the user
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
|
|
|
|
|
I have a simple insertinto statement with the correct syntax:
INSERT INTO BookingBook ('Date') Values ('01/01/2006');
Why is it throwing up the error that there is no such field as date when there most definitely is... Does the fact that it is a reserved word have anything to do with it...
Thanks.
°[Halo]°
Project Basilisk
|
|
|
|
|
INSERT INTO BookingBook ([Date]) Values ('01/01/2006');
The apostrophes indicate a string literal. Use square brackets or quotes.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
°[Halo]° wrote:
INSERT INTO BookingBook ('Date') Values ('01/01/2006');
the field name is not qouted by brackets, the correct one should be
INSERT INTO BookingBook (Date) Values ('01/01/2006')
u can refer in SQL book online
<< >>
|
|
|
|
|
You could convert the string date on the fly ( if your usinf TSQL )
<br />
INSERT INTO BookingBook ('Date') <br />
Values ((Convert( datetime, '01/01/2005', 103 ))<br />
103 is DD/MM/YYYY
I prefer stating the exact date to SQL as it stops date problems with mixing up DD and MM.
|
|
|
|
|
INSERT INTO BookingBook ([Date])
Values ((Convert( datetime, '01/01/2005', 103 ))
|
|
|
|
|
Using sq brackets did not solve this. This provided me also with the error "INSERT INTO statement contains an unknown field 'Date'" or something to that effect. In the end I just changed the field name... I believe that access is reading "Date" as an identifier rather than a field name where it expects a field name...?
°[Halo]°
Project Basilisk
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
