How to Access content of a file from different server using stored procedure

Question

How to Access content of a file from different server using stored procedure

sumedh-meshram

Valued Member

Points: 62
More actions
June 19, 2009 at 5:41 am

#135372

create table #tempBody
(
Body VARCHAR(8000)
)
set xact_abort on
bulk insert #tempBody from 'ftp://username:password@xx.xx.xxx.xxx/domainname/www/textdocument.txt' with
( FIELDTERMINATOR =',',ROWTERMINATOR = '')
SELECT * FROM #tempBody
Drop table #tempBody
i used this stored procedure to read the content for a file which is located on another server.
but i get this error
Msg 4861, Level 16, State 1, Line 7
Cannot bulk load because the file "ftp://username:password@xx.xx.xxx.xxx/domainname/www/textdocument.txt'" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).
please help me on this issue as i didn't find any solution on the web.
thankx in advance

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

'ftp://username:password@xx.xx.xxx.xxx/domainname/www/textdocument.txt' is not a static filepath..., you have do download the file from your ftp site to a location on the server(or maybe in anetwork share if your account running sql would have access to network shares)

after it is downloaded, you can use bulk insert to load the file contents.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sumedh-meshram Valued Member Points: 62 More actions · Answer 2

Can I download a file through ftp in stored procedure?

Lowell SSC Guru Points: 323547 More actions · Answer 3

it can be done yes...but it's going to involve a bit of work. search for "FTP" on this site, and look at "Scripts" ; there s quite a few contributions there.

one of the better oens i think uses SSIS:http://www.sqlservercentral.com/Scripts/SQL+Server+2005/SSIS/%5B/url%5D

that syncronizes an FTP site to a local folder. then you could process files in that folder....it's one subject that builds upon another.

note that you should use the right tools to do the job. IMHO, SQl server should just do SQL stuff, like your bulk insert. a real programming language likevb.NET/C#.Net, or a scheduled task,or a.bat file doing the FTP from a command line, or something else is far better suited to do the FTP grunt work, as I'm sure you will run into all sorts of permissions issues if you try to do the FTP inside SQL.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!