MSSQL : converting IMAGE to varbinary(max)

[#1]

Problem 1:

What's the best way to convert columns from IMAGE to varbinary(MAX)?

I'm just testing my application which runs on sql 2008 r2 (but on compatibility mode 80 (sql server 2000).

I probably have a lot of work to do., mainly to do with deprecated features (like no more pb syntax *= but using inner join)/

so I find out that IMAGE datatype will be deprecated ., but it should be a straightforward copy to varbinary(MAX).

now, the issue here is the 3 largest tables in my db are 76gb, 32gb, 7gb

eg 1: this might work (please let me know otherwise). except it fills up the db log and kills it.

update tblcasereporttrans set userreportimage2 = userreportimage

GO

ALTER TABLE tblcasereporttrans DROP COLUMN userreportimage

GO

exec sp_rename 'tblcasereporttrans.userreportimage2','userreportimage','COLUMN'

GO

personally I think a restore from db might be the best way to do it, but how can I get it to restore to a different column type.

restoring the full database into a test db takes  13 minutes, 786.896 seconds (154.579 MB/sec).

I suspect any of the other ways will take much longer.

-----

PROBLEM 2:

so i'm doing a test now with (so I suppose if need be, I can loop it 5000 times and keep the log file in check..(still, its not going to do any good for my offsite/snapshot backups I think)

SET ROWCOUNT 40

GO

WHILE EXISTS (SELECT * FROM tblcasereporttrans where userreportimage2 is null)

BEGIN

update tblcasereporttrans set userreportimage2 = userreportimage where userreportimage2 is null

END

GO

ALTER TABLE tblcasereporttrans DROP COLUMN userreportimage

GO

exec sp_rename 'tblcasereporttrans.userreportimage2','userreportimage','COLUMN'

GO

then I go and pull out my data (ms word document) and it appears to be corrupt.

selectblob  tblCaseReportTrans.UserReportImage

into :lb_doc

from  tblCaseReportTrans

where  tblCaseReportTrans.JobReferenceNo = :ls_refno and

tblCaseReportTrans.Reportid = :ls_reportid and

tblCaseReportTrans.create_date = :ldt_create;

if SQLCA.SQLCode < 0 then

f_display_msg("E", 5000, SQLCA.SQLErrText)

end if

if IsNull(lb_doc) then

Messagebox("error","error in blobdata")

return

end if

f_write_blob(lb_doc, f_gettemppath() + "CTSTEMP-CO-" +ls_reportid +"-" + string(f_getdate(),"yyyymmddhhmm") + "-" + ls_refno + ".doc")

ole_1.objectdata = lb_doc // FAILS HERE

which I find is that although datalength(userreportimage) is 149kb. the datasize on disk (ie: whatever was returned by selectblob) is 32767 only

yet my sql server settings in pbodb125.ini is

PBMaxBlobSize='2147483647'

PBMaxTextSize='2147483647'

so, its a selectblob problem (pb 12.5.2 5652) (maybe not working with varbinary(max) (but works with IMAGE)

so any better suggestions to both problems?

[Former Member]

Hi all, I got an update from support

Previously I modified pbodb*.ini in these locations

C:\Program Files (x86)\Sybase\Shared\PowerBuilder

and

application directory (ie: dev directory c:\pb12.5compile and the network share where i host the .exe/.pbd \\serverA\pb12.5 )

and that works just fine for IMAGE datatype.

however, I was informed there are other locations for pbodb*.ini , so that also needs to be sync with on the development machine

C:\Users\[user]\AppData\Local\Sybase\PowerBuilder 12.5

C:\Users\[user]\AppData\Local\Sybase\PowerBuilder 12.6

I've tested it and it works fine now using ODBC and varbinary(max)