Solved

MS SQL 2005 How to Insert / Create missing Records in Existing dbo  (table)

Posted on 2011-02-23
12
486 Views
Last Modified: 2012-05-11
How do I simply insert records into a MS SQL 2005 database object (table) that were not created becaus ethe MS SQL server was disconnected at the time it was suppose dto receive and archive data?
0
Comment
Question by:elliotsegal
  • 6
  • 4
  • 2
12 Comments
 
LVL 76

Expert Comment

by:arnold
ID: 34966648
Do you have the data is it in a csv format?
you can use a bulk insert you could use bcp it depends on how the data that you want to add is arranged/formated and what options are available to you.
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34967054
Ya, we're going to need a lot more information than that. Generally speaking, if an insert is attempted when the database is unavailable, its not logged and there's no way to recover it.
0
 

Author Comment

by:elliotsegal
ID: 34969272
Derek & Arnold,

This doesn't happen often but when it does it is very time consuming to manually enter, etc.

I'll get try and get the normal procedure(s) I use to update posted here.  Major problem as you stated is that there's no way to recover when the RDBMS is unavaillable.

This database object procedure is used with a SCADA system for it's historical database, many entried evry hour, minute, etc.  

What I've been doing is manually using Excel ith MS Query to edit the table(s) where the record/entry for a particular hour is missing because either the RDBMS or the SCADA system application(s) were disconnected/unavailable.

I could prep the data in a .csv format , but the real issue (I think) is that I'll have a record for 15:00 but at 16:00 the disconnect occured and no records ar in the .dbo (table) for that hour at all.  What's the best way to at least make the records for 16:00 and then I can copy the actual data/values to the .dbo/table using some VBA script/macros in Excel?

Thanks,

Elliot
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34969656
Using excel/macros is a possibility.

Are your databases unavailable often? Are these unexpected outages? I'd look more into the cause for the unavailability than working around it if possible.
0
 
LVL 76

Expert Comment

by:arnold
ID: 34969996
You could see whether a detection is an option within the scada app that will in a case where the DB is unavailable to add the entries into a file which can then be imported when the database becomes available by a cron/scheduled task job checking for the server.
0
 

Author Comment

by:elliotsegal
ID: 34983505
Derek & Arnold,

Good points on both. Here's more background:

The database isn't unavailable often, but when it is unavailable it's invasive and time consuming to create records and then insert.  I'm sure most of that is due to my lack-of-skil/knowledge and not thinking out of the box.

The cause is generally a catastrophic event (i.e., online/production SQL Server going offline).  The MS SQL server is on the same server(s) as my SCADA/EMS apps.

 [I have four servers with MS SQL all with the same DBs on it.

I use snap-shots to keep the other three insync and up-to-date with the online/production server.  One of the serves is running as back-up and will failover as required, the other two servers are Web servers distrubuting Java Web Apps, etc, for end user access via VPN, firewall to firewall, through initial access at a Proxy server.  

I do like the idea of a scheduled task to check for and when an insert wasn't done (currently scheduled on the hour with a 5-minute offset to allow the SCAD/EMS to do its front-end processing and have enough time to output to MS SQL into a historical database's .dbo's (i.e., tables).  Each .dbo is basically the current month's values.  I generate new .dbo's for the upcomming month o/a the 20th of each month even thoughthere's no live data getting to it yet.  

Isuue I may have is "buffering" the values from the SCAD/EMS front-end until the MS SQL Databse came back online, in order to have data to import after records in the tables were created.

Right now, if the online/production server crashed for some reason at the top of the hour througgh 5-minutes past the hour, then I don't have any place to insert the data because that hour that just past records were'nt created in the .dbo (table).  

It would appear as 01:00, 02:00, 03:00, 05:00.... if the crash/problem occured at the "hour ending" at 05:00.  Meaning that the data for the period 04:00:00 - 04:59:59 doesn't get written/archived and the next set of records wouldn't occur until it was time to do this for 05:00:00 - 05:59:59.

Adding to this, there are several .dbo's for each month (i.e., there's an hourly archive, a 5-minute archive, 1-minute archive, etc.)  these tables represent the different data acquired from the SCADA /EMS at different intervals.  (How much energy was used for an entire hour, what the status of NOx was from a generation plant minute-by-minute, etc.).

Any ideas?



0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 76

Expert Comment

by:arnold
ID: 34983915
Do you have an option to have the insert run more frequently such that it does not try to jam a large amount of data at the same time and perhaps that is the time when your snapshot runs making hings worse?
0
 

Author Comment

by:elliotsegal
ID: 34988482
Yes, the insert check could be scheduled prior to the snapshots (once before each snapshot interval).  I belive that could work where:

1. Insert check for previous hour's records were created in the .dbo (table)
2. If records aren't present, create them and then data values could either be copied from the last good hour's records or manually inserted (e.g., macro run in Excel uisng MS Query, etc.).

 Basically I'm thinking that if the "insert records" check shows that the subject hour's records weren't created, it would create them, but with no data values in them (they'd be nulls / "0").

If for some reason the front-end process didn't have value sot populate the newly created records, then the insert script would copy the previous hour's values (better than nulls / "0") as a last resort, which would at least maintain contents and reasonable historical values for trending and forecasting purposes.

Any specific values could always be manually inserted after the records are created, for example, the status indication (i.e., on/off, good/bad, fail/normal) for specific conitions (i.e., a generator on/offline, status of a switch or circuit breaker, etc.).

Now the hard part, scripting this!

What do you think?
0
 
LVL 76

Expert Comment

by:arnold
ID: 34999034
What are the available options that you currently use?
Does it have an option to check whether the connection to the database is available?
Do you have an option to do two things, i.e. attempt to insert data into the database while at the same time, outputing the data into a flat file (CSV, tsv, etc.) this way you can have a separate script run using the data from the file to see wther the data exists in the table and if not insert the record.  Presumably you have duplicate cancelation such that an entry using the same timestamp will be rejected as a duplicate.

Which programming languages are you comfortable with VB, C#, perl, etc.
0
 

Author Comment

by:elliotsegal
ID: 35001509
Arnold,

I'm not a coding Ace, but most of my work is done in C# and VBA.

I do currently have an option to check if the DB is available and an error will generate when attempting to connect; I've got to look closer at that to see exactly what's going on.  I belive that is being"checked" from the SCADA application and not at SQL Svr.

I do have duplicate cancellation set-up, and allow nulls (expect for primariy keys)

As far as the insert I'm going to have to get more detail/access the SCADA application code/script to see how that is happening;
0
 

Accepted Solution

by:
elliotsegal earned 0 total points
ID: 35109131
All concerned,

Due to workload and priorities, I'm going to have to back-burner this issue and resume at a later date.  Let's close it out and pick it up later.  Thanks for the help thus far.
0
 

Author Closing Comment

by:elliotsegal
ID: 35357061
Stopped work on project due to othe rhigher priorities
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now