May 6, 2014 at 1:42 am
Good morning,
I am supporting an application which stores Geometry values in tables for use with Spatialware this was before SQL Server 2008 and the inbuilt Spatial functions - I am using:
Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
We have moved between different versions of a program called "Mapinfo" and have found that something has changed between these two versions which has caused the spatial data written back to SQL Server (to a Text column) to change. This has had a knock-on affect on another process, causing it to fail.
So, my question is - given binary data in a Text type field in SQL server, is it possible to "Fix" a changed (I assume header) in the record; i.e.
In this order – original shape, modified in mapinfo 9.5, modified in mapinfo 10.5
0x01070000000100000001030000000100000005000000 B3B0EEFF370F234117045FF6EF990141DF4B2900C81 - WORKS OK
0x01070000000100000001030000000100000004000000 B3B0EEFF370F234117045FF6EF990141DF4B2900C81 - WORKS OK
0x0107000000010000000 4000000 B3B0EEFF370F234117045FF6EF990141DF4B2900C81 - FAILS
I hope the above is clear. The missing section in the 3rd line appears to be "103000000010000000" which is the only change in the binary representation of the data between the two software versions - it is this missing data which is causing other processes to fail.
Is it possible to insert binary data in an existing text column, into existing binary data (text type) stored in there, to an offset, to make the 3rd line above look like the 2nd line?
I have looked at the .write and updatetext commands but don't seem to be getting anywhere fast.. Any pointers would be much appreciated.
thank you
Duncan.
May 6, 2014 at 1:46 am
What about STUFF? Not sure it works with the text data type.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 6, 2014 at 1:54 am
Text is a deprecated datatype - are you able to consider a datatype change? Varchar(max) or, even better, Varchar(nnn) if your column width won't exceed 8000.
If yes, this update should be fairly trivial using STUFF().
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.
May 6, 2014 at 1:58 am
Am trying this...
-- Use a table variable to cast the trace
-- header from image to varbinary(max)
DECLARE @header TABLE (
header varbinary(max)
)
-- insert the trace header into the table
INSERT INTO @header
SELECT AD.SW_GEOMETRY
FROM Abstract_DPB AD
WHERE AD.SW_MEMBER=40967
-- update the byte at offset 390 with version 10 (SQLServer 2008)
-- instead of version 11 (SQLServer 2012)
UPDATE @header
SET header = STUFF(header,10,0,CONVERT(varbinary(max),0x103000000010000000))
-- write the header back to the trace table
UPDATE Abstract_DPB
SET SW_GEOMETRY = (SELECT header FROM @header)
WHERE SW_MEMBER=40967
but getting this error... "SQL.sql: Error (36,1): Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query."
I've added the convert in the update above..?
May 6, 2014 at 2:00 am
Phil Parkin (5/6/2014)
Text is a deprecated datatype - are you able to consider a datatype change? Varchar(max) or, even better, Varchar(nnn) if your column width won't exceed 8000.If yes, this update should be fairly trivial using STUFF().
Thanks Phil, unfortunately the TEXT type is mandatory for the Spatialware software we are using, hence we are stuck on SQL Server 2005 until we get rid of it and move to SQL Server spatial types...
D.
May 6, 2014 at 2:11 am
UPDATE Abstract_DPB
SET SW_GEOMETRY = STUFF(SW_GEOMETRY,20,0,0x103000000010000000)
WHERE SW_MEMBER=40967
"SQL.sql: Error (38,1): Argument data type image is invalid for argument 1 of stuff function."
ah...
May 6, 2014 at 3:07 am
No error from this:
-- header from image to varbinary(max)
DECLARE @header TABLE (
header varbinary(max)
)
INSERT INTO @header
SELECT AD.SW_GEOMETRY
FROM Abstract_DPB AD
WHERE AD.SW_MEMBER=40967
UPDATE @header
SET header = CAST(STUFF(header,19,0,103000000010000000) AS varbinary(max))
-- write the header back to the trace table
UPDATE Abstract_DPB
SET SW_GEOMETRY = (SELECT header FROM @header)
WHERE SW_MEMBER=40967
just need to work out where the correct data needs to be inserted to.. so working now, thanks for all comments.
D.
May 6, 2014 at 3:22 am
Glad you got it solved and thanks for posting back.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply