November 8, 2010 at 12:14 pm
We need to pull data from a SQL Server using an intermediate server that does not have SQL Server installed. The company that is hosting our client's data (it's a company that sells a software solution where they can host the DB for it or you can have the DB installed on your network) will only allow access to their SQL Server from an an intermediate server that does not have SQL Server installed.
The hosting company says we can use ODBC to connect to the DB server and pull text files to the intermediate server. The problem is there seem to be so many options for doing this that I don't know which one is best. All we need to do is to run about 10 SELECT statements from the intermediate box (which does not have SQL Server installed) that hit the SQL Server box to create separate text files for each one on the intermediate box. Does anyone know what I'd need for this and maybe a link for getting started?
Thanks!
November 8, 2010 at 2:31 pm
As long as your SQL Server Service account has write access to a drive on the intermediate server you can use BCP or SSIS to create the text files. Serch the net or books on line for these. There are lots of good sites.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 8, 2010 at 3:41 pm
Thanks - that would make it a lot easier, but we cannot use the hosted server at all. The solution has to completely reside on the intermediate server and pull from the hosted server without using BCP or SSIS.
November 8, 2010 at 4:01 pm
OK, use BCP or SSIS on the SQL server to dump the files locally, then set up a Windows scheduled job to Robocopy the files through the system. No SQL required on the intermediate system.
You biggest issue with this is managing timing, but this can be done with control files and logic.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 8, 2010 at 4:58 pm
Thanks again - but again, we can't use the hosted SQL Server (A) at all. That means no BCP, SSIS, Robocopy, or anything else. All code must reside on the intermediate server (B). Which is basically just a flat windows server with ODBC and whatever .exe we write to do the work. The vendor who has these servers has no interest in helping to deliver this data, which could explain some of the confusion here, since that's not a common situation. Their SQL server (A) will only allow connections, there will be no jobs, BCP, or SSIS from server (A) to facilitate the pull of data to server (B), the intermediate server.
November 8, 2010 at 5:09 pm
Sorry, I didn't realise your restiction was that you had to do ALL the work from the intermediate server. What all can you install on this server? Are they happy with you installing BCP or OSQL on the intermediate server? These are basically just executables.
If you can do either of these I'd recommend the BCP.exe and then use it to suck the data from the source server. I've used BCP like this before althoigh from a server with a full SQL install, but I've asked around and this should work.
CHeers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 8, 2010 at 5:14 pm
malik_nagel (11/8/2010)
Thanks again - but again, we can't use the hosted SQL Server (A) at all. That means no BCP, SSIS, Robocopy, or anything else. All code must reside on the intermediate server (B). Which is basically just a flat windows server with ODBC and whatever .exe we write to do the work. The vendor who has these servers has no interest in helping to deliver this data, which could explain some of the confusion here, since that's not a common situation. Their SQL server (A) will only allow connections, there will be no jobs, BCP, or SSIS from server (A) to facilitate the pull of data to server (B), the intermediate server.
:blink: Time to fire the vendor.
Um. Uh. Hrm. I assume you're using windows scheduler to fire off your .exe?
What about flat file delivery, dropoff/pickup on the intermediate server? Kinda like an inhouse ftp system?
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply