We're trying to put a view of data maintained in desktop Access databases online and into SQL Server.
The desktop Access system uses separate databases instead of tables within one database, It's a strange design, but it can't be changed.
We have been importing all of the separate databases into a single, new Access database, then upsizing the new databse to SQL Server, then uploading it.
This is not going to work long term, because we are stuck with a 250 mB Access database to upsize and upload, when we never need to update more than 2 or 3 of the tables and upload more than 2 mB.
We'd like to be able to upload only the tables -- preferably the Access *.mdb's -- that have changed, and then replace the SQL Server tables with the new information. And we'd like to automate it as much as possible, without upsizing Wizardy.
I don't know where to even begin looking for information about how this might be done.
Any suggestions would be deeply appreciated.
- Tinker
This sounds possibly like a job for DTS. You could set up a DTSpackage on the SQL Server that will loop through the Access databasesand perform the updates as needed.
This site is a good resources for DTS work:http://www.sqldts.com.
|||Hi Terri, and thank you.
When I first ran across information about DTS, I skipped right past it. I'm curently out in the MS Tech Net learning more and it appears DTS may be much closer to what we want to do than I first thought.
Neither the FAQs athttp://www.sqldts.com/ nor the information I have trolled-through so far on the Tech Net discuss automating DTS; is that possible?
While we don't want to ride herd on these data updates any more than is absolutely necessary, it would be super great if I don't have to try to program my way through deleting all the records in an SQL table, then refilling the table with the new records...
Appreciate your suggestion,
- Tinker|||Sure, it's possible to automate DTS. You can use SQL Agent toschedule a DTS package to run on a schedule of your choosing.|||Thank you. I finally found where I get to do all this.
Sometimes I feel dumber than usual... 8-)
- Tinker
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
Automatically add permissions on items for users?
Can someone give me a couple of pointers on how to interact with the web
service (?) to automatically add policies for a user to view folders and
reports rather than adding them manually through Report Manager?
thanksThis is a multi-part message in MIME format.
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Rather than authorizing each user in Reporting Services it is
recommended that you create a Windows Group (e.g. Reporting Users),
associate the Group with a Reporting Services Role (e.g. Browser), and
then when you create a new Windows User you make the user a member of
the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
&
Re: Automatically add permissions on items for users?
Rather than authorizing each user in Reporting =Services it is recommended that you create a Windows Group (e.g. =Reporting Users), associate the Group with a Reporting Services Role =(e.g. Browser), and then when you create a new Windows User you make the =user a member of the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620--|||the reason that I need to add each user is that I am using forms
authentication.
how can I call the web service to add policies for each user?
"Garry Lenz" wrote:
> Rather than authorizing each user in Reporting Services it is
> recommended that you create a Windows Group (e.g. Reporting Users),
> associate the Group with a Reporting Services Role (e.g. Browser), and
> then when you create a new Windows User you make the user a member of
> the [Reporting Users] Group.
> Garry
>
service (?) to automatically add policies for a user to view folders and
reports rather than adding them manually through Report Manager?
thanksThis is a multi-part message in MIME format.
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Rather than authorizing each user in Reporting Services it is
recommended that you create a Windows Group (e.g. Reporting Users),
associate the Group with a Reporting Services Role (e.g. Browser), and
then when you create a new Windows User you make the user a member of
the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
&
Re: Automatically add permissions on items for users?
Rather than authorizing each user in Reporting =Services it is recommended that you create a Windows Group (e.g. =Reporting Users), associate the Group with a Reporting Services Role =(e.g. Browser), and then when you create a new Windows User you make the =user a member of the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620--|||the reason that I need to add each user is that I am using forms
authentication.
how can I call the web service to add policies for each user?
"Garry Lenz" wrote:
> Rather than authorizing each user in Reporting Services it is
> recommended that you create a Windows Group (e.g. Reporting Users),
> associate the Group with a Reporting Services Role (e.g. Browser), and
> then when you create a new Windows User you make the user a member of
> the [Reporting Users] Group.
> Garry
>
Tuesday, March 20, 2012
Automatic SQL db update at set time?
I would like to limit the number of pages a user can view on my website each day. The users logs in and I can count the number of pages viewd in a field but i want to know how i can set the page count field to reset to 0 at the end of the day (ie midnight). Is it possible to do this? and if so how? Thanks.You can set up a job to run a stored proc at that time every day using SQL Server Agent (find it in SQL Server Enterprise Manager)
A better way might be to log pageviews with a timestamp, and then you can allow X pages within any 24 hour period - simply count the pageviews in the log newer than getdate() - 1 and check it against the limit.
Does that help?|||The server will be a shared sql server and i don't have access to creating new jobs, so I think your second suggestion would be best but not sure how to implement it. Do you have an example or a link to where I can find an example? Thanks|||Normally that should not be a problem - I use a shared database server (one of those cheap .net hosters) and I can create jobs just fine.
Think about my other solution if you really can't create jobs - it's better (I believe) and it does not require a scheduled job.
Check BOL for examples of creating jobs.
Monday, March 19, 2012
Automatic PDF output
Normally if I want a report in PDF I have to first view the report in the web browser, then select export as PDF and save the PDF file.
Is there a way to avoid the first step (web browser viewing) and have the report immediatly in PDF as soon as I click the "View Report" button? (without using email/file subscription)
Thank you,
Roberto
use this link
http://www.codeproject.com/sqlrs/PDFUsingSQLRepServices.asp
Wednesday, March 7, 2012
automate Access project connection to MSDE database
I'm using Visual C++ to insert records into my MSDE database. I'd like the
user to be able to view the data via Access projects. In my application, is
it possible to configure the connection settings for an Access project to
connect to the MSDE database? For example, if I were to click the View button
in my application, can I have Access project connect to the database behind
the scenes and then display the contents of the database?
Thanks!
You can use automation to launch Access and display forms and reports.
I'm not a C++ programmer, so I couldn't tell you *how* to do that.
However, I'm not sure I really understand what you are trying to do.
The phrase, "display the contents of the database" covers a lot of
ground. Perhaps you can be more specific.
--Mary
On Fri, 4 Feb 2005 15:13:02 -0800, "luv2travel"
<luv2travel@.discussions.microsoft.com> wrote:
>I'm using Visual C++ to insert records into my MSDE database. I'd like the
>user to be able to view the data via Access projects. In my application, is
>it possible to configure the connection settings for an Access project to
>connect to the MSDE database? For example, if I were to click the View button
>in my application, can I have Access project connect to the database behind
>the scenes and then display the contents of the database?
>Thanks!
|||When you create a new access project that connects to an existing database
there are certain settings that the wizard asks of you before connection to
the database can be made. How do I pass those parameters from my application
to access project? Once the project has been created after the connections
settings have been configured, the user will have full access to the tables
within the database. The user can then create queries and reports as needed.
Does this explain things a little better?
"Mary Chipman [MSFT]" wrote:
> You can use automation to launch Access and display forms and reports.
> I'm not a C++ programmer, so I couldn't tell you *how* to do that.
> However, I'm not sure I really understand what you are trying to do.
> The phrase, "display the contents of the database" covers a lot of
> ground. Perhaps you can be more specific.
> --Mary
> On Fri, 4 Feb 2005 15:13:02 -0800, "luv2travel"
> <luv2travel@.discussions.microsoft.com> wrote:
>
>
|||When you create a new access project, there are connection settings that need
to be configured before a connection can be made. How do I pass those
parameters to the connection wizard via my application? Once the connection
is made to the MSDE database, Access will then display all the tables in the
database. The user can then generate queries or reports as needed. Forms are
not necessary because if I were to create custom forms I would have created
it within my application instead of connecting to an Access database. The
reason I chose to use Access project is because I'd like the users to be able
to create queries and reports of their choosing. My job is just to dump the
data into the table and have them decide on the type of queries and reports
to generate. Is this more clear?
"Mary Chipman [MSFT]" wrote:
> You can use automation to launch Access and display forms and reports.
> I'm not a C++ programmer, so I couldn't tell you *how* to do that.
> However, I'm not sure I really understand what you are trying to do.
> The phrase, "display the contents of the database" covers a lot of
> ground. Perhaps you can be more specific.
> --Mary
> On Fri, 4 Feb 2005 15:13:02 -0800, "luv2travel"
> <luv2travel@.discussions.microsoft.com> wrote:
>
>
|||I understand now. However, your choice to use an Access project is a
bad one. Users will not be able to create and save local queries
(although they can save local reports). You would need to grant them
permissions to create views or stored procedures on the server, and
this you probably don't want to do for two reasons: (1) security and
(2) clogging the server with a lot of user objects. Unless a user is
connected as a sysadmin, they will own all their own objects and will
need to grant others permissions to use them, which you don't want to
get into. In your situation, I'd use an .mdb. You can programmatically
link tables at runtime using DAO, and if users create their own
queries, they are saved locally, not on SQL Server. HTH,
Mary
On Sun, 6 Feb 2005 23:03:08 -0800, "luv2travel"
<luv2travel@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>When you create a new access project, there are connection settings that need
>to be configured before a connection can be made. How do I pass those
>parameters to the connection wizard via my application? Once the connection
>is made to the MSDE database, Access will then display all the tables in the
>database. The user can then generate queries or reports as needed. Forms are
>not necessary because if I were to create custom forms I would have created
>it within my application instead of connecting to an Access database. The
>reason I chose to use Access project is because I'd like the users to be able
>to create queries and reports of their choosing. My job is just to dump the
>data into the table and have them decide on the type of queries and reports
>to generate. Is this more clear?
>"Mary Chipman [MSFT]" wrote:
user to be able to view the data via Access projects. In my application, is
it possible to configure the connection settings for an Access project to
connect to the MSDE database? For example, if I were to click the View button
in my application, can I have Access project connect to the database behind
the scenes and then display the contents of the database?
Thanks!
You can use automation to launch Access and display forms and reports.
I'm not a C++ programmer, so I couldn't tell you *how* to do that.
However, I'm not sure I really understand what you are trying to do.
The phrase, "display the contents of the database" covers a lot of
ground. Perhaps you can be more specific.
--Mary
On Fri, 4 Feb 2005 15:13:02 -0800, "luv2travel"
<luv2travel@.discussions.microsoft.com> wrote:
>I'm using Visual C++ to insert records into my MSDE database. I'd like the
>user to be able to view the data via Access projects. In my application, is
>it possible to configure the connection settings for an Access project to
>connect to the MSDE database? For example, if I were to click the View button
>in my application, can I have Access project connect to the database behind
>the scenes and then display the contents of the database?
>Thanks!
|||When you create a new access project that connects to an existing database
there are certain settings that the wizard asks of you before connection to
the database can be made. How do I pass those parameters from my application
to access project? Once the project has been created after the connections
settings have been configured, the user will have full access to the tables
within the database. The user can then create queries and reports as needed.
Does this explain things a little better?
"Mary Chipman [MSFT]" wrote:
> You can use automation to launch Access and display forms and reports.
> I'm not a C++ programmer, so I couldn't tell you *how* to do that.
> However, I'm not sure I really understand what you are trying to do.
> The phrase, "display the contents of the database" covers a lot of
> ground. Perhaps you can be more specific.
> --Mary
> On Fri, 4 Feb 2005 15:13:02 -0800, "luv2travel"
> <luv2travel@.discussions.microsoft.com> wrote:
>
>
|||When you create a new access project, there are connection settings that need
to be configured before a connection can be made. How do I pass those
parameters to the connection wizard via my application? Once the connection
is made to the MSDE database, Access will then display all the tables in the
database. The user can then generate queries or reports as needed. Forms are
not necessary because if I were to create custom forms I would have created
it within my application instead of connecting to an Access database. The
reason I chose to use Access project is because I'd like the users to be able
to create queries and reports of their choosing. My job is just to dump the
data into the table and have them decide on the type of queries and reports
to generate. Is this more clear?
"Mary Chipman [MSFT]" wrote:
> You can use automation to launch Access and display forms and reports.
> I'm not a C++ programmer, so I couldn't tell you *how* to do that.
> However, I'm not sure I really understand what you are trying to do.
> The phrase, "display the contents of the database" covers a lot of
> ground. Perhaps you can be more specific.
> --Mary
> On Fri, 4 Feb 2005 15:13:02 -0800, "luv2travel"
> <luv2travel@.discussions.microsoft.com> wrote:
>
>
|||I understand now. However, your choice to use an Access project is a
bad one. Users will not be able to create and save local queries
(although they can save local reports). You would need to grant them
permissions to create views or stored procedures on the server, and
this you probably don't want to do for two reasons: (1) security and
(2) clogging the server with a lot of user objects. Unless a user is
connected as a sysadmin, they will own all their own objects and will
need to grant others permissions to use them, which you don't want to
get into. In your situation, I'd use an .mdb. You can programmatically
link tables at runtime using DAO, and if users create their own
queries, they are saved locally, not on SQL Server. HTH,
Mary
On Sun, 6 Feb 2005 23:03:08 -0800, "luv2travel"
<luv2travel@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>When you create a new access project, there are connection settings that need
>to be configured before a connection can be made. How do I pass those
>parameters to the connection wizard via my application? Once the connection
>is made to the MSDE database, Access will then display all the tables in the
>database. The user can then generate queries or reports as needed. Forms are
>not necessary because if I were to create custom forms I would have created
>it within my application instead of connecting to an Access database. The
>reason I chose to use Access project is because I'd like the users to be able
>to create queries and reports of their choosing. My job is just to dump the
>data into the table and have them decide on the type of queries and reports
>to generate. Is this more clear?
>"Mary Chipman [MSFT]" wrote:
Saturday, February 25, 2012
Autoincrement record position in a view
Does anybody know the function or any other way in MS SQL Server 2000 or in MS Access or in MS FoxPro that I can get an increment record position in a view?
For example let's say that I have a table with only one field named persons. The table has three records person1, person2 and person3. What is the way in MS SQL Server 2000 or in MS Access or in MS FoxPro of retrieving the records in a view with an extra field named for example recno which will indicate the record autoincrement number in the view as it is below?
recno persons
1person1
2person2
3person3
Please help me
I will be very grateful if you also reply your answers also and to my email
Email: stavrinc@.hotmail.com
Thank you
Christos StavrinouWith FoxPro you can use the RECNO() function:
SELECT RECNO() AS recno, persons FROM myTable
This is not possible with SQL Server, and I am relatively sure it is not possible with Access.
Terri|||I should have said that this is possible with SQL Server only by inserting your result set into a #TEMP table which has an Identity column.view post 368541
Terri
Thursday, February 16, 2012
Auto update statistics
I've seen conflicting information about having auto-update statistics. On
view was that having this on would cause spikes in utilization whenever the
auto update kicks in during peak load time, and so this option should be
turned off and update statistics should be run via a job during off times.
The other view was that it didn't matter.
Any thoughts?
Thanks
Bob Castleman
SuccessWare SoftwareBob Castleman wrote:
> I've seen conflicting information about having auto-update
> statistics. On view was that having this on would cause spikes in
> utilization whenever the auto update kicks in during peak load time,
> and so this option should be turned off and update statistics should
> be run via a job during off times. The other view was that it didn't
> matter.
> Any thoughts?
> Thanks
> Bob Castleman
> SuccessWare Software
You're right that there is no clear concensus. Some customers do perform
statistics updates after hours. They may be doing this because the
database was around during the SQL 7 days or because the amount of data
loaded into tables during peak hours does not generall affect the
statistical distribution of data in those tables.
If you have tables that are effected by large loads during work hours
and are worried that queries that access those tables are likely to use
inefficient plans because of outdated statistics, then keep the option
on. Certainly, running a bunch of inefficient queries will put more
stress on the server than allowing SQL Server to update statistics as it
sees fit. OTOH, if your tables are unaffected by large changes during
the day, you can update statistics at night (daily, weekly, or monthly
as you see fit).
It all depends on your data and the queries hitting your data.
What is you situation?
David Gugick
Imceda Software
www.imceda.com|||Bob,
Yes, I come up against this a lot. I am a freelance consultant and I
hear both sides of the argument - the "correct" answer is that it
depends on your environment. If leaving auto-update stats on is
acceptable to the users -- ie they perceive no degradation in
performance -- then leave it on.
Unless your system is processing many transactions per second - say
10-100 then perhaps turn it off and schedule update stats manually in a
maintenance window.
Some places I go to have 24x7 shops where there simply isn't a
maintenance window, so they just leave auto-update stats on all the time
anyway. You need to base your decision on how it affects your users.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob Castleman wrote:
> I've seen conflicting information about having auto-update statistics. On
> view was that having this on would cause spikes in utilization whenever the
> auto update kicks in during peak load time, and so this option should be
> turned off and update statistics should be run via a job during off times.
> The other view was that it didn't matter.
> Any thoughts?
> Thanks
> Bob Castleman
> SuccessWare Software
>|||Our production environment hosts about 125 of our clients in a Citrix farm
against two Active/Passive clusters. Probably 90% of the load occurs between
8:00 AM and 6:00 PM and we have a defined maintenance window of 12:00 AM to
5:00 AM. No single client has huge processing needs, but the aggregate load
can get pretty heavy. We recently solved a problem that was sucking up
proccessor cycles and limited the number of clients on a database server. We
want to increase the number of clients per database server, so I am trying
to identify things I can do before hand that might help keep things under
control. We expect to triple or quadruple the number of clients we are
hosting over the next 24 to 36 months so I am trying to get everything as
stable as possible now instead of waiting for fires to start.
We are also looking at tools for helping us profile usage patterns and
things like that so we can start optimizing the queries and indexing. The
application was not designed for a hosted environment and query optimization
has been ad hoc in the past. It is highly likely that there are numerous
inefficient queries.
Thx,
Bob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uxFc8EX4EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Bob Castleman wrote:
>> I've seen conflicting information about having auto-update
>> statistics. On view was that having this on would cause spikes in
>> utilization whenever the auto update kicks in during peak load time,
>> and so this option should be turned off and update statistics should
>> be run via a job during off times. The other view was that it didn't
>> matter.
>> Any thoughts?
>> Thanks
>> Bob Castleman
>> SuccessWare Software
> You're right that there is no clear concensus. Some customers do perform
> statistics updates after hours. They may be doing this because the
> database was around during the SQL 7 days or because the amount of data
> loaded into tables during peak hours does not generall affect the
> statistical distribution of data in those tables.
> If you have tables that are effected by large loads during work hours and
> are worried that queries that access those tables are likely to use
> inefficient plans because of outdated statistics, then keep the option on.
> Certainly, running a bunch of inefficient queries will put more stress on
> the server than allowing SQL Server to update statistics as it sees fit.
> OTOH, if your tables are unaffected by large changes during the day, you
> can update statistics at night (daily, weekly, or monthly as you see fit).
> It all depends on your data and the queries hitting your data.
> What is you situation?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Our transaction rate runs 500-1000 per second. (see my response to Dave
Guigick for more info on our production environment)
It sounds like this is a peripheral issue, rather than one that will show
any significant change. I probably am better off digging deeper into
optimization of the queries and stuff.
Thx,
Bob
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:ejzUXsc4EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Bob,
> Yes, I come up against this a lot. I am a freelance consultant and I hear
> both sides of the argument - the "correct" answer is that it depends on
> your environment. If leaving auto-update stats on is acceptable to the
> users -- ie they perceive no degradation in performance -- then leave it
> on.
> Unless your system is processing many transactions per second - say 10-100
> then perhaps turn it off and schedule update stats manually in a
> maintenance window.
> Some places I go to have 24x7 shops where there simply isn't a maintenance
> window, so they just leave auto-update stats on all the time anyway. You
> need to base your decision on how it affects your users.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Bob Castleman wrote:
>> I've seen conflicting information about having auto-update statistics. On
>> view was that having this on would cause spikes in utilization whenever
>> the auto update kicks in during peak load time, and so this option should
>> be turned off and update statistics should be run via a job during off
>> times. The other view was that it didn't matter.
>> Any thoughts?
>> Thanks
>> Bob Castleman
>> SuccessWare Software|||Bob,
OK, that's quite healthy! :-)
If these transactions are DML statements then you may find that
auto-update stats has an effect on performance. This effect could be
positive or negative! NOT switching on auto-update stats may actually be
negative in such a high transaction environment. Is there any way you
can take a profiler trace and replay it into a test environment and
benchmarking your system with and without auto-update stats? You will
know for sure this way how it is going to affect you.
Obviously you will need to have similar spec hardware - not sure how
feasible this is. You may also wish to connect a typical user
application to this test environment while the trace is playing to see
how well it "feels".
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob Castleman wrote:
> Our transaction rate runs 500-1000 per second. (see my response to Dave
> Guigick for more info on our production environment)
> It sounds like this is a peripheral issue, rather than one that will show
> any significant change. I probably am better off digging deeper into
> optimization of the queries and stuff.|||Bob,
Is it possible for you to email me directly? I have a question for you but
don't know how to reach you.
Please remove the "nooospam" from my reply address.
Thanks
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:uTl8I7e4EHA.1260@.TK2MSFTNGP12.phx.gbl...
> Our production environment hosts about 125 of our clients in a Citrix farm
> against two Active/Passive clusters. Probably 90% of the load occurs
> between 8:00 AM and 6:00 PM and we have a defined maintenance window of
> 12:00 AM to 5:00 AM. No single client has huge processing needs, but the
> aggregate load can get pretty heavy. We recently solved a problem that was
> sucking up proccessor cycles and limited the number of clients on a
> database server. We want to increase the number of clients per database
> server, so I am trying to identify things I can do before hand that might
> help keep things under control. We expect to triple or quadruple the
> number of clients we are hosting over the next 24 to 36 months so I am
> trying to get everything as stable as possible now instead of waiting for
> fires to start.
> We are also looking at tools for helping us profile usage patterns and
> things like that so we can start optimizing the queries and indexing. The
> application was not designed for a hosted environment and query
> optimization has been ad hoc in the past. It is highly likely that there
> are numerous inefficient queries.
> Thx,
> Bob
>
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uxFc8EX4EHA.2012@.TK2MSFTNGP15.phx.gbl...
>> Bob Castleman wrote:
>> I've seen conflicting information about having auto-update
>> statistics. On view was that having this on would cause spikes in
>> utilization whenever the auto update kicks in during peak load time,
>> and so this option should be turned off and update statistics should
>> be run via a job during off times. The other view was that it didn't
>> matter.
>> Any thoughts?
>> Thanks
>> Bob Castleman
>> SuccessWare Software
>> You're right that there is no clear concensus. Some customers do perform
>> statistics updates after hours. They may be doing this because the
>> database was around during the SQL 7 days or because the amount of data
>> loaded into tables during peak hours does not generall affect the
>> statistical distribution of data in those tables.
>> If you have tables that are effected by large loads during work hours and
>> are worried that queries that access those tables are likely to use
>> inefficient plans because of outdated statistics, then keep the option
>> on. Certainly, running a bunch of inefficient queries will put more
>> stress on the server than allowing SQL Server to update statistics as it
>> sees fit. OTOH, if your tables are unaffected by large changes during the
>> day, you can update statistics at night (daily, weekly, or monthly as you
>> see fit).
>> It all depends on your data and the queries hitting your data.
>> What is you situation?
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||We are actually in the process of creating a test environment that includes
a mini Citrix farm and a clustered SQL setup for precisely this purpose.
It's still not complete. I'll probably put the auto-update issue on a task
list and try addressing it as you suggest. Turning it off just to see what
happens sounds too risky and it makes much more sense to work out a
reasonable test.
Thx,
Bob
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23U2Xyaf4EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Bob,
> OK, that's quite healthy! :-)
> If these transactions are DML statements then you may find that
> auto-update stats has an effect on performance. This effect could be
> positive or negative! NOT switching on auto-update stats may actually be
> negative in such a high transaction environment. Is there any way you can
> take a profiler trace and replay it into a test environment and
> benchmarking your system with and without auto-update stats? You will know
> for sure this way how it is going to affect you.
> Obviously you will need to have similar spec hardware - not sure how
> feasible this is. You may also wish to connect a typical user application
> to this test environment while the trace is playing to see how well it
> "feels".
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Bob Castleman wrote:
>> Our transaction rate runs 500-1000 per second. (see my response to Dave
>> Guigick for more info on our production environment)
>> It sounds like this is a peripheral issue, rather than one that will
>> show any significant change. I probably am better off digging deeper into
>> optimization of the queries and stuff.
view was that having this on would cause spikes in utilization whenever the
auto update kicks in during peak load time, and so this option should be
turned off and update statistics should be run via a job during off times.
The other view was that it didn't matter.
Any thoughts?
Thanks
Bob Castleman
SuccessWare SoftwareBob Castleman wrote:
> I've seen conflicting information about having auto-update
> statistics. On view was that having this on would cause spikes in
> utilization whenever the auto update kicks in during peak load time,
> and so this option should be turned off and update statistics should
> be run via a job during off times. The other view was that it didn't
> matter.
> Any thoughts?
> Thanks
> Bob Castleman
> SuccessWare Software
You're right that there is no clear concensus. Some customers do perform
statistics updates after hours. They may be doing this because the
database was around during the SQL 7 days or because the amount of data
loaded into tables during peak hours does not generall affect the
statistical distribution of data in those tables.
If you have tables that are effected by large loads during work hours
and are worried that queries that access those tables are likely to use
inefficient plans because of outdated statistics, then keep the option
on. Certainly, running a bunch of inefficient queries will put more
stress on the server than allowing SQL Server to update statistics as it
sees fit. OTOH, if your tables are unaffected by large changes during
the day, you can update statistics at night (daily, weekly, or monthly
as you see fit).
It all depends on your data and the queries hitting your data.
What is you situation?
David Gugick
Imceda Software
www.imceda.com|||Bob,
Yes, I come up against this a lot. I am a freelance consultant and I
hear both sides of the argument - the "correct" answer is that it
depends on your environment. If leaving auto-update stats on is
acceptable to the users -- ie they perceive no degradation in
performance -- then leave it on.
Unless your system is processing many transactions per second - say
10-100 then perhaps turn it off and schedule update stats manually in a
maintenance window.
Some places I go to have 24x7 shops where there simply isn't a
maintenance window, so they just leave auto-update stats on all the time
anyway. You need to base your decision on how it affects your users.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob Castleman wrote:
> I've seen conflicting information about having auto-update statistics. On
> view was that having this on would cause spikes in utilization whenever the
> auto update kicks in during peak load time, and so this option should be
> turned off and update statistics should be run via a job during off times.
> The other view was that it didn't matter.
> Any thoughts?
> Thanks
> Bob Castleman
> SuccessWare Software
>|||Our production environment hosts about 125 of our clients in a Citrix farm
against two Active/Passive clusters. Probably 90% of the load occurs between
8:00 AM and 6:00 PM and we have a defined maintenance window of 12:00 AM to
5:00 AM. No single client has huge processing needs, but the aggregate load
can get pretty heavy. We recently solved a problem that was sucking up
proccessor cycles and limited the number of clients on a database server. We
want to increase the number of clients per database server, so I am trying
to identify things I can do before hand that might help keep things under
control. We expect to triple or quadruple the number of clients we are
hosting over the next 24 to 36 months so I am trying to get everything as
stable as possible now instead of waiting for fires to start.
We are also looking at tools for helping us profile usage patterns and
things like that so we can start optimizing the queries and indexing. The
application was not designed for a hosted environment and query optimization
has been ad hoc in the past. It is highly likely that there are numerous
inefficient queries.
Thx,
Bob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uxFc8EX4EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Bob Castleman wrote:
>> I've seen conflicting information about having auto-update
>> statistics. On view was that having this on would cause spikes in
>> utilization whenever the auto update kicks in during peak load time,
>> and so this option should be turned off and update statistics should
>> be run via a job during off times. The other view was that it didn't
>> matter.
>> Any thoughts?
>> Thanks
>> Bob Castleman
>> SuccessWare Software
> You're right that there is no clear concensus. Some customers do perform
> statistics updates after hours. They may be doing this because the
> database was around during the SQL 7 days or because the amount of data
> loaded into tables during peak hours does not generall affect the
> statistical distribution of data in those tables.
> If you have tables that are effected by large loads during work hours and
> are worried that queries that access those tables are likely to use
> inefficient plans because of outdated statistics, then keep the option on.
> Certainly, running a bunch of inefficient queries will put more stress on
> the server than allowing SQL Server to update statistics as it sees fit.
> OTOH, if your tables are unaffected by large changes during the day, you
> can update statistics at night (daily, weekly, or monthly as you see fit).
> It all depends on your data and the queries hitting your data.
> What is you situation?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Our transaction rate runs 500-1000 per second. (see my response to Dave
Guigick for more info on our production environment)
It sounds like this is a peripheral issue, rather than one that will show
any significant change. I probably am better off digging deeper into
optimization of the queries and stuff.
Thx,
Bob
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:ejzUXsc4EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Bob,
> Yes, I come up against this a lot. I am a freelance consultant and I hear
> both sides of the argument - the "correct" answer is that it depends on
> your environment. If leaving auto-update stats on is acceptable to the
> users -- ie they perceive no degradation in performance -- then leave it
> on.
> Unless your system is processing many transactions per second - say 10-100
> then perhaps turn it off and schedule update stats manually in a
> maintenance window.
> Some places I go to have 24x7 shops where there simply isn't a maintenance
> window, so they just leave auto-update stats on all the time anyway. You
> need to base your decision on how it affects your users.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Bob Castleman wrote:
>> I've seen conflicting information about having auto-update statistics. On
>> view was that having this on would cause spikes in utilization whenever
>> the auto update kicks in during peak load time, and so this option should
>> be turned off and update statistics should be run via a job during off
>> times. The other view was that it didn't matter.
>> Any thoughts?
>> Thanks
>> Bob Castleman
>> SuccessWare Software|||Bob,
OK, that's quite healthy! :-)
If these transactions are DML statements then you may find that
auto-update stats has an effect on performance. This effect could be
positive or negative! NOT switching on auto-update stats may actually be
negative in such a high transaction environment. Is there any way you
can take a profiler trace and replay it into a test environment and
benchmarking your system with and without auto-update stats? You will
know for sure this way how it is going to affect you.
Obviously you will need to have similar spec hardware - not sure how
feasible this is. You may also wish to connect a typical user
application to this test environment while the trace is playing to see
how well it "feels".
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob Castleman wrote:
> Our transaction rate runs 500-1000 per second. (see my response to Dave
> Guigick for more info on our production environment)
> It sounds like this is a peripheral issue, rather than one that will show
> any significant change. I probably am better off digging deeper into
> optimization of the queries and stuff.|||Bob,
Is it possible for you to email me directly? I have a question for you but
don't know how to reach you.
Please remove the "nooospam" from my reply address.
Thanks
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:uTl8I7e4EHA.1260@.TK2MSFTNGP12.phx.gbl...
> Our production environment hosts about 125 of our clients in a Citrix farm
> against two Active/Passive clusters. Probably 90% of the load occurs
> between 8:00 AM and 6:00 PM and we have a defined maintenance window of
> 12:00 AM to 5:00 AM. No single client has huge processing needs, but the
> aggregate load can get pretty heavy. We recently solved a problem that was
> sucking up proccessor cycles and limited the number of clients on a
> database server. We want to increase the number of clients per database
> server, so I am trying to identify things I can do before hand that might
> help keep things under control. We expect to triple or quadruple the
> number of clients we are hosting over the next 24 to 36 months so I am
> trying to get everything as stable as possible now instead of waiting for
> fires to start.
> We are also looking at tools for helping us profile usage patterns and
> things like that so we can start optimizing the queries and indexing. The
> application was not designed for a hosted environment and query
> optimization has been ad hoc in the past. It is highly likely that there
> are numerous inefficient queries.
> Thx,
> Bob
>
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uxFc8EX4EHA.2012@.TK2MSFTNGP15.phx.gbl...
>> Bob Castleman wrote:
>> I've seen conflicting information about having auto-update
>> statistics. On view was that having this on would cause spikes in
>> utilization whenever the auto update kicks in during peak load time,
>> and so this option should be turned off and update statistics should
>> be run via a job during off times. The other view was that it didn't
>> matter.
>> Any thoughts?
>> Thanks
>> Bob Castleman
>> SuccessWare Software
>> You're right that there is no clear concensus. Some customers do perform
>> statistics updates after hours. They may be doing this because the
>> database was around during the SQL 7 days or because the amount of data
>> loaded into tables during peak hours does not generall affect the
>> statistical distribution of data in those tables.
>> If you have tables that are effected by large loads during work hours and
>> are worried that queries that access those tables are likely to use
>> inefficient plans because of outdated statistics, then keep the option
>> on. Certainly, running a bunch of inefficient queries will put more
>> stress on the server than allowing SQL Server to update statistics as it
>> sees fit. OTOH, if your tables are unaffected by large changes during the
>> day, you can update statistics at night (daily, weekly, or monthly as you
>> see fit).
>> It all depends on your data and the queries hitting your data.
>> What is you situation?
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||We are actually in the process of creating a test environment that includes
a mini Citrix farm and a clustered SQL setup for precisely this purpose.
It's still not complete. I'll probably put the auto-update issue on a task
list and try addressing it as you suggest. Turning it off just to see what
happens sounds too risky and it makes much more sense to work out a
reasonable test.
Thx,
Bob
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23U2Xyaf4EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Bob,
> OK, that's quite healthy! :-)
> If these transactions are DML statements then you may find that
> auto-update stats has an effect on performance. This effect could be
> positive or negative! NOT switching on auto-update stats may actually be
> negative in such a high transaction environment. Is there any way you can
> take a profiler trace and replay it into a test environment and
> benchmarking your system with and without auto-update stats? You will know
> for sure this way how it is going to affect you.
> Obviously you will need to have similar spec hardware - not sure how
> feasible this is. You may also wish to connect a typical user application
> to this test environment while the trace is playing to see how well it
> "feels".
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Bob Castleman wrote:
>> Our transaction rate runs 500-1000 per second. (see my response to Dave
>> Guigick for more info on our production environment)
>> It sounds like this is a peripheral issue, rather than one that will
>> show any significant change. I probably am better off digging deeper into
>> optimization of the queries and stuff.
Monday, February 13, 2012
auto numbering
Hello,
For analyses and reporting I would like to add a 'ranking' to a
table/view.
Example:
Using the 'order by desc' clause in query I get a list of Customers
ordered by Turnover (descending). I would like to add that ranking
numbers (same as recordnumbers) in the query. I would like to have the
following result:
Cust_nr Cust_Name Turnover_2004 Ranking
002234 Bayer 139.000 1
003456 Rentokill 123.456 2
001231 Air France 105.000 3
etc.
When the 'ranking' is part of the query/table I can use this ranking in
an other query.
Important: This questions is not about making an (empty) table structure
for filling in by an application and generating a new unique number each
time a record is added.
I hope you can help me.
Thanks,
Hans
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!On 10 Mar 2005 03:14:43 -0600, Hans de Korte wrote:
>Hello,
>For analyses and reporting I would like to add a 'ranking' to a
>table/view.
>Example:
>Using the 'order by desc' clause in query I get a list of Customers
>ordered by Turnover (descending). I would like to add that ranking
>numbers (same as recordnumbers) in the query. I would like to have the
>following result:
>Cust_nr Cust_Name Turnover_2004 Ranking
>002234 Bayer 139.000 1
>003456 Rentokill 123.456 2
>001231 Air France 105.000 3
>etc.
>When the 'ranking' is part of the query/table I can use this ranking in
>an other query.
(snip)
Hi Hans,
I'd recommend against storing it in the table, as you'll need to update
all rankings each time some data in the table changes. (Of course, if
you have a static database, where data won't change but that you have to
run extensive reports off, things change).
In a live database, I'd define a view to hold the ranking:
CREATE VIEW CustomersRanked
AS
SELECT Cust_nr, Cust_Name, Turnover_2004,
(SELECT COUNT(*)
FROM Customers AS b
WHERE b.Turnover_2004 > a.Turnover_2004) + 1 AS Ranking
FROM Customers AS a
ORDER BY Turnover_2004 DESC
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, the soluttion is working excellent!!
Regards,
Hans
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Sunday, February 12, 2012
Auto Incrent Attribute Sql Server 2005
In our application we use the ADODB.Recordset.
In SQL server 2000
If there was a view that joined 2 tables and I accessed the view the 2 ID fields in the view would still have the AutoIncrement attribute still set to true so that I knew those were Identity fields.
In SQL server 2005
I dont' know why but if you reference a View that has Identiy AutoInc fields in ADO it doesn't keep those properties.
Also for whatever reason we Set the ID field to 0 to let ourselves know its a new Record. SQL 2000 let it happen and assumed it to be null where as By Setting the ID to 0 in SQL 2005 causes it to blow up on me.
Is there some sort of setting in SQL that can make SQL 2005 work like SQL 2000 in these two instances...
CREATE TABLE [dbo].[table1](
[field1] [int] IDENTITY(1,1) NOT NULL,
[field2] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[field3] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
[field1] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
above example sql script create table with auto incrent. field (field1)
note: IDENTITY(1,1) keyword.
argument of IDENTITY indicate start and inc. value respiectivey.
Auto Incrent Attribute SQL Server 2000 vs Sql Server 2005
In our application we use the ADODB.Recordset.
In SQL server 2000
If there was a view that joined 2 tables and I accessed the view the 2 ID fields in the view would still have the AutoIncrement attribute still set to true so that I knew those were Identity fields.
In SQL server 2005
I dont' know why but if you reference a View that has Identiy AutoInc fields in ADO it doesn't keep those properties.
Also for whatever reason we Set the ID field to 0 to let ourselves know its a new Record. SQL 2000 let it happen and assumed it to be null where as By Setting the ID to 0 in SQL 2005 causes it to blow up on me.
Is there some sort of setting in SQL that can make SQL 2005 work like SQL 2000 in these two instances...
CREATE TABLE [dbo].[table1](
[field1] [int] IDENTITY(1,1) NOT NULL,
[field2] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[field3] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
[field1] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
above example sql script create table with auto incrent. field (field1)
note: IDENTITY(1,1) keyword.
argument of IDENTITY indicate start and inc. value respiectivey.
Friday, February 10, 2012
auto generated indexes
when viewing a database in the taskpad view from
enterprise manager, i notice there are a lot of indexes on
tables that have been automatically generated. they are
named like _WA_Sys_TransactionID_479C827A (where
TransactionID is a column in the table). Does someone
know where they come from and what they are used for?
There are more than one on some tables.
ThanksMaria
These are not indexes, they are just statistics on columns that are
auto-generated when the optimizer is trying to come up with a plan involving
the columns. Knowing the data distribution in the column can help the
optimizer come up with a better plan. By default, they are created on any
unindexed column used in a query. This is controlled by a database option
'auto create statistics'.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Maria" <mariac@.rcspf.co.za> wrote in message
news:2334101c38c21$f4de13b0$a601280a@.phx.gbl...
> when viewing a database in the taskpad view from
> enterprise manager, i notice there are a lot of indexes on
> tables that have been automatically generated. they are
> named like _WA_Sys_TransactionID_479C827A (where
> TransactionID is a column in the table). Does someone
> know where they come from and what they are used for?
> There are more than one on some tables.
> Thanks
enterprise manager, i notice there are a lot of indexes on
tables that have been automatically generated. they are
named like _WA_Sys_TransactionID_479C827A (where
TransactionID is a column in the table). Does someone
know where they come from and what they are used for?
There are more than one on some tables.
ThanksMaria
These are not indexes, they are just statistics on columns that are
auto-generated when the optimizer is trying to come up with a plan involving
the columns. Knowing the data distribution in the column can help the
optimizer come up with a better plan. By default, they are created on any
unindexed column used in a query. This is controlled by a database option
'auto create statistics'.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Maria" <mariac@.rcspf.co.za> wrote in message
news:2334101c38c21$f4de13b0$a601280a@.phx.gbl...
> when viewing a database in the taskpad view from
> enterprise manager, i notice there are a lot of indexes on
> tables that have been automatically generated. they are
> named like _WA_Sys_TransactionID_479C827A (where
> TransactionID is a column in the table). Does someone
> know where they come from and what they are used for?
> There are more than one on some tables.
> Thanks
Auto generate report without clicking on the "View Report"
Is there a way to automatically generate the report when the users
changes the parameter selection - without clicking on the "View
Report" button?
ThxFor what it's worth, you can just press the enter key - and if all required
parameters are filled, the report will refresh.
Not quite the same thing - but you need to do something to indicate that you
have finished in a cell.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0407161434.4bb91320@.posting.google.com...
> Is there a way to automatically generate the report when the users
> changes the parameter selection - without clicking on the "View
> Report" button?
> Thx
changes the parameter selection - without clicking on the "View
Report" button?
ThxFor what it's worth, you can just press the enter key - and if all required
parameters are filled, the report will refresh.
Not quite the same thing - but you need to do something to indicate that you
have finished in a cell.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0407161434.4bb91320@.posting.google.com...
> Is there a way to automatically generate the report when the users
> changes the parameter selection - without clicking on the "View
> Report" button?
> Thx
Subscribe to:
Posts (Atom)