Article

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

27 Jan 2018 Kamal Pratap
0 Comments 983 Views



Many times we need to send the URL into SMS and email but due to big URL length the SMS has devided into two parts.

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;

 

 

Kamal Pratap

I have 6+ years experience in .Net technologies like Asp.Net, C#, WCF, Web Services, SQL Server, Ajax, LinQ. Currently I am working in Netcarrots Loyalty Services as a Software Developer.

Comments

No coments found to display!

Leave a Comment