failed jobs ..to restart automatically.

hi
my job was scheduled at 11.45 pm..but server went down at 11.40..and restart
ed at 12.10 am..
i want to know
1) how can we reschedule our jobs..so that if server went down and job was s
chedule somewhere in between it should automatically start..
jobs can be anything like backup/reindex//etc
regards
sunnyHi,
Set SQL server agent to start automatically, when the server starts. And the
n schedule your job to run automatically at the startup, i.e when the agent
starts up.
You can use the following stored proc
sp_add_jobschedule
and set @.freq_type = 64
The value 64, sets the job to start automatically, when the sql server agent
starts.
Thanks
GYK

failed jobs ..to restart automatically.

h
my job was scheduled at 11.45 pm..but server went down at 11.40..and restarted at 12.10 am.
i want to kno
1) how can we reschedule our jobs..so that if server went down and job was schedule somewhere in between it should automatically start.
jobs can be anything like backup/reindex//et
regard
sunnHi,
Set SQL server agent to start automatically, when the server starts. And then schedule your job to run automatically at the startup, i.e when the agent starts up
You can use the following stored pro
sp_add_jobschedul
and set @.freq_type = 64
The value 64, sets the job to start automatically, when the sql server agent starts
Thank
GYK

Fail to start Integration Service

As part of Team Foundation Server the Integration Services are used. They are configured to start automatically but it doesn't. Status is "stopped". Trying to start the service manually fails too:

Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7000
Date: 07.11.2006
Time: 11:11:49
User: N/A
Computer: SDOSCARBUILDSRV
Description:
The SQL Server Integration Services service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

It uses default LogOnAs NT Authority\NetworkService. Any idea what might be wrong and what to do?

Thanks

Hans-Peter

Please check if this KB applies:
http://support.microsoft.com/kb/918644|||

SP1 has not been installed yet.

Hans-Peter

|||Is the Sql Server Service started? SSIS is dependent on that service.|||

Yes, the Sql Server Service is started. Also Agent, Analysis Service, Browser, Full Text Search, Reporting Services and VSS Writer. They all are started automatically and they are up.

Hans-Peter

|||

What to do? Any hints?

Hans-Peter

Fail to start Integration Service

As part of Team Foundation Server the Integration Services are used. They are configured to start automatically but it doesn't. Status is "stopped". Trying to start the service manually fails too:

Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7000
Date: 07.11.2006
Time: 11:11:49
User: N/A
Computer: SDOSCARBUILDSRV
Description:
The SQL Server Integration Services service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

It uses default LogOnAs NT Authority\NetworkService. Any idea what might be wrong and what to do?

Thanks

Hans-Peter

Please check if this KB applies:
http://support.microsoft.com/kb/918644|||

SP1 has not been installed yet.

Hans-Peter

|||Is the Sql Server Service started? SSIS is dependent on that service.|||

Yes, the Sql Server Service is started. Also Agent, Analysis Service, Browser, Full Text Search, Reporting Services and VSS Writer. They all are started automatically and they are up.

Hans-Peter

|||

What to do? Any hints?

Hans-Peter

Extracting from one database to another automatically hourly

For a GPS utility project we are planning on extracting certain attributes from a huge "GPS Raw Data" read only database which we have access to containing GPS data from several years from several devices attached to vehicles.

The data is time stamped. Where the time gap between pieces of data is more than 10 minutes, a new trip is instance is assumed and in our write access "Trip" database we create a new instance for the data clump with a newtrip idalong with the time range of the data.

The process is to be run hourly to update the "Trip" database with new trips and append to overlapping trips.

We've some questions:

a) Is it easy to read from one database and write into another in c# hourly

b) How would one go about running a C# program automatically every hour on the server?

c) Is there a better way to do this than an hourly update? (dynamically perhaps??)

d) When querying the database and comparing the time stamps, how for instance would we go about identifying a 10 minute gap when the time/date is in the format "22/12/2007 11:25:00". I can't get my head around actually writing this - it's probably ridiculously simple

what type of database r u using ?

If its SQL, why not create a storedprocedure and make it to run in a job every hour, it will best for performance .

|||

Perhaps you can look into solutions like Replication where your GPS Raw Data can be the publisher and your target db can be the subscriber. Any transactions on publisher are replicated on to the subscriber. and the subscriber can be used for querying.

>>a) Is it easy to read from one database and write into another in c# hourly

Depends on how much data there is to read/write.

b) How would one go about running a C# program automatically every hour on the server?

>>You can write a stored procedure that has the logic to query for modified/new records and does an INSERT into the target db.

>>c) Is there a better way to do this than an hourly update? (dynamically perhaps??)

Perhaps. Look into the replication technology and see if it suits your requirements.

>>d) When querying the database and comparing the time stamps, how for instance would we go about identifying a 10 minute gap when the time/date is in the format "22/12/2007 11:25:00". I can't get my head around actually writing this - it's probably ridiculously simple

You can use the DATEDIFF function : IF DATEDIFF(mi, <valueinrow>, Getdate()) = 10. But you'd have to have some kind of service running constantly to trigger this data transfer when the 10 minute interval happens.

|||

Thank you for your help Ramzi!
We are using MS SQL 2005 with Microsoft Visual Web Dev 2005.

I must read into stored procedures if you think this is the best way forward as a solution. Is there a particular utility you would advise for creating/managing stored procedures?

I just downloaded MS SQL Management Studio. However, I cannot find the ASPNETDB.MDF database created in Visual Web Developer on the local machine when I look for it.

|||

Thanks ndinakar

Perhaps you can look into solutions like Replication where your GPS Raw Data can be the publisher and your target db can be the subscriber. Any transactions on publisher are replicated on to the subscriber. and the subscriber can be used for querying.

>>a) Is it easy to read from one database and write into another in c# hourly

Depends on how much data there is to read/write.

Once the main "lot" from the past year is indexed, then each hourly update would be rather small with the only perhaps a hundred co-ordinates coming in over an hour. At times there will be no new data.

b) How would one go about running a C# program automatically every hour on the server?

>>You can write a stored procedure that has the logic to query for modified/new records and does an INSERT into the target db.

We will look into the stored procedure function. This looks like it could be very useful.. Would this be used alongside or instead of replication? (bear in mind I've not yet read up on replication)

>>d) When querying the database and comparing the time stamps, how for instance would we go about identifying a 10 minute gap when the time/date is in the format "22/12/2007 11:25:00". I can't get my head around actually writing this - it's probably ridiculously simple

You can use the DATEDIFF function : IF DATEDIFF(mi, <valueinrow>, Getdate()) = 10. But you'd have to have some kind of service running constantly to trigger this data transfer when the 10 minute interval happens.

In the stored procedure that runs hourly could this be used to run through the data from the past hour and identify these if/when these intervals happen or are you saying that it needs to be constantly checking (every few seconds)?

|||

I'm looking for info on stored procedures now.
On the MS website there is mention of a "CREATE TRIGGER" procedure... Is this what you refer to as something we could use with the DATEDIFF comparison?

Is it possible to set a timed execution? Could someone give a small example of this?

All of your help has been much appreciated!

|||

Going by your replies perhaps its best to create a job, have the job call a stored procedure. You can schedule the job to run every 10 minutes and in your stored procedure, query all the new/modified records after the previous run and migrate the data. You might need to store the timestamp of the last run in case the job does not run for any reason or you need to stop the job for a few hours. You can always catch up if you have the timestamp of the previous run.

Check out Books On Line (the documentation that comes with SQL Server) for CREATE PROCEDURE.

For creating/scheduling/managing jobs look for "Jobs" under SQL Server Agent.

|||

I have managed to do some basic copying from one database to another, however, I'm having some problems with the datediff function.

We are selecting the time and vehicle ID from the "GPS raw data" database. We want to compare each entry with the previous one and, where there is a gap of more than 15 mins, return it.

We will group the associated data into another "trip" table, with a primary key of "trip id". So any gap in GPS transmission of more than 15 mins will be considered the end/start of a trip.

This is just vague code.
I want to compare each row really.. I've no idea how to though..(I've put in time & time+1 where time+1 is the next row - Don't know how to do this)

SELECT Time, deviceID,
FROM [gps.db]
WHERE datediff(n, time, time+1) > 15

extracting data from sql server 2000 to draw bar charts in powerpoint/excel

Dear all,

I am doing a project where I heed to extract data from a table in a sql server and then automatically create a bar chart with this data preferrably in PowerPoint (or excel). I was thinking myself that it may be done in vba/macro added to a button in powerpoint. If anyone knows how to do this i would be very grateful or if anyone know a useful website could they post it up.

Thanks for your help

Laura

The Microsoft Office web site has a good overview of how to import data into Excel from SQL Server. That data could then be used to draw your chart. Check out http://office.microsoft.com/en-us/excel/HA010864661033.aspx for more information. Once you've set up the datasource connection, there is already a menu item/toolbar button to refresh the workbook's data from the server.|||There are several ways to export data into Excel

1. Use Jet OLEDB Provider. You need to create INSERT SQL statements and call them to insert one row of data at a time. Jet works pretty fast, but has some limitations when data in a same column contains different types.

2. Use Microsoft Office Tools for .NET. This way is slower, but gives you more functionality and allows formatting of the data. It allows you to control your output, but could be very slow and you need to pay attention to releasing all the resources associated with it, because it is COM-based. You have to pay attention to memory releasing, especially in ASP.NET environment

3. Write code that stores data into CSV or HTML. Those formats recognized by Excel

4. Use third party components that could provide desired functionality

Following are some links wit the samples

http://support.microsoft.com/kb/306022/en-us

http://support.microsoft.com/kb/326548/en-us

http://support.microsoft.com/kb/307029/en-us

extracting data from sql server 2000 to draw bar charts in powerpoint/excel

Dear all,

I am doing a project where I heed to extract data from a table in a sql server and then automatically create a bar chart with this data preferrably in PowerPoint (or excel). I was thinking myself that it may be done in vba/macro added to a button in powerpoint. If anyone knows how to do this i would be very grateful or if anyone know a useful website could they post it up.

Thanks for your help

Laura

The Microsoft Office web site has a good overview of how to import data into Excel from SQL Server. That data could then be used to draw your chart. Check out http://office.microsoft.com/en-us/excel/HA010864661033.aspx for more information. Once you've set up the datasource connection, there is already a menu item/toolbar button to refresh the workbook's data from the server.|||There are several ways to export data into Excel

1. Use Jet OLEDB Provider. You need to create INSERT SQL statements and call them to insert one row of data at a time. Jet works pretty fast, but has some limitations when data in a same column contains different types.

2. Use Microsoft Office Tools for .NET. This way is slower, but gives you more functionality and allows formatting of the data. It allows you to control your output, but could be very slow and you need to pay attention to releasing all the resources associated with it, because it is COM-based. You have to pay attention to memory releasing, especially in ASP.NET environment

3. Write code that stores data into CSV or HTML. Those formats recognized by Excel

4. Use third party components that could provide desired functionality

Following are some links wit the samples

http://support.microsoft.com/kb/306022/en-us

http://support.microsoft.com/kb/326548/en-us

http://support.microsoft.com/kb/307029/en-us