Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TJ
Posting Yak Master
201 Posts |
 Posted - 2002-02-05 : 11:58:39
|
| I have a new project! :o)This is the scenario:Data is gathered and written to a dbaseIII database on the backend.I need to get the data, compare it to recs in an SQL db, insert or update the info where applicable.I know it can be done with a dts, however, my instructions are to write an sproc to run as a job to do it every 15 minutes. Any advice on how to do it with Transact SQL code? Thanks!TJ |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-05 : 12:07:21
|
| I would go back and suggest that they change the requirements to DTS. You can schedule a DTS package to run every 15 minutes.If you have to use T-SQL (Why?) you could use a linked server, or OPENQUERY to access the other DB.-Chad |
 |
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-02-05 : 12:18:22
|
| *One record for each transaction generated for a taxpayer in the dbase III Database (This will result in millions of records)*One record per taxpayer will reside in the SQL database. It will need to inserted once and then updated appropriately with info from the dbaseIII db. (This will result in less than a million records but numerous updates)The object is to do this quickly and not bog down the servers with a complete read every time the process runs. Thanks! |
|
|
|
butlermi_11
Starting Member
10 Posts |
Posted - 2002-02-05 : 12:21:59
|
| It is possible to create a stored proc to call your DTS package to run every fifteen minutes.This will satisfy the requirement and give you the flexibilty you are looking for.Yes, this will add a step of complexity but it gives you what you want.Michael |
|
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-02-05 : 12:26:02
|
I suggested a DTS when this conversation took place. I was specifically told that I could not use one.Please suggest other ideas.  Thanks! |
|
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-02-05 : 13:22:06
|
Looks like I've stumped the band?  |
|
|
|
butlermi_11
Starting Member
10 Posts |
Posted - 2002-02-05 : 13:48:19
|
| If I understand you correctly for every one record in the DBASE III backend db, there has to be one record with all the necessary info on the SQL Server.1. Which tells me you have to have link your DBASE III server to your SQL Server. Linked Server2. Create a dynamic SQL SP to search for updates <= your 15 min critera.This may help some but this sounds very difficult with the restrictions placed on you.Michael |
|
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-02-05 : 14:50:24
|
I mentioned that no one had come up with a solution and it's been okayed for me to work with a DTS. Thanks for all of your input!  |
|
|
|
|
|
|
DBASE III & SQL