sql bit data type: sql2000

Showing posts with label sql2000. Show all posts

Tuesday, March 20, 2012

Book

Can anyone recommend a sql2000 book that will also have alot of practice exa
mples?
ThanksPROFFESIONAL SQL SERVER PROGRAMMING
By Robert Vieira
WROX ISBN 1-861004-48-6
"rjrollins" <anonymous@.discussions.microsoft.com> wrote in message
news:B699BF10-8EDB-4518-ACB3-B68314713BF6@.microsoft.com...
> Can anyone recommend a sql2000 book that will also have alot of practice
examples?
> Thanks|||Is this book good for someone just learning sql?|||Be sure to download and take a look at BOL -
http://www.microsoft.com/downloads/...&DisplayLang=en
The MSPress books for SQL Server have lots of labs, etc.
Ray Higdon MCSE, MCDBA, CCNA
--
"rjrollins" <anonymous@.discussions.microsoft.com> wrote in message
news:B699BF10-8EDB-4518-ACB3-B68314713BF6@.microsoft.com...
> Can anyone recommend a sql2000 book that will also have alot of practice
examples?
> Thanks|||Hi,
PROFESSIONAL SQL SERVER 2000 programming is a good book
but for advanced users. Before reading the Professional
Edition, There is one more book you need to buy from the
same Wrox press BEGINNING SQL SERVER 2000 PROGRAMMING
I also have Murach's SQL for SQL Server which is a great
book for beginners.
HTH
Regards
Thirumal
>--Original Message--
>Is this book good for someone just learning sql?
>.
>|||Inside sql server
by kalen delany is also very good book
Regards
Ajay
"Thirumal" <treddym@.hotmail.nospam.com> wrote in message
news:14dd401c3f9b2$368078f0$a501280a@.phx
.gbl...
> Hi,
> PROFESSIONAL SQL SERVER 2000 programming is a good book
> but for advanced users. Before reading the Professional
> Edition, There is one more book you need to buy from the
> same Wrox press BEGINNING SQL SERVER 2000 PROGRAMMING
> I also have Murach's SQL for SQL Server which is a great
> book for beginners.
> HTH
> Regards
> Thirumal|||What kind of book are you after? one in which to learn admin/operation side
of sql server or one to learn how to use TSQL?
"rjrollins" <anonymous@.discussions.microsoft.com> wrote in message
news:B699BF10-8EDB-4518-ACB3-B68314713BF6@.microsoft.com...
> Can anyone recommend a sql2000 book that will also have alot of practice
examples?
> Thankssql

Book

Can anyone recommend a sql2000 book that will also have alot of practice examples
ThanksPROFFESIONAL SQL SERVER PROGRAMMING
By Robert Vieira
WROX ISBN 1-861004-48-6
"rjrollins" <anonymous@.discussions.microsoft.com> wrote in message
news:B699BF10-8EDB-4518-ACB3-B68314713BF6@.microsoft.com...
> Can anyone recommend a sql2000 book that will also have alot of practice
examples?
> Thanks|||Is this book good for someone just learning sql?|||Be sure to download and take a look at BOL -
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
The MSPress books for SQL Server have lots of labs, etc.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"rjrollins" <anonymous@.discussions.microsoft.com> wrote in message
news:B699BF10-8EDB-4518-ACB3-B68314713BF6@.microsoft.com...
> Can anyone recommend a sql2000 book that will also have alot of practice
examples?
> Thanks|||Hi,
PROFESSIONAL SQL SERVER 2000 PROGRAMMING is a good book
but for advanced users. Before reading the Professional
Edition, There is one more book you need to buy from the
same Wrox press BEGINNING SQL SERVER 2000 PROGRAMMING
I also have Murach's SQL for SQL Server which is a great
book for beginners.
HTH
Regards
Thirumal
>--Original Message--
>Is this book good for someone just learning sql?
>.
>|||Inside sql server
by kalen delany is also very good book
Regards
Ajay
"Thirumal" <treddym@.hotmail.nospam.com> wrote in message
news:14dd401c3f9b2$368078f0$a501280a@.phx.gbl...
> Hi,
> PROFESSIONAL SQL SERVER 2000 PROGRAMMING is a good book
> but for advanced users. Before reading the Professional
> Edition, There is one more book you need to buy from the
> same Wrox press BEGINNING SQL SERVER 2000 PROGRAMMING
> I also have Murach's SQL for SQL Server which is a great
> book for beginners.
> HTH
> Regards
> Thirumal
> >--Original Message--
> >Is this book good for someone just learning sql?
> >.
> >|||What kind of book are you after? one in which to learn admin/operation side
of sql server or one to learn how to use TSQL?
"rjrollins" <anonymous@.discussions.microsoft.com> wrote in message
news:B699BF10-8EDB-4518-ACB3-B68314713BF6@.microsoft.com...
> Can anyone recommend a sql2000 book that will also have alot of practice
examples?
> Thanks

Monday, March 19, 2012

Bluescreen PFN_LIST_CORRUPT

My SQL2000 server periodically bluescreen with
PFN_LIST_CORRUPT 0x0000004E message,
searching for KB in Microsoft doesn't yield helpful
results. Can you please help me on what happen
to my server?
IanI did a quick search and came up with:
Microsoft Knowledge Base Article - 291806:
How to Troubleshoot a "STOP 0x0000004E PFN_LIST_CORRUPT" Error Message
http://support.microsoft.com/?kbid=291806
HTH
Regards,
Greg Linwood
SQL Server MVP
"@._@. ¥ì¤H ^_^" <ianho@.NOSPAMwellent.com> wrote in message
news:O$#i56UjDHA.684@.TK2MSFTNGP09.phx.gbl...
> My SQL2000 server periodically bluescreen with
> PFN_LIST_CORRUPT 0x0000004E message,
> searching for KB in Microsoft doesn't yield helpful
> results. Can you please help me on what happen
> to my server?
> Ian
>

Sunday, March 11, 2012

Blocking SPID ... no apparent reason

Hi,
I hava a JAVA application that updates a SQL2000 (SP3a)database.
The application handles different types of "jobs" which effectively update the DB.

One job in particular appears to block all subsequent jobs. It comprises of a large amount of inserts/updates in a single transaction. This is necessary as it is an "all or nothing" scenario - so we cannot break the transaction into smaller ones. The transaction appears to succeed as it reaches the COMMIT TRAN statement without error.
However the records do not get written to the database.
EM indicates a large number of locks held on the tables accessed by the transaction and these do not get released.

Using the SP sp_blocker_pss80, the blocking SPID has a waittime of 0 and a waittype of 0x0000 - the lastwaittype is WRITELOG and its status is AWAITING COMMAND

I am using MS SQLSERVER JDBC Driver SP2 (considering using jTDS)

I have tried
- increasing Transaction Log size
- Moving Transaction Log to a separate Disk
- Reducing Isolation Mode to Read Uncommitted
- Set AutoCOMMIT to true
- set Close Cursor on COMMIT
- set SelectMethod to Direct - (we use Cursor by default)

None of these have succeeded in fixing the issue.

The job will succeed if it is the first/only job to access the database.
But if another job precedes it - then the blocking occurs.
I have verified that the preceding job only holds shared dataabase locks
before the blocking job is run.

Each job will use its own JDBC connections to access the database for reading
purposes, but all of the writing goes through the blocking SPID.

Any ideas?
Thanks, LiamIf you close the JDBC connection (when the transaction is complete), does that fix the problem?

-PatP|||I wonder if it is not a deadlock situation. By default deadlocks are not logged. Try running the following:

dbcc traceon (-1, 1205)

then run the big update process. This should start logging deadlock information to the SQL Errorlog.

Except for the data not being written at the end, you have described exactly what locking is designed to do. While someone is writing data to the database, no one else can read that data until they are done. You can potentially try to reduce table locks by checking that the update process is using an appropriate index. Try running the Index Tuning Wizard, and see if it makes any suggestions. This should, of course, be done on a test box first.|||Hi,
Thanks for replies.
1) Connections cannot be terminated after batch jobs complete due to nature of the application

2) DBCC TRACEON resulted in a number of the following entries in the SQL Log

Starting deadlock search 5306
Target Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:57 ECID:0 Ec:(0x484D9510) Value:0x4b0eb320
Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:57 ECID:0 Ec:(0x484D9510) Value:0x4b0eb320
Node:2 ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x42ECD510) Value:0x4b103b00
End deadlock search 5306 ... a deadlock was not found.

3) Index tuner - havent tackled yet|||I thought 1205 gave you information about deadlocks in progress. I was apparently wrong. Try this:

dbcc traceoff (-1, 1205)
go
dbcc traceon (-1, 1204)

Blocking issue in sql2000

Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
MangeshMangesh Deshpande wrote:
> Hi
> I have one user who uses SQL EM and run queries and modifies data.
> We see that when the main blocking SPID is SQLEM and command is
> "select"
> My question is
> 1. How can select command from EM be a blocking spid.
> 2. How can you cause blocling using EM?
> Please explain.
> Mangesh
I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is a multi-part message in MIME format.
--070605070403080308080508
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
A SELECT statement will hold a shared (S) lock on the specific resource
(generally a KEY or RID) and an intent shared (IS) lock on the escalated
resources (usually an IS(PAG) and IS(TAB)). The data browsing window in
SQLEM will hold locks open for big tables while you're scrolling around
through the data (just like MSAccess). If SQLEM happens to have a
shared lock on a page (PAG), which is entirely possibly, and some other
process requests an intent exclusive (IX) lock on that same page (for
example it wants to delete a row on the page, so it will acquire an
X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then the IX(PAG) will be
blocked by the S(PAG), because they're incompatible lock types on the
same resource, until the S)PAG) is release.
See BOL for more into on lock compatibility:
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp)
The important thing to remember is that SQLEM is just another client app
running SELECT queries and issuing shared lock requests, sometimes on
pages of data at a time, and often doesn't release those locks (if it's
a bit enough table and you haven't reached the last page of it yet)
until you close the child window displaying that data (that issued the
SELECT statement).
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mangesh Deshpande wrote:
>Hi
> I have one user who uses SQL EM and run queries and modifies data.
>We see that when the main blocking SPID is SQLEM and command is "select"
>My question is
>1. How can select command from EM be a blocking spid.
>2. How can you cause blocling using EM?
>Please explain.
>Mangesh
>
--070605070403080308080508
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>A SELECT statement will hold a shared (S) lock on the specific
resource (generally a KEY or RID) and an intent shared (IS) lock on the
escalated resources (usually an IS(PAG) and IS(TAB)).Â The data
browsing window in SQLEM will hold locks open for big tables while
you're scrolling around through the data (just like MSAccess).Â If
SQLEM happens to have a shared lock on a page (PAG), which is entirely
possibly, and some other process requests an intent exclusive (IX) lock
on that same page (for example it wants to delete a row on the page, so
it will acquire an X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then
the IX(PAG) will be blocked by the S(PAG), because they're incompatible
lock types on the same resource, until the S)PAG) is release. 
 
See BOL for more into on lock compatibility:
(<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp</a>) ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp</a>) 
 
The important thing to remember is that SQLEM is just another client
app running SELECT queries and issuing shared lock requests, sometimes
on pages of data at a time, and often doesn't release those locks (if
it's a bit enough table and you haven't reached the last page of it
yet) until you close the child window displaying that data (that issued
the SELECT statement). 
 
HTH 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Mangesh Deshpande wrote:
<blockquote cite="mid5515F106-3E87-4255-9DD6-7CAA798E2526@.microsoft.com"
type="cite">
<pre wrap="">Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
</pre>
</blockquote>
</body>
</html>
--070605070403080308080508--|||What is a firehose cursor ?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Mangesh Deshpande wrote:
> > Hi
> >
> > I have one user who uses SQL EM and run queries and modifies data.
> > We see that when the main blocking SPID is SQLEM and command is
> > "select"
> >
> > My question is
> > 1. How can select command from EM be a blocking spid.
> > 2. How can you cause blocling using EM?
> >
> > Please explain.
> >
> > Mangesh
> I can't stress this enough when I say that you should never use SQL EM
> for editing data, unless in development or off-hours.
> SQL EM uses a server-side, firehose cursor which is good, but does not
> fetch all data immediately (which is bad), like Query Analyzer or any
> well-written application might. The benefit is that a large table can be
> queried and viewed quickly, but most of the rows are sitting on the
> server in the result set waiting for the user to scroll though the
> results (at which point they are fetched). Until then, shared locks
> remain on some pages on the table.
> To avoid this, immediately issue a CTRL+END to move the end of the
> results. That forces SQL EM to fetch all data, thereby releasing the
> locks. If it's a large table, however, this process could be time
> consuming.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||This is a multi-part message in MIME format.
--060809080506050602090205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
A firehose cursor is essentially a forward-only, read-only cursor. From
BOL:
Rows are sent to the client in the order they are placed in the
result set, and the application must process the rows in this order.
After executing an SQL statement on a connection, the application
cannot do anything on the connection other than retrieve the rows in
the result set until all the rows have been retrieved. The only
other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:
>What is a firehose cursor ?
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
>>Mangesh Deshpande wrote:
>>
>>Hi
>>I have one user who uses SQL EM and run queries and modifies data.
>>We see that when the main blocking SPID is SQLEM and command is
>>"select"
>>My question is
>>1. How can select command from EM be a blocking spid.
>>2. How can you cause blocling using EM?
>>Please explain.
>>Mangesh
>>
>>I can't stress this enough when I say that you should never use SQL EM
>>for editing data, unless in development or off-hours.
>>SQL EM uses a server-side, firehose cursor which is good, but does not
>>fetch all data immediately (which is bad), like Query Analyzer or any
>>well-written application might. The benefit is that a large table can be
>>queried and viewed quickly, but most of the rows are sitting on the
>>server in the result set waiting for the user to scroll though the
>>results (at which point they are fetched). Until then, shared locks
>>remain on some pages on the table.
>>To avoid this, immediately issue a CTRL+END to move the end of the
>>results. That forces SQL EM to fetch all data, thereby releasing the
>>locks. If it's a large table, however, this process could be time
>>consuming.
>>
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>
>
--060809080506050602090205
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>A firehose cursor is essentially a forward-only, read-only cursor.
From BOL: 
</tt>
<blockquote><a>Rows are sent to the client in the order they are placed
in the result set, and the application must process the rows in this
order. After executing an SQL statement on a connection, the
application cannot do anything on the connection other than retrieve
the rows in the result set until all the rows have been retrieved. The
only other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client. 
</a></blockquote>
<tt>See
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a></tt> ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a></tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Hassan wrote:
<blockquote cite="midujrJPozZFHA.2420@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">What is a firehose cursor ?
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl">news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Mangesh Deshpande wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is
"select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
</pre>
</blockquote>
<pre wrap="">I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--060809080506050602090205--

Blocking issue

We are experencing some serious locking issue on sql server (sql2000). Basically a method in VB component opens a recordset which loops through a table A (about 100,000 times), inside the loop, it calls a stpred proc and does an update on table A. Each update in the loop is quite small, a few records at the most. Table A has an update trigger which insert the updated records to a history table then delete this record from table a. There are some f other queries running on table a. The big update loop on table A causes locking issues. With every update in the loop, there are hundred of locks on the delete job when it's blocking other jobs. I can't fingure out why continuous small updates opens so many locks(by looking at sp_lock) therefore blocks other process that query the same table. Any help is appreciated. thanks.Ouch. That's just asking for locking problems... Why do you this using a
cursor? Have you tried doing it in a set-based fashion?
Andrés Taylor
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:182071FF-981D-4139-A29F-11B81157BFD2@.microsoft.com...
> We are experencing some serious locking issue on sql server (sql2000).
Basically a method in VB component opens a recordset which loops through a
table A (about 100,000 times), inside the loop, it calls a stpred proc and
does an update on table A. Each update in the loop is quite small, a few
records at the most. Table A has an update trigger which insert the updated
records to a history table then delete this record from table a. There are
some f other queries running on table a. The big update loop on table A
causes locking issues. With every update in the loop, there are hundred of
locks on the delete job when it's blocking other jobs. I can't fingure out
why continuous small updates opens so many locks(by looking at sp_lock)
therefore blocks other process that query the same table. Any help is
appreciated. thanks.|||Tom,
Sounds like a very poor way in general to do updates and you should probably
look into redoing the task using as much set and server based approaches as
possible. But in this case I think the real issue might be that there is a
BEGIN TRAN issued (either implicitly or explicitly) at the beginning and
thus the whole operation is wrapped in one large transaction. You can use
sp_who2, sp_lock and profiler to see for sure what is going on.
--
Andrew J. Kelly
SQL Server MVP
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:182071FF-981D-4139-A29F-11B81157BFD2@.microsoft.com...
> We are experencing some serious locking issue on sql server (sql2000).
Basically a method in VB component opens a recordset which loops through a
table A (about 100,000 times), inside the loop, it calls a stpred proc and
does an update on table A. Each update in the loop is quite small, a few
records at the most. Table A has an update trigger which insert the updated
records to a history table then delete this record from table a. There are
some f other queries running on table a. The big update loop on table A
causes locking issues. With every update in the loop, there are hundred of
locks on the delete job when it's blocking other jobs. I can't fingure out
why continuous small updates opens so many locks(by looking at sp_lock)
therefore blocks other process that query the same table. Any help is
appreciated. thanks.

Thursday, March 8, 2012

Blocking access to a database after hours running a application

I have a problem: Access to a database on SQL2000 is blocking after many hours running a application developt with vb.net 2003, after a time this blocking is gone and there is a normal acces to this database.
The application is used for logging parameters, counters and other for a part of a factory and is also used for controlling machines. If there is a blocking then there is no possibility control this machines adequate.
The connection-string: SQL_CONNECTION_STRING = "Initial Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
The vb module i use:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Module SQL
Public SQL_CONNECTION_STRING As String
'Structuur van de resultaat array
'Aantal velden is resultaat(0).aantalvelden -> integer
'Aantal records is resultaat(0).aantalrecords -> integer
'De veldnamen zijn resultaat(0).veldnamen(0..aantalvelden) -> string
'De veldinhoud is resultaat(1..aantalrecords).veldinhoud(0..aantalve lden) -> object
Structure Resultaat_
Dim AantalVelden As Integer ' aantal velden in de recordset
Dim AantalRecords As Integer ' aantal records in de recordset
Dim VeldNamen() As String ' namen van de velden
Dim VeldInhoud() As Object ' inhoud van de velden
End Structure
' Resultaat array is de inhoud van de query uitvoering
Public Resultaat() As Resultaat_
Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As Boolean = False) As Boolean
If upd_Q = True Then
GeenResultaat(strSQL)
End If
If upd_Q = False Then
WelResultaat(strSQL)
End If
End Function
Private Sub GeenResultaat(ByVal queryStr As String)
Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection1.Open()
Dim myCommand As SqlCommand = myConnection1.CreateCommand()
Dim myTrans As SqlTransaction
' Start a local transaction
myTrans = myConnection1.BeginTransaction()
' Must assign both transaction object and connection
' to Command object for a pending local transaction
myCommand.Connection = myConnection1
myCommand.Transaction = myTrans
myCommand.CommandTimeout = 1
Try
myCommand.CommandText = queryStr
myCommand.ExecuteNonQuery()
myTrans.Commit()
Catch e As Exception
Try
myTrans.Rollback()
Catch ex As SqlException
If Not myTrans.Connection Is Nothing Then
Debug.WriteLine("An exception of type " & ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If
End Try
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while inserting the data. " & Now())
Debug.WriteLine("Neither record was written to database.")
Finally
y = y + 1
myCommand.Dispose()
myConnection1.Close()
myConnection1 = Nothing
End Try
End Sub
Private Sub WelResultaat(ByVal queryStr As String)
Dim lRow As Integer = 0 ' recordnummer
Dim lFIELD As Integer = 0 ' kolomnummer
Dim lRecordsAffected As Integer = 0 ' aantal records
Dim LFieldsAffected As Integer = 0 ' aantal velden
Dim myDataR As SqlDataReader
Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection2.Open()
Dim myCommand As SqlCommand = myConnection2.CreateCommand()
myCommand.Connection = myConnection2
Try
myCommand.CommandText = queryStr
myCommand.CommandTimeout = 2
myDataR = myCommand.ExecuteReader()
'haal het aantal velden op van de recordset
LFieldsAffected = myDataR.FieldCount
'redimensioneer de dynamische array
ReDim Resultaat(1)
ReDim Resultaat(0).VeldNamen(LFieldsAffected)
ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
'zet het aantal velden in resultaat(0).aantalvelden
Resultaat(0).AantalVelden = LFieldsAffected
'Vul de veldnamen in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
Next
'Vul de inhoud van de query uitkomst in
If myDataR.HasRows() Then
While myDataR.Read
lRow = lRow + 1
ReDim Preserve Resultaat(lRow + 1)
ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
'Vul de velden in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
Next
End While
End If
'Vul het aantal records in
Resultaat(0).AantalRecords = lRow
Catch e As Exception
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while reading the data. " & Now())
Debug.WriteLine("Neither record was read from the database.")
Finally
z = z + 1
myCommand.Dispose()
myDataR.Close()
myConnection2.Close()
myConnection2 = Nothing
End Try
End Sub
End Module
a insert query i use:
SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing, commentaar, begin_, einde_, monteur)" & _
" VALUES (" & MM02_Inv_Stor & ", 'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(), "dd-MM-yyyy HH:mm:ss") & "', '--')", True)
a update query i use:
SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" & Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
and finally a select query:
SQL.ExeQuery("SELECT * FROM mm_telling", False)
every 5 seconds i write abouth 120 update and insert queries and abouth 150 select queries.
the most tables in this database are a few rows (abouth 10 to 20)
The server is MSSQL 2000 with SP 3a
I hope there is a solution for this problem
Martin IJzerman
Martin
A certain amount of blocking is normal and unavoidable.
Do you have long-running queries?
Do you cancel the query but not rolling them back?
"MartinY" <m_ijzerman@.hotmail.com> wrote in message
news:F4A1B423-A225-42C3-A02D-858791A503AD@.microsoft.com...
> I have a problem: Access to a database on SQL2000 is blocking after many
hours running a application developt with vb.net 2003, after a time this
blocking is gone and there is a normal acces to this database.
> The application is used for logging parameters, counters and other for a
part of a factory and is also used for controlling machines. If there is a
blocking then there is no possibility control this machines adequate.
> The connection-string: SQL_CONNECTION_STRING = "Initial
Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
> The vb module i use:
> Imports System
> Imports System.Data
> Imports System.Data.SqlClient
> Module SQL
> Public SQL_CONNECTION_STRING As String
> 'Structuur van de resultaat array
> 'Aantal velden is
-> integer
> 'Aantal records is
-> integer
> 'De veldnamen zijn
ldnamen(0..aantalvelden) -> string
> 'De veldinhoud is
resultaat(1..aantalrecords).veldinhoud(0..aantalve lden) -> object
> Structure Resultaat_
> Dim AantalVelden As Integer ' aantal velden in de recordset
> Dim AantalRecords As Integer ' aantal records in de recordset
> Dim VeldNamen() As String ' namen van de velden
> Dim VeldInhoud() As Object ' inhoud van de velden
> End Structure
> ' Resultaat array is de inhoud van de query uitvoering
> Public Resultaat() As Resultaat_
>
> Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As
Boolean = False) As Boolean
> If upd_Q = True Then
> GeenResultaat(strSQL)
> End If
> If upd_Q = False Then
> WelResultaat(strSQL)
> End If
> End Function
> Private Sub GeenResultaat(ByVal queryStr As String)
> Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection1.Open()
> Dim myCommand As SqlCommand = myConnection1.CreateCommand()
> Dim myTrans As SqlTransaction
> ' Start a local transaction
> myTrans = myConnection1.BeginTransaction()
> ' Must assign both transaction object and connection
> ' to Command object for a pending local transaction
> myCommand.Connection = myConnection1
> myCommand.Transaction = myTrans
> myCommand.CommandTimeout = 1
> Try
> myCommand.CommandText = queryStr
> myCommand.ExecuteNonQuery()
> myTrans.Commit()
> Catch e As Exception
> Try
> myTrans.Rollback()
> Catch ex As SqlException
> If Not myTrans.Connection Is Nothing Then
> Debug.WriteLine("An exception of type " &
ex.GetType().ToString() & _
> " was encountered while attempting to roll
back the transaction.")
> End If
> End Try
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while inserting the data. " &
Now())
> Debug.WriteLine("Neither record was written to database.")
> Finally
> y = y + 1
> myCommand.Dispose()
> myConnection1.Close()
> myConnection1 = Nothing
> End Try
> End Sub
> Private Sub WelResultaat(ByVal queryStr As String)
> Dim lRow As Integer = 0 ' recordnummer
> Dim lFIELD As Integer = 0 ' kolomnummer
> Dim lRecordsAffected As Integer = 0 ' aantal records
> Dim LFieldsAffected As Integer = 0 ' aantal velden
> Dim myDataR As SqlDataReader
> Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection2.Open()
> Dim myCommand As SqlCommand = myConnection2.CreateCommand()
> myCommand.Connection = myConnection2
> Try
> myCommand.CommandText = queryStr
> myCommand.CommandTimeout = 2
> myDataR = myCommand.ExecuteReader()
> 'haal het aantal velden op van de recordset
> LFieldsAffected = myDataR.FieldCount
> 'redimensioneer de dynamische array
> ReDim Resultaat(1)
> ReDim Resultaat(0).VeldNamen(LFieldsAffected)
> ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
> 'zet het aantal velden in resultaat(0).aantalvelden
> Resultaat(0).AantalVelden = LFieldsAffected
> 'Vul de veldnamen in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
> Next
> 'Vul de inhoud van de query uitkomst in
> If myDataR.HasRows() Then
> While myDataR.Read
> lRow = lRow + 1
> ReDim Preserve Resultaat(lRow + 1)
> ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
> 'Vul de velden in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
> Next
> End While
> End If
> 'Vul het aantal records in
> Resultaat(0).AantalRecords = lRow
> Catch e As Exception
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while reading the data. " & Now())
> Debug.WriteLine("Neither record was read from the database.")
> Finally
> z = z + 1
> myCommand.Dispose()
> myDataR.Close()
> myConnection2.Close()
> myConnection2 = Nothing
> End Try
> End Sub
> End Module
> a insert query i use:
> SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing,
commentaar, begin_, einde_, monteur)" & _
> " VALUES (" & MM02_Inv_Stor & ",
'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(),
"dd-MM-yyyy HH:mm:ss") & "', '--')", True)
> a update query i use:
> SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" &
Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
> and finally a select query:
> SQL.ExeQuery("SELECT * FROM mm_telling", False)
> every 5 seconds i write abouth 120 update and insert queries and abouth
150 select queries.
> the most tables in this database are a few rows (abouth 10 to 20)
> The server is MSSQL 2000 with SP 3a
> I hope there is a solution for this problem
> Martin IJzerman
>
>
|||Uri
What do you mean with, 'a certain amount of blocking is normal and unavoidable.', i hope it is not true.
In my application blocking is not acceptable. before i have written a application with MySQL as database-server and after 2.5 years 24 hours 7 days a week, there are never blocking problems. The policy by the factory i work for is using MSSQLserver 2000,
and there is no possibility to change to a other db-server.
I shall try to cancel the query in state of rolling them back.
Thanks, Martin IJzerman
|||Martin
Unfortunatly it is true.
Blocking occurs when one connection to SQL Server locks one or more records,
and a second connection to SQL Server requires a conflicting lock type on
the record or records locked by the first connection. This causes the second
connection to wait until the first connection releases its locks. By
default, a connection will wait an unlimited amount of time for the blocking
lock to go away.
To help identify blocking in your databases, Microsoft has two separate
stored procedures listed on their website (one each for SQL Server 7.0 and
2000) you can use to help identify blocking problems on your SQL Servers. On
these pages are scripts you can use to create stored procedures that you can
run anytime to help you identify blocking issues.
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
"MartinY" <anonymous@.discussions.microsoft.com> wrote in message
news:D24D7BA8-48DC-4FFC-828F-7231E753EC08@.microsoft.com...
> Uri
> What do you mean with, 'a certain amount of blocking is normal and
unavoidable.', i hope it is not true.
> In my application blocking is not acceptable. before i have written a
application with MySQL as database-server and after 2.5 years 24 hours 7
days a week, there are never blocking problems. The policy by the factory i
work for is using MSSQLserver 2000, and there is no possibility to change to
a other db-server.
> I shall try to cancel the query in state of rolling them back.
> Thanks, Martin IJzerman
>
|||Ury,
I have tried 'How to Monitor SQL Server 2000 Blocking (Q271509)' script and in the logging file there is no blocking detected, and also in the profiler trace there are no special things to see. There is after 10 to 14 hours some blocking with is not detec
ted.
Martin IJzerman.

Blocking access to a database after hours running a application

I have a problem: Access to a database on SQL2000 is blocking after many hours running a application developt with vb.net 2003, after a time this blocking is gone and there is a normal acces to this database
The application is used for logging parameters, counters and other for a part of a factory and is also used for controlling machines. If there is a blocking then there is no possibility control this machines adequate
The connection-string: SQL_CONNECTION_STRING = "Initial Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;
The vb module i use
Imports Syste
Imports System.Dat
Imports System.Data.SqlClien
Module SQ
Public SQL_CONNECTION_STRING As Strin
'Structuur van de resultaat arra
'Aantal velden is resultaat(0).aantalvelden -> intege
'Aantal records is resultaat(0).aantalrecords -> intege
'De veldnamen zijn resultaat(0).veldnamen(0..aantalvelden) -> strin
'De veldinhoud is resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) -> objec
Structure Resultaat
Dim AantalVelden As Integer ' aantal velden in de recordset
Dim AantalRecords As Integer ' aantal records in de recordse
Dim VeldNamen() As String ' namen van de velde
Dim VeldInhoud() As Object ' inhoud van de velde
End Structur
' Resultaat array is de inhoud van de query uitvoerin
Public Resultaat() As Resultaat
Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As Boolean = False) As Boolea
If upd_Q = True The
GeenResultaat(strSQL
End I
If upd_Q = False The
WelResultaat(strSQL
End I
End Functio
Private Sub GeenResultaat(ByVal queryStr As String
Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING
myConnection1.Open(
Dim myCommand As SqlCommand = myConnection1.CreateCommand(
Dim myTrans As SqlTransactio
' Start a local transactio
myTrans = myConnection1.BeginTransaction(
' Must assign both transaction object and connectio
' to Command object for a pending local transactio
myCommand.Connection = myConnection
myCommand.Transaction = myTran
myCommand.CommandTimeout = Tr
myCommand.CommandText = querySt
myCommand.ExecuteNonQuery(
myTrans.Commit(
Catch e As Exceptio
Tr
myTrans.Rollback(
Catch ex As SqlExceptio
If Not myTrans.Connection Is Nothing The
Debug.WriteLine("An exception of type " & ex.GetType().ToString() &
" was encountered while attempting to roll back the transaction."
End I
End Tr
Debug.WriteLine("An exception of type " & e.GetType().ToString() &
"was encountered while inserting the data. " & Now()
Debug.WriteLine("Neither record was written to database."
Finall
y = y +
myCommand.Dispose(
myConnection1.Close(
myConnection1 = Nothin
End Tr
End Su
Private Sub WelResultaat(ByVal queryStr As String
Dim lRow As Integer = 0 ' recordnumme
Dim lFIELD As Integer = 0 ' kolomnumme
Dim lRecordsAffected As Integer = 0 ' aantal records
Dim LFieldsAffected As Integer = 0 ' aantal velde
Dim myDataR As SqlDataReade
Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING
myConnection2.Open(
Dim myCommand As SqlCommand = myConnection2.CreateCommand(
myCommand.Connection = myConnection
Tr
myCommand.CommandText = querySt
myCommand.CommandTimeout = myDataR = myCommand.ExecuteReader(
'haal het aantal velden op van de recordse
LFieldsAffected = myDataR.FieldCoun
'redimensioneer de dynamische arra
ReDim Resultaat(1
ReDim Resultaat(0).VeldNamen(LFieldsAffected
ReDim Resultaat(0).VeldInhoud(LFieldsAffected
'zet het aantal velden in resultaat(0).aantalvelde
Resultaat(0).AantalVelden = LFieldsAffecte
'Vul de veldnamen i
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
Next
'Vul de inhoud van de query uitkomst in
If myDataR.HasRows() Then
While myDataR.Read
lRow = lRow + 1
ReDim Preserve Resultaat(lRow + 1)
ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
'Vul de velden in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
Next
End While
End If
'Vul het aantal records in
Resultaat(0).AantalRecords = lRow
Catch e As Exception
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while reading the data. " & Now())
Debug.WriteLine("Neither record was read from the database.")
Finally
z = z + 1
myCommand.Dispose()
myDataR.Close()
myConnection2.Close()
myConnection2 = Nothing
End Try
End Sub
End Module
a insert query i use:
SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing, commentaar, begin_, einde_, monteur)" & _
" VALUES (" & MM02_Inv_Stor & ", 'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(), "dd-MM-yyyy HH:mm:ss") & "', '--')", True)
a update query i use:
SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" & Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
and finally a select query:
SQL.ExeQuery("SELECT * FROM mm_telling", False)
every 5 seconds i write abouth 120 update and insert queries and abouth 150 select queries.
the most tables in this database are a few rows (abouth 10 to 20)
The server is MSSQL 2000 with SP 3a
I hope there is a solution for this problem
Martin IJzermanMartin
A certain amount of blocking is normal and unavoidable.
Do you have long-running queries?
Do you cancel the query but not rolling them back?
"MartinY" <m_ijzerman@.hotmail.com> wrote in message
news:F4A1B423-A225-42C3-A02D-858791A503AD@.microsoft.com...
> I have a problem: Access to a database on SQL2000 is blocking after many
hours running a application developt with vb.net 2003, after a time this
blocking is gone and there is a normal acces to this database.
> The application is used for logging parameters, counters and other for a
part of a factory and is also used for controlling machines. If there is a
blocking then there is no possibility control this machines adequate.
> The connection-string: SQL_CONNECTION_STRING = "Initial
Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
> The vb module i use:
> Imports System
> Imports System.Data
> Imports System.Data.SqlClient
> Module SQL
> Public SQL_CONNECTION_STRING As String
> 'Structuur van de resultaat array
> 'Aantal velden is
-> integer
> 'Aantal records is
-> integer
> 'De veldnamen zijn
ldnamen(0..aantalvelden) -> string
> 'De veldinhoud is
resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) -> object
> Structure Resultaat_
> Dim AantalVelden As Integer ' aantal velden in de recordset
> Dim AantalRecords As Integer ' aantal records in de recordset
> Dim VeldNamen() As String ' namen van de velden
> Dim VeldInhoud() As Object ' inhoud van de velden
> End Structure
> ' Resultaat array is de inhoud van de query uitvoering
> Public Resultaat() As Resultaat_
>
> Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As
Boolean = False) As Boolean
> If upd_Q = True Then
> GeenResultaat(strSQL)
> End If
> If upd_Q = False Then
> WelResultaat(strSQL)
> End If
> End Function
> Private Sub GeenResultaat(ByVal queryStr As String)
> Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection1.Open()
> Dim myCommand As SqlCommand = myConnection1.CreateCommand()
> Dim myTrans As SqlTransaction
> ' Start a local transaction
> myTrans = myConnection1.BeginTransaction()
> ' Must assign both transaction object and connection
> ' to Command object for a pending local transaction
> myCommand.Connection = myConnection1
> myCommand.Transaction = myTrans
> myCommand.CommandTimeout = 1
> Try
> myCommand.CommandText = queryStr
> myCommand.ExecuteNonQuery()
> myTrans.Commit()
> Catch e As Exception
> Try
> myTrans.Rollback()
> Catch ex As SqlException
> If Not myTrans.Connection Is Nothing Then
> Debug.WriteLine("An exception of type " &
ex.GetType().ToString() & _
> " was encountered while attempting to roll
back the transaction.")
> End If
> End Try
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while inserting the data. " &
Now())
> Debug.WriteLine("Neither record was written to database.")
> Finally
> y = y + 1
> myCommand.Dispose()
> myConnection1.Close()
> myConnection1 = Nothing
> End Try
> End Sub
> Private Sub WelResultaat(ByVal queryStr As String)
> Dim lRow As Integer = 0 ' recordnummer
> Dim lFIELD As Integer = 0 ' kolomnummer
> Dim lRecordsAffected As Integer = 0 ' aantal records
> Dim LFieldsAffected As Integer = 0 ' aantal velden
> Dim myDataR As SqlDataReader
> Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection2.Open()
> Dim myCommand As SqlCommand = myConnection2.CreateCommand()
> myCommand.Connection = myConnection2
> Try
> myCommand.CommandText = queryStr
> myCommand.CommandTimeout = 2
> myDataR = myCommand.ExecuteReader()
> 'haal het aantal velden op van de recordset
> LFieldsAffected = myDataR.FieldCount
> 'redimensioneer de dynamische array
> ReDim Resultaat(1)
> ReDim Resultaat(0).VeldNamen(LFieldsAffected)
> ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
> 'zet het aantal velden in resultaat(0).aantalvelden
> Resultaat(0).AantalVelden = LFieldsAffected
> 'Vul de veldnamen in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
> Next
> 'Vul de inhoud van de query uitkomst in
> If myDataR.HasRows() Then
> While myDataR.Read
> lRow = lRow + 1
> ReDim Preserve Resultaat(lRow + 1)
> ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
> 'Vul de velden in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
> Next
> End While
> End If
> 'Vul het aantal records in
> Resultaat(0).AantalRecords = lRow
> Catch e As Exception
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while reading the data. " & Now())
> Debug.WriteLine("Neither record was read from the database.")
> Finally
> z = z + 1
> myCommand.Dispose()
> myDataR.Close()
> myConnection2.Close()
> myConnection2 = Nothing
> End Try
> End Sub
> End Module
> a insert query i use:
> SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing,
commentaar, begin_, einde_, monteur)" & _
> " VALUES (" & MM02_Inv_Stor & ",
'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(),
"dd-MM-yyyy HH:mm:ss") & "', '--')", True)
> a update query i use:
> SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" &
Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
> and finally a select query:
> SQL.ExeQuery("SELECT * FROM mm_telling", False)
> every 5 seconds i write abouth 120 update and insert queries and abouth
150 select queries.
> the most tables in this database are a few rows (abouth 10 to 20)
> The server is MSSQL 2000 with SP 3a
> I hope there is a solution for this problem
> Martin IJzerman
>
>|||Ur
What do you mean with, 'a certain amount of blocking is normal and unavoidable.', i hope it is not true
In my application blocking is not acceptable. before i have written a application with MySQL as database-server and after 2.5 years 24 hours 7 days a week, there are never blocking problems. The policy by the factory i work for is using MSSQLserver 2000, and there is no possibility to change to a other db-server
I shall try to cancel the query in state of rolling them back
Thanks, Martin IJzerma|||Martin
Unfortunatly it is true.
Blocking occurs when one connection to SQL Server locks one or more records,
and a second connection to SQL Server requires a conflicting lock type on
the record or records locked by the first connection. This causes the second
connection to wait until the first connection releases its locks. By
default, a connection will wait an unlimited amount of time for the blocking
lock to go away.
To help identify blocking in your databases, Microsoft has two separate
stored procedures listed on their website (one each for SQL Server 7.0 and
2000) you can use to help identify blocking problems on your SQL Servers. On
these pages are scripts you can use to create stored procedures that you can
run anytime to help you identify blocking issues.
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
"MartinY" <anonymous@.discussions.microsoft.com> wrote in message
news:D24D7BA8-48DC-4FFC-828F-7231E753EC08@.microsoft.com...
> Uri
> What do you mean with, 'a certain amount of blocking is normal and
unavoidable.', i hope it is not true.
> In my application blocking is not acceptable. before i have written a
application with MySQL as database-server and after 2.5 years 24 hours 7
days a week, there are never blocking problems. The policy by the factory i
work for is using MSSQLserver 2000, and there is no possibility to change to
a other db-server.
> I shall try to cancel the query in state of rolling them back.
> Thanks, Martin IJzerman
>|||Ury
I have tried 'How to Monitor SQL Server 2000 Blocking (Q271509)' script and in the logging file there is no blocking detected, and also in the profiler trace there are no special things to see. There is after 10 to 14 hours some blocking with is not detected
Martin IJzerman

Blocking access to a database after hours running a application

I have a problem: Access to a database on SQL2000 is blocking after many hou
rs running a application developt with vb.net 2003, after a time this blocki
ng is gone and there is a normal acces to this database.
The application is used for logging parameters, counters and other for a par
t of a factory and is also used for controlling machines. If there is a bloc
king then there is no possibility control this machines adequate.
The connection-string: SQL_CONNECTION_STRING = "Initial Catalog=PTSB
A02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
The vb module i use:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Module SQL
Public SQL_CONNECTION_STRING As String
'Structuur van de resultaat array
'Aantal velden is resultaat(0).aantalvelden ->
integer
'Aantal records is resultaat(0).aantalrecords ->
integer
'De veldnamen zijn resultaat(0).veldnamen(0..aantalvelden) ->
string
'De veldinhoud is resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) ->
object
Structure Resultaat_
Dim AantalVelden As Integer ' aantal velden in de recordset
Dim AantalRecords As Integer ' aantal records in de recordset
Dim VeldNamen() As String ' namen van de velden
Dim VeldInhoud() As Object ' inhoud van de velden
End Structure
' Resultaat array is de inhoud van de query uitvoering
Public Resultaat() As Resultaat_
Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As Boo
lean = False) As Boolean
If upd_Q = True Then
GeenResultaat(strSQL)
End If
If upd_Q = False Then
WelResultaat(strSQL)
End If
End Function
Private Sub GeenResultaat(ByVal queryStr As String)
Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection1.Open()
Dim myCommand As SqlCommand = myConnection1.CreateCommand()
Dim myTrans As SqlTransaction
' Start a local transaction
myTrans = myConnection1.BeginTransaction()
' Must assign both transaction object and connection
' to Command object for a pending local transaction
myCommand.Connection = myConnection1
myCommand.Transaction = myTrans
myCommand.CommandTimeout = 1
Try
myCommand.CommandText = queryStr
myCommand.ExecuteNonQuery()
myTrans.Commit()
Catch e As Exception
Try
myTrans.Rollback()
Catch ex As SqlException
If Not myTrans.Connection Is Nothing Then
Debug.WriteLine("An exception of type " & ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If
End Try
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while inserting the data. " & Now())
Debug.WriteLine("Neither record was written to database.")
Finally
y = y + 1
myCommand.Dispose()
myConnection1.Close()
myConnection1 = Nothing
End Try
End Sub
Private Sub WelResultaat(ByVal queryStr As String)
Dim lRow As Integer = 0 ' recordnummer
Dim lFIELD As Integer = 0 ' kolomnummer
Dim lRecordsAffected As Integer = 0 ' aantal records
Dim LFieldsAffected As Integer = 0 ' aantal velden
Dim myDataR As SqlDataReader
Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection2.Open()
Dim myCommand As SqlCommand = myConnection2.CreateCommand()
myCommand.Connection = myConnection2
Try
myCommand.CommandText = queryStr
myCommand.CommandTimeout = 2
myDataR = myCommand.ExecuteReader()
'haal het aantal velden op van de recordset
LFieldsAffected = myDataR.FieldCount
'redimensioneer de dynamische array
ReDim Resultaat(1)
ReDim Resultaat(0).VeldNamen(LFieldsAffected)
ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
'zet het aantal velden in resultaat(0).aantalvelden
Resultaat(0).AantalVelden = LFieldsAffected
'Vul de veldnamen in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
Next
'Vul de inhoud van de query uitkomst in
If myDataR.HasRows() Then
While myDataR.Read
lRow = lRow + 1
ReDim Preserve Resultaat(lRow + 1)
ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
'Vul de velden in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
Next
End While
End If
'Vul het aantal records in
Resultaat(0).AantalRecords = lRow
Catch e As Exception
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while reading the data. " & Now())
Debug.WriteLine("Neither record was read from the database.")
Finally
z = z + 1
myCommand.Dispose()
myDataR.Close()
myConnection2.Close()
myConnection2 = Nothing
End Try
End Sub
End Module
a insert query i use:
SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing, commentaar,
begin_, einde_, monteur)" & _
" VALUES (" & MM02_Inv_Stor & ", 'MM02', 'JAM op baan', 'einde storing', '--
--', '" & Format(Now(), "dd-MM-yyyy HH:mm:ss") & "', '--')", True)
a update query i use:
SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" & Mm00_Error_Bi
ts & " where NomItem='" & "lezen02" , True)
and finally a select query:
SQL.ExeQuery("SELECT * FROM mm_telling", False)
every 5 seconds i write abouth 120 update and insert queries and abouth 150
select queries.
the most tables in this database are a few rows (abouth 10 to 20)
The server is MSSQL 2000 with SP 3a
I hope there is a solution for this problem
Martin IJzermanMartin
A certain amount of blocking is normal and unavoidable.
Do you have long-running queries?
Do you cancel the query but not rolling them back?
"MartinY" <m_ijzerman@.hotmail.com> wrote in message
news:F4A1B423-A225-42C3-A02D-858791A503AD@.microsoft.com...
> I have a problem: Access to a database on SQL2000 is blocking after many
hours running a application developt with vb.net 2003, after a time this
blocking is gone and there is a normal acces to this database.
> The application is used for logging parameters, counters and other for a
part of a factory and is also used for controlling machines. If there is a
blocking then there is no possibility control this machines adequate.
> The connection-string: SQL_CONNECTION_STRING = "Initial
Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
> The vb module i use:
> Imports System
> Imports System.Data
> Imports System.Data.SqlClient
> Module SQL
> Public SQL_CONNECTION_STRING As String
> 'Structuur van de resultaat array
> 'Aantal velden is
-> integer
> 'Aantal records is
-> integer
> 'De veldnamen zijn
ldnamen(0..aantalvelden) -> string
> 'De veldinhoud is
resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) -> object
> Structure Resultaat_
> Dim AantalVelden As Integer ' aantal velden in de recordset
> Dim AantalRecords As Integer ' aantal records in de recordset
> Dim VeldNamen() As String ' namen van de velden
> Dim VeldInhoud() As Object ' inhoud van de velden
> End Structure
> ' Resultaat array is de inhoud van de query uitvoering
> Public Resultaat() As Resultaat_
>
> Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As
Boolean = False) As Boolean
> If upd_Q = True Then
> GeenResultaat(strSQL)
> End If
> If upd_Q = False Then
> WelResultaat(strSQL)
> End If
> End Function
> Private Sub GeenResultaat(ByVal queryStr As String)
> Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection1.Open()
> Dim myCommand As SqlCommand = myConnection1.CreateCommand()
> Dim myTrans As SqlTransaction
> ' Start a local transaction
> myTrans = myConnection1.BeginTransaction()
> ' Must assign both transaction object and connection
> ' to Command object for a pending local transaction
> myCommand.Connection = myConnection1
> myCommand.Transaction = myTrans
> myCommand.CommandTimeout = 1
> Try
> myCommand.CommandText = queryStr
> myCommand.ExecuteNonQuery()
> myTrans.Commit()
> Catch e As Exception
> Try
> myTrans.Rollback()
> Catch ex As SqlException
> If Not myTrans.Connection Is Nothing Then
> Debug.WriteLine("An exception of type " &
ex.GetType().ToString() & _
> " was encountered while attempting to roll
back the transaction.")
> End If
> End Try
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while inserting the data. " &
Now())
> Debug.WriteLine("Neither record was written to database.")
> Finally
> y = y + 1
> myCommand.Dispose()
> myConnection1.Close()
> myConnection1 = Nothing
> End Try
> End Sub
> Private Sub WelResultaat(ByVal queryStr As String)
> Dim lRow As Integer = 0 ' recordnummer
> Dim lFIELD As Integer = 0 ' kolomnummer
> Dim lRecordsAffected As Integer = 0 ' aantal records
> Dim LFieldsAffected As Integer = 0 ' aantal velden
> Dim myDataR As SqlDataReader
> Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection2.Open()
> Dim myCommand As SqlCommand = myConnection2.CreateCommand()
> myCommand.Connection = myConnection2
> Try
> myCommand.CommandText = queryStr
> myCommand.CommandTimeout = 2
> myDataR = myCommand.ExecuteReader()
> 'haal het aantal velden op van de recordset
> LFieldsAffected = myDataR.FieldCount
> 'redimensioneer de dynamische array
> ReDim Resultaat(1)
> ReDim Resultaat(0).VeldNamen(LFieldsAffected)
> ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
> 'zet het aantal velden in resultaat(0).aantalvelden
> Resultaat(0).AantalVelden = LFieldsAffected
> 'Vul de veldnamen in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
> Next
> 'Vul de inhoud van de query uitkomst in
> If myDataR.HasRows() Then
> While myDataR.Read
> lRow = lRow + 1
> ReDim Preserve Resultaat(lRow + 1)
> ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
> 'Vul de velden in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
> Next
> End While
> End If
> 'Vul het aantal records in
> Resultaat(0).AantalRecords = lRow
> Catch e As Exception
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while reading the data. " & Now())
> Debug.WriteLine("Neither record was read from the database.")
> Finally
> z = z + 1
> myCommand.Dispose()
> myDataR.Close()
> myConnection2.Close()
> myConnection2 = Nothing
> End Try
> End Sub
> End Module
> a insert query i use:
> SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing,
commentaar, begin_, einde_, monteur)" & _
> " VALUES (" & MM02_Inv_Stor & ",
'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(),
"dd-MM-yyyy HH:mm:ss") & "', '--')", True)
> a update query i use:
> SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" &
Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
> and finally a select query:
> SQL.ExeQuery("SELECT * FROM mm_telling", False)
> every 5 seconds i write abouth 120 update and insert queries and abouth
150 select queries.
> the most tables in this database are a few rows (abouth 10 to 20)
> The server is MSSQL 2000 with SP 3a
> I hope there is a solution for this problem
> Martin IJzerman
>
>|||Uri
What do you mean with, 'a certain amount of blocking is normal and unavoidab
le.', i hope it is not true.
In my application blocking is not acceptable. before i have written a applic
ation with mysql as database-server and after 2.5 years 24 hours 7 days a we
ek, there are never blocking problems. The policy by the factory i work for
is using MSSQLserver 2000,
and there is no possibility to change to a other db-server.
I shall try to cancel the query in state of rolling them back.
Thanks, Martin IJzerman|||Martin
Unfortunatly it is true.
Blocking occurs when one connection to SQL Server locks one or more records,
and a second connection to SQL Server requires a conflicting lock type on
the record or records locked by the first connection. This causes the second
connection to wait until the first connection releases its locks. By
default, a connection will wait an unlimited amount of time for the blocking
lock to go away.
To help identify blocking in your databases, Microsoft has two separate
stored procedures listed on their website (one each for SQL Server 7.0 and
2000) you can use to help identify blocking problems on your SQL Servers. On
these pages are scripts you can use to create stored procedures that you can
run anytime to help you identify blocking issues.
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
"MartinY" <anonymous@.discussions.microsoft.com> wrote in message
news:D24D7BA8-48DC-4FFC-828F-7231E753EC08@.microsoft.com...
> Uri
> What do you mean with, 'a certain amount of blocking is normal and
unavoidable.', i hope it is not true.
> In my application blocking is not acceptable. before i have written a
application with mysql as database-server and after 2.5 years 24 hours 7
days a week, there are never blocking problems. The policy by the factory i
work for is using MSSQLserver 2000, and there is no possibility to change to
a other db-server.
> I shall try to cancel the query in state of rolling them back.
> Thanks, Martin IJzerman
>|||Ury,
I have tried 'How to Monitor SQL Server 2000 Blocking (Q271509)' script and
in the logging file there is no blocking detected, and also in the profiler
trace there are no special things to see. There is after 10 to 14 hours some
blocking with is not detec
ted.
Martin IJzerman.

Tuesday, March 20, 2012

Book

Book

Monday, March 19, 2012

Bluescreen PFN_LIST_CORRUPT

Sunday, March 11, 2012

Blocking SPID ... no apparent reason

Blocking issue in sql2000

Blocking issue in sql2000

Blocking issue in sql2000

Blocking issue

Thursday, March 8, 2012

Blocking access to a database after hours running a application

Blocking access to a database after hours running a application

Blocking access to a database after hours running a application

sql bit data type

Blog Archive

About Me