Attach remote DB file: sql

Showing posts with label sql. Show all posts

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

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?

Attach remote DB file

Blog Archive

About Me