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 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 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...
>

auditing

I have another question.
I want to create audit table with columns (userID, date, tableAffected,
ColumnAffected).
This table should have data from tables that I want to trace. It easy to
collect data about time and users, but I don't know how to collect data abou
t
which table and which column is affected.
So, if i have table employees employeeID,Lastname,Firstname I wolud like to
have 3 record in my Audit table:
uros; 11/11/2005; employees; EmployeeID
uros; 11/11/2005; employees; Lastname
uros; 11/11/2005; employees; Firstname
Do you have any suggestions?Hi,
I wat to give my suggestion here , there might be better suggestions pouring
for this prob. so keep checking,
Create trigger for all ur tables and in triggers use Updated(col_name) to
check whether column got updated or not.
--
Vishal Khajuria
9886170165
IBM Bangalore
"uros" wrote:

> I have another question.
> I want to create audit table with columns (userID, date, tableAffected,
> ColumnAffected).
> This table should have data from tables that I want to trace. It easy to
> collect data about time and users, but I don't know how to collect data ab
out
> which table and which column is affected.
> So, if i have table employees employeeID,Lastname,Firstname I wolud like t
o
> have 3 record in my Audit table:
> uros; 11/11/2005; employees; EmployeeID
> uros; 11/11/2005; employees; Lastname
> uros; 11/11/2005; employees; Firstname
> Do you have any suggestions?|||hi Uros,
You can design a SQL server trace using sql profiler
and save the trace to a file.
for optimum performance. you can run a trace
on another machine which is the standard way of profiling sql server.
in this way your auditing doesn't disturb
the production server
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"uros" wrote:

> I have another question.
> I want to create audit table with columns (userID, date, tableAffected,
> ColumnAffected).
> This table should have data from tables that I want to trace. It easy to
> collect data about time and users, but I don't know how to collect data ab
out
> which table and which column is affected.
> So, if i have table employees employeeID,Lastname,Firstname I wolud like t
o
> have 3 record in my Audit table:
> uros; 11/11/2005; employees; EmployeeID
> uros; 11/11/2005; employees; Lastname
> uros; 11/11/2005; employees; Firstname
> Do you have any suggestions?|||Uros,
I have this design working except I have OldValues and NewValues columns
instead of ColumnAffected. These two columns are for recording affected
columns names and values in an xml style. This allows for having just one
row per action. In addition I have EventType to tell update from insert from
delete and RowID column to link back to the original table record. The work
of writing to the audit table is done in a universal trigger that can be
dropped into a table design with no modifications. In that trigger the
following is user to get the table name:
select object_name(parent_obj)
from sysobjects
where id = @.@.procid
and columns_updated() function output is parsed to get to the columns
affected.
Ilya
"uros" <uros@.discussions.microsoft.com> wrote in message
news:0D4071F5-7445-43E1-B904-B4F43DB020FF@.microsoft.com...
> I have another question.
> I want to create audit table with columns (userID, date, tableAffected,
> ColumnAffected).
> This table should have data from tables that I want to trace. It easy to
> collect data about time and users, but I don't know how to collect data
about
> which table and which column is affected.
> So, if i have table employees employeeID,Lastname,Firstname I wolud like
to
> have 3 record in my Audit table:
> uros; 11/11/2005; employees; EmployeeID
> uros; 11/11/2005; employees; Lastname
> uros; 11/11/2005; employees; Firstname
> Do you have any suggestions?

audit

Hi,
I want to audit the log-on attempts and record with
another information (login, user, NTusername, date,
hostname, etc.)
We reviewed Audit level but errorlog only sent the login
used, C2 gave me a lot of information buy I only want a
subset of it.
Do you know how can I get this kind of information?
Do exist another tool to help me to get this information?
ThanksIf you want a subset of what you get with C2 auditing, you
can create a SQL Profiler trace that will capture what you
want. C2 auditing is comprised of classes and events
available to you when using Profiler. Look at the Security
Audit event classes when defining a trace in Profiler.
-Sue
On Mon, 29 Mar 2004 09:14:19 -0800, "MB"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
> I want to audit the log-on attempts and record with
>another information (login, user, NTusername, date,
>hostname, etc.)
> We reviewed Audit level but errorlog only sent the login
>used, C2 gave me a lot of information buy I only want a
>subset of it.
> Do you know how can I get this kind of information?
> Do exist another tool to help me to get this information?
>Thanks

attribute date of type numeric to datetime...!

hi everyone..!!

how can I turn an attribute date of type numeric of a table and pass it to another table (using SSIS) as datetime

Can you provide an example of what those 'dates' look like?

/Kenneth

|||There is a special SSIS forum, you may be better using that. One way however would be to use CAST or CONVERT and create a view which converts the current date to correct format.|||

The 'trick' is usually, that if the 'date' is stored as a number, you must find out what the numbers represent. eg 12345 - is this seconds, minutes, hours? And counting from when?

Once you know the time-unit the numbers are using, and the starting point, you can use the datefunctions to do date arithmetics on the 'number'.

/Kenneth