?
Solved

Calling a webservice URL from a SQL job or Stored Proc

Posted on 2011-03-08
12
Medium Priority
?
1,492 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:dteshome
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
12 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 35073393
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
 

Author Comment

by:dteshome
ID: 35085514
Thanks, I will try it and get back to you.
0
 

Author Comment

by:dteshome
ID: 35087383
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:dteshome
ID: 35087417
Hi, Icohan

Pls ignore previous comment, my bad, using wrong URL!
0
 

Author Comment

by:dteshome
ID: 35101843
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
 
LVL 40

Expert Comment

by:lcohan
ID: 35108969
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
 

Author Comment

by:dteshome
ID: 35109561
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
 
LVL 40

Expert Comment

by:lcohan
ID: 35109760
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
 

Author Comment

by:dteshome
ID: 35139893
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
 

Accepted Solution

by:
dteshome earned 0 total points
ID: 35159129
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
 

Author Closing Comment

by:dteshome
ID: 35187403
No sol'n were provided by anyone else.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35201205
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

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question