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 |
|
Liquid Metal
Starting Member
4 Posts |
 Posted - 2003-04-26 : 02:22:48
|
| Can someone verify this for me? I just talked to the DBA in my company. I explained to him the best methods to query external data would be using Link Server, Open Datasource or Openrowset.The guy kept insisting that I should import the data in, query it and then drop the table afterward.The method that he provided is only a get around. This process would increase the transaction log for one thing. Also, it is an extra step to bring the data in and then query. Plus, the database is to its max right now. If I import a huge table, it would take up quite a bit of space while the methods I provided only links the tables.Can you tell me if my reasons are legitimate?I'll Be Back! |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-04-26 : 03:30:36
|
quote:
I just talked to the DBA in my company.
Your first mistake  How "external" is the data ? if it is on another server on the same network then I agree with you.Unfortunately there are WAY too many DBAs inside companies that are more concerned with locking down their territory than having efficient systems  If the data is somewhere else entirely, then he may have a point.Damian |
 |
|
|
Liquid Metal
Starting Member
4 Posts |
Posted - 2003-04-26 : 18:21:04
|
| Hi Merkin,The external table is from an Access database.I'll Be Back! |
|
|
|
Liquid Metal
Starting Member
4 Posts |
Posted - 2003-04-26 : 18:25:38
|
| I am new to this forum. How do you edit a post anyway?I also want to add, the Access database could reside on the same computer of the SQL Server or on my computer.He was unable to get Openrowset or Opendatasource to work because I think both of these functions requires me to have ADMIN permission to the computer where SQL Server resides on. Do you know anything about this because when I was added into Window Admin group, Openrowset worked. If he takes me off, it did not work.I'll Be Back! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-26 : 21:58:57
|
| I would go with the dba probably. It keeps the dtaa access separate from the processing - means that someone can't add some processing then find the import fails and end up with things half done and also that the import method can be changed without affecting any of the rest of the process.The import could be into a seperate database specially for this sort of thing so could be non-logged.Depends on what the actual requirements are of course.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-04-27 : 15:01:20
|
| In addition to Nigel's suggestions, I would also recommend importing and querying in SQL Server rather than querying Access through a linked server because it will be no faster than querying Access itself, and will probably be slower. Assuming the Access data doesn't change all that frequently, you can import it on a regularly scheduled basis, once a day, once an hour, however often you like. Once it's in SQL Server you can do all kinds of things to improve querying...indexing the hell out of it, striping the data (if you have a RAID array set up for it) I wouldn't recommend dropping the table though; leave it in place and just delete and re-import each time.Also, what about just moving the data from Access to SQL Server and setting up linked tables in Access? That's really the direction you should be going in. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-04-28 : 02:04:55
|
| And to edit a post, Liquid Metal, you should see on any post that you made, there will be a little pencil & paper icon in the top row of your post (same line with the "Reply with Quote" icon). That pencil & paper icon lets you edit the post. You'll notice that it only appears on your posts and not the others.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
Liquid Metal
Starting Member
4 Posts |
Posted - 2003-04-29 : 02:08:09
|
| Thank you for your suggestions everyone.Thanks AjarnMark.I'll Be Back! |
|
|
|
|
|
|
|
|
OpenDatasource