How to convert URL to Shorten URL or Tiny URL in SQL Server

In next article Convert URL to Shorten URL or Tiny URL in Asp.Net with C#  we will explain how to create tiny URL in .Net using C#.

So in this case we need to create the short URL like 'http://tinyurl.com/yaakqa74'

This article will help you to generate shorten URL using Tiny URL API in sql server.

Declare @InputURL varchar(200),@CreateURL varchar(500),@Obj Int,@TinyURL varchar(500)

Set @InputURL='http://www.freshcodehub.com'

--//Create Tiny URL
set @CreateURL = 'http://tinyurl.com/api-create.php?url='+ @InputURL
Select @CreateURL
exec sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUT 
exec sp_OAMethod @Obj, 'Open', NULL, 'POST', @CreateURL, false 
exec sp_OAMethod @Obj, 'send'
exec sp_OAGetProperty @Obj, 'responseText', @TinyURL OUT 
exec sp_OADestroy @Obj

Select @TinyURL AS TinyURL

Output : http://tinyurl.com/yaakqa74

Sometimes if you did not intall sp_OACreate in SQL Server you will get the error given below.

Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.

To solve this issue you have to install sp_OACreate in sql server.

Execute the below query in the query analyzer.

sp_configure 'show advanced options', 1 

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