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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-16 : 09:20:41
|
| Mr. Chris writes "First off, I did not design this, I can't re-design this, and I have to make it work.That being said, here's my situation. I need to pull a table from an Access DB into my SQL2000 DB. I need to be able to initiate this process from a stored procedure. The tricky part happens when we take into account the everchanging structure of the source table. Due to POOR design (see disclaimer), columns can be added or removed over time to the source. The Import DTS Wizard works great to do this manually when I specify to drop/create the destination table, but when this gets saved as a DTS, the actual column x -> column y transformations become static and I lose the new columns since they don't exist in the destination.I think this a fair description of the problem, but then again, it always makes sense when you type it.Thanks in advance for any suggestions!Mr. Chris" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-16 : 09:49:55
|
| If you have the option, try testing linked servers. Create a linked server to the Access database file. As long as it's available through a WAN, LAN or intranet connection it should work.Once you set up the Access DB as a linked server, you can use the SELECT...INTO statement to create new SQL Server tables on the fly, as that command creates the new structure based on the source table(s) being SELECTed from.I think that might be the easiest route, certainly easier than writing DTS code to query the database tables and build the structure and transformations. Take a look in Books Online for more information on DTS development too; there's a whole section on programming DTS. Also take a look here:www.sqldts.comThey have a number of examples. So does MSDN.And smack whoever set up this database and handed it off to you. |
 |
|
|
|
|
|
|
|
Scheduled Import of an Access Table with Ever-Changing Structure