Scheduling an SSIS package that invokes a web service

Hey,

I have an SSIS package that invokes a web service and then updates a table. It runs fine as long as I am running it on the local machine. However, as soon as I save this package to the sql server, and try to schedule this as a job, it starts to fail. Now, the web service writes to an xml file and also uses an xsd and and an xsl file. When I save a dts package to the sql server, whats the proper way of referencing these files? I think this probably is what is making the package to fail, ut I am not sure.

Any help is greatly appreciated!!

Thanks!

You should use a configuration (right-click in the package and choose configurations) to set the ConnectionString property of the connection managers for the files. Or you could use expressions to set the connection strings (paths and filenames) based on variables. The variables can be set at runtime using the /SET option of DTEXEC.|||Also make sure you've configured SSIS logging, so you can find out why the package fails now or (once you fix the problem and go to production) if something goes wrong with scheduled package in production.|||That depends on where you want to keep them. I prefer to keep them in files on the disk. If your package is in SQL and you prefer to avoid the disk entirely, you can keep them in the database and just load them into variables via the Execute SQL task. The XML task and the XML Source component support receiving the XSD/XSLT from variables.
|||Thanks a lot for the suggestions. I will try them out and see how it works.|||

Hey,

Sorry for this delayed reply. Since I posted this question a lot of issues cropped up with my SQL server which eventually led to a total reinstallation of all apps on my pc. Anyway, I discovered that the problem I have been having was because of permission issues. I was able to fix that problem and just when I thought that I had everything going, I came across a new problem. After I save the SSIS package in sql server and create a job, the job starts failing. This is the error that I am getting:

-1073548540,0x,An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: Unable to connect to the remote server.

Any suggestions would be very helpful.

Thanks!

|||Could it be a authentication issue? Is there any security on the web service?|||

Guys!!Thanks a lot for all the suggestions. Really appreciate your help. Problem was a combination of many issues. One was related to 32bit/64bit differences, the other was authentication, and finally some syntax problems when invoking the web service. Seems it is working really well now.

Thanks again!

Scheduling a text file to be inserted to a database table

Hi there
New to SQL 2000 and i have a question regarding scheduling a task. I have a
text file that is being downloaded to a web folder from an AS400 system
three times a day. I want to then grab this file and insert and replace the
exisiting records in a database table.
What be the best way to do this? Stored Procedure?
Can you schedule stored procedures to be run at time specific intervals?
Any help appreciatedBJ
DTS Package
"bj" <orders@.seton.net.au> wrote in message
news:OifAtnhUGHA.5808@.TK2MSFTNGP12.phx.gbl...
> Hi there
> New to SQL 2000 and i have a question regarding scheduling a task. I have
> a text file that is being downloaded to a web folder from an AS400 system
> three times a day. I want to then grab this file and insert and replace
> the exisiting records in a database table.
> What be the best way to do this? Stored Procedure?
> Can you schedule stored procedures to be run at time specific intervals?
> Any help appreciated
>
>

schedules web page problem

hi all,

first of all i'm sorry for my bad english, i hope someone understand me.

i have a problem with schedules in reporting services.
i create a new one and i had no problem doing it.
the creation of the schedules make a new job in the sql server.
the job in the sql server works right.
instead the schedule's informartion in the reporting services web page doesn't refresh.
the schedule web page always reports the column "last execution" = never and the "next execution" = the first value.
any subscriptions set on the schedule doesn't work.

above some information about the installation:

- sql\report server is in another intranet. I manage the site from a different intranet.
- i restore on the main server the ReportServer and the ReportServerTempDB from the deployment server. i read the microsoft msdn to restore the db.

hope someone could help me
thanks
nicola

The microsoft article to restore reportserver db is very incomplete.. I do the following:
in ReportServer - Database

EXEC dbo.sp_AlterOwnerOfAgentJob @.username='ASPNET'
GO
EXEC dbo.sp_AlterOwnerOfSubscriptions 'ITEMOwner'
GO

where ASPNET is the assount of iis / application pool and ITEMOwner is a user from your domain. (usually this name has to be fullqualified domain\username)

The stored procedures are:
USE [ReportServer]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_AlterOwnerOfSubscriptions] ( @.username nvarchar(128))
AS
BEGIN
/* ?ndert den Besitzer der Abonnements, da sonst die Fehlermeldung:
"Benutzer XY wurde nicht gefunden"
Beim nachtr?glichen ?ndern eines Abos auftreten kann
*/

SET NOCOUNT ON;
declare @.UserID uniqueidentifier
/* ID aus Benutzertabelle holen */
select top 1 @.UserID=userid from users where username=@.username
if @.UserID is null
begin
print '======================================================='
print 'ERROR: The given user was not found'
print 'Check if''' + @.username + ''' is correct!'
print '======================================================='
end
else
begin
print 'Changing subscription user to: ' + @.username
update dbo.Subscriptions set OwnerID=@.UserID,ModifiedByID=@.UserID,ModifiedDate=getDate();
update dbo.Schedule set CreatedById=@.UserID;
end
END

GO

/* =========================================================================== */

CREATE PROCEDURE [dbo].[sp_AlterOwnerOfAgentJob] ( @.username nvarchar(128))
AS
BEGIN
/*
Diese SP ?ndert den Besitzer der SQL-Server Agent-Jobs, diese werden
f?lschlicherweise auf SYSTEM gesetzt, sollten aber ASPNET sein!
*/
SET NOCOUNT ON;
declare @.jobname nvarchar(128),@.servername nvarchar(128),@.UserID uniqueidentifier
/* Cursor zum auslesen der aktuellen Jobs */
DECLARE job_cursor CURSOR FOR select name from msdb.dbo.sysjobs
OPEN job_cursor

/* Resultset durchgehen und Besitzer jedes Jobs setzen */
FETCH NEXT FROM job_cursor INTO @.jobname
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Changing owner to: ''' + @.username + ''' for job ' + @.jobname
EXEC msdb.dbo.sp_update_job @.job_name=@.jobname,
@.owner_login_name=@.username
FETCH NEXT FROM job_cursor INTO @.jobname
END
/* Resultset / Cursor wieder schlie?en und Freigeben */
CLOSE job_cursor
DEALLOCATE job_cursor
END

GO

/* =========================================================================== */

Usually it works after that.. If not rebooting is a good choice ;) I was trying for 2hours to get the subscriptions work, after rebooting all went fine ;)

scheduled backups with the wizard not running"

Hello all,
I am very new to SQL. We are running SQL 2000. We are using one instance to
run Project Web Access and I need to setup a disaster recovery. I have
donwloaded the disaster recovery from the project server site. I have a
pretty good idea how go about backing up the databases in case the server
melts into a puddle. I have setup syncronized database transaction log
marking to have a consistent recovery point.
The problem I am running into is that I am trying to use the SQL wizard to
backup my databases and transaction logs at night. I have made sure to setup
the schedule transactions, but it wont run at night.
Can someone please shine light on my problem.
Thank,s
Joe Hernandez
Is SQL Agent actually running? By the way it does little good to backup
the log only once a night. Themain purpose of transaction log backups is to
minimize the amount of data loss. If you only backup the logs at night you
run the risk of loosing up to 24 hours worth of changes. You might want to
start with no more than an hour apart for the logs.
Andrew J. Kelly SQL MVP
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
> Hello all,
> I am very new to SQL. We are running SQL 2000. We are using one instance
> to
> run Project Web Access and I need to setup a disaster recovery. I have
> donwloaded the disaster recovery from the project server site. I have a
> pretty good idea how go about backing up the databases in case the server
> melts into a puddle. I have setup syncronized database transaction log
> marking to have a consistent recovery point.
> The problem I am running into is that I am trying to use the SQL wizard to
> backup my databases and transaction logs at night. I have made sure to
> setup
> the schedule transactions, but it wont run at night.
> Can someone please shine light on my problem.
> Thank,s
> Joe Hernandez
|||Andrew,
Thanks for your answer
How do I check that the SQL agent is running?
The reason why I setup the transaction log to backup once a day, is because
I have the automatic log and data marking happening at night, and I am trying
to back up the databases at night as well. If I backup the transaction log
every hour and the actual databases at night, I understood that I can only
restore up to the point where I backed up the database? Ithought too often
backup of the transaction log creates an overhead on the SQL performance.
Please advice
Joe Hernandez
"Andrew J. Kelly" wrote:

> Is SQL Agent actually running? By the way it does little good to backup
> the log only once a night. Themain purpose of transaction log backups is to
> minimize the amount of data loss. If you only backup the logs at night you
> run the risk of loosing up to 24 hours worth of changes. You might want to
> start with no more than an hour apart for the logs.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
>
>
|||You might want to do some more research on your backup strategies. This may
help as well:
http://www.microsoft.com/technet/pro...n/sqlops0.mspx
But here are some general comments about what I see so far. First off I am
not quite sure what you mean by "Mark" the backups. There is nothing
special required to issue a backup in SQL Server other than the BACKUP
command. A FULL backup is required in order to do any restores or even to
do a LOG backup. But doing additional FULL Backups will not in any way
invalidate the LOG backups or the process. They work together. The Log
chain is constant and spans FULL backups. When you need to Restore a DB you
always start from the last FULL backup and then only need to apply the LOG
backups since that one. But the act of doing a FULL backup does not in any
way invalidate the other log backups. Backups in general are very
lightweight, especially the log backups. You should not be afraid to backup
the logs during the day. Actually that is when they are most useful.
To check to see if SQL Agent is running you can open Enterprise Manager and
go to the Management Node. Then when you expand that you will see the SQL
Agent node. If you see a Green arrow it is running. Right click on it to
start or stop it.
Andrew J. Kelly SQL MVP
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...[vbcol=seagreen]
> Andrew,
> Thanks for your answer
> How do I check that the SQL agent is running?
> The reason why I setup the transaction log to backup once a day, is
> because
> I have the automatic log and data marking happening at night, and I am
> trying
> to back up the databases at night as well. If I backup the transaction log
> every hour and the actual databases at night, I understood that I can only
> restore up to the point where I backed up the database? Ithought too often
> backup of the transaction log creates an overhead on the SQL performance.
> Please advice
> Joe Hernandez
> "Andrew J. Kelly" wrote:
|||in the instace of SQL I am running 3 databases for projectwebaccess. one for
sharepoint service, projectserver and one for reporting services.
According to the disaster recovery for project server because I am using 3
different databases they say it is necessary to use marked transactions in
the transaction log of each database to facilitate recovery, I guess the idea
is that by applying marked transactions to all the databases at the same time
I create a consistent point in all databases, they right: "Recovery of
related databases to any time earlier than the point of failure can only be
accomplished by recovering to a marked transaction.
Thanks for the info on the SQL agent, it was not running. I since then
turned it on and was able to run a schedule back up
Joe H
"Andrew J. Kelly" wrote:

> You might want to do some more research on your backup strategies. This may
> help as well:
> http://www.microsoft.com/technet/pro...n/sqlops0.mspx
> But here are some general comments about what I see so far. First off I am
> not quite sure what you mean by "Mark" the backups. There is nothing
> special required to issue a backup in SQL Server other than the BACKUP
> command. A FULL backup is required in order to do any restores or even to
> do a LOG backup. But doing additional FULL Backups will not in any way
> invalidate the LOG backups or the process. They work together. The Log
> chain is constant and spans FULL backups. When you need to Restore a DB you
> always start from the last FULL backup and then only need to apply the LOG
> backups since that one. But the act of doing a FULL backup does not in any
> way invalidate the other log backups. Backups in general are very
> lightweight, especially the log backups. You should not be afraid to backup
> the logs during the day. Actually that is when they are most useful.
> To check to see if SQL Agent is running you can open Enterprise Manager and
> go to the Management Node. Then when you expand that you will see the SQL
> Agent node. If you see a Green arrow it is running. Right click on it to
> start or stop it.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...
>
>
|||Hello,
I think what they are talking about is you may need to keep all your
databases in a consistent state: Meaning if you restore one database and
replay transaction logs to a point and time - you may need to ensure you do
the same on the others to ensure they line up.
Not familiar with Project server so without researching it I can only gather
this is what they mean.
I consult for many, many clients who ALL use SQL - everyone of them I have
set up with full backups at night, and frequent transaction log dumps during
the day to facilitate the best recovery options.
For other clients who demand a little more I set up log shipping; which is a
great DR method and takes only a few hours to set up.
Cheers
Ian
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...[vbcol=seagreen]
> in the instace of SQL I am running 3 databases for projectwebaccess. one
> for
> sharepoint service, projectserver and one for reporting services.
> According to the disaster recovery for project server because I am using 3
> different databases they say it is necessary to use marked transactions in
> the transaction log of each database to facilitate recovery, I guess the
> idea
> is that by applying marked transactions to all the databases at the same
> time
> I create a consistent point in all databases, they right: "Recovery of
> related databases to any time earlier than the point of failure can only
> be
> accomplished by recovering to a marked transaction.
> Thanks for the info on the SQL agent, it was not running. I since then
> turned it on and was able to run a schedule back up
> Joe H
> "Andrew J. Kelly" wrote:
|||If you are updating data across more than one db at a time you should wrap
them in a transaction. Then if you restore to a point in time (nothing
special needed just proper log backups) on each db you should have
consistency between them.
Andrew J. Kelly SQL MVP
"Ian McQuade" <imcquade@.altara.com> wrote in message
news:eomnZE%23CGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I think what they are talking about is you may need to keep all your
> databases in a consistent state: Meaning if you restore one database and
> replay transaction logs to a point and time - you may need to ensure you
> do the same on the others to ensure they line up.
> Not familiar with Project server so without researching it I can only
> gather this is what they mean.
> I consult for many, many clients who ALL use SQL - everyone of them I have
> set up with full backups at night, and frequent transaction log dumps
> during the day to facilitate the best recovery options.
> For other clients who demand a little more I set up log shipping; which is
> a great DR method and takes only a few hours to set up.
> Cheers
> Ian
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...
>
|||Ian,
Thanks for your explanation. So it sounds like I am on the right track that
as long as I keep backups of my databases and transaction log, I should be
able to restore in the event of hardware and/or software malfunction. Good
because I am having a hard time running the automated script to mark all logs
and databases at the same time.
How hard is it to setup log shipping, do you know of a good place to go to
get good notes on that?
thanks for your help
Joe Hernandez
"Ian McQuade" wrote:

> Hello,
> I think what they are talking about is you may need to keep all your
> databases in a consistent state: Meaning if you restore one database and
> replay transaction logs to a point and time - you may need to ensure you do
> the same on the others to ensure they line up.
> Not familiar with Project server so without researching it I can only gather
> this is what they mean.
> I consult for many, many clients who ALL use SQL - everyone of them I have
> set up with full backups at night, and frequent transaction log dumps during
> the day to facilitate the best recovery options.
> For other clients who demand a little more I set up log shipping; which is a
> great DR method and takes only a few hours to set up.
> Cheers
> Ian
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...
>
>

scheduled backups with the wizard not running"

Hello all,
I am very new to SQL. We are running SQL 2000. We are using one instance to
run Project Web Access and I need to setup a disaster recovery. I have
donwloaded the disaster recovery from the project server site. I have a
pretty good idea how go about backing up the databases in case the server
melts into a puddle. I have setup syncronized database transaction log
marking to have a consistent recovery point.
The problem I am running into is that I am trying to use the SQL wizard to
backup my databases and transaction logs at night. I have made sure to setup
the schedule transactions, but it wont run at night.
Can someone please shine light on my problem.
Thank,s
Joe HernandezIs SQL Agent actually running? By the way it does little good to backup
the log only once a night. Themain purpose of transaction log backups is to
minimize the amount of data loss. If you only backup the logs at night you
run the risk of loosing up to 24 hours worth of changes. You might want to
start with no more than an hour apart for the logs.
--
Andrew J. Kelly SQL MVP
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
> Hello all,
> I am very new to SQL. We are running SQL 2000. We are using one instance
> to
> run Project Web Access and I need to setup a disaster recovery. I have
> donwloaded the disaster recovery from the project server site. I have a
> pretty good idea how go about backing up the databases in case the server
> melts into a puddle. I have setup syncronized database transaction log
> marking to have a consistent recovery point.
> The problem I am running into is that I am trying to use the SQL wizard to
> backup my databases and transaction logs at night. I have made sure to
> setup
> the schedule transactions, but it wont run at night.
> Can someone please shine light on my problem.
> Thank,s
> Joe Hernandez|||Andrew,
Thanks for your answer
How do I check that the SQL agent is running?
The reason why I setup the transaction log to backup once a day, is because
I have the automatic log and data marking happening at night, and I am trying
to back up the databases at night as well. If I backup the transaction log
every hour and the actual databases at night, I understood that I can only
restore up to the point where I backed up the database? Ithought too often
backup of the transaction log creates an overhead on the SQL performance.
Please advice
Joe Hernandez
"Andrew J. Kelly" wrote:
> Is SQL Agent actually running? By the way it does little good to backup
> the log only once a night. Themain purpose of transaction log backups is to
> minimize the amount of data loss. If you only backup the logs at night you
> run the risk of loosing up to 24 hours worth of changes. You might want to
> start with no more than an hour apart for the logs.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
> > Hello all,
> >
> > I am very new to SQL. We are running SQL 2000. We are using one instance
> > to
> > run Project Web Access and I need to setup a disaster recovery. I have
> > donwloaded the disaster recovery from the project server site. I have a
> > pretty good idea how go about backing up the databases in case the server
> > melts into a puddle. I have setup syncronized database transaction log
> > marking to have a consistent recovery point.
> >
> > The problem I am running into is that I am trying to use the SQL wizard to
> > backup my databases and transaction logs at night. I have made sure to
> > setup
> > the schedule transactions, but it wont run at night.
> >
> > Can someone please shine light on my problem.
> >
> > Thank,s
> >
> > Joe Hernandez
>
>|||You might want to do some more research on your backup strategies. This may
help as well:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
But here are some general comments about what I see so far. First off I am
not quite sure what you mean by "Mark" the backups. There is nothing
special required to issue a backup in SQL Server other than the BACKUP
command. A FULL backup is required in order to do any restores or even to
do a LOG backup. But doing additional FULL Backups will not in any way
invalidate the LOG backups or the process. They work together. The Log
chain is constant and spans FULL backups. When you need to Restore a DB you
always start from the last FULL backup and then only need to apply the LOG
backups since that one. But the act of doing a FULL backup does not in any
way invalidate the other log backups. Backups in general are very
lightweight, especially the log backups. You should not be afraid to backup
the logs during the day. Actually that is when they are most useful.
To check to see if SQL Agent is running you can open Enterprise Manager and
go to the Management Node. Then when you expand that you will see the SQL
Agent node. If you see a Green arrow it is running. Right click on it to
start or stop it.
--
Andrew J. Kelly SQL MVP
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...
> Andrew,
> Thanks for your answer
> How do I check that the SQL agent is running?
> The reason why I setup the transaction log to backup once a day, is
> because
> I have the automatic log and data marking happening at night, and I am
> trying
> to back up the databases at night as well. If I backup the transaction log
> every hour and the actual databases at night, I understood that I can only
> restore up to the point where I backed up the database? Ithought too often
> backup of the transaction log creates an overhead on the SQL performance.
> Please advice
> Joe Hernandez
> "Andrew J. Kelly" wrote:
>> Is SQL Agent actually running? By the way it does little good to backup
>> the log only once a night. Themain purpose of transaction log backups is
>> to
>> minimize the amount of data loss. If you only backup the logs at night
>> you
>> run the risk of loosing up to 24 hours worth of changes. You might want
>> to
>> start with no more than an hour apart for the logs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
>> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
>> > Hello all,
>> >
>> > I am very new to SQL. We are running SQL 2000. We are using one
>> > instance
>> > to
>> > run Project Web Access and I need to setup a disaster recovery. I have
>> > donwloaded the disaster recovery from the project server site. I have a
>> > pretty good idea how go about backing up the databases in case the
>> > server
>> > melts into a puddle. I have setup syncronized database transaction log
>> > marking to have a consistent recovery point.
>> >
>> > The problem I am running into is that I am trying to use the SQL wizard
>> > to
>> > backup my databases and transaction logs at night. I have made sure to
>> > setup
>> > the schedule transactions, but it wont run at night.
>> >
>> > Can someone please shine light on my problem.
>> >
>> > Thank,s
>> >
>> > Joe Hernandez
>>|||in the instace of SQL I am running 3 databases for projectwebaccess. one for
sharepoint service, projectserver and one for reporting services.
According to the disaster recovery for project server because I am using 3
different databases they say it is necessary to use marked transactions in
the transaction log of each database to facilitate recovery, I guess the idea
is that by applying marked transactions to all the databases at the same time
I create a consistent point in all databases, they right: "Recovery of
related databases to any time earlier than the point of failure can only be
accomplished by recovering to a marked transaction.
Thanks for the info on the SQL agent, it was not running. I since then
turned it on and was able to run a schedule back up
Joe H
"Andrew J. Kelly" wrote:
> You might want to do some more research on your backup strategies. This may
> help as well:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
> But here are some general comments about what I see so far. First off I am
> not quite sure what you mean by "Mark" the backups. There is nothing
> special required to issue a backup in SQL Server other than the BACKUP
> command. A FULL backup is required in order to do any restores or even to
> do a LOG backup. But doing additional FULL Backups will not in any way
> invalidate the LOG backups or the process. They work together. The Log
> chain is constant and spans FULL backups. When you need to Restore a DB you
> always start from the last FULL backup and then only need to apply the LOG
> backups since that one. But the act of doing a FULL backup does not in any
> way invalidate the other log backups. Backups in general are very
> lightweight, especially the log backups. You should not be afraid to backup
> the logs during the day. Actually that is when they are most useful.
> To check to see if SQL Agent is running you can open Enterprise Manager and
> go to the Management Node. Then when you expand that you will see the SQL
> Agent node. If you see a Green arrow it is running. Right click on it to
> start or stop it.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...
> > Andrew,
> > Thanks for your answer
> >
> > How do I check that the SQL agent is running?
> >
> > The reason why I setup the transaction log to backup once a day, is
> > because
> > I have the automatic log and data marking happening at night, and I am
> > trying
> > to back up the databases at night as well. If I backup the transaction log
> > every hour and the actual databases at night, I understood that I can only
> > restore up to the point where I backed up the database? Ithought too often
> > backup of the transaction log creates an overhead on the SQL performance.
> >
> > Please advice
> >
> > Joe Hernandez
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Is SQL Agent actually running? By the way it does little good to backup
> >> the log only once a night. Themain purpose of transaction log backups is
> >> to
> >> minimize the amount of data loss. If you only backup the logs at night
> >> you
> >> run the risk of loosing up to 24 hours worth of changes. You might want
> >> to
> >> start with no more than an hour apart for the logs.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> >> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
> >> > Hello all,
> >> >
> >> > I am very new to SQL. We are running SQL 2000. We are using one
> >> > instance
> >> > to
> >> > run Project Web Access and I need to setup a disaster recovery. I have
> >> > donwloaded the disaster recovery from the project server site. I have a
> >> > pretty good idea how go about backing up the databases in case the
> >> > server
> >> > melts into a puddle. I have setup syncronized database transaction log
> >> > marking to have a consistent recovery point.
> >> >
> >> > The problem I am running into is that I am trying to use the SQL wizard
> >> > to
> >> > backup my databases and transaction logs at night. I have made sure to
> >> > setup
> >> > the schedule transactions, but it wont run at night.
> >> >
> >> > Can someone please shine light on my problem.
> >> >
> >> > Thank,s
> >> >
> >> > Joe Hernandez
> >>
> >>
> >>
>
>|||Hello,
I think what they are talking about is you may need to keep all your
databases in a consistent state: Meaning if you restore one database and
replay transaction logs to a point and time - you may need to ensure you do
the same on the others to ensure they line up.
Not familiar with Project server so without researching it I can only gather
this is what they mean.
I consult for many, many clients who ALL use SQL - everyone of them I have
set up with full backups at night, and frequent transaction log dumps during
the day to facilitate the best recovery options.
For other clients who demand a little more I set up log shipping; which is a
great DR method and takes only a few hours to set up.
Cheers
Ian
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...
> in the instace of SQL I am running 3 databases for projectwebaccess. one
> for
> sharepoint service, projectserver and one for reporting services.
> According to the disaster recovery for project server because I am using 3
> different databases they say it is necessary to use marked transactions in
> the transaction log of each database to facilitate recovery, I guess the
> idea
> is that by applying marked transactions to all the databases at the same
> time
> I create a consistent point in all databases, they right: "Recovery of
> related databases to any time earlier than the point of failure can only
> be
> accomplished by recovering to a marked transaction.
> Thanks for the info on the SQL agent, it was not running. I since then
> turned it on and was able to run a schedule back up
> Joe H
> "Andrew J. Kelly" wrote:
>> You might want to do some more research on your backup strategies. This
>> may
>> help as well:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
>> But here are some general comments about what I see so far. First off I
>> am
>> not quite sure what you mean by "Mark" the backups. There is nothing
>> special required to issue a backup in SQL Server other than the BACKUP
>> command. A FULL backup is required in order to do any restores or even
>> to
>> do a LOG backup. But doing additional FULL Backups will not in any way
>> invalidate the LOG backups or the process. They work together. The Log
>> chain is constant and spans FULL backups. When you need to Restore a DB
>> you
>> always start from the last FULL backup and then only need to apply the
>> LOG
>> backups since that one. But the act of doing a FULL backup does not in
>> any
>> way invalidate the other log backups. Backups in general are very
>> lightweight, especially the log backups. You should not be afraid to
>> backup
>> the logs during the day. Actually that is when they are most useful.
>> To check to see if SQL Agent is running you can open Enterprise Manager
>> and
>> go to the Management Node. Then when you expand that you will see the
>> SQL
>> Agent node. If you see a Green arrow it is running. Right click on it to
>> start or stop it.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
>> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...
>> > Andrew,
>> > Thanks for your answer
>> >
>> > How do I check that the SQL agent is running?
>> >
>> > The reason why I setup the transaction log to backup once a day, is
>> > because
>> > I have the automatic log and data marking happening at night, and I am
>> > trying
>> > to back up the databases at night as well. If I backup the transaction
>> > log
>> > every hour and the actual databases at night, I understood that I can
>> > only
>> > restore up to the point where I backed up the database? Ithought too
>> > often
>> > backup of the transaction log creates an overhead on the SQL
>> > performance.
>> >
>> > Please advice
>> >
>> > Joe Hernandez
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Is SQL Agent actually running? By the way it does little good to
>> >> backup
>> >> the log only once a night. Themain purpose of transaction log backups
>> >> is
>> >> to
>> >> minimize the amount of data loss. If you only backup the logs at
>> >> night
>> >> you
>> >> run the risk of loosing up to 24 hours worth of changes. You might
>> >> want
>> >> to
>> >> start with no more than an hour apart for the logs.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
>> >> > Hello all,
>> >> >
>> >> > I am very new to SQL. We are running SQL 2000. We are using one
>> >> > instance
>> >> > to
>> >> > run Project Web Access and I need to setup a disaster recovery. I
>> >> > have
>> >> > donwloaded the disaster recovery from the project server site. I
>> >> > have a
>> >> > pretty good idea how go about backing up the databases in case the
>> >> > server
>> >> > melts into a puddle. I have setup syncronized database transaction
>> >> > log
>> >> > marking to have a consistent recovery point.
>> >> >
>> >> > The problem I am running into is that I am trying to use the SQL
>> >> > wizard
>> >> > to
>> >> > backup my databases and transaction logs at night. I have made sure
>> >> > to
>> >> > setup
>> >> > the schedule transactions, but it wont run at night.
>> >> >
>> >> > Can someone please shine light on my problem.
>> >> >
>> >> > Thank,s
>> >> >
>> >> > Joe Hernandez
>> >>
>> >>
>> >>
>>|||If you are updating data across more than one db at a time you should wrap
them in a transaction. Then if you restore to a point in time (nothing
special needed just proper log backups) on each db you should have
consistency between them.
--
Andrew J. Kelly SQL MVP
"Ian McQuade" <imcquade@.altara.com> wrote in message
news:eomnZE%23CGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I think what they are talking about is you may need to keep all your
> databases in a consistent state: Meaning if you restore one database and
> replay transaction logs to a point and time - you may need to ensure you
> do the same on the others to ensure they line up.
> Not familiar with Project server so without researching it I can only
> gather this is what they mean.
> I consult for many, many clients who ALL use SQL - everyone of them I have
> set up with full backups at night, and frequent transaction log dumps
> during the day to facilitate the best recovery options.
> For other clients who demand a little more I set up log shipping; which is
> a great DR method and takes only a few hours to set up.
> Cheers
> Ian
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...
>> in the instace of SQL I am running 3 databases for projectwebaccess. one
>> for
>> sharepoint service, projectserver and one for reporting services.
>> According to the disaster recovery for project server because I am using
>> 3
>> different databases they say it is necessary to use marked transactions
>> in
>> the transaction log of each database to facilitate recovery, I guess the
>> idea
>> is that by applying marked transactions to all the databases at the same
>> time
>> I create a consistent point in all databases, they right: "Recovery of
>> related databases to any time earlier than the point of failure can only
>> be
>> accomplished by recovering to a marked transaction.
>> Thanks for the info on the SQL agent, it was not running. I since then
>> turned it on and was able to run a schedule back up
>> Joe H
>> "Andrew J. Kelly" wrote:
>> You might want to do some more research on your backup strategies. This
>> may
>> help as well:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
>> But here are some general comments about what I see so far. First off I
>> am
>> not quite sure what you mean by "Mark" the backups. There is nothing
>> special required to issue a backup in SQL Server other than the BACKUP
>> command. A FULL backup is required in order to do any restores or even
>> to
>> do a LOG backup. But doing additional FULL Backups will not in any way
>> invalidate the LOG backups or the process. They work together. The Log
>> chain is constant and spans FULL backups. When you need to Restore a DB
>> you
>> always start from the last FULL backup and then only need to apply the
>> LOG
>> backups since that one. But the act of doing a FULL backup does not in
>> any
>> way invalidate the other log backups. Backups in general are very
>> lightweight, especially the log backups. You should not be afraid to
>> backup
>> the logs during the day. Actually that is when they are most useful.
>> To check to see if SQL Agent is running you can open Enterprise Manager
>> and
>> go to the Management Node. Then when you expand that you will see the
>> SQL
>> Agent node. If you see a Green arrow it is running. Right click on it
>> to
>> start or stop it.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in
>> message
>> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...
>> > Andrew,
>> > Thanks for your answer
>> >
>> > How do I check that the SQL agent is running?
>> >
>> > The reason why I setup the transaction log to backup once a day, is
>> > because
>> > I have the automatic log and data marking happening at night, and I am
>> > trying
>> > to back up the databases at night as well. If I backup the transaction
>> > log
>> > every hour and the actual databases at night, I understood that I can
>> > only
>> > restore up to the point where I backed up the database? Ithought too
>> > often
>> > backup of the transaction log creates an overhead on the SQL
>> > performance.
>> >
>> > Please advice
>> >
>> > Joe Hernandez
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Is SQL Agent actually running? By the way it does little good to
>> >> backup
>> >> the log only once a night. Themain purpose of transaction log
>> >> backups is
>> >> to
>> >> minimize the amount of data loss. If you only backup the logs at
>> >> night
>> >> you
>> >> run the risk of loosing up to 24 hours worth of changes. You might
>> >> want
>> >> to
>> >> start with no more than an hour apart for the logs.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
>> >> > Hello all,
>> >> >
>> >> > I am very new to SQL. We are running SQL 2000. We are using one
>> >> > instance
>> >> > to
>> >> > run Project Web Access and I need to setup a disaster recovery. I
>> >> > have
>> >> > donwloaded the disaster recovery from the project server site. I
>> >> > have a
>> >> > pretty good idea how go about backing up the databases in case the
>> >> > server
>> >> > melts into a puddle. I have setup syncronized database transaction
>> >> > log
>> >> > marking to have a consistent recovery point.
>> >> >
>> >> > The problem I am running into is that I am trying to use the SQL
>> >> > wizard
>> >> > to
>> >> > backup my databases and transaction logs at night. I have made sure
>> >> > to
>> >> > setup
>> >> > the schedule transactions, but it wont run at night.
>> >> >
>> >> > Can someone please shine light on my problem.
>> >> >
>> >> > Thank,s
>> >> >
>> >> > Joe Hernandez
>> >>
>> >>
>> >>
>>
>|||Ian,
Thanks for your explanation. So it sounds like I am on the right track that
as long as I keep backups of my databases and transaction log, I should be
able to restore in the event of hardware and/or software malfunction. Good
because I am having a hard time running the automated script to mark all logs
and databases at the same time.
How hard is it to setup log shipping, do you know of a good place to go to
get good notes on that?
thanks for your help
Joe Hernandez
"Ian McQuade" wrote:
> Hello,
> I think what they are talking about is you may need to keep all your
> databases in a consistent state: Meaning if you restore one database and
> replay transaction logs to a point and time - you may need to ensure you do
> the same on the others to ensure they line up.
> Not familiar with Project server so without researching it I can only gather
> this is what they mean.
> I consult for many, many clients who ALL use SQL - everyone of them I have
> set up with full backups at night, and frequent transaction log dumps during
> the day to facilitate the best recovery options.
> For other clients who demand a little more I set up log shipping; which is a
> great DR method and takes only a few hours to set up.
> Cheers
> Ian
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...
> > in the instace of SQL I am running 3 databases for projectwebaccess. one
> > for
> > sharepoint service, projectserver and one for reporting services.
> > According to the disaster recovery for project server because I am using 3
> > different databases they say it is necessary to use marked transactions in
> > the transaction log of each database to facilitate recovery, I guess the
> > idea
> > is that by applying marked transactions to all the databases at the same
> > time
> > I create a consistent point in all databases, they right: "Recovery of
> > related databases to any time earlier than the point of failure can only
> > be
> > accomplished by recovering to a marked transaction.
> >
> > Thanks for the info on the SQL agent, it was not running. I since then
> > turned it on and was able to run a schedule back up
> >
> > Joe H
> >
> > "Andrew J. Kelly" wrote:
> >
> >> You might want to do some more research on your backup strategies. This
> >> may
> >> help as well:
> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
> >>
> >> But here are some general comments about what I see so far. First off I
> >> am
> >> not quite sure what you mean by "Mark" the backups. There is nothing
> >> special required to issue a backup in SQL Server other than the BACKUP
> >> command. A FULL backup is required in order to do any restores or even
> >> to
> >> do a LOG backup. But doing additional FULL Backups will not in any way
> >> invalidate the LOG backups or the process. They work together. The Log
> >> chain is constant and spans FULL backups. When you need to Restore a DB
> >> you
> >> always start from the last FULL backup and then only need to apply the
> >> LOG
> >> backups since that one. But the act of doing a FULL backup does not in
> >> any
> >> way invalidate the other log backups. Backups in general are very
> >> lightweight, especially the log backups. You should not be afraid to
> >> backup
> >> the logs during the day. Actually that is when they are most useful.
> >>
> >> To check to see if SQL Agent is running you can open Enterprise Manager
> >> and
> >> go to the Management Node. Then when you expand that you will see the
> >> SQL
> >> Agent node. If you see a Green arrow it is running. Right click on it to
> >> start or stop it.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> >> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...
> >> > Andrew,
> >> > Thanks for your answer
> >> >
> >> > How do I check that the SQL agent is running?
> >> >
> >> > The reason why I setup the transaction log to backup once a day, is
> >> > because
> >> > I have the automatic log and data marking happening at night, and I am
> >> > trying
> >> > to back up the databases at night as well. If I backup the transaction
> >> > log
> >> > every hour and the actual databases at night, I understood that I can
> >> > only
> >> > restore up to the point where I backed up the database? Ithought too
> >> > often
> >> > backup of the transaction log creates an overhead on the SQL
> >> > performance.
> >> >
> >> > Please advice
> >> >
> >> > Joe Hernandez
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Is SQL Agent actually running? By the way it does little good to
> >> >> backup
> >> >> the log only once a night. Themain purpose of transaction log backups
> >> >> is
> >> >> to
> >> >> minimize the amount of data loss. If you only backup the logs at
> >> >> night
> >> >> you
> >> >> run the risk of loosing up to 24 hours worth of changes. You might
> >> >> want
> >> >> to
> >> >> start with no more than an hour apart for the logs.
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >>
> >> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
> >> >> > Hello all,
> >> >> >
> >> >> > I am very new to SQL. We are running SQL 2000. We are using one
> >> >> > instance
> >> >> > to
> >> >> > run Project Web Access and I need to setup a disaster recovery. I
> >> >> > have
> >> >> > donwloaded the disaster recovery from the project server site. I
> >> >> > have a
> >> >> > pretty good idea how go about backing up the databases in case the
> >> >> > server
> >> >> > melts into a puddle. I have setup syncronized database transaction
> >> >> > log
> >> >> > marking to have a consistent recovery point.
> >> >> >
> >> >> > The problem I am running into is that I am trying to use the SQL
> >> >> > wizard
> >> >> > to
> >> >> > backup my databases and transaction logs at night. I have made sure
> >> >> > to
> >> >> > setup
> >> >> > the schedule transactions, but it wont run at night.
> >> >> >
> >> >> > Can someone please shine light on my problem.
> >> >> >
> >> >> > Thank,s
> >> >> >
> >> >> > Joe Hernandez
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

scheduled backups with the wizard not running"

Hello all,
I am very new to SQL. We are running SQL 2000. We are using one instance to
run Project Web Access and I need to setup a disaster recovery. I have
donwloaded the disaster recovery from the project server site. I have a
pretty good idea how go about backing up the databases in case the server
melts into a puddle. I have setup syncronized database transaction log
marking to have a consistent recovery point.
The problem I am running into is that I am trying to use the SQL wizard to
backup my databases and transaction logs at night. I have made sure to setup
the schedule transactions, but it wont run at night.
Can someone please shine light on my problem.
Thank,s
Joe HernandezIs SQL Agent actually running? By the way it does little good to backup
the log only once a night. Themain purpose of transaction log backups is to
minimize the amount of data loss. If you only backup the logs at night you
run the risk of loosing up to 24 hours worth of changes. You might want to
start with no more than an hour apart for the logs.
Andrew J. Kelly SQL MVP
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
> Hello all,
> I am very new to SQL. We are running SQL 2000. We are using one instance
> to
> run Project Web Access and I need to setup a disaster recovery. I have
> donwloaded the disaster recovery from the project server site. I have a
> pretty good idea how go about backing up the databases in case the server
> melts into a puddle. I have setup syncronized database transaction log
> marking to have a consistent recovery point.
> The problem I am running into is that I am trying to use the SQL wizard to
> backup my databases and transaction logs at night. I have made sure to
> setup
> the schedule transactions, but it wont run at night.
> Can someone please shine light on my problem.
> Thank,s
> Joe Hernandez|||Andrew,
Thanks for your answer
How do I check that the SQL agent is running?
The reason why I setup the transaction log to backup once a day, is because
I have the automatic log and data marking happening at night, and I am tryin
g
to back up the databases at night as well. If I backup the transaction log
every hour and the actual databases at night, I understood that I can only
restore up to the point where I backed up the database? Ithought too often
backup of the transaction log creates an overhead on the SQL performance.
Please advice
Joe Hernandez
"Andrew J. Kelly" wrote:

> Is SQL Agent actually running? By the way it does little good to backup
> the log only once a night. Themain purpose of transaction log backups is
to
> minimize the amount of data loss. If you only backup the logs at night yo
u
> run the risk of loosing up to 24 hours worth of changes. You might want t
o
> start with no more than an hour apart for the logs.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
>
>|||You might want to do some more research on your backup strategies. This may
help as well:
http://www.microsoft.com/technet/pr...in/sqlops0.mspx
But here are some general comments about what I see so far. First off I am
not quite sure what you mean by "Mark" the backups. There is nothing
special required to issue a backup in SQL Server other than the BACKUP
command. A FULL backup is required in order to do any restores or even to
do a LOG backup. But doing additional FULL Backups will not in any way
invalidate the LOG backups or the process. They work together. The Log
chain is constant and spans FULL backups. When you need to Restore a DB you
always start from the last FULL backup and then only need to apply the LOG
backups since that one. But the act of doing a FULL backup does not in any
way invalidate the other log backups. Backups in general are very
lightweight, especially the log backups. You should not be afraid to backup
the logs during the day. Actually that is when they are most useful.
To check to see if SQL Agent is running you can open Enterprise Manager and
go to the Management Node. Then when you expand that you will see the SQL
Agent node. If you see a Green arrow it is running. Right click on it to
start or stop it.
Andrew J. Kelly SQL MVP
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...[vbcol=seagreen]
> Andrew,
> Thanks for your answer
> How do I check that the SQL agent is running?
> The reason why I setup the transaction log to backup once a day, is
> because
> I have the automatic log and data marking happening at night, and I am
> trying
> to back up the databases at night as well. If I backup the transaction log
> every hour and the actual databases at night, I understood that I can only
> restore up to the point where I backed up the database? Ithought too often
> backup of the transaction log creates an overhead on the SQL performance.
> Please advice
> Joe Hernandez
> "Andrew J. Kelly" wrote:
>|||in the instace of SQL I am running 3 databases for projectwebaccess. one for
sharepoint service, projectserver and one for reporting services.
According to the disaster recovery for project server because I am using 3
different databases they say it is necessary to use marked transactions in
the transaction log of each database to facilitate recovery, I guess the ide
a
is that by applying marked transactions to all the databases at the same tim
e
I create a consistent point in all databases, they right: "Recovery of
related databases to any time earlier than the point of failure can only be
accomplished by recovering to a marked transaction.
Thanks for the info on the SQL agent, it was not running. I since then
turned it on and was able to run a schedule back up
Joe H
"Andrew J. Kelly" wrote:

> You might want to do some more research on your backup strategies. This ma
y
> help as well:
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx[/url
]
> But here are some general comments about what I see so far. First off I a
m
> not quite sure what you mean by "Mark" the backups. There is nothing
> special required to issue a backup in SQL Server other than the BACKUP
> command. A FULL backup is required in order to do any restores or even to
> do a LOG backup. But doing additional FULL Backups will not in any way
> invalidate the LOG backups or the process. They work together. The Log
> chain is constant and spans FULL backups. When you need to Restore a DB y
ou
> always start from the last FULL backup and then only need to apply the LOG
> backups since that one. But the act of doing a FULL backup does not in an
y
> way invalidate the other log backups. Backups in general are very
> lightweight, especially the log backups. You should not be afraid to back
up
> the logs during the day. Actually that is when they are most useful.
> To check to see if SQL Agent is running you can open Enterprise Manager an
d
> go to the Management Node. Then when you expand that you will see the SQL
> Agent node. If you see a Green arrow it is running. Right click on it to
> start or stop it.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...
>
>|||Hello,
I think what they are talking about is you may need to keep all your
databases in a consistent state: Meaning if you restore one database and
replay transaction logs to a point and time - you may need to ensure you do
the same on the others to ensure they line up.
Not familiar with Project server so without researching it I can only gather
this is what they mean.
I consult for many, many clients who ALL use SQL - everyone of them I have
set up with full backups at night, and frequent transaction log dumps during
the day to facilitate the best recovery options.
For other clients who demand a little more I set up log shipping; which is a
great DR method and takes only a few hours to set up.
Cheers
Ian
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...[vbcol=seagreen]
> in the instace of SQL I am running 3 databases for projectwebaccess. one
> for
> sharepoint service, projectserver and one for reporting services.
> According to the disaster recovery for project server because I am using 3
> different databases they say it is necessary to use marked transactions in
> the transaction log of each database to facilitate recovery, I guess the
> idea
> is that by applying marked transactions to all the databases at the same
> time
> I create a consistent point in all databases, they right: "Recovery of
> related databases to any time earlier than the point of failure can only
> be
> accomplished by recovering to a marked transaction.
> Thanks for the info on the SQL agent, it was not running. I since then
> turned it on and was able to run a schedule back up
> Joe H
> "Andrew J. Kelly" wrote:
>|||If you are updating data across more than one db at a time you should wrap
them in a transaction. Then if you restore to a point in time (nothing
special needed just proper log backups) on each db you should have
consistency between them.
Andrew J. Kelly SQL MVP
"Ian McQuade" <imcquade@.altara.com> wrote in message
news:eomnZE%23CGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I think what they are talking about is you may need to keep all your
> databases in a consistent state: Meaning if you restore one database and
> replay transaction logs to a point and time - you may need to ensure you
> do the same on the others to ensure they line up.
> Not familiar with Project server so without researching it I can only
> gather this is what they mean.
> I consult for many, many clients who ALL use SQL - everyone of them I have
> set up with full backups at night, and frequent transaction log dumps
> during the day to facilitate the best recovery options.
> For other clients who demand a little more I set up log shipping; which is
> a great DR method and takes only a few hours to set up.
> Cheers
> Ian
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...
>|||Ian,
Thanks for your explanation. So it sounds like I am on the right track that
as long as I keep backups of my databases and transaction log, I should be
able to restore in the event of hardware and/or software malfunction. Good
because I am having a hard time running the automated script to mark all log
s
and databases at the same time.
How hard is it to setup log shipping, do you know of a good place to go to
get good notes on that?
thanks for your help
Joe Hernandez
"Ian McQuade" wrote:

> Hello,
> I think what they are talking about is you may need to keep all your
> databases in a consistent state: Meaning if you restore one database and
> replay transaction logs to a point and time - you may need to ensure you d
o
> the same on the others to ensure they line up.
> Not familiar with Project server so without researching it I can only gath
er
> this is what they mean.
> I consult for many, many clients who ALL use SQL - everyone of them I have
> set up with full backups at night, and frequent transaction log dumps duri
ng
> the day to facilitate the best recovery options.
> For other clients who demand a little more I set up log shipping; which is
a
> great DR method and takes only a few hours to set up.
> Cheers
> Ian
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...
>
>

Schedule the integration packages from the web application

Hi all,

I have developed some SSIS packages and scheduling them by using the SQL Server Agent.

But now I want to change all the scheduling from my web application.

is there anyone could help me this?

Do you mean that you want to alter the schedule? If so, this is not an appropriate question for the SSIS forum. The schedule is defined in SQL Server Agent, NOT SSIS.

I do not know if there is an API available for Agent. Try the SMO forum: https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1

-Jamie

schedule into excel directly

Is it possible to schedule into excel directly, without choosing excel and
exporting once the report has been delployed onto the web in reporting
services.
--
shelYou can access the Excel-file via URL.
There may be other possibilities.
Ex:
http://server/ReportServer?%2fReportSolution%2fOperationalReport&datebegin=01.01.2004+00%3a00%3a00&dateend=01.01.2005+00%3a00%3a00&systemid=1&rs%3aCommand=Render&rs%3AFormat=EXCEL
You can find out the URL by copying it when u export the report as excel file.
Ya
"shel" wrote:
> Is it possible to schedule into excel directly, without choosing excel and
> exporting once the report has been delployed onto the web in reporting
> services.
> --
> shel|||--
shel
"Y. Adams" wrote:
> You can access the Excel-file via URL.
> There may be other possibilities.
> Ex:
> http://server/ReportServer?%2fReportSolution%2fOperationalReport&datebegin=01.01.2004+00%3a00%3a00&dateend=01.01.2005+00%3a00%3a00&systemid=1&rs%3aCommand=Render&rs%3AFormat=EXCEL
> You can find out the URL by copying it when u export the report as excel file.
> Ya
>
> "shel" wrote:
> > Is it possible to schedule into excel directly, without choosing excel and
> > exporting once the report has been delployed onto the web in reporting
> > services.
> > --
> > shel
Thanks for this - I have also worked out how to use the subscriptions.

Schedule DTS Package

I have MS SQL installed on my workstation at work. I am trying to use DTS to export data from our local network that uses a Pervasive DB to our web server that is hosted with another company.

If I go in and manually execute the DTS package from my workstation, it send the data to the web server.

If I try to schedule the DTS Package to automatically send the data, it fails. SQL Server Agent is running on my workstation and on the web server.

Is what I am trying to do possible? What am I doing wrong?This is a very frequent problem and it is related to permissions.

This is a KB article that describes the problem and the solution:INF: How to Run a DTS Package as a Scheduled Job

Terri

Schedule a Stored Procedure to run overnight

What I want to do is to create a Web Interface to allow a User to click a
link and execute a Stored Procedure, I think this would be straight forward.
What I also want to do is to allow the user to select a check box to have
that stored procedure run over night or even schedule the Stored procedures
to run at differnet times.
through E-SQL I would just set up an Agent to do the job, the User won't
have that privelage they will have to go through a web page.
I am not even sure what approach to take? Is there a way to setup an agent
to run via stored procedures so through my code I can just call a system
stored procedure that will create the agent and schedule the job?
Thanks
SteveCreate a queue Table to hold the requests by users for Jobs to run, and a SQ
L
Agent Job to run every tem minutes, or whatever, to read the records out of
that table, and execute them if the current system datetime has passed the
datetime as stored in the queue table.. Then delete the queue table record
(or, - better, flag it as completed, when the job has run successfully.
"Steve K" wrote:

> What I want to do is to create a Web Interface to allow a User to click a
> link and execute a Stored Procedure, I think this would be straight forwar
d.
> What I also want to do is to allow the user to select a check box to have
> that stored procedure run over night or even schedule the Stored procedure
s
> to run at differnet times.
> through E-SQL I would just set up an Agent to do the job, the User won't
> have that privelage they will have to go through a web page.
> I am not even sure what approach to take? Is there a way to setup an agent
> to run via stored procedures so through my code I can just call a system
> stored procedure that will create the agent and schedule the job?
> Thanks
> Steve|||See sp_add_job, sp_add_jobstep, sp_add_jobschedule, etc. in BOL.
AMB
"Steve K" wrote:

> What I want to do is to create a Web Interface to allow a User to click a
> link and execute a Stored Procedure, I think this would be straight forwar
d.
> What I also want to do is to allow the user to select a check box to have
> that stored procedure run over night or even schedule the Stored procedure
s
> to run at differnet times.
> through E-SQL I would just set up an Agent to do the job, the User won't
> have that privelage they will have to go through a web page.
> I am not even sure what approach to take? Is there a way to setup an agent
> to run via stored procedures so through my code I can just call a system
> stored procedure that will create the agent and schedule the job?
> Thanks
> Steve|||a Queue table is not a bad idea, but I would have to write the name of a
Stored Procedure in a field and then have the agent read that name and
execute the named procedure - I am not sure how to do that but I like the
thought I will look into it.
"CBretana" wrote:
> Create a queue Table to hold the requests by users for Jobs to run, and a
SQL
> Agent Job to run every tem minutes, or whatever, to read the records out o
f
> that table, and execute them if the current system datetime has passed the
> datetime as stored in the queue table.. Then delete the queue table record
> (or, - better, flag it as completed, when the job has run successfully.
> "Steve K" wrote:
>|||Thanks I will look up these sp's and see how to use them
"Alejandro Mesa" wrote:
> See sp_add_job, sp_add_jobstep, sp_add_jobschedule, etc. in BOL.
>
> AMB
> "Steve K" wrote:
>|||Steve,
After some research, Alej's idea is much simpler. (use sp_add_job)
I discovered you can even add the job with a flag set (@.delete_level =
3) that will cause the job to execute once and then delete iteself, which wa
s
the main reason I would have rolled-my-own system to do what you are trying
to do...
"Steve K" wrote:

> What I want to do is to create a Web Interface to allow a User to click a
> link and execute a Stored Procedure, I think this would be straight forwar
d.
> What I also want to do is to allow the user to select a check box to have
> that stored procedure run over night or even schedule the Stored procedure
s
> to run at differnet times.
> through E-SQL I would just set up an Agent to do the job, the User won't
> have that privelage they will have to go through a web page.
> I am not even sure what approach to take? Is there a way to setup an agent
> to run via stored procedures so through my code I can just call a system
> stored procedure that will create the agent and schedule the job?
> Thanks
> Steve|||I think there is a disconnect here. The solution that Charly is recommending
involves writing a record into a queue table and then having a scheduled job
execute the stored proc using the queue value to determine the data on which
to
act. The job would have the stored proc that should be executed hard coded i
nto
its list of steps.
You aren't asking to execute any stored proc in the system like right?
Thomas
"Steve K" <SteveK@.discussions.microsoft.com> wrote in message
news:0C18A082-88DF-4924-B65B-B4AB13EAEF55@.microsoft.com...
>a Queue table is not a bad idea, but I would have to write the name of a
> Stored Procedure in a field and then have the agent read that name and
> execute the named procedure - I am not sure how to do that but I like the
> thought I will look into it.
> "CBretana" wrote:
>|||sounds good that's the direction I am going to run.
Thanks
Steve
"CBretana" wrote:
> Steve,
> After some research, Alej's idea is much simpler. (use sp_add_job)
> I discovered you can even add the job with a flag set (@.delete_level =
> 3) that will cause the job to execute once and then delete iteself, which
was
> the main reason I would have rolled-my-own system to do what you are tryin
g
> to do...
> "Steve K" wrote:
>|||No, I would have a seperate table that contains a list of avalable stored
procedured users can run on demand from this table I would probably just
throw a grid on the screen with an execute link and allow the user to select
execute, the difficult part here is if the user wants to schedule that job t
o
run overnight then I would need some way to know which Sp they selected and
what time to run it.
does that make sense?
"Thomas Coleman" wrote:

> I think there is a disconnect here. The solution that Charly is recommendi
ng
> involves writing a record into a queue table and then having a scheduled j
ob
> execute the stored proc using the queue value to determine the data on whi
ch to
> act. The job would have the stored proc that should be executed hard coded
into
> its list of steps.
> You aren't asking to execute any stored proc in the system like right?
>
> Thomas
>
> "Steve K" <SteveK@.discussions.microsoft.com> wrote in message
> news:0C18A082-88DF-4924-B65B-B4AB13EAEF55@.microsoft.com...
>
>|||Do I understand what you are planning on doing? Yes, I think so. Do I unders
tand
why you are doing it? No, but that may not be important ;->
I'm generally inclined to abstract that functionality provided to users away
from the means by which it is implemented. Thus, I wouldn't have users selec
ting
stored procedures per se and it may be the case that you are not either. Sin
ce
I'm presuming that the set of functionality provided to the user is finite,
I'd
be inclined to either make multiple queues and multiple jobs for each stored
proc or perhaps a single queue and multiple jobs for each stored proc. Users
would be presented with a list of actions to perform such that behind the sc
enes
would be a mapping of actions to stored procedures. If the user asked for a
scheduled action, the system would map that request to a post into the
appropriate queue table. That queue table would store the time and day that
the
stored procedure should be executed and the scheduled job would be set to po
ll
continuously (say every minute or every five minutes) to determine if there
is
work to do.
However, since I don't know the specifics of the problem you are attempting
to
solve, I may be completely off the mark.
Thomas
"Steve K" <SteveK@.discussions.microsoft.com> wrote in message
news:B4674B29-52CA-4320-9408-6C47600FEBB7@.microsoft.com...
> No, I would have a seperate table that contains a list of avalable stored
> procedured users can run on demand from this table I would probably just
> throw a grid on the screen with an execute link and allow the user to sele
ct
> execute, the difficult part here is if the user wants to schedule that job
to
> run overnight then I would need some way to know which Sp they selected an
d
> what time to run it.
> does that make sense?
> "Thomas Coleman" wrote:
>

Schedule a scripted restore on 2005 Express from .bak file.

Hi i have an web app demo that allows users to add info and change attributes within a SQL 2005 Express DB. I'd like to restore a clean copy of this database every couple of hours from a .bak file using a Windows scheduled task on the server. Has anyone got a .sql script for database restoration that i could use and call using a .cmd script file? Thanks.

Hi TheGrox,

Generally speaking the sql restoration script is like this:

RESTORE DATABASE [Your_databasename] FROM DISK = N'File_Path\BCKUP.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO

However, I don't think it can be used from a .cmd script file. To run .sql script file, you need to run it within sql server query analyzer (if you run it from a .cmd script, first it will jump up a window asking your to login to sql server, even though you enter the server name and user/password and login successfully, that sql script file will be opened there while won't be executed until you click the execute button from the GUI).

I think there are two solutions for you requirment. One solution is the easist, upgrade your sql express to a full vertion and use Server Agent-> Jobs. Run that script in a job and schedule the job to run at a sepcific time frequency. I believe it won't cost you more than 5 minutes;

The second solution is to use ado.net.Open your visual studio and create a new project, build a connection to your database first and then send the restore sqlcommand to it. Compile and build you project and after that, you create a windows schedule task and run that application regularly.

Hope my suggestion helps

|||

Hi, thanks for the suggestions. The running the .sql from a .cmd file isnt a problem, i already do that to automatically back up all DB's in my SQL Express instance by having a file called BackupDB.cmd with the following code:

CLS
ECHO OFF
ECHO Testing to make sure directories exist.
IF NOT EXIST C:\SQLBackups MD C:\SQLBackups

ECHO Complete with directory creation
ECHO **********************************************************
ECHO Backing up databases......
sqlcmd -S .\SQLEXPRESS -i c:\Windows\BackupExpress.sql -o C:\SQLbackups\backup.log
ECHO **********************************************************
ECHO Backup complete. Look in the C:\SQLBackups\Backup.log file for information.

ECHO ON

The BackupExpress.sql file is simply a script that loops through all databases on the server and creates a .bak file for each of them in the specified directory. Works nicely andmeans i dont need to reconfigure anything if a new DB is created, it is just automatically included in the backups.

So i guess i just need to create a Restore.sql script using the syntax you quoted and call it as above. The command runs under the context of a valid SQL user account specified when the scheduled task is created so login is not an issue.

The script for the BackupExpress.sql file if anyone is interested in using it is:

/**File Name: BackupExpress.sqlDescription: Backs up all databases. This script is mainly meant for SQL Express instancesThe script requires a C:\SQLBackups directory by default to backup to but can be changed with the @.OutputPath variable.Accompanying file is BackupExpress.cmd, which is used to schedule the script.**/SET QUOTED_IDENTIFIEROFF USE masterGOSET NOCOUNT ON DECLARE @.dayofweekvarchar(20)SELECT @.dayofweek =CASE datepart(dw,getdate())WHEN 1THEN'Sunday'WHEN 2THEN'Monday'WHEN 3THEN'Tuesday'WHEN 4THEN'Wednesday'WHEN 5THEN'Thursday'WHEN 6THEN'Friday'WHEN 7THEN'Saturday'ENDDECLARE @.OutputPathvarchar(500)DECLARE @.DatabaseBackupFilevarchar(500)DECLARE @.FolderNamevarchar(25)DECLARE @.DatabaseNamevarchar(25)DECLARE @.strSQLvarchar(2000)DECLARE @.hostnamevarchar(255)SET @.hostname = (select replace(convert(varchar(255),serverproperty('SERVERNAME')),'\','_'))SET @.OutputPath ='C:\SQLBackups'DECLARE cur_BackupCURSOR FOR select name fromsysdatabaseswhere name !='tempdb'OPEN cur_Backup-- Fetch the db names from CursorFETCH NEXT FROM cur_BackupINTO @.DatabaseNameWHILE@.@.FETCH_STATUS = 0BEGINSET @.DatabaseBackupFile = @.OutputPath +'\' + @.hostname +'-' + @.DatabaseName +'-' + @.dayofweek +'.bak'print @.DatabaseBackupFileSET @.strSQL ='BACKUP DATABASE '+@.DatabaseName+' TO DISK = "'+ @.DatabaseBackupFile+'" WITH RETAINDAYS = 1, NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'PRINT @.strSQLEXEC (@.strSQL)FETCH NEXT FROM cur_BackupINTO @.DatabaseNameEND-- Distroy CursorCLOSE cur_BackupDEALLOCATE cur_BackupSET NOCOUNT OFF