sql to access file in different server

Hi,

I have a test file in server A and my DB server is in server B.
Now I want to write query in SSMS from Server B and that query should access the file from server A and parse it to store the data in DB.

I am not sure how to establish the connection to server B and get that file.
I am trying to write a stored procedure for this.
No I should not copy that file.

So any suggestions how to get that file from server B to DB server A.

Thanks,
shragiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
John_VidmarConnect With a Mentor Commented:
Use SQL Server Integration Services (SSIS) to read the file in it's original location, perform data transformations (if required), and store the data in SQL Server.  SSIS can also do other non-database tasks, like move/copy/delete/rename files.
0
 
Matthew BorrussoConnect With a Mentor Commented:
You need to write the query to import, say CSV from a network location.

if this is batch-able, you may want to do something like xcopy \\server1\data.csv d:\temp or whatever.

schedule this transfer to occur in conjunction with your export. Say 6 am export data from whatever to this CSV, 7am perform the batch to xcopy the data to the server. then 8 pm schedule your query to import the csv accordingly..

Times would be your gig.. Granted, this is not the cleanest way to do it, but if you have to import/export, this might just be an easy way if you cannot just create a connector and directly transfer the data table to table.
0
 
Matthew BorrussoCommented:
Again, I just want to say, I know its not clean.. but it should work given limited options.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
shragiAuthor Commented:
does this command removes the file from original server B
0
 
Matthew BorrussoCommented:
you can add a delete command to be run later. say 10am, or just set the commands to constantly overwrite the file names.
your call..

it just depends on how you want to do this.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If the idea it's to import data from a file to a table SSMS already provide this functionality.
Just right-click over the database name, choose Tasks / Import data... and follow the wizard.
0
All Courses

From novice to tech pro — start learning today.