Solved

How to transfer data from sql server without linked server

Posted on 2011-02-18
5
436 Views
Last Modified: 2012-05-11
I have setup a SQL 08 express database locally.  I want to load a table in my express database based on data in a database on a server. I have read access to the server (via windows authentication), but setting up linked server on that server is not an option.  What is the best method to use (opendatasource, openquery, openrowset, etc) in order to query the server and then insert the results into my sql express  database.   Please provide syntax for using the commands if possible.
0
Comment
Question by:BostonMA
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 34930069
You can use BCP  to send data out/in from SQL.
0
 
LVL 4

Author Comment

by:BostonMA
ID: 34930079
BCP is not supported in sql express
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 34930103
Ooops...sorry
Can you try pipe results out into a file like


exec master..xp_cmdshell N'sqlcmd -E -Q"select * from server_name.dba_name.schema_name.table_name" -h-1 -s"," -W -o"c:\output.txt"', no_output

then read that file in:

declare @filename sysname
set @filename = 'c:\date.txt'
Create table #tempfile (line varchar(8000))
exec ('bulk insert #tempfile from "' + @filename + '"')
select * from #tempfile
drop table #tempfile
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 34931376
>> but setting up linked server on that server is not an option.  <<
You do not have to setup the linked server on that server, you can just create one on the SQL Server Express box.
0
 
LVL 4

Author Closing Comment

by:BostonMA
ID: 34942916
Both of the solutions were helpful in setting up my solution.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
A short film showing how OnPage and Connectwise integration works.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now