Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite

I am trying to import data into a SQL Server. I can import through the Import and Export Data wizard. I cannot import from my machine using BULK IMPORT or OPENROWSET since the file is not on the server machine. How does the IED wizard insert data into its target db? Does it go row by row?

Are there any other possible solutions to importing data from my machine?

share|improve this question
1  
Why can't the file be moved? Why can't the server see the file remotely? – Aaron Bertrand Aug 16 '12 at 20:18
1  
So someone with authority is telling you to "load this data" and someone else with authority is telling you "you cannot move the file"? Sounds like a workplace conflict, not a technical problem. If SQL Server can't see the file, what magic do you expect to happen? – Aaron Bertrand Aug 16 '12 at 20:25
1  
So continuously import data to a server but not be allowed to make it convenient for the server to import the data. Surely if you can run an Import/Export wizard that allows Server A to see file on Server B, you could also write something that copies the file on Server B to Server A, even if it has to copy to your machine first. – Aaron Bertrand Aug 16 '12 at 20:28
3  
@njk your dbas need to be brought into the process. they should be aware of the projects requirements before you start coding it. this is a problem nearly everywhere i've been, but the reality is that just because they're ops and you're dev, that doesn't mean that they're hands-off until deployment. give them requirements and put it in their court. – swasheck Aug 16 '12 at 20:47
1  
No, I didn't say that the Import/Export wizard copies the file, but if it works successfully, that means that your machine can see both the server where the file exists, and the database server. So an easier-to-automate process would be to copy the file to your machine, then copy it to the SQL Server machine, if it can't be arranged much more efficiently, like being allowed to copy the file to the SQL Server directly, or to expose the file to SQL Server. (Also please stop calling it IE; every time you do I think of the Internet Explorer browser.) – Aaron Bertrand Aug 16 '12 at 20:54
show 3 more comments

2 Answers

up vote 4 down vote accepted

SQL Server cannot import what it cannot see. The reason that you can import it via SSMS is because it opens a Windows File Dialog box, which uses your permissions and not SQL Servers to find the file. If you must import the file then the only way you can do this is to make the file accessible to SQL Server.

How you decide to do this is up to you, whether it be through File Shares, the NET USE command or simply by copying the file over to the server that SQL sits on. You may not be able to do any of these, but somebody in your organization can - if this is a regular thing you should also talk to them about arranging either a SQL Job or a scheduled task whichever is easier to do.

share|improve this answer
up vote 0 down vote

A good option for importing or migrating data from or to a database in SQL Server is setting up an ODBC connection to the source. You can create a DSN by specifying which server you're referring to and which database you want to get the data from.

Then use this DSN as the source in your import export wizard.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.