September 2, 2010 at 3:48 pm
How can I go about accessing information from one server when i'm on a different one. I looked into linked servers but i'm not very familiar with it. I also looked at the Import/Export Data to try to create a file that I could run as a sql job to just copy the table from one server to another. It doesn't have to be real time. Do i have to use SSIS? If so can anyone point me in a good direction.
Any thoughts?
September 2, 2010 at 3:58 pm
You're on the right track in both cases. For non real time/overnight updates, you'd build an SSIS job and then schedule it with SQL Agent.
If you need real-time access, you need to use a linked server, or get into more complicated cases like replication to the local server.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 2, 2010 at 4:04 pm
Any good place to start to learn how to do it?
September 2, 2010 at 4:21 pm
Off a couple of google hits I briefly checked before deciding this one seemed sound:
SSIS: http://www.mssqltips.com/tutorial.asp?tutorial=200
Linked Servers: I'd recommend just using Books Online. They're not that particularly difficult once you understand the security questions.
Your best bet (for simplicity) would be to create a 'linked_user' sql account on the foreign box with read rights to the table(s) you need and use that as your connect as in the security in linked server, will save you some hassles down the road.
Try not to use joins between the two servers (IE: FROM dbo.localtable JOIN OtherServer.OtherDB.dbo.OtherTable ON x=y) because it needs to bring the entire table across to pull that off, bad on traffic and network if it's a largish table. Best is to grab what you need off the foreign table (or by preference, proc), and drop it into a local temptable/tablevar.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 2, 2010 at 4:34 pm
You can build a simple SSIS package using the import/export wizard in SSMS.
September 2, 2010 at 4:37 pm
Steve Jones - Editor (9/2/2010)
You can build a simple SSIS package using the import/export wizard in SSMS.
Yeah, that link above walks you through that as the first step. My laziness shone through. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 3, 2010 at 7:45 am
Thanks, I kinda got it to work with the import/export wizard. The only problem i'm having now is it fails if I run it again. The table does need to be updated once a day. My thought is to drop the table then re-import every time. It only needs to be ran once a day after business hours.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply