Newbie Question - Please be gentle!

Can I run a script as an automated process?
i.e. I have a simple script that deletes the content of a table so I can
import fresh data, I know how to set up the DTS to import the data
automatically, but I have to run the script every day manually before the DTS
runs & I want the delete script to run at a predefined time rather than
having to remember to do it. Does that make sense?
Tia
Jonathan
Sure. It's hard to give precise directions without know what you're scripts
look like, but... if you already have a DTS job you can easily create a
step in that package (that's what the DTS container is called) that will cun
a TSQL script. Then you can easily schedule that from SQLAgent. You can
right click on the job name from DTS and select 'schedule job' which will
walk you throught the process of setting up the DTS package to run from SQL
agent.
Hope that helps,
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:F7E842B8-BFD6-4922-8ED2-4AD0BCE55CA6@.microsoft.com...
> Can I run a script as an automated process?
> i.e. I have a simple script that deletes the content of a table so I can
> import fresh data, I know how to set up the DTS to import the data
> automatically, but I have to run the script every day manually before the
DTS
> runs & I want the delete script to run at a predefined time rather than
> having to remember to do it. Does that make sense?
> Tia
> Jonathan

Newbie Question - Please be gentle!

Can I run a script as an automated process?
i.e. I have a simple script that deletes the content of a table so I can
import fresh data, I know how to set up the DTS to import the data
automatically, but I have to run the script every day manually before the DT
S
runs & I want the delete script to run at a predefined time rather than
having to remember to do it. Does that make sense?
Tia
JonathanSure. It's hard to give precise directions without know what you're scripts
look like, but... if you already have a DTS job you can easily create a
step in that package (that's what the DTS container is called) that will cun
a TSQL script. Then you can easily schedule that from SQLAgent. You can
right click on the job name from DTS and select 'schedule job' which will
walk you throught the process of setting up the DTS package to run from SQL
agent.
Hope that helps,
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:F7E842B8-BFD6-4922-8ED2-4AD0BCE55CA6@.microsoft.com...
> Can I run a script as an automated process?
> i.e. I have a simple script that deletes the content of a table so I can
> import fresh data, I know how to set up the DTS to import the data
> automatically, but I have to run the script every day manually before the
DTS
> runs & I want the delete script to run at a predefined time rather than
> having to remember to do it. Does that make sense?
> Tia
> Jonathan

Newbie Question - Please be gentle!

Can I run a script as an automated process?
i.e. I have a simple script that deletes the content of a table so I can
import fresh data, I know how to set up the DTS to import the data
automatically, but I have to run the script every day manually before the DTS
runs & I want the delete script to run at a predefined time rather than
having to remember to do it. Does that make sense?
Tia
JonathanSure. It's hard to give precise directions without know what you're scripts
look like, but... if you already have a DTS job you can easily create a
step in that package (that's what the DTS container is called) that will cun
a TSQL script. Then you can easily schedule that from SQLAgent. You can
right click on the job name from DTS and select 'schedule job' which will
walk you throught the process of setting up the DTS package to run from SQL
agent.
Hope that helps,
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:F7E842B8-BFD6-4922-8ED2-4AD0BCE55CA6@.microsoft.com...
> Can I run a script as an automated process?
> i.e. I have a simple script that deletes the content of a table so I can
> import fresh data, I know how to set up the DTS to import the data
> automatically, but I have to run the script every day manually before the
DTS
> runs & I want the delete script to run at a predefined time rather than
> having to remember to do it. Does that make sense?
> Tia
> Jonathan

Newbie needs help with timestamp calculation

I'm a newbie, so please be gentle

I have a table that has a timestamp column that I can reference, e.g.,

SELECT *
FROM mytable
WHERE RECORDTIME < {TS '2006-05-01 00:00:00.000' }

This type of selection works.

What I want to do now is select the rows where the timestamp is less than 30 days prior to the current date instead of hardcoding a timestamp every time. I'm trying to select anything older than 30 days.

SELECT *
FROM myfile
WHERE RECORDTIME < [ current date's timestamp] - 30 days

I don't even know where to start so any help is greatly appreciated.

Thanks in advance,

Robert

I think I have it. This seems to work.

SELECT *
FROM myfile
WHERE RECORDTIME < ( cast( getdate() as datetime) - day(30) )

Please advise if you see anything that would be an issue.

Thanks!

|||

I don't think that it works exactly as the spec 'today less 30 days'.

Consider these two different results:

select getdate() - day(30), getdate()
select dateadd(day, -30, getdate()), getdate()

-- -
2006-04-30 15:44:45.890 2006-05-31 15:44:45.890

(1 row(s) affected)

-- --
2006-05-01 15:44:45.890 2006-05-31 15:44:45.890

(1 row(s) affected)

/Kenneth

Newbie needs help with homework due today

Hi Folks,
Please be gentle as this newbie is in a beginners SQL class and is stuck on the homework assignment. I would be very grateful for any help I can get.

System: MS Access2000

Problem: To write an SQL statement that will write the results of a UNION query to a new table in my database.

Where am I at? I have written the UNION query & it does return the results I expect. When I modify the query (by adding INTO Newtable) to write the result set to the new table, I get an error, "An action query cannot be used as a row source"

Code I'm using:
SELECT Employees_TBL.FirstName, Employees_TBL.LastName, JobTitle_TBL.JobTitle, Employees_TBL.Salary
INTO Newtable
FROM Employees_TBL, JobTitle_TBL
WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode AND JobTitle_TBL.Status = 'Exempt'
UNION
SELECT Employees_TBL.FirstName, Employees_TBL.LastName, JobTitle_TBL.JobTitle, Employees_TBL.Salary
FROM Employees_TBL, JobTitle_TBL
WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode AND JobTitle_TBL.Status = 'Non-exempt';

Additional Info: If I just do one part of the compound query, I can write records to Newtable with no problem.

HEre is what my instructor says on the matter:
I've had some questions about how to integrate the UNION query with the SELECT...INTO statement. So here's some syntax information. I hope it helps.

In simple terms, the syntax for the SELECT ... INTO is

SELECT fieldlist INTO newtablename FROM recordsource

Where fieldlist has the list of new field names for your table. You will need to make sure that the recordsource returns the same number of fields.

newtablename is the name you want the new table to have

recordsource is a valid table or query that returns a valid recordset to match fieldlist. If you are using a query, then you would enclose the query in parentheses. The recordsource could be as complex as needed to get you the records you want to add. It could even be a UNION query!

Example:

SELECT ItemName, LunchPrice INTO LunchMenu
FROM (SELECT EntreeName, ItemCost*2.5 FROM RecipeList WHERE LunchFlag=1)
:( :(

I seem to be having a problem with syntax because the query works without the INTO part and the writing of records works if I don't try to use the UNION SELECT statement.

Any ideas?Just look more closely at the syntax definition/example:

recordsource is a valid table or query that returns a valid recordset to match fieldlist. If you are using a query, then you would enclose the query in parentheses. The recordsource could be as complex as needed to get you the records you want to add. It could even be a UNION query!

Example:

SELECT ItemName, LunchPrice INTO LunchMenu
FROM (SELECT EntreeName, ItemCost*2.5 FROM RecipeList WHERE LunchFlag=1)

So, you could try this:

SELECT * INTO Newtable
FROM (
SELECT Employees_TBL.FirstName, Employees_TBL.LastName
, JobTitle_TBL.JobTitle, Employees_TBL.Salary
FROM Employees_TBL, JobTitle_TBL
WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode
AND JobTitle_TBL.Status = 'Exempt'
UNION
SELECT Employees_TBL.FirstName, Employees_TBL.LastName
, JobTitle_TBL.JobTitle, Employees_TBL.Salary
FROM Employees_TBL, JobTitle_TBL
WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode
AND JobTitle_TBL.Status = 'Non-exempt');

:cool:
DISCLAIMER: This is just a suggestion due to the fact I know very little MS Access! :o

Newbie backup question

Hi, am really new to all of this, so please be gentle. :)

On computer A (the server) I have SBS2k3 with SQL server 2k. On computer B (the workstation) I have MS Office/Access. I have created a couple of access projects on the workstation that have tables, stored procedures, etc.. in a couple of database on the server. Now I need some kind of backup plan.

I backup the Access projects on removable USB drives plugged into the workstation. I'd like to do scheduled backups of the databases on the server and also copy those backups to the USB drives on the workstation.

I've read that I can use Enterprise manager on the server to do scheduled backups to any drive on the server. I figure if I do that to a shared folder on the server that I can then copy that backup via the workstation and save a copy of it to the USB drive on the workstation.

1 - Do you see any problems with this scheme?

2 - Does the backup from enterprise manager really back everything up (table structures, stored procedures, etc...)?

3 - Assuming that I cycle between onsite and offsite USB drives on the workstation, if the place burned down and I had to rebuild the machines from scratch, is the backup saved to the USB drive mentioned above going to contain everything I need to recreate my databases on new blank hardware (after a fresh install of SBS2k3 and SQL server, of course)?there are many ways of doing this.
1. create a shared on the serverA (i.e. c:\shared -> \\serverA\shared)
2. create a sql job to backup your db:
backup database [your_db]
to disk 'c:\shared\your_db.bak'
with init
3. on your workstation, you can just create a windows schedule to connect to \\serverA\shared and copy the backup.

In general, I think it's a bad idea to try to schedule a backup job on the server to backup to your workstation usb drive. This can be done but is not really reliable. You have a regular backup job on the server and do copy/pull of the backup file as needed.|||Thanks.

No, I wasn't planning to schedule a task on the server to backup directly to the workstation's USB drive. I figured I'd do that manually from the workstation, since I don't always have one of the USB drives turned on. But I was planning to do a scheduled backup on the server to a folder on the server. (probably in the middle of the night)

> backup database [your_db]
> to disk 'c:\shared\your_db.bak'
> with init

So, that will backup everything, including the table structures and stored procedures, so the database can be recreated on a different computer in addition to being able to restore on the same one?|||The BAK file should contain everything. However, you shouldn't take anyone here's word for it. Create a backup of "DatabaseName", then take that BAK file and restore it to "NewDatabaseName". See if the restored database contains what you want. You can delete it after that, but then you'll know for sure what you'll get.