Easy Way to Grab Remote SQL Data
Okay, this is just too cool so I thought I'd share it.
Let's say you have access to a database on a remote server somewhere, and there's data there that you would love to have a copy of locally. Options, options, options...well, if we aren't using SQL Express we have lots of options for migrating the data. But in our case, we ARE using SQL Express, so we don't have easy access to linking servers and other more advanced commands. Wouldn't it be just perfect then if we could simply do a SELECT * INTO MyNewLocalTable from [remote table]? Turns out that I CAN if I turn on an option that is turned off by default. So first, I execute these two lines in a query window (I'm using SQL Server Management Studio Express):
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
Then, I execute THIS query (well, one like it with suitable values substituted):
SELECT * INTO MYDATABASE.dbo.MyNewTable
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=[url to remote server here, including port if needed];User ID=[sql username];Password=[sql password]'
).REMOTEDATABASE.dbo.SourceTable
Worked like a charm! Now I have a local copy of the remote data. In my case I migrated a little over 80,000 rows and it took about a minute or less. Saweeeet.
Doug out.
Subscription Options
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
Re: Easy Way to Grab Remote SQL Data
That's pretty cool! I've been enjoying reading your blog.
Posted by Donnetta on March 12, 2009 at 9:38 PM
Re: Easy Way to Grab Remote SQL Data
Very, very cool! Thanks!
Posted by Seth on March 12, 2009 at 11:41 PM
Re: Easy Way to Grab Remote SQL Data
Cool, I am going to check this out
Posted by John Gag on March 13, 2009 at 9:23 AM
Re: Easy Way to Grab Remote SQL Data
Hey Doug:
Any idea if there's an equivalent for MySQL..?
Any idea if there's an equivalent for MySQL..?
Posted by Jay on March 13, 2009 at 9:45 AM
Re: Easy Way to Grab Remote SQL Data
@jay - Not sure, Jay. I don't use MySQL much...but I wouldn't be at all surprised to find out there IS an equivalent function to OPENDATASOURCE. :)
Posted by dougboude on March 13, 2009 at 10:22 AM
Re: Easy Way to Grab Remote SQL Data
Doug, that's a pretty nifty trick indeed. It does scare me a bit though. Do you not have to turn on some server-side setting on the remote database in order to enable the feature? Even if someone has a valid username/password I'd want to have this setting turned off globally or only allow it for certain users. I have an awesome SQL Server DBA so I'll send him a link to your post and see what he says.
Also, your comment spam method - while probably very effective - is a bit of a pain. I'm not recommending you turn it off or anything, I just wanted to provide my feedback on it "just so you know." Cheers!
Also, your comment spam method - while probably very effective - is a bit of a pain. I'm not recommending you turn it off or anything, I just wanted to provide my feedback on it "just so you know." Cheers!
Posted by Aaron West on March 13, 2009 at 1:40 PM
Re: Easy Way to Grab Remote SQL Data
@Aaron - no, I did nothing at all on the remote server. In fact, the absolute ONLY access my user has is SELECT on a specific database, that's it. As tight as the admins are on the remote server, I'm SURE they didn't enable distributed queries on purpose. In fact, if they knew I did it, they'd probably find a way to block it asap. :)
Posted by dougboude on March 13, 2009 at 1:52 PM
Re: Easy Way to Grab Remote SQL Data
I really like South Park also. For some reason you have a disney banner ad under your recent rant. This is total insanity or pure genius, in an ironic way. Can you recommend the fastest way for a beginner to learn SQL express? Thanks, Phil
Posted by Phil on March 15, 2009 at 12:45 PM
Re: Easy Way to Grab Remote SQL Data
@phil - I'm gonna go with the "pure genius" choice on that one. :) Actually, I have google ads inserted there, and it selects ads based on content. But I do love the irony of it as well.
as far as learning sql express...do you mean learning how to write queries, create tables, use the admin interface, or what? Of course, the best way to learn anything is to just jump in and play with it, which is what i always recommend. Think up a small project you want to create, then attempt to build it, using Google and your peer network (which you can include me in) to ask questions of as you go and can't figure things out. By the time you get it working like you want, you will have learned a WHOLE lot!
as far as learning sql express...do you mean learning how to write queries, create tables, use the admin interface, or what? Of course, the best way to learn anything is to just jump in and play with it, which is what i always recommend. Think up a small project you want to create, then attempt to build it, using Google and your peer network (which you can include me in) to ask questions of as you go and can't figure things out. By the time you get it working like you want, you will have learned a WHOLE lot!
Posted by dougboude on March 15, 2009 at 2:18 PM
Re: Easy Way to Grab Remote SQL Data
I get an error when I try to execute the first RECONFIGRE part in SQL Express. It says the 'Ad Hoc Distributed Queries' is not a valid option or might be an advanced option.
Any Ideas?
The reason I want to do this is because we get provided a view on a remote server (which does not belong to us), so I want to JOIN OPENROWSET from the view with local data from our database.
But I get a No Login-mapping Exists error. I cannot create a linked server because we use Express and the remote data does not belong to us. Any help would be greatly appreciated!
Any Ideas?
The reason I want to do this is because we get provided a view on a remote server (which does not belong to us), so I want to JOIN OPENROWSET from the view with local data from our database.
But I get a No Login-mapping Exists error. I cannot create a linked server because we use Express and the remote data does not belong to us. Any help would be greatly appreciated!
Posted by Jonathan on June 1, 2009 at 2:53 AM
Re: Easy Way to Grab Remote SQL Data
That's pretty awesome... I wish I knew about this a few weeks ago! Still very handy to know.
Posted by Philip on June 28, 2010 at 8:37 PM
Re: Easy Way to Grab Remote SQL Data
are you doing the sp_Configure on remote server or local?
Posted by Caruncles on April 15, 2011 at 10:02 AM
Re: Easy Way to Grab Remote SQL Data
@Caruncles, I wrote this up so long ago that I don't remember for sure! But, I'm reasonably certain that I executed the sp on my local database server to allow it to do the remote data connection.
Posted by dougboude on April 15, 2011 at 11:51 AM
Re: Easy Way to Grab Remote SQL Data
I ran it on local server. Then ran....
SELECT * INTO SfiTest.dbo.tblCustomertest
FROM OPENDATASOURCE(
'SQLOLEDB',
--'Data Source=[url to remote server here, including port if needed];User ID=[sql username];Password=[sql password]'
'Data Source=[OWDC];User ID=[sa];Password=[FishBait]'
).Sfidata.dbo.tblCustomer
but I get this....
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.
I'm looking at the remote server in SSMS. It's in my object explorer and I can navigate all thru the Databases. I can right-click on a table and SELECT TOP ?? I can start a new query while having the remote server hilited and run a query on it and it works (like I'm there)- all that works like it's sittin right here. However, when I try to run queries like the one above I get errors. It's kickin my butt.
SELECT * INTO SfiTest.dbo.tblCustomertest
FROM OPENDATASOURCE(
'SQLOLEDB',
--'Data Source=[url to remote server here, including port if needed];User ID=[sql username];Password=[sql password]'
'Data Source=[OWDC];User ID=[sa];Password=[FishBait]'
).Sfidata.dbo.tblCustomer
but I get this....
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.
I'm looking at the remote server in SSMS. It's in my object explorer and I can navigate all thru the Databases. I can right-click on a table and SELECT TOP ?? I can start a new query while having the remote server hilited and run a query on it and it works (like I'm there)- all that works like it's sittin right here. However, when I try to run queries like the one above I get errors. It's kickin my butt.
Posted by Caruncles on April 15, 2011 at 2:00 PM