Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inserting binary string at an offset to exsting data in a Text type field Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2014 1:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:36 AM
Points: 32, Visits: 471
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.
Post #1567814
Posted Tuesday, May 6, 2014 1:46 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 13,277, Visits: 11,066
What about STUFF? Not sure it works with the text data type.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1567816
Posted Tuesday, May 6, 2014 1:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 5,170, Visits: 12,024
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().



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1567818
Posted Tuesday, May 6, 2014 1:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:36 AM
Points: 32, Visits: 471
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..?
Post #1567820
Posted Tuesday, May 6, 2014 2:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:36 AM
Points: 32, Visits: 471
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.
Post #1567821
Posted Tuesday, May 6, 2014 2:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:36 AM
Points: 32, Visits: 471
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...
Post #1567825
Posted Tuesday, May 6, 2014 3:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:36 AM
Points: 32, Visits: 471
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.
Post #1567841
Posted Tuesday, May 6, 2014 3:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 13,277, Visits: 11,066
Glad you got it solved and thanks for posting back.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1567847
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse