Calling a webservice URL from a SQL job or Stored Proc

I need to "execute" a Web Service, URL, for every mlsnum returned by the query below:


select mlsnum
from dbo.mls_...tbl
where Created_on > 'dec  1 2009'
and (Longitude is null or  Latitude is null)
order by Created_on, mlsnum desc

I need a job that calls the following URL with the MLSnum appended to the end.  

http://www.xyz.com/bingmaps/WS/GeocodeXxByHttp/?username=abcde&save=true&mlsnum=11-511979

I don't want to write code, a CLR, etc. or use windows scheduler, etc. mainly because there are other actions that are being undertaken by the same SQL job that I want to do the above.  I want the entire process to be database driven.

Thank you in advance.
D
dteshomeAsked:
Who is Participating?
 
dteshomeConnect With a Mentor Author Commented:
I figured it out!  The problem is with the "extended chars" - & etc.

Placing a ^ infront of the extended chars fixes the problem. Please see below

set @sqlstr = (select top 1 '"C:\Program Files (x86)\Internet Explorer\iexplore.exe" http://www.themls.com/bingmaps/WS/GeocodeByHttp/?username=X64251^&save=true^&mlsnum=' +  @mlsnum) --cast(1234 as sysname)
0
 
lcohanDatabase AnalystCommented:
For one single page you can create a job using SQL Server Agent, and create a single step of type "Operating System (CmdExec)" with the above command.

"C:\Program Files\Internet Explorer\iexplore.exe" "http://yoursite.com/yourpage.aspx

Or for 64-bit Windows:

"C:\Program Files (x86)\Internet Explorer\iexplore.exe" "http://yoursite.com/yourpage.aspx

To run it for all the values as you mentioned you put the query below in a cursor, build dynamic SQL and execute it with cmdshell depending wht os you have - I give example with 32 bit:


declare @sqlstr varchar(1000)
set @sqlstr =
(
select top 1 '"C:\Program Files (x86)\Internet Explorer\iexplore.exe" "http://www.xyz.com/bingmaps/WS/GeocodeXxByHttp/?username=abcde&save=true&mlsnum="' + cast(mlsnum as sysname)
from dbo.mls_tbl
where Created_on > 'dec  1 2009'
and (Longitude is null or  Latitude is null)
order by Created_on, mlsnum desc
)

--then execute each line in cursor like:

exec xp_cmdshell @sqlstr




0
 
dteshomeAuthor Commented:
Thanks, I will try it and get back to you.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
dteshomeAuthor Commented:
Hi, Icohan

I run the following for a single mlsnum and the query hand for over 20 minutes, I cancelled by trying to kill the SPID, but it is still saying canceling Query for the last 30 minutes:

declare @sqlstr varchar(1000), @mlsnum varchar(16)
set @mlsnum = '11-512227'
--
set @sqlstr =
(
select top 1 'C:\"Program Files (x86)"\"Internet Explorer"\iexplore.exe http://www.xyz.com/bingmaps/WS/GeocodeXxByHttp/?username=abcde&save=true&mlsnum=' +  @mlsnum + '' --cast(1234 as sysname)
)

print @sqlstr

--exec xp_cmdshell @sqlstr

The print gave me this ... so I believe it was good ...
what did I do wrong?

C:\"Program Files (x86)"\"Internet Explorer"\iexplore.exe http://www.xyz.com/bingmaps/WS/GeocodeXxByHttp/?username=abcde&save=true&mlsnum=11-512227

0
 
dteshomeAuthor Commented:
Hi, Icohan

Pls ignore previous comment, my bad, using wrong URL!
0
 
dteshomeAuthor Commented:
Hi, Guys:

I get the error (please see below) 'save' is not recognized as an internal or external command,
when I execute XP_CMDSHELL

 I believe I need an escape character …????

I will appreciate your input, only if you can spare the time.

Thanks,
Dan


declare @sqlstr varchar(1000), @mlsnum varchar(16)
set @mlsnum = '11-512323'
--
set @sqlstr = (select top 1 '"C:\Program Files (x86)\Internet Explorer\iexplore.exe" http://www.theis.com/bingmaps/WS/GeocodeByHttp/?username=X11251&save=true&mlsnum=' +  @mlsnum)


print @sqlstr
-- above returns the string below:
-- "C:\Program Files (x86)\Internet Explorer\iexplore.exe" http://www.themls.com/bingmaps/WS/GeocodeByHttp/?username=X64251&save=true&mlsnum=11-512323
--
exec xp_cmdshell @sqlstr
--
-- above gives error below:
--'save' is not recognized as an internal or external command,
--operable program or batch file.
--'mlsnum' is not recognized as an internal or external command,
--operable program or batch file.
--NULL


-- NOTE:
--If I take the URL http://www.themls.com/bingmaps/WS/GeocodeByHttp/?username=X64251&save=true&mlsnum=11-512323
--and paste in IE, it works fine.
0
 
lcohanDatabase AnalystCommented:
Works fine for me - try execute the string @sqlstr in a CMD prompt or window the command instead of using sp_cmdshell ON THE SQL SERVER box not local to your computer - just copy/paste following on your SQL box to test it works:

"C:\Program Files (x86)\Internet Explorer\iexplore.exe" http://www.themls.com/bingmaps/WS/GeocodeByHttp/?username=X64251&save=true&mlsnum=11-512323
0
 
dteshomeAuthor Commented:
Yes, Icohan, that has been working (exec string from cmd prompt).  My need is to be able to exec the same string from a sql job from an exec xp_cmdshell command.
That is what returns the following:
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
'save' is not recognized as an internal or external command,
operable program or batch file.
'mlsnum' is not recognized as an internal or external command,
operable program or batch file.
NULL
0
 
lcohanDatabase AnalystCommented:
Ok but did you executed the string ON the SQL Server computer? Not your local workstation in CMD prompt?
BTW - could you let us know the OS version and SQL Version where you try all these?
Older OS where "Program Files" were "ProgramFiles" don't need the double quotes and I believe even on newer there are differencies. I ran it successfull on Win2003/2008 having SQL 2005/2008 installed.
Only difference I don't havge the & in my URL but you get for some odd reason the error

'C:\Program' is not recognized as an internal or external command

Which means it is stumbeling on the space between Program and Files but you have the "" doble quotes around it - strange..
0
 
dteshomeAuthor Commented:
I am running this from the local computer, the Production SQL Server.

Here are the specifics:

Win 2003 Server , SP2
Microsoft SQL Server 2005 - 9.00.4053.00 (X64)   May 26 2009 14:13:01  
 Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
0
 
dteshomeAuthor Commented:
No sol'n were provided by anyone else.
0
 
lcohanDatabase AnalystCommented:
No offence but I think what you just did was cheap because I believe I gave you the code to "Calling a webservice URL from a SQL job or Stored Proc" and obviously could not execute your URL in my environment to figure out your string issue....anyway good luck!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.