block a parameter

I have parametro call client, who is a list of clients, taken from the data
base to traves of a query, access the report through the URL, by means of
Internet to explorer, in some cases desire of passing parameter clients
already selected and who the user who is seeing the report cannot modify it,
As blockade the parameter?
--
ING. JOSE DAVID GALVIZ MUÑOZ
MCAD
DCE Tercera EstrellaHi Jose,
You can, either when designing the report or through Report Manager set the
parameter to No prompt. When you do this, the parameter will not be
displayed to the user, so the user will not be able to change the value.
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"José David Galviz M" <joda_26@.hotmail.com> wrote in message
news:Okp1lnQcEHA.644@.tk2msftngp13.phx.gbl...
>I have parametro call client, who is a list of clients, taken from the data
> base to traves of a query, access the report through the URL, by means of
> Internet to explorer, in some cases desire of passing parameter clients
> already selected and who the user who is seeing the report cannot modify
> it,
> As blockade the parameter?
> --
> ING. JOSE DAVID GALVIZ MUÑOZ
> MCAD
> DCE Tercera Estrella
>|||Thanks
--
ING. JOSE DAVID GALVIZ MUÑOZ
MCAD
DCE Tercera Estrella
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> escribió en el
mensaje news:O%23bJnl1cEHA.1000@.TK2MSFTNGP12.phx.gbl...
> Hi Jose,
> You can, either when designing the report or through Report Manager set
the
> parameter to No prompt. When you do this, the parameter will not be
> displayed to the user, so the user will not be able to change the value.
> -Lukasz
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "José David Galviz M" <joda_26@.hotmail.com> wrote in message
> news:Okp1lnQcEHA.644@.tk2msftngp13.phx.gbl...
> >I have parametro call client, who is a list of clients, taken from the
data
> > base to traves of a query, access the report through the URL, by means
of
> > Internet to explorer, in some cases desire of passing parameter clients
> > already selected and who the user who is seeing the report cannot modify
> > it,
> > As blockade the parameter?
> >
> > --
> > ING. JOSE DAVID GALVIZ MUÑOZ
> > MCAD
> > DCE Tercera Estrella
> >
> >
>

blobs?

I have searched high and low for this answer, but still to no avail...

Does anyone have a reliable way of storing serialized data into a table which does not use direct SQL on the client side? I am using Stored Procedures and simply need to be able to read out of the table the object written in.

Inserting appears fine, but reading only seems to come back with 16 bytes of data and I just can't figure out why. Whats the recommended way of doing this?

Matt.You're not reading the byte array properly. See if this article helps:

http://www.dotnetbips.com/displayarticle.aspx?id=60|||just thought I would let everyone know that I solved my serialization problem...

...I was creating a SqlParameter object with a SqlDbType.Image and giving it a size of 16, which meant every time I performed a DB op with that entry I got no more than 16 bytes of data back. very annoying.

anyway, Iv'e removed that from the new SqlParameter() and it works.

I had even read in various places that it's not possible to do serialization to the db using things like SQL, but I may have been just very paranoid by then...

cheers folks,
m.

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
David Portas|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).

> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
David Portas
|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).

> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
--
David Portas|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

BLOBS vs. regular files

Over the weekend I was told that my SQL Server was going to start doubling as
a file server. I guess now the other option is to store files as BLOBS. I
have no experience with either scenario. Can anyone give me the procs/ cons
of doing it either way?
--
TIA,
ChrisRChris,
If they meant a file server for Word documents, executables, and so forth,
then the SQL Server will not be any help to you. You will have a server
running the SQL Server process and also hosting file services. This will
affect how you configure resources on the server since it will no longer be
dedicated.
If you are going to be getting only files associated with a database, then
there are plenty of discussions and opinions.
Try Googling 'blob file sql versus' and read the several hundred comments.
:-)
Here is a link to the thinking behind what the TerraServer guys did.
www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx
RLF
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR|||The pro for BLOBs is very simple, the "files" are part of the database, are
kept in sync transactionally, participate in the backups, and can be
processed by SQL. In other words, they have all the traditional benefits of
a database system.
The cons are more extensive. They don't appear in the filesystem namespace.
Before they can be processed by a regular program they have to be copied
into a temporary file, they aren't structured as efficiently as files for
bulk copying (and can't be directly copied by kernel mode services). In
other words, performance is generally poor by comparison.
Now as a database guy I'd be inclined to store just about everything in the
database, get the management/integrity benefits, and give up some
performance. But realistically
usage would dictate which model I would use. The more the object will need
to be processed by an external file-based application the more likely I'd be
to store it in the file system. The more important it was to keep it
tightly bound to the data in the database system the more likely I'd be to
store it in a blob. For example, say you want to store an employee's photo
with their personnel data. I'd store that in a Blob in the database.
But if I were creating a library of photographs that were going to be
primarily accessed by Photoshop, and the purpose of the database was to make
searching etc easier, then I'd probably keep the photographs in the
filesystem with pointers from the database. Of course, then I'd have to
create more complex backup/resore procedures for my shop.
--
Hal Berenson, President
PredictableIT, LLC
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR

BLOBS vs. regular files

Over the weekend I was told that my SQL Server was going to start doubling as
a file server. I guess now the other option is to store files as BLOBS. I
have no experience with either scenario. Can anyone give me the procs/ cons
of doing it either way?
TIA,
ChrisR
Chris,
If they meant a file server for Word documents, executables, and so forth,
then the SQL Server will not be any help to you. You will have a server
running the SQL Server process and also hosting file services. This will
affect how you configure resources on the server since it will no longer be
dedicated.
If you are going to be getting only files associated with a database, then
there are plenty of discussions and opinions.
Try Googling 'blob file sql versus' and read the several hundred comments.
:-)
Here is a link to the thinking behind what the TerraServer guys did.
http://www.microsoft.com/technet/pro...rt3/c1161.mspx
RLF
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR
|||The pro for BLOBs is very simple, the "files" are part of the database, are
kept in sync transactionally, participate in the backups, and can be
processed by SQL. In other words, they have all the traditional benefits of
a database system.
The cons are more extensive. They don't appear in the filesystem namespace.
Before they can be processed by a regular program they have to be copied
into a temporary file, they aren't structured as efficiently as files for
bulk copying (and can't be directly copied by kernel mode services). In
other words, performance is generally poor by comparison.
Now as a database guy I'd be inclined to store just about everything in the
database, get the management/integrity benefits, and give up some
performance. But realistically
usage would dictate which model I would use. The more the object will need
to be processed by an external file-based application the more likely I'd be
to store it in the file system. The more important it was to keep it
tightly bound to the data in the database system the more likely I'd be to
store it in a blob. For example, say you want to store an employee's photo
with their personnel data. I'd store that in a Blob in the database.
But if I were creating a library of photographs that were going to be
primarily accessed by Photoshop, and the purpose of the database was to make
searching etc easier, then I'd probably keep the photographs in the
filesystem with pointers from the database. Of course, then I'd have to
create more complex backup/resore procedures for my shop.
Hal Berenson, President
PredictableIT, LLC
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR

BLOBS vs. regular files

Over the weekend I was told that my SQL Server was going to start doubling a
s
a file server. I guess now the other option is to store files as BLOBS. I
have no experience with either scenario. Can anyone give me the procs/ cons
of doing it either way?
--
TIA,
ChrisRChris,
If they meant a file server for Word documents, executables, and so forth,
then the SQL Server will not be any help to you. You will have a server
running the SQL Server process and also hosting file services. This will
affect how you configure resources on the server since it will no longer be
dedicated.
If you are going to be getting only files associated with a database, then
there are plenty of discussions and opinions.
Try Googling 'blob file sql versus' and read the several hundred comments.
:-)
Here is a link to the thinking behind what the TerraServer guys did.
www.microsoft.com/technet/prodtechn...art3/c1161.mspx
RLF
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR|||The pro for BLOBs is very simple, the "files" are part of the database, are
kept in sync transactionally, participate in the backups, and can be
processed by SQL. In other words, they have all the traditional benefits of
a database system.
The cons are more extensive. They don't appear in the filesystem namespace.
Before they can be processed by a regular program they have to be copied
into a temporary file, they aren't structured as efficiently as files for
bulk copying (and can't be directly copied by kernel mode services). In
other words, performance is generally poor by comparison.
Now as a database guy I'd be inclined to store just about everything in the
database, get the management/integrity benefits, and give up some
performance. But realistically
usage would dictate which model I would use. The more the object will need
to be processed by an external file-based application the more likely I'd be
to store it in the file system. The more important it was to keep it
tightly bound to the data in the database system the more likely I'd be to
store it in a blob. For example, say you want to store an employee's photo
with their personnel data. I'd store that in a Blob in the database.
But if I were creating a library of photographs that were going to be
primarily accessed by Photoshop, and the purpose of the database was to make
searching etc easier, then I'd probably keep the photographs in the
filesystem with pointers from the database. Of course, then I'd have to
create more complex backup/resore procedures for my shop.
--
Hal Berenson, President
PredictableIT, LLC
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR

Blobs not displayed when printed programatically

Hey folks,

I have a report that I have created. It contains some graphical BLOB fields that are in a database. These blobs were simply dragged and dropped onto the report.

When I preview the report in Crystal Reports the BLOB fields are displayed. When I print the report programatically in VB .net the BLOBs are blank...

Any help would be much apreciated.
ThanksWhat type of image did you use?
Goto support section of this site and see if you find answer
www.BusinessObjects.com

Blobs in SQL Server

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...Connie (csawyer@.rwbaird.com) writes:

Quote:

Originally Posted by

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...

I have a very quick sketch for this on
http://www.sommarskog.se/blobload.txt.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 15, 4:26 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Connie (csaw...@.rwbaird.com) writes:

Quote:

Originally Posted by

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...

>
I have a very quick sketch for this onhttp://www.sommarskog.se/blobload.txt.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Here is some good information that I found in my search to complete
the above task, I am posting as this may help others working with
blobs:
In this article I want to show, how you can copy a single text or
image value into or out of SQL Server with textcopy.exe utility. You
can find this utility in the directory containing the standard SQL
Server EXE files (C:\Mssql\Binn by default for SQL Server 6.5 and C:
\Mssql7\Binn by default for SQL Server 7.0).

The Textcopy utility is not described in SQL Server Books Online, but
you can get its description by typing textcopy /? from the command
prompt. This is the description:

Copies a single text or image value into or out of SQL Server. The
value
is a specified text or image 'column' of a single row (specified by
the
"where clause") of the specified 'table'.

If the direction is IN (/I) then the data from the specified 'file' is
copied into SQL Server, replacing the existing text or image value. If
the
direction is OUT (/O) then the text or image value is copied from
SQL Server into the specified 'file', replacing any existing file.

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

/S sqlserver The SQL Server to connect to. If 'sqlserver' is
not
specified, the local SQL Server is used.
/U login The login to connect with. If 'login' is not
specified,
a trusted connection will be used.
/P password The password for 'login'. If 'password' is not
specified, a NULL password will be used.
/D database The database that contains the table with the
text or
image data. If 'database' is not specified, the
default
database of 'login' is used.
/T table The table that contains the text or image value.
/C column The text or image column of 'table'.
/W "where clause" A complete where clause (including the WHERE
keyword)
that specifies a single row of 'table'.
/F file The file name.
/I Copy text or image value into SQL Server from
'file'.
/O Copy text or image value out of SQL Server into
'file'.
/K chunksize Size of the data transfer buffer in bytes.
Minimum
value is 1024 bytes, default value is 4096 bytes.
/Z Display debug information while running.
/? Display this usage information and exit.

You will be prompted for any required options you did not specify.

You can use the following stored procedure to simplify the using of
textcopy utility:

CREATE PROCEDURE sp_textcopy (
@.srvname varchar (30),
@.login varchar (30),
@.password varchar (30),
@.dbname varchar (30),
@.tbname varchar (30),
@.colname varchar (30),
@.filename varchar (30),
@.whereclause varchar (40),
@.direction char(1))
AS
DECLARE @.exec_str varchar (255)
SELECT @.exec_str =
'textcopy /S ' + @.srvname +
' /U ' + @.login +
' /P ' + @.password +
' /D ' + @.dbname +
' /T ' + @.tbname +
' /C ' + @.colname +
' /W "' + @.whereclause +
'" /F ' + @.filename +
' /' + @.direction
EXEC master..xp_cmdshell @.exec_str

This is the example to copy image into SQL Server database pubs, table
pub_info, column name logo from picture.bmp file where pub_id='0736':

sp_textcopy @.srvname = 'ServerName',
@.login = 'Login',
@.password = 'Password',
@.dbname = 'pubs',
@.tbname = 'pub_info',
@.colname = 'logo',
@.filename = 'c:\picture.bmp',
@.whereclause = " WHERE pub_id='0736' ",
@.direction = 'I'

Blobs in SQL

Can anyone tell me what the pros/cons are of using blobs in sql?
We are looking at scanning certain paperwork and storing it with the
appropriate records in our database. The two options are to store the images
as blobs or to store them as file system objects with a referral pointer in
the db.
I have heard a number of different people say that storing blobs is
inherently evil but they haven't explained why that is.
Any help will be appreciated.
This is one of those topics that has been debated countless times, and there
is no universal truth.
Storing in the database
-transactional integrity
-maintenance simplicity
Storing in the filesystem
-performance
-efficiency
-accessibility
"Jawallaby" wrote:

> Can anyone tell me what the pros/cons are of using blobs in sql?
> We are looking at scanning certain paperwork and storing it with the
> appropriate records in our database. The two options are to store the images
> as blobs or to store them as file system objects with a referral pointer in
> the db.
> I have heard a number of different people say that storing blobs is
> inherently evil but they haven't explained why that is.
> Any help will be appreciated.
>
|||Thanks Dennis.
When you list performance as a pro of using the filesystem what are the
factors that increase performance? What is the performance hit when storing
in the db?
Most if not all of these files will be less than 1mb. There is a potential
for roughly 20 different files to be stored for each person/record in the db
and there are roughly 46000 records active.
I am trying to decide if this is worth worrying about or not and just don't
know enough about blobs in sql.
"Dennis Forbes" wrote:
[vbcol=seagreen]
> This is one of those topics that has been debated countless times, and there
> is no universal truth.
> Storing in the database
> -transactional integrity
> -maintenance simplicity
> Storing in the filesystem
> -performance
> -efficiency
> -accessibility
> "Jawallaby" wrote:
|||Dennis,
We've been scanning and storing document images as 'blobs' in our Informix
db
basically for years. We opted for the transactional integrity &
maintenance simplicity
over the performance and efficiency. Most of our scanned .pdf documents
are
fairly small and average around 100k.
We're not a highly transactional environment and we may scan (insert or
update)
from 50 to 100 documents a day.
Using this method we totally eliminate any file naming concerns, pathing,
directory
file limit issues, etc. (since I've never tried to maintain a pointer /
file based I can't
really speak to the up and down side of the method)
We're currently in the process of migrating to Sql Server and I've verified
that the
Sql Server Image column works in a very similar manner to the Informix Byte
(blob)
columns. The up side of going to Sql Server is that we can now manage our
image table insert, updates & deletes using stored procedures. We don't
have that
ability with Informix using oledb connectivity.
You can see the system in action at the following urls by clicking the blue
permit
or certificate numbers at
http://apps.wrd.state.or.us/apps/wr/...ID&snpid=64734
The downside can be database backups ... we currently have around 80G of
scanned imagesand a full db backup takes about 3 to 4 hrs to DLT tape.
Hope this helps.
Barry
in Oregon
"Jawallaby" <Jawallaby@.discussions.microsoft.com> wrote in message
news:24797C1E-595B-4C48-A4C7-AE264E8423BA@.microsoft.com...[vbcol=seagreen]
> Thanks Dennis.
> When you list performance as a pro of using the filesystem what are the
> factors that increase performance? What is the performance hit when
> storing
> in the db?
> Most if not all of these files will be less than 1mb. There is a potential
> for roughly 20 different files to be stored for each person/record in the
> db
> and there are roughly 46000 records active.
> I am trying to decide if this is worth worrying about or not and just
> don't
> know enough about blobs in sql.
>
> "Dennis Forbes" wrote:

Blobs in SQL

Can anyone tell me what the pros/cons are of using blobs in sql?
We are looking at scanning certain paperwork and storing it with the
appropriate records in our database. The two options are to store the images
as blobs or to store them as file system objects with a referral pointer in
the db.
I have heard a number of different people say that storing blobs is
inherently evil but they haven't explained why that is.
Any help will be appreciated.This is one of those topics that has been debated countless times, and there
is no universal truth.
Storing in the database
-transactional integrity
-maintenance simplicity
Storing in the filesystem
-performance
-efficiency
-accessibility
"Jawallaby" wrote:

> Can anyone tell me what the pros/cons are of using blobs in sql?
> We are looking at scanning certain paperwork and storing it with the
> appropriate records in our database. The two options are to store the imag
es
> as blobs or to store them as file system objects with a referral pointer i
n
> the db.
> I have heard a number of different people say that storing blobs is
> inherently evil but they haven't explained why that is.
> Any help will be appreciated.
>

Blobs in SQL

Can anyone tell me what the pros/cons are of using blobs in sql?
We are looking at scanning certain paperwork and storing it with the
appropriate records in our database. The two options are to store the images
as blobs or to store them as file system objects with a referral pointer in
the db.
I have heard a number of different people say that storing blobs is
inherently evil but they haven't explained why that is.
Any help will be appreciated.This is one of those topics that has been debated countless times, and there
is no universal truth.
Storing in the database
-transactional integrity
-maintenance simplicity
Storing in the filesystem
-performance
-efficiency
-accessibility
"Jawallaby" wrote:
> Can anyone tell me what the pros/cons are of using blobs in sql?
> We are looking at scanning certain paperwork and storing it with the
> appropriate records in our database. The two options are to store the images
> as blobs or to store them as file system objects with a referral pointer in
> the db.
> I have heard a number of different people say that storing blobs is
> inherently evil but they haven't explained why that is.
> Any help will be appreciated.
>|||Thanks Dennis.
When you list performance as a pro of using the filesystem what are the
factors that increase performance? What is the performance hit when storing
in the db?
Most if not all of these files will be less than 1mb. There is a potential
for roughly 20 different files to be stored for each person/record in the db
and there are roughly 46000 records active.
I am trying to decide if this is worth worrying about or not and just don't
know enough about blobs in sql.
"Dennis Forbes" wrote:
> This is one of those topics that has been debated countless times, and there
> is no universal truth.
> Storing in the database
> -transactional integrity
> -maintenance simplicity
> Storing in the filesystem
> -performance
> -efficiency
> -accessibility
> "Jawallaby" wrote:
> > Can anyone tell me what the pros/cons are of using blobs in sql?
> >
> > We are looking at scanning certain paperwork and storing it with the
> > appropriate records in our database. The two options are to store the images
> > as blobs or to store them as file system objects with a referral pointer in
> > the db.
> >
> > I have heard a number of different people say that storing blobs is
> > inherently evil but they haven't explained why that is.
> >
> > Any help will be appreciated.
> >
> >|||Dennis,
We've been scanning and storing document images as 'blobs' in our Informix
db
basically for years. We opted for the transactional integrity &
maintenance simplicity
over the performance and efficiency. Most of our scanned .pdf documents
are
fairly small and average around 100k.
We're not a highly transactional environment and we may scan (insert or
update)
from 50 to 100 documents a day.
Using this method we totally eliminate any file naming concerns, pathing,
directory
file limit issues, etc. (since I've never tried to maintain a pointer /
file based I can't
really speak to the up and down side of the method)
We're currently in the process of migrating to Sql Server and I've verified
that the
Sql Server Image column works in a very similar manner to the Informix Byte
(blob)
columns. The up side of going to Sql Server is that we can now manage our
image table insert, updates & deletes using stored procedures. We don't
have that
ability with Informix using oledb connectivity.
You can see the system in action at the following urls by clicking the blue
permit
or certificate numbers at
http://apps.wrd.state.or.us/apps/wr/wrinfo/wrinfo.php?search_type=SNPID&snpid=64734
The downside can be database backups ... we currently have around 80G of
scanned imagesand a full db backup takes about 3 to 4 hrs to DLT tape.
Hope this helps.
Barry
in Oregon
"Jawallaby" <Jawallaby@.discussions.microsoft.com> wrote in message
news:24797C1E-595B-4C48-A4C7-AE264E8423BA@.microsoft.com...
> Thanks Dennis.
> When you list performance as a pro of using the filesystem what are the
> factors that increase performance? What is the performance hit when
> storing
> in the db?
> Most if not all of these files will be less than 1mb. There is a potential
> for roughly 20 different files to be stored for each person/record in the
> db
> and there are roughly 46000 records active.
> I am trying to decide if this is worth worrying about or not and just
> don't
> know enough about blobs in sql.
>
> "Dennis Forbes" wrote:
>> This is one of those topics that has been debated countless times, and
>> there
>> is no universal truth.
>> Storing in the database
>> -transactional integrity
>> -maintenance simplicity
>> Storing in the filesystem
>> -performance
>> -efficiency
>> -accessibility
>> "Jawallaby" wrote:
>> > Can anyone tell me what the pros/cons are of using blobs in sql?
>> >
>> > We are looking at scanning certain paperwork and storing it with the
>> > appropriate records in our database. The two options are to store the
>> > images
>> > as blobs or to store them as file system objects with a referral
>> > pointer in
>> > the db.
>> >
>> > I have heard a number of different people say that storing blobs is
>> > inherently evil but they haven't explained why that is.
>> >
>> > Any help will be appreciated.
>> >
>> >

BLOBS in a sql server database - yes or no?

Hi,
we have a sql server 2000 database running that manages amout 10 million
invoices (growing at 10000/day). in addition, we have a file server that
holds all invoices as pdf files. the problem is that it is extremely
difficult to backup these files or to do other file-realted task since there
are so many of them.
thats why we started thinking abut moving all these files directly into the
database as binary objects (BLOBs) so that invoice meta data and content is
at the same place. the average file size is about 40k. im not a big fan of
blobs but in this case it seems to be a good thing since backup and file
management wouldnt be a problem anymore (among other advantages).
my question is if you already experienced having large amounts of blobs in
the database and if you think its a good idea or not. i don't want our
overall query performance to be affected in any way (i know that sql server
stores pointer to the blob data in the tables so it shouldnt have a great
impact on performance, should it?). i'm interested in your experiences and
recommendations.
btw.: we would upgrade the hardware to something like this:
2 x Xeon 64, 3.6 GHz
8 GB RAM
10-14 disk drives for a total of 1-2 TB
i think it should be enough.
thanks in advance,
Benjamin Janecke
The knock on blobs in the database is really that they are inefficient for
the application that is going to process them. You can't directly stream
from the database out over a network file/web protocol as you can from the
filesystem, and most applications read from the file system so that you have
to first copy the blob into a file in order to process it. The benefit of
storing what are essentially files in blobs is, as you describe below, the
management benefits. From syncronized backup to avoiding broken links. We,
for example, put the T&Cs for our promotion codes in the database. If you
want a description of the promotion then you click on a hyperlink and we pop
up a page populated from the database. This isn't a super high volume
occurence so we aren't worried about the extra overhead involved. What we
gain is we don't have to worry about a link to a file describing the promo
code getting broken and the user getting either no description or the wrong
description.
As you already know the blob (unless it is very small) isn't stored in the
row and thus there is little or no performance impact on normal queries from
having the blob in the database.
The preeminent example of storing blobs in the database is the Terraserver
(http://terraserver.microsoft.com) which stores multiple Terabytes of
imagery in blobs and serves it out over the internet. This has been running
successfully since 1998, at times handling a few million hits per day. I
spent a few sleepless nights helping deal with problems the week it went
live (and took a lot more hits then we'd expected), and none of those were
related to the use of blobs.
Personally, my criteria for storing your invoices in the database would
involve two decisions. One is how long you wish to retain them (short, then
don't bother storing them in the database) and the other is how frequently
they are accessed. Since keeping invoices in this manner would typically be
for archival purposes and they would almost never be opened, you really
don't care about the performance hit versus keeping them in the file system
when one needs to be displayed. So the management benefits would outweigh
the performance negatives.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
> Hi,
> we have a sql server 2000 database running that manages amout 10 million
> invoices (growing at 10000/day). in addition, we have a file server that
> holds all invoices as pdf files. the problem is that it is extremely
> difficult to backup these files or to do other file-realted task since
> there
> are so many of them.
> thats why we started thinking abut moving all these files directly into
> the
> database as binary objects (BLOBs) so that invoice meta data and content
> is
> at the same place. the average file size is about 40k. im not a big fan of
> blobs but in this case it seems to be a good thing since backup and file
> management wouldnt be a problem anymore (among other advantages).
> my question is if you already experienced having large amounts of blobs in
> the database and if you think its a good idea or not. i don't want our
> overall query performance to be affected in any way (i know that sql
> server
> stores pointer to the blob data in the tables so it shouldnt have a great
> impact on performance, should it?). i'm interested in your experiences and
> recommendations.
> btw.: we would upgrade the hardware to something like this:
> 2 x Xeon 64, 3.6 GHz
> 8 GB RAM
> 10-14 disk drives for a total of 1-2 TB
> i think it should be enough.
> thanks in advance,
> Benjamin Janecke
|||thanks for your very informative post.
we store the invoices for both archive purposes and daily access. the
database is part of an application that enables our customers to view their
invoices online. the invoices can also be accessed by our customer care
employees. in addition, we provide functionality to send invoices via e-mail
etc. in total, i think we have more than 1000 requests a day for pdf files,
but much less than 10000, so its not a big deal.
the good news is that the application doesn't depend on accessing the
invoices as files cause we only read them to deliver them as binary data to
the webbrowser (using special content type). therefore it wouldn't be a
problem to get binary data back from the database instead of opening physical
files. for e-mail-sending it is also possible to include binary data as
attachment instead of providing attachment files. thus, i don't think there
will be a big negative performance impact on the application except that the
data has to go through the network.
So, we will propably give it a try...
best regards,
Benjamin Janecke
"Hal Berenson" wrote:

> The knock on blobs in the database is really that they are inefficient for
> the application that is going to process them. You can't directly stream
> from the database out over a network file/web protocol as you can from the
> filesystem, and most applications read from the file system so that you have
> to first copy the blob into a file in order to process it. The benefit of
> storing what are essentially files in blobs is, as you describe below, the
> management benefits. From syncronized backup to avoiding broken links. We,
> for example, put the T&Cs for our promotion codes in the database. If you
> want a description of the promotion then you click on a hyperlink and we pop
> up a page populated from the database. This isn't a super high volume
> occurence so we aren't worried about the extra overhead involved. What we
> gain is we don't have to worry about a link to a file describing the promo
> code getting broken and the user getting either no description or the wrong
> description.
> As you already know the blob (unless it is very small) isn't stored in the
> row and thus there is little or no performance impact on normal queries from
> having the blob in the database.
> The preeminent example of storing blobs in the database is the Terraserver
> (http://terraserver.microsoft.com) which stores multiple Terabytes of
> imagery in blobs and serves it out over the internet. This has been running
> successfully since 1998, at times handling a few million hits per day. I
> spent a few sleepless nights helping deal with problems the week it went
> live (and took a lot more hits then we'd expected), and none of those were
> related to the use of blobs.
> Personally, my criteria for storing your invoices in the database would
> involve two decisions. One is how long you wish to retain them (short, then
> don't bother storing them in the database) and the other is how frequently
> they are accessed. Since keeping invoices in this manner would typically be
> for archival purposes and they would almost never be opened, you really
> don't care about the performance hit versus keeping them in the file system
> when one needs to be displayed. So the management benefits would outweigh
> the performance negatives.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
> message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
>
>

BLOBS in a sql server database - yes or no?

Hi,
we have a sql server 2000 database running that manages amout 10 million
invoices (growing at 10000/day). in addition, we have a file server that
holds all invoices as pdf files. the problem is that it is extremely
difficult to backup these files or to do other file-realted task since there
are so many of them.
thats why we started thinking abut moving all these files directly into the
database as binary objects (BLOBs) so that invoice meta data and content is
at the same place. the average file size is about 40k. im not a big fan of
blobs but in this case it seems to be a good thing since backup and file
management wouldnt be a problem anymore (among other advantages).
my question is if you already experienced having large amounts of blobs in
the database and if you think its a good idea or not. i don't want our
overall query performance to be affected in any way (i know that sql server
stores pointer to the blob data in the tables so it shouldnt have a great
impact on performance, should it?). i'm interested in your experiences and
recommendations.
btw.: we would upgrade the hardware to something like this:
2 x Xeon 64, 3.6 GHz
8 GB RAM
10-14 disk drives for a total of 1-2 TB
i think it should be enough.
thanks in advance,
Benjamin JaneckeThe knock on blobs in the database is really that they are inefficient for
the application that is going to process them. You can't directly stream
from the database out over a network file/web protocol as you can from the
filesystem, and most applications read from the file system so that you have
to first copy the blob into a file in order to process it. The benefit of
storing what are essentially files in blobs is, as you describe below, the
management benefits. From syncronized backup to avoiding broken links. We,
for example, put the T&Cs for our promotion codes in the database. If you
want a description of the promotion then you click on a hyperlink and we pop
up a page populated from the database. This isn't a super high volume
occurence so we aren't worried about the extra overhead involved. What we
gain is we don't have to worry about a link to a file describing the promo
code getting broken and the user getting either no description or the wrong
description.
As you already know the blob (unless it is very small) isn't stored in the
row and thus there is little or no performance impact on normal queries from
having the blob in the database.
The preeminent example of storing blobs in the database is the Terraserver
(http://terraserver.microsoft.com) which stores multiple Terabytes of
imagery in blobs and serves it out over the internet. This has been running
successfully since 1998, at times handling a few million hits per day. I
spent a few sleepless nights helping deal with problems the week it went
live (and took a lot more hits then we'd expected), and none of those were
related to the use of blobs.
Personally, my criteria for storing your invoices in the database would
involve two decisions. One is how long you wish to retain them (short, then
don't bother storing them in the database) and the other is how frequently
they are accessed. Since keeping invoices in this manner would typically be
for archival purposes and they would almost never be opened, you really
don't care about the performance hit versus keeping them in the file system
when one needs to be displayed. So the management benefits would outweigh
the performance negatives.
--
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
> Hi,
> we have a sql server 2000 database running that manages amout 10 million
> invoices (growing at 10000/day). in addition, we have a file server that
> holds all invoices as pdf files. the problem is that it is extremely
> difficult to backup these files or to do other file-realted task since
> there
> are so many of them.
> thats why we started thinking abut moving all these files directly into
> the
> database as binary objects (BLOBs) so that invoice meta data and content
> is
> at the same place. the average file size is about 40k. im not a big fan of
> blobs but in this case it seems to be a good thing since backup and file
> management wouldnt be a problem anymore (among other advantages).
> my question is if you already experienced having large amounts of blobs in
> the database and if you think its a good idea or not. i don't want our
> overall query performance to be affected in any way (i know that sql
> server
> stores pointer to the blob data in the tables so it shouldnt have a great
> impact on performance, should it?). i'm interested in your experiences and
> recommendations.
> btw.: we would upgrade the hardware to something like this:
> 2 x Xeon 64, 3.6 GHz
> 8 GB RAM
> 10-14 disk drives for a total of 1-2 TB
> i think it should be enough.
> thanks in advance,
> Benjamin Janecke|||thanks for your very informative post.
we store the invoices for both archive purposes and daily access. the
database is part of an application that enables our customers to view their
invoices online. the invoices can also be accessed by our customer care
employees. in addition, we provide functionality to send invoices via e-mail
etc. in total, i think we have more than 1000 requests a day for pdf files,
but much less than 10000, so its not a big deal.
the good news is that the application doesn't depend on accessing the
invoices as files cause we only read them to deliver them as binary data to
the webbrowser (using special content type). therefore it wouldn't be a
problem to get binary data back from the database instead of opening physical
files. for e-mail-sending it is also possible to include binary data as
attachment instead of providing attachment files. thus, i don't think there
will be a big negative performance impact on the application except that the
data has to go through the network.
So, we will propably give it a try...
best regards,
Benjamin Janecke
"Hal Berenson" wrote:
> The knock on blobs in the database is really that they are inefficient for
> the application that is going to process them. You can't directly stream
> from the database out over a network file/web protocol as you can from the
> filesystem, and most applications read from the file system so that you have
> to first copy the blob into a file in order to process it. The benefit of
> storing what are essentially files in blobs is, as you describe below, the
> management benefits. From syncronized backup to avoiding broken links. We,
> for example, put the T&Cs for our promotion codes in the database. If you
> want a description of the promotion then you click on a hyperlink and we pop
> up a page populated from the database. This isn't a super high volume
> occurence so we aren't worried about the extra overhead involved. What we
> gain is we don't have to worry about a link to a file describing the promo
> code getting broken and the user getting either no description or the wrong
> description.
> As you already know the blob (unless it is very small) isn't stored in the
> row and thus there is little or no performance impact on normal queries from
> having the blob in the database.
> The preeminent example of storing blobs in the database is the Terraserver
> (http://terraserver.microsoft.com) which stores multiple Terabytes of
> imagery in blobs and serves it out over the internet. This has been running
> successfully since 1998, at times handling a few million hits per day. I
> spent a few sleepless nights helping deal with problems the week it went
> live (and took a lot more hits then we'd expected), and none of those were
> related to the use of blobs.
> Personally, my criteria for storing your invoices in the database would
> involve two decisions. One is how long you wish to retain them (short, then
> don't bother storing them in the database) and the other is how frequently
> they are accessed. Since keeping invoices in this manner would typically be
> for archival purposes and they would almost never be opened, you really
> don't care about the performance hit versus keeping them in the file system
> when one needs to be displayed. So the management benefits would outweigh
> the performance negatives.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
> message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
> > Hi,
> >
> > we have a sql server 2000 database running that manages amout 10 million
> > invoices (growing at 10000/day). in addition, we have a file server that
> > holds all invoices as pdf files. the problem is that it is extremely
> > difficult to backup these files or to do other file-realted task since
> > there
> > are so many of them.
> >
> > thats why we started thinking abut moving all these files directly into
> > the
> > database as binary objects (BLOBs) so that invoice meta data and content
> > is
> > at the same place. the average file size is about 40k. im not a big fan of
> > blobs but in this case it seems to be a good thing since backup and file
> > management wouldnt be a problem anymore (among other advantages).
> >
> > my question is if you already experienced having large amounts of blobs in
> > the database and if you think its a good idea or not. i don't want our
> > overall query performance to be affected in any way (i know that sql
> > server
> > stores pointer to the blob data in the tables so it shouldnt have a great
> > impact on performance, should it?). i'm interested in your experiences and
> > recommendations.
> >
> > btw.: we would upgrade the hardware to something like this:
> >
> > 2 x Xeon 64, 3.6 GHz
> > 8 GB RAM
> > 10-14 disk drives for a total of 1-2 TB
> >
> > i think it should be enough.
> >
> > thanks in advance,
> > Benjamin Janecke
>
>

BLOBs and the 8K rows

Does anyone know if BLOBs are restricted by the 8KB row size rule? Or are
they more like images and text and not restricted to 8KB?
ThanksDepend how is setup the table's option "text in row" for this specific table
.
See "sp_tableoption" in BOL for more info.
AMB
"Robert Kinesta" wrote:

> Does anyone know if BLOBs are restricted by the 8KB row size rule? Or are
> they more like images and text and not restricted to 8KB?
> Thanks|||Hi
BLOB is a synonym for Image and Text data types.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Kinesta" <RobertKinesta@.discussions.microsoft.com> wrote in message
news:BFD95D29-B5A7-4DB1-BCB6-E26FAFD8CF7C@.microsoft.com...
> Does anyone know if BLOBs are restricted by the 8KB row size rule? Or are
> they more like images and text and not restricted to 8KB?
> Thanks|||BLOBs (image and text) are not restricted by the 8KB row size. A BLOB could
be as big as 2Gb. It is stored to multiple 8KB pages and managered by a
btree-like structure.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Kinesta" <RobertKinesta@.discussions.microsoft.com> wrote in message
news:BFD95D29-B5A7-4DB1-BCB6-E26FAFD8CF7C@.microsoft.com...
> Does anyone know if BLOBs are restricted by the 8KB row size rule? Or are
> they more like images and text and not restricted to 8KB?
> Thanks

BLOBs and the 8K rows

Does anyone know if BLOBs are restricted by the 8KB row size rule? Or are
they more like images and text and not restricted to 8KB?
Thanks
Depend how is setup the table's option "text in row" for this specific table.
See "sp_tableoption" in BOL for more info.
AMB
"Robert Kinesta" wrote:

> Does anyone know if BLOBs are restricted by the 8KB row size rule? Or are
> they more like images and text and not restricted to 8KB?
> Thanks
|||Hi
BLOB is a synonym for Image and Text data types.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Kinesta" <RobertKinesta@.discussions.microsoft.com> wrote in message
news:BFD95D29-B5A7-4DB1-BCB6-E26FAFD8CF7C@.microsoft.com...
> Does anyone know if BLOBs are restricted by the 8KB row size rule? Or are
> they more like images and text and not restricted to 8KB?
> Thanks
|||BLOBs (image and text) are not restricted by the 8KB row size. A BLOB could
be as big as 2Gb. It is stored to multiple 8KB pages and managered by a
btree-like structure.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Kinesta" <RobertKinesta@.discussions.microsoft.com> wrote in message
news:BFD95D29-B5A7-4DB1-BCB6-E26FAFD8CF7C@.microsoft.com...
> Does anyone know if BLOBs are restricted by the 8KB row size rule? Or are
> they more like images and text and not restricted to 8KB?
> Thanks

BLOBs and the 8K rows

Does anyone know if BLOBs are restricted by the 8KB row size rule? Or are
they more like images and text and not restricted to 8KB?
ThanksDepend how is setup the table's option "text in row" for this specific table.
See "sp_tableoption" in BOL for more info.
AMB
"Robert Kinesta" wrote:
> Does anyone know if BLOBs are restricted by the 8KB row size rule? Or are
> they more like images and text and not restricted to 8KB?
> Thanks|||Hi
BLOB is a synonym for Image and Text data types.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Kinesta" <RobertKinesta@.discussions.microsoft.com> wrote in message
news:BFD95D29-B5A7-4DB1-BCB6-E26FAFD8CF7C@.microsoft.com...
> Does anyone know if BLOBs are restricted by the 8KB row size rule? Or are
> they more like images and text and not restricted to 8KB?
> Thanks|||BLOBs (image and text) are not restricted by the 8KB row size. A BLOB could
be as big as 2Gb. It is stored to multiple 8KB pages and managered by a
btree-like structure.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Kinesta" <RobertKinesta@.discussions.microsoft.com> wrote in message
news:BFD95D29-B5A7-4DB1-BCB6-E26FAFD8CF7C@.microsoft.com...
> Does anyone know if BLOBs are restricted by the 8KB row size rule? Or are
> they more like images and text and not restricted to 8KB?
> Thanks

BLOBs and Stored Procedures

I have been told that using BLOBS and Stored Procedures is a bad thing.
Running the SQL in the page is the only correct way. We are using SQL Serve
r
2000 - soon to go to 2005. Could someone direct me to documentation that
addresses this situation?
--
Tonywho told you this?
BLOBS I can see why people would advise you to avoid them, they aren't
bad things, they just have the capability to be used badly.
Stored procedures are very good things, by "running the SQL in the page
is the only correct way" do you mean passing SQL as a string to your
sqlCommand objects? is this just for when you are using BLOBS or all
SQL you run?|||some of the key benefits of stored procedures are mentioned in this
article:
http://msdn.microsoft.com/library/d...>
_07_31vb.asp|||Will,
Thanks for getting back to me. I have used Stored Procedures for years.
No, my questions is specific to BLOBS and stored procedures. (I have heard
all the negatives about BLOBS - pdfs in databases, but I have a client ...)
My DBAs tell me that 'adding' a layer to the data process - for BLOBs only-
is too high a price to pay (resources) for me to use them.
I can't find definitive proof one way or the other. I would really like to
continue to use my data layer and not use in-page SQL (ADO .Net to SQL Serve
r
with no SP). I can't argue to vehemently because I don't know if a query
plan is even generated for the BLOB handling SP.
With over twelve years of experience with SQL Server, I have never seen a
situation where performance was better without a stored procedure. But SQL
has to handle BLOBs differently, so I was hoping to find something to suppor
t
either side of the argument that I could take to my DBAs.
Thanks for your time.
--
Tony
"Will" wrote:

> some of the key benefits of stored procedures are mentioned in this
> article:
> http://msdn.microsoft.com/library/d...
es_07_31vb.asp
>

blob size limit

"jason" <jason@.discussions.microsoft.com> wrote in message
news:21C19525-825F-4DD3-83E6-B257AB500946@.microsoft.com...
> is there a size at which the items just get too big to be practically
> stored
> in blobs?
Not really, but you have to be increasingly careful with blobs as they get
bigger. For instance in client applciations if you can't afford to easilly
store the whole blob in memory, you need to use streaming access to move it
into and out of the database.
Davidis there a size at which the items just get too big to be practically stored
in blobs?|||"jason" <jason@.discussions.microsoft.com> wrote in message
news:21C19525-825F-4DD3-83E6-B257AB500946@.microsoft.com...
> is there a size at which the items just get too big to be practically
> stored
> in blobs?
Not really, but you have to be increasingly careful with blobs as they get
bigger. For instance in client applciations if you can't afford to easilly
store the whole blob in memory, you need to use streaming access to move it
into and out of the database.
David|||Hi Jason,
In SQL 2000 ntext, text, and image data types are capable of holding
extremely large amounts of data (up to 2 GB) in a single value. In SQL
2005, varchar(max), nvarchar(max), varbinary(max) and nvarbinary(max) also
has 2 GB limitation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Jason,
In SQL 2000 ntext, text, and image data types are capable of holding
extremely large amounts of data (up to 2 GB) in a single value. In SQL
2005, varchar(max), nvarchar(max), varbinary(max) and nvarbinary(max) also
has 2 GB limitation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

blob size limit

is there a size at which the items just get too big to be practically stored
in blobs?"jason" <jason@.discussions.microsoft.com> wrote in message
news:21C19525-825F-4DD3-83E6-B257AB500946@.microsoft.com...
> is there a size at which the items just get too big to be practically
> stored
> in blobs?
Not really, but you have to be increasingly careful with blobs as they get
bigger. For instance in client applciations if you can't afford to easilly
store the whole blob in memory, you need to use streaming access to move it
into and out of the database.
David|||Hi Jason,
In SQL 2000 ntext, text, and image data types are capable of holding
extremely large amounts of data (up to 2 GB) in a single value. In SQL
2005, varchar(max), nvarchar(max), varbinary(max) and nvarbinary(max) also
has 2 GB limitation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

BLOB Removal

I am working on a DOD contract and would like to find out
if there is any software out there that would allow
deletion of a sql blob, like a word document, and then
scrubbing of the disk where the blob had resided.
Currently we are forced to delete the doc, backup the
database, scrub the disk and over write with hex mutliple
times and then restore the database.
Please let me know if there is software available for
this.
Thankyou
I'm not aware of any tools that would alllow you to scrub the disk with the
DB files in place.
There are a few tools that allow you to encrypt data in SQL Server. But I'm
not aware of anything that would scrube the disks.
Brian
"Thom" <anonymous@.discussions.microsoft.com> wrote in message
news:822f01c48530$f253d040$a501280a@.phx.gbl...
> I am working on a DOD contract and would like to find out
> if there is any software out there that would allow
> deletion of a sql blob, like a word document, and then
> scrubbing of the disk where the blob had resided.
> Currently we are forced to delete the doc, backup the
> database, scrub the disk and over write with hex mutliple
> times and then restore the database.
> Please let me know if there is software available for
> this.
> Thankyou

BLOB performance SQL 2005 Do's and Don'ts

Hi,
I have a content based site with over a 1000 downloadable word docs
which are currently stored on a server filesystem.
however these are split in muliple folder paths for different clients
and i am trying to find a more efficient way of doing this. i have
been looking into storing the files as blobs in a db rather than
searching on a filename on a file IO etc
in SQL 2000 this was frowned upon as it was so poorly managed but has
this been improved in 2005 or would i be better off using the same
methods i have in place now?
any comments?If you're searching on filenames, why don't you store the filename in the
database?
"km200281" <k.a.mortimer@.gmail.com> wrote in message
news:1147267971.088679.264550@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a content based site with over a 1000 downloadable word docs
> which are currently stored on a server filesystem.
> however these are split in muliple folder paths for different clients
> and i am trying to find a more efficient way of doing this. i have
> been looking into storing the files as blobs in a db rather than
> searching on a filename on a file IO etc
> in SQL 2000 this was frowned upon as it was so poorly managed but has
> this been improved in 2005 or would i be better off using the same
> methods i have in place now?
> any comments?
>|||Hi,
that is what we do now however a large number of updates are done to
the site on a regular basis and this is because we deal with multiple
versions of the same document in different locations its not easy to
manage hence my query regarding the use of BLOBs

BLOB or VARBINARY(MAX) as SSIS source.

Hi all,

Can a SSIS package treat a file from a table ( VARBINARY(MAX) or BLOB) as a source for migration ?

Thanks in advance,

DBAnalyst

Sure. The varbinary(max) SQL 2005 data type maps to the SSIS type DT_IMAGE, which is just a bunch of bytes of which the file consists.

So what you have at that point is a byte stream, or byte array.

To use those bytes as file source, you could write them to a file in one dataflow and read them in a second dataflow.

Another way to use varbinary(max) data as a "file source" would be to use a script or custom source component (its not that painful) to wrap a StreamReader object arround the SQL byte stream. Using this approach, the varbinary(max) never touches down to disk as an intermediate step.

There are certainly other techniques, but those come to mind.|||Thanks a lot Jaegd.

BLOB or PIPING a Select

I posted this a few months ago, but at the time did not find a simple method
..
I would like to have a select statement output into a single result...
IE
select email from a.client
rather then return as:
user1@.user.com
user2@.user.com
user3@.user.com
I would like:
user1@.user.comuser1@.user.comuser1@.user.com
Basically treating the result as a single row rather then one for each...
anythoughts or ideas? Am I on crack?
Jordyhttp://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Jordy Boss" wrote:

> I posted this a few months ago, but at the time did not find a simple meth
od...
> I would like to have a select statement output into a single result...
> IE
> select email from a.client
> rather then return as:
> user1@.user.com
> user2@.user.com
> user3@.user.com
> I would like:
> user1@.user.comuser1@.user.comuser1@.user.com
> Basically treating the result as a single row rather then one for each...
> anythoughts or ideas? Am I on crack?
> Jordy

BLOB Merge Replication

Hi,

I have setup a Merge Replication between a SQL Server 2005 and a SQL Server Mobile using native C++ code in Windows CE 5. Replication of normal data types (nvarchar, ints etc) works great, but when I try to replicate a blob about 1.5 MB the performance is awful. The download phase seems to be quick while the applytime (printed in debugger) when the database on my CE device is updated is about 3 minutes for 1.5MB binary data which is unacceptable since the database gets locked up during the applytime. The BLOB column is defined as image data type which shall hold up to 1GB data. I have tried to disable compression during replication, but it doesnt matter. Does anyone have some experience of using merge replication with blobs in SQL Server Mobile?

My Idea was to use merge replication as a way to transfer CAB-files for installation on the Windows CE 5 device by storing them as BLOB:s, but the bad performance may make it impossible.

Ideas? Anyone?

Best regards

Johan Johansson

I'm appending the debug output from the merge replication of the table containing the 1.5 MB BLOB.

SQLCECA30: Client Agent Log Start --
SQLCECA30: 08/23/2006-16:37:41 CSSCEMerge::SingleRun id=0 hr=0 bread=8027251 bwritten=273 c_urows=0 c_drows=0 s_urows=5 s_drows=0 md_n=0 md_c=0 md_r=0 app=mpsim.exe
SQLCECA30: 08/23/2006-16:37:41 CSSCEMerge::Run totalsynctime=222 uploadtime=371 applytime=217185 id=0 publisher='se-cwo-ddb-1' publication='Software' initialsync=1 bread=8027251 bwritten=273 c_urows=0 c_drows=0 s_urows=5 s_drows=0 loops=1 app=mpsim.exe

|||

About the only thing you can do is to factor out the BLOB itself into a related table. For example, let's say right now you have a table called "Orders" which has 10 columns, one of which is a customer signature (image column in SQL Mobile). Every time any column in the orders table is updated, the whole row is replicated, including the image. It would be more efficient to have an Orders table and a separate OrderSignatures table, related by a foreign key.

Darren

|||

Yes, thank you for the suggestion.

I must say that I'm dissapointed in the performance. I mean 1.5 MB binary data is not alot in these days and it is almost impossible to handle for SQL Server Mobile in a merge replication. Can I expect the same bad performance when I replicate alot of rows (lets say 3000) with more ordinary data (say a few columns with ints and varchar(50)) or is this performance problem only related to dealing with BLOBs?

Johan

|||

Johan,

I was thinking about your post some more and it occurs to me that you are trying to solve the problem of automatically downloading CAB files

from the server to the CE5 device. I also frequently have this requirement when a mobile app must be automatically updating. There are some

great samples (they are not native code, but you can get the idea of how to do this) of auto-updating by downloading CABs to the device at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetcomp/html/AutoUpdater.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetcomp/html/AUTD_Functionality_NETCF_WebServices.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetcomp/html/DeploymentPatterns.asp

Regards,

Darren

|||

Thank you Darren,

I will have a look at the documents before I decide how I'm going to implement this.

Best regards

Johan

blob info

I put an image as a blob into database.
When I return it and put it into picturebox can I somehow get it's
information: PictureSize, PictureType(jpg,gif...) ?
HrckoSQL Server can return the image length (DATALENGTH function) but has no
other knowledge of the binary contents. If you need that info, create a
separate column for that data.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:e1nv1n$d2d$1@.ss405.t-com.hr...
>I put an image as a blob into database.
> When I return it and put it into picturebox can I somehow get it's
> information: PictureSize, PictureType(jpg,gif...) ?
> Hrcko
>

blob info

I put an image as a blob into database.
When I return it and put it into picturebox can I somehow get it's
information: PictureSize, PictureType(jpg,gif...) ?
HrckoSQL Server can return the image length (DATALENGTH function) but has no
other knowledge of the binary contents. If you need that info, create a
separate column for that data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:e1nv1n$d2d$1@.ss405.t-com.hr...
>I put an image as a blob into database.
> When I return it and put it into picturebox can I somehow get it's
> information: PictureSize, PictureType(jpg,gif...) ?
> Hrcko
>

BLOB index search

Hi,
I am wondering if I can user SQL server to store Word documents in the BLOB
field and then use Index Server to crawl through those documents (maybe on a
timed basis) to generate a catalog which can be used for web based queries
against. If this can be done then any caveats would also be welcome.
Thanks,
Yes. You can do that via SQL Full Text Search:
http://www.microsoft.com/sql/evaluat...s/fulltext.asp
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <ancientsiam@.yahoo.com> wrote in message
news:ebl2DFEMFHA.3448@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am wondering if I can user SQL server to store Word documents in the
BLOB
> field and then use Index Server to crawl through those documents (maybe on
a
> timed basis) to generate a catalog which can be used for web based queries
> against. If this can be done then any caveats would also be welcome.
> Thanks,
>

BLOB index search

Hi,
I am wondering if I can user SQL server to store Word documents in the BLOB
field and then use Index Server to crawl through those documents (maybe on a
timed basis) to generate a catalog which can be used for web based queries
against. If this can be done then any caveats would also be welcome.
Thanks,Yes. You can do that via SQL Full Text Search:
http://www.microsoft.com/sql/evaluation/features/fulltext.asp
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <ancientsiam@.yahoo.com> wrote in message
news:ebl2DFEMFHA.3448@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am wondering if I can user SQL server to store Word documents in the
BLOB
> field and then use Index Server to crawl through those documents (maybe on
a
> timed basis) to generate a catalog which can be used for web based queries
> against. If this can be done then any caveats would also be welcome.
> Thanks,
>

BLOB index search

Hi,
I am wondering if I can user SQL server to store Word documents in the BLOB
field and then use Index Server to crawl through those documents (maybe on a
timed basis) to generate a catalog which can be used for web based queries
against. If this can be done then any caveats would also be welcome.
Thanks,Yes. You can do that via SQL Full Text Search:
http://www.microsoft.com/sql/evalua...es/fulltext.asp
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <ancientsiam@.yahoo.com> wrote in message
news:ebl2DFEMFHA.3448@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am wondering if I can user SQL server to store Word documents in the
BLOB
> field and then use Index Server to crawl through those documents (maybe on
a
> timed basis) to generate a catalog which can be used for web based queries
> against. If this can be done then any caveats would also be welcome.
> Thanks,
>

BLOB in T-SQL

Hi,

can anyone help ,me out here with some design consideration reguarding importing of BLOB data to a SQL server 2000 using T-SQL statements?

I want to make an import of some documents which are stored in a Access database, to an Ms SQL server 2000. The documents are stored in the access database as a OLE Object, by now I thought of using the base64String function to convert the data from the access field and write it to the T-SQL statements which will written in a text batch file. And then I apply the SQL Convert function something like:

INSERT INTO testBin VALUES(convert(image,'base64sting_encoded'))
go

Does this work? Is it correct what I am doing?

Thanks.

one rule i remember about handling blob

is that it needs to be on a separate filegroup to avoid

fragmentation

|||

Access OLE Object column data goes directly into image in SQL Server. So I am not sure why you are doing the convert and it may not really produce the correct results. See below links for more details on Access migration:

http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx

http://www.microsoft.com/sql/solutions/migration/default.mspx

See the link below for some great information on when to store BLOBs in the database:

http://research.microsoft.com/research/pubs/view.aspx?type=technical+report&id=1089

|||that was also what i have in mind though i hesitate to say

Blob in Tsql

Hi,
I am writing a tsql in which i am saving contents of one table into another using "insert into.. select".
But it doesnt work for Blobs.. what should i do??
can anyone help me on this.
Thank You.Refer to UPDATETEXT/WRITETEXT/READTEXT under BOL.|||ya.. i have tried with writetext and readtext but am not able to get it right with it.

i have to read a blob from a table and insert it into another table.

the code is as follows:
/**************
DECLARE @.ptrval binary(16)
DECLARE @.destval binary(16)

SELECT @.ptrval = TEXTPTR(icon_file)
FROM subject_master where subject_id=1

READTEXT subject_master.icon_file @.ptrval 1 2000

select @.destval = TEXTPTR(icon_file)
FROM subject_master_ver where subject_id=1

Writetext subject_master_ver.icon_file @.destVal @.ptrval
************/
but it doesnt work...
can u help me out with this...

Thank You.

Originally posted by Satya
Refer to UPDATETEXT/WRITETEXT/READTEXT under BOL.|||Have you tried using DTS in this case, if its a regular process then schedule the job to do so.

HTH
Originally posted by swatisk
ya.. i have tried with writetext and readtext but am not able to get it right with it.

i have to read a blob from a table and insert it into another table.

the code is as follows:
/**************
DECLARE @.ptrval binary(16)
DECLARE @.destval binary(16)

SELECT @.ptrval = TEXTPTR(icon_file)
FROM subject_master where subject_id=1

READTEXT subject_master.icon_file @.ptrval 1 2000

select @.destval = TEXTPTR(icon_file)
FROM subject_master_ver where subject_id=1

Writetext subject_master_ver.icon_file @.destVal @.ptrval
************/
but it doesnt work...
can u help me out with this...

Thank You.|||its not a regular process... it is triggered by the user of the system.
i have not used DTS at all.
by the way what is the problem with my code??
the way i am using writetext,readtext is fine??
i am not able to find any example for writing a blob(image) into a blob(image) of a table... not available in the books online.
pls help.

Thank You.

Originally posted by Satya
Have you tried using DTS in this case, if its a regular process then schedule the job to do so.

HTH|||Try this

http://www.motobit.com/help/ScptUtl/sa307.htm

BLOB fields - questions

Hi,

Ok, I am pretty new to BLOB's on SQL SERVER 200 so I need some answeres.

1. Can BLOB Fields hold .doc and/or .pdf documents?
2. If yes, can the contents of the documents be searchable.
3. If documents are updated do BLOB Fields lengtht change/grow dynamically?

I would apreciate any answers in this matter and if possible examples were I can get a better idea on how to configure the BLOB fieds on my DB and/or documentation regarding BLOB's.

Thank you, Pepe.I am far from an expert in BLOB fields but here is my understanding of them. If there are any other gurus out there I will follow with interest as it is not something I have had cause to use/do yet.

1. Being Binary Large Objects I believe a BLOB field should be able to hold a pointer to a .doc or .pdf file.

2. I don't think you can search on the BLOB field as it is only a pointer to the actuall file. I seem to remember form somewhere though that you may be able to search them if it is full text indexed. (Don't quote me on that though)

3. As the table only stores a pointer to the BLOB the length of the field doesn't change when the file changes.

Sorry can't supply any examples for using or configuring BLOB fields|||If you want to learn more on this topic check the following link:

Brief tutorial on using text, ntext, and image data types (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp)|||Hi,

I need to upload .doc files to a BLOB field.
Does anyone know a way to do this? I guess I could use plain Insert commands from SQL Query Analyzer. Alternately, I want to use VB.NET to create an interface for the user to upload this .docs and to retrieve them.

Thank you and I would appreciate some examples if possible.

Pepe.|||ok, since you're saying whatever the website shows is not enough, here is what i came up with when i needed something similar to store various types of documents.

Blob Fields

Can anyone explain me what is blob fields?A blob is a binary large object data type that allows the storage of things like a picture or text in SQL Server , or a pulsing ball of protoplasm from outer space that has an insatiable hunger for human flesh. :)|||rhigdon: don't talk bad about my mama! :)|||Hehe:D

BLOB Field with vbNewLine Query Help

Hello,
I have a Access form that writes a record when anything is changed. This
record is written to a MEMO field with a vbCrLf after each one. This is
done so when the form is opened, the text box will have each change
written as a New Line.
This MEMO Field is written to a Link Table in SQL 2000.
The data is written like so:
Forms!OrderHeader!Changes = Forms!OrderHeader!Changes & vbCrLf & _
Format(Date) & " Agency Disc. was changed from " & Adisc.OldValue
If I look at the Data in the table, it returns like so:
________________________________________
________________
| Field 1 |Field 2 |
|_______________________________________
_______|________|
11/19/1998 CiC No. was changed from 54241 | 53 |
9/19/1998 CiC No. was changed from 542418 | |
11/28/2004 Line ID 4 Ad ID was changed from 26 | |
11/29/2004 Agency Disc. was changed from 5 | |
________________________________________
_______|________|
The above is simply executing: SELECT [Field 1] FROM tblTest WHERE
[Field 2]=53
I've been asked to create a report based on the LAST change date. In
this case 11/29/2004 (Line 4 in Field 1). My question is: what's the
best way to go through this 197, Line Break blob and come up with a
query to return the last date?
- Somehow use RIGHT() ans go backwards to find the date?
- Somehow find the position of, in this case, the 3rd New Line Character
and then SELECT the next LEFT up to the end of the date ?
I've been dying over this for two days now. Can someone give me a
pointer. Thank you much.Answered in .programming. Please don't multi-post.
http://www.aspfaq.com/
(Reverse address to reply.)
"Jim Lou" <jim@.no_spam_no.com> wrote in message
news:MPG.1c165b5cff12dc6a9896a6@.msnews.microsoft.com...
> Hello,
> I have a Access form that writes a record when anything is changed. This
> record is written to a MEMO field with a vbCrLf after each one. This is
> done so when the form is opened, the text box will have each change
> written as a New Line.
> This MEMO Field is written to a Link Table in SQL 2000.
> The data is written like so:
> Forms!OrderHeader!Changes = Forms!OrderHeader!Changes & vbCrLf & _
> Format(Date) & " Agency Disc. was changed from " & Adisc.OldValue
> If I look at the Data in the table, it returns like so:
> ________________________________________
________________
> | Field 1 |Field 2 |
> |_______________________________________
_______|________|
> 11/19/1998 CiC No. was changed from 54241 | 53 |
> 9/19/1998 CiC No. was changed from 542418 | |
> 11/28/2004 Line ID 4 Ad ID was changed from 26 | |
> 11/29/2004 Agency Disc. was changed from 5 | |
> ________________________________________
_______|________|
> The above is simply executing: SELECT [Field 1] FROM tblTest WHERE
> [Field 2]=53
>
> I've been asked to create a report based on the LAST change date. In
> this case 11/29/2004 (Line 4 in Field 1). My question is: what's the
> best way to go through this 197, Line Break blob and come up with a
> query to return the last date?
> - Somehow use RIGHT() ans go backwards to find the date?
> - Somehow find the position of, in this case, the 3rd New Line Character
> and then SELECT the next LEFT up to the end of the date ?
> I've been dying over this for two days now. Can someone give me a
> pointer. Thank you much.