SQLTeam.com | Weblogs | Forums

Download a file from SQL Server

I am trying to download a file and I have been successful if the file is less than 8K. Once the file is larger than 8K, the variable (VARBINARY(MAX)) from the "ResponseBody" returns null. I wonder if I need to use VARBINARY FileStream whatever that is?

If I convert this code to VBScript, then there is no problem at all.

I have included code with two URLs. One works and one does not work.

DECLARE @URL				AS NVARCHAR(MAX)

--THIS does not WORKS because it is more than 8K
SET @URL = 'https://img.icons8.com/emoji/2x/chimpanzee-emoji.png'		

--THIS WORKS because it is less than 8k
SET @URL = 'https://img.icons8.com/emoji/2x/bear-emoji.png'	
----------------------------------------
DECLARE @oREST_API				AS INT
DECLARE @Verb					AS VARCHAR(25) = 'GET'
DECLARE @ReturnCode				AS INT
DECLARE @ReturnMessage			AS INT 
EXEC @ReturnCode = sp_OACreate 'Msxml2.ServerXMLHTTP.6.0', @oREST_API OUT			
EXEC @ReturnCode = sp_OAMethod @oREST_API, 'OPEN', NULL, @Verb, @URL, 'FALSE'	--, @UserName, @Password
EXEC @ReturnCode = sp_OAMethod @oREST_API, 'setRequestHeader', NULL, 'Content-Type', 'image/jpeg'	
EXEC @ReturnCode= sp_OAMethod @oREST_API, 'SEND'
IF @ReturnCode <> 0 EXEC sp_OAGetErrorInfo @ReturnMessage

DECLARE @Status									NVARCHAR(50);
DECLARE @Status_Text							NVARCHAR(50);
DECLARE @Get_All_Response_Headers				NVARCHAR(4000);
EXEC @ReturnCode = sp_OAGetProperty @oREST_API, 'Status',					@Status out;
EXEC @ReturnCode = sp_OAGetProperty @oREST_API, 'StatusText',				@Status_Text out;
EXEC @ReturnCode = sp_OAGetProperty @oREST_API, 'GetAllResponseHeaders',	@Get_All_Response_Headers out;

SELECT
	  @Status												AS [fStatus]						
	, @Status_Text											AS [fStatus_Text]				
	, @Get_All_Response_Headers								AS [fGet_All_Response_Headers]	
----------------------------------------
--PURPOSE: Get URL
DECLARE @Response_URL							NVARCHAR(4000);
EXEC @ReturnCode = sp_OAGetProperty @oREST_API, 'getOption(-1)',			@Response_URL out;			--WORKS Only with Msxml2.ServerXMLHTTP
IF @ReturnCode <> 0
BEGIN
	EXEC sp_OAGetErrorInfo @ReturnMessage
END

DECLARE @FileName		AS NVARCHAR(MAX) = REPLACE(	RIGHT(@Response_URL,	CHARINDEX('/', REVERSE(@Response_URL), 0))		, '/', '')
SET @FileName = 'C:\(Delete) ' + @FileName
SELECT
	  @Response_URL		AS Original_URL
	, @FileName			AS Saved_Location

----------------------------------------
DECLARE @JSONResponseBody			AS VARBINARY(MAX) --= CAST(REPLICATE(CAST('' AS NVARCHAR(MAX)) + 'a', 10000)	AS VARBINARY(MAX))
DECLARE @TableJSON					AS TABLE (MyData	VARBINARY(MAX))

INSERT INTO @TableJSON (MyData)
EXEC @ReturnCode = sp_OAGetProperty @oREST_API, 'responseBody'

SELECT @JSONResponseBody = MyData FROM @TableJSON
SELECT *, LEN(MyData) FROM @TableJSON

EXEC sp_OADestroy @oREST_API
----------------------------------------
DECLARE @ADO AS INT
EXEC sp_OACreate 'ADODB.Stream', @ADO OUTPUT
EXEC sp_OASetProperty	@ADO, 'Type', 1				--Write
--EXEC sp_OASetProperty	@ADO, 'Type', 2				--WriteText

EXEC sp_OAMethod		@ADO, 'Open'

EXEC sp_OAMethod		@ADO, 'Write', NULL, @JSONResponseBody

EXEC sp_OAMethod		@ADO, 'SaveToFile', NULL, @FileName, 2		--jpg	txt

--EXEC sp_OAGetErrorInfo	@ADO
EXEC sp_OAMethod		@ADO, 'Close'
EXEC sp_OADestroy		@ADO
----------------------------------------

Try it and lets see :grinning:

Try it?? I wrote the code and I tried it already.

But did you try VARBINARY approach

Of course I did. The code above is in VARBINARY. I even tried it in VARBINARY FileStream. Anything else?

other scripting languages do it much easier if you want to dabble in python or powershell. the following lines in python.

import requests

url = 'https://img.icons8.com/emoji/2x/chimpanzee-emoji.png'

myfile = requests.get(url)

open('C:/_personal/sqlteam/(Delete) chimpanzee-emoji.png', 'wb').write(myfile.content)

or with powershell pick one of these


#method 1
$WebClient = New-Object System.Net.WebClient
$WebClient.DownloadFile("https://img.icons8.com/emoji/2x/chimpanzee-emoji.png","C:\_personal\sqlteam\chimpanzee-emoji.png")

#method 2
Invoke-WebRequest -Uri "https://img.icons8.com/emoji/2x/chimpanzee-emoji.png" -OutFile "C:\_personal\sqlteam\chimpanzee-emoji.png"


#method 3
wget "https://img.icons8.com/emoji/2x/chimpanzee-emoji.png" -outfile C:\_personal\sqlteam\chimpanzee-emoji.png

Hmmm... That really didn't answer my original question about how to get it to work when the picture (source) is over 8K which when I run the same in VBScript, it worked.

However, your samples open up new doors (for both PowerShell and Python) which gives me new ideas to research into. Thanks

Well, it could work but you really dont want to go down that road. it would take you implementing file stream, which requires a lot.