Impact of SELECTed colums on the execution plan

Hi guys,

I have something weird that I want to understand.

I have a big table, containing around 17 millions of rows. This table has been progressively indexed over time, mainly by following some of the recommendations of the DB tuning advisor. As a result, we have around ten indexes on this table, some of them are using the new "INCLUDE" feature of SQL2005 indexes with non-key data.

The problem I have is the following : I have a very simple query (SELECT <some columns> FROM that_table WHERE <simple clause conditions>) which should benefit from the indexes built on the table. But depending on the columns I select, the execution plan varies totally !!

With one column selected, the good index is used, and the query is fast.

With two columns selected, the execution plan becomes complicated (several different indexes seeked in parallel). I guess the optimization system is trying to get benefit from some non-key data included in some of the indexes ? But I'm not sure...

With a "SELECT *" no appropriate index is used. The excution plan shows it will do a "clustered index scan" over the primary key (which is not part of the where clause), resulting in a full scan of the table... OK, I know that "SELECT *" is not a good practice, but anyway, this result is very surprising.

(I tried to make it short, I can detail the queries and the construction of the indexes if needed, but the main info is there I think)

How can it be possible to have so many differences, just by changing the list of SELECTed columns ? It's beyond my understanding of what is an index...

Thanks for your help

Mathieu

Hi Mathieu,

There's a few things that could be going on here, and it's hard to make a definitive call without investigating the query plan, but...

If you include columns in the select list that are not served by the index the used in order to serve the where clause, the engine will need to perform what's known as a bookmark lookup in order to retrieve the column values that are not included in the index. It of course gets a bit more complicated if the optimiser chooses parallel indexes.

So, if your query was something like:

SELECT id, name, dob

FROM tblPerson

WHERE id = 993

You can also try execting the query with OPTION (MAXDOP 1) in order to determine if the generation of a parallel query plan is introducing unnecessary overhead.

You would create an index on the id column and list name and dob columns in the new INCLUDE clause. This would be a good starting point, but you'd need to take into account all other queries against this table in order to decide if indeed this. Indexing is a huge subject, so get googling! :)

Cheers,

Rob

|||

To be clearer :

1st case :

SELECT a FROM table WHERE b=constant AND c>constant AND d LIKE 'string%'.

c AND d are covered by an index, and the column a is included (INCLUDE keyword) in that index. b, c, and d are regulars data columns (not involved in PK). The exec plan is fine, the query is fast.

2nd case : I just add a column in the SELECT, the where clause is left untouched

SELECT a, e FROM table WHERE b=constant AND c>constant AND d LIKE 'string%'.

Here, the column e is not included in the index mentionned hereunder. The exec plan becomes complicated, involving others indexes in parallel. The query becomes slow.

3rd case : retrieving all columns, the where clause is still left untouched

SELECT * FROM table WHERE b=constant AND c>constant AND d LIKE 'string%'.

This time, the exec plan is "clustered index scan" over the PK. But the PK does not appear in the where clause ! Result is a full scan of the 17millions of rows... catastrophic !

While writing this post, I'm getting convinced that SQL Server is perturbed by the INCLUDEd columns in the indexes... What's your opinion ?

|||

Hi Mathieu,

As mentioned, if a column in the select list is not included in the index, a bookmark lookup is used. So, in the 2nd case above, you would either include column e in the index used for the operation either as a key value or in the INCLUDE list.

In the 3rd case, it would appear the optimiser has decided that rather then performing lookups for all the columns not servicable by the index (ie the SELECT *), it has chosen to perform a full scan. I'd need to see the full query plan and the schema to provide more info, but it sounds like you need to revisit your indexing strategy from the ground up.

Although INCLUDE is new to 2005, I've never had nor heard of an issue directly related to its use (yet).

Cheers,

Rob

immediate updating triggers have "incorrect syntax near ')'."

Hello,
I am using SQL 7.0 to replicate to an immediate-updating subscriber and I am
having trouble when updating a table from the subscriber. When I run the
update statement, I get the error "incorrect syntax near ')'. I believe that
this is either in the auto-generated MSsync trigger or the sp on the
publisher. I am sure the original update statement is correct.
It seems almost as though a parameter is missing... I don't know.
One thing that I noticed is that I only receive this error on certain
tables. The main differences are:
1. the tables that don't work are really wide in columns (by all means they
should be three tables each) whereas the tables that do work are relatively
small.
2. the tables that don't work used to have text columns, but I changed them
to enormous varchar columns. The tables that do work have never had text
columns.
My experience with replication is limited, so any help or advice in this
matter would be greatly appreciated.
Thanks in advance,
-b.
Please can you confirm what service pack you are using, as this issue (or a
v.similar one) cropped up prior to sp2 and was fixed there
(http://support.microsoft.com/kb/238658/EN-US/).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you for your reply. Yes, I read something about that. I had applied sp4
and assumed that it was cumulative... is it not?
-b.
"Paul Ibison" wrote:

> Please can you confirm what service pack you are using, as this issue (or a
> v.similar one) cropped up prior to sp2 and was fixed there
> (http://support.microsoft.com/kb/238658/EN-US/).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Okay, I just read that the service packs are cumulative.
However, I did read something that said that text and image columns will not
replicate normally from immediate updating subscribers, and that special
considerations need to be made
(http://msdn.microsoft.com/library/de...lplan_1l4e.asp)
I no longer have text columns in the tables, but I DID, however, publish to
the subscriber previously with text fields before changing them to varchars
and publishing to the subscriber again. Is it possible that these triggers
remain from the previous publication...?
-b.
"pukeboot" wrote:
[vbcol=seagreen]
> Thank you for your reply. Yes, I read something about that. I had applied sp4
> and assumed that it was cumulative... is it not?
> -b.
> "Paul Ibison" wrote:
|||That shouldn't be the case, unless you did a nosync initialization. Can you
post up the text of some of the triggers so I can take a look?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||to avoid a massively lengthy post, I have sent the trigger code directly to
you via attachment...
Thanks again,
-b.
"Paul Ibison" wrote:

> That shouldn't be the case, unless you did a nosync initialization. Can you
> post up the text of some of the triggers so I can take a look?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||My email address isn't the one I post with (long story). Please send to Paul
.. Ibison@.ReplicationAnswers . Com (no spaces).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I read that as "paulibison@.replicationanswers.com" ... I wasn't sure with the
line breaks and the elipsis.
I forwarded the message there. I hope that's it.
-b.
"Paul Ibison" wrote:

> My email address isn't the one I post with (long story). Please send to Paul
> .. Ibison@.ReplicationAnswers . Com (no spaces).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Bobby,
there's nothing I can see wrong in the trigger, and no reference to text
columns. As you are on sp4 (publisher, distributor and subscriber?), this is
baffling. I'd run profiler to see where it gets to, and fails, in the
synchronization, and also enable logging to see what that reveals
(http://support.microsoft.com/?id=312292).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I am unfamiliar with the profiler, but I gave it a go and found that the
"internal abort" event was occuring after this line in the trigger (bear with
this... it's a long line):
exec @.retcode = dbo.sp_executesql @.rpc_proc, @.rpc_types,
@.c1
output,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8,@.c9,@.c10,@.c11,@. c12,@.c13,@.c14,@.c15,@.c16,@.c17,@.c18,@.c19,@.c20,@.c21,@. c22,@.c23,@.c24,@.c25,@.c26,@.c27,@.c28,@.c29,@.c30,@.c31,@. c32,@.c33,@.c34,@.c35,@.c36,@.c37,@.c38,@.c39,@.c40,@.c41,@. c42,@.c43,@.c44,@.c45,@.c46,@.c47,@.c48,@.c49,@.c50,@.c51,@. c52,@.
c53,@.c54,@.c55,@.c56,@.c57,@.c58,@.c59,@.c60,@.c61,@.c62,@. c63,@.c64,@.c65,@.c66,@.c67,@.c68,@.c69,@.c70,@.c71,@.c72,@. c73,@.c74,@.c75,@.c76,@.c77,@.c78,@.c79,@.c80,@.c81,@.c82,@. c83,@.c84,@.c85,@.c86,@.c87,@.c88,@.c89,@.c90,@.c91,@.c92,@. c93,@.c94,@.c95,@.c96,@.c97,@.c98,@.c99,@.c100,@.c101,@.c10 2,@.c1
03,@.c104,@.c105,@.c106,@.c107,@.c108,@.c109,@.c110,@.c111 ,@.c112,@.c113,@.c114,@.c115,@.c116,@.c117,@.c118,@.c119,@. c120,@.c121,@.c122,@.c123,@.c124,@.c125,@.c126,@.c127,@.c1 28,@.c129,@.c130,@.c131,@.c132,@.c133,@.c134,@.c135,@.c136 ,@.c137,@.c138,@.c139,@.c140,@.c141,@.c142,@.c143,@.c144,@. c145,
@.c146,@.c147,@.c148,@.c149,@.c150,@.c151,@.c152,@.c153,@.c 154,@.c155,@.c156,@.c157,@.c158,@.c159,@.c160,@.c161,@.c16 2,@.c163,@.c164,@.c165,@.c166,@.c167,@.c168,@.c169,@.c170, @.c171,@.c172,@.c173,@.c174,@.c175,@.c176,@.c177,@.c178,@.c 179,@.c180,@.c181,@.c182,@.c183,@.c184,@.c185,@.c186,@.c18 7,@.c1
88,@.c189,@.c190,@.c191,@.c192,@.c193,@.c194,@.c195,@.c196 ,@.c197,@.c198,@.c199,@.c200,@.c201,@.c202,@.c203,@.c204,@. c205,@.c206,@.c207,@.c208,@.c209,@.c210,@.c211,@.c212,@.c2 13,@.c214,@.c215,@.c216,@.c217,@.c218,@.c219,@.c220,@.c221 ,@.c222,@.c223,@.c224,@.c225,@.c226,@.c227,@.c228,@.c229,@. c230,
@.c231,@.c232,@.c233,@.c234,@.c235,@.c236
output,@.c237,@.c238,@.c239,@.c240,@.c241,@.c242,@.c243,@. c244,@.c245,@.c246,@.c247,@.c248
,@.c1_old,@.c2_old,@.c3_old,@.c4_old,@.c5_old,@.c6_old,@. c7_old,@.c8_old,@.c9_old,@.c10_old,@.c11_old,@.c12_old, @.c13_old,@.c14_old,@.c15_old,@.c16_old,@.c17_old,@.c18_ old,@.c19_old,@.c20_old,@.c21_old,@.c22_old,@.c23_old,@. c24_old,@.c25_old,@.c26_old,@.c27_old,@.c28_old,@.c29_o ld,@.c
30_old,@.c31_old,@.c32_old,@.c33_old,@.c34_old,@.c35_ol d,@.c36_old,@.c37_old,@.c38_old,@.c39_old,@.c40_old,@.c4 1_old,@.c42_old,@.c43_old,@.c44_old,@.c45_old,@.c46_old ,@.c47_old,@.c48_old,@.c49_old,@.c50_old,@.c51_old,@.c52 _old,@.c53_old,@.c54_old,@.c55_old,@.c56_old,@.c57_old, @.c58_
old,@.c59_old,@.c60_old,@.c61_old,@.c62_old,@.c63_old,@. c64_old,@.c65_old,@.c66_old,@.c67_old,@.c68_old,@.c69_o ld,@.c70_old,@.c71_old,@.c72_old,@.c73_old,@.c74_old,@.c 75_old,@.c76_old,@.c77_old,@.c78_old,@.c79_old,@.c80_ol d,@.c81_old,@.c82_old,@.c83_old,@.c84_old,@.c85_old,@.c8 6_old
,@.c87_old,@.c88_old,@.c89_old,@.c90_old,@.c91_old,@.c92 _old,@.c93_old,@.c94_old,@.c95_old,@.c96_old,@.c97_old, @.c98_old,@.c99_old,@.c100_old,@.c101_old,@.c102_old,@.c 103_old,@.c104_old,@.c105_old,@.c106_old,@.c107_old,@.c 108_old,@.c109_old,@.c110_old,@.c111_old,@.c112_old,@.c 113_o
ld,@.c114_old,@.c115_old,@.c116_old,@.c117_old,@.c118_o ld,@.c119_old,@.c120_old,@.c121_old,@.c122_old,@.c123_o ld,@.c124_old,@.c125_old,@.c126_old,@.c127_old,@.c128_o ld,@.c129_old,@.c130_old,@.c131_old,@.c132_old,@.c133_o ld,@.c134_old,@.c135_old,@.c136_old,@.c137_old,@.c138_o ld,@.c
139_old,@.c140_old,@.c141_old,@.c142_old,@.c143_old,@.c 144_old,@.c145_old,@.c146_old,@.c147_old,@.c148_old,@.c 149_old,@.c150_old,@.c151_old,@.c152_old,@.c153_old,@.c 154_old,@.c155_old,@.c156_old,@.c157_old,@.c158_old,@.c 159_old,@.c160_old,@.c161_old,@.c162_old,@.c163_old,@.c 164_o
ld,@.c165_old,@.c166_old,@.c167_old,@.c168_old,@.c169_o ld,@.c170_old,@.c171_old,@.c172_old,@.c173_old,@.c174_o ld,@.c175_old,@.c176_old,@.c177_old,@.c178_old,@.c179_o ld,@.c180_old,@.c181_old,@.c182_old,@.c183_old,@.c184_o ld,@.c185_old,@.c186_old,@.c187_old,@.c188_old,@.c189_o ld,@.c
190_old,@.c191_old,@.c192_old,@.c193_old,@.c194_old,@.c 195_old,@.c196_old,@.c197_old,@.c198_old,@.c199_old,@.c 200_old,@.c201_old,@.c202_old,@.c203_old,@.c204_old,@.c 205_old,@.c206_old,@.c207_old,@.c208_old,@.c209_old,@.c 210_old,@.c211_old,@.c212_old,@.c213_old,@.c214_old,@.c 215_o
ld,@.c216_old,@.c217_old,@.c218_old,@.c219_old,@.c220_o ld,@.c221_old,@.c222_old,@.c223_old,@.c224_old,@.c225_o ld,@.c226_old,@.c227_old,@.c228_old,@.c229_old,@.c230_o ld,@.c231_old,@.c232_old,@.c233_old,@.c234_old,@.c235_o ld,@.c236_old,@.c237_old,@.c238_old,@.c239_old,@.c240_o ld,@.c
241_old,@.c242_old,@.c243_old,@.c244_old,@.c245_old,@.c 246_old,@.c247_old,@.c248_old
, @.bitmap
plus, I also get an error that says:
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@.c30o'.
If you have any ideas what might be causing this, or if you have any further
advice on what I can do to resolve the problem, I would love to hear it.
Thanks for all your input.
-b.
"Paul Ibison" wrote:

> Bobby,
> there's nothing I can see wrong in the trigger, and no reference to text
> columns. As you are on sp4 (publisher, distributor and subscriber?), this is
> baffling. I'd run profiler to see where it gets to, and fails, in the
> synchronization, and also enable logging to see what that reveals
> (http://support.microsoft.com/?id=312292).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Immediate processing of large recordset with VB

I have a table with roughly 165,000,000 records. Within
this table is a subset of roughly 6,000,000 records I
need to retrieve and process sequentially with some VB
code. These records are identified by a numeric code in
1 field. There is an index built on this field. When I
run a simple query, it takes roughly 20 minutes to return
the subset of records.
My question: Is there a way to have SQL return record 1
immediately to the VB program so it can begin
processing? As it stands when I issue the ADO command to
build the recordset I am continuosly hitting timeouts. I
assume that's because the SQL server is building the
entire recordset to return to the client. While I can
conceiveably increase my timeout(s), I'd like the client
to start processing whe the SQL server has pulled the
first record, or block of 1000 or something, rather than
wait for the entire recordset to be assembled.
Thanks for your help.You can use:
SELECT ...
FROM ...
WHERE...
OPTION (FAST n)
Where n will be number of rows in the first batch that will returned
quicker. Note that using this option can slow down overal execution time of
your query, and by a not inconsiderable amount.
Why exactly do you need to process 6 million rows sequentially in VB? There
are many things that can be done quicker in T-SQL than in VB with a set that
large.
Jacco Schalkwijk
SQL Server MVP
"DB" <dave_nospam_blair@.adelphia.net> wrote in message
news:130301c52faf$e7457bc0$a501280a@.phx.gbl...
>I have a table with roughly 165,000,000 records. Within
> this table is a subset of roughly 6,000,000 records I
> need to retrieve and process sequentially with some VB
> code. These records are identified by a numeric code in
> 1 field. There is an index built on this field. When I
> run a simple query, it takes roughly 20 minutes to return
> the subset of records.
> My question: Is there a way to have SQL return record 1
> immediately to the VB program so it can begin
> processing? As it stands when I issue the ADO command to
> build the recordset I am continuosly hitting timeouts. I
> assume that's because the SQL server is building the
> entire recordset to return to the client. While I can
> conceiveably increase my timeout(s), I'd like the client
> to start processing whe the SQL server has pulled the
> first record, or block of 1000 or something, rather than
> wait for the entire recordset to be assembled.
> Thanks for your help.|||See clause OPTION of the select statement. There is an option FAST (xxx) tha
t
can help. If you are using ADO, also recommend to increase recordset propert
y
CacheSize.
Can you explain what are you trying to do?, may be somebody can come with a
set-based solution.
AMB
"DB" wrote:

> I have a table with roughly 165,000,000 records. Within
> this table is a subset of roughly 6,000,000 records I
> need to retrieve and process sequentially with some VB
> code. These records are identified by a numeric code in
> 1 field. There is an index built on this field. When I
> run a simple query, it takes roughly 20 minutes to return
> the subset of records.
> My question: Is there a way to have SQL return record 1
> immediately to the VB program so it can begin
> processing? As it stands when I issue the ADO command to
> build the recordset I am continuosly hitting timeouts. I
> assume that's because the SQL server is building the
> entire recordset to return to the client. While I can
> conceiveably increase my timeout(s), I'd like the client
> to start processing whe the SQL server has pulled the
> first record, or block of 1000 or something, rather than
> wait for the entire recordset to be assembled.
> Thanks for your help.
>|||You can use TOP to retrieve a batch of a predefined number of rows:
SELECT TOP 10000 WITH TIES col1, col2, ...
FROM YourTable
WHERE key_col > @.key_col
/* @.key_col = The primary key of the last value processed */
AND code = 1
ORDER BY key_col
Also consider whether your VB process would be better done in SQL or
located in the middle tier if it isn't already.
David Portas
SQL Server MVP
--|||> I am continuosly hitting timeouts. I
> assume that's because the SQL server is building the
> entire recordset to return to the client.
Yeah, some of the time that you have to wait might involve query processing
time but the rest of the time involves all that data flowing across the
network onto your PC. I assume that the memory usage on the PC that the VB
program is running on goes up as the 6 million rows are returned.
When you "process" a row of data is there any way to determine that the
specific row is processed? If so you could do something like:
SELECT TOP 1000
FROM YourTable
/*pull the rows (based on the numeric code) that you are interested in*/
WHERE SomeColumn = @.TheNumericCode
/*this pulls the rows that have not been processed*/
AND HasThisRowBeenProcessed = 0
/*important to make sure that the rows are processed in the correct order*/
ORDER BY ...
Keith
"DB" <dave_nospam_blair@.adelphia.net> wrote in message
news:130301c52faf$e7457bc0$a501280a@.phx.gbl...
> I have a table with roughly 165,000,000 records. Within
> this table is a subset of roughly 6,000,000 records I
> need to retrieve and process sequentially with some VB
> code. These records are identified by a numeric code in
> 1 field. There is an index built on this field. When I
> run a simple query, it takes roughly 20 minutes to return
> the subset of records.
> My question: Is there a way to have SQL return record 1
> immediately to the VB program so it can begin
> processing? As it stands when I issue the ADO command to
> build the recordset I am continuosly hitting timeouts. I
> assume that's because the SQL server is building the
> entire recordset to return to the client. While I can
> conceiveably increase my timeout(s), I'd like the client
> to start processing whe the SQL server has pulled the
> first record, or block of 1000 or something, rather than
> wait for the entire recordset to be assembled.
> Thanks for your help.|||I wonder is possible to indetify that subset of data? I mean, using an
indentity field.
In any case, carrying up that subset into a temp table and working with that
?
See you,
"Keith Kratochvil" wrote:

> Yeah, some of the time that you have to wait might involve query processin
g
> time but the rest of the time involves all that data flowing across the
> network onto your PC. I assume that the memory usage on the PC that the V
B
> program is running on goes up as the 6 million rows are returned.
> When you "process" a row of data is there any way to determine that the
> specific row is processed? If so you could do something like:
> SELECT TOP 1000
> FROM YourTable
> /*pull the rows (based on the numeric code) that you are interested in*/
> WHERE SomeColumn = @.TheNumericCode
> /*this pulls the rows that have not been processed*/
> AND HasThisRowBeenProcessed = 0
> /*important to make sure that the rows are processed in the correct order*
/
> ORDER BY ...
> --
> Keith
>
> "DB" <dave_nospam_blair@.adelphia.net> wrote in message
> news:130301c52faf$e7457bc0$a501280a@.phx.gbl...
>

imitating nested "FOREACH" loop in SQL Query

Dear All,

I need to create a query to list all the subfolders within a folder.

I have a database table that lists the usual properties of each of the folder.

I have another database table that has two columns

1. Parent folder
2. Child folder

But this table maintains the parent child relationship only to one level.

For example if i have a folder X that has a subfolder Y and Z.
And Y has subfolders A and B.
and B has subfolder C and D
and C has subfolder E and F

The database table will look like

parentfolder child folder
X Y
X Z
Y A
Y B
B C
B D
C E
C F

I want to write a query which will take a folder name as the input and will provide me a list of all the folders and subfolders under it. The query should be based on the table (parent - child) and there should not be any restriction on the subfolder levels to search and report for.

I have been banging my head to do this but i have failed so far. Any help on this will be highly appreciated.

The APPLY operator will do what you need.

Check out:

http://msdn2.microsoft.com/en-us/library/ms175156.aspx

For a description and an example that pretty much is like your needs.

|||

In sql server 2005 you can use CTE..

Code Snippet

Create Table #folder (

[parentfolder] Varchar(100) ,

[childfolder] Varchar(100)

);

Insert Into #folder Values('X','Y');

Insert Into #folder Values('X','Z');

Insert Into #folder Values('Y','A');

Insert Into #folder Values('Y','B');

Insert Into #folder Values('B','C');

Insert Into #folder Values('B','D');

Insert Into #folder Values('C','E');

Insert Into #folder Values('C','F');

;With CTE([parentfolder],[childfolder],[Level],[Paths]) as

(

Select [parentfolder],[childfolder], 1 Level, Cast(Parentfolder + '\' + childfolder as varchar) Paths From #folder Where parentfolder = 'X'

UNION ALL

Select data.[parentfolder],data.[childfolder], Level + 1,Cast(Paths + '\' + data.[childfolder] as varchar)From #folder Data Join CTE On Data.ParentFolder = CTE.childfolder

)

Select * from CTE Order By Paths

|||Nicely done Mani!

Images not displaying on reports!

We are trying to create a Report w/ images that are stored in the SQL Server table.

These images are being copied and pasted into an ACCESS application (w/, as said above, a SQL Server back-end)....

All I get on the report is a box with a red 'X'.

(Now, I have tried the routine to remove the OLE header as suggested on other places on the Internet. While this works for the Northwind images, this is not working on our images. Which leads me to guess that the OLE Header on our images may be different than 78. But how do we know or calculate that?)..

My guess would be that some other people have encountered similar problems.

Any help will be highly appreciated.

Thanks..

Were you able to find the solution for this?

I'm encountering the same problem.

Thanks!

sql

Images not displaying

Hi,
I have images that reside in a database table on SQL server. When previewing
a report in Report Designer, the images display just fine. After uploading
the report to Report Manager, the images don't display and a red x displays.
Another issue is that the graph on the report is not displaying as well. A
red x displays instead. Again, the report previews perfectly in Report
Designer. Any suggestions?
Thanks!One more thing I forgot to mention. All drilldowns are not working either.
When you click on a drilldown, it makes the "click" sound and moves a bit to
the left. It doesn't drilldown. Again, it works just fine in Report Designer.
"clutch" wrote:
> Hi,
> I have images that reside in a database table on SQL server. When previewing
> a report in Report Designer, the images display just fine. After uploading
> the report to Report Manager, the images don't display and a red x displays.
> Another issue is that the graph on the report is not displaying as well. A
> red x displays instead. Again, the report previews perfectly in Report
> Designer. Any suggestions?
> Thanks!

Images in SQL

I notice that SQL 2000 has a datatype of image.
My question is after I setup the table and fields how do I populate the
table with image data?Look up the usage and example of statements UPDATETEXT and WRITETEXT in SQL
Server Books Online.
Anith|||I looked those up and they just give examples of writing text to the fields.
I would like to know how to add a picture to the field.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OoEv9%23glGHA.4444@.TK2MSFTNGP02.phx.gbl...
> Look up the usage and example of statements UPDATETEXT and WRITETEXT in
> SQL Server Books Online.
> --
> Anith
>|||Image and nText fields are binary fields, like the old BLOB (binary large
object) fields.
To write an image into thsee fields you need to serialize the data into eith
er a
stream or use ADO or ADO.Net
A better question back to you is how and when do you want to get the picture
s
into the database?
Alternatively, you may wish to simply store the images somewhere and use a p
ath
to the raw image files, there are benefits and costs for both methods, in th
e
database or a pointer to a file.
HTH
JeffP...
<Preacher Man> wrote in message news:udRf$ChlGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I looked those up and they just give examples of writing text to the field
s.
> I would like to know how to add a picture to the field.
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OoEv9%23glGHA.4444@.TK2MSFTNGP02.phx.gbl...
>|||I am trying to write a simple application for printing employee badges. I
have VFP 9.0 that I will be using as the application writer.
Any ideas on how I should enter these images into the database? I can use a
VFP table or SQL is doesn't really matter to me in this case.
A path to the filename would be fine for me to use also, but how would I
implement that into a form?
Thanks for any info.
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23cB$WUhlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> Image and nText fields are binary fields, like the old BLOB (binary large
> object) fields.
> To write an image into thsee fields you need to serialize the data into
> either a
> stream or use ADO or ADO.Net
> A better question back to you is how and when do you want to get the
> pictures
> into the database?
> Alternatively, you may wish to simply store the images somewhere and use a
> path
> to the raw image files, there are benefits and costs for both methods, in
> the
> database or a pointer to a file.
> HTH
> JeffP...
>
> <Preacher Man> wrote in message
> news:udRf$ChlGHA.4772@.TK2MSFTNGP04.phx.gbl...
>|||Sorry, I'm out of the loop on VFP since 3.2... but google this...
image data into SQL
HTH
JeffP...
<Preacher Man> wrote in message news:%23pQTiXhlGHA.3740@.TK2MSFTNGP02.phx.gbl...ed">
> I am trying to write a simple application for printing employee badges. I
> have VFP 9.0 that I will be using as the application writer.
> Any ideas on how I should enter these images into the database? I can use
a
> VFP table or SQL is doesn't really matter to me in this case.
> A path to the filename would be fine for me to use also, but how would I
> implement that into a form?
> Thanks for any info.
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:%23cB$WUhlGHA.4792@.TK2MSFTNGP02.phx.gbl...
>|||Here is an article that I wrote about storing and retrieving images from SQL
Server that might help:
http://www.dbazine.com/sql/sql-articles/larsen13
"Preacher Man" wrote:

> I notice that SQL 2000 has a datatype of image.
> My question is after I setup the table and fields how do I populate the
> table with image data?
>
>|||See if this helps:
http://groups.google.com/groups/sea...lum
n%22
Steve Kass
Drew University
Preacher Man wrote:

>I notice that SQL 2000 has a datatype of image.
>My question is after I setup the table and fields how do I populate the
>table with image data?
>
>|||I would store them in the filesystem, as suggested. Using the IMAGE
datatype is more of a hassle than it's worth. Here's an article describing
some of the ups and downs: http://www.aspfaq.com/show.asp?id=2149. If you
stored the path to the filename in a VARCHAR column, you would retrieve the
image by filename in your front-end application. I'm not familiar with VFP
9, so I couldn't give you specific advice on that, but I can almost
guarantee it would be easier than using TEXTPTR and READTEXT! :)
<Preacher Man> wrote in message
news:%23pQTiXhlGHA.3740@.TK2MSFTNGP02.phx.gbl...
>I am trying to write a simple application for printing employee badges. I
>have VFP 9.0 that I will be using as the application writer.
> Any ideas on how I should enter these images into the database? I can use
> a VFP table or SQL is doesn't really matter to me in this case.
> A path to the filename would be fine for me to use also, but how would I
> implement that into a form?
>

Images in Reporting Services from database

I have a data table which contains a bmp image saved as a byte array. I am trying to have this image presented in a report, however, I get a red X after I follow the wizard.

I also tried it with the SQL Northwind database and I have the same issue. I am running SQL Reporting services 2005. Any suggestions on what could be going wrong?

Joe

after placing the image you have select the image source i.e. database in your case and then you have select the field name which contains image and the MIME type as image/bmp or try image/png.

this should work

|||

I have tried with both image/bmp and image/png on northwind DB as recommended. I still get red cross. Any ideal what's wrong?

|||

I have tried with both image/bmp and image/png on northwind DB as recommended. I still get red cross. Any idea what's wrong?

|||Please read this RS BOL topic: http://msdn2.microsoft.com/en-us/library/ms156342(SQL.90).aspx

Particularly read the comment near the top if the images came originally from an Access database, because in that case the actual image data is an OLE image and you have to write an expression to remove the OLE image header from the data. This also applies to the Northwind sample database.

Furthermore, I recommend to install SQL Server 2005 SP1 for Reporting Services.

-- Robert

sql

Images in Reporting Services from database

I have a data table which contains a bmp image saved as a byte array. I am trying to have this image presented in a report, however, I get a red X after I follow the wizard.

I also tried it with the SQL Northwind database and I have the same issue. I am running SQL Reporting services 2005. Any suggestions on what could be going wrong?

Joe

after placing the image you have select the image source i.e. database in your case and then you have select the field name which contains image and the MIME type as image/bmp or try image/png.

this should work

|||

I have tried with both image/bmp and image/png on northwind DB as recommended. I still get red cross. Any ideal what's wrong?

|||

I have tried with both image/bmp and image/png on northwind DB as recommended. I still get red cross. Any idea what's wrong?

|||Please read this RS BOL topic: http://msdn2.microsoft.com/en-us/library/ms156342(SQL.90).aspx

Particularly read the comment near the top if the images came originally from an Access database, because in that case the actual image data is an OLE image and you have to write an expression to remove the OLE image header from the data. This also applies to the Northwind sample database.

Furthermore, I recommend to install SQL Server 2005 SP1 for Reporting Services.

-- Robert

Images in a table cell, conditional on a data item value

Phew!
I have a table, and in one cell I want to display an image. No big deal. However, I then want another image to be substituted for the original one, if a certain field value is false.

So the logic is,if field value is true, display image 1
if the same field value is false, display image no 2.

The expression I have written so far is:

-iif(Fields!Expr9.Value=False,"image1","image2")

where both image1 and image2 are images embedded into the report.

It shows the first image when the field value is false, but It isn't showing image 2 on lines where the field value is true.

Any takers?

What happens if you invert a condition, i.e.

=iif(Fields!Expr9.Value,"image2","image1")

Images From Sql Database

I have a database in sql server with a table containing a few images stored as binary data and there unique ids. From a Visual Basic.NET Application I want to use the crystal report to display 1 of the images based on the id that it is fed. How can I read and image from the sql database and display it in a Crystal Report? Any help offered is greatly appreciated.I've used a stored procedure to get the images from db and then add the blob field to the report. problem is that the wizard "enter parameter value" keeps prompting while you're going to drag the blob to your report. I still haven't find a solution for this problem.|||Just drag the blob column to report and go to preview mode and check if it is displaying the image|||I too have the same problem But in the case of Auto cad images(*.dwg)

I am saving the image in image field (SQL server) in binary format

Is there any solution for that?

Reply as much as earlier!|||What is the error you get?|||While adding the image field(SQL server) in crystal report,it doesn't showing any error

But

1.If the image format other than autocad(*.dwg) then it is showing the image as it is.

2.But in the case of autocad image it doesn't showing anything the field space would be blank.

If anyone guide me for this prob,please help me out

My project has been delayed because of this problem

Lingeswaran.r|||Hi all
I have some problem load image to Crytal Report 11

I have database from sql Server. The Columns Images after.
The column
Column name Data Type Length Allownulls
Picture varchar 50 *

After i save to database is

image\picture.jpg.

When i drag the column to Crystal Report 11 it have only is image\picture.jpg.
Can you help me ?
Display the picture.jpg is a picture
Thank you.|||Hi,
I had face the problem in Sql server with CrystalReports11 at Visual studio2005. By doing the following way you can solve the problem.

Let assume table "Item_t" having "ItemPicture'' column and its datatype is "Image'' and assume picture is stored in that table.

fecth the picture data to a datatable.

Let assume the picture is in dtItem & write the following method

private DataTable ConvertPicture(DataTable dtItem)
{
DataTable dtReturn = dtItem.Clone();

foreach (DataRow dRow in dtItem.Rows)
{
DataRow drRow = dtReturn.NewRow();

// Convert the picture into binary format

Byte[] bPictureInByte = (Byte[])dtItem.Row["ItemPicture''];
System.IO.MemoryStream mStream = new System.IO.MemoryStream(bPictureInByte ); // convert as stream
Bitmap pictureBimap= new Bitmap(mStream);//store in a Bitmap

// save the picture data in Local harddisk as .jpg file
pictureBimap.Save("C:\\temp\\Itempicture.Jpg", System.Drawing.Imaging.ImageFormat.Jpeg);

//Read the same jpg Picture from your Local harddisk
System.IO.FileStream fs = new System.IO.FileStream("C:\\temp\\Itempicture.Jpg", System.IO.FileMode.Open);
System.IO.BinaryReader br = new System.IO.BinaryReader(fs);

//convert to byte[] & save in your new data row
drRow = br.ReadBytes((int)br.BaseStream.Length);
br.Close();

//add the datarow to the datatable dtReturn;

dtReturn.Rows.add(drRow);

}

return dtReturn

}

now you passing the dtReturn data table to your .rpt file and print the Image.

Best wishes|||But now i don't want to Write a picture into the SQL Server
I only want write a link of it.
Example: image\picture_name.jpg
After that i load into Crytal Report.
How I can to do it ?
You can help me ?

If i write to sql server is no proplem.|||Hi all,
I want to display image in report but I can't. The following is what I do.
I create a report in C#.NET 2003, using CR XI. Data is stored in SQL Server 2005, and I store image as BLOB field. I design by drag and drop fields into report. In my code, I connect to database, get mydataset by executing a sql string, and then using rpt.SetDataSource(mydataset). All data in text field is ok but image field is empty.
Anybody help me, please.|||I know why I can't display image in report now. A simple reason is the aslias name of image field in sql statement and the image field name in my report are different.

But now I don't understand the reason why sometimes I can 't drag image field from tree view into report. Anybody know?

Images and PDF's in the same DB or in another DB?

Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:

>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>
|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
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
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>
|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas

"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
sql

Images and PDF's in the same DB or in another DB?

Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
RobertSame DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:

>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
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
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert

Images and PDF's in the same DB or in another DB?

Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
RobertThis is a multi-part message in MIME format.
--040906000104030808000509
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:
>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>
--040906000104030808000509
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Same DB.<br>
<br>
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).<br>
<br>
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up
& restore the image data separately to the data in all your other
tables, which may or may not be handy for you).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
robert madrian wrote:
<blockquote cite="miduJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
</pre>
</blockquote>
</body>
</html>
--040906000104030808000509--|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
--
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
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert

Images and complications

This may possibly be the wrong place to ask this question, but never mind. I
am in the process of creating a table to hold images, from which crystal
reports will be used to access the images. I have inserted jpg's into the ne
w
table, but they do not show in the report.
The next logical move was to have a look at the example DB's that ship with
SQL, starting with Pubs. The Pub_info has an image field, so I tried
reporting from that, still no images being displayed in the report.
Next I tried the Employees table in the Northwind db, the photo field is an
images field. These images were displayed with no problems.
Which leads to my actual question/s, does anyone know what the differences
are between the images stored in the northwind database and the pubs
database, are they different file formats (jpeg, gif, etc)?, or are there an
y
other differences?.
As a matter of interest I imported the images from the northwind db into my
new images table and the report read those fine as well.
Any help or pointers much appreciated.I dont see any specific differences in the databases and the images fields.
You might want to explore on the images specifics with Crystal reports side
I suppose. Just a distant shot ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Martin Barrett" <MartinBarrett@.discussions.microsoft.com> wrote in message
news:B3522CB8-C863-402B-8255-2796CFEB4C2A@.microsoft.com...
> This may possibly be the wrong place to ask this question, but never mind.
I
> am in the process of creating a table to hold images, from which crystal
> reports will be used to access the images. I have inserted jpg's into the
new
> table, but they do not show in the report.
> The next logical move was to have a look at the example DB's that ship
with
> SQL, starting with Pubs. The Pub_info has an image field, so I tried
> reporting from that, still no images being displayed in the report.
> Next I tried the Employees table in the Northwind db, the photo field is
an
> images field. These images were displayed with no problems.
> Which leads to my actual question/s, does anyone know what the differences
> are between the images stored in the northwind database and the pubs
> database, are they different file formats (jpeg, gif, etc)?, or are there
any
> other differences?.
> As a matter of interest I imported the images from the northwind db into
my
> new images table and the report read those fine as well.
> Any help or pointers much appreciated.

Image save into folder

Dear Professional,
Image saved in the table and I wanna copy that image into any folder let's
say... D:\Image\MineImage.gif
Is it possible ?
Thanks
Hi
You will have to write some VB or c# code to do that. SQL Server has no
native support to write out a BLOB filed to a file.
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/
"Roy" <roy@.hotmail.com> wrote in message
news:OnuaCw3cFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Dear Professional,
> Image saved in the table and I wanna copy that image into any folder let's
> say... D:\Image\MineImage.gif
> Is it possible ?
> Thanks
>
>
>
>
|||Okie I am ready to write code in VB... any website is available where I can
find that sort of help.
Thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:O0KTG33cFHA.1036@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi
> You will have to write some VB or c# code to do that. SQL Server has no
> native support to write out a BLOB filed to a file.
> 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/
> "Roy" <roy@.hotmail.com> wrote in message
> news:OnuaCw3cFHA.3932@.TK2MSFTNGP12.phx.gbl...
let's
>
|||http://support.microsoft.com/default...b;en-us;194975
http://support.microsoft.com/kb/308042/EN-US/
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/
"Roy" <roy@.hotmail.com> wrote in message
news:uj$FjV4cFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Okie I am ready to write code in VB... any website is available where I
> can
> find that sort of help.
> Thanks
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:O0KTG33cFHA.1036@.tk2msftngp13.phx.gbl...
> let's
>
|||Roy wrote:
> Okie I am ready to write code in VB... any website is available where
> I can find that sort of help.
>
I prefer a book for this. For Vb.Net 2003, I'd recommend "Programming
Microsoft Visual Basic.Net 2003," by Francesco Balena (Microsoft Press).
You're about to get into some very large topics: The .Net 1.1 framework,
Windows Forms, and ADO.Net.
Also, check out http://www.devx.com/
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||here is the simple example for saving images into disk
'=================================
Dim rst As New ADODB.Recordset
Dim Image1() As Byte
Dim ms As New ADODB.Stream
Dim cmd As New ADODB.Command
Dim l As Long
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "proc_getImage" '==== poc_code_look_below
cmd.ActiveConnection = CurrentProject.Connection
cmd.Parameters("@.ID") = ID
Set rst = cmd.Execute
ms.Type = adTypeBinary
ms.Open
l = rst(0).ActualSize
Image1() = rst(0).GetChunk(l + 1)
rst.Close
ms.Write (Image1())
deletefile ("c:\img.jpg")
ms.SaveToFile "c:\img.jpg", adSaveCreateOverWrite
ms.Close
Set cmd = Nothing
Set rst = Nothing
Set ms = Nothing
'================================
create PROCEDURE proc_GetImage
(@.ID int)
AS
BEGIN
SELECT photo
FROM tbl_images
WHERE ([ID] = @.ID)
END
'========================
it is just an example, so it is up to you

Image data type. Tricks and tips anyone?

Tools I want to use...

Sql2k/MSDE
ADO 2.x
VB6

Before I waste time experimenting, thought I'd ask the veterans:

I want to store Jpgs into a table, using the image datatype.

Will ADO auto-magically pick up that I'm streaming an image in when I try to do this...

Dim Pic as StdPicutre, RS as ADODB.Recordset
RS.open "select * from...."
set pic = Rs!MyImage

Or do I have to go through bizarre motions like this...

Dim Pic as StdPicutre, RS as ADODB.Recordset
Dim Blob() as byte

Blob = rs!MyImage

Open MyFilePath for Binary as #FileNum
Put #FileNum,,Blob
Close #FileNum

Set Pic = LoadPicture(MyFilePath)

Any thoughts?

(Please don't recommended linked controls, I hate those damn things with a passion.)

Thanks for reading!Agree about linked controls.
If you search you can find code related to inserting images.
Do you need to hold them in the database?
I usually hold them in files on the ado application server with the filename in the database - this saves the network traffic in saving and retrieving and gets round the transaction/trigger issues.|||I found some code, yes. All ways to acomplish this are easy, don't get me wrong...just looking for the simplest solution.

You seam to be implying that you do not recomend storing the actually image bits in the database itself...at all. The system you talk off is similar to what I'm doing now.

If it's not broken, don't fit it?

Is there anything to gain by placing these images into a table?

Thanks!|||Don't see what you would gain - you can't do a lot with them anyway.
If you do want to save them in a table then I would make this a separate database so that it doesn't compromise the rest of the data.
Would still prefer to leave them in files though.

Image data type to character string

I have a table with an image datatype field.

When I retrieve it it displays as a binary array. How do I convert that array back and forth to get the underlying text?

Post the SQL statement used in this regard, you need to use READTEXT statement, also http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp fyi..

If you are using any application to display that image column then refer to http://www.akadia.com/services/dotnet_load_blob.html link for more information.

Image Filepath in sql

Hi in my table i have a field which would store the file path of an image, the datatype is a varchar, and i wrote the following filepath in it but it didnt work, when i tried to call it up in the browser it didnt show, is the way i saved it below the right format?

C:\Millio\Documents\Project\Images\car.jpg

That's fine, but you will have to post some of your code so we can see why it might not be diplaying.

|||

Hi thanks for responding this is the part where i am calling the image from;

<ItemTemplate>

<span><spanstyle="font-size: 14pt">

<asp:ImageID="Image1"ImageUrl='<%# Eval("Images") %>'runat="server"/>

<br/>

|||

You should store the virtual path of your image in the database rather than the physical path.

|||

If you use a physical path like the one you are showing, you will need to use an HttpHandler to stream the image to the browser. The img tag takes a url value for the src attribute, so you could move the images to a folder within your web site, and reference them with a virtual path. Or if Project is already your virtual directory (the root folder for your web site), the path that you should store in the database is "images/car.jpg" or "~/images/car.jpg". Both will work, but the second one is ASP.NET specific, and can only be used when setting the ImageURL property of an asp server control. I would opt for the first one, just in case I decided to convert the site to PHP.

Or more realistically, decide to use the value to build up the src attribute of an <img> tag in code, and don't bother with a server control.

|||

Hi Mike the reason i did it that way is because i am using a user control to load up my pages, and i didnt want to have to make a page for each category, i will show you what i mean;

<%@.ControlLanguage="C#"AutoEventWireup="true"CodeFile="CategoryList.ascx.cs"Inherits="CategoryList" %>

<scriptrunat="server">

string _Category = "washing machines";

public string Category

{

get { return _Category; }

set { _Category = value; }

}

protected void DataList1_SelectedIndexChanged(object sender, EventArgs e)

{

Label ID = (Label)DataList1.SelectedItem.FindControl("categoryIDLabel");

// Label AD = (Label)DataList1.SelectedItem.FindControl("PriceLabel");

Label MD = (Label)DataList1.SelectedItem.FindControl("categoryNameLabel");

Trace.Write("category " + ID.Text);

//Trace.Write("Price " + AD.Text);

Trace.Write("categoryName " + MD.Text);

Session["scategoryID"] = ID.Text;

//Session["scategorydescription"] = AD.Text;

Session["scategoryName"] = MD.Text;

}

protected void Page_Load(object sender, EventArgs e)

{

SqlDataSource1.SelectParameters["categoryName"].DefaultValue = _Category;

}

</script>

<asp:DataListID="DataList1"runat="server"BackColor="White"BorderColor="Transparent"

BorderStyle="None"BorderWidth="1px"CellPadding="4"DataSourceID="SqlDataSource1"

ForeColor="Black"GridLines="Horizontal"OnSelectedIndexChanged="DataList1_SelectedIndexChanged">

<FooterStyleBackColor="#CCCC99"ForeColor="Black"/>

<SelectedItemStyleBackColor="White"Font-Bold="True"ForeColor="White"Font-Italic="False"Font-Overline="False"Font-Strikeout="False"Font-Underline="False"/>

<HeaderStyleBackColor="#333333"Font-Bold="True"ForeColor="White"/>

<SeparatorStyleBackColor="Black"BorderColor="Black"BorderStyle="Solid"BorderWidth="2px"/>

<AlternatingItemStyleBackColor="White"Font-Bold="False"Font-Italic="False"Font-Overline="False"

Font-Strikeout="False"Font-Underline="False"/>

<ItemTemplate>

<span><spanstyle="font-size: 14pt">

<asp:ImageID="Image1"ImageUrl='<%# Eval("categoryImage") %>'runat="server"/>

<br/>

<asp:LabelID="categoryNameLabel"runat="server"Text='<%# Eval("categoryName") %>'ForeColor="DimGray"Font-Size="11pt"Font-Names="Trebuchet MS"></asp:Label><br/>

<br/>

<spanstyle="color: black"></span>

<asp:LabelID="CategoryIDLabel"runat="server"Visible="False"Text='<%# Eval("categoryID") %>'ForeColor="DimGray"Font-Size="11pt"></asp:Label><br/>

<br/>

<asp:LabelID="categoryDescriptionLabel"runat="server"Visible="True"Text='<%# Eval("categoryDescription") %>'ForeColor="DimGray"Font-Size="11pt"></asp:Label><br/>

<br/>

<br/>

</ItemTemplate>

</asp:DataList>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:streamConnectionString %>"

SelectCommand="SELECT [categoryID], [categoryImage], [categoryName], [categoryDescription] FROM [Categories] WHERE ([categoryName] = @.categoryName)">

<SelectParameters>

<asp:ParameterName="categoryName"Type="string"/>

</SelectParameters>

</asp:SqlDataSource>

Image field in Trigger

Hi , i have a trigger that start on a record insert . . .This take de ID of the inserted record and start to fill other table in the database.In this triggher i have to update an image field of a table , getting the value from another table .I've casted this value in varbinary , but var binary can take only 8000 byte , and this fileis bigger than 8000 byte . . .How can i proced to solve the problem ?ThanksIf you are using SQL Server 2005 you can use the VARBINARY(MAX) type. if you are on a version prior to SQL Server 2k5 you can try to code your statement set based, eliminating the need for interim variables.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Hi , i'm using Sql Server 2000 . . . How can i eliminate the need for interim variables ?I've tryed to do this :UPDATA table1 SET img1 = (SELECT img2 FROM table2 where ...) where ...But the message is that i can use image field in trigger ...|||Hi,

did you have a look on:

http://msdn2.microsoft.com/en-us/library/ms189799.aspx

"In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is set to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.

If the compatibility level is 80 or higher, SQL Server allows for the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views. "

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql