Two questions:
1. With SQL, how can I setup display mask for dollar amount :
eg. 100000 display as $100,000
2. How can I select number N to M records in a table.
eg. select No. 50 - 100 records from a table. ( not top 50)
Thanks,
Guyang1.
cast(100000 as money)
2.
select top 51 foo
from (
select top 100 foo
from yourtable
order by foo desc
) as derivedtable
order by foo
rudy
http://r937.com/|||Formatting of results is best handled by the user interface (in my humble opinion).
Rudy's method works for a specific case, but the TOP command (very unfortunately) does not accept variables as parameters. You will need to cast your query as a dynamic SQL statement to use it for the general N of M case.
A lot of people come across this issue when trying to produce results that can be "paged" through, such as in a web page. If this is the case for you, then instead of trying to find records N of M, have your interface submit the largest sort-value of the previous recordset as a parameter, and then your procedure can return the first 50 (hard-coded) records that are greater than that value.
blindman|||For question one,
I try the cast(100000 as money), result as:
100000.0000 , any way to output as: $100,000 ?
Thanks for advise.
Guyang|||SELECT '$' + CONVERT (varchar(10), 100000, 1)
If you don't want the decimal values:
SELECT '$' + LEFT(CONVERT(varchar(10), 100000, 1), len(CONVERT (varchar(10), 100000, 1) - 3))
...but I'll say again that it is better to let the interface handle this. I'm thinking that you are coming from the Access world, but Access is a database and an interface wrapped together. SQL Server is purely a database (but much more powerful than MS Access). You should you other tools to communicate with the database and format the output, such as VB applications, MS Access Data Projects, or (yech) Crystal Reports.
blindmansql
2012年3月29日星期四
2012年3月27日星期二
Disk space usage
SQL 7.0 SP4/NT 4.0
DB File is set to grow by 5%.
It's given that SQL preallocates its hard disk space so
that as records are added to the table, there is no
change to the hard drive space at the NT level.
Is there a way to see, at the SQL level, how much of the
space currently allocated, is available?
Thanks,
Donsp_spaceused will show you how much space is currently
allocated in a database.
Linchi
>--Original Message--
>SQL 7.0 SP4/NT 4.0
>DB File is set to grow by 5%.
>It's given that SQL preallocates its hard disk space so
>that as records are added to the table, there is no
>change to the hard drive space at the NT level.
>Is there a way to see, at the SQL level, how much of the
>space currently allocated, is available?
>Thanks,
>Don
>
>.
>
DB File is set to grow by 5%.
It's given that SQL preallocates its hard disk space so
that as records are added to the table, there is no
change to the hard drive space at the NT level.
Is there a way to see, at the SQL level, how much of the
space currently allocated, is available?
Thanks,
Donsp_spaceused will show you how much space is currently
allocated in a database.
Linchi
>--Original Message--
>SQL 7.0 SP4/NT 4.0
>DB File is set to grow by 5%.
>It's given that SQL preallocates its hard disk space so
>that as records are added to the table, there is no
>change to the hard drive space at the NT level.
>Is there a way to see, at the SQL level, how much of the
>space currently allocated, is available?
>Thanks,
>Don
>
>.
>
2012年3月8日星期四
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.
Bad WLAN performance, but the network stack is not aware of it. So your
session thinks it is okay, but it's not.
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,
Try using a permanent open recordset.
Use filterted recordsets.
Use serverside filters (views).
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...
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.
>
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:
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?
>
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.
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.
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.
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.
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.
2012年3月7日星期三
Disapearing Records
The first line in my global.asax is the creation of an ActiveX object that will hold a data connection to a SQL2000 Server database for the lifespan of an "Application" scope.
< OBJECT RUNAT="Server" SCOPE="Application" ID="Conn" name="Conn" PROGID="ADODB.Connection" VIEWASTEXT >< /OBJECT >
With that done, every time a new user invokes global.asax, a routine will verify rather it needs to open the connection or if the connection is already open:
If Conn.State <> adStateOpen Then
Conn.ConnectionTimeOut = 999999
Conn.ConnectionString = strConnStr
Conn.CursorLocation = adUseClient
Conn.Open
End If
The purpose of this is to avoid 400 simultaneous users from having to wait for their connections to estabilish communication to the server before initiating transaction.
Surprisingly enough, this works perfectly (for a massively shared, perpetual Connection object), except that once or twice a month, a record disapears from the main database and takes with it all relevant records from supporting tables.
Yes, it seems like a Forced Roolback is being issued...
Do you see Why?
Aleks,I don't see anything that would cause that behavior based on the code you've provided.
Logging deletions through a trigger is one possible first step to figuring out when, where and why the behavior is occurring.
David Sceppa
Microsoft
2012年2月25日星期六
Disabling the flat file destination at run-time?
I noticed that The flat file destination that I am using, creates a blank file everytime even if there are no records processed by the data flow.
I just wanted to know if there is any way to stop this. I dont want to see a chunk of blank files on the disk. (I create the files with a timestamp in the file name so every run creates a new file.)
So I want to implement a logic to stop the creation of these blank files in case there are no records to write into it.
Any suggestion would be of great help.
Regards
Saurabh
One option is to use an Execute SQL in the control flow before the data flow to check the record count, and use a condition on a precedence constraint to the data flow to prevent it from running at all.
Another option is to use a Row Count component in the data flow to capture the number of rows written to the flat file. Set a File System task to delete the file after the data flow, with a precedence constraint that uses the row count to determine whether the File System task should execute.
|||well the first option is not suitable for me because I cannot determine the number of records to be written beforehand.
second option looks good. I'll do that but again that will be having to do one extra task. if at all there is any way to stop the file creation then that will be great. (I am playing around with a script component with no luck)
Thanks for your response John!
Please get back if you find out any other way...
|||
jwelch wrote:
One option is to use an Execute SQL in the control flow before the data flow to check the record count, and use a condition on a precedence constraint to the data flow to prevent it from running at all.
Another option is to use a Row Count component in the data flow to capture the number of rows written to the flat file. Set a File System task to delete the file after the data flow, with a precedence constraint that uses the row count to determine whether the File System task should execute.
Or to adapt a theme, push the data into a raw file or recordset (http://blogs.conchango.com/jamiethomson/archive/2006/06/28/SSIS_3A00_-Comparing-performance-of-a-raw-file-against-a-recordset-destination.aspx) and then use a conditional precedence constraint (as suggested by John) to decide whether to push the data into your flat file or not.
Plenty of ways anyway.
-Jamie
|||Sounds good! though me too didn't like to push the intermediate data to the disk, the performance analysis shown by Jamie is really helpful in making a decision.
Thanks for the response. I shall go with this solution insead of adding more complications to my short and sweet package!
Regards
Saurabh
Disabling 'Export to excel' option when record count >5000 in a re
Hi,
I have a report that has two drill downs that drill to about 5000 records
and due to this I am having time out problems while exporting to excel. For
now, we have worked around the timeout problem but what I would like to know
is whether or not there is a way to disable the excel option in the export
drop down list - only when the SQL report generates like more than 5000 rows.
The reason to disable being, the RS/RM is consuming CPU like crazy and its
repurcussions are unbearable.Hi,
The only thing you can do is to disable Excel export server wide. This you
can do in the RSReportServer.config and add Visible="false" to the element of
the Excel export.
Unfortunately it isn't possible to set it per report or even dynamic.
Jan Pieter Posthuma
"Jammer" wrote:
> Hi,
> I have a report that has two drill downs that drill to about 5000 records
> and due to this I am having time out problems while exporting to excel. For
> now, we have worked around the timeout problem but what I would like to know
> is whether or not there is a way to disable the excel option in the export
> drop down list - only when the SQL report generates like more than 5000 rows.
> The reason to disable being, the RS/RM is consuming CPU like crazy and its
> repurcussions are unbearable.|||"Jan Pieter Posthuma" <JanPieterPosthuma@.discussions.microsoft.com> wrote in
message news:B18CDAAB-35F2-4CA6-AC13-EC30640558EA@.microsoft.com...
> Hi,
> The only thing you can do is to disable Excel export server wide. This you
> can do in the RSReportServer.config and add Visible="false" to the element
> of
> the Excel export.
> Unfortunately it isn't possible to set it per report or even dynamic.
>
If you take a short peek into the generated script when you activate the
toolbar, you can create your own 'export' button with or without excel
export very easily (rs:format=EXCEL)
I modified the ReportViewer sample to display a small popup toolbar with:
export the Excel, PDF and Print on it (takes up way less space then the
standard toolbar)
Alfred|||Hey Alfred,
Can you tell me how you achieved creating your own export to excel bar:
Thanks
Balaji
"Smeenk, A.D." wrote:
> "Jan Pieter Posthuma" <JanPieterPosthuma@.discussions.microsoft.com> wrote in
> message news:B18CDAAB-35F2-4CA6-AC13-EC30640558EA@.microsoft.com...
> > Hi,
> >
> > The only thing you can do is to disable Excel export server wide. This you
> > can do in the RSReportServer.config and add Visible="false" to the element
> > of
> > the Excel export.
> >
> > Unfortunately it isn't possible to set it per report or even dynamic.
> >
> If you take a short peek into the generated script when you activate the
> toolbar, you can create your own 'export' button with or without excel
> export very easily (rs:format=EXCEL)
> I modified the ReportViewer sample to display a small popup toolbar with:
> export the Excel, PDF and Print on it (takes up way less space then the
> standard toolbar)
> Alfred
>
>|||"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:29713B57-0C78-4AB9-B7BE-8E5234D59809@.microsoft.com...
> Hey Alfred,
> Can you tell me how you achieved creating your own export to excel bar:
>
Like I said, I modifed the ReportViewer sample, its (by default) installed
here:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Applications\ReportViewer
I modified the properties and added 'small' to the ToolBar options, and when
selected I generate different code. I add an extra table around the iframe
and use a div to display a popup menu like when you hover over an image in
IE. The 'onclick' of the images activates a new window with the requested
export of the report (actually a re-render of the report I think, I didn't
add the session ID)
Alfred|||Instead of disabling export to Excel you might want to add a link at the top
of the page that says Export Data. Use jump to URL and specify CSV and ASCII
(the CSV export does Unicode and Excel doesn't handle unicode CSV). By
rendering in ASCII CSV Excel comes up automatically and this is very very
fast. I had an Excel export that took 5 minutes and brought the server to
its knees go to 10 seconds.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Smeenk, A.D." <alfreds@.dbs-at-nospam-at-nl> wrote in message
news:uX66yHuVFHA.2196@.TK2MSFTNGP09.phx.gbl...
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:29713B57-0C78-4AB9-B7BE-8E5234D59809@.microsoft.com...
>> Hey Alfred,
>> Can you tell me how you achieved creating your own export to excel bar:
> Like I said, I modifed the ReportViewer sample, its (by default) installed
> here:
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Applications\ReportViewer
> I modified the properties and added 'small' to the ToolBar options, and
> when selected I generate different code. I add an extra table around the
> iframe and use a div to display a popup menu like when you hover over an
> image in IE. The 'onclick' of the images activates a new window with the
> requested export of the report (actually a re-render of the report I
> think, I didn't add the session ID)
> Alfred
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23HdntxuVFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Instead of disabling export to Excel you might want to add a link at the
> top of the page that says Export Data. Use jump to URL and specify CSV and
> ASCII (the CSV export does Unicode and Excel doesn't handle unicode CSV).
> By rendering in ASCII CSV Excel comes up automatically and this is very
> very fast. I had an Excel export that took 5 minutes and brought the
> server to its knees go to 10 seconds.
>
That 'export to CSV' should 'officially' be on the right-click menu when you
view a table layout report part, now for some reason that does not function
on all machines... any idea's?
Yes same report, all machines Windows XP SP2, IE 6.
With one person the option re-appeared after a reset of the local intranet
security to default...
Alfred|||No idea about that. Note that the export to CSV you are talking about
exports as Unicode and Excel will bring it all together in a single column.
Bruce L-C
"Smeenk, A.D." <alfreds@.dbs-at-nospam-at-nl> wrote in message
news:OFAlFu4VFHA.1452@.TK2MSFTNGP14.phx.gbl...
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23HdntxuVFHA.2664@.TK2MSFTNGP15.phx.gbl...
>> Instead of disabling export to Excel you might want to add a link at the
>> top of the page that says Export Data. Use jump to URL and specify CSV
>> and ASCII (the CSV export does Unicode and Excel doesn't handle unicode
>> CSV). By rendering in ASCII CSV Excel comes up automatically and this is
>> very very fast. I had an Excel export that took 5 minutes and brought the
>> server to its knees go to 10 seconds.
> That 'export to CSV' should 'officially' be on the right-click menu when
> you view a table layout report part, now for some reason that does not
> function on all machines... any idea's?
> Yes same report, all machines Windows XP SP2, IE 6.
> With one person the option re-appeared after a reset of the local intranet
> security to default...
> Alfred
>|||Why don't you increase the timeout?
Farahmand
"Jammer" wrote:
> Hi,
> I have a report that has two drill downs that drill to about 5000 records
> and due to this I am having time out problems while exporting to excel. For
> now, we have worked around the timeout problem but what I would like to know
> is whether or not there is a way to disable the excel option in the export
> drop down list - only when the SQL report generates like more than 5000 rows.
> The reason to disable being, the RS/RM is consuming CPU like crazy and its
> repurcussions are unbearable.|||Why don't you increase the timeout?
Farahmand
"Jammer" wrote:
> Hi,
> I have a report that has two drill downs that drill to about 5000 records
> and due to this I am having time out problems while exporting to excel. For
> now, we have worked around the timeout problem but what I would like to know
> is whether or not there is a way to disable the excel option in the export
> drop down list - only when the SQL report generates like more than 5000 rows.
> The reason to disable being, the RS/RM is consuming CPU like crazy and its
> repurcussions are unbearable.
I have a report that has two drill downs that drill to about 5000 records
and due to this I am having time out problems while exporting to excel. For
now, we have worked around the timeout problem but what I would like to know
is whether or not there is a way to disable the excel option in the export
drop down list - only when the SQL report generates like more than 5000 rows.
The reason to disable being, the RS/RM is consuming CPU like crazy and its
repurcussions are unbearable.Hi,
The only thing you can do is to disable Excel export server wide. This you
can do in the RSReportServer.config and add Visible="false" to the element of
the Excel export.
Unfortunately it isn't possible to set it per report or even dynamic.
Jan Pieter Posthuma
"Jammer" wrote:
> Hi,
> I have a report that has two drill downs that drill to about 5000 records
> and due to this I am having time out problems while exporting to excel. For
> now, we have worked around the timeout problem but what I would like to know
> is whether or not there is a way to disable the excel option in the export
> drop down list - only when the SQL report generates like more than 5000 rows.
> The reason to disable being, the RS/RM is consuming CPU like crazy and its
> repurcussions are unbearable.|||"Jan Pieter Posthuma" <JanPieterPosthuma@.discussions.microsoft.com> wrote in
message news:B18CDAAB-35F2-4CA6-AC13-EC30640558EA@.microsoft.com...
> Hi,
> The only thing you can do is to disable Excel export server wide. This you
> can do in the RSReportServer.config and add Visible="false" to the element
> of
> the Excel export.
> Unfortunately it isn't possible to set it per report or even dynamic.
>
If you take a short peek into the generated script when you activate the
toolbar, you can create your own 'export' button with or without excel
export very easily (rs:format=EXCEL)
I modified the ReportViewer sample to display a small popup toolbar with:
export the Excel, PDF and Print on it (takes up way less space then the
standard toolbar)
Alfred|||Hey Alfred,
Can you tell me how you achieved creating your own export to excel bar:
Thanks
Balaji
"Smeenk, A.D." wrote:
> "Jan Pieter Posthuma" <JanPieterPosthuma@.discussions.microsoft.com> wrote in
> message news:B18CDAAB-35F2-4CA6-AC13-EC30640558EA@.microsoft.com...
> > Hi,
> >
> > The only thing you can do is to disable Excel export server wide. This you
> > can do in the RSReportServer.config and add Visible="false" to the element
> > of
> > the Excel export.
> >
> > Unfortunately it isn't possible to set it per report or even dynamic.
> >
> If you take a short peek into the generated script when you activate the
> toolbar, you can create your own 'export' button with or without excel
> export very easily (rs:format=EXCEL)
> I modified the ReportViewer sample to display a small popup toolbar with:
> export the Excel, PDF and Print on it (takes up way less space then the
> standard toolbar)
> Alfred
>
>|||"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:29713B57-0C78-4AB9-B7BE-8E5234D59809@.microsoft.com...
> Hey Alfred,
> Can you tell me how you achieved creating your own export to excel bar:
>
Like I said, I modifed the ReportViewer sample, its (by default) installed
here:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Applications\ReportViewer
I modified the properties and added 'small' to the ToolBar options, and when
selected I generate different code. I add an extra table around the iframe
and use a div to display a popup menu like when you hover over an image in
IE. The 'onclick' of the images activates a new window with the requested
export of the report (actually a re-render of the report I think, I didn't
add the session ID)
Alfred|||Instead of disabling export to Excel you might want to add a link at the top
of the page that says Export Data. Use jump to URL and specify CSV and ASCII
(the CSV export does Unicode and Excel doesn't handle unicode CSV). By
rendering in ASCII CSV Excel comes up automatically and this is very very
fast. I had an Excel export that took 5 minutes and brought the server to
its knees go to 10 seconds.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Smeenk, A.D." <alfreds@.dbs-at-nospam-at-nl> wrote in message
news:uX66yHuVFHA.2196@.TK2MSFTNGP09.phx.gbl...
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:29713B57-0C78-4AB9-B7BE-8E5234D59809@.microsoft.com...
>> Hey Alfred,
>> Can you tell me how you achieved creating your own export to excel bar:
> Like I said, I modifed the ReportViewer sample, its (by default) installed
> here:
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Applications\ReportViewer
> I modified the properties and added 'small' to the ToolBar options, and
> when selected I generate different code. I add an extra table around the
> iframe and use a div to display a popup menu like when you hover over an
> image in IE. The 'onclick' of the images activates a new window with the
> requested export of the report (actually a re-render of the report I
> think, I didn't add the session ID)
> Alfred
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23HdntxuVFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Instead of disabling export to Excel you might want to add a link at the
> top of the page that says Export Data. Use jump to URL and specify CSV and
> ASCII (the CSV export does Unicode and Excel doesn't handle unicode CSV).
> By rendering in ASCII CSV Excel comes up automatically and this is very
> very fast. I had an Excel export that took 5 minutes and brought the
> server to its knees go to 10 seconds.
>
That 'export to CSV' should 'officially' be on the right-click menu when you
view a table layout report part, now for some reason that does not function
on all machines... any idea's?
Yes same report, all machines Windows XP SP2, IE 6.
With one person the option re-appeared after a reset of the local intranet
security to default...
Alfred|||No idea about that. Note that the export to CSV you are talking about
exports as Unicode and Excel will bring it all together in a single column.
Bruce L-C
"Smeenk, A.D." <alfreds@.dbs-at-nospam-at-nl> wrote in message
news:OFAlFu4VFHA.1452@.TK2MSFTNGP14.phx.gbl...
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23HdntxuVFHA.2664@.TK2MSFTNGP15.phx.gbl...
>> Instead of disabling export to Excel you might want to add a link at the
>> top of the page that says Export Data. Use jump to URL and specify CSV
>> and ASCII (the CSV export does Unicode and Excel doesn't handle unicode
>> CSV). By rendering in ASCII CSV Excel comes up automatically and this is
>> very very fast. I had an Excel export that took 5 minutes and brought the
>> server to its knees go to 10 seconds.
> That 'export to CSV' should 'officially' be on the right-click menu when
> you view a table layout report part, now for some reason that does not
> function on all machines... any idea's?
> Yes same report, all machines Windows XP SP2, IE 6.
> With one person the option re-appeared after a reset of the local intranet
> security to default...
> Alfred
>|||Why don't you increase the timeout?
Farahmand
"Jammer" wrote:
> Hi,
> I have a report that has two drill downs that drill to about 5000 records
> and due to this I am having time out problems while exporting to excel. For
> now, we have worked around the timeout problem but what I would like to know
> is whether or not there is a way to disable the excel option in the export
> drop down list - only when the SQL report generates like more than 5000 rows.
> The reason to disable being, the RS/RM is consuming CPU like crazy and its
> repurcussions are unbearable.|||Why don't you increase the timeout?
Farahmand
"Jammer" wrote:
> Hi,
> I have a report that has two drill downs that drill to about 5000 records
> and due to this I am having time out problems while exporting to excel. For
> now, we have worked around the timeout problem but what I would like to know
> is whether or not there is a way to disable the excel option in the export
> drop down list - only when the SQL report generates like more than 5000 rows.
> The reason to disable being, the RS/RM is consuming CPU like crazy and its
> repurcussions are unbearable.
2012年2月17日星期五
disable logging
Is there is a way to disable logging as i need to
transfer 4000000 records from oracle..I am doing throgh
this DTS..my transaction los size grows more than 10 GB..
Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>
|||Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>
transfer 4000000 records from oracle..I am doing throgh
this DTS..my transaction los size grows more than 10 GB..
Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>
|||Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>
disable logging
Is there is a way to disable logging as i need to
transfer 4000000 records from oracle..I am doing throgh
this DTS..my transaction los size grows more than 10 GB..Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>|||Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>
transfer 4000000 records from oracle..I am doing throgh
this DTS..my transaction los size grows more than 10 GB..Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>|||Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>
disable logging
Is there is a way to disable logging as i need to
transfer 4000000 records from oracle..I am doing throgh
this DTS..my transaction los size grows more than 10 GB..Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>|||Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>
transfer 4000000 records from oracle..I am doing throgh
this DTS..my transaction los size grows more than 10 GB..Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>|||Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>
2012年2月14日星期二
disable and enable triggers
hi,
I have 500-1000 records daily need to be updated in a heavy production
table. I have to disable the all triggers update records, enable all
triggers again.
My question is ...
1. Is it practicale in prod table?
2. Is table doing reindexing everytime I enable the triggers in that table.
3.What is the best way to update the records it if above is bad.
Thanks
mecn wrote:
> hi,
> I have 500-1000 records daily need to be updated in a heavy production
> table. I have to disable the all triggers update records, enable all
> triggers again.
> My question is ...
> 1. Is it practicale in prod table?
> 2. Is table doing reindexing everytime I enable the triggers in that table.
> 3.What is the best way to update the records it if above is bad.
> Thanks
>
>
Why do you need to disable the triggers to do this update?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Yes,
I don't want to fire the triggers when I updating records
on that table
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE8D0F.5040007@.realsqlguy.com...
> mecn wrote:
> Why do you need to disable the triggers to do this update?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||http://msdn2.microsoft.com/en-us/library/ms189748.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>
|||mecn,
Then why are the triggers there in the first place? If there is a business
rule as to which kinds of rows should fire the trigger then you might be
able to incorporate that into the trigger itself.
-- Bill
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>
I have 500-1000 records daily need to be updated in a heavy production
table. I have to disable the all triggers update records, enable all
triggers again.
My question is ...
1. Is it practicale in prod table?
2. Is table doing reindexing everytime I enable the triggers in that table.
3.What is the best way to update the records it if above is bad.
Thanks
mecn wrote:
> hi,
> I have 500-1000 records daily need to be updated in a heavy production
> table. I have to disable the all triggers update records, enable all
> triggers again.
> My question is ...
> 1. Is it practicale in prod table?
> 2. Is table doing reindexing everytime I enable the triggers in that table.
> 3.What is the best way to update the records it if above is bad.
> Thanks
>
>
Why do you need to disable the triggers to do this update?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Yes,
I don't want to fire the triggers when I updating records
on that table
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE8D0F.5040007@.realsqlguy.com...
> mecn wrote:
> Why do you need to disable the triggers to do this update?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||http://msdn2.microsoft.com/en-us/library/ms189748.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>
|||mecn,
Then why are the triggers there in the first place? If there is a business
rule as to which kinds of rows should fire the trigger then you might be
able to incorporate that into the trigger itself.
-- Bill
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>
disable and enable triggers
hi,
I have 500-1000 records daily need to be updated in a heavy production
table. I have to disable the all triggers update records, enable all
triggers again.
My question is ...
1. Is it practicale in prod table?
2. Is table doing reindexing everytime I enable the triggers in that table.
3.What is the best way to update the records it if above is bad.
Thanksmecn wrote:
> hi,
> I have 500-1000 records daily need to be updated in a heavy production
> table. I have to disable the all triggers update records, enable all
> triggers again.
> My question is ...
> 1. Is it practicale in prod table?
> 2. Is table doing reindexing everytime I enable the triggers in that table
.
> 3.What is the best way to update the records it if above is bad.
> Thanks
>
>
Why do you need to disable the triggers to do this update?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes,
I don't want to fire the triggers when I updating records
on that table
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE8D0F.5040007@.realsqlguy.com...
> mecn wrote:
> Why do you need to disable the triggers to do this update?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||http://msdn2.microsoft.com/en-us/library/ms189748.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>|||mecn,
Then why are the triggers there in the first place? If there is a business
rule as to which kinds of rows should fire the trigger then you might be
able to incorporate that into the trigger itself.
-- Bill
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>
I have 500-1000 records daily need to be updated in a heavy production
table. I have to disable the all triggers update records, enable all
triggers again.
My question is ...
1. Is it practicale in prod table?
2. Is table doing reindexing everytime I enable the triggers in that table.
3.What is the best way to update the records it if above is bad.
Thanksmecn wrote:
> hi,
> I have 500-1000 records daily need to be updated in a heavy production
> table. I have to disable the all triggers update records, enable all
> triggers again.
> My question is ...
> 1. Is it practicale in prod table?
> 2. Is table doing reindexing everytime I enable the triggers in that table
.
> 3.What is the best way to update the records it if above is bad.
> Thanks
>
>
Why do you need to disable the triggers to do this update?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes,
I don't want to fire the triggers when I updating records
on that table
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE8D0F.5040007@.realsqlguy.com...
> mecn wrote:
> Why do you need to disable the triggers to do this update?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||http://msdn2.microsoft.com/en-us/library/ms189748.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>|||mecn,
Then why are the triggers there in the first place? If there is a business
rule as to which kinds of rows should fire the trigger then you might be
able to incorporate that into the trigger itself.
-- Bill
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>
disable and enable triggers
hi,
I have 500-1000 records daily need to be updated in a heavy production
table. I have to disable the all triggers update records, enable all
triggers again.
My question is ...
1. Is it practicale in prod table?
2. Is table doing reindexing everytime I enable the triggers in that table.
3.What is the best way to update the records it if above is bad.
Thanksmecn wrote:
> hi,
> I have 500-1000 records daily need to be updated in a heavy production
> table. I have to disable the all triggers update records, enable all
> triggers again.
> My question is ...
> 1. Is it practicale in prod table?
> 2. Is table doing reindexing everytime I enable the triggers in that table.
> 3.What is the best way to update the records it if above is bad.
> Thanks
>
>
Why do you need to disable the triggers to do this update?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes,
I don't want to fire the triggers when I updating records
on that table
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE8D0F.5040007@.realsqlguy.com...
> mecn wrote:
>> hi,
>> I have 500-1000 records daily need to be updated in a heavy production
>> table. I have to disable the all triggers update records, enable all
>> triggers again.
>> My question is ...
>> 1. Is it practicale in prod table?
>> 2. Is table doing reindexing everytime I enable the triggers in that
>> table.
>> 3.What is the best way to update the records it if above is bad.
>> Thanks
>>
> Why do you need to disable the triggers to do this update?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||http://msdn2.microsoft.com/en-us/library/ms189748.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>> mecn wrote:
>> hi,
>> I have 500-1000 records daily need to be updated in a heavy production
>> table. I have to disable the all triggers update records, enable all
>> triggers again.
>> My question is ...
>> 1. Is it practicale in prod table?
>> 2. Is table doing reindexing everytime I enable the triggers in that
>> table.
>> 3.What is the best way to update the records it if above is bad.
>> Thanks
>>
>>
>> Why do you need to disable the triggers to do this update?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||mecn,
Then why are the triggers there in the first place? If there is a business
rule as to which kinds of rows should fire the trigger then you might be
able to incorporate that into the trigger itself.
-- Bill
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>> mecn wrote:
>> hi,
>> I have 500-1000 records daily need to be updated in a heavy production
>> table. I have to disable the all triggers update records, enable all
>> triggers again.
>> My question is ...
>> 1. Is it practicale in prod table?
>> 2. Is table doing reindexing everytime I enable the triggers in that
>> table.
>> 3.What is the best way to update the records it if above is bad.
>> Thanks
>>
>>
>> Why do you need to disable the triggers to do this update?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>
I have 500-1000 records daily need to be updated in a heavy production
table. I have to disable the all triggers update records, enable all
triggers again.
My question is ...
1. Is it practicale in prod table?
2. Is table doing reindexing everytime I enable the triggers in that table.
3.What is the best way to update the records it if above is bad.
Thanksmecn wrote:
> hi,
> I have 500-1000 records daily need to be updated in a heavy production
> table. I have to disable the all triggers update records, enable all
> triggers again.
> My question is ...
> 1. Is it practicale in prod table?
> 2. Is table doing reindexing everytime I enable the triggers in that table.
> 3.What is the best way to update the records it if above is bad.
> Thanks
>
>
Why do you need to disable the triggers to do this update?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes,
I don't want to fire the triggers when I updating records
on that table
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE8D0F.5040007@.realsqlguy.com...
> mecn wrote:
>> hi,
>> I have 500-1000 records daily need to be updated in a heavy production
>> table. I have to disable the all triggers update records, enable all
>> triggers again.
>> My question is ...
>> 1. Is it practicale in prod table?
>> 2. Is table doing reindexing everytime I enable the triggers in that
>> table.
>> 3.What is the best way to update the records it if above is bad.
>> Thanks
>>
> Why do you need to disable the triggers to do this update?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||http://msdn2.microsoft.com/en-us/library/ms189748.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>> mecn wrote:
>> hi,
>> I have 500-1000 records daily need to be updated in a heavy production
>> table. I have to disable the all triggers update records, enable all
>> triggers again.
>> My question is ...
>> 1. Is it practicale in prod table?
>> 2. Is table doing reindexing everytime I enable the triggers in that
>> table.
>> 3.What is the best way to update the records it if above is bad.
>> Thanks
>>
>>
>> Why do you need to disable the triggers to do this update?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||mecn,
Then why are the triggers there in the first place? If there is a business
rule as to which kinds of rows should fire the trigger then you might be
able to incorporate that into the trigger itself.
-- Bill
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>> mecn wrote:
>> hi,
>> I have 500-1000 records daily need to be updated in a heavy production
>> table. I have to disable the all triggers update records, enable all
>> triggers again.
>> My question is ...
>> 1. Is it practicale in prod table?
>> 2. Is table doing reindexing everytime I enable the triggers in that
>> table.
>> 3.What is the best way to update the records it if above is bad.
>> Thanks
>>
>>
>> Why do you need to disable the triggers to do this update?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>
Disable all constraints in a table?
I have to delete records in quite a few of the tables of a DataWarehouse and
then populate them from else where. I'm not able to delete the records due to
Foreign Key constraints and I was wondering if there is a simpler way for me
to disable all the constraints in a table.
Thanks,
rgnI think you can use
alter table mytable nocheck constraint all
but I am not sure if it is a good idea as you will disable enforcing the
integrity of your data.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> I have to delete records in quite a few of the tables of a DataWarehouse and
> then populate them from else where. I'm not able to delete the records due to
> Foreign Key constraints and I was wondering if there is a simpler way for me
> to disable all the constraints in a table.
> Thanks,
> rgn
then populate them from else where. I'm not able to delete the records due to
Foreign Key constraints and I was wondering if there is a simpler way for me
to disable all the constraints in a table.
Thanks,
rgnI think you can use
alter table mytable nocheck constraint all
but I am not sure if it is a good idea as you will disable enforcing the
integrity of your data.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> I have to delete records in quite a few of the tables of a DataWarehouse and
> then populate them from else where. I'm not able to delete the records due to
> Foreign Key constraints and I was wondering if there is a simpler way for me
> to disable all the constraints in a table.
> Thanks,
> rgn
Disable all constraints in a table?
I have to delete records in quite a few of the tables of a DataWarehouse and
then populate them from else where. I'm not able to delete the records due t
o
Foreign Key constraints and I was wondering if there is a simpler way for me
to disable all the constraints in a table.
Thanks,
rgnI think you can use
alter table mytable nocheck constraint all
but I am not sure if it is a good idea as you will disable enforcing the
integrity of your data.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> I have to delete records in quite a few of the tables of a DataWarehouse a
nd
> then populate them from else where. I'm not able to delete the records due
to
> Foreign Key constraints and I was wondering if there is a simpler way for
me
> to disable all the constraints in a table.
> Thanks,
> rgn
then populate them from else where. I'm not able to delete the records due t
o
Foreign Key constraints and I was wondering if there is a simpler way for me
to disable all the constraints in a table.
Thanks,
rgnI think you can use
alter table mytable nocheck constraint all
but I am not sure if it is a good idea as you will disable enforcing the
integrity of your data.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> I have to delete records in quite a few of the tables of a DataWarehouse a
nd
> then populate them from else where. I'm not able to delete the records due
to
> Foreign Key constraints and I was wondering if there is a simpler way for
me
> to disable all the constraints in a table.
> Thanks,
> rgn
Disable all constraints in a table?
I have to delete records in quite a few of the tables of a DataWarehouse and
then populate them from else where. I'm not able to delete the records due to
Foreign Key constraints and I was wondering if there is a simpler way for me
to disable all the constraints in a table.
Thanks,
rgn
I think you can use
alter table mytable nocheck constraint all
but I am not sure if it is a good idea as you will disable enforcing the
integrity of your data.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> I have to delete records in quite a few of the tables of a DataWarehouse and
> then populate them from else where. I'm not able to delete the records due to
> Foreign Key constraints and I was wondering if there is a simpler way for me
> to disable all the constraints in a table.
> Thanks,
> rgn
then populate them from else where. I'm not able to delete the records due to
Foreign Key constraints and I was wondering if there is a simpler way for me
to disable all the constraints in a table.
Thanks,
rgn
I think you can use
alter table mytable nocheck constraint all
but I am not sure if it is a good idea as you will disable enforcing the
integrity of your data.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> I have to delete records in quite a few of the tables of a DataWarehouse and
> then populate them from else where. I'm not able to delete the records due to
> Foreign Key constraints and I was wondering if there is a simpler way for me
> to disable all the constraints in a table.
> Thanks,
> rgn
订阅：
博文 (Atom)