Insert, update does not work , until SQL server restart.

Hi,
I am not sure if this is the right place to post my problem. If not so, pls
do advise me of the right group.
Here is my problem:
I have this problem with SQL server. All of a sudden the insert, updates does
not work on SQL server based application written in ASP. The select commands
are returning fine, its only the inserts and updates. They dont appera to
make any changes in the database. I looked into the ErrorLog files on SQL
server and they look pretty normal. No error or query truncation messages.
I restarted the SQL server and things get to be working again. This happened
a couple of times. It can't be the memory issue, coz there is ample of space
and also the datalog is set to grow as required (not limited).
SQL server is upgraded to SP3. So it is up with the upgrades.
From the application side, either the asp page goes to a page not found
error or returns back to the page as it should, but no updates to database.
Any clues to the problem would be appreciated.
Thanks,
Sam
You may want to check for blocking, locking issue when you
can't do the inserts/updates. You can use query analyzer and
query sysprocesses, execute sp_who2, sp_lock to see what's
going on when the inserts, updates aren't successful.
-Sue
On Thu, 4 Aug 2005 10:15:11 -0700, sam
<sam@.discussions.microsoft.com> wrote:

>Hi,
>I am not sure if this is the right place to post my problem. If not so, pls
>do advise me of the right group.
>Here is my problem:
>I have this problem with SQL server. All of a sudden the insert, updates does
> not work on SQL server based application written in ASP. The select commands
> are returning fine, its only the inserts and updates. They dont appera to
>make any changes in the database. I looked into the ErrorLog files on SQL
> server and they look pretty normal. No error or query truncation messages.
> I restarted the SQL server and things get to be working again. This happened
> a couple of times. It can't be the memory issue, coz there is ample of space
> and also the datalog is set to grow as required (not limited).
> SQL server is upgraded to SP3. So it is up with the upgrades.
> From the application side, either the asp page goes to a page not found
> error or returns back to the page as it should, but no updates to database.
>Any clues to the problem would be appreciated.
>Thanks,
>Sam
|||Thanks for the reply Sue,
I checked the sp_who2 and the sp_lock results. Didnt find any clue there. I
didnt find any in the BlkBy field of the results nor any thing from sp_lock.
all the processes are Granted. And i checked it at the time the problem
arised. It looks like SQL server is sorting itself after a while. The Lock
Type for al lthe requests is DB. Does that mean anything and the Status shows
as GRANT. Mode is 'S'.
Is there anything else that I should look for?
"Sue Hoegemeier" wrote:

> You may want to check for blocking, locking issue when you
> can't do the inserts/updates. You can use query analyzer and
> query sysprocesses, execute sp_who2, sp_lock to see what's
> going on when the inserts, updates aren't successful.
> -Sue
> On Thu, 4 Aug 2005 10:15:11 -0700, sam
> <sam@.discussions.microsoft.com> wrote:
>
>
|||Then next you may want to try running profiler to monitor
what's going across when you do an insert, update and
monitor the server itself with Perf Mon.
When you trace the database activity, check for statements
starting with no corresponding statement completed. It could
be a timeout issue or could be related to resource
constraints on the server itself.
-Sue
On Tue, 9 Aug 2005 15:19:03 -0700, sam
<sam@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the reply Sue,
>I checked the sp_who2 and the sp_lock results. Didnt find any clue there. I
>didnt find any in the BlkBy field of the results nor any thing from sp_lock.
>all the processes are Granted. And i checked it at the time the problem
>arised. It looks like SQL server is sorting itself after a while. The Lock
>Type for al lthe requests is DB. Does that mean anything and the Status shows
>as GRANT. Mode is 'S'.
>Is there anything else that I should look for?
>"Sue Hoegemeier" wrote:

Insert, update does not work , until SQL server restart.

Hi,
I am not sure if this is the right place to post my problem. If not so, pls
do advise me of the right group.
Here is my problem:
I have this problem with SQL server. All of a sudden the insert, updates doe
s
not work on SQL server based application written in ASP. The select commands
are returning fine, its only the inserts and updates. They dont appera to
make any changes in the database. I looked into the ErrorLog files on SQL
server and they look pretty normal. No error or query truncation messages.
I restarted the SQL server and things get to be working again. This happened
a couple of times. It can't be the memory issue, coz there is ample of space
and also the datalog is set to grow as required (not limited).
SQL server is upgraded to SP3. So it is up with the upgrades.
From the application side, either the asp page goes to a page not found
error or returns back to the page as it should, but no updates to database.
Any clues to the problem would be appreciated.
Thanks,
SamYou may want to check for blocking, locking issue when you
can't do the inserts/updates. You can use query analyzer and
query sysprocesses, execute sp_who2, sp_lock to see what's
going on when the inserts, updates aren't successful.
-Sue
On Thu, 4 Aug 2005 10:15:11 -0700, sam
<sam@.discussions.microsoft.com> wrote:

>Hi,
>I am not sure if this is the right place to post my problem. If not so, pls
>do advise me of the right group.
>Here is my problem:
>I have this problem with SQL server. All of a sudden the insert, updates do
es
> not work on SQL server based application written in ASP. The select comman
ds
> are returning fine, its only the inserts and updates. They dont appera to
>make any changes in the database. I looked into the ErrorLog files on SQL
> server and they look pretty normal. No error or query truncation messages.
> I restarted the SQL server and things get to be working again. This happen
ed
> a couple of times. It can't be the memory issue, coz there is ample of spa
ce
> and also the datalog is set to grow as required (not limited).
> SQL server is upgraded to SP3. So it is up with the upgrades.
> From the application side, either the asp page goes to a page not found
> error or returns back to the page as it should, but no updates to database
.
>Any clues to the problem would be appreciated.
>Thanks,
>Sam|||Thanks for the reply Sue,
I checked the sp_who2 and the sp_lock results. Didnt find any clue there. I
didnt find any in the BlkBy field of the results nor any thing from sp_lock.
all the processes are Granted. And i checked it at the time the problem
arised. It looks like SQL server is sorting itself after a while. The Lock
Type for al lthe requests is DB. Does that mean anything and the Status show
s
as GRANT. Mode is 'S'.
Is there anything else that I should look for?
"Sue Hoegemeier" wrote:

> You may want to check for blocking, locking issue when you
> can't do the inserts/updates. You can use query analyzer and
> query sysprocesses, execute sp_who2, sp_lock to see what's
> going on when the inserts, updates aren't successful.
> -Sue
> On Thu, 4 Aug 2005 10:15:11 -0700, sam
> <sam@.discussions.microsoft.com> wrote:
>
>|||Then next you may want to try running profiler to monitor
what's going across when you do an insert, update and
monitor the server itself with Perf Mon.
When you trace the database activity, check for statements
starting with no corresponding statement completed. It could
be a timeout issue or could be related to resource
constraints on the server itself.
-Sue
On Tue, 9 Aug 2005 15:19:03 -0700, sam
<sam@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the reply Sue,
>I checked the sp_who2 and the sp_lock results. Didnt find any clue there. I
>didnt find any in the BlkBy field of the results nor any thing from sp_lock
.
>all the processes are Granted. And i checked it at the time the problem
>arised. It looks like SQL server is sorting itself after a while. The Lock
>Type for al lthe requests is DB. Does that mean anything and the Status sho
ws
>as GRANT. Mode is 'S'.
>Is there anything else that I should look for?
>"Sue Hoegemeier" wrote:
>

Insert Trigger with If Statement

I want to check if a field has a 0 in it, if it does it should run some
code, if not do nothing
If field1 = 0 then
some code
end if
Please advise of the syntax for this
TIA PaulTry something on these lines:
If Exists (Select 1 from <Table> where field = 0)
Begin
-- the activity you wanted to do
End
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Paul Goldney" <paulg@.wizardit.co.uk> wrote in message
news:ca97dk$pm3$1$8302bc10@.news.demon.co.uk...
> I want to check if a field has a 0 in it, if it does it should run some
> code, if not do nothing
> If field1 = 0 then
> some code
> end if
> Please advise of the syntax for this
> TIA Paul
>|||Remember that more than one row may be inserted at once. Maybe one row = 0
and another row is <> 0. Probably you just need a WHERE clause on whatever
statement(s) are in "some code" instead of an IF statement:
?
..
FROM Inserted WHERE col = 0
You can test for col = 0 in an IF statement using EXISTS but crucially this
tests for the presence of *any* row where col = 0, which may or may not be
what you want in your trigger.
IF EXISTS
(SELECT *
FROM Inserted
WHERE col = 0)
David Portas
SQL Server MVP
--

Insert Trigger with If Statement

I want to check if a field has a 0 in it, if it does it should run some
code, if not do nothing
If field1 = 0 then
some code
end if
Please advise of the syntax for this
TIA Paul
Try something on these lines:
If Exists (Select 1 from <Table> where field = 0)
Begin
-- the activity you wanted to do
End
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Paul Goldney" <paulg@.wizardit.co.uk> wrote in message
news:ca97dk$pm3$1$8302bc10@.news.demon.co.uk...
> I want to check if a field has a 0 in it, if it does it should run some
> code, if not do nothing
> If field1 = 0 then
> some code
> end if
> Please advise of the syntax for this
> TIA Paul
>
|||Remember that more than one row may be inserted at once. Maybe one row = 0
and another row is <> 0. Probably you just need a WHERE clause on whatever
statement(s) are in "some code" instead of an IF statement:
?
...
FROM Inserted WHERE col = 0
You can test for col = 0 in an IF statement using EXISTS but crucially this
tests for the presence of *any* row where col = 0, which may or may not be
what you want in your trigger.
IF EXISTS
(SELECT *
FROM Inserted
WHERE col = 0)
David Portas
SQL Server MVP

Insert Trigger with If Statement

I want to check if a field has a 0 in it, if it does it should run some
code, if not do nothing
If field1 = 0 then
some code
end if
Please advise of the syntax for this
TIA PaulTry something on these lines:
If Exists (Select 1 from <Table> where field = 0)
Begin
-- the activity you wanted to do
End
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Paul Goldney" <paulg@.wizardit.co.uk> wrote in message
news:ca97dk$pm3$1$8302bc10@.news.demon.co.uk...
> I want to check if a field has a 0 in it, if it does it should run some
> code, if not do nothing
> If field1 = 0 then
> some code
> end if
> Please advise of the syntax for this
> TIA Paul
>|||Remember that more than one row may be inserted at once. Maybe one row = 0
and another row is <> 0. Probably you just need a WHERE clause on whatever
statement(s) are in "some code" instead of an IF statement:
?
...
FROM Inserted WHERE col = 0
You can test for col = 0 in an IF statement using EXISTS but crucially this
tests for the presence of *any* row where col = 0, which may or may not be
what you want in your trigger.
IF EXISTS
(SELECT *
FROM Inserted
WHERE col = 0)
--
David Portas
SQL Server MVP
--sql