Sql geo-spatial linked server query

  • I have one question on the TSQL Linked Server Query. Linked Server is GIS enforced so we pass the coordinates to that server which it returns the data from the Linked Server. Please find the below-working query.
    DECLARE @input varchar(max), @sql varchar(max);

    SET @input = N'((-119.470830216356 46.2642458295079,-119.470722927989 46.2642050348762,-119.470076515615 46.2647075484513,-119.470240130371 46.2647075484512,-119.470830216356 46.2642458295079))'
    BEGIN 
          SELECT @sql = 'select * from openquery([LinkedServerName],''DECLARE @b-2 geometry;      
                                       SET @b-2 = geometry::STGeomFromText(''''POLYGON '+ @input + ' '''', 4326);  
                                       SET @b-2 = @B.MakeValid();       
        SELECT * from [Database].[Table] AS b      
    where b.Shape.STIntersects(@b.STCentroid()) = 1'')'
    END
    EXEC(@sql)

    But the issue is sometimes we have to pass more than 8000 characters to the input parameter @input since it is varchar(max) and EXEC command both have an 8000 character limitation. So we are trying to get rid of Dynamic SQL so that we can pass the input using 2 input variables (We have implemented splitting the input into subsets each of 8000 characters in our C# code and sending them as 2 different inputs to the SQL Query). We have tried the below query in the Actual Server (Linked Server) which is working fine.
    DECLARE @b-2 geometry
    SET @input = N'((-119.470830216356 46.2642458295079,-119.470722927989 46.2642050348762,'
    SET @input2 = N'-119.470076515615 46.2647075484513,-119.470240130371 46.2647075484512,-119.470830216356 46.2642458295079))'

    SELECT @b-2 = geometry::STGeomFromText('POLYGON ' + @input + @input2 + '', 4326)
    SELECT @b-2 = @B.MakeValid()
    SELECT * FROM [Database].[TableName] AS b
    WHERE b.Shape.STIntersects(@b.STCentroid()) = 1

    We tried below SQL Linked query in our local server but it is throwing below error


    DECLARE @input varchar(max), @input2 varchar(max);
    SET @input = N'((-119.470830216356 46.2642458295079,-119.470722927989 46.2642050348762,'
    SET @input2 = N'-119.470076515615 46.2647075484513,-119.470240130371 46.2647075484512,-119.470830216356 46.2642458295079))'

    SELECT * FROM OPENQUERY([LinkedServerName],
    'DECLARE @b geometry;
    SELECT @b = geometry::STGeomFromText(''''POLYGON ' + @input + @input2 + '' ', 4326);
    SELECT @b = @b.MakeValid();
    SELECT * FROM [DatabaseName].[TableName] AS b
    where b.Shape.STIntersects(@b.STCentroid()) = 1') AS AD 

    In the above query, issue has been highlighted in the attached image.

    Any help is appreciated.

  • Can you create a stored proc on the linked server and then call it with the necessary parameters?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin - Friday, July 13, 2018 10:36 AM

    Can you create a stored proc on the linked server and then call it with the necessary parameters?

    Sounds like a plan , but unfortunately It is not feasible for us to create objects on the linked server.

  • OPENQUERY takes only a single 'query' parameter, with a maximum length of 8,000, so splitting up the input as you are doing is not going to work (because you are putting the inputs back together as part of a single OPENQUERY call.

    What might work, however, is making multiple OPENQUERY calls. It's cumbersome and expensive though.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • sravanthitadikonda - Friday, July 13, 2018 11:51 AM

    Phil Parkin - Friday, July 13, 2018 10:36 AM

    Can you create a stored proc on the linked server and then call it with the necessary parameters?

    Sounds like a plan , but unfortunately It is not feasible for us to create objects on the linked server.

    I'm pretty sure you are not going to be able to use OPENQUERY with any input that would exceed 8,000 characters, and trying to do so in pieces doesn't sound even remotely practical for a set of geo-spatial object coordinates.   You may have no viable alternative than to convince someone to put a stored procedure in place on the linked server.   Even executing that stored procedure is going to be problematic, because even then, OPENQUERY may not be viable due to the 8,000 character limitation.   If you can't run it as EXECUTE LinkedServerName.DatabaseName.SchemaName.StoredProcedureName @ParameterName = value, then you may not be able to do it at all without using a program that connects to the Linked Server directly instead of as a Linked Server.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply