locked
Trying to use geography data type in Reporting Services dataset query RRS feed

  • Question

  • I have a select that picks up latitude and longitude as well as a couple of other fields from a IBM iSeries db2 database...

    select t1.sosono as soso,
    cast( t2.lxlon as varchar(20)) as long,
    cast( t2.lxlat as varchar(20)) as lattit
    from library1.table1 t1 join library2.table2 t2 on t1.loc = t2.loc
    where t1.code='*CL' and t1.rtdt = '0001-01-01'

    ...and I would like to include a generated field of type geography from the long and lattit fields, imagining it to look something like...

    select t1.sosono as soso,
    cast( t2.lxlon as varchar(20)) as long,
    cast( t2.lxlat as varchar(20)) as lattit,

    cast (Point(t2.lxlat, t2.lxlon, 4326) as geography) as position

    from library1.table1 t1 join library2.table2 t2 on t1.loc = t2.loc
    where t1.code='*CL' and t1.rtdt = '0001-01-01'

    ...but I don't seem to be able to get it right.

    By way of a trial I used ...

    SELECT geography::Point(-77.010996, 38.890358, 4326)

    ...in a query on MS SQL Server management studio and it returned a location...

    0xE6100000010CC2189128B44053C0B1DD3D40F7714340

    ...no problem but when I tried using the same query in a dataset in reporting services and try to save the query it throws the alert starting...

    "Could not create a list of fields for the query...Cause:  An attempt was made to open cursor C000001 which refered to prepared statement S00000.  Statement S000001 has one of the following conditions:--the statement has never been prepared.--the statement was prepared in another program  or another call of this program...."

      Could someone kindly show how to use the geography data type and spatial functions in Reporting Services?

    Thanks much for any help, Roscoe

    Wednesday, August 6, 2014 3:54 PM

Answers

  • Hi rpfinn,

    According to your description, you can query a geography data successfully in SQL Server Management Studio, but same query is failed to executed in Reporting Services. Right?

    In Reporting Services, all the query we put in Query Builder will be sent to database and execute. So if the query is executed successfully in SSMS, it supposed to run properly in Reporting Services. Please try to reconnect the database engine and restart the Report Server. As we tested in our local environment, it also shows the correct result.

    If you have any question, please feel free to ask.

    Best Regards,
    Simon Hou

    • Marked as answer by rpfinn Thursday, August 7, 2014 4:09 PM
    Thursday, August 7, 2014 12:52 PM

All replies

  • Hi rpfinn,

    According to your description, you can query a geography data successfully in SQL Server Management Studio, but same query is failed to executed in Reporting Services. Right?

    In Reporting Services, all the query we put in Query Builder will be sent to database and execute. So if the query is executed successfully in SSMS, it supposed to run properly in Reporting Services. Please try to reconnect the database engine and restart the Report Server. As we tested in our local environment, it also shows the correct result.

    If you have any question, please feel free to ask.

    Best Regards,
    Simon Hou

    • Marked as answer by rpfinn Thursday, August 7, 2014 4:09 PM
    Thursday, August 7, 2014 12:52 PM
  • Thanks for the reply Simon, but nevermind issue is that geographic data type is not supported by the target db db2 for iSeries server.  I imported the lat and long data into a ms sql server db on my windows server and ran the query against that and it works fine.  I guess I could also use a temp table instead.  Sorry for the bother, Roscoe
    Thursday, August 7, 2014 4:09 PM