Solved

Calling a webservice URL from a SQL job or Stored Proc

Posted on 2011-03-08
12
1,408 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

623 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