| Author |
Topic |
|
l-jeff@excite.com
Starting Member
39 Posts |
 Posted - 2008-05-14 : 15:09:07
|
| I have a AS400 that I retrieved data from and imported into SQL 2000 Server. However, the data will only insert 1 time. I would like to query the AS400 every minutes to update my table on SQL server. If the data is already in my table it does not get updated. How can I get updated/changed data every 15 minutes?HELP!!!!!Lisa Jefferson |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-14 : 15:29:39
|
| Put your code into a job that runs every 15 minutes. It would be fairly easy to grab just the inserts and deletes provided you have a primary key, however updates will be hard and slow. Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
l-jeff@excite.com
Starting Member
39 Posts |
Posted - 2008-05-14 : 16:05:22
|
| The job is running every 15 minutes, however, the rows are just increasing with the same information. It is not giving me new/changed/updated information. How can I just get the changed info from AS400? So instead of having 400+ records, I have 400+ records every 15 minutes and it keeps increasing. I would like to get this info real time and I'm sure there is a way. Any suggestions for me?Lisa Jefferson |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-14 : 16:10:35
|
| You can only get this information real-time if you do it on the AS400 side. I don't know what it has to offer in terms of replication or triggers. Is the database DB2? Can it connect to SQL Server?Do you have a primary key on the SQL Server table? If so, you can use that for inserts and deletes. With updates, you'll need to compare every single column to check if that row changed at the source for the respective row.Do you have a linked server setup on the SQL Server pointing to the AS400? If so, could you post a sample of how to use it so that I can show you how to do inserts and deletes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
l-jeff@excite.com
Starting Member
39 Posts |
Posted - 2008-05-14 : 16:16:48
|
| Yes, I have a primary key on the SQL, however, I get an error message when I use the key. If I take it off, I get duplicate rows again. ------------------------------------------------------The task reported failure on execution.The statement has been terminated.Violation of PRIMARY KEY constraint 'PK_TEST RESULTS'. Cannot insert duplicate key in object 'TEST RESULTS'.????I'm not sure about a linked server setup on SQL Server pointing to the AS400????Second day on SQL scripting.Lisa Jefferson |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-14 : 16:20:25
|
| Don't worry about the insert and delete code as we call help you with that. I'll give you tips on the update, but I won't have the time to write it. If you aren't using a linked server, then how are you able to import the data from the AS400 into the SQL Server currently?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
l-jeff@excite.com
Starting Member
39 Posts |
Posted - 2008-05-14 : 16:23:25
|
| The connection from AS400 to SQL server was already setup for me. Second day on the job and picking up the pieces for someone else. I used the sql server import data and retrieved the data from AS400.Lisa Jefferson |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-14 : 16:29:59
|
| This would be easiest to handle through T-SQL rather than through DTS. If you can get your connection to the AS400 setup as a linked server or OPENQUERY (check SQL Server Books Online for information), then this is simple:-- insertsINSERT INTO YourTable(Column1, Column2)SELECT Column1, Column2FROM LinkedServerName.DatabaseName.ObjectOwner.ObjectName l (this is where I don't know what to put for AS400)WHERE NOT EXISTS (SELECT * FROM YourTable y WHERE y.PKColumn1 = l.PKColumn1)-- deletesDELETE yFROM YourTable yWHERE NOT EXISTS (SELECT * FROM LinkedServerName.DatabaseName.ObjectOwner.ObjectName l WHERE l.PKColumn1 = y.PKColumn1)-- updatesUPDATE ySET Column1 = l.Column1, Column2 = l.Column2, ...FROM YourTable yINNER JOIN Linked... lON y.PKColumn1 = l.PKColumn1WHERE l.Column1 <> y.Column1 OR l.Column2 <> y.Column2 ...Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-14 : 16:31:13
|
| If you must use DTS or can't get the linked server/OPENQUERY setup, then have your DTS package import the data into a staging table that has the same layout as your actual table. Then use the code above to modify your table by replacing Linked... parts with the staging table.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
l-jeff@excite.com
Starting Member
39 Posts |
Posted - 2008-05-14 : 16:52:28
|
| I will lookup the linked server or OPENQUERY first. Thanks so much.Lisa Jefferson |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Sql Server Enterprise Manager