can a varbinary(MAX) be split into multiple varbinary(8000) for compatibility from 2005 to 2000
-
I found a method for putting the data into a varbinary datatype using a stored procedure. But only in sql server 2005. It works. However, the database needs to be on sql server 2000. When I try to pull the table over to sql server 2000, the column is too large for sql server 2000. So I am trying to split up the sql server 2005 table (after adding a seqNo column) into multiple records into a table defined by varbinary(8000) instead of varbinary(MAX) hoping this new table can be moved over to sql server 2000. Does anyone know how to split up the varbinary(MAX) column into multiple varbinary(8000) records? I know how to do the math to determine how many records to create as I know how many bytes each PDF will have in the varbinary(MAX) column. I just need to do a substring type function on the varbinary(MAX) column giving a start position and length
like 1,8000:8001,16000;16001,16456 thus creating 3 records. So that on the sql server 2000 side I can restring them back together to render the PDF contents via a web app Using a byte array.
thanks,
I was able to get the varbinary(MAX) on 2005 to an image(16) data type on 2000 using DTS and the whole PDF is rendered even though the datatypes are not the same and the size limits are different.
-
Since it's a PDF file, maybe save it in an IMAGE datatype in 2000 instead?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
thanks,
I was able to get the varbinary(MAX) on 2005 to an image(16) data type on 2000 using DTS and the whole PDF is rendered even though the datatypes are not the same and the size limits are different.
-
The image data type is a BLOB type. It stores a 16 byte pointer in the data record that points to the first data page that holds the image data, which can be a large as 2 GB; just like the varbinary(max), which is where you will want to put the data in the IMAGE columns when you move the database to SQL Server 2005 as text, ntext, and image are being depreciated and will be removed from SQL Server sometime in the future.
-
You're welcome... but the IMAGE datatype is 2 GB... the (16) is just a pointer... the following is from BOL 2000...
Image
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Lynn beat me to it...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Lynn Pettis (3/18/2008)
The image data type is a BLOB type. It stores a 16 byte pointer in the data record that points to the first data page that holds the image data, which can be a large as 2 GB; just like the varbinary(max), which is where you will want to put the data in the IMAGE columns when you move the database to SQL Server 2005 as text, ntext, and image are being depreciated and will be removed from SQL Server sometime in the future.Op already has it in VARBINARY(MAX) in 2k5... had to move it back to a 2k instance.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
True, but is the SQL Server 2005 database still around or was it temporary, for development? If it is sourcing the data for the SQL 2000 database then all is good when the SQL 2000 database does go away.
That part wasn't really mentioned, and I would hope that if the data was migrated back from SQL 2000, that it was put back in the varbinary columns.
:w00t:
-
Yep... I gotta agree with that.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply