Attach remote DB file: March 2012

Thursday, March 29, 2012

auto format of stored procedures?

hi,
i have many stored procedures, and i'm currently working on one with many
levels of nested IF's. it came from someone else, and i'm modifying it.
So...i'd love a utility, that i've seen in most other dev environments, that
will 'pretty print', or automatically format all the levels of a stored
procedure.
any one know of such a thing? i don't find much when googling!
thanks,
fred
You could look at SQLEdit from www.ApexSQL.com, and SSMS for SQL 2005 has
what is called 'Smart formatting' -though many may quibble about how 'smart'
it may be...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Freddy" <frosenbergNOSpam@.globaltouchtelecom.com> wrote in message
news:%23eICHNO4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> hi,
> i have many stored procedures, and i'm currently working on one with many
> levels of nested IF's. it came from someone else, and i'm modifying it.
> So...i'd love a utility, that i've seen in most other dev environments,
> that will 'pretty print', or automatically format all the levels of a
> stored procedure.
> any one know of such a thing? i don't find much when googling!
> thanks,
> fred
>
|||thanks arnie, i'll take a look!
fred
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eQDB%23CP4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> You could look at SQLEdit from www.ApexSQL.com, and SSMS for SQL 2005 has
> what is called 'Smart formatting' -though many may quibble about how
> 'smart' it may be...
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Freddy" <frosenbergNOSpam@.globaltouchtelecom.com> wrote in message
> news:%23eICHNO4GHA.1848@.TK2MSFTNGP06.phx.gbl...
>
sql

Auto filling

Hi,
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIAYou could write a simple UPDATE command in Query Analyzer, something like
this:
UPDATE myTable
SET newColumn = 'EN'
If you exclude the WHERE clause, all rows will be affected.
HTH,
Andrés
"Peter Osawa" <posawa@.sun.es> wrote in message
news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
>|||Thanks
King regards
Andres Taylor wrote:
> You could write a simple UPDATE command in Query Analyzer, something like
> this:
> UPDATE myTable
> SET newColumn = 'EN'
> If you exclude the WHERE clause, all rows will be affected.
> HTH,
> Andrés
> "Peter Osawa" <posawa@.sun.es> wrote in message
> news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
>>Hi,
>>I'm new to MSSQL world and I have a little question:
>>I have just added a new field to a table containing some 3000 rows...
>>The new field has the same value for all the existent rows...
>>How can I automatically update all rows ?
>>I would like to put "EN" string in all rows... What tool do I need to
>>use ? (SQL query analyzer ?)
>>Any site, if this is to complex to explain here ?
>>TIA
>
>

Auto filling

Hi,
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIA
You could write a simple UPDATE command in Query Analyzer, something like
this:
UPDATE myTable
SET newColumn = 'EN'
If you exclude the WHERE clause, all rows will be affected.
HTH,
Andrs
"Peter Osawa" <posawa@.sun.es> wrote in message
news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
>
|||Thanks
King regards
Andres Taylor wrote:
> You could write a simple UPDATE command in Query Analyzer, something like
> this:
> UPDATE myTable
> SET newColumn = 'EN'
> If you exclude the WHERE clause, all rows will be affected.
> HTH,
> Andrs
> "Peter Osawa" <posawa@.sun.es> wrote in message
> news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
>
>
sql

Auto filling

Hi,
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIAYou could write a simple UPDATE command in Query Analyzer, something like
this:
UPDATE myTable
SET newColumn = 'EN'
If you exclude the WHERE clause, all rows will be affected.
HTH,
Andrs
"Peter Osawa" <posawa@.sun.es> wrote in message
news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
>|||Thanks
King regards
Andres Taylor wrote:
> You could write a simple UPDATE command in Query Analyzer, something like
> this:
> UPDATE myTable
> SET newColumn = 'EN'
> If you exclude the WHERE clause, all rows will be affected.
> HTH,
> Andrs
> "Peter Osawa" <posawa@.sun.es> wrote in message
> news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
>
>
>

Auto fill in colums trough foreign key relationship

Hi,
I have a table users where there is a user_id and an department column.
Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,
Can someone help me with this?
Cheers WimYou want help creating a denormalized database?

[sigh...]

If you absolutely need to do this, you can accomplish it through an INSERT trigger on your KRS table.

Why are you duplicating data like this?|||You want help creating a denormalized database?

[sigh...]

If you absolutely need to do this, you can accomplish it through an INSERT trigger on your KRS table.

Why are you duplicating data like this?I keep trying to shoot myself it the foot, but the rifle wobbles when I pull the trigger. Can one of you guys hold it for me?

I actually thought about trying to formulate a reply to this question, but nothing I wrote seemed fit to post. I'm thinking that a VIEW would make life a lot easier in the long run, what do you think?

-PatP

Auto fill collumn

I have an existing collumn in a table that contains a 5 digit number used as
an employee id field.(datatype nvarchar) I would like to auto fill the rows
that are '00000' or Null with an number beginning with X0001 and incrementin
g
by 1 thereafter.( I want the X as a prefix). Any Ideas'
Thanks, RobOn Mon, 31 Jan 2005 13:37:23 GMT, Robb Mann wrote:

> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the ro
ws
>that are '00000' or Null with an number beginning with X0001 and incrementi
ng
>by 1 thereafter.( I want the X as a prefix). Any Ideas'
>Thanks, Rob
Hi Rob,
Define: "beginning" and "thereafter". Remember that tables are UNordered
sets of data, by definition. There is no "first" or "beginning" row. You
will have to define some ranking to base your numbers on.
Assuming you want to use the column date_entered to base the ranking, and
assuming you have no diuplicates in the date_entered, you could use the
following:
UPDATE MyTable
SET employee_id = 'X' + LEFT ('0000' +
CAST((SELECT COUNT(*)
FROM MyTable AS a
WHERE a.date_entered <= MyTable.date_entered
AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
WHERE COALESCE (employee_id, '00000') = '00000'
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What is the primary key of the table? SSN? Payroll number? Are you
saying that the table doesn't have a key right now? If you just want an
arbitrary surrogate key then use IDENTITY. If you requirements are more
complex then we'll need some more information: DDL, sample data,
required end result.
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||If your table does not have duplicated employees by first name and last name
,
then you can use this approach (if not, use also the department to calculate
the rank):
use northwind
go
create table t (
empid char(5) null,
fname varchar(25) not null,
lname varchar(25) not null
)
insert into t values ('00000', 'a', 'b')
insert into t values ('00000', 'c', 'd')
insert into t values (null, 'e', 'f')
insert into t values ('00001', 'v', 'w')
select * from t order by fname, lname
update
e
set
e.empid = 'x' + right('000' + ltrim(rank), 4)
from
t as e
inner join
(
select
a.fname,
a.lname,
count(*) as rank
from
t as a
inner join
t as b
on b.fname + b.lname <= a.fname + a.lname
and (a.empid is null or a.empid = '00000')
and (b.empid is null or b.empid = '00000')
group by
a.fname,
a.lname
) as f
on e.fname = f.fname and e.lname = f.lname
select * from t order by fname, lname
drop table t
go
AMB
"Robb Mann" wrote:

> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the r
ows
> that are '00000' or Null with an number beginning with X0001 and increment
ing
> by 1 thereafter.( I want the X as a prefix). Any Ideas'
> Thanks, Rob
>|||On Mon, 31 Jan 2005 14:54:43 +0100, Hugo Kornelis wrote:

>UPDATE MyTable
>SET employee_id = 'X' + LEFT ('0000' +
> CAST((SELECT COUNT(*)
> FROM MyTable AS a
> WHERE a.date_entered <= MyTable.date_entered
> AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
>WHERE COALESCE (employee_id, '00000') = '00000'
(snip)
Correction: I should have used RIGHT, not LEFT.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Auto field calculation

Hi,
I would like to calculate field based on the entry in another in SQL server
2000 i.e.; field3 = field2 * field1
field1 field2 field3
10 2.5 25.00( =sum(field1*field2))
How do I go about this in the SQL DB itself, can it be done within field3?
Regards
SimonLook up "computed columns" in Books Online.
It's simple:
create table <table name>
(
Column1 <data type>
,Column2 <data type>
,Column3 as Column1 * Column2
)
go
or:
alter table <table name>
add Column3 as Column1 * Column2
go
ML
http://milambda.blogspot.com/

Auto Failover Issues

I have been looking over the forum, and also other sites for information about my problem but cant seem to find what im looking for so I have decided to make a quick post.

Presently, I have 3 computers setup for mirroring. One is the principal, another the mirror and the third is the witness.

Im using SQL 2005 Enterprise Edition on all three, and creation of the mirror using SQL Studio works without problems. Manual failover (using the button in SQL Studio) also works fine.

When I start the "Mirror Monitor" application, and connect to the two DB servers the status is all green, they are connected to each other and the witness server can be contacted.

Now here is my issue; when its time for an automatic failover situation (pulling the plug from the current principal for example) it detects the fault and changes the status of the mirror to "principal" BUT keeps the other status to "Disconnected" (Principal, Disconnected) so no active connections to the failed over database will work.

When running the mirror monitor during the failed fail-over attempt the still online database reports that the connection to the witness is still present but the mirror is offline.

There are a few error notices in the logs, but from what I can tell they are normal for whats happening. But the codes would be 1479 (cant talk to the database; this would be the one we took offline) and 1474 (network name is no longer available; once again as it was taken offline). Note that these errors are also in the witness server logs, as I believe one should expect.

Any help or assistance with this problem would be appreciated.

Thanks,

Sean

Do you see any network related issues or warnings between the principal and mirror servers?|||Hate to state the obvious, but you don't mention that you re-connect server 1 in your scenario. What you are describing is exactly what I would expect to happen.sql

Auto export to Excel

Is there a way to export directly to an excel .csv file when the report is run without having to preview the report first and then click the export icon?

I want to be able to schedule a report to run and have it go directly to an excel .csv file.

You can use the file delivery subscription. This allows you to automaticly create a csv file at a UNC path.

See http://msdn2.microsoft.com/en-us/library/ms157386.aspx for more information.

|||You can specify the export format using the URL access syntax. Have a look in the BOL, there are some samples for that listed.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I cannot get to the property tab. It's greyed out. Only the location shows.

|||Alright I found it. It's all done on the server side. I was on the client side.

Auto export to Excel

Is there a way to export directly to an excel .csv file when the report is run without having to preview the report first and then click the export icon?

I want to be able to schedule a report to run and have it go directly to an excel .csv file.

You can use the file delivery subscription. This allows you to automaticly create a csv file at a UNC path.

See http://msdn2.microsoft.com/en-us/library/ms157386.aspx for more information.

|||You can specify the export format using the URL access syntax. Have a look in the BOL, there are some samples for that listed.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I cannot get to the property tab. It's greyed out. Only the location shows.

|||Alright I found it. It's all done on the server side. I was on the client side.

auto expanding Temp DB to fit large requests

I have run into a glitch in SQL 2000's ability to automatically expand the temp db when temp tables become very large.

In our business it is sometimes required that I alter a customer's configuration data without modifying any of their transaction data. This requires a rather complex procedure that creates a script of insert/update/delete statements that, when run on a customer's database, modifies their configuration to a replica of our in-house test environment.

While creating this script, sometimes a few lines are dropped. The real problem is that we have no error or indication that the script had dropped lines until we attempt to run it (which is usually on-site in a live environment.) Our solution is to manually increase the size of the temp db and it's transaction log. After we do this, the script is always created correctly.

This appears to be a bug in the ability for the temp db to auto expand. Is this fixed in SQL 2005?

Various tempdb defects have been fixed in SQL Server 2005. In addition there is a new feature allowing the "automatic space growth" without zeroing pages (makes auto growth much faster).
Without going into more details I expect that your problem will be fixed
SQL Server CTP15.
Please install the CTP15 release and test it. In addition I suggest to contact
MS-CSS (SQL Server 2000 Customer Service) and report your problem for SQL Server 2000

Please let me know the test results for SQL Server 2005 CTP15
Thanks
Mirek

Tuesday, March 27, 2012

Auto e-mails

Hello,

I have a SQL server though a hosting company and I am trying to send autoemails using xp_sendmail. The permissions were set and I used the following command to test it.

EXEC master.dbo.xp_sendmail
@.recipients='tracey@.yahoo.com',@.subject='test',@.me ssage='testing
sql stored procedure'

It gave me a message saying "Mail sent" but there none in my e-mail box.

How do I set yp the SQL Mail server, right? Please help. I don't know what is happening.

Thanks,
TraceyHave you checked whether the Mail Service has been turned on and the machine has MS Outlook installed(And it has to work)?|||And an Outlook need to have mail configured using the same account the SQL Server Agent runs as.|||Insead of all that, you could use CDO to send mail, which eliminates the need to have Outlook installed.

Check out this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_cdo_for_nts_library.asp

and this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_session_object_cdonts_library_.asp

Auto Email Statistics from DB Table

quote:

> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>

|||Create a job, in the job do xp_sendmail (see BOL for detail), in the next
job step clear the table.
hth
Quentin
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...

quote:

> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>

|||sincere regards for the inputs.
Chip

quote:

>--Original Message--
>Create a job, in the job do xp_sendmail (see BOL for

detail), in the next

quote:

>job step clear the table.
>hth
>Quentin
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in

message

quote:

>news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
daily[QUOTE]
appreciated.[QUOTE]
>
>.
>

|||In case you don't have xp_sendmail configured (can be a real pain to setup),
you might want to check out xp_smtp_sendmail from www.sqldev.net.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:131b01c3df78$96d273b0$a501280a@.phx.gbl...[QUOTE]
> sincere regards for the inputs.
> Chip
> detail), in the next
> message
> daily
> appreciated.|||Hi Karaszi,
Thank you very much for the resource. You saved me for the
time being.
Regards
Chip

quote:

>--Original Message--
>In case you don't have xp_sendmail configured (can be a

real pain to setup),

quote:

>you might want to check out xp_smtp_sendmail from

www.sqldev.net.

quote:

>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in

message

quote:

>news:131b01c3df78$96d273b0$a501280a@.phx.gbl...
various[QUOTE]
get[QUOTE]
does[QUOTE]
soon[QUOTE]
>
>.
>

sql

Auto Email Statistics from DB Table

Hi,
I have a user database 'MyDatabase'. There are various
Tables in this db. After executing a complex query I get
some statistics , which I have to send to my boss on daily
basis. I am failing to send statistics manually by
preparing it as email most of the times coz of other
prioity issues. I am looking for a automatic mailing
solution with SQL Server Machine ( the machine also does
have MS SMTP). I have the prototype in mind...
1. I need to get the data into a temporary table as soon
as the clock moves to 12:00 AM
2. Do Email Operations
3. Clear the Contents of the table
I am not at all sure with what has to be done
programatically. Any guidance will be highly appreciated.
Sincere Regards
ChipYou could use SQL Jobs to do these along with the mailing.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>|||Create a job, in the job do xp_sendmail (see BOL for detail), in the next
job step clear the table.
hth
Quentin
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>|||sincere regards for the inputs.
Chip
>--Original Message--
>Create a job, in the job do xp_sendmail (see BOL for
detail), in the next
>job step clear the table.
>hth
>Quentin
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
>> Hi,
>> I have a user database 'MyDatabase'. There are various
>> Tables in this db. After executing a complex query I get
>> some statistics , which I have to send to my boss on
daily
>> basis. I am failing to send statistics manually by
>> preparing it as email most of the times coz of other
>> prioity issues. I am looking for a automatic mailing
>> solution with SQL Server Machine ( the machine also does
>> have MS SMTP). I have the prototype in mind...
>> 1. I need to get the data into a temporary table as soon
>> as the clock moves to 12:00 AM
>> 2. Do Email Operations
>> 3. Clear the Contents of the table
>> I am not at all sure with what has to be done
>> programatically. Any guidance will be highly
appreciated.
>> Sincere Regards
>> Chip
>
>.
>|||In case you don't have xp_sendmail configured (can be a real pain to setup),
you might want to check out xp_smtp_sendmail from www.sqldev.net.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:131b01c3df78$96d273b0$a501280a@.phx.gbl...
> sincere regards for the inputs.
> Chip
> >--Original Message--
> >Create a job, in the job do xp_sendmail (see BOL for
> detail), in the next
> >job step clear the table.
> >
> >hth
> >
> >Quentin
> >
> >
> >"Chip" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
> >> Hi,
> >>
> >> I have a user database 'MyDatabase'. There are various
> >> Tables in this db. After executing a complex query I get
> >> some statistics , which I have to send to my boss on
> daily
> >> basis. I am failing to send statistics manually by
> >> preparing it as email most of the times coz of other
> >> prioity issues. I am looking for a automatic mailing
> >> solution with SQL Server Machine ( the machine also does
> >> have MS SMTP). I have the prototype in mind...
> >>
> >> 1. I need to get the data into a temporary table as soon
> >> as the clock moves to 12:00 AM
> >>
> >> 2. Do Email Operations
> >> 3. Clear the Contents of the table
> >>
> >> I am not at all sure with what has to be done
> >> programatically. Any guidance will be highly
> appreciated.
> >>
> >> Sincere Regards
> >>
> >> Chip
> >>
> >
> >
> >.
> >|||Hi Karaszi,
Thank you very much for the resource. You saved me for the
time being.
Regards
Chip
>--Original Message--
>In case you don't have xp_sendmail configured (can be a
real pain to setup),
>you might want to check out xp_smtp_sendmail from
www.sqldev.net.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:131b01c3df78$96d273b0$a501280a@.phx.gbl...
>> sincere regards for the inputs.
>> Chip
>> >--Original Message--
>> >Create a job, in the job do xp_sendmail (see BOL for
>> detail), in the next
>> >job step clear the table.
>> >
>> >hth
>> >
>> >Quentin
>> >
>> >
>> >"Chip" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I have a user database 'MyDatabase'. There are
various
>> >> Tables in this db. After executing a complex query I
get
>> >> some statistics , which I have to send to my boss on
>> daily
>> >> basis. I am failing to send statistics manually by
>> >> preparing it as email most of the times coz of other
>> >> prioity issues. I am looking for a automatic mailing
>> >> solution with SQL Server Machine ( the machine also
does
>> >> have MS SMTP). I have the prototype in mind...
>> >>
>> >> 1. I need to get the data into a temporary table as
soon
>> >> as the clock moves to 12:00 AM
>> >>
>> >> 2. Do Email Operations
>> >> 3. Clear the Contents of the table
>> >>
>> >> I am not at all sure with what has to be done
>> >> programatically. Any guidance will be highly
>> appreciated.
>> >>
>> >> Sincere Regards
>> >>
>> >> Chip
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

Auto email

Hi All,
I want to write a console application to send email. There is a Date field in the SQL Server and I need to send email 2 weeks before that date.I have no idea how to write a console application and make it work.Does anybody have code for this? If so please post it.
Thanks a lot,
Kumar.Easier might be just to create a stored procedure, and then schedule that via DTS on the SQL Server itself.Addition: You can use SQL Mail and do all mailing inside SQL Server.

Otherwise, search Google for "SMTP VB.NET" you will get thousands of links, including this:

http://www.codeproject.com/vb/net/epsendmail.asp|||Also you can either:

1) create a job in sql server that fetches the relevant rows
and sends emails

2) create a webservice that selects rows from the database
and create some vbs file that pings web service via http (XmlHttp)
and schedule this vbs in windows scheduler to run each day at night

AUTO DROP OF SUBSCRIPTION

Hi,
I have merge replicated, remote server & local server.
The database has only one table. It was working fine.
More than 15 days, it was isolated without any transaction.
After some days (around 15 days) I found that the subscription is dropped.
What is the reason for this?
Is there any way to avoid this?
Thanks,
Soura.
Soura,
the Expired Subscription Cleanup agent has deleted the subscription.
Disabling this agent won't help, because you need to forcably prevent the
subscription from expiring. If it is transactional, make sure that the
subscription expiration period is sufficiently long eg 3 weeks, and history
retention period also the same length of time. If it was transactional, the
same would apply to the transaction retention period.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I was under the impression that transactional replication needs to be
re-established after 72 hours and that this is the limit as it can not be
changed past this value. Are you primarily discussing the removal of the
subscription or the expiration of the subscription ?
It has been a long held understanding with the DBA's at my organisation that
72 Hours was the maximum time before expiration of a subscription to
transactional replication required a rebuild of the subscritpion .
"Paul Ibison" wrote:

> Soura,
> the Expired Subscription Cleanup agent has deleted the subscription.
> Disabling this agent won't help, because you need to forcably prevent the
> subscription from expiring. If it is transactional, make sure that the
> subscription expiration period is sufficiently long eg 3 weeks, and history
> retention period also the same length of time. If it was transactional, the
> same would apply to the transaction retention period.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Perhaps the quantity of information in your company makes this a practivcal
limit for the size of the distribution database, but there is no such
hardcoded limit in SQL Server - it is entirely configurable.
Rgds,
Paul Ibison

Auto Documenter

I was wondering if sql server 2000 has an auto documenter utility that
can dump information about a database into a word, excel, or text
file. I would not need anything to complicated just table, fields,
types, sizes, and maybe indexes.

Currently I am just exporting my tables into access, and then using
the auto documenter in access to get at this information. This works
but the field types do not match exactly, and when the tables get
large this is no longer a valid solution.

Thanks,

Charliehi Charlie,
"charlie" <charlie_knudsen@.hotmail.com> ha scritto nel messaggio
news:o2qkovgbe4vnqfjsl8r72lu4av55m71qd6@.4ax.com...
> I was wondering if sql server 2000 has an auto documenter utility that
> can dump information about a database into a word, excel, or text
> file. I would not need anything to complicated just table, fields,
> types, sizes, and maybe indexes.
> Currently I am just exporting my tables into access, and then using
> the auto documenter in access to get at this information. This works
> but the field types do not match exactly, and when the tables get
> large this is no longer a valid solution.

if you like, you can have a look at a free prj of mine at my home page, wich
provides an HTML export documentation feature..
hth
Andrea Montanari
andrea.sql@.virgilio.it
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.4.0 - DbaMgr ver 0.50.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--- remove DMO to reply an HTML export documentation feature..|||Hi

There are some tools available, and previous posts have advertised them.

I personally would reverse engineer into the Enterprise Architect Version of
Visio and run the reports to an rtf file.

John

"charlie" <charlie_knudsen@.hotmail.com> wrote in message
news:o2qkovgbe4vnqfjsl8r72lu4av55m71qd6@.4ax.com...
> I was wondering if sql server 2000 has an auto documenter utility that
> can dump information about a database into a word, excel, or text
> file. I would not need anything to complicated just table, fields,
> types, sizes, and maybe indexes.
> Currently I am just exporting my tables into access, and then using
> the auto documenter in access to get at this information. This works
> but the field types do not match exactly, and when the tables get
> large this is no longer a valid solution.
> Thanks,
> Charlie

Auto deletion of records sqlserver

I am not sure if I am at right place, anyhow I hope I am :)

Now the question: I am using an ASP.net Application with SQL-Server. I want to make a page so that it set the expiration time (date) for certain record and once that time reaches, it deletes those records, or make any updates to the record (what ever applicable). I also want to control this auto deletion from my application, means that turn this On/Off whenever needed. I am not sure how to start this. I was told by a friend that I need to use triggers from SQL-server but I need some help. Can anyone help me out on this?

Regards
Mykhan

A trigger will not expire the record; you will need to create a windows service or create a SQL job to delete the expired records. Alternatively you could add the following clause to your list select

AND (ExpiryDate is Null OR ExpiryDate > GetDate())

The latter would not delete the record but would achieve the same effect.

sql

Auto delete?

is there a way to auto delete all the record that is more than 1 month old compare to the date field in that table.

No try using the sql job that will run at a stupilate time and that will delete the records

Auto delete of backup files after 1 week problem

I have SQL 2000 maintenance plan setup to do full backups and have the "delete backups after 1 week" enabled but it never works. It seems to only work on the master and msdb backups but no user databases.

Any ideas?
Do you have any antivirus software installed?
What is the service pack on SQL Server?|||I think we have Trend Server Protect on all the servers.

How do I check the service pack of SQL?

Auto date through priority

Hi,

I have a table which contains a Create datetimefield which has a default on the current date , a priorityfield and another datetimefield which will be the due date and has to be calculated by the first date and the priority field,

How can i do this and what fields must i have.

Does someone does this?
can someone help me with this?

Kind regards Wimwhat whould happen to the due-date if the priority changed?|||what whould happen to the due-date if the priority changed?

The due date has to change to!|||use a computed column for the due date, fe:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER
, DUEDATE AS DATEADD(DD, PRIO, GETDATE())
)
GO

INSERT INTO TAB1 (PRIO) VALUES (1)
GO

SELECT * FROM TAB1
UPDATE TAB1 SET PRIO = 10
SELECT * FROM TAB1
GO

DROP TABLE TAB1
GO

EDIT: Just to be sure; either have a default prio or have a not null constraint|||use a computed column for the due date, fe:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER
, DUEDATE AS DATEADD(DD, PRIO, GETDATE())
)
GO

INSERT INTO TAB1 (PRIO) VALUES (1)
GO

SELECT * FROM TAB1
UPDATE TAB1 SET PRIO = 10
SELECT * FROM TAB1
GO

DROP TABLE TAB1
GO

EDIT: Just to be sure; either have a default prio or have a not null constraint

And what if i use the priority through an foreign key an have several prioritys which uses different time like:

prio 1 = 1day
prio 2 =1 week
prio3 = 1 month

How will it look like then?|||well, it could look like:

CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER NOT NULL
, DUEDATE AS CASE PRIO
WHEN 1 THEN DATEADD(DD, 1, FIRSTDATE)
WHEN 2 THEN DATEADD(WW, 1, FIRSTDATE)
WHEN 3 THEN DATEADD(MM, 1, FIRSTDATE)
ELSE DATEADD(YY, 1, FIRSTDATE)
END
)
GO|||well, it could look like:

CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER NOT NULL
, DUEDATE AS CASE PRIO
WHEN 1 THEN DATEADD(DD, 1, FIRSTDATE)
WHEN 2 THEN DATEADD(WW, 1, FIRSTDATE)
WHEN 3 THEN DATEADD(MM, 1, FIRSTDATE)
ELSE DATEADD(YY, 1, FIRSTDATE)
END
)
GO
Thanx alot man, that hit the spot!!
I really appreciated your help..

Cheers Wim

auto date & time

there is an auto increment function at SQL server.
is there a function to automatically to stamp the date and time of the
record?
is it the only way to do it at application level?
thanks a lot.
TonyHi Tony,
You can set a default of getdate() on a DateTime Column an this will record
the current date and time when you insert a row
Is that what you mean?
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"tony wong" <x34@.netvigator.com> wrote in message
news:OMlsTGxpFHA.3656@.TK2MSFTNGP09.phx.gbl...
> there is an auto increment function at SQL server.
> is there a function to automatically to stamp the date and time of the
> record?
> is it the only way to do it at application level?
> thanks a lot.
> Tony
>|||Sounds like getdate() function. Something like,
insert into table values(getdate())
or when you create a table,
create table table1 (d datetime default getdate())
Pohwan Han. Seoul. Have a nice day.
"tony wong" <x34@.netvigator.com> wrote in message
news:OMlsTGxpFHA.3656@.TK2MSFTNGP09.phx.gbl...
> there is an auto increment function at SQL server.
> is there a function to automatically to stamp the date and time of the
> record?
> is it the only way to do it at application level?
> thanks a lot.
> Tony
>|||Yes
Thanks Greg & Han
"GregO" <grego@.community.nospam> glsD:e9m7ZKxpFHA.1480@.TK2MSFTNGP10.phx.gbl...[co
lor=darkred]
> Hi Tony,
> You can set a default of getdate() on a DateTime Column an this will
> record the current date and time when you insert a row
> Is that what you mean?
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "tony wong" <x34@.netvigator.com> wrote in message
> news:OMlsTGxpFHA.3656@.TK2MSFTNGP09.phx.gbl...
>[/color]|||Here is another example:
http://www.mssql.com.au/kb/html/gmg...=psearch_articl
e_text&@.sa_id=63
"tony wong" <x34@.netvigator.com> wrote in message
news:OMlsTGxpFHA.3656@.TK2MSFTNGP09.phx.gbl...
> there is an auto increment function at SQL server.
> is there a function to automatically to stamp the date and time of the
> record?
> is it the only way to do it at application level?
> thanks a lot.
> Tony
>|||I forgot to mention smalldatetime. Generally the data type is more economic.
Pohwan Han. Seoul. Have a nice day.
"tony wong" <x34@.netvigator.com> wrote in message
news:u%23DBjNxpFHA.764@.TK2MSFTNGP14.phx.gbl...
> Yes
> Thanks Greg & Han
>
> "GregO" <grego@.community.nospam>
> glsD:e9m7ZKxpFHA.1480@.TK2MSFTNGP10.phx.gbl...
>

Auto database connetion when SQL server restarted in VC++

How can I connect database automatically when SQL server gets restarted while VC application execution. Any suggestions...
Regards
SabithaCould u please elaborate?|||I have a problem, now I am forcefully closing my application when SQL Server failed and reexecuting the application when the server is restarted. So I have to reconnect the database when Server is restarted without reexecution of application.
How do I know that the server is failed and it is restarted??sql

Auto Data Extraction through ODBC

Hi,

Could you please help me to find easiest way to schedule daily auto data extraction time in SQL Server Express 2005 Edition (I need to put time to load data every day).

Thanks

Amir

hi Amir,

please excuse my poor english, but can you please rephrase your question?

thank you and regards

|||

For SQL Express, you would most likely need to do the following:

1. Create a script file using bcp to unload the data.

2. Use SQLCmd.exe to run the script file

3. Use Windows Scheduler to execute SQLCmd.exe on a time schedule.

Look up using [ bcp ] in books Online

Look up using SQLCmd.exe in Books Online.

Look up using Windows Scheduler in Windows Help.

|||

Hi,

Thanks for your reply, I have gone through BCP link http://msdn2.microsoft.com/en-us/library/ms162802.aspx, I am wondering if you can provide a link of Sample "Sample Script of BCP to import" it would be great help for me.

Thanks

Amir

|||

Perhaps this will help you.

http://msdn2.microsoft.com/en-us/library/aa173839(SQL.80).aspx

|||

Thanks for info.

It might be a silly question for you but I am using first time and I have no idea could you please define below also

a) Should I creat Batch file OR Text File for BCP

b) How should I run and test

|||

I normally consider a 'batch file' as an input source, and a 'text file' as an output source.

Look up bcp in Books Online, Topic: bcp Utility.

There are a couple of examples that will execute in your query window.

Auto created statistics and missing statistics

Hello group.

I have an issue, which has bothered me for a while now:

I'm wondering why the column statistics, which SQL Server wants me to
create, if I turn off auto-created statistics, are so important to the
optimizer?

Example: from Northwind (with auto create stats off), I do the following:

SELECT * FROM Customers WHERE Country = 'Sweden'

My query plan show a clustered index scan, which is expected - no index
exists for Country. BUT, the query plan also shows, that the optimizer is
missing a statistic on Country, which tells me, that the optimizer would
benefit from knowing this.

I cannot see why? (and I've been trying for a while now).

If I create the missing statistics, nothing happens in the query plan (and
why should it?). I could understand it, if the optimizer suggested an index
on Country - this would make sense, but if creating the missing index, query
analyzer creates the statistics with an empty index, which seems to me to be
less than usable.

I've been thinking long and hard about this, but haven't been able to reach
a conclusion :) It has some relevance to my work, because allowing the
optimizer to create missing statistics limits my options for designing
indexes (e.g. covering) for some rather wide tables, so I'm thinking why not
turn it off altogether. But I would like to know the consequences - hope
somebody has already delved into this, and knows a good explanation.

Rgds
Jesper"Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message
news:40727f2e$0$237$edfadb0f@.dread12.news.tele.dk. ..
> Hello group.
> I have an issue, which has bothered me for a while now:
> I'm wondering why the column statistics, which SQL Server wants me to
> create, if I turn off auto-created statistics, are so important to the
> optimizer?
> Example: from Northwind (with auto create stats off), I do the following:
> SELECT * FROM Customers WHERE Country = 'Sweden'
> My query plan show a clustered index scan, which is expected - no index
> exists for Country. BUT, the query plan also shows, that the optimizer is
> missing a statistic on Country, which tells me, that the optimizer would
> benefit from knowing this.
> I cannot see why? (and I've been trying for a while now).
> If I create the missing statistics, nothing happens in the query plan (and
> why should it?). I could understand it, if the optimizer suggested an
index
> on Country - this would make sense, but if creating the missing index,
query
> analyzer creates the statistics with an empty index, which seems to me to
be
> less than usable.
> I've been thinking long and hard about this, but haven't been able to
reach
> a conclusion :) It has some relevance to my work, because allowing the
> optimizer to create missing statistics limits my options for designing
> indexes (e.g. covering) for some rather wide tables, so I'm thinking why
not
> turn it off altogether. But I would like to know the consequences - hope
> somebody has already delved into this, and knows a good explanation.
> Rgds
> Jesper

http://msdn.microsoft.com/library/d...l/statquery.asp

Simon|||Thanks, Simon, informative article, but ...

... it doesn't really explain the stuff, that I wrote. The closest I get to
an explanation, when reading this is 'These statistics are created for
columns where the optimizer would have to estimate the approximate density
or distribution otherwise'.

I knew this, but I still do not know, why the optimizer needs to know the
density and/or distribution?? I can see no valid reason, and therefore I can
see no good reason for enabling auto-creation of stats.

What I probably looking for is a good example, where the use of an
automatically created stat saves time, cycles and IOs :)

Best Rgds - Jesper

"Simon Hayes" <sql@.hayes.ch> skrev i en meddelelse
news:4072f05a$1_2@.news.bluewin.ch...
> "Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message
> news:40727f2e$0$237$edfadb0f@.dread12.news.tele.dk. ..
> > Hello group.
> > I have an issue, which has bothered me for a while now:
> > I'm wondering why the column statistics, which SQL Server wants me to
> > create, if I turn off auto-created statistics, are so important to the
> > optimizer?
> > Example: from Northwind (with auto create stats off), I do the
following:
> > SELECT * FROM Customers WHERE Country = 'Sweden'
> > My query plan show a clustered index scan, which is expected - no index
> > exists for Country. BUT, the query plan also shows, that the optimizer
is
> > missing a statistic on Country, which tells me, that the optimizer would
> > benefit from knowing this.
> > I cannot see why? (and I've been trying for a while now).
> > If I create the missing statistics, nothing happens in the query plan
(and
> > why should it?). I could understand it, if the optimizer suggested an
> index
> > on Country - this would make sense, but if creating the missing index,
> query
> > analyzer creates the statistics with an empty index, which seems to me
to
> be
> > less than usable.
> > I've been thinking long and hard about this, but haven't been able to
> reach
> > a conclusion :) It has some relevance to my work, because allowing the
> > optimizer to create missing statistics limits my options for designing
> > indexes (e.g. covering) for some rather wide tables, so I'm thinking why
> not
> > turn it off altogether. But I would like to know the consequences - hope
> > somebody has already delved into this, and knows a good explanation.
> > Rgds
> > Jesper
>
http://msdn.microsoft.com/library/d...l/statquery.asp
> Simon|||"Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message
news:4072f91b$0$300$edfadb0f@.dread12.news.tele.dk. ..
> Thanks, Simon, informative article, but ...
> ... it doesn't really explain the stuff, that I wrote. The closest I get
to
> an explanation, when reading this is 'These statistics are created for
> columns where the optimizer would have to estimate the approximate density
> or distribution otherwise'.
> I knew this, but I still do not know, why the optimizer needs to know the
> density and/or distribution?? I can see no valid reason, and therefore I
can
> see no good reason for enabling auto-creation of stats.
> What I probably looking for is a good example, where the use of an
> automatically created stat saves time, cycles and IOs :)
> Best Rgds - Jesper

OK, here's another informative article :-)

http://www.winnetmag.com/SQLServer/...2075/22075.html

In summary, index statistics exist only for the first column in an index,
but auto-created (or manually created) statistics can exist for any column.
This gives the optimizer extra information, which might mean it chooses a
different, more efficient index for a query.

Check out the example on the second page of the article - on my system, this
reduced the logical reads required for the query from 104 to 43.

But you're correct to consider that there can be an impact on performance in
some situations:

http://support.microsoft.com/defaul...kb;en-us;195565

Simon|||Thanks, Simon, that one did the trick.

One less mystery.

On my machine, QA tells me that the two queries (the index scan on
ProductID/Quantity vs. the clustered index scan) takes 43.65 and 56.35% cost
respectively. I would argue, that this saving is not worth the 'used up
index space'. In my professional life, I've seen tables, which are wide
enough (200+ columns) to demand, that precious index space is saved.

Basically, I think there are too many 'ifs' before an auto-created index
saves performance, but I appreciate the optimization idea behind it.

Thanks - Jesper

"Simon Hayes" <sql@.hayes.ch> skrev i en meddelelse
news:4073121e_1@.news.bluewin.ch...
> "Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message
> news:4072f91b$0$300$edfadb0f@.dread12.news.tele.dk. ..
> > Thanks, Simon, informative article, but ...
> > ... it doesn't really explain the stuff, that I wrote. The closest I get
> to
> > an explanation, when reading this is 'These statistics are created for
> > columns where the optimizer would have to estimate the approximate
density
> > or distribution otherwise'.
> > I knew this, but I still do not know, why the optimizer needs to know
the
> > density and/or distribution?? I can see no valid reason, and therefore I
> can
> > see no good reason for enabling auto-creation of stats.
> > What I probably looking for is a good example, where the use of an
> > automatically created stat saves time, cycles and IOs :)
> > Best Rgds - Jesper
> OK, here's another informative article :-)
> http://www.winnetmag.com/SQLServer/...2075/22075.html
> In summary, index statistics exist only for the first column in an index,
> but auto-created (or manually created) statistics can exist for any
column.
> This gives the optimizer extra information, which might mean it chooses a
> different, more efficient index for a query.
> Check out the example on the second page of the article - on my system,
this
> reduced the logical reads required for the query from 104 to 43.
> But you're correct to consider that there can be an impact on performance
in
> some situations:
> http://support.microsoft.com/defaul...kb;en-us;195565
> Simon|||"Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message news:<40732877$0$274$edfadb0f@.dread12.news.tele.dk>...
> Thanks, Simon, that one did the trick.
> One less mystery.
> On my machine, QA tells me that the two queries (the index scan on
> ProductID/Quantity vs. the clustered index scan) takes 43.65 and 56.35% cost
> respectively. I would argue, that this saving is not worth the 'used up
> index space'. In my professional life, I've seen tables, which are wide
> enough (200+ columns) to demand, that precious index space is saved.
> Basically, I think there are too many 'ifs' before an auto-created index
> saves performance, but I appreciate the optimization idea behind it.
> Thanks - Jesper

<snip
Well, you have to be careful about reaching conclusions based on
simple queries using small data sets. It's possible that a complex
join involving millions of rows would give a more significant
difference. To get a definite answer for your environment, you would
have to do some benchmarking, with and without statistics.

Simon

Auto Create trigger after re-initialization completed

Hi all,

Is it possible to create a trigger after creation of table during reinitialization? if so, how can I do that? Thanks in advance!

Hi, Stephanie,

If I understand your question correctly, you wan to create a trigger on subscriber db right after replication agent created replicating table on subscriber database. If so, you can use post snapshot script.

To specify a post snapshot script in Management Studio, right click a publication under replication node, choose Properties context menu to bring up Publication Properties dialog, on the left pane treeview, click snapshot, you should see the input field to specify Pre snapshot script and post snapshot script under "Run Additional scripts" section.

Hope that helps,

Zhiqiang

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thx for reply

I can't find the option 'After applying the snapshot, execute
this script text box' in Snapshot page of the Publication Properties as
stated in Online Books. Also, I found a note in online book stating 'These
options are not available if the Snapshot format option is set to Character.'

The replication from oracle to MSSQL2005 and the snapshot format is set to 'character' automatically. Is it possible to create trigger in such case? Thanks in advance

|||

Stephanie,

You're right, for snapshot using Character mode, the "run additional scripts" option is not available under Publication Properties dialog (disabled in the UI).

In this case, you can add an extra step to create your subscriber table trigger after the distribution or merge agent step in your synchronization job. Following are the steps:

1. Find your sync job under Management Studio --> Object Explorer --> SQL Server Agent --> Jobs --> Your distribution/merge agent job name

2. Right click the sync job, pick Properties from context menu, this will bring up Job Properties dialog

3. On the left side pane under "Select a page", click Steps

4. Insert a new step right after step "Run Agent" to execute your script to create the subscriber table trigger,

Example:

step name: Run Post snapshot script to create subscriber table trigger

type: Operating System (cmdexec)

RunAs: SQL Server Agent Service Account

Command: sqlcmd.exe -E -Ssubscriberinstancename -dsubscriberdbname -Q"create trigger postsnapshottrigger on [subscriberdbname].[dbo].[subscribertable] for update,insert,delete AS select * from sys.databases"

5. Change the Advanced Option for the new step:

On Success action: Quit the job reporting success

On failure action: Go to the next step

6. Change the Advanced Option for step "Run Agent":

On success action: Go to the next step

Leave on failure action unchanged

The original setting for "On success action" is "quite the job reporting success", this will result in the new step you just added being ignored thus we need this change.

7. Reinitialize your subscription and rerun your sync job

I have tested this scenario on a character mode merge publication and it worked just fine (I'm not using Oracle in this test).

If you're not using SQL Server agent to run your jobs, for example, using a batch file to run sync command directly, you can just add the command to create trigger after sync command.

Let me know if this helps.

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

The table is re-created after re-initialize but the trigger didn't be created. I found that the agent job is keeping 'running'...

Just want to confirm that the distribution job with the 1step 'Distribution agent startup message' , right?

Thanks in advance

|||

Hi, stephanie,

I just realized there is one issue with my approach if your distribution agent is set to run continuously (there is a -continuous prameter for the distribution command for step Run Agent), this way your new step will never get a chance to run since the Run Agent step is running continuously.

If your oracle publication can be re-created, you can walk through the New Oracle Publication Wizard and at the page of "Wizard Actions", uncheck "Create the publication" option and check "Generate a script file with steps to create the publication", once the script is created, modify the @.post_snapshot_script pointing to the location of your script to create the trigger, then run this generated script to create your oracle publication.

If you don't want to re-create your oracle publication, you can modify the generated snapshot files to create the trigger after the subscriber table is created (there should be a .sch file for your replicating table), then re-initialize your publication. Note this manual step need to be done each time you re-generated the snapshot files

You can also create a new SQL Server agent job to just create the trigger and have it to run continuously. The logic of your script should be: if subscriber table exist and if the trigger doesn't exist, then create the trigger. This will have some system overhead and your trigger may miss some transactions if updates to the table occurred and before the trigger is created.

Let me know if it helps.

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I prefer option 1 but i found that there is no '@.post_snapshot_script ' in the scripted generated... should I append this option into the script? Also, there are 3 parts in the script, running

sys.sp_addlogreader_agent, exec sp_addpublication,
exec sp_addpublication_snapshot and sp_addarticle

where should I append it? Thanks in advance.

|||

@.post_snapshot_script is a parameter of sp_addpublication so it should be appended to the sp_addpublication call, see Books Online help topic for sp_addpublication for details of this parameter.

Let me know if you encounter any issues.

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks a lot, it perfectly solve our problem!

|||

That's great!

By the way, I've submitted a feature request to have new publication wizard support the addition of pre/post snapshot script, this issue may be addressed in a future release of SQL Server so you configure pre/post snapshot script without needing the manual step of modifying and running the replication script as a workaround.

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

Auto Create trigger after re-initialization completed

Is it possible to create a trigger after creation of table during
reinitialization? if so, how can I do that? Thanks in advance!
Do it through a post snapshot command.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:C8C2E193-7228-4A86-8E30-A6523CF62EFA@.microsoft.com...
> Is it possible to create a trigger after creation of table during
> reinitialization? if so, how can I do that? Thanks in advance!
>
|||What about if the replication from oracle to MSSQL2005 and the snapshot
format is set to 'character'? Thanks in advance.
"Hilary Cotter" wrote:

> Do it through a post snapshot command.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:C8C2E193-7228-4A86-8E30-A6523CF62EFA@.microsoft.com...
>
>
|||A character format for the snapshot refers to the data.
A post snapshot command is a script file that you specify. Once the
snapshot completes, SQL Server will open the script file that you specify
and execute anything in the script.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:A5E40A16-E2EF-4339-9F4E-D005820DEE45@.microsoft.com...[vbcol=seagreen]
> What about if the replication from oracle to MSSQL2005 and the snapshot
> format is set to 'character'? Thanks in advance.
> "Hilary Cotter" wrote:
|||Thx for reply. I can't find the option 'After applying the snapshot, execute
this script text box' in Snapshot page of the Publication Properties as
stated in Online Books. Also, I found a note in online book stating 'These
options are not available if the Snapshot format option is set to Character.'
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/b7bb1e4c-5b48-4bb1-9dc8-47c911f2cc82.htm
Please advise. Thanks
"Michael Hotek" wrote:

> A character format for the snapshot refers to the data.
> A post snapshot command is a script file that you specify. Once the
> snapshot completes, SQL Server will open the script file that you specify
> and execute anything in the script.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:A5E40A16-E2EF-4339-9F4E-D005820DEE45@.microsoft.com...
>
>
|||Forgot about that restriction. There was a reason for that restriction, but
I can't remember what it was right now.
One workaround is to create a DDL trigger like the following:
create trigger test
on database
for create_table
as
declare @.parentid int
select @.parentid = object_id from sys.tables where name = 'MyTable'
print @.parentid
if @.parentid is null
return
if exists (select 1 from sys.triggers where parent_id = @.parentid
and name = 'MyTestTrigger')
return
exec sp_executesql N'create trigger MyTestTrigger on MyTable for insert as
raiserror(''Test'',16,10)'
go
create table MyTable
(id int)
go
I can't think of any other workarounds at the moment.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:2343A716-0B57-4972-A9B2-C3B4FECA72D9@.microsoft.com...[vbcol=seagreen]
> Thx for reply. I can't find the option 'After applying the snapshot,
> execute
> this script text box' in Snapshot page of the Publication Properties as
> stated in Online Books. Also, I found a note in online book stating
> 'These
> options are not available if the Snapshot format option is set to
> Character.'
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/b7bb1e4c-5b48-4bb1-9dc8-47c911f2cc82.htm
> Please advise. Thanks
> "Michael Hotek" wrote:

Auto create stats on but DTA recommends creating them

So I am running a query against a database that has Auto Create statistics
turned on and then what I did was pass this same query to DTA and it
recommends creating statistics as an improvement. Not to mention, it also
said to create some indices.
So the question I have is, when I did run the query, why does SQL not create
those statistics that DTA was recommending ?Hi Hassan
Are these statistics on multiple columns?
John
"Hassan" wrote:
> So I am running a query against a database that has Auto Create statistics
> turned on and then what I did was pass this same query to DTA and it
> recommends creating statistics as an improvement. Not to mention, it also
> said to create some indices.
> So the question I have is, when I did run the query, why does SQL not create
> those statistics that DTA was recommending ?
>|||I wish I had it saved .. Not sure..
Is that a bug where if it is on multiple columns, the auto create stats
doesnt automatically take care of it when i run the query ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
> Hi Hassan
> Are these statistics on multiple columns?
> John
> "Hassan" wrote:
>> So I am running a query against a database that has Auto Create
>> statistics
>> turned on and then what I did was pass this same query to DTA and it
>> recommends creating statistics as an improvement. Not to mention, it also
>> said to create some indices.
>> So the question I have is, when I did run the query, why does SQL not
>> create
>> those statistics that DTA was recommending ?
>>|||No, it is not a bug. John may refer to the fact that statistics on multiple
columns (non-indexed) are not created automatically by SQL Server. You need
to create these statistics manually. But once these multi-columns statistics
are created, SQL Server will automatically update them if Auto Update
Statistics is set to True.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
> > Hi Hassan
> >
> > Are these statistics on multiple columns?
> >
> > John
> >
> > "Hassan" wrote:
> >
> >> So I am running a query against a database that has Auto Create
> >> statistics
> >> turned on and then what I did was pass this same query to DTA and it
> >> recommends creating statistics as an improvement. Not to mention, it also
> >> said to create some indices.
> >>
> >> So the question I have is, when I did run the query, why does SQL not
> >> create
> >> those statistics that DTA was recommending ?
> >>
> >>
>|||Hi Hassan
Ben has said that the AUTO_CREATE_STATISTICS will only create statistics for
a single column and this is by design.
Unless you have explicitly deleted the DTA session, you should be able to
review the output again if you go back into DTA.
John
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
> > Hi Hassan
> >
> > Are these statistics on multiple columns?
> >
> > John
> >
> > "Hassan" wrote:
> >
> >> So I am running a query against a database that has Auto Create
> >> statistics
> >> turned on and then what I did was pass this same query to DTA and it
> >> recommends creating statistics as an improvement. Not to mention, it also
> >> said to create some indices.
> >>
> >> So the question I have is, when I did run the query, why does SQL not
> >> create
> >> those statistics that DTA was recommending ?
> >>
> >>
>

Auto create stats on but DTA recommends creating them

Auto Create Stats & Auto Update stats

I've inherited a server where auto create stats and auto update stats have b
een turned off. There is a weekly job which executes Update Statistics on e
ach table with the norecompute clause. In looking at traces I'm seeing quit
e a few missing statistics
entries.
When is it appropriate to turn off auto create stats?Peter,
as I understand it, if you've just created a table and are loading it with
loads of rows, you might disable the creation of statistics because the
performance overhead is more than you can afford. Likewise if the table
already exists with statistics and requires loading, you might run
sp_autostats or the UPDATE STATISTICS command with the WITH NORECOMPUTE
option. However, this is only a temporary measure and once loaded it is
normal to have it auto updating, or else incorrect query plans may result.
Regards,
Paul Ibison

Auto Create Stats & Auto Update stats

I've inherited a server where auto create stats and auto update stats have been turned off. There is a weekly job which executes Update Statistics on each table with the norecompute clause. In looking at traces I'm seeing quite a few missing statistics
entries.
When is it appropriate to turn off auto create stats?
Peter,
as I understand it, if you've just created a table and are loading it with
loads of rows, you might disable the creation of statistics because the
performance overhead is more than you can afford. Likewise if the table
already exists with statistics and requires loading, you might run
sp_autostats or the UPDATE STATISTICS command with the WITH NORECOMPUTE
option. However, this is only a temporary measure and once loaded it is
normal to have it auto updating, or else incorrect query plans may result.
Regards,
Paul Ibison

Auto Create Stats

Is it a good idea to have Auto create stats and Auto
update stats ON in a database
Does it slow down performance while loading large amounts
of data
SanjayHi Sanjay
No, the updating of stats does not happen during a load. When you run a
query, and SQL Server notices the stats are out of date, then it will auto
update them.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:000a01c39cb9$c906e160$a601280a@.phx.gbl...
> Is it a good idea to have Auto create stats and Auto
> update stats ON in a database
> Does it slow down performance while loading large amounts
> of data
> Sanjay|||Sanjay,
Nothing is free, so Auto Create Stats does cost something from the server.
However, I understand that it tries to do that work during low periods and
stay out of the way of other work. And remember, statistics that are not in
sync with the data can cause the optimizer to guess wrong.
There is a discussion of the issues at:
http://www.sql-server-performance.com/statistics.asp
Russell Fields
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:000a01c39cb9$c906e160$a601280a@.phx.gbl...
> Is it a good idea to have Auto create stats and Auto
> update stats ON in a database
> Does it slow down performance while loading large amounts
> of data
> Sanjaysql

Auto Create Statistics

We have accidentally had the "Auto Create Statistics" turned set to true on
one of our production databases. When we told the group responsible for the
application for this database, they said they have been having performance
issues and are enquiring if having this setting turned on could be the cause
of that.
From BOL, with automatic statistics, the statistics are updated periodically
as the data in the table changes. A statistics update occurs whenever the
statistics in a query execution fail a test for the current statistics.
Statistical information is updated whenever approximately 20 percent of the
data rows have been changed.
Is it possible that this overhead could have an affect on database
performance?
Also, they asked if we could give then a report for displaying all the
statistics. I know the "DBBC SHOW_STATISTICS" command will display the
statistics if you supply the table and index parameters. The database is
quite large with many tables and indexes. I could probably write a script
using cursors but I am wondering if there is any other way for generating a
report for all of the statistics for the database.Hi Loren
This setting is ON by default on all databases, and it is usually a good
idea to leave it on. In addition, this setting is not the same as AUTO
UPDATE STATISTICS. Since this was most likely on since the time the db was
created, it's unlikely that it is the cause for any recent performance
problems.
AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
columns. AUTO UPDATE STATISTICS tells SQL Server to update index statistics
when they get stale.
Actually looking at statistics is rarely needed, and most people do it only
when troubleshooting one particular query that is misbehaving. Why do they
want a report for ALL the statistics when they are subject to frequent
change? By the time they got through looking at the whole report, the values
would probably have changed.
I would think the DBCC SHOW_STATISTICS would be good enough, when they need
to look at particular statistics for a particular table or index.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
> We have accidentally had the "Auto Create Statistics" turned set to true
> on one of our production databases. When we told the group responsible for
> the application for this database, they said they have been having
> performance issues and are enquiring if having this setting turned on
> could be the cause of that.
> From BOL, with automatic statistics, the statistics are updated
> periodically as the data in the table changes. A statistics update occurs
> whenever the statistics in a query execution fail a test for the current
> statistics. Statistical information is updated whenever approximately 20
> percent of the data rows have been changed.
> Is it possible that this overhead could have an affect on database
> performance?
> Also, they asked if we could give then a report for displaying all the
> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
> statistics if you supply the table and index parameters. The database is
> quite large with many tables and indexes. I could probably write a script
> using cursors but I am wondering if there is any other way for generating
> a report for all of the statistics for the database.
>|||As far as I know, this post was asking about Auto CREATE Statistics, not
AUTO UPDATE.
Yes, AUTO UPDATE does have a cost, but in most cases, updating stats and
recompiling has far less of a cost than the costs of using a bad plan
because your statistics are out of date. SQL 2005 also introduced AUTO
UPDATE STATISTICS ASYNC, so that the stats will be automatically updated,
but it will not impact the query that triggered the update, i.e. that query
will not have to wait for the update and the subsequent recompile.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:4AF1D2DE-60B6-4C24-9F60-3BA208A222F1@.microsoft.com...
> Hello Kalen!
>
> As far as I know Auto Update Statistics cause changing Query Plans and
> causes SPs to be recompiled so this makes a performance problem. I know
> that if statistics would be ouf of date, then it's another problem. This
> seems kind of dilemma.
> I would be happy to hear your comments about this.
>
> --
> Ekrem Önsoy
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ekdDiSSBIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi Loren
>> This setting is ON by default on all databases, and it is usually a good
>> idea to leave it on. In addition, this setting is not the same as AUTO
>> UPDATE STATISTICS. Since this was most likely on since the time the db
>> was created, it's unlikely that it is the cause for any recent
>> performance problems.
>> AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
>> columns. AUTO UPDATE STATISTICS tells SQL Server to update index
>> statistics when they get stale.
>> Actually looking at statistics is rarely needed, and most people do it
>> only when troubleshooting one particular query that is misbehaving. Why
>> do they want a report for ALL the statistics when they are subject to
>> frequent change? By the time they got through looking at the whole
>> report, the values would probably have changed.
>> I would think the DBCC SHOW_STATISTICS would be good enough, when they
>> need to look at particular statistics for a particular table or index.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
>> news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> We have accidentally had the "Auto Create Statistics" turned set to true
>> on one of our production databases. When we told the group responsible
>> for the application for this database, they said they have been having
>> performance issues and are enquiring if having this setting turned on
>> could be the cause of that.
>> From BOL, with automatic statistics, the statistics are updated
>> periodically as the data in the table changes. A statistics update
>> occurs whenever the statistics in a query execution fail a test for the
>> current statistics. Statistical information is updated whenever
>> approximately 20 percent of the data rows have been changed.
>> Is it possible that this overhead could have an affect on database
>> performance?
>> Also, they asked if we could give then a report for displaying all the
>> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
>> statistics if you supply the table and index parameters. The database is
>> quite large with many tables and indexes. I could probably write a
>> script using cursors but I am wondering if there is any other way for
>> generating a report for all of the statistics for the database.
>>
>|||Hello Kalen!
As far as I know Auto Update Statistics cause changing Query Plans and
causes SPs to be recompiled so this makes a performance problem. I know that
if statistics would be ouf of date, then it's another problem. This seems
kind of dilemma.
I would be happy to hear your comments about this.
Ekrem Önsoy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ekdDiSSBIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi Loren
> This setting is ON by default on all databases, and it is usually a good
> idea to leave it on. In addition, this setting is not the same as AUTO
> UPDATE STATISTICS. Since this was most likely on since the time the db was
> created, it's unlikely that it is the cause for any recent performance
> problems.
> AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
> columns. AUTO UPDATE STATISTICS tells SQL Server to update index
> statistics when they get stale.
> Actually looking at statistics is rarely needed, and most people do it
> only when troubleshooting one particular query that is misbehaving. Why do
> they want a report for ALL the statistics when they are subject to
> frequent change? By the time they got through looking at the whole report,
> the values would probably have changed.
> I would think the DBCC SHOW_STATISTICS would be good enough, when they
> need to look at particular statistics for a particular table or index.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> We have accidentally had the "Auto Create Statistics" turned set to true
>> on one of our production databases. When we told the group responsible
>> for the application for this database, they said they have been having
>> performance issues and are enquiring if having this setting turned on
>> could be the cause of that.
>> From BOL, with automatic statistics, the statistics are updated
>> periodically as the data in the table changes. A statistics update occurs
>> whenever the statistics in a query execution fail a test for the current
>> statistics. Statistical information is updated whenever approximately 20
>> percent of the data rows have been changed.
>> Is it possible that this overhead could have an affect on database
>> performance?
>> Also, they asked if we could give then a report for displaying all the
>> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
>> statistics if you supply the table and index parameters. The database is
>> quite large with many tables and indexes. I could probably write a script
>> using cursors but I am wondering if there is any other way for generating
>> a report for all of the statistics for the database.
>|||Yes, I know that post was about CREATE STATISTICS. However, when you
mentioned Update Statistics, I wanted to have your comments about this.
Thanks for sharing your thoughts about this.
--
Ekrem Önsoy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O1gT8wSBIHA.4656@.TK2MSFTNGP04.phx.gbl...
> As far as I know, this post was asking about Auto CREATE Statistics, not
> AUTO UPDATE.
> Yes, AUTO UPDATE does have a cost, but in most cases, updating stats and
> recompiling has far less of a cost than the costs of using a bad plan
> because your statistics are out of date. SQL 2005 also introduced AUTO
> UPDATE STATISTICS ASYNC, so that the stats will be automatically updated,
> but it will not impact the query that triggered the update, i.e. that
> query will not have to wait for the update and the subsequent recompile.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:4AF1D2DE-60B6-4C24-9F60-3BA208A222F1@.microsoft.com...
>> Hello Kalen!
>>
>> As far as I know Auto Update Statistics cause changing Query Plans and
>> causes SPs to be recompiled so this makes a performance problem. I know
>> that if statistics would be ouf of date, then it's another problem. This
>> seems kind of dilemma.
>> I would be happy to hear your comments about this.
>>
>> --
>> Ekrem Önsoy
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ekdDiSSBIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi Loren
>> This setting is ON by default on all databases, and it is usually a good
>> idea to leave it on. In addition, this setting is not the same as AUTO
>> UPDATE STATISTICS. Since this was most likely on since the time the db
>> was created, it's unlikely that it is the cause for any recent
>> performance problems.
>> AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
>> columns. AUTO UPDATE STATISTICS tells SQL Server to update index
>> statistics when they get stale.
>> Actually looking at statistics is rarely needed, and most people do it
>> only when troubleshooting one particular query that is misbehaving. Why
>> do they want a report for ALL the statistics when they are subject to
>> frequent change? By the time they got through looking at the whole
>> report, the values would probably have changed.
>> I would think the DBCC SHOW_STATISTICS would be good enough, when they
>> need to look at particular statistics for a particular table or index.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
>> news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> We have accidentally had the "Auto Create Statistics" turned set to
>> true on one of our production databases. When we told the group
>> responsible for the application for this database, they said they have
>> been having performance issues and are enquiring if having this setting
>> turned on could be the cause of that.
>> From BOL, with automatic statistics, the statistics are updated
>> periodically as the data in the table changes. A statistics update
>> occurs whenever the statistics in a query execution fail a test for the
>> current statistics. Statistical information is updated whenever
>> approximately 20 percent of the data rows have been changed.
>> Is it possible that this overhead could have an affect on database
>> performance?
>> Also, they asked if we could give then a report for displaying all the
>> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
>> statistics if you supply the table and index parameters. The database
>> is quite large with many tables and indexes. I could probably write a
>> script using cursors but I am wondering if there is any other way for
>> generating a report for all of the statistics for the database.
>>
>>
>

Thursday, March 29, 2012

auto format of stored procedures?

Auto filling

Auto filling

Auto filling

Auto filling

Auto fill in colums trough foreign key relationship

Auto fill collumn

Auto field calculation

Auto Failover Issues

Auto export to Excel

Auto export to Excel

auto expanding Temp DB to fit large requests

Tuesday, March 27, 2012

Auto e-mails

Auto Email Statistics from DB Table

Auto Email Statistics from DB Table

Auto email

AUTO DROP OF SUBSCRIPTION

Auto Documenter

Auto deletion of records sqlserver

Auto delete?

Auto delete of backup files after 1 week problem

Auto date through priority

auto date & time

Auto database connetion when SQL server restarted in VC++

Auto Data Extraction through ODBC

Auto created statistics and missing statistics

Auto Create trigger after re-initialization completed

Auto Create trigger after re-initialization completed

Auto create stats on but DTA recommends creating them

Auto create stats on but DTA recommends creating them

Auto create stats on but DTA recommends creating them

Auto Create Stats & Auto Update stats

Auto Create Stats & Auto Update stats

Auto Create Stats

Auto Create Statistics

Attach remote DB file

Blog Archive

About Me