disk outage and the database

I was at the SQL Server launch in Detroit and they talked about if a disk outage occurs with SS05, then the database doesn't go down, just access to that data. So, it was conveyed that in SS00 the entire database is lost. For SS05 however, whenever someone talks about this feature, it was always with table partitioning. Is this just conicidence, or something that table partitioning helps with and you need it?

If I didn't use table partitioning, and a disk went down, wouldn't the data in that data file just be unavailable -- can someone set me straight. thanks..What this feature actually relates to is using more than one filegroup.
In SQl Server 2000, if any filegroup went offline, the entire database was offline.

With SQL Server 2005, as long as the primary filegroup is online, you can still have access to the database. Obviously, you cannot access data in the filegroup which is offline, but you can access data in other, online filegroups.

Disappearing Records

I am running an Access 2000 MDB against a SQL 7 back end, using ODBC linked
tables over a LAN and a WAN. The system has been operational for years with
relatively few problems.

Recently, WAN users have been reporting several hundred records disappearing
at a time. The records are all sequential, and they're in a table that
contains about 60,000 records. This all started last week at about the same
time (not sure if before or after) that the WAN went down for a couple of
hours for an unknown reason. Since then, every once in a while, a WAN user
will report that several hundred records in a block are just "missing."
Then, an hour or two later, they reappear.

Any ideas as to what's going on or what can be done to rectify this?

Thanks!

Neilhi Neil,

Neil wrote:

Quote:

Originally Posted by

Any ideas as to what's going on or what can be done to rectify this?

Bad WLAN performance, but the network stack is not aware of it. So your
session thinks it is okay, but it's not.

mfG
--stefan <--|||Any ideas on what can be done to rectify it?

"Stefan Hoffmann" <stefan.hoffmann@.explido.dewrote in message
news:OFe9nMmOHHA.2232@.TK2MSFTNGP02.phx.gbl...

Quote:

Originally Posted by

hi Neil,
>
Neil wrote:

Quote:

Originally Posted by

>Any ideas as to what's going on or what can be done to rectify this?

Bad WLAN performance, but the network stack is not aware of it. So your
session thinks it is okay, but it's not.
>
>
mfG
--stefan <--

|||hi Neil,

Neil wrote:

Quote:

Originally Posted by

Any ideas on what can be done to rectify it?

Try using a permanent open recordset.
Use filterted recordsets.
Use serverside filters (views).

mfG
--stefan <--|||OK, thanks. I guess I was thinking that, since this system has been in place
for years without these problems; and since these problems just started last
week when the WAN went down for a few hours; that perhaps there was
something on the network end that can be done to rectify it. This has never
been a problem before, so something must have happened to cause it. The
database hasn't changed very much in years.

Thanks,

Neil

"Stefan Hoffmann" <stefan.hoffmann@.explido.dewrote in message
news:e3lZ9rmOHHA.3872@.TK2MSFTNGP06.phx.gbl...

Quote:

Originally Posted by

hi Neil,
>
Neil wrote:

Quote:

Originally Posted by

>Any ideas on what can be done to rectify it?

Try using a permanent open recordset.
Use filterted recordsets.
Use serverside filters (views).
>
>
mfG
--stefan <--

|||"Neil" <nospam@.nospam.netwrote in message
news:XNvrh.12389$yx6.3307@.newsread2.news.pas.earth link.net...

Quote:

Originally Posted by

OK, thanks. I guess I was thinking that, since this system has been in
place for years without these problems; and since these problems just
started last week when the WAN went down for a few hours; that perhaps
there was something on the network end that can be done to rectify it.
This has never been a problem before, so something must have happened to
cause it. The database hasn't changed very much in years.

It is surprising how often only one record is needed for a particular
business function, if it exists, or none, if it does not. Limiting the
number of records retrieved by Query Criteria is a very good way to speed up
client-server performance, and you'll never "lose" several hundred records
on a one-record retrieval. A client-server application which retrieves
hundreds or thousands of records, or more, and then "finds" the one of
interest is not very efficient and effective.

That said, the "fix" is to correct the LAN/WAN problems. I once observed a
company who got a really noticeable improvement when they replaced about 95%
of their network support staff. {:-) or :-(, depending on whether you were
part of the new or the old staff} That said, because a LAN is totally
within control of the network support team, it's much easier to fix than a
WAN, which relies on external providers for some of its services.

Larry Linson
Microsoft Access MVP|||Larry,

I agree with what you wrote 100%. The application was inherited by me after
it was converted to an Access back end from an off-the-shelf product.
There's much that needs to be improved with it. One of the major changes
that we are looking to make is to change the way it works with records in
the way you describe. Bringing over tens of thousands of records is
ridiculous. I was considering giving the user options to work with small,
pre-defined sets of records based on business needs, with the additional
option of a custom set based on search criteria (with the sets being
compiled in the back end, of course, and then brought over). But your note
has made me rethink this. In what situations would the user need anything
*but* a custom set? So I'm rethinking that paradigm, and may just have the
user pull over whatever set of records they need. So thanks for that.

Getting back to the network situation, the problem only seems to manifest
itself in the WAN. The LAN users aren't experiencing this problem. And, as
noted, it only started about a week ago (after many years of the database
and WAN being up and running without this problem) and on the same day that
the WAN went down for several hours. So this tells me that SOMETHING
happened on that day that hasn't yet been rectified. But I know what the
network guy's going to say: everything's working fine now; he doesn't see
any problem with anything. And round and round we go.....

Thanks,

Neil

"Larry Linson" <bouncer@.localhost.notwrote in message
news:iMCrh.3792$E35.2163@.trnddc02...

Quote:

Originally Posted by

>
"Neil" <nospam@.nospam.netwrote in message
news:XNvrh.12389$yx6.3307@.newsread2.news.pas.earth link.net...

Quote:

Originally Posted by

>OK, thanks. I guess I was thinking that, since this system has been in
>place for years without these problems; and since these problems just
>started last week when the WAN went down for a few hours; that perhaps
>there was something on the network end that can be done to rectify it.
>This has never been a problem before, so something must have happened to
>cause it. The database hasn't changed very much in years.

>
It is surprising how often only one record is needed for a particular
business function, if it exists, or none, if it does not. Limiting the
number of records retrieved by Query Criteria is a very good way to speed
up client-server performance, and you'll never "lose" several hundred
records on a one-record retrieval. A client-server application which
retrieves hundreds or thousands of records, or more, and then "finds" the
one of interest is not very efficient and effective.
>
That said, the "fix" is to correct the LAN/WAN problems. I once observed a
company who got a really noticeable improvement when they replaced about
95% of their network support staff. {:-) or :-(, depending on whether you
were part of the new or the old staff} That said, because a LAN is
totally within control of the network support team, it's much easier to
fix than a WAN, which relies on external providers for some of its
services.
>
Larry Linson
Microsoft Access MVP
>
>

|||Neil wrote:

Quote:

Originally Posted by

Getting back to the network situation, the problem only seems to manifest
itself in the WAN. The LAN users aren't experiencing this problem. And, as
noted, it only started about a week ago (after many years of the database
and WAN being up and running without this problem) and on the same day that
the WAN went down for several hours. So this tells me that SOMETHING
happened on that day that hasn't yet been rectified. But I know what the
network guy's going to say: everything's working fine now; he doesn't see
any problem with anything. And round and round we go.....

Is he denying that the WAN outage is responsible for the problem, or is
he just claiming that the WAN outage was an isolated incident and he
doesn't know what caused it and thus he doesn't know how to prevent
future occurrences?

In the former case, you could test the issue by deliberately cutting a
workstation's WAN connection and seeing whether the problem recurs.|||As for what he believes, here's what he wrote:

"It sounds as if it is using cached information and not updating from the
database directly. We are having intermittent issues with the T1s,
but that has been going on for the last few months. Last week the T1s went
down for over two hours this issue was brought to my attention
right after that incident so I am not sure if it is related.

"When I get in this afternoon I'll start sniffing around on the network and
see if there is anything going on at the Network layer.
Later tonight I will reset both the routers and firewalls on both end as
well to see if that helps. I will let them run over the weekend to see what
kind of data we can collect on errors, interface resets etc."

Note that he says the issue was brought to his attention right after the T1s
went down; yet he still isn't sure if the two are related!

Re. testing for the problem, it's hard to do because the problem is very
intermittent. Also, the WAN computers get their data from the remote
location.

Thanks,

Neil

"Ed Murphy" <emurphy42@.socal.rr.comwrote in message
news:45afd262$0$5750$4c368faf@.roadrunner.com...

Quote:

Originally Posted by

Neil wrote:
>

Quote:

Originally Posted by

>Getting back to the network situation, the problem only seems to manifest
>itself in the WAN. The LAN users aren't experiencing this problem. And,
>as noted, it only started about a week ago (after many years of the
>database and WAN being up and running without this problem) and on the
>same day that the WAN went down for several hours. So this tells me that
>SOMETHING happened on that day that hasn't yet been rectified. But I know
>what the network guy's going to say: everything's working fine now; he
>doesn't see any problem with anything. And round and round we go.....

>
Is he denying that the WAN outage is responsible for the problem, or is
he just claiming that the WAN outage was an isolated incident and he
doesn't know what caused it and thus he doesn't know how to prevent
future occurrences?
>
In the former case, you could test the issue by deliberately cutting a
workstation's WAN connection and seeing whether the problem recurs.

Disappearing Records

I am running an Access 2000 MDB against a SQL 7 back end, using ODBC linked
tables over a LAN and a WAN. The system has been operational for years with
relatively few problems.
Recently, WAN users have been reporting several hundred records disappearing
at a time. The records are all sequential, and they're in a table that
contains about 60,000 records. This all started last week at about the same
time (not sure if before or after) that the WAN went down for a couple of
hours for an unknown reason. Since then, every once in a while, a WAN user
will report that several hundred records in a block are just "missing."
Then, an hour or two later, they reappear.
Any ideas as to what's going on or what can be done to rectify this?
Thanks!
Neilhi Neil,
Neil wrote:
> Any ideas as to what's going on or what can be done to rectify this?
Bad WLAN performance, but the network stack is not aware of it. So your
session thinks it is okay, but it's not.
mfG
--> stefan <--|||Any ideas on what can be done to rectify it?
"Stefan Hoffmann" <stefan.hoffmann@.explido.de> wrote in message
news:OFe9nMmOHHA.2232@.TK2MSFTNGP02.phx.gbl...
> hi Neil,
> Neil wrote:
>> Any ideas as to what's going on or what can be done to rectify this?
> Bad WLAN performance, but the network stack is not aware of it. So your
> session thinks it is okay, but it's not.
>
> mfG
> --> stefan <--|||hi Neil,
Neil wrote:
> Any ideas on what can be done to rectify it?
Try using a permanent open recordset.
Use filterted recordsets.
Use serverside filters (views).
mfG
--> stefan <--|||OK, thanks. I guess I was thinking that, since this system has been in place
for years without these problems; and since these problems just started last
week when the WAN went down for a few hours; that perhaps there was
something on the network end that can be done to rectify it. This has never
been a problem before, so something must have happened to cause it. The
database hasn't changed very much in years.
Thanks,
Neil
"Stefan Hoffmann" <stefan.hoffmann@.explido.de> wrote in message
news:e3lZ9rmOHHA.3872@.TK2MSFTNGP06.phx.gbl...
> hi Neil,
> Neil wrote:
>> Any ideas on what can be done to rectify it?
> Try using a permanent open recordset.
> Use filterted recordsets.
> Use serverside filters (views).
>
> mfG
> --> stefan <--|||"Neil" <nospam@.nospam.net> wrote in message
news:XNvrh.12389$yx6.3307@.newsread2.news.pas.earthlink.net...
> OK, thanks. I guess I was thinking that, since this system has been in
> place for years without these problems; and since these problems just
> started last week when the WAN went down for a few hours; that perhaps
> there was something on the network end that can be done to rectify it.
> This has never been a problem before, so something must have happened to
> cause it. The database hasn't changed very much in years.
It is surprising how often only one record is needed for a particular
business function, if it exists, or none, if it does not. Limiting the
number of records retrieved by Query Criteria is a very good way to speed up
client-server performance, and you'll never "lose" several hundred records
on a one-record retrieval. A client-server application which retrieves
hundreds or thousands of records, or more, and then "finds" the one of
interest is not very efficient and effective.
That said, the "fix" is to correct the LAN/WAN problems. I once observed a
company who got a really noticeable improvement when they replaced about 95%
of their network support staff. {:-) or :-(, depending on whether you were
part of the new or the old staff} That said, because a LAN is totally
within control of the network support team, it's much easier to fix than a
WAN, which relies on external providers for some of its services.
Larry Linson
Microsoft Access MVP|||Larry,
I agree with what you wrote 100%. The application was inherited by me after
it was converted to an Access back end from an off-the-shelf product.
There's much that needs to be improved with it. One of the major changes
that we are looking to make is to change the way it works with records in
the way you describe. Bringing over tens of thousands of records is
ridiculous. I was considering giving the user options to work with small,
pre-defined sets of records based on business needs, with the additional
option of a custom set based on search criteria (with the sets being
compiled in the back end, of course, and then brought over). But your note
has made me rethink this. In what situations would the user need anything
*but* a custom set? So I'm rethinking that paradigm, and may just have the
user pull over whatever set of records they need. So thanks for that.
Getting back to the network situation, the problem only seems to manifest
itself in the WAN. The LAN users aren't experiencing this problem. And, as
noted, it only started about a week ago (after many years of the database
and WAN being up and running without this problem) and on the same day that
the WAN went down for several hours. So this tells me that SOMETHING
happened on that day that hasn't yet been rectified. But I know what the
network guy's going to say: everything's working fine now; he doesn't see
any problem with anything. And round and round we go.....
Thanks,
Neil
"Larry Linson" <bouncer@.localhost.not> wrote in message
news:iMCrh.3792$E35.2163@.trnddc02...
> "Neil" <nospam@.nospam.net> wrote in message
> news:XNvrh.12389$yx6.3307@.newsread2.news.pas.earthlink.net...
>> OK, thanks. I guess I was thinking that, since this system has been in
>> place for years without these problems; and since these problems just
>> started last week when the WAN went down for a few hours; that perhaps
>> there was something on the network end that can be done to rectify it.
>> This has never been a problem before, so something must have happened to
>> cause it. The database hasn't changed very much in years.
> It is surprising how often only one record is needed for a particular
> business function, if it exists, or none, if it does not. Limiting the
> number of records retrieved by Query Criteria is a very good way to speed
> up client-server performance, and you'll never "lose" several hundred
> records on a one-record retrieval. A client-server application which
> retrieves hundreds or thousands of records, or more, and then "finds" the
> one of interest is not very efficient and effective.
> That said, the "fix" is to correct the LAN/WAN problems. I once observed a
> company who got a really noticeable improvement when they replaced about
> 95% of their network support staff. {:-) or :-(, depending on whether you
> were part of the new or the old staff} That said, because a LAN is
> totally within control of the network support team, it's much easier to
> fix than a WAN, which relies on external providers for some of its
> services.
> Larry Linson
> Microsoft Access MVP
>|||Neil wrote:
> Getting back to the network situation, the problem only seems to manifest
> itself in the WAN. The LAN users aren't experiencing this problem. And, as
> noted, it only started about a week ago (after many years of the database
> and WAN being up and running without this problem) and on the same day that
> the WAN went down for several hours. So this tells me that SOMETHING
> happened on that day that hasn't yet been rectified. But I know what the
> network guy's going to say: everything's working fine now; he doesn't see
> any problem with anything. And round and round we go.....
Is he denying that the WAN outage is responsible for the problem, or is
he just claiming that the WAN outage was an isolated incident and he
doesn't know what caused it and thus he doesn't know how to prevent
future occurrences?
In the former case, you could test the issue by deliberately cutting a
workstation's WAN connection and seeing whether the problem recurs.|||As for what he believes, here's what he wrote:
"It sounds as if it is using cached information and not updating from the
database directly. We are having intermittent issues with the T1s,
but that has been going on for the last few months. Last week the T1s went
down for over two hours this issue was brought to my attention
right after that incident so I am not sure if it is related.
"When I get in this afternoon I'll start sniffing around on the network and
see if there is anything going on at the Network layer.
Later tonight I will reset both the routers and firewalls on both end as
well to see if that helps. I will let them run over the weekend to see what
kind of data we can collect on errors, interface resets etc."
Note that he says the issue was brought to his attention right after the T1s
went down; yet he still isn't sure if the two are related!
Re. testing for the problem, it's hard to do because the problem is very
intermittent. Also, the WAN computers get their data from the remote
location.
Thanks,
Neil
"Ed Murphy" <emurphy42@.socal.rr.com> wrote in message
news:45afd262$0$5750$4c368faf@.roadrunner.com...
> Neil wrote:
>> Getting back to the network situation, the problem only seems to manifest
>> itself in the WAN. The LAN users aren't experiencing this problem. And,
>> as noted, it only started about a week ago (after many years of the
>> database and WAN being up and running without this problem) and on the
>> same day that the WAN went down for several hours. So this tells me that
>> SOMETHING happened on that day that hasn't yet been rectified. But I know
>> what the network guy's going to say: everything's working fine now; he
>> doesn't see any problem with anything. And round and round we go.....
> Is he denying that the WAN outage is responsible for the problem, or is
> he just claiming that the WAN outage was an isolated incident and he
> doesn't know what caused it and thus he doesn't know how to prevent
> future occurrences?
> In the former case, you could test the issue by deliberately cutting a
> workstation's WAN connection and seeing whether the problem recurs.

Disappearing Records

I am running an Access 2000 MDB against a SQL 7 back end, using ODBC linked
tables over a LAN and a WAN. The system has been operational for years with
relatively few problems.
Recently, WAN users have been reporting several hundred records disappearing
at a time. The records are all sequential, and they're in a table that
contains about 60,000 records. This all started last week at about the same
time (not sure if before or after) that the WAN went down for a couple of
hours for an unknown reason. Since then, every once in a while, a WAN user
will report that several hundred records in a block are just "missing."
Then, an hour or two later, they reappear.
Any ideas as to what's going on or what can be done to rectify this?
Thanks!
Neil
Any ideas on what can be done to rectify it?
"Stefan Hoffmann" <stefan.hoffmann@.explido.de> wrote in message
news:OFe9nMmOHHA.2232@.TK2MSFTNGP02.phx.gbl...
> hi Neil,
> Neil wrote:
> Bad WLAN performance, but the network stack is not aware of it. So your
> session thinks it is okay, but it's not.
>
> mfG
> --> stefan <--
|||OK, thanks. I guess I was thinking that, since this system has been in place
for years without these problems; and since these problems just started last
week when the WAN went down for a few hours; that perhaps there was
something on the network end that can be done to rectify it. This has never
been a problem before, so something must have happened to cause it. The
database hasn't changed very much in years.
Thanks,
Neil
"Stefan Hoffmann" <stefan.hoffmann@.explido.de> wrote in message
news:e3lZ9rmOHHA.3872@.TK2MSFTNGP06.phx.gbl...
> hi Neil,
> Neil wrote:
> Try using a permanent open recordset.
> Use filterted recordsets.
> Use serverside filters (views).
>
> mfG
> --> stefan <--
|||"Neil" <nospam@.nospam.net> wrote in message
news:XNvrh.12389$yx6.3307@.newsread2.news.pas.earth link.net...
> OK, thanks. I guess I was thinking that, since this system has been in
> place for years without these problems; and since these problems just
> started last week when the WAN went down for a few hours; that perhaps
> there was something on the network end that can be done to rectify it.
> This has never been a problem before, so something must have happened to
> cause it. The database hasn't changed very much in years.
It is surprising how often only one record is needed for a particular
business function, if it exists, or none, if it does not. Limiting the
number of records retrieved by Query Criteria is a very good way to speed up
client-server performance, and you'll never "lose" several hundred records
on a one-record retrieval. A client-server application which retrieves
hundreds or thousands of records, or more, and then "finds" the one of
interest is not very efficient and effective.
That said, the "fix" is to correct the LAN/WAN problems. I once observed a
company who got a really noticeable improvement when they replaced about 95%
of their network support staff. {:-) or :-(, depending on whether you were
part of the new or the old staff} That said, because a LAN is totally
within control of the network support team, it's much easier to fix than a
WAN, which relies on external providers for some of its services.
Larry Linson
Microsoft Access MVP
|||Larry,
I agree with what you wrote 100%. The application was inherited by me after
it was converted to an Access back end from an off-the-shelf product.
There's much that needs to be improved with it. One of the major changes
that we are looking to make is to change the way it works with records in
the way you describe. Bringing over tens of thousands of records is
ridiculous. I was considering giving the user options to work with small,
pre-defined sets of records based on business needs, with the additional
option of a custom set based on search criteria (with the sets being
compiled in the back end, of course, and then brought over). But your note
has made me rethink this. In what situations would the user need anything
*but* a custom set? So I'm rethinking that paradigm, and may just have the
user pull over whatever set of records they need. So thanks for that.
Getting back to the network situation, the problem only seems to manifest
itself in the WAN. The LAN users aren't experiencing this problem. And, as
noted, it only started about a week ago (after many years of the database
and WAN being up and running without this problem) and on the same day that
the WAN went down for several hours. So this tells me that SOMETHING
happened on that day that hasn't yet been rectified. But I know what the
network guy's going to say: everything's working fine now; he doesn't see
any problem with anything. And round and round we go.....
Thanks,
Neil
"Larry Linson" <bouncer@.localhost.not> wrote in message
news:iMCrh.3792$E35.2163@.trnddc02...
> "Neil" <nospam@.nospam.net> wrote in message
> news:XNvrh.12389$yx6.3307@.newsread2.news.pas.earth link.net...
> It is surprising how often only one record is needed for a particular
> business function, if it exists, or none, if it does not. Limiting the
> number of records retrieved by Query Criteria is a very good way to speed
> up client-server performance, and you'll never "lose" several hundred
> records on a one-record retrieval. A client-server application which
> retrieves hundreds or thousands of records, or more, and then "finds" the
> one of interest is not very efficient and effective.
> That said, the "fix" is to correct the LAN/WAN problems. I once observed a
> company who got a really noticeable improvement when they replaced about
> 95% of their network support staff. {:-) or :-(, depending on whether you
> were part of the new or the old staff} That said, because a LAN is
> totally within control of the network support team, it's much easier to
> fix than a WAN, which relies on external providers for some of its
> services.
> Larry Linson
> Microsoft Access MVP
>
|||Neil wrote:

> Getting back to the network situation, the problem only seems to manifest
> itself in the WAN. The LAN users aren't experiencing this problem. And, as
> noted, it only started about a week ago (after many years of the database
> and WAN being up and running without this problem) and on the same day that
> the WAN went down for several hours. So this tells me that SOMETHING
> happened on that day that hasn't yet been rectified. But I know what the
> network guy's going to say: everything's working fine now; he doesn't see
> any problem with anything. And round and round we go.....
Is he denying that the WAN outage is responsible for the problem, or is
he just claiming that the WAN outage was an isolated incident and he
doesn't know what caused it and thus he doesn't know how to prevent
future occurrences?
In the former case, you could test the issue by deliberately cutting a
workstation's WAN connection and seeing whether the problem recurs.
|||As for what he believes, here's what he wrote:
"It sounds as if it is using cached information and not updating from the
database directly. We are having intermittent issues with the T1s,
but that has been going on for the last few months. Last week the T1s went
down for over two hours this issue was brought to my attention
right after that incident so I am not sure if it is related.
"When I get in this afternoon I'll start sniffing around on the network and
see if there is anything going on at the Network layer.
Later tonight I will reset both the routers and firewalls on both end as
well to see if that helps. I will let them run over the weekend to see what
kind of data we can collect on errors, interface resets etc."
Note that he says the issue was brought to his attention right after the T1s
went down; yet he still isn't sure if the two are related!
Re. testing for the problem, it's hard to do because the problem is very
intermittent. Also, the WAN computers get their data from the remote
location.
Thanks,
Neil
"Ed Murphy" <emurphy42@.socal.rr.com> wrote in message
news:45afd262$0$5750$4c368faf@.roadrunner.com...
> Neil wrote:
>
> Is he denying that the WAN outage is responsible for the problem, or is
> he just claiming that the WAN outage was an isolated incident and he
> doesn't know what caused it and thus he doesn't know how to prevent
> future occurrences?
> In the former case, you could test the issue by deliberately cutting a
> workstation's WAN connection and seeing whether the problem recurs.

Disappearing Records

I am running an Access 2000 MDB against a SQL 7 back end, using ODBC linked
tables over a LAN and a WAN. The system has been operational for years with
relatively few problems.
Recently, WAN users have been reporting several hundred records disappearing
at a time. The records are all sequential, and they're in a table that
contains about 60,000 records. This all started last week at about the same
time (not sure if before or after) that the WAN went down for a couple of
hours for an unknown reason. Since then, every once in a while, a WAN user
will report that several hundred records in a block are just "missing."
Then, an hour or two later, they reappear.
Any ideas as to what's going on or what can be done to rectify this?
Thanks!
Neilhi Neil,
Neil wrote:
> Any ideas as to what's going on or what can be done to rectify this?
Bad WLAN performance, but the network stack is not aware of it. So your
session thinks it is okay, but it's not.
mfG
--> stefan <--|||Any ideas on what can be done to rectify it?
"Stefan Hoffmann" <stefan.hoffmann@.explido.de> wrote in message
news:OFe9nMmOHHA.2232@.TK2MSFTNGP02.phx.gbl...
> hi Neil,
> Neil wrote:
> Bad WLAN performance, but the network stack is not aware of it. So your
> session thinks it is okay, but it's not.
>
> mfG
> --> stefan <--|||hi Neil,
Neil wrote:
> Any ideas on what can be done to rectify it?
Try using a permanent open recordset.
Use filterted recordsets.
Use serverside filters (views).
mfG
--> stefan <--|||OK, thanks. I guess I was thinking that, since this system has been in place
for years without these problems; and since these problems just started last
week when the WAN went down for a few hours; that perhaps there was
something on the network end that can be done to rectify it. This has never
been a problem before, so something must have happened to cause it. The
database hasn't changed very much in years.
Thanks,
Neil
"Stefan Hoffmann" <stefan.hoffmann@.explido.de> wrote in message
news:e3lZ9rmOHHA.3872@.TK2MSFTNGP06.phx.gbl...
> hi Neil,
> Neil wrote:
> Try using a permanent open recordset.
> Use filterted recordsets.
> Use serverside filters (views).
>
> mfG
> --> stefan <--|||"Neil" <nospam@.nospam.net> wrote in message
news:XNvrh.12389$yx6.3307@.newsread2.news.pas.earthlink.net...
> OK, thanks. I guess I was thinking that, since this system has been in
> place for years without these problems; and since these problems just
> started last week when the WAN went down for a few hours; that perhaps
> there was something on the network end that can be done to rectify it.
> This has never been a problem before, so something must have happened to
> cause it. The database hasn't changed very much in years.
It is surprising how often only one record is needed for a particular
business function, if it exists, or none, if it does not. Limiting the
number of records retrieved by Query Criteria is a very good way to speed up
client-server performance, and you'll never "lose" several hundred records
on a one-record retrieval. A client-server application which retrieves
hundreds or thousands of records, or more, and then "finds" the one of
interest is not very efficient and effective.
That said, the "fix" is to correct the LAN/WAN problems. I once observed a
company who got a really noticeable improvement when they replaced about 95%
of their network support staff. {:-) or :-(, depending on whether you w
ere
part of the new or the old staff} That said, because a LAN is totally
within control of the network support team, it's much easier to fix than a
WAN, which relies on external providers for some of its services.
Larry Linson
Microsoft Access MVP|||Larry,
I agree with what you wrote 100%. The application was inherited by me after
it was converted to an Access back end from an off-the-shelf product.
There's much that needs to be improved with it. One of the major changes
that we are looking to make is to change the way it works with records in
the way you describe. Bringing over tens of thousands of records is
ridiculous. I was considering giving the user options to work with small,
pre-defined sets of records based on business needs, with the additional
option of a custom set based on search criteria (with the sets being
compiled in the back end, of course, and then brought over). But your note
has made me rethink this. In what situations would the user need anything
*but* a custom set? So I'm rethinking that paradigm, and may just have the
user pull over whatever set of records they need. So thanks for that.
Getting back to the network situation, the problem only seems to manifest
itself in the WAN. The LAN users aren't experiencing this problem. And, as
noted, it only started about a week ago (after many years of the database
and WAN being up and running without this problem) and on the same day that
the WAN went down for several hours. So this tells me that SOMETHING
happened on that day that hasn't yet been rectified. But I know what the
network guy's going to say: everything's working fine now; he doesn't see
any problem with anything. And round and round we go.....
Thanks,
Neil
"Larry Linson" <bouncer@.localhost.not> wrote in message
news:iMCrh.3792$E35.2163@.trnddc02...
> "Neil" <nospam@.nospam.net> wrote in message
> news:XNvrh.12389$yx6.3307@.newsread2.news.pas.earthlink.net...
> It is surprising how often only one record is needed for a particular
> business function, if it exists, or none, if it does not. Limiting the
> number of records retrieved by Query Criteria is a very good way to speed
> up client-server performance, and you'll never "lose" several hundred
> records on a one-record retrieval. A client-server application which
> retrieves hundreds or thousands of records, or more, and then "finds" the
> one of interest is not very efficient and effective.
> That said, the "fix" is to correct the LAN/WAN problems. I once observed a
> company who got a really noticeable improvement when they replaced about
> 95% of their network support staff. {:-) or :-(, depending on whether
you
> were part of the new or the old staff} That said, because a LAN is
> totally within control of the network support team, it's much easier to
> fix than a WAN, which relies on external providers for some of its
> services.
> Larry Linson
> Microsoft Access MVP
>|||Neil wrote:

> Getting back to the network situation, the problem only seems to manifest
> itself in the WAN. The LAN users aren't experiencing this problem. And, as
> noted, it only started about a week ago (after many years of the database
> and WAN being up and running without this problem) and on the same day tha
t
> the WAN went down for several hours. So this tells me that SOMETHING
> happened on that day that hasn't yet been rectified. But I know what the
> network guy's going to say: everything's working fine now; he doesn't see
> any problem with anything. And round and round we go.....
Is he denying that the WAN outage is responsible for the problem, or is
he just claiming that the WAN outage was an isolated incident and he
doesn't know what caused it and thus he doesn't know how to prevent
future occurrences?
In the former case, you could test the issue by deliberately cutting a
workstation's WAN connection and seeing whether the problem recurs.|||As for what he believes, here's what he wrote:
"It sounds as if it is using cached information and not updating from the
database directly. We are having intermittent issues with the T1s,
but that has been going on for the last few months. Last week the T1s went
down for over two hours this issue was brought to my attention
right after that incident so I am not sure if it is related.
"When I get in this afternoon I'll start sniffing around on the network and
see if there is anything going on at the Network layer.
Later tonight I will reset both the routers and firewalls on both end as
well to see if that helps. I will let them run over the weekend to see what
kind of data we can collect on errors, interface resets etc."
Note that he says the issue was brought to his attention right after the T1s
went down; yet he still isn't sure if the two are related!
Re. testing for the problem, it's hard to do because the problem is very
intermittent. Also, the WAN computers get their data from the remote
location.
Thanks,
Neil
"Ed Murphy" <emurphy42@.socal.rr.com> wrote in message
news:45afd262$0$5750$4c368faf@.roadrunner
.com...
> Neil wrote:
>
> Is he denying that the WAN outage is responsible for the problem, or is
> he just claiming that the WAN outage was an isolated incident and he
> doesn't know what caused it and thus he doesn't know how to prevent
> future occurrences?
> In the former case, you could test the issue by deliberately cutting a
> workstation's WAN connection and seeing whether the problem recurs.

Disappearing Data -- SQL 2000

I'm using an Access front end to a SQL 2000 database.

I have a form from which I enter data. Everything seems fine, I check the
tables and my records are being inserted.

When I close Access and reopen it, data from three of my tables is gone.

SQL Profiler turns up nothing. If I try to access a problem table from
Enterprise manager while Access is open I get an ODBC error. If I try to
access it after I close Access, the records are gone.

Where do I start looking to figure out where the problem is? I'm at a lost.

TIA for help.

--Jake"Jake Jessup" <watcherdude@.hotmail.com> wrote in message
news:oBc1d.14931$QJ3.11293@.newssvr21.news.prodigy. com...
> I'm using an Access front end to a SQL 2000 database.
> I have a form from which I enter data. Everything seems fine, I check the
> tables and my records are being inserted.
> When I close Access and reopen it, data from three of my tables is gone.
> SQL Profiler turns up nothing. If I try to access a problem table from
> Enterprise manager while Access is open I get an ODBC error. If I try to
> access it after I close Access, the records are gone.
> Where do I start looking to figure out where the problem is? I'm at a
> lost.
> TIA for help.
> --Jake

It sounds as if Access is holding locks on the tables, which are stopping
you seeing the data from another connections - perhaps it isn't committing a
transaction when it should? You can use DBCC OPENTRAN to check, although
this is just a guess. Or conversely, perhaps it's doing the INSERTs
correctly, but then rolling back?

In any case, rows do not suddenly vanish, so you might want to look for
triggers or cascading DRI actions which have deleted the rows without an
explicit DELETE statement on the tables themselves. If you get really stuck,
you could put a DELETE trigger on the table yourself, to record the SPID and
any other information which might help you identify the problem process,
although Profiler would normally be the best tool for this.

Simon|||Jake Jessup (watcherdude@.hotmail.com) writes:
> I'm using an Access front end to a SQL 2000 database.
> I have a form from which I enter data. Everything seems fine, I check the
> tables and my records are being inserted.
> When I close Access and reopen it, data from three of my tables is gone.
> SQL Profiler turns up nothing. If I try to access a problem table from
> Enterprise manager while Access is open I get an ODBC error. If I try to
> access it after I close Access, the records are gone.

It sounds like you start a transaction which you never commit, so when
you close Access, the whole transaction is rolled back, and the data
you entered goes poof.

I assume that that ODBC error in EM says "Lock timeout expireed" or
somesuch?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote >
> I assume that that ODBC error in EM says "Lock timeout expireed" or
> somesuch?

Yes, that's exactly it.

I'll look for the transaction processing and make sure that the transaction
is getting committed.

Thanks for the tip.|||That did it Erland, I appreciate your help. I guess I just went brain dead.

Most of my work has been with regular Access and I forgot about commiting
transactions.

--Jake

disappearing data

I am not sure where to put this but correct me if i'm in the wrong forum.

I've been working on this database for some time now. It's an Access 2003 database with a SQL backend. Everything was going well until one of the users noticed her data was disappearing after she send an email. The database has a memo field that gets pasted in the body of an email when it is created. Sometimes, when the user sends an email and goes back into the record, the memo field is blank. I know the field has data in it at the time of the email because it will give you an error if the field is null and you won't be able to send it. And, it goes into a history table with everything the user does with each record. I've checked this history table and it shows that all records in question, an email was sent. I have no idea why the data would disappear in this field. It does not happen all the time. I've performed the same steps and I can't duplicate the problem. There's no code associated with the memo field that deletes it after sending the email. Can anyone think of anything that would cause this?

I guess the user is doing STRG-X instead of STRG-C ?! Although I also had the feeling that Access sometimes eats up the data in the past, there was always a possible explanation why the data was lost.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I don't know what that is?|||Hi,

you mentioned that the data from the record is pasted into an email, this sounds like manual work for me, so i suggested that they do that on their own and not doing it automatically with an application. Therefore I assumed that they might not copied the data (STRG-C) from the memo field as they cut the data (STRG-X) from the memo field to paste it (STRG-V) within the email. Did I understand you right, or what is the actual problem ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Disallow administrator access

Can anyone tell me how I can stop a domain administrator from accessing
SQL Server 2000 on Windows 2003 Server.
ToddRemove the builtin\Administrators from the Security - login in SQL server.
Regards
Jonas
"Todd Hazer" <NO_SPAMthazer@.gmail.com> wrote in message
news:%231Tl8k6uEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Can anyone tell me how I can stop a domain administrator from accessing
> SQL Server 2000 on Windows 2003 Server.
> Todd|||Will setting the builtin\Administrators security access to 'Deny' work?
thanks for the reply.
Todd
Jonas Larsen wrote:
> Remove the builtin\Administrators from the Security - login in SQL server.
> Regards
> Jonas
> "Todd Hazer" <NO_SPAMthazer@.gmail.com> wrote in message
> news:%231Tl8k6uEHA.2876@.TK2MSFTNGP12.phx.gbl...
>
>
>|||I have added my domain user account to the security logins and set
myself up as a system administrator. My domain user account is in the
domain Administrators group and in the Domain Admins group.
I just set the SQL Server builtin\Administrators security access setting
to "Deny" and it stopped me from connecting as well. How can I disallow
one administrator without denying myself?
Todd
Jonas Larsen wrote:
> Remove the builtin\Administrators from the Security - login in SQL server.
> Regards
> Jonas
> "Todd Hazer" <NO_SPAMthazer@.gmail.com> wrote in message
> news:%231Tl8k6uEHA.2876@.TK2MSFTNGP12.phx.gbl...
>
>
>|||Remove the builtin\Administrators from the SQL Server instead of deny. And
the add you own domain account as system admin in SQL.
Jonas
"Todd Hazer" <NO_SPAMthazer@.gmail.com> wrote in message
news:%23JM86O7uEHA.3276@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> I have added my domain user account to the security logins and set
> myself up as a system administrator. My domain user account is in the
> domain Administrators group and in the Domain Admins group.
> I just set the SQL Server builtin\Administrators security access setting
> to "Deny" and it stopped me from connecting as well. How can I disallow
> one administrator without denying myself?
> Todd
> Jonas Larsen wrote:
server.[vbcol=seagreen]|||You can remove the builtin\administrators group from SQL
Server but that can sometimes introduce other problems.
Whether you get problems or not depends. The following
article has more information on removing the
builtin\administrators group as well as a section with links
to some issues that could come up:
INF: How to impede Windows NT administrators from
administering a clustered instance of SQL Server
http://support.microsoft.com/?id=263712
-Sue
On Tue, 26 Oct 2004 15:18:08 -0700, Todd Hazer
<NO_SPAMthazer@.gmail.com> wrote:

>Can anyone tell me how I can stop a domain administrator from accessing
>SQL Server 2000 on Windows 2003 Server.
>Todd

disabling the export option

Hi,

As we can disable the toolbar by url access like '&rc:toolbar=false', I would like to know whether there is any way to disable the export control.

The reason for this is, I dont want to show the exporting option in my reports and I want to show the print and other options in the toolbar.

No, that's not possible, using the "rc" parameter.

Ayzan

|||Is there any other way to do this , I'm opening the report through HTML hyperlink.|||

No,

the export functionality can only be disabled within the config file, disabling the rendering extensions. But this can only be done per se, not per user, per permission set etc, only per Reporting Server Instance.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Disabling Named Pipes

In the process of doing some routine monitoring/clean-up we've
discovered that several (many?) users are apparently set to access our
SQL Server 2000 database instances via the Named Pipes protocol. In
readings and recommendations we've decided that our WAN would be best
served if we use the less "chatty" TCP/IP.

As such we've also decided to try to enforce this decision to use
TCP/IP exclusively using the domain login script used by all of our
end-users.

Question: does anyone know what registry entries are created/used to
indicate that TCP/IP is enabled and is the default protocol for SQL
Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
(typically SP3).

TIA
Glenn - newbie DBA(glenn.owens@.gmail.com) writes:
> In the process of doing some routine monitoring/clean-up we've
> discovered that several (many?) users are apparently set to access our
> SQL Server 2000 database instances via the Named Pipes protocol. In
> readings and recommendations we've decided that our WAN would be best
> served if we use the less "chatty" TCP/IP.
> As such we've also decided to try to enforce this decision to use
> TCP/IP exclusively using the domain login script used by all of our
> end-users.
> Question: does anyone know what registry entries are created/used to
> indicate that TCP/IP is enabled and is the default protocol for SQL
> Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
> (typically SP3).

The brutal variant would be to open the Server Network Utility on
the server box, and disable Named Pipes (server restart needed, as I
recall.)

On the clients it could be a good thing to use the Client Network
Utility to disable Named Pipes, or put it lower in priority than
TCP/IP.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, I appreciate the input but we're not looking at wiping out the
use of named pipes all-together (there may be background processes and
legacy applications which use/require the protocol). We're hoping only
to disable Named Pipes for End-Users who login via the default domain.
So, the question still stands...

Question: does anyone know what registry entries are created/used to
indicate that TCP/IP is enabled and is the default protocol for SQL
Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
(typically SP3).

Erland Sommarskog wrote:
> (glenn.owens@.gmail.com) writes:
> > In the process of doing some routine monitoring/clean-up we've
> > discovered that several (many?) users are apparently set to access
our
> > SQL Server 2000 database instances via the Named Pipes protocol. In
> > readings and recommendations we've decided that our WAN would be
best
> > served if we use the less "chatty" TCP/IP.
> > As such we've also decided to try to enforce this decision to use
> > TCP/IP exclusively using the domain login script used by all of our
> > end-users.
> > Question: does anyone know what registry entries are created/used
to
> > indicate that TCP/IP is enabled and is the default protocol for SQL
> > Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
> > (typically SP3).
> The brutal variant would be to open the Server Network Utility on
> the server box, and disable Named Pipes (server restart needed, as I
> recall.)
> On the clients it could be a good thing to use the Client Network
> Utility to disable Named Pipes, or put it lower in priority than
> TCP/IP.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(gowens@.nixonpeabody.com) writes:
> Erland, I appreciate the input but we're not looking at wiping out the
> use of named pipes all-together (there may be background processes and
> legacy applications which use/require the protocol). We're hoping only
> to disable Named Pipes for End-Users who login via the default domain.
> So, the question still stands...
> Question: does anyone know what registry entries are created/used to
> indicate that TCP/IP is enabled and is the default protocol for SQL
> Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
> (typically SP3).

I'm still not sure what you are asking for. Your question make it
seem like you want to do this on the server level, but this appears
to be impossible, since you want to keep Named Pipes for some clients.

Thus, your only option is to change this at the clients. The supported
way to do this, is to use the Client Network Utility.

If you want to hack the registry yourself, it appeats that the relevant
values and keys are under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\SuperSocketNetLib
The value Protocol Order appears to be the priority order, and should
thus have "tcp" in the beginning. Under the subkey Tcp, there should
be a REG_DWORD value DefaulPort with the value of 0x00000599 (1433.)

I recommend you to play around from the Client Network Utility a little
more, before you start any remote registry hacking or whatever you are
planning to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, what I'm suggesting is that we do NOT do this at the server
level but rather on a per user basis using our standard login script.

We have several hundred end-users in geographically different
locations. Virtually none of the users have access to, or have
knowledge of, the SQL Client Utility. So, the idea is that when they
logon to their PCs a scriptlet will be run which will disable Named
Pipes and/or insure that TCP/IP is the default protocol.

I was hoping that someone could suggest/provide such a script or
specify the WIN XP registry key/values that define the protocol, etc.

I hope that this clears thing up.

Thanks

Erland Sommarskog wrote:
> (gowens@.nixonpeabody.com) writes:
> > Erland, I appreciate the input but we're not looking at wiping out
the
> > use of named pipes all-together (there may be background processes
and
> > legacy applications which use/require the protocol). We're hoping
only
> > to disable Named Pipes for End-Users who login via the default
domain.
> > So, the question still stands...
> > Question: does anyone know what registry entries are created/used
to
> > indicate that TCP/IP is enabled and is the default protocol for SQL
> > Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
> > (typically SP3).
> I'm still not sure what you are asking for. Your question make it
> seem like you want to do this on the server level, but this appears
> to be impossible, since you want to keep Named Pipes for some
clients.
> Thus, your only option is to change this at the clients. The
supported
> way to do this, is to use the Client Network Utility.
> If you want to hack the registry yourself, it appeats that the
relevant
> values and keys are under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\SuperSocketNetLib
> The value Protocol Order appears to be the priority order, and should
> thus have "tcp" in the beginning. Under the subkey Tcp, there should
> be a REG_DWORD value DefaulPort with the value of 0x00000599 (1433.)
> I recommend you to play around from the Client Network Utility a
little
> more, before you start any remote registry hacking or whatever you
are
> planning to do.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(gowens@.nixonpeabody.com) writes:
> I was hoping that someone could suggest/provide such a script or
> specify the WIN XP registry key/values that define the protocol, etc.

My post contained some hints in this direction, and my hope was that
you could work from this suggestion. If you want something ready of
the shelf, I'm afraid that I don't have any thing in store.

A quick search lead me to:
http://msdn.microsoft.com/library/d...dbnetlib_22.asp

There you find some more information about the registrty entries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Disabling guest account doesn't work as expected (please help!)

Hi all, I want to disable access of the SQL guest account except
master and tempdb. I tried it for one database now, but before I used
this script in query analyzer to check whether the guest account had
access whatsoever:
select has_dbaccess('DatabaseName') as Access,* from sysusers
and it gave me a 1 for the row corresponding to guest:
>1< 2 0 guest 0x00 0x00 2000-08-06 01:27:55.187[...]
now I tried to disable it for the database via:
USE <DatabaseName
GO
EXECUTE sp_revokedbaccess guest
and I got an appropriate message that the account was disabled.
When I queried the sysusers table again as I did in the first
statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
this possible? In addition to this problem I tried to disable it again
for the same database, but now I get "User 'guest' does not exist in
the current database." which is actually that what I expected after
disabling the account, but why does it still show up in sysusers with
access to exactly the database that I disabled its access to!'!?!
Any help greatly appreciated, thank you
TimGuest always exists in sysusers, it's just only enabled when
you add the user. If it's not enabled in the database, the
status is 0 in sysusers.
-Sue
On 6 Apr 2004 09:07:17 -0700, google@.supertimmy.com (Tim
Gorgs) wrote:

>Hi all, I want to disable access of the SQL guest account except
>master and tempdb. I tried it for one database now, but before I used
>this script in query analyzer to check whether the guest account had
>access whatsoever:
>select has_dbaccess('DatabaseName') as Access,* from sysusers
>and it gave me a 1 for the row corresponding to guest:
>now I tried to disable it for the database via:
>USE <DatabaseName
>GO
>EXECUTE sp_revokedbaccess guest
>and I got an appropriate message that the account was disabled.
>When I queried the sysusers table again as I did in the first
>statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
>this possible? In addition to this problem I tried to disable it again
>for the same database, but now I get "User 'guest' does not exist in
>the current database." which is actually that what I expected after
>disabling the account, but why does it still show up in sysusers with
>access to exactly the database that I disabled its access to!'!?!
>Any help greatly appreciated, thank you
>Tim|||> select has_dbaccess('DatabaseName') as Access,* from sysusers
Note that there is no correlation in this query between HAS_DBACCESS and the
sysusers table so HAS_DBACCESS will have the same value for each row
returned. If your login has access to the specified database, it will
return 1 for all rows.
The guest account will always be in the sysusers table but can
enabled/disabled using sp_grantdbaccess/sp_revokedbaccess (or
sp_adduser/sp_dropuser). The guest account is disabled by default in user
databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Gorgs" <google@.supertimmy.com> wrote in message
news:afa954a3.0404060807.509acc2a@.posting.google.com...
> Hi all, I want to disable access of the SQL guest account except
> master and tempdb. I tried it for one database now, but before I used
> this script in query analyzer to check whether the guest account had
> access whatsoever:
> select has_dbaccess('DatabaseName') as Access,* from sysusers
> and it gave me a 1 for the row corresponding to guest:
> now I tried to disable it for the database via:
> USE <DatabaseName
> GO
> EXECUTE sp_revokedbaccess guest
> and I got an appropriate message that the account was disabled.
> When I queried the sysusers table again as I did in the first
> statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
> this possible? In addition to this problem I tried to disable it again
> for the same database, but now I get "User 'guest' does not exist in
> the current database." which is actually that what I expected after
> disabling the account, but why does it still show up in sysusers with
> access to exactly the database that I disabled its access to!'!?!
> Any help greatly appreciated, thank you
> Tim|||> Guest always exists in sysusers, it's just only enabled when
> you add the user. If it's not enabled in the database, the
> status is 0 in sysusers.
> -Sue
>
Thank you very much, I thought it must be something easy like that,
but somehow the status column in sysusers isn't documented in the
online help ("only for internal purposes") and I couldn't figure out
how to check if the guest user was enabled or not.
Have a nice day
Tim

Disabling guest account doesn't work as expected (please help!)

Hi all, I want to disable access of the SQL guest account except
master and tempdb. I tried it for one database now, but before I used
this script in query analyzer to check whether the guest account had
access whatsoever:
select has_dbaccess('DatabaseName') as Access,* from sysusers
and it gave me a 1 for the row corresponding to guest:
>1<20guest0x000x002000-08-06 01:27:55.187[...]
now I tried to disable it for the database via:
USE <DatabaseName
GO
EXECUTE sp_revokedbaccess guest
and I got an appropriate message that the account was disabled.
When I queried the sysusers table again as I did in the first
statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
this possible? In addition to this problem I tried to disable it again
for the same database, but now I get "User 'guest' does not exist in
the current database." which is actually that what I expected after
disabling the account, but why does it still show up in sysusers with
access to exactly the database that I disabled its access to!?!?!
Any help greatly appreciated, thank you
Tim
> select has_dbaccess('DatabaseName') as Access,* from sysusers
Note that there is no correlation in this query between HAS_DBACCESS and the
sysusers table so HAS_DBACCESS will have the same value for each row
returned. If your login has access to the specified database, it will
return 1 for all rows.
The guest account will always be in the sysusers table but can
enabled/disabled using sp_grantdbaccess/sp_revokedbaccess (or
sp_adduser/sp_dropuser). The guest account is disabled by default in user
databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Gorgs" <google@.supertimmy.com> wrote in message
news:afa954a3.0404060807.509acc2a@.posting.google.c om...
> Hi all, I want to disable access of the SQL guest account except
> master and tempdb. I tried it for one database now, but before I used
> this script in query analyzer to check whether the guest account had
> access whatsoever:
> select has_dbaccess('DatabaseName') as Access,* from sysusers
> and it gave me a 1 for the row corresponding to guest:
> now I tried to disable it for the database via:
> USE <DatabaseName
> GO
> EXECUTE sp_revokedbaccess guest
> and I got an appropriate message that the account was disabled.
> When I queried the sysusers table again as I did in the first
> statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
> this possible? In addition to this problem I tried to disable it again
> for the same database, but now I get "User 'guest' does not exist in
> the current database." which is actually that what I expected after
> disabling the account, but why does it still show up in sysusers with
> access to exactly the database that I disabled its access to!?!?!
> Any help greatly appreciated, thank you
> Tim
|||> Guest always exists in sysusers, it's just only enabled when
> you add the user. If it's not enabled in the database, the
> status is 0 in sysusers.
> -Sue
>
Thank you very much, I thought it must be something easy like that,
but somehow the status column in sysusers isn't documented in the
online help ("only for internal purposes") and I couldn't figure out
how to check if the guest user was enabled or not.
Have a nice day
Tim

Disabling extended stored procedures for security

Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time? Or, is the security team being too cautious and we should just tell them to leave these intact?

There are some that are extremely useful (perhaps even necessary) for many routine maintenance tasks.

Perhaps your security department just needs to know that you have access to the stored procedures 'locked' down to only administrative users.

|||

The onyl procedure which is questionable to let it enabled it the xp_cmdshell procedure as it can execute a command shell on the server. But... if noone (or only adminstrators) are granted access to this procedure there should be no security impact for you at all. make just sure that noone create a extended stored procedure which is not shipped by MS.

Code Snippet

SELECT

xproc.name AS [Name],

SCHEMA_NAME(xproc.schema_id)AS [Schema],

CAST(

xproc.is_ms_shipped

ASbit)AS [IsSystemObject],

xproc.create_date AS [CreateDate]

FROM

sys.all_objectsAS xproc

WHERE

(xproc.type='X')and(CAST(

xproc.is_ms_shipped

ASbit)=0)

ORDERBY

[Schema] ASC,[Name] ASC

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens. The security dept wants us to drop xp_cmdshell, not just remove the rights. Our MSDE 2000 database stores app settings and is not involved in heavy transactions or replication, etc. So would dropping this cause any harm? I read in the Microsoft KB article that it could affect some other stored procedures that depend on it, but those SPs appear to support enterprise-level features.

|||

I agree with Jens. I would not remove xp_cmdshell since I find it very useful for automating maintenance tasks.

However, set the permissions so that only the sysadmin role can execute xp_cmdshell. (Unless, of course, the worst imaginable sin has been commited and the application is running under the 'sa' account.)

No one, no group, no other role, except the very small list of SQL Server administrators 'should' be in the sysadmin role -AND the sa account 'should' NOT be used at all, and it should be tightly locked down with a strong password.

Since the SQL Server administrators have access to the server, there is nothing that they could do using xp_cmdshell that they couldn't do at an OS level command prompt.

Completely removing xp_cmdshell is folly. Tightly limiting access through appropriate permissions is a 'best practice'.

But if you MUST remove it, there 'may' be little impact. Only time will tell. And you can always put it back if necessary.

Disabling extended stored procedures for security

Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time? Or, is the security team being too cautious and we should just tell them to leave these intact?

There are some that are extremely useful (perhaps even necessary) for many routine maintenance tasks.

Perhaps your security department just needs to know that you have access to the stored procedures 'locked' down to only administrative users.

|||

The onyl procedure which is questionable to let it enabled it the xp_cmdshell procedure as it can execute a command shell on the server. But... if noone (or only adminstrators) are granted access to this procedure there should be no security impact for you at all. make just sure that noone create a extended stored procedure which is not shipped by MS.

Code Snippet

SELECT

xproc.name AS [Name],

SCHEMA_NAME(xproc.schema_id) AS [Schema],

CAST(

xproc.is_ms_shipped

AS bit) AS [IsSystemObject],

xproc.create_date AS [CreateDate]

FROM

sys.all_objects AS xproc

WHERE

(xproc.type='X')and(CAST(

xproc.is_ms_shipped

AS bit)=0)

ORDER BY

[Schema] ASC,[Name] ASC

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens. The security dept wants us to drop xp_cmdshell, not just remove the rights. Our MSDE 2000 database stores app settings and is not involved in heavy transactions or replication, etc. So would dropping this cause any harm? I read in the Microsoft KB article that it could affect some other stored procedures that depend on it, but those SPs appear to support enterprise-level features.

|||

I agree with Jens. I would not remove xp_cmdshell since I find it very useful for automating maintenance tasks.

However, set the permissions so that only the sysadmin role can execute xp_cmdshell. (Unless, of course, the worst imaginable sin has been commited and the application is running under the 'sa' account.)

No one, no group, no other role, except the very small list of SQL Server administrators 'should' be in the sysadmin role -AND the sa account 'should' NOT be used at all, and it should be tightly locked down with a strong password.

Since the SQL Server administrators have access to the server, there is nothing that they could do using xp_cmdshell that they couldn't do at an OS level command prompt.

Completely removing xp_cmdshell is folly. Tightly limiting access through appropriate permissions is a 'best practice'.

But if you MUST remove it, there 'may' be little impact. Only time will tell. And you can always put it back if necessary.

disable all SA accounts remotly - access only for low user ?

Hi,
I need to access my web based SQL 2005 express edition database on 1433
using a user with minimal privileges that can only run a stored procedure. I
do this by connecting to IP / port.
Is there anyway I can disable SA using this same method but still allow SA
when I remote desktop to the machine and connect using Management Studio
Express.
I.e if I disable the account the SA account or other admin accounts I cant
login when I RDP into the machine.
The minimal privileges user can simply run a single stored procedure but
must do using 1433.
I just need to disable 1433 access for all other accounts and cant figure
out how to (I guess Management Studio Express connects using 1433 anyway so
im stuck).
If this is the case I guess I need to use SQLXML.
Thanks for any help
Scott
I'm not sure, but you could try playing with something like:
DENY CONNECT ON ENDPOINT::TCP TO "sa"
(Check BOL for exact syntax)
I'm not sure whether such permissions are actually checked for sysadmins (let us know after your
test), and of course, you'd have to make sure some other netlib is used when inside the private
network (like Shared Memory, which only work locally).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <www.sage-eshop.com> wrote in message news:O$T7b0J3GHA.4764@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I need to access my web based SQL 2005 express edition database on 1433 using a user with minimal
> privileges that can only run a stored procedure. I do this by connecting to IP / port.
> Is there anyway I can disable SA using this same method but still allow SA when I remote desktop
> to the machine and connect using Management Studio Express.
> I.e if I disable the account the SA account or other admin accounts I cant login when I RDP into
> the machine.
> The minimal privileges user can simply run a single stored procedure but must do using 1433.
> I just need to disable 1433 access for all other accounts and cant figure out how to (I guess
> Management Studio Express connects using 1433 anyway so im stuck).
> If this is the case I guess I need to use SQLXML.
> Thanks for any help
> Scott
>

disable all SA accounts remotly - access only for low user ?

Hi,
I need to access my web based SQL 2005 express edition database on 1433
using a user with minimal privileges that can only run a stored procedure. I
do this by connecting to IP / port.
Is there anyway I can disable SA using this same method but still allow SA
when I remote desktop to the machine and connect using Management Studio
Express.
I.e if I disable the account the SA account or other admin accounts I cant
login when I RDP into the machine.
The minimal privileges user can simply run a single stored procedure but
must do using 1433.
I just need to disable 1433 access for all other accounts and cant figure
out how to (I guess Management Studio Express connects using 1433 anyway so
im stuck).
If this is the case I guess I need to use SQLXML.
Thanks for any help
ScottI'm not sure, but you could try playing with something like:
DENY CONNECT ON ENDPOINT::TCP TO "sa"
(Check BOL for exact syntax)
I'm not sure whether such permissions are actually checked for sysadmins (le
t us know after your
test), and of course, you'd have to make sure some other netlib is used when
inside the private
network (like Shared Memory, which only work locally).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <www.sage-eshop.com> wrote in message news:O$T7b0J3GHA.4764@.TK2MSFTNGP05.phx.gbl...[
vbcol=seagreen]
> Hi,
> I need to access my web based SQL 2005 express edition database on 1433 us
ing a user with minimal
> privileges that can only run a stored procedure. I do this by connecting t
o IP / port.
> Is there anyway I can disable SA using this same method but still allow SA
when I remote desktop
> to the machine and connect using Management Studio Express.
> I.e if I disable the account the SA account or other admin accounts I cant
login when I RDP into
> the machine.
> The minimal privileges user can simply run a single stored procedure but m
ust do using 1433.
> I just need to disable 1433 access for all other accounts and cant figure
out how to (I guess
> Management Studio Express connects using 1433 anyway so im stuck).
> If this is the case I guess I need to use SQLXML.
> Thanks for any help
> Scott
>[/vbcol]

disable all SA accounts remotly - access only for low user ?

Hi,
I need to access my web based SQL 2005 express edition database on 1433
using a user with minimal privileges that can only run a stored procedure. I
do this by connecting to IP / port.
Is there anyway I can disable SA using this same method but still allow SA
when I remote desktop to the machine and connect using Management Studio
Express.
I.e if I disable the account the SA account or other admin accounts I cant
login when I RDP into the machine.
The minimal privileges user can simply run a single stored procedure but
must do using 1433.
I just need to disable 1433 access for all other accounts and cant figure
out how to (I guess Management Studio Express connects using 1433 anyway so
im stuck).
If this is the case I guess I need to use SQLXML.
Thanks for any help
ScottI'm not sure, but you could try playing with something like:
DENY CONNECT ON ENDPOINT::TCP TO "sa"
(Check BOL for exact syntax)
I'm not sure whether such permissions are actually checked for sysadmins (let us know after your
test), and of course, you'd have to make sure some other netlib is used when inside the private
network (like Shared Memory, which only work locally).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <www.sage-eshop.com> wrote in message news:O$T7b0J3GHA.4764@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I need to access my web based SQL 2005 express edition database on 1433 using a user with minimal
> privileges that can only run a stored procedure. I do this by connecting to IP / port.
> Is there anyway I can disable SA using this same method but still allow SA when I remote desktop
> to the machine and connect using Management Studio Express.
> I.e if I disable the account the SA account or other admin accounts I cant login when I RDP into
> the machine.
> The minimal privileges user can simply run a single stored procedure but must do using 1433.
> I just need to disable 1433 access for all other accounts and cant figure out how to (I guess
> Management Studio Express connects using 1433 anyway so im stuck).
> If this is the case I guess I need to use SQLXML.
> Thanks for any help
> Scott
>

Disable a task in the current package programmatically?

Hi,

I have a package comprising a number of Data Flow Task steps, to import various tables of data from some Access databases into SQL Server. The name of some of these Access databases will change depending on the date, e.g. last year's data is currently in a database called "2005data.mdb". At year end this will be superseded by "2006data.mdb". The Access databases are within a 3rd party system so I have no control over the file names.

I have a Script Task that checks the current date, and changes the name of an Access database in the connection string to reflect last year's date. But to complicate matters, last year's file might not exist.

So the Script Task checks whether the file name exists. If not, I would like to disable the Task that uses this connection. But how do I reference a task within the package that contains my Script Task, by name, to set the Disabled attribute to true?

I want to do something like CurrentPackage.Tasks("MyDataFlowTask").Disabled = True.

I would also welcome alternative suggestions for how to achieve this.

Many thanks,

Keith.

You cannot reference inwards within a package, so you cannot reference a task. The task must reference the value. The way to do this is to set a variable, and use a property expression that uses that variable value. So you would set a variable, DisableMyTask as true to false in the script then set the property expression on the task's Disable property to that variable, "@.DisableMyTask".

I would however be careful using the Disable property, as the evaluation can be done before you set the variable, so it is too late. A better way to do this is to use an expression on Workflow. Have a constraint leading to your task, and then just add the variable as the test, or rather the negative, so that the constraint is only satisfied when the variable is false. If you have no preceding task to anchor the constraint, just use a sequence container. It will act as an anchor, but do nothing else.

|||

Keith,

You can also add an Expression to the constraint from your Script Task that is modifying the connection string. By doing so you can control the flow without having to enable/disable the invalid tasks.

Here's a link on how to add expressions to a precendence constraint: http://msdn2.microsoft.com/en-us/library/ms140040.aspx

Cheers,
Patrik

|||

Hi,

Thanks for the replies. I have tried adding expressions to the precedence constraints, but it is not working correctly.

I originally had 7 Tasks, with a simple precedence constraint of Success between one and the next. I've now added an expression based on a variable, and changed the precedence constraint to "Constraint and Expression", but with this change, steps 5 to 7 no longer execute. They are still validated, but do not Start.

In simplistic terms, the logic I have added is:

After Task 3, if Success and (@.boolPCStatsLastYearExists) then Execute Task 4.

After Task 4, if Success, execute Task 5.

After Task 3, if Success and (@.boolPCStatsLastYearExists = False) then Execute Task 5.

So, whether the boolean value @.boolPCStatsLastYearExists is True or False, Task 5 should be executed. In fact I think it is set to True, because Task 4 executes and creates rows in the database, so it appears Task 4 is executing but Task 5 isn't.

I am developing on my local machine, but cannot run locally (I get an error on the first Data Flow Task), because the SQL database is on a server. So I am deploying to the server and running there.

Can you advise how I should get debugging information to indicate what happens after Task 4 executes, and why Task 5 isn't reached?

Many thanks,

Keith.

|||

To debug stuff, use the debugger. Creating a mock DB on your local machine would be a good idea.

Sounds like you have two constraints leading to task 5, but they follow different, mutually exclusive execution paths. So you will never satisfy both constraints in a single execution, correct? If so, have you turned the Or logic option on for those constsraints? It is in the constraint UI, or the LogicalAnd property of the constraint, set it to false.

|||

Hi again,

Thanks for that. I had left the default setting (AND) on the constraints. Changing all the constraints where 2 constraints affect one task, led to some progress.

Tasks 1 - 6 now execute fine. But Task 7 never executes.

The constraints on it are:

From Task 5, Success AND @.boolPCFDR1LastYearExists = False

OR

From Task 6, Success.

The first of these conditions should be met, as Task 5 succeeds and writes data, and the boolean is set to False by Task 1 (the Script task), which I can verify in the debugger.

I would greatly appreciate any further suggestions. However, I suspect I will now need to install SQL Server on my already overloaded laptop and recreate the server database locally, so that I can debug. But having looked at the debugger, I am not confident I will have a sufficiently granular view to identify the problem. I'll report back!

Thanks for all your help so far,

Keith.

|||

Hi,

OK, I've installed SQL Server locally, copied the database to it, and changed my connection to point to the local database. The same error occurs, and I don't see how to use the debugger to help me resolve it.

I can put a stop in Step 5 Post-Execute, but I can't do any more than verify that the boolean is set to False as expected. What can I do to identify why the processor does not proceed to execute Step 7?

Thanks,

Keith.

|||

Solved! (Sort of). I've had a problem with conditions on a precedence constraint. I can set a condition of @.myBooleanVariable, and it executes correctly, only when the variable is set to true. However, I can't find a syntax to say execute when this boolean is False. I tried the obvious NOT @.myBooleanVariable, but this does not pass the syntax check. @.myBooleanVariable = False passes the syntax check, but crucially, does not execute. I've not found another syntax that is accepted.

So reluctantly, I created 2 new variables, set them to the boolean opposite of the 2 existing variables, and used them in my "negative" conditions. So all my conditions on Precedence Constraints now comprise just the name of a boolean variable, prefixed with @..

Thanks,

Keith.

|||

Keith,

In the expressions not is represented with ! and equivalency is tested with ==. This should help you remove the additional variable.

Cheers,
Patrik