on 10-29-2014 4:05 AM
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?
Hello,
Problem 1:
Regarding to your legitimate concerns about the log file, you could test to set the database recovery model in SQL Server to 'simple' during the conversion, that should keep file size of the log quite low.
http://msdn.microsoft.com/de-de/library/ms189275.aspx
When we did our conversion from image to varbinary (max) some time ago we even did a direct ALTER TABLE not using a second column.
ALTER TABLE tblcasereporttrans ALTER COLUMN userreportimage VARBINARY ( MAX );
But in our case the max size of the affected table was about 20GB, so a lot smaller than your example.
Problem 2:
When we changed the datatype we hadn't to change anything else in the code. So SELECTBLOB works fine with varbinary(max) (we use PB 12.5 too). Could this be a driver issue? How to you connect to your db?
On our side we use SQLNCLI10 (SQL Native Client for SQL Server 2008).
HTH
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
If possible you should do a cross check with SNC instead of ODBC. It could be that varbinary (max) is not supported with ODBC. It's not clearly stated that ODBC won't function, but documentation always mentions SNC only.
[...]The PowerBuilder SNC interface maps the Varbinary(max) datatype to a BLOB datatype, so to retrieve or update filestream data, use the SelectBlob or UpdateBlob SQL statements, respectively.[...]
Have you tried it with smaller blobs to narrow the problem?
Since varbinary (max) is mapped to BLOBs, maxblobsize could be an issue.
FYI:
http://www.linkedin.com/groups/Help-on-varbinary-max-issue-1082737.S.245805926
Hi, I've marked you as correct.
a) I tried an odbc connection using sql server native client 10. but it didn't work. (ie: under windows odbc, the selected driver sql server native client
b) my pbodb125.ini is deployed with the app and also updated in (both the application directory, as well as the development's machine pb shared files folder), but that doesn't help (pbmaxblobsize is updated to
PBMaxBlobSize='2147483647'
PBMaxTextSize='2147483647'
)
changing my db login to snc worked though. but it will involve tons of testing before I can deploy.
I suppose I will procrastinate instead.
yeap. my connect string for snc is like so
//snc login
//SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
//SQLCA.LogPass = ls_password
//SQLCA.ServerName = ProfileString(gs_CTSINI, "Database", "ServerName", "")
//SQLCA.LogId =sle_userid.text
//SQLCA.AutoCommit = False
//SQLCA.DBParm = "Database='"+ls_database+"',Provider='SQLNCLI10',CommitOnDisconnect='No',OJSyntax='PB'"
---
I'm just worried if there's anything going to go wrong if I just change it like that..much testing will be needed.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.