Extracting From One Database To Another Automatically Hourly

Mar 7, 2007

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 new trip id along 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
 

View 7 Replies


ADVERTISEMENT

Backup And Restore Database Hourly: How To Implement It?

Feb 15, 2007

I have two servers, using SQL server 2000.I was asked for implementing hourly Backup 3 databases in one serverand restore those databases to another server.Could anyone give me the detailed steps to do that?Thanks a lot in advance!

View 13 Replies View Related

Hourly Batch Process Locking My Database

Jul 20, 2005

Hello everyone,I have around 20 reports in an ASP web-application which connects to aSQL Server 2000 dB, executes stored procedures based on inputparameters and returns the data in a nice tabular format.The data which is used in these reports actually originates from a 3rdparty accounting application called Exchequer. I have written a VBapplication (I call it the extractor) which extracts data fromExchequer and dumps the same into the SQL Server dB every hour. Therunning time for the extractor is an average of 10 minutes. Duringthese 10 minutes, while the extractor seems to run happily, my ASPweb-application which queries the same dB that the extractorapplication is updating becomes dead slow.Is there anyway I can get the extractor to be nice to SQL Server andnot take up all its resources so that the ASP web-application users donot have to contend with a very very slow application during thosetimes?I am using a DSN to connect to the dB from the server that runs theweb-application and well as the other server which runs extractor.Connection pooling has been enabled on both (using the ODBCAdministrator). The Detach Database dialog gives me a list of openconnections to the dB. I have been monitoring the same and I havenoted 10-15 open connections at most times, even during the executionof extractor.All connection objects in the ASP as well as VB applications areclosed and then set to nothing.This system has been in use from 2002. My Data file has grown to 450MBand my Transaction Log is close to 2GB. Can the Transaction Log be aproblem. For some reason, the size of the Transaction Log does not godown even after a complete dB backup is done. Once a complete dBbackup is done, doesn't the Transaction Log lose its significance andcan be actually deleted? Anyway this is another post I'm doing todayto the group.In the extractor program,1) I create a temporary table2) I create an empty recordset out of the table3) I loop through the Exchequer records using Exchequer's APIs, addingrecords into the recordset of the temporary table as I go along.4) I do an UpdateBatch of the Recordset intermitently5) I open an SQL Transaction6) I delete all records from the main table7) I run a INSERT INTO main_table SELECT * FROM #temp_table8) I commit the transactionI hope that the information is sufficientThanksSam

View 4 Replies View Related

Replication :: Syncing Of Database From Local Host Database To Online Database Automatically After Some Interval

Oct 14, 2015

I have database on localhost and i want to show this data on my website. I want to create a database online and want to sync with Local Host. Can it be possible syncing data automatically after some interval?

View 6 Replies View Related

Help With Extracting Info From Sql Database

May 22, 2006

The situation is I need info from a certain SharePoint site that is in a sql database. The problem is The SharePoint site currently contains data from 9 different projects, all using the same database. The database is a complex setup and it would not be an easy operation to extract the special information alone. The database is run with Microsoft SQL and is comprised of 4 separate databases. 3 databases organize the data (500+mb of data total) and 1 stores the data for all the sites (12.5+GB and increasing at a rate of 100-300mb per day).

How can I get the special data I need from this setup???

Please help

View 3 Replies View Related

SQL Server 2014 :: Extracting Data From Database?

Nov 5, 2015

I am running an hotel application for all the bookings.

I would like to get all the data of all the guests that stayed in the hotel (name, stay duration and dates, payment, etc).However, the GUI would make me go over one booking at a time, which would take me forever.

The application has an SQL database locally on my computer.

Will I be able to use SQL server and extract all the guests data from the last year and export it into a file, e.g. A CSV file?

View 8 Replies View Related

Extracting The Specified Record No Of Records From Database Table

Jun 11, 2006



hi all,

I need to select the no of records on the basis of specified range of records.

In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..

I have used temporary table,map the table primary key to the next table with identity

but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...

Is there some other best way to extract the specified data

here is the type of query.

select * from customers where rownum between 1000 and 10000

this is in oracle

i am in need to do this in the sql server

waiting for the response...............................

View 5 Replies View Related

SQL Security :: How To Create Database Specifications On Newly Created Database Automatically For Audit

Jul 15, 2015

I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.

View 6 Replies View Related

Database Shutsdown Automatically

Aug 30, 2001

Hai,

My database shutsdown automatically after 2 hours , when i start to run a jobs from a queue.

Can someone suggest me a way for monitoring it, so that i can find the possible causes.

Thanks
Balaji

View 1 Replies View Related

Automatically Growing The Database

Apr 10, 2000

Does anyone know at what point SQL Server 7.0 decides to grow the database when the autogrow option is set? Our site just went down for 45 minutes because the growing process was taking too long as compared to the data coming in, so the device filled up.

Ray? Craig? You guys seem to know all, so jobs.com appreciates your input...

View 7 Replies View Related

Database Shutsdown Automatically.....

Aug 30, 2001

when i start to run a jobs from a queue to the database, after 2 hours the database shutsdown automatically.

This problem is reproducable, Running a trace on it from profiler is what comes to my mind. Is there any other suggestion.....

Thanks
Balaji

View 1 Replies View Related

SQL 2012 :: Automatically Log Off From Database

Apr 3, 2015

Our auditors are trying to enforce a requirement that all users be disconnected from any application or database after 15 minutes of inactivity. Is there any way to force a logoff within SQL Server? For example, someone is in Management Studio connected to a database. They should be logged off after 15 minutes of no activity.

View 1 Replies View Related

Automatically Copy Database To Another Pc

Nov 25, 2006

When I create a setup program for my vb.net 1.0 app which has embedded sql express database, it successfully copies the database to the new machine alone with the upgraded app.

However, if I just copy the vb.exe app to the other pc and also copy the mdf, ldf files, I get an error opening the sql database.

I am presuming that the setup program does some kind of backup restore or detach, attach to copy in the .mdf.

If the user cannot do this from the management studio (or if they do not have the management studio installed), is there any way I can create some method to have this copying done automatically via some code by the user?



Thanks

SM Haig

View 1 Replies View Related

Hourly Reporting

Jan 5, 2005

Hi! how do get the number of students who atteded prep with the last 1 hour. and log store the number in a Variable

CREATE TABLE [dbo].[PrepTime] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PrepNo] [int] NOT NULL ,
[Log_Time] datetime NOT NULL DEFAULT, CURRENT_TIMESTAMP,
[coming] [int] NULL ,
[going] [int] NULL ,
[Student_ID] [bigint] NULL ,
[Study_Type] [int] NULL ,
[State] [smallint] NULL

) ON [PRIMARY]
GO

View 2 Replies View Related

Hourly Average

Aug 17, 2006

Apologies for the simplicity of the question, but it reflects mycapabilities! I have the following sample fields coming from differenttables:LocationTimeDate (timestamp)DataI need to return the average of Data per Location per HOUR.Thanks.

View 7 Replies View Related

Database Automatically Growing Issue

Nov 1, 2000

Hi ALL

I have question about database automatically growing for SQL Server 7. It seems to me that SQL database automatically grow will ONLY happen when it's getting really full, maybe above 90% full. Even if you manually increase the DB size, actually the increased DB size will only keep very short time. And DB size will be back to the smaller size again. If you have any suggestions I will be really appreciated. Thanks

View 2 Replies View Related

Why Was Path To Database Automatically Put With Name When Reattached?

Mar 10, 2008



I have a database that I have made with Sql Server 2005 Express. I am building a windows app using this db as the back end. I am using visual C# Express as well.

I found out during this project that I had to detach my database in order to establish a new connection to it using the express editions. I learned that this is a short coming of the Sql Server 2005 Express Edition. So I detached my db. When I reattached my database the name had the path to where the file was in it's name. I don't think I have done anything to cause this. But, obviously something went wrong.I have a jpeg screenshot of this but can't post it in this forum.

I created another small database as a test to see if the same thing would happen again. The database only has one table with two columns. I detached and then reattached it with no problems. It did not get the weird file name that automatically included the path to the file.

Does any one know what is going on with this? Do you know how I can reattach this database and only have the original name(not the path to the file)?

Thanks

View 5 Replies View Related

Automatically Backup New Created Database Once

Dec 26, 2007

Hi,

i've sql 2005 std sp2,and i'm working with a program that create every view month's a new database automatically.

my question is - how can i make a full backup of the newly created database automatically only one time when it's created?

Thx

View 16 Replies View Related

Hourly Rate For Consulting

May 8, 2008

I am working with a company that needs some SQL Reporting developed. What is the going rate for SQL Reporting work?

View 14 Replies View Related

Get Hourly Data From Table

Mar 18, 2014

I am trying to get hourly data on a table which was working fine on mysql but not on sql. how to use this in sql server managmenmt studio, i receive 'time' is not a recognized function name.and curdate() is not recognise function.

select [pa_number], [pa_surname]
,[pa_forename],
sum(time(datetime) >= '07:00:00' and time(datetime) < '08:00:00') as '7.00-8.00 AM',
sum(time(datetime) >= '08:00:00' and time(datetime) < '09:00:00') as '8.00-9.00 AM',
sum(time(datetime) >= '09:00:00' and time(datetime) < '10:00:00') as '9.00-10.00 AM ',
sum(time(datetime) >= '10:00:00' and time(datetime) < '11:00:00') as '10.00-11.00 AM',

[code]....

View 2 Replies View Related

Hourly Schedule Between Times

Nov 23, 2007

hi,
I have setup an hourly schedule to run every hour, but how can I choose a start and end time ? This is within subscriptions.
is this possible ?

View 3 Replies View Related

Snapshot Of Databases = Hourly Job

Apr 18, 2007

HiHow can I create a job in sql agent to create a new snapshot every hour?I have, for eg a T-SQL that does it manually.create database Snapshotter_snap_20070418_1821 on( name = Snapshotter, filename ='c: empSnapshotter_snap_20070418_1821.ss')as snapshot of SnapshotterNow, what I do NOT want, is to only have one copy, but rather to do thisevery hour or two through out the day - and keep the old copies for sometime. (In that case, a DROP database, and a CREATE database <generic name>is easy).Any help appreciated,M

View 4 Replies View Related

Exporting SQL To Excel Hourly

Jul 20, 2005

First time here so please bear with me.Set up a DTS package to export data to an excel sheet on an hourlybasis. Problem is, it keeps appending to the same excel sheet.Any idea how to prevent that. All I want to accomplish is that everyhour, the latest data is in the excel sheet and the previous data isdeleted.Thanks in advance!

View 5 Replies View Related

How Can I Detect Changes Automatically In The SQL Server Database When Anything Is Updated..?

Mar 13, 2007

I been asked recently in the interview thatHow can I detect changes automatically in the SQL Server Database when anything is updated, deleted or inserted?if anyone can help me in this that will be really great.
I dont actually know whether I can ask this here but I wanted to know the answer for this and thought this might be right place to ask?
sorry if I am wrong?
Thanks in Advance

View 2 Replies View Related

Database Automatically Writing To App_data Folder

Feb 28, 2008

Hello!
I have created my self a person website in VS2008 using Linq and ahev downloaded a copy of sql express to use as my database when working on it at home.
I have just noticed, now I am trying to upload it to a server that my sql express is automatically creating the database in my app_data folder, this is causing me problems, as I don't want it to do this. I want it to be able to point it at a full version of SQL server.
 Is there some setting I have missed? Of how can I stop tis automatic creation of the database?
Thanks
Bex 

View 1 Replies View Related

How Can I Update One Database Automatically Everyday Based On Another One?

Sep 3, 2002

Hi guys,

I need to update one table in database A based on the other database B, which is on a romote server. The requirement is to update it automatically at 12:00 in the mid-night everyday. Database A is MS SQL Server 7, and database B is Oracle database.

What's the best to implement it? I thinking to use trigger, am I right?

Thanks in advance for any advice.

Henry

View 1 Replies View Related

SQL 2012 :: Automatically Syncing Database To Different Server?

Jul 25, 2014

We have two SQL 2012 servers, sever A and B.

We need to sync a database on A so that there is a copy of it on B.

We need to be able to query both databases so mirroring isn't an option.

View 7 Replies View Related

Package Hourly Transaction Log Failed.

Mar 27, 2008

Any thoughts as to why I get this error, every hour. How do I fix it?
Be gentle I'm a SQL novice.

Error reported in the Application Event Log.

Package "Hourly Transaction Log" failed.

OS: Windows Ser_2003_sp1
Source: SQLISPackage
Category: None
Type: Error
Event ID: 12291
File Name dtsmsg.rll
File Version: 2005.90.3042.0
Product Name: Mic SQL Server.
Product Version: 9.0.3042.
Time 3:00:01 PM
Type: Error

View 4 Replies View Related

Automatically Update Datetime Field In A Database Table.

Feb 28, 2006

hi e'body:

I have some database tables, and each one of them have a creation_date and modified_date in them. I was trying to figure out a way where when a row in one of these tables is changed using Enterprise Manager (Database -> Tables -> select table -> right click -> select all rows -> change a field in a row inside a table), is there a way apart from triggers, such that the "modified_date" column for that row get changed to 'getdate()' (rather picks up the current datetime).

thanks in advance.

View 1 Replies View Related

How To Automatically Replicate Remote Database Which Is Not Permanently Online

Jul 23, 2005

Dear group,I have to replicate remote data to a SQL Server in the headquarter.The data are on a site which does not have permanent online-connectionto the headquarter.I have written a script which replicates the data and i want to set upa process / batch on the headquarter-machine which roughly does thefollowing1) Connect to the remote site2) start replication script (in fact a stored procedure)3) on success disconnect from the remote siteAll this has to run automatically e.g. during nighttime.Could someone please outline how i set up the connection and keep itup during my database-action and how to automatically disconnect aftersuccess of my replication script.I'd be jolly grateful.Thanks in advance and Greetings from ViennaUli

View 1 Replies View Related

Transact SQL :: Create Database Trigger Automatically Whenever New DB Is Creating?

Aug 5, 2015

I need to deploy the trigger in database whenever new DB is creating on the server.

View 6 Replies View Related

Transact SQL :: Hourly Records From Current Hour

Jun 29, 2015

I am using the following query in a view to retrieve the latest 24 hourly records for a site.This returns 24 hourly records for the last day of measurements at a Site.This works great. However, I now need to retrieve the latest hourly records from the current hour. For example, hours will run from 00:00 to 23:00 and if the query is executed at 15:00, I will return only hourly records for 00:00 to 15:00 etc. I believe I need to filter the result set or modify the query to exclude records greater than the current hour.

View 6 Replies View Related

HELP To Write Stored Procedure Whose Values Are Calculated Automatically In Database

May 12, 2007

 Hi frdz,    I m creating my web-application in asp.net with C# 2005 and using sql server 2005.    I have created the stored procedure for the insert,update.    I want to know how to write the mathematical calculations in the stored procedure..    Pls tell me from the below  stored procedure  were i m making the mistake  ??    As the discount and the total amount are not calculated by itself....and stored in the database   How to convert the @discpercent numeric(5,2) to@discpercent ="NoDiscount" should be displayed when no discount is being given to the customers....     ALTER PROCEDURE CalculationStoredProcedure

@accountid int output,
@accountname varchar(20),
@opbal numeric(10, 2),
@opbalcode char(2),
@total numeric(10, 2),
@clbal numeric(10, 2),
@clbalcode char(2),
@discpercent numeric(5,2),
@discamt numeric(10, 2)


as


begin
set nocount on



if @opbal IS NULL OR @opbal = 0

begin
select @opbal=0
select @opbalcode= ' '
select @clbal= 0
select @total= 0
select @clbalcode= ' '
@discpercent ="NoDiscount"
@discamt=0
end




select @accountid = isnull(max(accountid),0) + 1 from accountmaster



select @total=@opbal - @clbal from accountmaster
select @discamt=@total* @discpercent/100 from accountmaster

begin
insert into accountmaster
(
accountname,opbal,opbalcode,clbal,clbalcode

)
values
(
@accountname,@opbal,@opbalcode,@clbal,@clbalcode
)

end

set nocount off
end

      Thanxs in adv... 

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved