Geocoding CLR and t-sql - what's wrong with this code?

Posted on 2011-10-12
Last Modified: 2012-05-12
Ok I'm trying to rewrite a clr to use yahoo's placefinder ... the old one stopped working when yahoo deprecated its previous api.

So I'm not sure what I'm doing wrong here but I can't get this thing to return any data to sql server.  I can't debug it either because its a dll ... I'm not a windows programmer but rather a web developer.  So I don't really know much about creating a a windows form in order to debug it as I need an executable file to trigger a debug.  I can't see anything wrong with the code.  

Is there anyway to write the stacktrace to the sql server output window?  Here is the code:

using System;
using Microsoft.SqlServer.Server;
using System.Net;
using System.Xml.Serialization;

public partial class StoredProcedures
    public static void SPGeocode(int userId, string Address, string City, string State, string Zip, string appid)
        //Declare string for URL
        string URL;

            //Replace the spaces in your Address with the plus sign to build the URL needed for Yahoo
            Address = Address.Replace(" ", "+");

            //Build the URL
            URL = "" + Address.Replace(" ", "+") + "," + City.Replace(" ", "+") + "," + State.Replace(" ", "+") + "," + Zip + "&appid=" + appid;

            //create a new httprequest
            HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(URL);
            //Get the response
            var response = request.GetResponse();
            //Serializing Response XML to C# Class
            XmlSerializer sr = new XmlSerializer(typeof(GeoCoordinates));
            var result = sr.Deserialize(response.GetResponseStream()) as GeoCoordinates;

            // Create the record and specify the metadata for the columns.
            SqlDataRecord record = new SqlDataRecord
                new SqlMetaData("UserID", System.Data.SqlDbType.Int),
                new SqlMetaData("Latitude", System.Data.SqlDbType.VarChar, 100),
                new SqlMetaData("Longitude", System.Data.SqlDbType.VarChar, 100)

            // Mark the begining of the result-set.

            //populate the record
            record.SetInt32(0, userId);
            record.SetString(1, result.result.Latitude);
            record.SetString(2, result.result.Longitude);


            // Mark the end of the result-set.
        catch (Exception ex)
            //Console.WriteLine (ex.StackTrace);

Open in new window

using System;
using System.Xml.Serialization;

[XmlRoot(ElementName = "ResultSet", Namespace = "")]

public class GeoCoordinates
    [XmlElement(ElementName = "Result")]
    public Result result { get; set; }

public class Result
    [XmlElement(ElementName = "longitude")]
    public string Longitude { get; set; }
    [XmlElement(ElementName = "latitude")]
    public string Latitude { get; set; }

Open in new window

Question by:afacts
    LVL 59

    Accepted Solution

    I am not spotting it right away either, but if you believe the code is hitting the Catch block you can throw exception or use SqlPipe.Send ( to send message back to client.

    Author Closing Comment


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    745 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

    14 Experts available now in Live!

    Get 1:1 Help Now