I am using SQL Server 2000 and running number of query simulatneously. While
running update query it is always giving above error. The error discriptions
are -
1. [SQL Server]Transaction (Process ID 52) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock} reso
urces
with another process and has been chosen as the deadlock victim
3. [SQL Server]Transaction (Process ID 56) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
I have tried cleaning up the database, increased the fixed memory size of
SQL Server to 800 MB, restart the machine. But nothing works.
Plz help out to resolve this problem.get output from
sp_who
sp_who2
sp_lock
when the trans is running
"Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>I am using SQL Server 2000 and running number of query simulatneously.
>While
> running update query it is always giving above error. The error
> discriptions
> are -
> 1. [SQL Server]Transaction (Process ID 52) was deadlocked on {loc
k}
> resources with another process and has been chosen as the deadlock victim
> 2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock}
> resources
> with another process and has been chosen as the deadlock victim
> 3. [SQL Server]Transaction (Process ID 56) was deadlocked on {loc
k}
> resources with another process and has been chosen as the deadlock victim
>
> I have tried cleaning up the database, increased the fixed memory size of
> SQL Server to 800 MB, restart the machine. But nothing works.
> Plz help out to resolve this problem.|||Thx. I will check that.
"David J. Cartwright" wrote:
> get output from
> sp_who
> sp_who2
> sp_lock
> when the trans is running
> "Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
> news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>
>
Showing posts with label number. Show all posts
Showing posts with label number. Show all posts
Monday, March 19, 2012
[SQL Server]Transaction (Process ID 52) was deadlocked
I am using SQL Server 2000 and running number of query simulatneously. While
running update query it is always giving above error. The error discriptions
are -
1. [SQL Server]Transaction (Process ID 52) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock} resources
with another process and has been chosen as the deadlock victim
3. [SQL Server]Transaction (Process ID 56) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
I have tried cleaning up the database, increased the fixed memory size of
SQL Server to 800 MB, restart the machine. But nothing works.
Plz help out to resolve this problem.
get output from
sp_who
sp_who2
sp_lock
when the trans is running
"Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>I am using SQL Server 2000 and running number of query simulatneously.
>While
> running update query it is always giving above error. The error
> discriptions
> are -
> 1. [SQL Server]Transaction (Process ID 52) was deadlocked on {lock}
> resources with another process and has been chosen as the deadlock victim
> 2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock}
> resources
> with another process and has been chosen as the deadlock victim
> 3. [SQL Server]Transaction (Process ID 56) was deadlocked on {lock}
> resources with another process and has been chosen as the deadlock victim
>
> I have tried cleaning up the database, increased the fixed memory size of
> SQL Server to 800 MB, restart the machine. But nothing works.
> Plz help out to resolve this problem.
|||Thx. I will check that.
"David J. Cartwright" wrote:
> get output from
> sp_who
> sp_who2
> sp_lock
> when the trans is running
> "Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
> news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>
>
running update query it is always giving above error. The error discriptions
are -
1. [SQL Server]Transaction (Process ID 52) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock} resources
with another process and has been chosen as the deadlock victim
3. [SQL Server]Transaction (Process ID 56) was deadlocked on {lock}
resources with another process and has been chosen as the deadlock victim
I have tried cleaning up the database, increased the fixed memory size of
SQL Server to 800 MB, restart the machine. But nothing works.
Plz help out to resolve this problem.
get output from
sp_who
sp_who2
sp_lock
when the trans is running
"Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>I am using SQL Server 2000 and running number of query simulatneously.
>While
> running update query it is always giving above error. The error
> discriptions
> are -
> 1. [SQL Server]Transaction (Process ID 52) was deadlocked on {lock}
> resources with another process and has been chosen as the deadlock victim
> 2. SQL Server]Transaction (Process ID 51) was deadlocked on {lock}
> resources
> with another process and has been chosen as the deadlock victim
> 3. [SQL Server]Transaction (Process ID 56) was deadlocked on {lock}
> resources with another process and has been chosen as the deadlock victim
>
> I have tried cleaning up the database, increased the fixed memory size of
> SQL Server to 800 MB, restart the machine. But nothing works.
> Plz help out to resolve this problem.
|||Thx. I will check that.
"David J. Cartwright" wrote:
> get output from
> sp_who
> sp_who2
> sp_lock
> when the trans is running
> "Sudhanshu" <Sudhanshu@.discussions.microsoft.com> wrote in message
> news:EBF56A23-2CBC-4E4F-9220-F53C6B656AB1@.microsoft.com...
>
>
Labels:
database,
deadlocked,
error,
microsoft,
mysql,
number,
oracle,
process,
query,
running,
server,
servertransaction,
simulatneously,
sql,
update,
whilerunning
Tuesday, March 6, 2012
[newbe] SQL number of querries / connections limits ?
Hi all!
Can you tell me if there are any default or manually set limits of
subsequent querries / connections per user or database? I couldnt find
any in Enterprise Manager, but my Perl / ASP aplications seems to behave
as if it were some.
Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
2000 - 3000 per hour). After doing a couple of thosands it sudenly stops
- querry is sent but no result returns. If I limit the numer of being
sent per minute - the program seems to work propotionally longer, but
finally 'hangs' in the very same well.
Moreover, ASP IIS based cooperating aplication seems to reach the same
limit - after some time querries (actually doing SELECTS on data
previously INSERTED by Perl script) are processed longer than ASP
timeout allows, well the actually hangs, Profiler shows some crazy
processing times. If I reduce the number of queries per transaction -
namely changing a subquerry to join construction - it works ok. It all
makes me think I hit some limits.
I've searched the WEB archives (my MS coleeges' knowledge as well
)
but couldn't find much - does any body knows sth about it?
pk
There is no hard limit on the number of connections you can have to a SQL
Server. But every connection takes up about 40KB of server memory, so there
are definitely practical limitations. Make sure that all your code drops the
connections to the server that are no longer needed.
Jacco Schalkwijk
SQL Server MVP
"Piotr Kowalski" <wywaltopkwlski@.oitotez2.pl> wrote in message
news:ctl2ea$oa9$1@.atlantis.news.tpi.pl...
> Hi all!
> Can you tell me if there are any default or manually set limits of
> subsequent querries / connections per user or database? I couldnt find any
> in Enterprise Manager, but my Perl / ASP aplications seems to behave as if
> it were some.
> Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
> 2000 - 3000 per hour). After doing a couple of thosands it sudenly stops -
> querry is sent but no result returns. If I limit the numer of being sent
> per minute - the program seems to work propotionally longer, but finally
> 'hangs' in the very same well.
> Moreover, ASP IIS based cooperating aplication seems to reach the same
> limit - after some time querries (actually doing SELECTS on data
> previously INSERTED by Perl script) are processed longer than ASP timeout
> allows, well the actually hangs, Profiler shows some crazy processing
> times. If I reduce the number of queries per transaction - namely changing
> a subquerry to join construction - it works ok. It all makes me think I
> hit some limits.
> I've searched the WEB archives (my MS coleeges' knowledge as well
) but
> couldn't find much - does any body knows sth about it?
> pk
|||Jacco Schalkwijk wrote:
> There is no hard limit on the number of connections you can have to a SQL
> Server. But every connection takes up about 40KB of server memory, so there
> are definitely practical limitations. Make sure that all your code drops the
> connections to the server that are no longer needed.
>
yeah, I've considered that.
Sybase DBI library unfortunately uses new connection every query, but
neither client's nor server's network stack reports other TCP
connections than being actually used (some 3 connection in every moment)
so older ones are properly disconnected. Maybe some "residues" still
remains on the SQL box, on the aplication layer, which aren't counted by
netstat but still reserve some resources (RAM). Enterprise manager shows
only as many proceses as really exists (3).
What about query limit? Or other limits that could come in question in
this case?
hk
Can you tell me if there are any default or manually set limits of
subsequent querries / connections per user or database? I couldnt find
any in Enterprise Manager, but my Perl / ASP aplications seems to behave
as if it were some.
Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
2000 - 3000 per hour). After doing a couple of thosands it sudenly stops
- querry is sent but no result returns. If I limit the numer of being
sent per minute - the program seems to work propotionally longer, but
finally 'hangs' in the very same well.
Moreover, ASP IIS based cooperating aplication seems to reach the same
limit - after some time querries (actually doing SELECTS on data
previously INSERTED by Perl script) are processed longer than ASP
timeout allows, well the actually hangs, Profiler shows some crazy
processing times. If I reduce the number of queries per transaction -
namely changing a subquerry to join construction - it works ok. It all
makes me think I hit some limits.
I've searched the WEB archives (my MS coleeges' knowledge as well
but couldn't find much - does any body knows sth about it?
pk
There is no hard limit on the number of connections you can have to a SQL
Server. But every connection takes up about 40KB of server memory, so there
are definitely practical limitations. Make sure that all your code drops the
connections to the server that are no longer needed.
Jacco Schalkwijk
SQL Server MVP
"Piotr Kowalski" <wywaltopkwlski@.oitotez2.pl> wrote in message
news:ctl2ea$oa9$1@.atlantis.news.tpi.pl...
> Hi all!
> Can you tell me if there are any default or manually set limits of
> subsequent querries / connections per user or database? I couldnt find any
> in Enterprise Manager, but my Perl / ASP aplications seems to behave as if
> it were some.
> Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
> 2000 - 3000 per hour). After doing a couple of thosands it sudenly stops -
> querry is sent but no result returns. If I limit the numer of being sent
> per minute - the program seems to work propotionally longer, but finally
> 'hangs' in the very same well.
> Moreover, ASP IIS based cooperating aplication seems to reach the same
> limit - after some time querries (actually doing SELECTS on data
> previously INSERTED by Perl script) are processed longer than ASP timeout
> allows, well the actually hangs, Profiler shows some crazy processing
> times. If I reduce the number of queries per transaction - namely changing
> a subquerry to join construction - it works ok. It all makes me think I
> hit some limits.
> I've searched the WEB archives (my MS coleeges' knowledge as well
> couldn't find much - does any body knows sth about it?
> pk
|||Jacco Schalkwijk wrote:
> There is no hard limit on the number of connections you can have to a SQL
> Server. But every connection takes up about 40KB of server memory, so there
> are definitely practical limitations. Make sure that all your code drops the
> connections to the server that are no longer needed.
>
yeah, I've considered that.
Sybase DBI library unfortunately uses new connection every query, but
neither client's nor server's network stack reports other TCP
connections than being actually used (some 3 connection in every moment)
so older ones are properly disconnected. Maybe some "residues" still
remains on the SQL box, on the aplication layer, which aren't counted by
netstat but still reserve some resources (RAM). Enterprise manager shows
only as many proceses as really exists (3).
What about query limit? Or other limits that could come in question in
this case?
hk
[newbe] SQL number of querries / connections limits ?
Hi all!
Can you tell me if there are any default or manually set limits of
subsequent querries / connections per user or database? I couldnt find
any in Enterprise Manager, but my Perl / ASP aplications seems to behave
as if it were some.
Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
2000 - 3000 per hour). After doing a couple of thosands it sudenly stops
- querry is sent but no result returns. If I limit the numer of being
sent per minute - the program seems to work propotionally longer, but
finally 'hangs' in the very same well.
Moreover, ASP IIS based cooperating aplication seems to reach the same
limit - after some time querries (actually doing SELECTS on data
previously INSERTED by Perl script) are processed longer than ASP
timeout allows, well the actually hangs, Profiler shows some crazy
processing times. If I reduce the number of queries per transaction -
namely changing a subquerry to join construction - it works ok. It all
makes me think I hit some limits.
I've searched the WEB archives (my MS coleeges' knowledge as well :))
but couldn't find much - does any body knows sth about it?
pkThere is no hard limit on the number of connections you can have to a SQL
Server. But every connection takes up about 40KB of server memory, so there
are definitely practical limitations. Make sure that all your code drops the
connections to the server that are no longer needed.
--
Jacco Schalkwijk
SQL Server MVP
"Piotr Kowalski" <wywaltopkwlski@.oitotez2.pl> wrote in message
news:ctl2ea$oa9$1@.atlantis.news.tpi.pl...
> Hi all!
> Can you tell me if there are any default or manually set limits of
> subsequent querries / connections per user or database? I couldnt find any
> in Enterprise Manager, but my Perl / ASP aplications seems to behave as if
> it were some.
> Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
> 2000 - 3000 per hour). After doing a couple of thosands it sudenly stops -
> querry is sent but no result returns. If I limit the numer of being sent
> per minute - the program seems to work propotionally longer, but finally
> 'hangs' in the very same well.
> Moreover, ASP IIS based cooperating aplication seems to reach the same
> limit - after some time querries (actually doing SELECTS on data
> previously INSERTED by Perl script) are processed longer than ASP timeout
> allows, well the actually hangs, Profiler shows some crazy processing
> times. If I reduce the number of queries per transaction - namely changing
> a subquerry to join construction - it works ok. It all makes me think I
> hit some limits.
> I've searched the WEB archives (my MS coleeges' knowledge as well :)) but
> couldn't find much - does any body knows sth about it?
> pk|||Jacco Schalkwijk wrote:
> There is no hard limit on the number of connections you can have to a SQL
> Server. But every connection takes up about 40KB of server memory, so there
> are definitely practical limitations. Make sure that all your code drops the
> connections to the server that are no longer needed.
>
yeah, I've considered that.
Sybase DBI library unfortunately uses new connection every query, but
neither client's nor server's network stack reports other TCP
connections than being actually used (some 3 connection in every moment)
so older ones are properly disconnected. Maybe some "residues" still
remains on the SQL box, on the aplication layer, which aren't counted by
netstat but still reserve some resources (RAM). Enterprise manager shows
only as many proceses as really exists (3).
What about query limit? Or other limits that could come in question in
this case?
hk
Can you tell me if there are any default or manually set limits of
subsequent querries / connections per user or database? I couldnt find
any in Enterprise Manager, but my Perl / ASP aplications seems to behave
as if it were some.
Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
2000 - 3000 per hour). After doing a couple of thosands it sudenly stops
- querry is sent but no result returns. If I limit the numer of being
sent per minute - the program seems to work propotionally longer, but
finally 'hangs' in the very same well.
Moreover, ASP IIS based cooperating aplication seems to reach the same
limit - after some time querries (actually doing SELECTS on data
previously INSERTED by Perl script) are processed longer than ASP
timeout allows, well the actually hangs, Profiler shows some crazy
processing times. If I reduce the number of queries per transaction -
namely changing a subquerry to join construction - it works ok. It all
makes me think I hit some limits.
I've searched the WEB archives (my MS coleeges' knowledge as well :))
but couldn't find much - does any body knows sth about it?
pkThere is no hard limit on the number of connections you can have to a SQL
Server. But every connection takes up about 40KB of server memory, so there
are definitely practical limitations. Make sure that all your code drops the
connections to the server that are no longer needed.
--
Jacco Schalkwijk
SQL Server MVP
"Piotr Kowalski" <wywaltopkwlski@.oitotez2.pl> wrote in message
news:ctl2ea$oa9$1@.atlantis.news.tpi.pl...
> Hi all!
> Can you tell me if there are any default or manually set limits of
> subsequent querries / connections per user or database? I couldnt find any
> in Enterprise Manager, but my Perl / ASP aplications seems to behave as if
> it were some.
> Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
> 2000 - 3000 per hour). After doing a couple of thosands it sudenly stops -
> querry is sent but no result returns. If I limit the numer of being sent
> per minute - the program seems to work propotionally longer, but finally
> 'hangs' in the very same well.
> Moreover, ASP IIS based cooperating aplication seems to reach the same
> limit - after some time querries (actually doing SELECTS on data
> previously INSERTED by Perl script) are processed longer than ASP timeout
> allows, well the actually hangs, Profiler shows some crazy processing
> times. If I reduce the number of queries per transaction - namely changing
> a subquerry to join construction - it works ok. It all makes me think I
> hit some limits.
> I've searched the WEB archives (my MS coleeges' knowledge as well :)) but
> couldn't find much - does any body knows sth about it?
> pk|||Jacco Schalkwijk wrote:
> There is no hard limit on the number of connections you can have to a SQL
> Server. But every connection takes up about 40KB of server memory, so there
> are definitely practical limitations. Make sure that all your code drops the
> connections to the server that are no longer needed.
>
yeah, I've considered that.
Sybase DBI library unfortunately uses new connection every query, but
neither client's nor server's network stack reports other TCP
connections than being actually used (some 3 connection in every moment)
so older ones are properly disconnected. Maybe some "residues" still
remains on the SQL box, on the aplication layer, which aren't counted by
netstat but still reserve some resources (RAM). Enterprise manager shows
only as many proceses as really exists (3).
What about query limit? Or other limits that could come in question in
this case?
hk
[newbe] SQL number of querries / connections limits ?
Hi all!
Can you tell me if there are any default or manually set limits of
subsequent querries / connections per user or database? I couldnt find
any in Enterprise Manager, but my PERL / ASP aplications seems to behave
as if it were some.
Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
2000 - 3000 per hour). After doing a couple of thosands it sudenly stops
- querry is sent but no result returns. If I limit the numer of being
sent per minute - the program seems to work propotionally longer, but
finally 'hangs' in the very same well.
Moreover, ASP IIS based cooperating aplication seems to reach the same
limit - after some time querries (actually doing SELECTS on data
previously INSERTED by PERL script) are processed longer than ASP
timeout allows, well the actually hangs, Profiler shows some crazy
processing times. If I reduce the number of queries per transaction -
namely changing a subquerry to join construction - it works ok. It all
makes me think I hit some limits.
I've searched the WEB archives (my MS coleeges' knowledge as well
)
but couldn't find much - does any body knows sth about it?
pkThere is no hard limit on the number of connections you can have to a SQL
Server. But every connection takes up about 40KB of server memory, so there
are definitely practical limitations. Make sure that all your code drops the
connections to the server that are no longer needed.
Jacco Schalkwijk
SQL Server MVP
"Piotr Kowalski" <wywaltopkwlski@.oitotez2.pl> wrote in message
news:ctl2ea$oa9$1@.atlantis.news.tpi.pl...
> Hi all!
> Can you tell me if there are any default or manually set limits of
> subsequent querries / connections per user or database? I couldnt find any
> in Enterprise Manager, but my PERL / ASP aplications seems to behave as if
> it were some.
> PERL script (DBI + Sybase DBD) is doing some quite massive inserts (a
> 2000 - 3000 per hour). After doing a couple of thosands it sudenly stops -
> querry is sent but no result returns. If I limit the numer of being sent
> per minute - the program seems to work propotionally longer, but finally
> 'hangs' in the very same well.
> Moreover, ASP IIS based cooperating aplication seems to reach the same
> limit - after some time querries (actually doing SELECTS on data
> previously INSERTED by PERL script) are processed longer than ASP timeout
> allows, well the actually hangs, Profiler shows some crazy processing
> times. If I reduce the number of queries per transaction - namely changing
> a subquerry to join construction - it works ok. It all makes me think I
> hit some limits.
> I've searched the WEB archives (my MS coleeges' knowledge as well
) but
> couldn't find much - does any body knows sth about it?
> pk|||Jacco Schalkwijk wrote:
> There is no hard limit on the number of connections you can have to a SQL
> Server. But every connection takes up about 40KB of server memory, so ther
e
> are definitely practical limitations. Make sure that all your code drops t
he
> connections to the server that are no longer needed.
>
yeah, I've considered that.
Sybase DBI library unfortunately uses new connection every query, but
neither client's nor server's network stack reports other TCP
connections than being actually used (some 3 connection in every moment)
so older ones are properly disconnected. Maybe some "residues" still
remains on the SQL box, on the aplication layer, which aren't counted by
netstat but still reserve some resources (RAM). Enterprise manager shows
only as many proceses as really exists (3).
What about query limit? Or other limits that could come in question in
this case?
hk
Can you tell me if there are any default or manually set limits of
subsequent querries / connections per user or database? I couldnt find
any in Enterprise Manager, but my PERL / ASP aplications seems to behave
as if it were some.
Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
2000 - 3000 per hour). After doing a couple of thosands it sudenly stops
- querry is sent but no result returns. If I limit the numer of being
sent per minute - the program seems to work propotionally longer, but
finally 'hangs' in the very same well.
Moreover, ASP IIS based cooperating aplication seems to reach the same
limit - after some time querries (actually doing SELECTS on data
previously INSERTED by PERL script) are processed longer than ASP
timeout allows, well the actually hangs, Profiler shows some crazy
processing times. If I reduce the number of queries per transaction -
namely changing a subquerry to join construction - it works ok. It all
makes me think I hit some limits.
I've searched the WEB archives (my MS coleeges' knowledge as well
but couldn't find much - does any body knows sth about it?
pkThere is no hard limit on the number of connections you can have to a SQL
Server. But every connection takes up about 40KB of server memory, so there
are definitely practical limitations. Make sure that all your code drops the
connections to the server that are no longer needed.
Jacco Schalkwijk
SQL Server MVP
"Piotr Kowalski" <wywaltopkwlski@.oitotez2.pl> wrote in message
news:ctl2ea$oa9$1@.atlantis.news.tpi.pl...
> Hi all!
> Can you tell me if there are any default or manually set limits of
> subsequent querries / connections per user or database? I couldnt find any
> in Enterprise Manager, but my PERL / ASP aplications seems to behave as if
> it were some.
> PERL script (DBI + Sybase DBD) is doing some quite massive inserts (a
> 2000 - 3000 per hour). After doing a couple of thosands it sudenly stops -
> querry is sent but no result returns. If I limit the numer of being sent
> per minute - the program seems to work propotionally longer, but finally
> 'hangs' in the very same well.
> Moreover, ASP IIS based cooperating aplication seems to reach the same
> limit - after some time querries (actually doing SELECTS on data
> previously INSERTED by PERL script) are processed longer than ASP timeout
> allows, well the actually hangs, Profiler shows some crazy processing
> times. If I reduce the number of queries per transaction - namely changing
> a subquerry to join construction - it works ok. It all makes me think I
> hit some limits.
> I've searched the WEB archives (my MS coleeges' knowledge as well
> couldn't find much - does any body knows sth about it?
> pk|||Jacco Schalkwijk wrote:
> There is no hard limit on the number of connections you can have to a SQL
> Server. But every connection takes up about 40KB of server memory, so ther
e
> are definitely practical limitations. Make sure that all your code drops t
he
> connections to the server that are no longer needed.
>
yeah, I've considered that.
Sybase DBI library unfortunately uses new connection every query, but
neither client's nor server's network stack reports other TCP
connections than being actually used (some 3 connection in every moment)
so older ones are properly disconnected. Maybe some "residues" still
remains on the SQL box, on the aplication layer, which aren't counted by
netstat but still reserve some resources (RAM). Enterprise manager shows
only as many proceses as really exists (3).
What about query limit? Or other limits that could come in question in
this case?
hk
Sunday, February 19, 2012
Monday, February 13, 2012
[.NET and SQL Server 2000] Explicit order Insert
My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together
.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? Ho
w
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonaghLooking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:
> My current project, which I am programming in .NET, requires me to insert
a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
in
> each set MUST be kept together, and sets in each batch MUST be kept togeth
er.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updatin
g
> won't be a problem, but inserting will be) before I start inserting rows?
How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:
> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> A subset of rows must be defined in terms of shared column values. So giv
e
> each row a BatchID and a SetID.
> David
>
>
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together
.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? Ho
w
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonaghLooking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:
> My current project, which I am programming in .NET, requires me to insert
a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
in
> each set MUST be kept together, and sets in each batch MUST be kept togeth
er.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updatin
g
> won't be a problem, but inserting will be) before I start inserting rows?
How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:
> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> A subset of rows must be defined in terms of shared column values. So giv
e
> each row a BatchID and a SetID.
> David
>
>
[.NET and SQL Server 2000] Explicit order Insert
My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? How
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonagh
Looking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:
> My current project, which I am programming in .NET, requires me to insert a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows in
> each set MUST be kept together, and sets in each batch MUST be kept together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updating
> won't be a problem, but inserting will be) before I start inserting rows? How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh
|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David
|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:
> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> A subset of rows must be defined in terms of shared column values. So give
> each row a BatchID and a SetID.
> David
>
>
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? How
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonagh
Looking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:
> My current project, which I am programming in .NET, requires me to insert a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows in
> each set MUST be kept together, and sets in each batch MUST be kept together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updating
> won't be a problem, but inserting will be) before I start inserting rows? How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh
|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David
|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:
> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> A subset of rows must be defined in terms of shared column values. So give
> each row a BatchID and a SetID.
> David
>
>
[.NET and SQL Server 2000] Explicit order Insert
My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? How
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonaghLooking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:
> My current project, which I am programming in .NET, requires me to insert a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows in
> each set MUST be kept together, and sets in each batch MUST be kept together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updating
> won't be a problem, but inserting will be) before I start inserting rows? How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David|||No such thing as an ordered INSERT. What you need to do is add a batch
number or othetr identifier to tell you what batches belong together.
--
David Portas
SQL Server MVP
--|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:
> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> > My current project, which I am programming in .NET, requires me to insert
> > a
> > variable number of rows, which make up a set, in a specific order. A
> > collection of sets that are inserted one after the other is a batch. Rows
> > in
> > each set MUST be kept together, and sets in each batch MUST be kept
> > together.
> >
> > What is the best way to implement this, both on the .NET side and also the
> > SQL Server side? Should I lock the table from inserts and updates
> > (updating
> > won't be a problem, but inserting will be) before I start inserting rows?
> > How
> > would I implement a system that would roll back all the inserts that have
> > occured in that batch if an error occurs?
> A subset of rows must be defined in terms of shared column values. So give
> each row a BatchID and a SetID.
> David
>
>|||In that case please explain what you mean by a batch being "kept
together". Are you referring to an IDENTITY column here? Please post
DDL and sample data so that we can understand the problem:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? How
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonaghLooking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:
> My current project, which I am programming in .NET, requires me to insert a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows in
> each set MUST be kept together, and sets in each batch MUST be kept together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updating
> won't be a problem, but inserting will be) before I start inserting rows? How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David|||No such thing as an ordered INSERT. What you need to do is add a batch
number or othetr identifier to tell you what batches belong together.
--
David Portas
SQL Server MVP
--|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:
> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> > My current project, which I am programming in .NET, requires me to insert
> > a
> > variable number of rows, which make up a set, in a specific order. A
> > collection of sets that are inserted one after the other is a batch. Rows
> > in
> > each set MUST be kept together, and sets in each batch MUST be kept
> > together.
> >
> > What is the best way to implement this, both on the .NET side and also the
> > SQL Server side? Should I lock the table from inserts and updates
> > (updating
> > won't be a problem, but inserting will be) before I start inserting rows?
> > How
> > would I implement a system that would roll back all the inserts that have
> > occured in that batch if an error occurs?
> A subset of rows must be defined in terms of shared column values. So give
> each row a BatchID and a SetID.
> David
>
>|||In that case please explain what you mean by a batch being "kept
together". Are you referring to an IDENTITY column here? Please post
DDL and sample data so that we can understand the problem:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--
Saturday, February 11, 2012
@@Version still shows Service Pack 1
We just installed the service pack 2 to our server, so I did a select @.@.version but it's still say Service Pack 1, even though the version number is now 3054. Is that a bug in the service pack?
|||
Thanks for your help.
Thanks.
Can you post the entire string returned by SELECT @.@.VERSION? My guess is that you are seeing the Windows OS Service Pack that is also included in the @.@.VERSION call. SQL Server should return SP2.
Thanks,
Sam Lester (MSFT)
|||Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)|||
Yeah, the SP1 in this case refers to your Windows Version:
Windows NT 5.2 (Build 3790: Service Pack 1)
The Service Pack level apparently isn't listed in the @.@.VERSION as I thought it was. Build 3054 is definitely SP2, but you can also check Add/Remove Programs report or the setup regitry hives to confirm.
Thanks,
Sam Lester (MSFT)
Thanks for your help.
Thursday, February 9, 2012
@@ROWCOUNT
How can I return the number of rows returned by a select statement such as
the following one that returns zero in @.Qty ?
Alter Procedure GetMonitors
(
@.Qty INT OUTPUT
)
As
SELECT a.Suburb, a.State FROM
(
SELECT Suburb, State FROM Monitors
UNION
SELECT Suburb, State FROM Properties
WHERE Delisted Is Null
) a
GROUP BY Suburb, State
SET @.Qty = @.@.ROWCOUNT
return
Thanks in advance...Below work just fine for me... Did you define the parameter as out when you
call the proc?
USE pubs
GO
ALTER PROC p
@.qty int OUT
AS
SELECT au_lname, au_fname FROM authors
SET @.qty = @.@.ROWCOUNT
GO
--Prove that it works:
DECLARE @.rows int
EXEC p @.qty = @.rows OUT
PRINT @.rows
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"McHenry" <mchenry@.mchenry.com> wrote in message
news:44350412$0$20647$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
> How can I return the number of rows returned by a select statement such as
the following one that
> returns zero in @.Qty ?
> Alter Procedure GetMonitors
> (
> @.Qty INT OUTPUT
> )
> As
> SELECT a.Suburb, a.State FROM
> (
> SELECT Suburb, State FROM Monitors
> UNION
> SELECT Suburb, State FROM Properties
> WHERE Delisted Is Null
> ) a
> GROUP BY Suburb, State
> SET @.Qty = @.@.ROWCOUNT
> return
>
> Thanks in advance...
>|||Thanks... my mistake !
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMCL8TXWGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Below work just fine for me... Did you define the parameter as out when
> you call the proc?
> USE pubs
> GO
> ALTER PROC p
> @.qty int OUT
> AS
> SELECT au_lname, au_fname FROM authors
> SET @.qty = @.@.ROWCOUNT
> GO
> --Prove that it works:
> DECLARE @.rows int
> EXEC p @.qty = @.rows OUT
> PRINT @.rows
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "McHenry" <mchenry@.mchenry.com> wrote in message
> news:44350412$0$20647$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
>
the following one that returns zero in @.Qty ?
Alter Procedure GetMonitors
(
@.Qty INT OUTPUT
)
As
SELECT a.Suburb, a.State FROM
(
SELECT Suburb, State FROM Monitors
UNION
SELECT Suburb, State FROM Properties
WHERE Delisted Is Null
) a
GROUP BY Suburb, State
SET @.Qty = @.@.ROWCOUNT
return
Thanks in advance...Below work just fine for me... Did you define the parameter as out when you
call the proc?
USE pubs
GO
ALTER PROC p
@.qty int OUT
AS
SELECT au_lname, au_fname FROM authors
SET @.qty = @.@.ROWCOUNT
GO
--Prove that it works:
DECLARE @.rows int
EXEC p @.qty = @.rows OUT
PRINT @.rows
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"McHenry" <mchenry@.mchenry.com> wrote in message
news:44350412$0$20647$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
> How can I return the number of rows returned by a select statement such as
the following one that
> returns zero in @.Qty ?
> Alter Procedure GetMonitors
> (
> @.Qty INT OUTPUT
> )
> As
> SELECT a.Suburb, a.State FROM
> (
> SELECT Suburb, State FROM Monitors
> UNION
> SELECT Suburb, State FROM Properties
> WHERE Delisted Is Null
> ) a
> GROUP BY Suburb, State
> SET @.Qty = @.@.ROWCOUNT
> return
>
> Thanks in advance...
>|||Thanks... my mistake !
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMCL8TXWGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Below work just fine for me... Did you define the parameter as out when
> you call the proc?
> USE pubs
> GO
> ALTER PROC p
> @.qty int OUT
> AS
> SELECT au_lname, au_fname FROM authors
> SET @.qty = @.@.ROWCOUNT
> GO
> --Prove that it works:
> DECLARE @.rows int
> EXEC p @.qty = @.rows OUT
> PRINT @.rows
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "McHenry" <mchenry@.mchenry.com> wrote in message
> news:44350412$0$20647$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
>
@@ROWCOUNT
Hi all.
Why doesn't this query return number of affected rows?
UPDATE tblOrderPackages
SET packageIsSend = 1
WHERE id = 134
SELECT @.@.ROWCOUNT AS rowsAffected
Thanks,
Sharon.It should. Is this in a stored procedure or something? What do you get as a
result?
MC
"Sharon" <nothing@.null.void> wrote in message
news:u4Op3j9$FHA.1872@.TK2MSFTNGP10.phx.gbl...
> Hi all.
> Why doesn't this query return number of affected rows?
> UPDATE tblOrderPackages
> SET packageIsSend = 1
> WHERE id = 134
> SELECT @.@.ROWCOUNT AS rowsAffected
> Thanks,
> Sharon.
>|||Hi,
Its a simple query, not in a stored procedure.
When trying to get "rowsAffected" from then result set, i get this:
Item cannot be found in the collection
Sharon.
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:uqTzat9$FHA.3568@.TK2MSFTNGP09.phx.gbl...
> It should. Is this in a stored procedure or something? What do you get as
> a result?
>
> MC
>
> "Sharon" <nothing@.null.void> wrote in message
> news:u4Op3j9$FHA.1872@.TK2MSFTNGP10.phx.gbl...
>|||When i run this in the query analyzer, it works.
"Sharon" <nothing@.null.void> wrote in message
news:%23nheAw9$FHA.984@.tk2msftngp13.phx.gbl...
> Hi,
> Its a simple query, not in a stored procedure.
> When trying to get "rowsAffected" from then result set, i get this:
> Item cannot be found in the collection
> Sharon.
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:uqTzat9$FHA.3568@.TK2MSFTNGP09.phx.gbl...
>|||How are you reading this? Perhaps this is not an sql problem?
MC
"Sharon" <nothing@.null.void> wrote in message
news:%23nheAw9$FHA.984@.tk2msftngp13.phx.gbl...
> Hi,
> Its a simple query, not in a stored procedure.
> When trying to get "rowsAffected" from then result set, i get this:
> Item cannot be found in the collection
> Sharon.
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:uqTzat9$FHA.3568@.TK2MSFTNGP09.phx.gbl...
>|||Hi
Depending on your settings, it might be in the 2nd recordset returned.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:O9T4979$FHA.436@.TK2MSFTNGP10.phx.gbl...
> How are you reading this? Perhaps this is not an sql problem?
>
> MC
>
> "Sharon" <nothing@.null.void> wrote in message
> news:%23nheAw9$FHA.984@.tk2msftngp13.phx.gbl...
>|||From asp page:
sql = "UPDATE tblOrderPackages SET packageIsSend = 1 WHERE id = 134 SELECT
@.@.ROWCOUNT AS rowsAffected"
rowsAffected = dbConn.Execute(sql)("rowsAffected")
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:O9T4979$FHA.436@.TK2MSFTNGP10.phx.gbl...
> How are you reading this? Perhaps this is not an sql problem?
>
> MC
>
> "Sharon" <nothing@.null.void> wrote in message
> news:%23nheAw9$FHA.984@.tk2msftngp13.phx.gbl...
>|||Thanks Mike.
This works:
rowsAffected = db.execute(sql).NextRecordset()("rowsAffected")
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uZn12$9$FHA.1408@.TK2MSFTNGP15.phx.gbl...
> Hi
> Depending on your settings, it might be in the 2nd recordset returned.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:O9T4979$FHA.436@.TK2MSFTNGP10.phx.gbl...
>|||Sharon wrote on Tue, 13 Dec 2005 14:26:01 +0200:
> From asp page:
> sql = "UPDATE tblOrderPackages SET packageIsSend = 1 WHERE id = 134 SELECT
> @.@.ROWCOUNT AS rowsAffected"
> rowsAffected = dbConn.Execute(sql)("rowsAffected")
Try adding SET NOCOUNT ON at the start - the first recordset returned is
returning the number of rows updated by the UPDATE statement.
Dan
Why doesn't this query return number of affected rows?
UPDATE tblOrderPackages
SET packageIsSend = 1
WHERE id = 134
SELECT @.@.ROWCOUNT AS rowsAffected
Thanks,
Sharon.It should. Is this in a stored procedure or something? What do you get as a
result?
MC
"Sharon" <nothing@.null.void> wrote in message
news:u4Op3j9$FHA.1872@.TK2MSFTNGP10.phx.gbl...
> Hi all.
> Why doesn't this query return number of affected rows?
> UPDATE tblOrderPackages
> SET packageIsSend = 1
> WHERE id = 134
> SELECT @.@.ROWCOUNT AS rowsAffected
> Thanks,
> Sharon.
>|||Hi,
Its a simple query, not in a stored procedure.
When trying to get "rowsAffected" from then result set, i get this:
Item cannot be found in the collection
Sharon.
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:uqTzat9$FHA.3568@.TK2MSFTNGP09.phx.gbl...
> It should. Is this in a stored procedure or something? What do you get as
> a result?
>
> MC
>
> "Sharon" <nothing@.null.void> wrote in message
> news:u4Op3j9$FHA.1872@.TK2MSFTNGP10.phx.gbl...
>|||When i run this in the query analyzer, it works.
"Sharon" <nothing@.null.void> wrote in message
news:%23nheAw9$FHA.984@.tk2msftngp13.phx.gbl...
> Hi,
> Its a simple query, not in a stored procedure.
> When trying to get "rowsAffected" from then result set, i get this:
> Item cannot be found in the collection
> Sharon.
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:uqTzat9$FHA.3568@.TK2MSFTNGP09.phx.gbl...
>|||How are you reading this? Perhaps this is not an sql problem?
MC
"Sharon" <nothing@.null.void> wrote in message
news:%23nheAw9$FHA.984@.tk2msftngp13.phx.gbl...
> Hi,
> Its a simple query, not in a stored procedure.
> When trying to get "rowsAffected" from then result set, i get this:
> Item cannot be found in the collection
> Sharon.
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:uqTzat9$FHA.3568@.TK2MSFTNGP09.phx.gbl...
>|||Hi
Depending on your settings, it might be in the 2nd recordset returned.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:O9T4979$FHA.436@.TK2MSFTNGP10.phx.gbl...
> How are you reading this? Perhaps this is not an sql problem?
>
> MC
>
> "Sharon" <nothing@.null.void> wrote in message
> news:%23nheAw9$FHA.984@.tk2msftngp13.phx.gbl...
>|||From asp page:
sql = "UPDATE tblOrderPackages SET packageIsSend = 1 WHERE id = 134 SELECT
@.@.ROWCOUNT AS rowsAffected"
rowsAffected = dbConn.Execute(sql)("rowsAffected")
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:O9T4979$FHA.436@.TK2MSFTNGP10.phx.gbl...
> How are you reading this? Perhaps this is not an sql problem?
>
> MC
>
> "Sharon" <nothing@.null.void> wrote in message
> news:%23nheAw9$FHA.984@.tk2msftngp13.phx.gbl...
>|||Thanks Mike.
This works:
rowsAffected = db.execute(sql).NextRecordset()("rowsAffected")
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uZn12$9$FHA.1408@.TK2MSFTNGP15.phx.gbl...
> Hi
> Depending on your settings, it might be in the 2nd recordset returned.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:O9T4979$FHA.436@.TK2MSFTNGP10.phx.gbl...
>|||Sharon wrote on Tue, 13 Dec 2005 14:26:01 +0200:
> From asp page:
> sql = "UPDATE tblOrderPackages SET packageIsSend = 1 WHERE id = 134 SELECT
> @.@.ROWCOUNT AS rowsAffected"
> rowsAffected = dbConn.Execute(sql)("rowsAffected")
Try adding SET NOCOUNT ON at the start - the first recordset returned is
returning the number of rows updated by the UPDATE statement.
Dan
Subscribe to:
Posts (Atom)