Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
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

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);
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900