HTTP Streaming in SQL

Question

Post reply

HTTP Streaming in SQL

oradbguru

Hall of Fame

Points: 3126
More actions
December 17, 2014 at 12:07 pm

#299574

Hello, is it possible to take a URL that is stored in a database and stream the actual document to a file system? I have a table in my database with 38k URL's along with othe key elements associated with the file. I would like to stream the actual document to a disk, using the key elements in the naming of the file. Attached is an example of the data:
I would like to extract the file and name it like:
9184 Montes Marcos 19631130 2014121712493

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you'd do this in a programming language. but, if you really wanna do it in TSQL...keep reading....

the only way to do it is via CLR, because SQL, natively, does not have the ability to read web sites, or write to disk.

I happen to have a suite of CLR functions that can do all you ask, and i can post them here if you want, but you'd have to create the CLR project and deploy them.

here's a link to the CLR for reading an url:

http://www.sqlservercentral.com/Forums/Topic1382247-391-1.aspx

and another link, to another CLR example:

http://www.sqlservercentral.com/Forums/Topic1321529-391-1.aspx

so my code would be something like this:

DECLARE @NewFileName varchar(200),

@url varchar(1000);

;WITH MyCTE([person_nbr],[other_id_number],[last_name],[first_name],[date_of_birth],[ExamDate],[Description],)

AS

(

SELECT '117445','19187','Montossa','Marcos','19601225','12/17/11 12:49 PM','Echocardiogram','http://srv-web/IVReport.asp?ServerName=CRLRH0NYI81&uid=1.2.840.113619.2.118.788132489.1418135771.0.51' UNION ALL

SELECT '12334','190793','Green','Dorothy','19361229','12/17/11 12:48 PM','Echocardiogram','http://srv-web/IVReport.asp?ServerName=CRLRH0NYI81&uid=1.2.840.113619.2.118.788132489.1418135771.0.50' UNION ALL

SELECT '112334','118785','Costas','Bonnie','19931115','12/17/11 12:04 PM','Echocardiogram','http://srv-web/IVReport.asp?ServerName=CRLRH0NYI81&uid=1.2.840.113619.2.118.788132489.1418135771.0.49' UNION ALL

SELECT '443356','22950','Galligar','Kate','19410621','12/17/11 11:31 AM','Stress echocardiogram','http://srv-web/IVReport.asp?ServerName=CRLRH0NYI81&uid=1.2.840.113619.2.118.337501479.1418418390.0.43' UNION ALL

SELECT '1222323','91882','Roscoe','Linda','19991111','12/17/11 11:30 AM','Stress echocardiogram','http://srv-web/IVReport.asp?ServerName=CRLRH0NYI81&uid=1.2.840.113619.2.118.337501479.1418418390.0.42'

)

SELECT @NewFileName = CONVERT(varchar,person_nbr)

+ '_'

+ last_name

+ '_'

+ first_name

+ '_'

+ convert(varchar,date_of_birth)

+ CONVERT(VARCHAR,getdate(),112)

+ '-'

+ REPLACE(CONVERT(VARCHAR,getdate(),114),':','')

+ '.html',

@url=

FROM myCTE --represents your table

WHERE person_nbr= 117445 --one record at a time: script it or loop it.

PRINT @FileName

PRINT @url

CREATE TABLE #Results(ResultsText varchar(max))

INSERT INTO #Results

SELECT dbo.CLR_WebQuery(@url)

EXECUTE CLR_ExportQueryToCustomDelim @QueryCommand = 'SELECT ResultsText FROM #Results',

@FilePath = 'C:\Data\',

@FileName = @NewFileName,

@IncludeHeaders = 0, --no header, just the string we have in hte table

@CustomDelimiter = ''--no delimiter

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!

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

Step 1 would be to figure out how to write code to make a web request and save the response to a file, in a .Net framework language. This is not difficult once you realize T-SQL is not the answer.

Then if you need this functionality in SQL Server on demand, putting this in a CLR stored procedure would be the best solution. If it could be a scheduled batch process, then a script task in SSIS could do it. If this is a one-time data migration it could be a standalone program in any language, maybe even PowerShell.