How to create image field?

Hello,
I would like to store some images in an msde table. When I go into the
enterprise manager design table mode and pick image as a field type, it
defaults to 16 bytes in size, and doesn't allow me to change the size. I
can't create a varbinary field greater than 8000 bytes either. The msde
instance is a recent install downloaded fresh from Microsoft, so it is the
current version (but for the life of me I can't figure out how to determine
the version #).
I've also tried using a shareware package called MSDE manager, but with the
same results.
How do I create an image field of 50K, for example?
thanks
Marc Pelletier
Hi,
It is not an issue, By default it will take bytes. Image data type will
allow you store a maximum of 2 GB. No need to change any thing
VARBINARY will allow a maximum of bytes.
Thanks
Hari
SQL Server MVP
"Marc Pelletier" <no.email@.please.com> wrote in message
news:Xns959A92CA0DCBAmpdd445@.216.168.3.44...
> Hello,
> I would like to store some images in an msde table. When I go into the
> enterprise manager design table mode and pick image as a field type, it
> defaults to 16 bytes in size, and doesn't allow me to change the size. I
> can't create a varbinary field greater than 8000 bytes either. The msde
> instance is a recent install downloaded fresh from Microsoft, so it is the
> current version (but for the life of me I can't figure out how to
> determine
> the version #).
> I've also tried using a shareware package called MSDE manager, but with
> the
> same results.
> How do I create an image field of 50K, for example?
> thanks
> Marc Pelletier
|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in news:eFR6PmUxEHA.1264
@.TK2MSFTNGP12.phx.gbl:

> It is not an issue, By default it will take bytes. Image data type will
> allow you store a maximum of 2 GB. No need to change any thing
> VARBINARY will allow a maximum of bytes.
>
I don't mean to look a gift horse in the mouth, but you haven't told me
anything. Do you mean to say that even though it appears to be 16 bytes, I
can actually stuff whatever I want in there? That doesn't seem likely.
cheers
Marc Pelletier
|||> I don't mean to look a gift horse in the mouth, but you haven't told me
> anything. Do you mean to say that even though it appears to be 16 bytes, I
> can actually stuff whatever I want in there? That doesn't seem likely.
Stop looking at enterprise manager to "tell you anything." The 16 bytes
represents a pointer to the data, not the actual data. Hari was not lying
when he said that you can store ~2 GB of data in there...
|||Typically, all that is stored in the data row for image, text, or ntext
columns is a 16 byte pointer to where the actual data is stored. You can
store up to 2GB of data for each data value using these data types.
For more information about how the data for these data types are stored,
see:
http://msdn.microsoft.com/library/?u...asp?frame=true
http://msdn.microsoft.com/library/?u...asp?frame=true
http://msdn.microsoft.com/library/?u...asp?frame=true
Alan Brewer [MSFT]
Content Architect
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
news:#l6OeKbxEHA.2876@.TK2MSFTNGP12.phx.gbl:

> Stop looking at enterprise manager to "tell you anything." The 16
> bytes represents a pointer to the data, not the actual data. Hari was
> not lying when he said that you can store ~2 GB of data in there...
>
Ok, thanks, I didn't think he was lying ( and if I gave that impression,
sorry). That being the case, how come varbinary allows me to set the size
up to 8000, when it also has a limit of ~2G? How would I define my field
to store a large amount of data?
I'm guessing that under the hood they are essentially the same type,
since I could stream any binary data into an image field anyway, couldn't
I?
thanks
Marc Pelletier
|||> sorry). That being the case, how come varbinary allows me to set the size
> up to 8000, when it also has a limit of ~2G?
Who said varbinary has a limit of ~2 GB?

> How would I define my field
> to store a large amount of data?
Using the IMAGE datatype, not VARBINARY.

> I'm guessing that under the hood they are essentially the same type,
> since I could stream any binary data into an image field anyway, couldn't
> I?
I'm not going to presume to know exactly how the engine works with these
types. But I know that they are stored at least slightly differently,
similar to VARCHAR(8000) and TEXT (even though, deep down, they are both
just storing text).
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
news:O0zalzcxEHA.2568@.TK2MSFTNGP10.phx.gbl:

> Who said varbinary has a limit of ~2 GB?
>
Well... I did. But I shouldn't have. If it isn't clear already, I'm new to
sql server. I generally vigorously avoid database programming, but can't
avoid it for my current project.
Thanks for the help, everyone. I think I'm away for now.
Marc
|||Hello,
I am new to databases. But i dont understand how one could store a picture
in a cell in a database? Or do you mean that there is a link to the picture
in the cell?
Thanks
WStoreyII
"Marc Pelletier" wrote:

> Hello,
> I would like to store some images in an msde table. When I go into the
> enterprise manager design table mode and pick image as a field type, it
> defaults to 16 bytes in size, and doesn't allow me to change the size. I
> can't create a varbinary field greater than 8000 bytes either. The msde
> instance is a recent install downloaded fresh from Microsoft, so it is the
> current version (but for the life of me I can't figure out how to determine
> the version #).
> I've also tried using a shareware package called MSDE manager, but with the
> same results.
> How do I create an image field of 50K, for example?
> thanks
> Marc Pelletier
>
|||=?Utf-8?B?V1N0b3JleUlJ?= <WStoreyII@.discussions.microsoft.com> wrote in
news:54836FEC-DDCB-4F80-85F0-13F580D4C292@.microsoft.com:

> I am new to databases. But i dont understand how one could store a
> picture in a cell in a database? Or do you mean that there is a link
> to the picture in the cell?
>
WStorey,
The image is stored as a binary stream directly in a blob (binary large
object, I think ) field of the database. It adds a substantial overhead to
the process, and I think that in many cases it is better to store the image
on disk and store a reference to it in the db, as you have suggested.
However it is what I am going to do. In my case the number ( and size) of
images will be fairly small and dynamic, maybe a couple of thousand, and
will be accessed a lot, so I expext the sql server cacheing to help with
the performance hit.
The how is explained quite clearly in
http://support.microsoft.com/default...en-us;309158#1
for the csharp case.
Hope this helps.
Marc Pelletier

How to create column in existing table

Hi i want to create a store procedure
i have table called ABC which has 10 column and have lot of data now i want
to design a store procedure to add another emplty column into that table
which i will fill with another store procedure that i already have thanksUse Alter Table..ADD
see example
CREATE TABLE ABC (id INT,SomeColumn VARCHAR(49))
SELECT * FROM ABC
ALTER TABLE ABC
ADD SomeOtherColumn int
SELECT * FROM ABC
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||You're going to do this once, or all the time? If this is something you're
going to be doing repeatedly, I'd strongly recommend reviewing your
requirements and coming up with a more sane implementation plan.
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:DB18DF5B-16CF-4918-A42D-8406A1617675@.microsoft.com...
> Hi i want to create a store procedure
> i have table called ABC which has 10 column and have lot of data now i
> want
> to design a store procedure to add another emplty column into that table
> which i will fill with another store procedure that i already have thankssql

How to Count the Items In Dataregion With out writing Query

Hi Group,
How to Count the items
Sample:
ID TestColumn
1 Coding
2 Design
3 Coding
4 Coding
5 Requirement
6 Coding
My Output Should be
Requirements Count Design Count Coding Count
1 1 4If you make a group on the Testcolumn, you may use one of the Count()
functions to count the rows...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Manoj.Pasumarthi" <ManojPasumarthi@.discussions.microsoft.com> wrote in
message news:19D19F77-093A-4A54-BC72-4520693088F1@.microsoft.com...
> Hi Group,
> How to Count the items
> Sample:
> ID TestColumn
> 1 Coding
> 2 Design
> 3 Coding
> 4 Coding
> 5 Requirement
> 6 Coding
> My Output Should be
> Requirements Count Design Count Coding Count
> 1 1 4|||You might also consider these expressions:
=Sum(iif(Fields!TestColumn.Value = "Coding", 1, 0))
=Sum(iif(Fields!TestColumn.Value = "Design", 1, 0))
=Sum(iif(Fields!TestColumn.Value = "Requirement", 1, 0))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OmdPRQTrEHA.1952@.TK2MSFTNGP12.phx.gbl...
> If you make a group on the Testcolumn, you may use one of the Count()
> functions to count the rows...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Manoj.Pasumarthi" <ManojPasumarthi@.discussions.microsoft.com> wrote in
> message news:19D19F77-093A-4A54-BC72-4520693088F1@.microsoft.com...
> > Hi Group,
> >
> > How to Count the items
> >
> > Sample:
> > ID TestColumn
> > 1 Coding
> > 2 Design
> > 3 Coding
> > 4 Coding
> > 5 Requirement
> > 6 Coding
> >
> > My Output Should be
> > Requirements Count Design Count Coding Count
> > 1 1 4
>

how to copy design of table

i want to create a new table with the existing design of any another table without copying the data of that table in query analyzer not by enterprise managerselect *
into newtable
from anyanothertable
where 1=0:)|||Be aware that Rudy's technique, while fast, will not copy indexes, triggers, or constraints, or identities.|||is this homework?

You seem to know how to script a table in EM, so why not?|||I would just create a script file for the table and it's indexes, etc. and run it in the new server/database. I would do this creation of the script by right-clicking on "all tasks" -> "generate SQL script" in EM. I would create the script that way because I am too lazy to write it by hand/BOL lookups.|||Hi,
we can get the exact schema of procedures, triggers using sp_helptext [object name] but to get the schema of the existing table we cant use sp_helptext. so you can use the procedure mentioned in the below link to know the DDL of the current table.

http://www.koders.com/sql/fid7D3195CD2CA27581E1073314FF58005D2DA4E82B.aspx?s =datediff

Ex:
The name of the procedure used is sp__revtable
sp__revtable [table_name] will give schema of the table. By using it, you can create the schema for the new table|||thanks i got it|||Hi,
we can get the exact schema of procedures, triggers using sp_helptext [object name] but to get the schema of the existing table we cant use sp_helptext. so you can use the procedure mentioned in the below link to know the DDL of the current table.

http://www.koders.com/sql/fid7D3195CD2CA27581E1073314FF58005D2DA4E82B.aspx?s =datediff

Ex:
The name of the procedure used is sp__revtable
sp__revtable [table_name] will give schema of the table. By using it, you can create the schema for the new table

that proc is for sybase, not sql server. parts of it may work, probably not the whole thing, though.|||that proc is for sybase, not sql server. parts of it may work, probably not the whole thing, though.Oh gee, so you mean that I can't take a control computer from a Toyota and put it into a Lexus?

-PatP