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):
RECONFIGURE
Then, I execute THIS query (well, one like it with suitable values substituted):
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.
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
Any idea if there's an equivalent for MySQL..?
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!
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!
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!