Access server files from sql server

Question

I want to check whether a file exist in an online server from sql server. I have this code

exec master.dbo.xp_fileexist 'http://filepath'.

This function returns value zero for FileExists Column. I think this function will only work for searching files in the local system. If so ,how can I check a file exist in online server from sql server.

xp_fileexist really isn't meant for this type of operation, its an undocumented proc mainly meant for MS internal usage. As for the 'how', I would really question the 'why', what are you trying to do? This operation sounds like it should be in another layer of the application. — cjb110
– cjb110, Commented Apr 22, 2015 at 7:41
I have written a function which need to return the filepath of user uploaded files from website to display it in mobile apps.@cjb110 — Techy
– Techy, Commented Apr 22, 2015 at 7:43
none of that sounds like SQL or database related, it should be in the mobile app code, or maybe a service running on/near the database. — cjb110
– cjb110, Commented Apr 22, 2015 at 7:46
@cjb110 So you mean that we cannot search online files from sql server which is in other server,right?? — Techy
– Techy, Commented Apr 22, 2015 at 7:47
I'm saying there is no native/built in way, and you shouldn't be attempting it anyway. Not that you 'cant' :) You could abuse xp_cmdshell and launch an external process, or possibly using .net CLR functions. — cjb110
– cjb110, Commented Apr 22, 2015 at 7:59

Henrik Staun Poulsen · Accepted Answer · 2015-04-22 07:52:26Z

1

You may be able to use the FileStream feature in newer versions of SQL Server.

I find this link most informative: http://www.codeproject.com/Articles/128657/How-Do-I-Use-SQL-File-Stream

answered Apr 22, 2015 at 7:52

Henrik Staun Poulsen

14.1k4 gold badges25 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cjb110 Over a year ago

FileStream isn't a general purpose IO layer, its meant for storing large objects in the database on the filesystem.

Henrik Staun Poulsen Over a year ago

@cjb110; yes, but it may be a way around various security problems, and it sounded on the OP that it was a specific "data type" that was needed.

Tom V · Accepted Answer · 2015-04-22 08:22:55Z

This sounds like a terrible idea, you shouldn't be wasting schedulers waiting for a http response. It will kill the performance of your system. That being said, if you have no other options, and you are very careful about what and when you are doing stuff like this it actually is possible using ole automation.

First you need to enable ole automation using

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Then you can use xmlhttp to fetch the url

Something along the lines of

sp_OACreate 'MSXML2.XMLHttp', @Obj OUT

will create a msxml2.xmlhttp object

You can then call all methods defined in that object such as

sp_OAMethod @Obj, 'open', NULL, 'GET', 'http://myurl', false

You will have to read up on the object and play around with it for a while, but it certainly is possible

Collectives™ on Stack Overflow

Access server files from sql server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related