Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Professional
| Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Professional If you are an experienced ASP.NET programmer, this is the forum for your 2.0 questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 23rd, 2007, 05:09 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Mentiri, Muara, Brunei.
Posts: 363
Thanks: 0
Thanked 1 Time in 1 Post
Default Insert/Update to sql server DB in a scheduler

Hi,

  I am importing external data(oracle) to local sql server DB thru linked server. This is actual replica of the external data(not normalized). I need to run this import for every 2 minutes. Its working well.
 Now, I need to split this data into child tables to fulfil the normalization. For that, I have created a C# class which gets all records(newly added/modified) and passing these values to stored procedure as parameters.
The SP will check if that record exists, it will update or it will insert into my local tables.

I made DLL and I need to run this DLL for every 2 minutes(this is my client requirement). If I have 1500 records, this import consumes 10 minutes(to establish connection, retrieve, and calling SP).
But this is more time. Its not acceptable.

How can I reduce the time? Is it correct way what I am doing or any other way is possible?

Pls suggest me an idea.

Thanks in advance

 
Old October 23rd, 2007, 05:47 AM
Friend of Wrox
Points: 410, Level: 7
Points: 410, Level: 7 Points: 410, Level: 7 Points: 410, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2004
Location: New Delhi, India.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to anubhav.kumar
Default

Hi,

How are you importing data? Can you use DTS or Integration Services [if u are using sql2k]? If so, you will have greater control on how the data is transformed in the target database. So right at the time of importing, you can populate appropriate tables and you will not need any external program to do it !!

Anubhav Kumar
 
Old October 23rd, 2007, 06:03 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Mentiri, Muara, Brunei.
Posts: 363
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi,
 Tx for the reply. How can I pass value to a query that is to be executed in DTS?
I want to retrieve all the records from external database based on the last updated date time.

means, the query should look like

select * from oracledb.table where update_dtm > ?

How can I do this in DTS?

 
Old October 26th, 2007, 04:27 AM
Friend of Wrox
Points: 410, Level: 7
Points: 410, Level: 7 Points: 410, Level: 7 Points: 410, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2004
Location: New Delhi, India.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to anubhav.kumar
Default

Hi,

Please refer to books online, on How to use variables in DTS packages. It will give you clue to proceed. Additionally, U can control many things by writing a VB module in DTS and calling it from within. Please refer to documentation for this. Currently I cannot share my code with you. But it is not that much tough!!

Anubhav Kumar




Similar Threads
Thread Thread Starter Forum Replies Last Post
store insert/update date/time stamp in sql2k db alexdcosta ASP.NET 2.0 Basics 0 June 22nd, 2006 05:44 AM
Tring to insert date into SQL server Db srjordan Classic ASP Databases 1 August 15th, 2005 06:57 PM
access db to sql server db mikersantiago Classic ASP Basics 4 November 16th, 2004 03:33 AM
do insert into and update sql commmand X-Ken VS.NET 2002/2003 10 June 21st, 2004 03:30 AM
Bound Controls don't update SQL DB Ron Howerton ADO.NET 1 January 28th, 2004 03:46 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.