Automating a time-sensitive query

My company uses MS Retail Management System, which is SQL-based. Without
going into a lot of detail about RMS, here's what I need to do: schedule a
query that will insert a new row into an existing table. Part of the data
that needs to be inserted is date/time. For example, here's the query I
need to run:
INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title, FromDate)
VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
What I don't know how to do:
Generate the date fields "Today 6:30PM" and "NOW"
Schedule the query.
I've got SQL 2000 (as part of SBS Premium), and I am certain that I have the
capability to do this. I think I have figured out the scheduling part, but
I'm hesitant to try it out on a live database. In fact, I think I'll try a
simpler query on a sample database before I ever go live with it.
Just to be perfectly clear, I'm a whole lot closer to being an SQL newbie
than an SQL expert.
Thanks for any advice you might offer,
Tom
--
Stop Fishing For e-MailThe function getdate() returns the current date/time, and jobs are the
standard method of scheduling a query, stored procedure, or package to
execute at a specific time or at intervals.
"Terrible Tom" <tomg@.gofish.robysfurniture.com> wrote in message
news:eHO$g55QGHA.1772@.TK2MSFTNGP14.phx.gbl...
> My company uses MS Retail Management System, which is SQL-based. Without
> going into a lot of detail about RMS, here's what I need to do: schedule
> a query that will insert a new row into an existing table. Part of the
> data that needs to be inserted is date/time. For example, here's the
> query I need to run:
> INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title,
> FromDate)
> VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
> What I don't know how to do:
> Generate the date fields "Today 6:30PM" and "NOW"
> Schedule the query.
> I've got SQL 2000 (as part of SBS Premium), and I am certain that I have
> the capability to do this. I think I have figured out the scheduling
> part, but I'm hesitant to try it out on a live database. In fact, I think
> I'll try a simpler query on a sample database before I ever go live with
> it.
> Just to be perfectly clear, I'm a whole lot closer to being an SQL newbie
> than an SQL expert.
> Thanks for any advice you might offer,
> Tom
> --
> Stop Fishing For e-Mail
>|||> INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title,
> FromDate)
> VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
> What I don't know how to do:
> Generate the date fields "Today 6:30PM" and "NOW"
> Schedule the query.
DECLARE @.eff SMALLDATETIME, @.from SMALLDATETIME;
SET @.from = CURRENT_TIMESTAMP;
SET @.eff = DATEADD(MINUTE, 30, DATEADD(HOUR, 18, DATEADD(DAY, 0,
DATEDIFF(DAY, 0, @.from))));
INSERT WorkSheet
(
Style,
EffectiveDate,
Status,
Notes,
Title,
FromDate
)
SELECT
250,
@.eff,
2,
'Auto 250',
'Automated Daily Item Update',
@.from;

> I'm hesitant to try it out on a live database. In fact, I think I'll try
> a simpler query on a sample database before I ever go live with it.
Never a bad idea. We test our code on three non-essential environments
before live clients ever hear about it.
A|||Use the ANSI/ISO Standard CURRENT_TIMESTAMP instead of NOW or the old
proprietary getdate().
.|||Why is it called a time "stamp" ?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141927581.261444.223900@.v46g2000cwv.googlegroups.com...
> Use the ANSI/ISO Standard CURRENT_TIMESTAMP instead of NOW or the old
> proprietary getdate().
> .
>

Automatic Trimming Transformation

I would love to see an automatic trimming transformation. We currently have 14 different ERP systems throughout our company and almost everything we do, when it comes to strings, requires trimming. Although doing it through the derived column task is somewhat easier, it is very time consuming and error proned, if you overlook a column or two.

-BradG

Sorry, meant for this post to go under the "MOST Wanted" post.|||

Sounds like an excellent candidate for a custom transform to me :)

-Jamie

Automated client report rendering

My company would like to write an app that does on-demand rendering of
certain reports. Is there a .NET client report rendering library that
is not dependent on IIS (as the SOAP/URL apis are), nor WinForms (as
the ReportViewer is)?
Thanks,
EvaAfter some research I've found one third-party product called the RDL
Project that renders RDL into HTML, PDF, and some other formats. It's
found at http://www.fyiReporting.com. I played with it, and its version
1.0.1 can't yet parse a report that I built in VS2005, although I think
it could if I removed a few unsupported things from the report and if I
studied its code a bit more (documentation seems lacking).
Hope this post helps someone else, and of course if you know of any
other options please let me know!

automated change config notifications

There is a company, Idera http://idera.com/Products/SQLconfig/ that produces
a product called SQLconfig. Among other things, it monitors database server
hardware, operating system (OS), SQL Server instance and database
configuration, providing both automated change notification and comprehensiv
e
reporting on changes in a SQL Server environment
Does anyone know of any VBS or T-SQL that can be used instead? Any info,
links would be appreciated to get started on this project. Thank you.Well www.red-gate.com has a product with a development kit that gives you an
API to do these kinds of things programmatically.
Andrew J. Kelly SQL MVP
"SQLcat" <SQLcat@.discussions.microsoft.com> wrote in message
news:362547A2-43E0-409B-9E82-45D9428E2D9F@.microsoft.com...
> There is a company, Idera http://idera.com/Products/SQLconfig/ that
> produces
> a product called SQLconfig. Among other things, it monitors database
> server
> hardware, operating system (OS), SQL Server instance and database
> configuration, providing both automated change notification and
> comprehensive
> reporting on changes in a SQL Server environment
> Does anyone know of any VBS or T-SQL that can be used instead? Any info,
> links would be appreciated to get started on this project. Thank you.|||I'd also like to get away from trial versions of software. I'd like to be
able to do this in house.
"Andrew J. Kelly" wrote:

> Well www.red-gate.com has a product with a development kit that gives you
an
> API to do these kinds of things programmatically.
> --
> Andrew J. Kelly SQL MVP
>
> "SQLcat" <SQLcat@.discussions.microsoft.com> wrote in message
> news:362547A2-43E0-409B-9E82-45D9428E2D9F@.microsoft.com...
>
>|||Then why not buy the tool? It's only a few hundred dollars. You will waste
that much in your own time in a few hours.
Andrew J. Kelly SQL MVP
"SQLcat" <SQLcat@.discussions.microsoft.com> wrote in message
news:2F604881-2008-4A83-A83D-1F8FC7A2DF52@.microsoft.com...
> I'd also like to get away from trial versions of software. I'd like to be
> able to do this in house.
> "Andrew J. Kelly" wrote:
>|||a few hours I have....
"Andrew J. Kelly" wrote:

> Then why not buy the tool? It's only a few hundred dollars. You will was
te
> that much in your own time in a few hours.
> --
> Andrew J. Kelly SQL MVP
>
> "SQLcat" <SQLcat@.discussions.microsoft.com> wrote in message
> news:2F604881-2008-4A83-A83D-1F8FC7A2DF52@.microsoft.com...
>
>|||So if you are doing this for fun rather than profit why are you asking
for us to show you ... :-)
You'll perhaps want to look at WMI and DMO for this. DMO gives access
to SQL Server objects and allows you to script them. However, DMO is
superceded by SMO in 2005 so now probably isn't a good time to embark
on such a project. One reason why most people don't write system
management tools is because of the cost of keeping up with the
technology.
David Portas
SQL Server MVP
--|||I'll look into the DMO route...we're not going to SQL 2005 anytime soon, so
hopefully SMO won't be an issue for some time. This will "profit" my compan
y
and my ability to keep on top of changes as they occur....good and bad.
Just Google SQL DMO and go from there?
"David Portas" wrote:

> So if you are doing this for fun rather than profit why are you asking
> for us to show you ... :-)
> You'll perhaps want to look at WMI and DMO for this. DMO gives access
> to SQL Server objects and allows you to script them. However, DMO is
> superceded by SMO in 2005 so now probably isn't a good time to embark
> on such a project. One reason why most people don't write system
> management tools is because of the cost of keeping up with the
> technology.
> --
> David Portas
> SQL Server MVP
> --
>