I'm also getting the message that the stored procedure ran successfully but I'm not getting any results. Could a firewall and/or proxy restrict this from truly running?
Update:
It was a proxy causing the problem. Here is a tweak to Corey's code that I found to work for my company's authenticated proxy. I found that trying to use the webproxy.getdefaultproxy() didn't work because this seems to run as a service, possibly, therefore tossing out any user credentials. Hopefully this helps someone out.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.Xml;
using System.Xml.XPath;
using System.Text;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SPGeocode(string Address, string City, string State, string appid)
{
//Declare string for URL
//string URL;
WebRequest request;
try
{
//Replace the spaces in your Address with the plus sign to build the URL needed for Yahoo
Address = Address.Replace(" ", "+");
//Build the URL
request = WebRequest.Create("http://api.local.yahoo.com/MapsService/V1/geocode?appid=" + appid + "&street=" + Address + "&city=" + City + "&state=" + State);
request.Proxy = new WebProxy("ww.xx.yy.zz", 8080);
request.Proxy.Credentials = new System.Net.NetworkCredential("username", "password", "domain");
request.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Create the record and specify the metadata for the columns.
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Latitude", System.Data.SqlDbType.VarChar, 100),
new SqlMetaData("Longitude", System.Data.SqlDbType.VarChar, 100),
new SqlMetaData("GeoCodedAddress", System.Data.SqlDbType.VarChar, 100),
new SqlMetaData("GeoCodedCity", System.Data.SqlDbType.VarChar, 100),
new SqlMetaData("GeoCodedState", System.Data.SqlDbType.VarChar, 2),
new SqlMetaData("GeoCodedZip", System.Data.SqlDbType.VarChar, 10),
new SqlMetaData("GeoCodedCountry", System.Data.SqlDbType.VarChar, 10),
new SqlMetaData("Precision", System.Data.SqlDbType.VarChar, 100),
new SqlMetaData("Warning", System.Data.SqlDbType.VarChar, 100));
using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
{
DataSet ds = new DataSet();
ds.ReadXml(response.GetResponseStream());
//If the response included an error, then the array length is different, 0-7
if (ds.Tables[0].Rows[0].ItemArray.Length.Equals(9))
{
record.SetString(0, ds.Tables[0].Rows[0].ItemArray[0].ToString());
record.SetString(1, ds.Tables[0].Rows[0].ItemArray[1].ToString());
record.SetString(2, ds.Tables[0].Rows[0].ItemArray[2].ToString());
record.SetString(3, ds.Tables[0].Rows[0].ItemArray[3].ToString());
record.SetString(4, ds.Tables[0].Rows[0].ItemArray[4].ToString());
record.SetString(5, ds.Tables[0].Rows[0].ItemArray[5].ToString());
record.SetString(6, ds.Tables[0].Rows[0].ItemArray[6].ToString());
record.SetString(7, ds.Tables[0].Rows[0].ItemArray[7].ToString());
record.SetString(8, ds.Tables[0].Rows[0].ItemArray[8].ToString());
}
else
//If the response did not include an error, then the array length is 0-6
{
record.SetString(0, ds.Tables[0].Rows[0].ItemArray[0].ToString());
record.SetString(1, ds.Tables[0].Rows[0].ItemArray[1].ToString());
record.SetString(2, ds.Tables[0].Rows[0].ItemArray[2].ToString());
record.SetString(3, ds.Tables[0].Rows[0].ItemArray[3].ToString());
record.SetString(4, ds.Tables[0].Rows[0].ItemArray[4].ToString());
record.SetString(5, ds.Tables[0].Rows[0].ItemArray[5].ToString());
record.SetString(6, ds.Tables[0].Rows[0].ItemArray[6].ToString());
record.SetString(7, "Precision Good");
record.SetString(8, "No Error");
}
}
// Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(record);
SqlContext.Pipe.SendResultsRow(record);
// Mark the end of the result-set.
SqlContext.Pipe.SendResultsEnd();
}
catch (Exception ex)
{
//Catch any errors here
throw new Exception("mynewexception", ex);
}
}
};