Click here to Skip to main content
13,704,361 members
Rate this:
 
Please Sign up or sign in to vote.
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 geometry;
                    SET @b = geometry::STGeomFromText(''''POLYGON '+ @input + ' '''', 4326);
                    SET @b = @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 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 = geometry::STGeomFromText('POLYGON ' + @input + @input2 + '', 4326)
SELECT @b = @b.MakeValid()
SELECT * FROM [Database].[TableName] AS b
WHERE b.Shape.STIntersects(@b.STCentroid()) = 1


What I have tried:

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

But getting below error
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '+'.

Throwing an error here in the above query
POLYGON ' + @input
Posted 13-Jul-18 5:21am
Updated 13-Jul-18 10:08am
Comments
RedDk 13-Jul-18 13:26pm
   
Just a hunch ... have you ever tried using the BCP and outputting SELECT results as .csv then BULK LOAD the (whole) thing back into the table? Sounds possible, yes?

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Your "before and after" single quotes "changed". You need to "simplify" your string handling so you get a better handle on what's happening.

SET @b = geometry::STGeomFromText(''''POLYGON '+ @input + ' '''', 4326);


SELECT @b = geometry::STGeomFromText(''''POLYGON ' + @input + @input2 + '' ', 4326);
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05-2016 | 2.8.180920.1 | Last Updated 13 Jul 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100