sql bit data type: access

Showing posts with label access. Show all posts

Thursday, March 29, 2012

Booleans?

Hello.
I just changed the databases of a website from Access to SQL Server.
Converting the bases went smothly, but now I recieve error message to my SQL-quereries:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'True'.

The query is: "Select pieces,date from orders where active = True AND company_id <> 19 AND authorize_wait = 0 ORDER BY send_date desc"

When I change int the query to: "active = 1" the page works like before (with Access)

I supose SQL Server is able to understand commands with True/False-quereries, so what is wrong here?
Thank you for your help
/CalleMSSQL does not define a "True" or "False" variable. You need to either use a one as you have in your example OR define a local variable

declare @.True bit
set @.True = 1
Select pieces,date
from orders
where active = @.True
AND company_id <> 19
AND authorize_wait = 0
ORDER BY send_date desc|||Thank you for your response!
/Calle

Boolean Values 0,1?

Could someone please confirm that in SQL 2000 0,1 are used to refer to
Boolean values as oppose to 0,-1 in Access.
Thank you.
There is no boolean datatype in SQL Server.
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:ecXFRV9UEHA.1164@.tk2msftngp13.phx.gbl...
> Could someone please confirm that in SQL 2000 0,1 are used to refer to
> Boolean values as oppose to 0,-1 in Access.
> Thank you.
>
|||Although there is a BIT data type that has a value 0, 1, or NULL.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23GFMAc9UEHA.2840@.TK2MSFTNGP11.phx.gbl...
> There is no boolean datatype in SQL Server.
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:ecXFRV9UEHA.1164@.tk2msftngp13.phx.gbl...
>
|||"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ORxEyf9UEHA.2716@.tk2msftngp13.phx.gbl...
> Although there is a BIT data type that has a value 0, 1, or NULL.
True, but that's not a boolean (although it would be closer to a boolean
if you set the column to NOT NULL); I would expect, were there a boolean
datatype, the ability to do this:
SELECT (ColA = ColB) AS ColAEqualsColB
FROM MyTable
As well as other things like a UDF returning TRUE/FALSE (rather than having
to evaluate whether it returns 1 or 0), etc...
|||Thank you having 0/1 answers my q. :-)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23WdVqx9UEHA.484@.TK2MSFTNGP10.phx.gbl...
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ORxEyf9UEHA.2716@.tk2msftngp13.phx.gbl...
>
> True, but that's not a boolean (although it would be closer to a
boolean
> if you set the column to NOT NULL); I would expect, were there a boolean
> datatype, the ability to do this:
> SELECT (ColA = ColB) AS ColAEqualsColB
> FROM MyTable
> As well as other things like a UDF returning TRUE/FALSE (rather than
having
> to evaluate whether it returns 1 or 0), etc...
>
|||"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> Thank you having 0/1 answers my q. :-)
For what it's worth, most shops I've seen don't use the BIT datatype at
all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint to
enforce one or the other). I'm not sure if I believe it's better or worse,
but a lot of the 'experts' seem to think it is better than using BIT. The
upside is that you can index the CHAR(1), if it's highly selective (e.g. if
a very low percentage of the rows happen to be either true or false). BIT
cannot be indexed.
|||Strange as it might, most DBs I have come across used bit type for their
Boolean fields. I wonder if there are any other benefits to using Char(1)
instead of Bit (other than indexing).
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eCLb%23qGVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> For what it's worth, most shops I've seen don't use the BIT datatype
at
> all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint to
> enforce one or the other). I'm not sure if I believe it's better or
worse,
> but a lot of the 'experts' seem to think it is better than using BIT. The
> upside is that you can index the CHAR(1), if it's highly selective (e.g.
if
> a very low percentage of the rows happen to be either true or false). BIT
> cannot be indexed.
>
|||Depends if you are a Joe Celko fan or not...
http://groups.google.com/groups?as_q...ver*&lr=&hl=en
... Or I'll save you the effort and sum it up:
"BIT is hardware dependant, not portable, and bad. And you don't and never
will understand SQL as well as I do!"
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:OvBs6CIVEHA.2564@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Strange as it might, most DBs I have come across used bit type for their
> Boolean fields. I wonder if there are any other benefits to using Char(1)
> instead of Bit (other than indexing).
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eCLb%23qGVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> at
to[vbcol=seagreen]
> worse,
The[vbcol=seagreen]
> if
BIT
>
|||hi,
"Dragon" <NoSpam_Baadil@.hotmail.com> ha scritto nel messaggio
news:OvBs6CIVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Strange as it might, most DBs I have come across used bit type for their
> Boolean fields. I wonder if there are any other benefits to using Char(1)
> instead of Bit (other than indexing).
if space is an issue, more bit columns can be packed in 2 bytes...
http://msdn.microsoft.com/library/de...ba-bz_2it0.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Boolean Values 0,1?

Could someone please confirm that in SQL 2000 0,1 are used to refer to
Boolean values as oppose to 0,-1 in Access.
Thank you.There is no boolean datatype in SQL Server.
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:ecXFRV9UEHA.1164@.tk2msftngp13.phx.gbl...
> Could someone please confirm that in SQL 2000 0,1 are used to refer to
> Boolean values as oppose to 0,-1 in Access.
> Thank you.
>|||Although there is a BIT data type that has a value 0, 1, or NULL.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23GFMAc9UEHA.2840@.TK2MSFTNGP11.phx.gbl...
> There is no boolean datatype in SQL Server.
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:ecXFRV9UEHA.1164@.tk2msftngp13.phx.gbl...
>|||"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ORxEyf9UEHA.2716@.tk2msftngp13.phx.gbl...
> Although there is a BIT data type that has a value 0, 1, or NULL.
True, but that's not a boolean (although it would be closer to a boolean
if you set the column to NOT NULL); I would expect, were there a boolean
datatype, the ability to do this:
SELECT (ColA = ColB) AS ColAEqualsColB
FROM MyTable
As well as other things like a UDF returning TRUE/FALSE (rather than having
to evaluate whether it returns 1 or 0), etc...|||Thank you having 0/1 answers my q. :-)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23WdVqx9UEHA.484@.TK2MSFTNGP10.phx.gbl...
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ORxEyf9UEHA.2716@.tk2msftngp13.phx.gbl...
>
> True, but that's not a boolean (although it would be closer to a
boolean
> if you set the column to NOT NULL); I would expect, were there a boolean
> datatype, the ability to do this:
> SELECT (ColA = ColB) AS ColAEqualsColB
> FROM MyTable
> As well as other things like a UDF returning TRUE/FALSE (rather than
having
> to evaluate whether it returns 1 or 0), etc...
>|||"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> Thank you having 0/1 answers my q. :-)
For what it's worth, most shops I've seen don't use the BIT datatype at
all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint to
enforce one or the other). I'm not sure if I believe it's better or worse,
but a lot of the 'experts' seem to think it is better than using BIT. The
upside is that you can index the CHAR(1), if it's highly selective (e.g. if
a very low percentage of the rows happen to be either true or false). BIT
cannot be indexed.|||Strange as it might, most DBs I have come across used bit type for their
Boolean fields. I wonder if there are any other benefits to using Char(1)
instead of Bit (other than indexing).
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eCLb%23qGVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> For what it's worth, most shops I've seen don't use the BIT datatype
at
> all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint to
> enforce one or the other). I'm not sure if I believe it's better or
worse,
> but a lot of the 'experts' seem to think it is better than using BIT. The
> upside is that you can index the CHAR(1), if it's highly selective (e.g.
if
> a very low percentage of the rows happen to be either true or false). BIT
> cannot be indexed.
>|||Depends if you are a Joe Celko fan or not...
qlserver*&lr=&hl=en" target="_blank">http://groups.google.com/groups?as_...rver*&lr=&hl=en
... Or I'll save you the effort and sum it up:
"BIT is hardware dependant, not portable, and bad. And you don't and never
will understand SQL as well as I do!"
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:OvBs6CIVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Strange as it might, most DBs I have come across used bit type for their
> Boolean fields. I wonder if there are any other benefits to using Char(1)
> instead of Bit (other than indexing).
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eCLb%23qGVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> at
to[vbcol=seagreen]
> worse,
The[vbcol=seagreen]
> if
BIT[vbcol=seagreen]
>|||hi,
"Dragon" <NoSpam_Baadil@.hotmail.com> ha scritto nel messaggio
news:OvBs6CIVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Strange as it might, most DBs I have come across used bit type for their
> Boolean fields. I wonder if there are any other benefits to using Char(1)
> instead of Bit (other than indexing).
if space is an issue, more bit columns can be packed in 2 bytes...
2it0.asp" target="_blank">http://msdn.microsoft.com/library/d...br />
2it0.asp
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to replysql

Boolean Values 0,1?

Could someone please confirm that in SQL 2000 0,1 are used to refer to
Boolean values as oppose to 0,-1 in Access.
Thank you.There is no boolean datatype in SQL Server.
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:ecXFRV9UEHA.1164@.tk2msftngp13.phx.gbl...
> Could someone please confirm that in SQL 2000 0,1 are used to refer to
> Boolean values as oppose to 0,-1 in Access.
> Thank you.
>|||Although there is a BIT data type that has a value 0, 1, or NULL.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23GFMAc9UEHA.2840@.TK2MSFTNGP11.phx.gbl...
> There is no boolean datatype in SQL Server.
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:ecXFRV9UEHA.1164@.tk2msftngp13.phx.gbl...
> > Could someone please confirm that in SQL 2000 0,1 are used to refer to
> > Boolean values as oppose to 0,-1 in Access.
> >
> > Thank you.
> >
> >
>|||"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ORxEyf9UEHA.2716@.tk2msftngp13.phx.gbl...
> Although there is a BIT data type that has a value 0, 1, or NULL.
True, but that's not a boolean (although it would be closer to a boolean
if you set the column to NOT NULL); I would expect, were there a boolean
datatype, the ability to do this:
SELECT (ColA = ColB) AS ColAEqualsColB
FROM MyTable
As well as other things like a UDF returning TRUE/FALSE (rather than having
to evaluate whether it returns 1 or 0), etc...|||Thank you having 0/1 answers my q. :-)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23WdVqx9UEHA.484@.TK2MSFTNGP10.phx.gbl...
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ORxEyf9UEHA.2716@.tk2msftngp13.phx.gbl...
> > Although there is a BIT data type that has a value 0, 1, or NULL.
>
> True, but that's not a boolean (although it would be closer to a
boolean
> if you set the column to NOT NULL); I would expect, were there a boolean
> datatype, the ability to do this:
> SELECT (ColA = ColB) AS ColAEqualsColB
> FROM MyTable
> As well as other things like a UDF returning TRUE/FALSE (rather than
having
> to evaluate whether it returns 1 or 0), etc...
>|||"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> Thank you having 0/1 answers my q. :-)
For what it's worth, most shops I've seen don't use the BIT datatype at
all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint to
enforce one or the other). I'm not sure if I believe it's better or worse,
but a lot of the 'experts' seem to think it is better than using BIT. The
upside is that you can index the CHAR(1), if it's highly selective (e.g. if
a very low percentage of the rows happen to be either true or false). BIT
cannot be indexed.|||Strange as it might, most DBs I have come across used bit type for their
Boolean fields. I wonder if there are any other benefits to using Char(1)
instead of Bit (other than indexing).
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eCLb%23qGVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> > Thank you having 0/1 answers my q. :-)
> For what it's worth, most shops I've seen don't use the BIT datatype
at
> all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint to
> enforce one or the other). I'm not sure if I believe it's better or
worse,
> but a lot of the 'experts' seem to think it is better than using BIT. The
> upside is that you can index the CHAR(1), if it's highly selective (e.g.
if
> a very low percentage of the rows happen to be either true or false). BIT
> cannot be indexed.
>|||Depends if you are a Joe Celko fan or not...
http://groups.google.com/groups?as_q=celko%20bit&safe=images&ie=UTF-8&as_ugroup=*sqlserver*&lr=&hl=en
... Or I'll save you the effort and sum it up:
"BIT is hardware dependant, not portable, and bad. And you don't and never
will understand SQL as well as I do!"
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:OvBs6CIVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Strange as it might, most DBs I have come across used bit type for their
> Boolean fields. I wonder if there are any other benefits to using Char(1)
> instead of Bit (other than indexing).
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eCLb%23qGVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> >
> > "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> > news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> > > Thank you having 0/1 answers my q. :-)
> >
> > For what it's worth, most shops I've seen don't use the BIT datatype
> at
> > all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint
to
> > enforce one or the other). I'm not sure if I believe it's better or
> worse,
> > but a lot of the 'experts' seem to think it is better than using BIT.
The
> > upside is that you can index the CHAR(1), if it's highly selective (e.g.
> if
> > a very low percentage of the rows happen to be either true or false).
BIT
> > cannot be indexed.
> >
> >
>|||hi,
"Dragon" <NoSpam_Baadil@.hotmail.com> ha scritto nel messaggio
news:OvBs6CIVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Strange as it might, most DBs I have come across used bit type for their
> Boolean fields. I wonder if there are any other benefits to using Char(1)
> instead of Bit (other than indexing).
if space is an issue, more bit columns can be packed in 2 bytes...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Boolean in SQL Server 2005

What is the boolean equivalent for TRUE and FALSE in SQL Server 2005?

In Access true = -1 and false = 0
What should I use in SQL Server bit values?0 = false; 1 = true|||Can I write statements like Match='TRUE' instead of Match=1?|||

No, you need to use 0 and 1. SQL Server has no knowledge of true and false. This is only possible by creating custom data type via .NET.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Hi,

but this is not recommended, you should really use the internal used data types for this simple operations. Your application should transfer the value to refelect the appropiate SQL Statements and present the data types the way you want the users to see them.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||why you want to do this. Do you have requirment to write a one code for both SQL Server and Access

Sunday, March 25, 2012

Books Online

How do you install sql-server 2000 books online to a server that does
not have sql-server installed at all. I want get access the
documentation via a link from our internal intranet. Any ideas!
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I can answer the first question, you can download the most current version
from
http://www.microsoft.com/downloads/...DisplayLang=en,
and install it on the server, but I don't know how to make it work from
clients connected to that server.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave Brooks" <dbrooks@.neca.org> wrote in message
news:eqDdZAtAEHA.212@.TK2MSFTNGP12.phx.gbl...
> How do you install sql-server 2000 books online to a server that does
> not have sql-server installed at all. I want get access the
> documentation via a link from our internal intranet. Any ideas!
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Dave Brooks wrote:
> How do you install sql-server 2000 books online to a server that does
> not have sql-server installed at all. I want get access the
> documentation via a link from our internal intranet. Any ideas!
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
Doesn't it work?
1) Copy Program Files\Microsoft SQL Server\80\Tools\Books directory
2) Execute C:\WINDOWS\hh.exe
" THE_DIRECTORY_WITH_BOL_ON_YOUR_MACHINE\S
QL80.col"
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||in addition to the downloadable BOL link Stephen provided and the suggestion
from Sebastian, BOL is also available online on MSDN website at:
http://msdn.microsoft.com/library/d...
ap1.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply only to the newsgroup.
"Dave Brooks" <dbrooks@.neca.org> wrote in message
news:eqDdZAtAEHA.212@.TK2MSFTNGP12.phx.gbl...
> How do you install sql-server 2000 books online to a server that does
> not have sql-server installed at all. I want get access the
> documentation via a link from our internal intranet. Any ideas!
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Thursday, March 22, 2012

Book recommendation

I have begun to try to break out of using Access db's (97!) and have been trying out SQL Server Express 2005 along with the SQL Management Studio Express. I am a little confused with it as I am trying to use the interface inside of VB.NET 2005 as well as the management studio and sometimes I can connect from one without the other.

Anyway this points to the fact that I have a lot to learn and I was looking for a recommendation for a book that could be a tutorial for using VB.NET 2005 with SQL Server Express. I really need something that starts from square one but hopefully builds fast. Right now it appears I need to understand connection strings (when do I put ".\sqlexpress" and when do I use the server name followed by the instance for example?).

I have tried some of the books online for example and ran into a dead end with the simple tutorial (http://msdn2.microsoft.com/en-us/library/ms165732.aspx) when the headers didn't sort, I couldn't select any other pages and the edit button didn't work. I don't have a clue what happened as I followed the instructions.

Anyway if someone could recommend something that teaches using SQL Server Express while building an application with VB2005 that would be perfect.

Dana

There are a TON of books that would help you (as I have read about .5 of them and believe or not I collect them...hey i love what I do :)). Anyway as a Wrox author I recommend the following two titles:

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764595733.html

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764589237.html

and if you want to learn SQL Server 2005's CLR integration one day then this one :)

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0470054034.html

|||Derek,

Just wanted to follow up on your recommendation and say thank you. I purchased the WROX SQL Server Express Starter Kit you recommended as well as a book called Beginning SQL Server Express by Rick Dobson (mainly because Amazon gave me a package deal.. brilliant marketing move!) and although I am only beginning to read them they are exactly what I wanted .. something that starts from the very beginning and moves along fairly quickly.

My problem has been that I have lived Access97 until this so I really needed to start with a very basic understanding of the daunting world of the "server databases" and the associated management tools, leaving my cocoon of saved queries and linked tables behind and learning connection strings and stored procedures instead. This should get me on track so thank you for taking the time to reply.

Danasql

Book recommendation

Anyway if someone could recommend something that teaches using SQL Server Express while building an application with VB2005 that would be perfect.

Dana

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764595733.html

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764589237.html

and if you want to learn SQL Server 2005's CLR integration one day then this one :)

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0470054034.html

|||Derek,

Dana

Tuesday, March 20, 2012

book on EVERYTHING in SQL

i havnt used sql in a long time and now my job requires it. il be using it in access, maby oricle and maby sql server. i need a book that teaches general sql. all about updates, deletes,dates, joins, corrrelates sub queries, aliases,and anything else i could encounter that will cause me to pull my hair out in sql.maby even it it went into TSQL that would be useful.any recommendations?Did you visit Amazon.com, for example? Search through "books" and use keyword "SQL" (as you don't know which one you'll use). Check readers' reviews - could be helpful.|||The SQL Bible is probably good. I heard it will blow your mind what all it has. :) I may pick up both the one for Access and SQL. The books titled "The complete reference book of ____" are also good i heard|||I prefer:

Transact-SQL:
The Guru's Guide to Transact SQL and XML (or something like that) by Ken Henderson
Teach Yourself Transact-SQL in 21 Days by Sam's (believe it or not)

Architecture:
Inside SQL Server by Kalen Delaney (it's the best out there)

Performance Tuning:
SQL Server 2000 Performance Tuning Manual by Microsoft Press|||Each SQL implementation is different. Of the three you've listed, there is a wide variety of syntax supported. MS-SQL implementes full SQL-92. Jet (the engine used by MS-Access) implements a large subset of SQL-87 with a lot of the SQL-92 features grafted on in peculiar ways. Oracle supports most of the features of SQL-92, but using Oracle's non-standard syntax.

I don't know of any one book that covers all of the possible SQL-like variations. I like Derrick's suggestions for MS-SQL. If you are looking for a very generic SQL overview, I'd suggest SQL for Dummies. If you want quality background that will help you understand how you should use SQL (but not get far into any particular implementation), I'd recommend Joe Celko's SQL For Smarties.

In short, I don't think exactly what you want can exist, but there are lots of choices that might be "good enough" for what you really want... You just have to pick the one that seems the closest to what you think you'll need and go from there.

-PatP

Sunday, March 11, 2012

Blocking Threshold Exceeded

I have a site that calls a stored procedure to populate data on an ASP page.
50% of the time when I access this page I get a timeout error. I looked in
the Event Viewer and noticed MSSQLSERVER errors with this description:
Error: 50001, Severity: 16, State: 1
Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
The total blockers number changes occasionaly but I don't know what this
means and how to fix it. I am not doing anything complex with the stored
procedure. It is a select statement which joins multiple tables. No
inserts, updates, or deletes are being done.
Does anyone have an idea how to correct this?
Thanks.Even though the procedure that performs the query is not
inserting/updating/deleting, is this still being done by any process? When
it actually completes, what is the average and maximum runtime of the query?
Also, the following article describes various methods for identifying what
processes are blocked, what process is doing the blocking, and what specific
T-SQL statement is blocking.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
If this is a reporting type query against data potentially with uncommitted
transactions, then you may want to consider using "set transaction isolation
level read uncommitted", so long as you understand how this can impact the
results. Read up on this in Books Online.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A1346683-78D7-40CE-9F7F-3AE8521D392F@.microsoft.com...
>I have a site that calls a stored procedure to populate data on an ASP
>page.
> 50% of the time when I access this page I get a timeout error. I looked
> in
> the Event Viewer and noticed MSSQLSERVER errors with this description:
> Error: 50001, Severity: 16, State: 1
> Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
> The total blockers number changes occasionaly but I don't know what this
> means and how to fix it. I am not doing anything complex with the stored
> procedure. It is a select statement which joins multiple tables. No
> inserts, updates, or deletes are being done.
> Does anyone have an idea how to correct this?
> Thanks.
>

blocking prevent maintenance to run

I am having intermittent issues with a team that has an Access front end
that hits SQL2000sp3a+. They are wanting me to do some type of kill of all
users in the db prior to running the DBCC DBREINDEX that is done during the
night. Granted I could do a DBCC INDEXDEFRAG for online.
I see the application causing the problem. They have confirmed that some
users are leaving reports open that are bound to data, which I assume is
causing the issue.
1. Is it possible to selectively kill the process that is causing the block
as to kill all users in the db?
I am open to ideas how to address the issue. How to battle being forced to
implement a server side bandaid to a application issue.Look up Alter Database in BOL. See the restricted_user and single_user
options with Rollback Immediate.
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:OJJRZhzdFHA.612@.TK2MSFTNGP12.phx.gbl...
>I am having intermittent issues with a team that has an Access front end
>that hits SQL2000sp3a+. They are wanting me to do some type of kill of all
>users in the db prior to running the DBCC DBREINDEX that is done during the
>night. Granted I could do a DBCC INDEXDEFRAG for online.
> I see the application causing the problem. They have confirmed that some
> users are leaving reports open that are bound to data, which I assume is
> causing the issue.
> 1. Is it possible to selectively kill the process that is causing the
> block as to kill all users in the db?
> I am open to ideas how to address the issue. How to battle being forced
> to implement a server side bandaid to a application issue.
>

blocking prevent maintenance to run

I am having intermittent issues with a team that has an Access front end
that hits SQL2000sp3a+. They are wanting me to do some type of kill of all
users in the db prior to running the DBCC DBREINDEX that is done during the
night. Granted I could do a DBCC INDEXDEFRAG for online.
I see the application causing the problem. They have confirmed that some
users are leaving reports open that are bound to data, which I assume is
causing the issue.
1. Is it possible to selectively kill the process that is causing the block
as to kill all users in the db?
I am open to ideas how to address the issue. How to battle being forced to
implement a server side bandaid to a application issue.
Look up Alter Database in BOL. See the restricted_user and single_user
options with Rollback Immediate.
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:OJJRZhzdFHA.612@.TK2MSFTNGP12.phx.gbl...
>I am having intermittent issues with a team that has an Access front end
>that hits SQL2000sp3a+. They are wanting me to do some type of kill of all
>users in the db prior to running the DBCC DBREINDEX that is done during the
>night. Granted I could do a DBCC INDEXDEFRAG for online.
> I see the application causing the problem. They have confirmed that some
> users are leaving reports open that are bound to data, which I assume is
> causing the issue.
> 1. Is it possible to selectively kill the process that is causing the
> block as to kill all users in the db?
> I am open to ideas how to address the issue. How to battle being forced
> to implement a server side bandaid to a application issue.
>

blocking prevent maintenance to run

Blocking ms-access

Hi,
There is a way to block all request that come from a ms-
access ?
I am working for a company where users always connects to a SQL database to get information they need (because applications don't provide them).
The problem is when the users run queries, they lock almost all database preventing others users to work.
I can't remove SQL permissions because the application depends on it to run and i can't uninstall ms-access from the users because they need the information to do their job.
I am configuring a log shipping environment with "read-
only" permission and trying to identify users running ms-
access querying sysprocesses (to change the odbc to another server) but not everyone that uses ms-access are logged in sysprocesses as "Microsoft=AE Access", sometimes the application in sysprocesses is empty and i can't identify what user is running.
Any suggetion ?
Thanks,
Julio CarvalhoYours is probably the best solution. If another SQL Server isn't an
option due to cost or whatever, the data could always be pushed out to
an Access mdb and users could run reports by linking to it instead of
the server.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Tue, 22 Jul 2003 15:26:18 -0400, "Stefan" <st@.email.net> wrote:
>Julio,
>I have exactly the same environment here. We had to set up another server
>and use transactional replication to push data. Then all Access clients were
>pointed to this new box where they could run their reports without
>disturbing any other production application.
>"Julio Carvalho" <jc.carvalho@.terra.com.br> wrote in message
>news:01ec01c3507c$b7fbb8c0$a401280a@.phx.gbl...
>Hi,
>There is a way to block all request that come from a ms-
>access ?
>I am working for a company where users always connects to
>a SQL database to get information they need (because
>applications don't provide them).
>The problem is when the users run queries, they lock
>almost all database preventing others users to work.
>I can't remove SQL permissions because the application
>depends on it to run and i can't uninstall ms-access from
>the users because they need the information to do their
>job.
>I am configuring a log shipping environment with "read-
>only" permission and trying to identify users running ms-
>access querying sysprocesses (to change the odbc to
>another server) but not everyone that uses ms-access are
>logged in sysprocesses as "Microsoft® Access", sometimes
>the application in sysprocesses is empty and i can't
>identify what user is running.
>Any suggetion ?
>Thanks,
>Julio Carvalho
>

Blocking MS Access from linking tables...

Good morning...
I have an Access front end that uses SQL Server linked tables. SQL Server
uses Windows authentication. I have one Windows group that all Access users
are a member of. I added that group to SQL Server logins and gave it
public, datareader, and datawriter rights to the one database that's used.
My front end is locked down, but I want to stop users from creating a new
.mdb and linking SQL Server tables through DSNs or ADO connections or even
just importing the links from the actual front end.. I've tried setting the
"denydatareader" security policy - that keeps the SQL tables from being seen
in the import/link list- but also blocks read rights from the actual front
end database. I could set an Access database password on the front end to
block importing the links, but that only solves one of the three problems
and I want to stay away from Access security altogether.
Is there a way to stop users from creating their own DSNs or connection
objects or linking tables while still using Windows authentication?
Thanks.
Matthew Wells
MWells@.FirstByte.netNo. If you're using Windows authentication, and have granted
users/roles permissions on the base tables, then they can get at the
data no matter what tool they use. An alternative would be to revoke
permissions to public on the tables, and create an Access application
that does not use linked tables, but instead uses pass-through queries
to execute stored procedures. This is a lot more work since you'll
need to create an unbound FE, but it can be done. Only users who are
comfortable working with stored procedures would be able to get at the
data. Another option would be application roles, but they are a really
poor choice for linked table apps. see
http://support.microsoft.com/defaul...;EN-US;Q229564.
--Mary
On Thu, 28 Oct 2004 13:54:05 GMT, "Matthew Wells"
<MWells@.FirstByte.net> wrote:

>Good morning...
>I have an Access front end that uses SQL Server linked tables. SQL Server
>uses Windows authentication. I have one Windows group that all Access user
s
>are a member of. I added that group to SQL Server logins and gave it
>public, datareader, and datawriter rights to the one database that's used.
>My front end is locked down, but I want to stop users from creating a new
>.mdb and linking SQL Server tables through DSNs or ADO connections or even
>just importing the links from the actual front end.. I've tried setting th
e
>"denydatareader" security policy - that keeps the SQL tables from being see
n
>in the import/link list- but also blocks read rights from the actual front
>end database. I could set an Access database password on the front end to
>block importing the links, but that only solves one of the three problems
>and I want to stay away from Access security altogether.
>Is there a way to stop users from creating their own DSNs or connection
>objects or linking tables while still using Windows authentication?
>Thanks.
>Matthew Wells
>MWells@.FirstByte.net
>|||I read that article. It seems to apply only to ADO conenctions. Aren't
linked tables DAO? Does connection pooling work the same way? This is a
database that was converted from Access to SQL Server. We have to lock down
the data from any outside attempts to get it. I don't want to use SQL
authentication because I don't want to maintain two sets of security logins.
I know that using an Access form can create multiple SPIDs on SQL Server
(combo box rowsources et al). What is the downside of using Application
Roles?
Thanks.
Matthew Wells
MWells@.FirstFleet.com
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:vh62o051su3gi6fsaljgvmh1me12b8fu8p@.
4ax.com...
> No. If you're using Windows authentication, and have granted
> users/roles permissions on the base tables, then they can get at the
> data no matter what tool they use. An alternative would be to revoke
> permissions to public on the tables, and create an Access application
> that does not use linked tables, but instead uses pass-through queries
> to execute stored procedures. This is a lot more work since you'll
> need to create an unbound FE, but it can be done. Only users who are
> comfortable working with stored procedures would be able to get at the
> data. Another option would be application roles, but they are a really
> poor choice for linked table apps. see
> http://support.microsoft.com/defaul...;EN-US;Q229564.
> --Mary
> On Thu, 28 Oct 2004 13:54:05 GMT, "Matthew Wells"
> <MWells@.FirstByte.net> wrote:
>
Server[vbcol=seagreen]
users[vbcol=seagreen]
used.[vbcol=seagreen]
even[vbcol=seagreen]
the[vbcol=seagreen]
seen[vbcol=seagreen]
front[vbcol=seagreen]
to[vbcol=seagreen]
>|||Yes, connection pooling works the same way. If you are serious about
locking down the SQL Server database, then DO NOT use Access as a
front-end unless you use it in an unbound scenario as I described
below. You will need to revoke or deny permissions on the tables and
create parameterized stored procedures for all DML operations so that
users can interact with the data only through your stored procedures,
which are executed using a least-priviledged account.
Application roles are intrinsically insecure because you must store
the password that activates them on the client, where it can be
discovered by a determined attacker. If you create an unbound
application that executes under least priviledges, then there likely
won't be much penalty for using them (other than performance) or much
harm if the password is uncovered, but then there's not much benefit,
either. Using Windows authentication is more secure than SQL logins,
and if all users belong to roles that have extremely restricted
permissions that only allow them to execute parameterized stored
procedures, then that's about the best you can do. You can also
provide additional verification and validation in your stored
procedure code (for example, only allowing users to access rows that
they "own").
There is code and discussion of the techniques involved in writing an
unbound Access application in the book described in my sig. It's a lot
of work, but if security is a priority then you have to do it. Access
was designed to be easy to use, not secure.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 01 Nov 2004 14:29:21 GMT, "Matthew Wells"
<MWells@.FirstByte.net> wrote:

>I read that article. It seems to apply only to ADO conenctions. Aren't
>linked tables DAO? Does connection pooling work the same way? This is a
>database that was converted from Access to SQL Server. We have to lock dow
n
>the data from any outside attempts to get it. I don't want to use SQL
>authentication because I don't want to maintain two sets of security logins
.
>I know that using an Access form can create multiple SPIDs on SQL Server
>(combo box rowsources et al). What is the downside of using Application
>Roles?
>Thanks.
>Matthew Wells
>MWells@.FirstFleet.com
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:vh62o051su3gi6fsaljgvmh1me12b8fu8p@.
4ax.com...
>Server
>users
>used.
>even
>the
>seen
>front
>to
>

Blocking Issue

I would be grateful for any help on a blocking problem. Access 2000 adp
front end, SQL server 2000 back end, and about 15 workstations. The blocking
statement is a stored procedure that is called roughly every 15 seconds to
update a waiting list ( this is a medical database). Eventually the blocks
spread to most of the connections and we get connection time out problems
with all the work stations. I have temporarily resolved the spread issue by
using sql guard 2000 which identifies the blocking process and kills it, so
at least the problem then only applies to one workstation. When a block
occurs, in Enterprise Manager I note that it does say that there is 1 open
transaction present on the blocking SPID ( see later)
I appreciate that this may not supply enough information - however
the SP is only a select statement( select on only one table), and returns a
read only, static ADO recordset. It is not part of a transaction, and
frankly I cannot see why it should be locking this table. I have run the
index wizard on a profile trace that includes a lock up and no re indexing
suggestions are made
If anyone could point me in the right direction. I am guessing that since
this is a server side problem that the front end recordset type that is
returned may well be irrelevant.
Thank you in advance
Roddy ReidThis is a multi-part message in MIME format.
--=_NextPart_000_05EE_01C3761E.D3AC0B70
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
As a quick fix, you can change the SELECT in the proc to use the NOLOCK =hint:
SELECT
*
FROM
MyTable WITH (NOLOCK)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roderick Reid" <roddy@.roderickreid.UNSP!AMdemon.co.uk> wrote in message =news:bjikqp$7pd$1$830fa79d@.news.demon.co.uk...
I would be grateful for any help on a blocking problem. Access 2000 adp
front end, SQL server 2000 back end, and about 15 workstations. The =blocking
statement is a stored procedure that is called roughly every 15 seconds =to
update a waiting list ( this is a medical database). Eventually the =blocks
spread to most of the connections and we get connection time out =problems
with all the work stations. I have temporarily resolved the spread issue =by
using sql guard 2000 which identifies the blocking process and kills it, =so
at least the problem then only applies to one workstation. When a block
occurs, in Enterprise Manager I note that it does say that there is 1 =open
transaction present on the blocking SPID ( see later)
I appreciate that this may not supply enough information - =however
the SP is only a select statement( select on only one table), and =returns a
read only, static ADO recordset. It is not part of a transaction, and
frankly I cannot see why it should be locking this table. I have run the
index wizard on a profile trace that includes a lock up and no re =indexing
suggestions are made
If anyone could point me in the right direction. I am guessing that =since
this is a server side problem that the front end recordset type that is
returned may well be irrelevant.
Thank you in advance
Roddy Reid
--=_NextPart_000_05EE_01C3761E.D3AC0B70
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

As a quick fix, you can change the =SELECT in the proc to use the NOLOCK hint:
SELECT
=*
FROM
MyTable WITH =(NOLOCK)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Roderick Reid" wrote in message news:bjikqp$7pd$1$83=0fa79d@.news.demon.co.uk...I would be grateful for any help on a blocking problem. Access 2000 =adpfront end, SQL server 2000 back end, and about 15 workstations. The blockingstatement is a stored procedure that is called roughly every =15 seconds toupdate a waiting list ( this is a medical database). =Eventually the blocksspread to most of the connections and we get =connection time out problemswith all the work stations. I have temporarily resolved =the spread issue byusing sql guard 2000 which identifies the blocking =process and kills it, soat least the problem then only applies to one =workstation. When a blockoccurs, in Enterprise Manager I note that it does say =that there is 1 opentransaction present on the blocking SPID ( see later) I appreciate =that this may not supply enough information - howeverthe SP is only a =select statement( select on only one table), and returns aread only, static =ADO recordset. It is not part of a transaction, andfrankly I cannot see =why it should be locking this table. I have run theindex wizard on a =profile trace that includes a lock up and no re indexingsuggestions are =madeIf anyone could point me in the right direction. I am guessing that sincethis is a server side problem that the front end recordset type =that isreturned may well be irrelevant.Thank you in advanceRoddy Reid

--=_NextPart_000_05EE_01C3761E.D3AC0B70--|||This is a multi-part message in MIME format.
--=_NextPart_000_0010_01C37634.04010AE0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
fyi...
the Index Tuning Wizard is almost worthless. Did I say that outloud? In =fact it's more than worthless since it often tricks non-experts into =thinking that additional indexes really wouldn't help. The wizard misses =a lot...
hard to diagnose remotely... but I did want to make sure you knew that =the wizarad doesn't guarantee that all the indexes are really there...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:edFyQBkdDHA.2816@.TK2MSFTNGP10.phx.gbl...
As a quick fix, you can change the SELECT in the proc to use the =NOLOCK hint:
SELECT
*
FROM
MyTable WITH (NOLOCK)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roderick Reid" <roddy@.roderickreid.UNSP!AMdemon.co.uk> wrote in =message news:bjikqp$7pd$1$830fa79d@.news.demon.co.uk...
I would be grateful for any help on a blocking problem. Access 2000 =adp
front end, SQL server 2000 back end, and about 15 workstations. The =blocking
statement is a stored procedure that is called roughly every 15 =seconds to
update a waiting list ( this is a medical database). Eventually the =blocks
spread to most of the connections and we get connection time out =problems
with all the work stations. I have temporarily resolved the spread =issue by
using sql guard 2000 which identifies the blocking process and kills =it, so
at least the problem then only applies to one workstation. When a =block
occurs, in Enterprise Manager I note that it does say that there is 1 =open
transaction present on the blocking SPID ( see later)
I appreciate that this may not supply enough information - =however
the SP is only a select statement( select on only one table), and =returns a
read only, static ADO recordset. It is not part of a transaction, and
frankly I cannot see why it should be locking this table. I have run =the
index wizard on a profile trace that includes a lock up and no re =indexing
suggestions are made
If anyone could point me in the right direction. I am guessing that =since
this is a server side problem that the front end recordset type that =is
returned may well be irrelevant.
Thank you in advance
Roddy Reid
--=_NextPart_000_0010_01C37634.04010AE0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

fyi...
the Index Tuning Wizard is almost worthless. Did I =say that outloud? In fact it's more than worthless since it often tricks =non-experts into thinking that additional indexes really wouldn't help. The wizard misses =a lot...
hard to diagnose remotely... but I did want to make =sure you knew that the wizarad doesn't guarantee that all the indexes are really there...
-- Brian MoranPrincipal MentorSolid Quality LearningSQL Server MVPhttp://www.solidqualitylearning.com">http://www.solidqualitylearn=ing.com
"Tom Moreau" = wrote in message news:edFyQBkdDHA.2816=@.TK2MSFTNGP10.phx.gbl...
As a quick fix, you can change the =SELECT in the proc to use the NOLOCK hint:

SELECT
=*
FROM
MyTable =WITH (NOLOCK)

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Roderick Reid" wrote in message news:bjikqp$7pd$1$83=0fa79d@.news.demon.co.uk...I would be grateful for any help on a blocking problem. Access 2000 =adpfront end, SQL server 2000 back end, and about 15 workstations. The blockingstatement is a stored procedure that is called roughly =every 15 seconds toupdate a waiting list ( this is a medical database). =Eventually the blocksspread to most of the connections and we get =connection time out problemswith all the work stations. I have temporarily =resolved the spread issue byusing sql guard 2000 which identifies the =blocking process and kills it, soat least the problem then only applies to =one workstation. When a blockoccurs, in Enterprise Manager I note that =it does say that there is 1 opentransaction present on the blocking SPID ( =see later) I appreciate =that this may not supply enough information - howeverthe SP is only a select statement( select on only one table), and returns =aread only, static ADO recordset. It is not part of a transaction, andfrankly =I cannot see why it should be locking this table. I have run theindex =wizard on a profile trace that includes a lock up and no re =indexingsuggestions are madeIf anyone could point me in the right direction. I am guessing that sincethis is a server side problem that the =front end recordset type that isreturned may well be =irrelevant.Thank you in advanceRoddy Reid

--=_NextPart_000_0010_01C37634.04010AE0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0064_01C3764D.E30C7670
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I have to agree with Brian on that assessment. I stopped using the ITW =a long time ago.
--
Andrew J. Kelly
SQL Server MVP
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message =news:%23S7E6WldDHA.1712@.tk2msftngp13.phx.gbl...
fyi...
the Index Tuning Wizard is almost worthless. Did I say that outloud? =In fact it's more than worthless since it often tricks non-experts into =thinking that additional indexes really wouldn't help. The wizard misses =a lot...
hard to diagnose remotely... but I did want to make sure you knew that =the wizarad doesn't guarantee that all the indexes are really there...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:edFyQBkdDHA.2816@.TK2MSFTNGP10.phx.gbl...
As a quick fix, you can change the SELECT in the proc to use the =NOLOCK hint:
SELECT
*
FROM
MyTable WITH (NOLOCK)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roderick Reid" <roddy@.roderickreid.UNSP!AMdemon.co.uk> wrote in =message news:bjikqp$7pd$1$830fa79d@.news.demon.co.uk...
I would be grateful for any help on a blocking problem. Access 2000 =adp
front end, SQL server 2000 back end, and about 15 workstations. The =blocking
statement is a stored procedure that is called roughly every 15 =seconds to
update a waiting list ( this is a medical database). Eventually the =blocks
spread to most of the connections and we get connection time out =problems
with all the work stations. I have temporarily resolved the spread =issue by
using sql guard 2000 which identifies the blocking process and kills =it, so
at least the problem then only applies to one workstation. When a =block
occurs, in Enterprise Manager I note that it does say that there is =1 open
transaction present on the blocking SPID ( see later)
I appreciate that this may not supply enough information - =however
the SP is only a select statement( select on only one table), and =returns a
read only, static ADO recordset. It is not part of a transaction, =and
frankly I cannot see why it should be locking this table. I have run =the
index wizard on a profile trace that includes a lock up and no re =indexing
suggestions are made
If anyone could point me in the right direction. I am guessing that =since
this is a server side problem that the front end recordset type that =is
returned may well be irrelevant.
Thank you in advance
Roddy Reid
--=_NextPart_000_0064_01C3764D.E30C7670
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

I have to agree with Brian on that assessment. I stopped using the ITW a long time ago.
-- Andrew J. KellySQL Server MVP
"Brian Moran" wrote in message news:%23S7E6WldDHA.=1712@.tk2msftngp13.phx.gbl...
fyi...

the Index Tuning Wizard is almost worthless. Did I =say that outloud? In fact it's more than worthless since it often tricks =non-experts into thinking that additional indexes really wouldn't help. The wizard =misses a lot...

hard to diagnose remotely... but I did want to =make sure you knew that the wizarad doesn't guarantee that all the indexes are =really there...
-- Brian MoranPrincipal MentorSolid Quality LearningSQL Server MVPhttp://www.solidqualitylearning.com">http://www.solidqualitylearn=ing.com

"Tom Moreau" = wrote in message news:edFyQBkdDHA.2816=@.TK2MSFTNGP10.phx.gbl...
As a quick fix, you can change the =SELECT in the proc to use the NOLOCK hint:

SELECT
=*
FROM
MyTable =WITH (NOLOCK)

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Roderick Reid" wrote in message news:bjikqp$7pd$1$83=0fa79d@.news.demon.co.uk...I would be grateful for any help on a blocking problem. Access 2000 adpfront end, SQL server 2000 back end, and about 15 =workstations. The blockingstatement is a stored procedure that is called roughly =every 15 seconds toupdate a waiting list ( this is a medical database). Eventually the blocksspread to most of the connections and we =get connection time out problemswith all the work stations. I =have temporarily resolved the spread issue byusing sql guard 2000 =which identifies the blocking process and kills it, soat least the =problem then only applies to one workstation. When a blockoccurs, in =Enterprise Manager I note that it does say that there is 1 opentransaction =present on the blocking SPID ( see later) I =appreciate that this may not supply enough information - howeverthe SP is only a = select statement( select on only one table), and returns =aread only, static ADO recordset. It is not part of a transaction, =andfrankly I cannot see why it should be locking this table. I have run =theindex wizard on a profile trace that includes a lock up and no re indexingsuggestions are madeIf anyone could point me in =the right direction. I am guessing that sincethis is a server =side problem that the front end recordset type that isreturned =may well be irrelevant.Thank you in advanceRoddy Reid

--=_NextPart_000_0064_01C3764D.E30C7670--|||This is a multi-part message in MIME format.
--=_NextPart_000_0AB5_01C376B1.528BABC0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Tom,
Doesn't NOLOCK give out dirty reads ... This under the situation =discussed would mean that the data viewed may not be correct ... I guess =if the user would not like to have consistency of data then the solution =would be fine. -- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:edFyQBkdDHA.2816@.TK2MSFTNGP10.phx.gbl...
As a quick fix, you can change the SELECT in the proc to use the =NOLOCK hint:
SELECT
*
FROM
MyTable WITH (NOLOCK)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roderick Reid" <roddy@.roderickreid.UNSP!AMdemon.co.uk> wrote in =message news:bjikqp$7pd$1$830fa79d@.news.demon.co.uk...
I would be grateful for any help on a blocking problem. Access 2000 =adp
front end, SQL server 2000 back end, and about 15 workstations. The =blocking
statement is a stored procedure that is called roughly every 15 =seconds to
update a waiting list ( this is a medical database). Eventually the =blocks
spread to most of the connections and we get connection time out =problems
with all the work stations. I have temporarily resolved the spread =issue by
using sql guard 2000 which identifies the blocking process and kills =it, so
at least the problem then only applies to one workstation. When a =block
occurs, in Enterprise Manager I note that it does say that there is 1 =open
transaction present on the blocking SPID ( see later)
I appreciate that this may not supply enough information - =however
the SP is only a select statement( select on only one table), and =returns a
read only, static ADO recordset. It is not part of a transaction, and
frankly I cannot see why it should be locking this table. I have run =the
index wizard on a profile trace that includes a lock up and no re =indexing
suggestions are made
If anyone could point me in the right direction. I am guessing that =since
this is a server side problem that the front end recordset type that =is
returned may well be irrelevant.
Thank you in advance
Roddy Reid
--=_NextPart_000_0AB5_01C376B1.528BABC0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Tom,
Doesn't NOLOCK give out dirty =reads ... This under the situation discussed would mean that the data viewed may not be =correct ... I guess if the user would not like to have consistency of data then =the solution would be fine.
-- HTH,Vinod KumarMCSE, DBA, MCADhttp://www.extremeexperts.com<=/DIV>
"Tom Moreau" = wrote in message news:edFyQBkdDHA.2816=@.TK2MSFTNGP10.phx.gbl...
As a quick fix, you can change the =SELECT in the proc to use the NOLOCK hint:

SELECT
=*
FROM
MyTable =WITH (NOLOCK)

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Roderick Reid" wrote in message news:bjikqp$7pd$1$83=0fa79d@.news.demon.co.uk...I would be grateful for any help on a blocking problem. Access 2000 =adpfront end, SQL server 2000 back end, and about 15 workstations. The blockingstatement is a stored procedure that is called roughly =every 15 seconds toupdate a waiting list ( this is a medical database). =Eventually the blocksspread to most of the connections and we get =connection time out problemswith all the work stations. I have temporarily =resolved the spread issue byusing sql guard 2000 which identifies the =blocking process and kills it, soat least the problem then only applies to =one workstation. When a blockoccurs, in Enterprise Manager I note that =it does say that there is 1 opentransaction present on the blocking SPID ( =see later) I appreciate =that this may not supply enough information - howeverthe SP is only a select statement( select on only one table), and returns =aread only, static ADO recordset. It is not part of a transaction, andfrankly =I cannot see why it should be locking this table. I have run theindex =wizard on a profile trace that includes a lock up and no re =indexingsuggestions are madeIf anyone could point me in the right direction. I am guessing that sincethis is a server side problem that the =front end recordset type that isreturned may well be =irrelevant.Thank you in advanceRoddy Reid

--=_NextPart_000_0AB5_01C376B1.528BABC0--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C3769F.F2448400
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Of course. I was suggesting this as a "quick fix". As always, it =really depends on the app. If all they are doing is browsing and =inconsistent data is tolerable, then you're fine. Alternately, you can =use the READPAST hint. This way, you skip the ones that are currently =locked.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message =news:uE2XBModDHA.1128@.tk2msftngp13.phx.gbl...
Tom,
Doesn't NOLOCK give out dirty reads ... This under the situation =discussed would mean that the data viewed may not be correct ... I guess =if the user would not like to have consistency of data then the solution =would be fine. -- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:edFyQBkdDHA.2816@.TK2MSFTNGP10.phx.gbl...
As a quick fix, you can change the SELECT in the proc to use the =NOLOCK hint:
SELECT
*
FROM
MyTable WITH (NOLOCK)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roderick Reid" <roddy@.roderickreid.UNSP!AMdemon.co.uk> wrote in =message news:bjikqp$7pd$1$830fa79d@.news.demon.co.uk...
I would be grateful for any help on a blocking problem. Access 2000 =adp
front end, SQL server 2000 back end, and about 15 workstations. The =blocking
statement is a stored procedure that is called roughly every 15 =seconds to
update a waiting list ( this is a medical database). Eventually the =blocks
spread to most of the connections and we get connection time out =problems
with all the work stations. I have temporarily resolved the spread =issue by
using sql guard 2000 which identifies the blocking process and kills =it, so
at least the problem then only applies to one workstation. When a =block
occurs, in Enterprise Manager I note that it does say that there is 1 =open
transaction present on the blocking SPID ( see later)
I appreciate that this may not supply enough information - =however
the SP is only a select statement( select on only one table), and =returns a
read only, static ADO recordset. It is not part of a transaction, and
frankly I cannot see why it should be locking this table. I have run =the
index wizard on a profile trace that includes a lock up and no re =indexing
suggestions are made
If anyone could point me in the right direction. I am guessing that =since
this is a server side problem that the front end recordset type that =is
returned may well be irrelevant.
Thank you in advance
Roddy Reid
--=_NextPart_000_000D_01C3769F.F2448400
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Of course. I was suggesting this =as a "quick fix". As always, it really depends on the app. If all they =are doing is browsing and inconsistent data is tolerable, then you're fine. Alternately, you can use the READPAST hint. This way, you skip the =ones that are currently locked.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Vinodk" wrote in message news:uE2XBModDHA.1128=@.tk2msftngp13.phx.gbl...
Tom,
Doesn't NOLOCK give out dirty =reads ... This under the situation discussed would mean that the data viewed may not be =correct ... I guess if the user would not like to have consistency of data then =the solution would be fine.
-- HTH,Vinod KumarMCSE, DBA, MCADhttp://www.extremeexperts.com<=/DIV>
"Tom Moreau" = wrote in message news:edFyQBkdDHA.2816=@.TK2MSFTNGP10.phx.gbl...
As a quick fix, you can change the =SELECT in the proc to use the NOLOCK hint:

SELECT
=*
FROM
MyTable =WITH (NOLOCK)

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Roderick Reid" wrote in message news:bjikqp$7pd$1$83=0fa79d@.news.demon.co.uk...I would be grateful for any help on a blocking problem. Access 2000 =adpfront end, SQL server 2000 back end, and about 15 workstations. The blockingstatement is a stored procedure that is called roughly =every 15 seconds toupdate a waiting list ( this is a medical database). =Eventually the blocksspread to most of the connections and we get =connection time out problemswith all the work stations. I have temporarily =resolved the spread issue byusing sql guard 2000 which identifies the =blocking process and kills it, soat least the problem then only applies to =one workstation. When a blockoccurs, in Enterprise Manager I note that =it does say that there is 1 opentransaction present on the blocking SPID ( =see later) I appreciate =that this may not supply enough information - howeverthe SP is only a select statement( select on only one table), and returns =aread only, static ADO recordset. It is not part of a transaction, andfrankly =I cannot see why it should be locking this table. I have run theindex =wizard on a profile trace that includes a lock up and no re =indexingsuggestions are madeIf anyone could point me in the right direction. I am guessing that sincethis is a server side problem that the =front end recordset type that isreturned may well be =irrelevant.Thank you in advanceRoddy Reid

--=_NextPart_000_000D_01C3769F.F2448400--|||On Mon, 8 Sep 2003 20:19:27 +0100, "Roderick Reid"
<roddy@.roderickreid.UNSP!AMdemon.co.uk> wrote:
> I appreciate that this may not supply enough information -
Right, it's not.
> however
>the SP is only a select statement( select on only one table), and returns a
>read only, static ADO recordset. It is not part of a transaction, and
>frankly I cannot see why it should be locking this table.
Well, me neither. So, it probably isn't.
>I have run the
>index wizard on a profile trace that includes a lock up and no re indexing
>suggestions are made
>If anyone could point me in the right direction. I am guessing that since
>this is a server side problem that the front end recordset type that is
>returned may well be irrelevant.
Look through the profiler trace to see whatever else the blocking SPID
is doing.
I haven't used Access with SQLServer for several years, but I recall
that Access was always very "clever" at doing obscure things with
connections and recordsets that caused these kinds of problems.
Joshua Stern

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

Thursday, March 29, 2012

Booleans?

Boolean Values 0,1?

Boolean Values 0,1?

Boolean Values 0,1?

Boolean in SQL Server 2005

Sunday, March 25, 2012

Books Online

Thursday, March 22, 2012

Book recommendation

Book recommendation

Tuesday, March 20, 2012

book on EVERYTHING in SQL

Sunday, March 11, 2012

Blocking Threshold Exceeded

blocking prevent maintenance to run

blocking prevent maintenance to run

blocking prevent maintenance to run

Blocking ms-access

Blocking MS Access from linking tables...

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

sql bit data type

Blog Archive

About Me