Solved

Calling a webservice URL from a SQL job or Stored Proc

Posted on 2011-03-08
12
1,218 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
  • 8
  • 4
12 Comments
 
LVL 39

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
 

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 39

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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 39

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 39

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now