Solved

How to transfer data from sql server without linked server

Posted on 2011-02-18
5
439 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 40

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 40

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL - format decimal in a string 5 56
SQL Server 2008 R2 service pack updates 5 64
SQL Server Express or Standard? 5 63
T-SQL: How to extract records into a new table 7 44
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 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