After spending some time trying to make a custom query work, I'd like to check if anyone can answer a question for me please!
First, some background.
As I can't rely on the Find Address functionality actually returning correct Lat and Long coordinates for Sweden (they're nearly right, but at the street level I need them to be exactly right!) I decided to adopt the following strategy:
- Add some custom fields to the User Profile including Lat, Long, Icon and a DisplayOnMap attribute.
- Write a stored procedure to "flatten" all of the User Properties into a Temporary Table via a Stored Procedure
- Have that stored procedure then return all of the User Data in the correct format that corresponds to the Map_Points table.
I know that my stored procedure is working as I am returning data with the same column values and column names as per data already existing on the Map_Points table on my db. The problem is that having changed to "Custom Query" in my map config, if I use the query "SELECT * FROM Map_Points" (with no connection string as my data is local) I get back the expected data correctly formatted.
If I replace the Query with a call to my Stored Procedure, I get no data on the map. Remember, I am certain that the column names and data returned are the same as the Map_Points table.
My questions therefore is:
The temp table in my stored procedure is simply a flat representation of the User Profile Data, it is not a table that matches the Map_Points table. I run a SELECT statement against this temp table and reformat the output to match the column names on the Map_Points table. Is this the problem? Does the result set returned have to match the column datatypes as well as the column names? If this is the case I'll have to rework the stored procedure to actually generate the temp table in exactly the same form as the Map_Points table, but your answers may save me a ton of work on this which is why I haven't done it yet ;o)
As always, looking forward to any help you folks can provide.
Regards, Sean