Pros and cons of using image data type

Can anybody let me know the pros and cons of storing
images using SQL server image data type as opposed to just
storing the image file in system file directories. I'm
more concern on the efficiency and the space used if I
store images using SQL server 2000. Anything bad that
might occur?That's a sensible question to ask ... And many out there forget when they
use image datatypes ... BTW, Iam not a against image datatype but I always
prefer in storing them in the web server itself rather than passing them
around to and forth to the SQL Server. Moreover storing large files at the
sql server is also not advisable ... But if you are storing just a small
signature image for example .. It is fine to use SQL Server ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
www.extremeexperts.com
"twk" <twengkeat@.hotmail.com> wrote in message
news:01ce01c35d84$fce7c5e0$a001280a@.phx.gbl...
> Can anybody let me know the pros and cons of storing
> images using SQL server image data type as opposed to just
> storing the image file in system file directories. I'm
> more concern on the efficiency and the space used if I
> store images using SQL server 2000. Anything bad that
> might occur?|||The Pros of storing the images in SQL Server are pretty simple: You gain
all the "ACID" properties of a database system. The images are backed up
with the data in a coordinated fashion. All "pointers" between your
structured data and the images are maintained. No "broken link" problems,
no out of sync problems, no multiple namespaces, etc. If you store the data
in the database and the images in the file system then you have multiple
backups and various ways for the database and image information to end up
out of sync. Plus, you have to manage two different security environments.
The big negative of storing images in SQL Server is performance. There are
three issues here. The first is that SQL Server breaks images up into
chunks that fit on database pages. This makes reassembling the images
slower than if they are stored, without additional internal structure, in a
file. It also makes it impossible to use the operating system's built-in
facilities to transmit a file directly from disk out over a communications
link in kernel mode. So, from the standpoint of serving the image out onto
the web it is definitely much slower. Second, images stored in SQL Server
are returned to the application via the TDS protocol and the data access
APIs. Again, these are not optimal for image processing and impose overhead
that doesn't exist with a file. Third, most applications that process
images read and write them from the file system. So, if the image is stored
in SQL Server then you have to read the image out of the server, write it to
a temporary file, then invoke the image processing software against the
temporary file. It is these performance issues that lead many people to
store the images outside the database itself.
The real suitability of storing images inside SQL Server versus in the file
system comes down to the analysis of the application itself. For example,
if you have an HR application and one of the pieces of information that you
store about an employee is the picture that is on their id card then I think
you should store that image inside the database. Why? Well, you don't
access it very often. You aren't serving it up to the web constantly. It's
always accessed in conjunction with other employee data. You need to
protect access to the images under your HR policies. The performance hit is
thus not a significant factor when compared with the application and
operational issues.
If I had a server whose primary function was to serve up images all day,
then I'd store them in the file system. Or, at least maybe I would. I
actually have a preferred architecture for this scenario that addresses both
the management and performance issues. But it cost disk space. I would
store the authoritative copies of the images in the database, and then
create copies (a cache) in the file system. I would serve up the images
from the file system, but my recovery procedures would blast the
authoritative copies from the database into the filesystem. So I get the
best of both worlds. And with 120GB disks going for less money than I
usually carry around in my wallet, the duplication hardly seems to be a
problem for most situations.
One system that runs counter to conventional wisdom is the TerraServer
(http://terraserver-usa.com/). You can get details of how it works from
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-99-29.
Even though it serves up images all day long the images are stored in SQL
Server. This was done for a few reasons, chief among them to show that SQL
Server was capable of hosting such an application. Terraserver has been
operational since the summer of 1998, at times serving up several million
images per day.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"twk" <twengkeat@.hotmail.com> wrote in message
news:01ce01c35d84$fce7c5e0$a001280a@.phx.gbl...
> Can anybody let me know the pros and cons of storing
> images using SQL server image data type as opposed to just
> storing the image file in system file directories. I'm
> more concern on the efficiency and the space used if I
> store images using SQL server 2000. Anything bad that
> might occur?

Property Promotion with multi-level XML data type

How would you extract data from an XML datatype column when it has multiple levels? I've done this with single levels using CROSS APPLY and the nodes method, but can't seem to grab data from the intermediate levels.

Example:

<Level1>

<Level2>xyz</Level2>

<Level2>xyz</Level2>

<Level3>abc</Level3>

<Level3>abc</Level3>

<Level3>abc</Level3>

<Level2>xyz</Level2>

<Level2>xyz</Level2>

</Level1>

For the root level, I use the xml.value() function, and for the details I use the cross apply xml.nodes() function typically, but if I use the xpath in the xml.nodes, I have to use the path to the lowest level (level3) to grab those values, but can't seem to grab the changing values at the intermediate levels (level2).

Would this involve multiple cross-apply instances?

I don't understand your example, why are those Level3 elements indented further to the right than the Level2 elements? They are both children of the root element Level1.

And it is not obvious what you want to extract.

|||

Sorry if this wasn't clear. The XML is supposed to represent a multiple-level heirarchy and the indentation indicates a parent-child relationship.

So level 1 can be Order, for example. At this level there can be several elements that define the Order (ID, Cust #, etc). Level 2 would be Order Type, like Internal, External, Global, Domestic (each order always has these 4 types associated with it). Level 3 would be details relevant to that order detail item, like part #, quantity, etc. (each Order has 4 Order Types, of which have multiple line-items within each order type).

So I need to return a table of data based on this single order. The results returned would have these fields:

Order ID (repeated for every order detail)

Cust # (repeated for every order detail)

OrderType (repeated for every order type/order detail)

Part # (unique per order detail/type)

Qty (unique per order detail/type)

Analogous to joining three relational tables (Order details -> Order Type -> Order) and getting the combined results of all three.

In SQL xquery, I use the .value function to get the Order ID and Cust# because they are at the root level of the XML field. I use the CROSS APPLY .nodes method to get to the lowest level of detail (Part# and Qty) and this produces the correct data. But for the intermediate level (Order Type) I can't seem to get to it.

If it still isn't clear, I'll send some actual XML.

Thanks

Kory

|||

Consider posting the XML and the query you have, then we can work from there to improve it.

|||

A coworker of mine helped me to figure this out:

I was doing this:

Before

With Namespaces(....)
select
t.EventDateTime
,t.OrderId

,ref.value('(//gns:OrderType/text())[1]','varchar(max)') OrderType
,ref.value('(ns:Product/text())[1]','varchar(max)') Product
,ref.value('(ns:Qty/text())[1]','varchar(max)') Qty
FROM
Orders t
CROSS APPLY
xmlfld.nodes ('//ns:OrderDetailItem') AS R(ref)

And we changed it to this:

After

With Namespaces(....)
select
t.EventDateTime
,t.OrderId

,ref.value('(http://gns:OrderType/text())[1]','varchar(max)') OrderType

,ref.value('(//gns:OrderType/text())[1]','varchar(max)') OrderType
,ref.value('(ns:Product/text())[1]','varchar(max)') Product
,ref.value('(ns:Qty/text())[1]','varchar(max)') Qty
FROM
Orders t
CROSS APPLY
xmlfld.nodes ('//ns:OrderDetailItem') AS R(ref)

So the only change was to the xpath from //gnsrderType to http://gnsrderType

I really don't understand why this worked, but the first way just promoted the first occurance of the value, but the second method promoted the value when it changed.

-Kory

proper or title case function?

I know there are flaws with this type of function (lots of exceptions to
handle such as when "John Jones III" becomes "John Jones Iii" or "Detroit,
MI" becomes "Detroit, Mi") and some of you might yell at me for wanting to
do this type of formatting on the backend, but I was wondering if there is a
proper or title case type of function. Of course there's UPPER and LOWER but
I can't find a PROPER or TITLE. If you don't know what I mean, it's where
the first letter of every word is in upper case and the reset of the letters
are in lower case (MS Word calls it "Title Case"). Just wondering.
Thanks,
KeithKeith,
You're right this is probably better for the middle-tier or client however
see:
http://www.aspfaq.com/show.asp?id=2299
HTH
Jerry
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:e7TnenN2FHA.1100@.TK2MSFTNGP15.phx.gbl...
>I know there are flaws with this type of function (lots of exceptions to
> handle such as when "John Jones III" becomes "John Jones Iii" or "Detroit,
> MI" becomes "Detroit, Mi") and some of you might yell at me for wanting to
> do this type of formatting on the backend, but I was wondering if there is
> a
> proper or title case type of function. Of course there's UPPER and LOWER
> but
> I can't find a PROPER or TITLE. If you don't know what I mean, it's where
> the first letter of every word is in upper case and the reset of the
> letters
> are in lower case (MS Word calls it "Title Case"). Just wondering.
> Thanks,
> Keith
>

Proper name for average types

Is there a proper name for the type of Average which the AVG() function
returns, where NULLs are eliminated versus: an Average calculated by
dividing the SUM() of the column by the COUNT(*) of the table?
It seems as if the AVG() function by itself returns the same result as if
you assigned an 'average' value to the NULLs:
i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
It also seems as if dividing the SUM() of the column by the COUNT(*) of the
table gives the same result as if you treated NULLs as zero:
i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0)) would
return 10; SUM(column)/COUNT(*) would return 10 as well.
Anyway, just wondering if there was a proper name for these different
averages. Thanks.I do not think there is a proper name... The thing is that if you consider
NULLs you must give them some value, when you use sum/ Count(*) it act as
if the Null values are zero...
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
"Michael C#" <xyz@.abcdef.com> wrote in message
news:2Bfte.15144$l_2.5675@.fe09.lga...
> Is there a proper name for the type of Average which the AVG() function
> returns, where NULLs are eliminated versus: an Average calculated by
> dividing the SUM() of the column by the COUNT(*) of the table?
> It seems as if the AVG() function by itself returns the same result as if
> you assigned an 'average' value to the NULLs:
> i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
> would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
> It also seems as if dividing the SUM() of the column by the COUNT(*) of
> the table gives the same result as if you treated NULLs as zero:
> i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0))
> would return 10; SUM(column)/COUNT(*) would return 10 as well.
> Anyway, just wondering if there was a proper name for these different
> averages. Thanks.
>|||On Sun, 19 Jun 2005 10:48:23 -0400, Michael C# wrote:
>Is there a proper name for the type of Average which the AVG() function
>returns, where NULLs are eliminated versus: an Average calculated by
>dividing the SUM() of the column by the COUNT(*) of the table?
>It seems as if the AVG() function by itself returns the same result as if
>you assigned an 'average' value to the NULLs:
>i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
>would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
>It also seems as if dividing the SUM() of the column by the COUNT(*) of the
>table gives the same result as if you treated NULLs as zero:
>i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0)) would
>return 10; SUM(column)/COUNT(*) would return 10 as well.
>Anyway, just wondering if there was a proper name for these different
>averages. Thanks.
>
Hi Michael,
The proper name is "average".
>i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
>would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
I'd call this "average" or, if I really must be explicit, "average of
only the values that are not missing" (but since eliminating NULLS
before applying an aggregate is SOP in SQL, that's really just some
extra unnecessary redundancy).
>i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0)) would
>return 10; SUM(column)/COUNT(*) would return 10 as well.
I'd call this "average of the set after replacing missing values by the
arbitrarily chosen value of zero". Of course, the function itself is
still just called "average" - the rest actually described the COALESCE
expression that is used as argument for the AVG().
(Similarly, for AVG(A + B), I'd say "the average of the sum of A and B")
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The AVG function is simply the Arithmetic Mean but with the additional rule
that NULL values are ignored. If you prefer to be a bit more explicit about
that calculation then maybe you could add x IS NOT NULL to your WHERE
clause. If you want to include NULLs in the computation then you can
substitute some alternative expression inside the AVG funcion. For example:
AVG(COALESCE(x,0)).
--
David Portas
SQL Server MVP
--|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:jqnbb19pig8vfd8ev7pgtqj59gact2h2ou@.4ax.com...
> On Sun, 19 Jun 2005 10:48:23 -0400, Michael C# wrote:
>>Is there a proper name for the type of Average which the AVG() function
>>returns, where NULLs are eliminated versus: an Average calculated by
>>dividing the SUM() of the column by the COUNT(*) of the table?
>>It seems as if the AVG() function by itself returns the same result as if
>>you assigned an 'average' value to the NULLs:
>>i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
>>would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
>>It also seems as if dividing the SUM() of the column by the COUNT(*) of
>>the
>>table gives the same result as if you treated NULLs as zero:
>>i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0))
>>would
>>return 10; SUM(column)/COUNT(*) would return 10 as well.
>>Anyway, just wondering if there was a proper name for these different
>>averages. Thanks.
> Hi Michael,
> The proper name is "average".
>>i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
>>would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
> I'd call this "average" or, if I really must be explicit, "average of
> only the values that are not missing" (but since eliminating NULLS
> before applying an aggregate is SOP in SQL, that's really just some
> extra unnecessary redundancy).
>>i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0))
>>would
>>return 10; SUM(column)/COUNT(*) would return 10 as well.
> I'd call this "average of the set after replacing missing values by the
> arbitrarily chosen value of zero". Of course, the function itself is
> still just called "average" - the rest actually described the COALESCE
> expression that is used as argument for the AVG().
> (Similarly, for AVG(A + B), I'd say "the average of the sum of A and B")
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
It's all redundancy; that's why I was wondering if there was a more
'concise' way to say it.
I guess "10 words or less" is out of the question.|||Actually I was just wondering if there was a more concise way to say it.
Apparently "SQL AVG()" is about as concise as it gets; it seems there's no
concise term to describe how it arrives at an answer.
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:hL2dna5IZ4cocyjfRVn-2w@.giganews.com...
> The AVG function is simply the Arithmetic Mean but with the additional
> rule that NULL values are ignored. If you prefer to be a bit more explicit
> about that calculation then maybe you could add x IS NOT NULL to your
> WHERE clause. If you want to include NULLs in the computation then you can
> substitute some alternative expression inside the AVG funcion. For
> example: AVG(COALESCE(x,0)).
> --
> David Portas
> SQL Server MVP
> --
>

Proper name for average types

Is there a proper name for the type of Average which the AVG() function
returns, where NULLs are eliminated versus: an Average calculated by
dividing the SUM() of the column by the COUNT(*) of the table?
It seems as if the AVG() function by itself returns the same result as if
you assigned an 'average' value to the NULLs:
i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; y
ou
would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 2
0 }
It also seems as if dividing the SUM() of the column by the COUNT(*) of the
table gives the same result as if you treated NULLs as zero:
i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0))
would
return 10; SUM(column)/COUNT(*) would return 10 as well.
Anyway, just wondering if there was a proper name for these different
averages. Thanks.I do not think there is a proper name... The thing is that if you consider
NULLs you must give them some value, when you use sum/ Count(*) it act as
if the Null values are zero...
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
"Michael C#" <xyz@.abcdef.com> wrote in message
news:2Bfte.15144$l_2.5675@.fe09.lga...
> Is there a proper name for the type of Average which the AVG() function
> returns, where NULLs are eliminated versus: an Average calculated by
> dividing the SUM() of the column by the COUNT(*) of the table?
> It seems as if the AVG() function by itself returns the same result as if
> you assigned an 'average' value to the NULLs:
> i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5;
you
> would get the same result for the AVG() of the set { 5, 10, 15, 12.5,
20 }
> It also seems as if dividing the SUM() of the column by the COUNT(*) of
> the table gives the same result as if you treated NULLs as zero:
> i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0)
)
> would return 10; SUM(column)/COUNT(*) would return 10 as well.
> Anyway, just wondering if there was a proper name for these different
> averages. Thanks.
>|||On Sun, 19 Jun 2005 10:48:23 -0400, Michael C# wrote:

>Is there a proper name for the type of Average which the AVG() function
>returns, where NULLs are eliminated versus: an Average calculated by
>dividing the SUM() of the column by the COUNT(*) of the table?
>It seems as if the AVG() function by itself returns the same result as if
>you assigned an 'average' value to the NULLs:
>i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5;
you
>would get the same result for the AVG() of the set { 5, 10, 15, 12.5,
20 }
>It also seems as if dividing the SUM() of the column by the COUNT(*) of the
>table gives the same result as if you treated NULLs as zero:
>i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0))
would
>return 10; SUM(column)/COUNT(*) would return 10 as well.
>Anyway, just wondering if there was a proper name for these different
>averages. Thanks.
>
Hi Michael,
The proper name is "average".

>i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5;
you
>would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }[/vb
col]
I'd call this "average" or, if I really must be explicit, "average of
only the values that are not missing" (but since eliminating NULLS
before applying an aggregate is SOP in SQL, that's really just some
extra unnecessary redundancy).
[vbcol=seagreen]
>i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0))
would
>return 10; SUM(column)/COUNT(*) would return 10 as well.
I'd call this "average of the set after replacing missing values by the
arbitrarily chosen value of zero". Of course, the function itself is
still just called "average" - the rest actually described the COALESCE
expression that is used as argument for the AVG().
(Similarly, for AVG(A + B), I'd say "the average of the sum of A and B")
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The AVG function is simply the Arithmetic Mean but with the additional rule
that NULL values are ignored. If you prefer to be a bit more explicit about
that calculation then maybe you could add x IS NOT NULL to your WHERE
clause. If you want to include NULLs in the computation then you can
substitute some alternative expression inside the AVG funcion. For example:
AVG(COALESCE(x,0)).
David Portas
SQL Server MVP
--|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:jqnbb19pig8vfd8ev7pgtqj59gact2h2ou@.
4ax.com...
> On Sun, 19 Jun 2005 10:48:23 -0400, Michael C# wrote:
>
> Hi Michael,
> The proper name is "average".
>
> I'd call this "average" or, if I really must be explicit, "average of
> only the values that are not missing" (but since eliminating NULLS
> before applying an aggregate is SOP in SQL, that's really just some
> extra unnecessary redundancy).
>
> I'd call this "average of the set after replacing missing values by the
> arbitrarily chosen value of zero". Of course, the function itself is
> still just called "average" - the rest actually described the COALESCE
> expression that is used as argument for the AVG().
> (Similarly, for AVG(A + B), I'd say "the average of the sum of A and B")
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
It's all redundancy; that's why I was wondering if there was a more
'concise' way to say it.
I guess "10 words or less" is out of the question.|||Actually I was just wondering if there was a more concise way to say it.
Apparently "SQL AVG()" is about as concise as it gets; it seems there's no
concise term to describe how it arrives at an answer.
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:hL2dna5IZ4cocyjfRVn-2w@.giganews.com...
> The AVG function is simply the Arithmetic Mean but with the additional
> rule that NULL values are ignored. If you prefer to be a bit more explicit
> about that calculation then maybe you could add x IS NOT NULL to your
> WHERE clause. If you want to include NULLs in the computation then you can
> substitute some alternative expression inside the AVG funcion. For
> example: AVG(COALESCE(x,0)).
> --
> David Portas
> SQL Server MVP
> --
>

Proper name for average types

Is there a proper name for the type of Average which the AVG() function
returns, where NULLs are eliminated versus: an Average calculated by
dividing the SUM() of the column by the COUNT(*) of the table?
It seems as if the AVG() function by itself returns the same result as if
you assigned an 'average' value to the NULLs:
i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
It also seems as if dividing the SUM() of the column by the COUNT(*) of the
table gives the same result as if you treated NULLs as zero:
i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0)) would
return 10; SUM(column)/COUNT(*) would return 10 as well.
Anyway, just wondering if there was a proper name for these different
averages. Thanks.
I do not think there is a proper name... The thing is that if you consider
NULLs you must give them some value, when you use sum/ Count(*) it act as
if the Null values are zero...
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
"Michael C#" <xyz@.abcdef.com> wrote in message
news:2Bfte.15144$l_2.5675@.fe09.lga...
> Is there a proper name for the type of Average which the AVG() function
> returns, where NULLs are eliminated versus: an Average calculated by
> dividing the SUM() of the column by the COUNT(*) of the table?
> It seems as if the AVG() function by itself returns the same result as if
> you assigned an 'average' value to the NULLs:
> i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
> would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
> It also seems as if dividing the SUM() of the column by the COUNT(*) of
> the table gives the same result as if you treated NULLs as zero:
> i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0))
> would return 10; SUM(column)/COUNT(*) would return 10 as well.
> Anyway, just wondering if there was a proper name for these different
> averages. Thanks.
>
|||On Sun, 19 Jun 2005 10:48:23 -0400, Michael C# wrote:

>Is there a proper name for the type of Average which the AVG() function
>returns, where NULLs are eliminated versus: an Average calculated by
>dividing the SUM() of the column by the COUNT(*) of the table?
>It seems as if the AVG() function by itself returns the same result as if
>you assigned an 'average' value to the NULLs:
>i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
>would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
>It also seems as if dividing the SUM() of the column by the COUNT(*) of the
>table gives the same result as if you treated NULLs as zero:
>i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0)) would
>return 10; SUM(column)/COUNT(*) would return 10 as well.
>Anyway, just wondering if there was a proper name for these different
>averages. Thanks.
>
Hi Michael,
The proper name is "average".

>i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
>would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
I'd call this "average" or, if I really must be explicit, "average of
only the values that are not missing" (but since eliminating NULLS
before applying an aggregate is SOP in SQL, that's really just some
extra unnecessary redundancy).

>i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0)) would
>return 10; SUM(column)/COUNT(*) would return 10 as well.
I'd call this "average of the set after replacing missing values by the
arbitrarily chosen value of zero". Of course, the function itself is
still just called "average" - the rest actually described the COALESCE
expression that is used as argument for the AVG().
(Similarly, for AVG(A + B), I'd say "the average of the sum of A and B")
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||The AVG function is simply the Arithmetic Mean but with the additional rule
that NULL values are ignored. If you prefer to be a bit more explicit about
that calculation then maybe you could add x IS NOT NULL to your WHERE
clause. If you want to include NULLs in the computation then you can
substitute some alternative expression inside the AVG funcion. For example:
AVG(COALESCE(x,0)).
David Portas
SQL Server MVP
|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:jqnbb19pig8vfd8ev7pgtqj59gact2h2ou@.4ax.com...
> On Sun, 19 Jun 2005 10:48:23 -0400, Michael C# wrote:
>
> Hi Michael,
> The proper name is "average".
>
> I'd call this "average" or, if I really must be explicit, "average of
> only the values that are not missing" (but since eliminating NULLS
> before applying an aggregate is SOP in SQL, that's really just some
> extra unnecessary redundancy).
>
> I'd call this "average of the set after replacing missing values by the
> arbitrarily chosen value of zero". Of course, the function itself is
> still just called "average" - the rest actually described the COALESCE
> expression that is used as argument for the AVG().
> (Similarly, for AVG(A + B), I'd say "the average of the sum of A and B")
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
It's all redundancy; that's why I was wondering if there was a more
'concise' way to say it.
I guess "10 words or less" is out of the question.
|||Actually I was just wondering if there was a more concise way to say it.
Apparently "SQL AVG()" is about as concise as it gets; it seems there's no
concise term to describe how it arrives at an answer.
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:hL2dna5IZ4cocyjfRVn-2w@.giganews.com...
> The AVG function is simply the Arithmetic Mean but with the additional
> rule that NULL values are ignored. If you prefer to be a bit more explicit
> about that calculation then maybe you could add x IS NOT NULL to your
> WHERE clause. If you want to include NULLs in the computation then you can
> substitute some alternative expression inside the AVG funcion. For
> example: AVG(COALESCE(x,0)).
> --
> David Portas
> SQL Server MVP
> --
>

Promlem with loging in Managment Studio

hi

I'm going to use Enterprise edition

when I want to log in Managment Studio I face a window asking me for Server Name in addition to Server Type and authentication mode.

what should I write in Server Name section.I know the format is:

ComputerName\Instance Name

but what should I write in Instance Name?

thanks

Hello -

If you've installed SQL Server on the system with all the defaults, you don't have to type anything in the instance name. It's just the name of the server. If you installed SQL Server Express, it installs as a named instance by default. Usually it is named SQLExpress, so the name would be:

(your Server name)\SQLEXPRESS

To find out if the server is default or named, open a command prompt in Windows and type this:

NET START

Look for the service that starts with "SQL Server" in the name and to the right you'll see if it is a named instance or not. It will have the name of the instance in parenthesis, and if it is MSSQLSERVER it's default, and you don't have to type anything other than the server name. If it has something else there, it is named and that is the name you type.

More here: http://www.mssqltips.com/tip.asp?tip=1048

Note: If this answers your question, make sure you mark it "answered" so that others can find it!

Buck Woody
http://www.buckwoody.com

|||

You have to know what the instance name is; we can't tell you that. The best advice I can give you is to launch the SQL Server Configuration Manager (In your Start Menu->SQL Server 2005->Configuration Tools folder), browse to the Services tab, and locate any service that has a Service Type of "SQL Server", and then work from there.

If the service name is "SQL Server (MSSQLSERVER)" then you do not have an instance name (just use the computer name)

If the service name is "SQL Server (SQLEXPRESS)" then use ComputerName\SqlExpress

Check out my SQL Server 2005 video tutorials: http://www.learnsqlserver.com/

|||Where is it? I searched it and it does not come up in my search but when I go to microsoft .com they say it is already on the compuer

Promlem with loging in Managment Studio

hi

I'm going to use Enterprise edition

when I want to log in Managment Studio I face a window asking me for Server Name in addition to Server Type and authentication mode.

what should I write in Server Name section.I know the format is:

ComputerName\Instance Name

but what should I write in Instance Name?

thanks

Hello -

If you've installed SQL Server on the system with all the defaults, you don't have to type anything in the instance name. It's just the name of the server. If you installed SQL Server Express, it installs as a named instance by default. Usually it is named SQLExpress, so the name would be:

(your Server name)\SQLEXPRESS

To find out if the server is default or named, open a command prompt in Windows and type this:

NET START

Look for the service that starts with "SQL Server" in the name and to the right you'll see if it is a named instance or not. It will have the name of the instance in parenthesis, and if it is MSSQLSERVER it's default, and you don't have to type anything other than the server name. If it has something else there, it is named and that is the name you type.

More here: http://www.mssqltips.com/tip.asp?tip=1048

Note: If this answers your question, make sure you mark it "answered" so that others can find it!

Buck Woody
http://www.buckwoody.com

|||

You have to know what the instance name is; we can't tell you that. The best advice I can give you is to launch the SQL Server Configuration Manager (In your Start Menu->SQL Server 2005->Configuration Tools folder), browse to the Services tab, and locate any service that has a Service Type of "SQL Server", and then work from there.

If the service name is "SQL Server (MSSQLSERVER)" then you do not have an instance name (just use the computer name)

If the service name is "SQL Server (SQLEXPRESS)" then use ComputerName\SqlExpress

Check out my SQL Server 2005 video tutorials: http://www.learnsqlserver.com/

Project Type - Business Intelligence

I have sql server 2005 beta 2 and Visual Studio .Net 2003 installed.
I do not have the project type "Business Intelligence" in Visual Studio.
I have tried everything I could think of.
it is supposed to be there correct?On Sat, 30 Oct 2004 09:33:01 -0700, "Bryan Farrell"
<BryanFarrell@.discussions.microsoft.com> wrote:
>I have sql server 2005 beta 2 and Visual Studio .Net 2003 installed.
>I do not have the project type "Business Intelligence" in Visual Studio.
>I have tried everything I could think of.
>it is supposed to be there correct?
Bryan,
Do you have the beta 2 of SQL Server 2005 or of SQL Server 2005
Express?
If you have Express then the Business Intelligence Development Studio
is not included.
If you have the beta 2 of SQL Server 2005 then you should be able to
find the Business Intelligence Development Studio in the SQL Server
2005 folder under All Programs.
Andrew Watt
MVP - InfoPath|||i have beta 2 of SQL Server 2005.
i have the Business Intelligence Development Studio.
but what i am looking for is Visual Studio .Net 2003 - then New Project -
then Business Intelligence. thats what i do no have. the Business
Intelligence project type.
thx.
"Andrew Watt [MVP - InfoPath]" wrote:
> On Sat, 30 Oct 2004 09:33:01 -0700, "Bryan Farrell"
> <BryanFarrell@.discussions.microsoft.com> wrote:
> >I have sql server 2005 beta 2 and Visual Studio .Net 2003 installed.
> >I do not have the project type "Business Intelligence" in Visual Studio.
> >I have tried everything I could think of.
> >
> >it is supposed to be there correct?
> Bryan,
> Do you have the beta 2 of SQL Server 2005 or of SQL Server 2005
> Express?
> If you have Express then the Business Intelligence Development Studio
> is not included.
> If you have the beta 2 of SQL Server 2005 then you should be able to
> find the Business Intelligence Development Studio in the SQL Server
> 2005 folder under All Programs.
> Andrew Watt
> MVP - InfoPath
>

Programmatically loop over variables in the variable dispenser?

In my custom task, I would like to loop over the variables in the variable dispenser, and only modify those that are of a certain type. Is this possible?

Thanks!

I think you can use Me.variables(x).getType to get the type of each variable. If that is not correct, you can define variables name sXXX for string, iXXX for integer, etc. Then parse the variable name, get the first character and determine the variable type.|||

That would work, but I should clarify my question:

I have the VariableDispenser object passed into my Execute method. I do not know ahead of time what variables will be accessible via this instance of the VariableDispenser object. I would like to loop over each variable that is accessible (again, without knowing its name), then perform an operation on it conditionally based on its type (which I will get from the getType method).

Sorry for not being clearer in my original post!

|||I know that we can say variables(0).xxxx to read the first variable. But I am not sure whether we can get the count of variables. If you can't get the count, what you can do is read variables(0), variables(1), etc like that, and put that code in a try..catch. If you get an exception as 'index out of range', come out of the loop.|||This would be a great way to loop over all the variables assuming I could get access to the entire variables collection to begin with. The problem is that all I have is the VariableDispenser object. The VariableDispenser object methods that interact with the Variables collection (LockForRead, LockOneForRead, etc.) require that I know the name of the variables I would like to access. In my component, I do not know this upfront. Unless I am missing something (and I hope I am), I think I am out of luck?|||

Hi David,

I am not sure whether I understood your problem correctly or not. If I am confusing you, I am sorry. So, what you want is variable name and its type before calling variableDispenser object method. If that is true, it is pretty much simple.

DTS.Variables collection has 'count', 'Name' and 'DataType' properties. Loop thru this collection to get the name of a variable and its datatype. If the datatype is what you are looking for, call the VariableDispenser method using the name that you got.

Hope it will work.

|||

Thiru,

No worries. I am probably not being as clear as I could be - I always find it difficult to describe technical problems on the first 3 or 4 tries. Thanks for the help thus far.

I should have clarified from the beginning that I writing my custom task in C#. All of my code exists within the Execute method of my task. I do not appear to have the equivalent of the DTS.Variables collection available to me. Instead, all I have is the VariableDispenser object. Again, I hope I am missing something simple here.

Thanks again for your help,

David

|||

Can I ask why you want to this? How does the type identify the variables you want to modify? I ask in case there is some better way which we could suggest to acheive your business case.

Donald

|||

The answer is kind of convoluted, but here goes:

I have a package that manages the execution of child packages. Exactly what child packages are to be executed in a given run of the parent package are store in a database table. The child packages stored in the database table are looped over in a ForEach loop, and executed sequentially.

Each child package also requires a set of variables for successful execution. The list of variables required differs for each child package. The value for each of the required variables is not going to be known until run-time, immediately before the child package executes. The easiest way for me to manage this is to have a database table that lists what variables are necessary for each child package, and what expression / database query should be executed at run-time to evaluate each variable.

I have found a number of limitations (perceived or real) within the current SSIS achitecture that makes what I am trying to accomplish difficult. To get around these limitations, I have created my own set of objects to interact with the variables stored in the database, evaluate them at run-time, and put them into a single custom "variable list" object (to which an SSIS variable is assigned) that is available to the child packages.

The problem I have is that my parent package contains the variable(s) assigned to the list object(s). For maximum portability, I do not want my child packages to have to know what the names of those variables are. Instead, I would like to write a custom task that loops over all variables accessible via the variable dispenser, identifies those of my custom type, iterates through all the custom variables in the list, and assigns those values to corresponding ssis child variables (of the same name).

I know - that is quite a mouthful, and likely is not as clear as it could be. I may be going down a path that is unnecessary as I have only been playing around with SSIS for about 3 weeks now. Any help / advice is much appreciated.

Thanks,

David

Programatically setting the sub-report to load

My system uses a variety of invoice templates for each client, so 'Bank A' would have one invoice type sent to it, and 'Bank B' would have another etc. Each invoice template is a report, and they can vary quite substantially.

Each month the accounts team need to print out all the invoices for a month. I would like to create a report which has the invoices as a sub-report, so they can all be exported together and printed together.

However each sub-report could obviously be a different invoice-template. I notice that the sub-report selector field is one of the only fields that doesn't take an expression. Is there a way to programatically set which sub-report is used? The parameter sent to each report type is always the same (the primary key of the invoice to report on).

Any ideas? Or any other methods which would achieve the same goals?

Thanks,

Paul

One way is to add all subreport-types you could ever need to your report and hide all but the one you need dynamically via the hidden-expression..|||

Yes I could see that working, but it is pretty messy. As I would like the facility to just add extra invoice-template reports at any time. But with your method I'd have to also modify the main list report.

Thanks for your help anyway :)

Program type out of range - S1003

We have a Powerbuilder 4.0 application that used to run on SQL Server 7.0. Recently, we migrated our database to SQL Server 2000. We can do ODBC and can connect to the database from any tools such as Access, Excel and register the new SQL Server 2000 to other SQL Server, but when we try to use the client application, the user can not connect to the database. We receive the error message as the subject line. The user interface opens up but there is no data. The orginal program was written in Powerbuilder 4.0 by our vendor who probably does not have a copy of the source codes in proper state as I understand from talking to them. My question is, is there something that I can do to help my user connect to the database by using the client interface? This is getting a bit frustrating as no one really has an answer for it. I tried to install Powerbuilder 9.0 just to see if that updates any driver file that my machine has and that would be recongnize by the client programs, but it will not.

What can we do or what suggestions anyone can extend? Thanks for any inputs.What is the error message ? Look in the sql server log file as well as the event viewer for additional error messages.|||Originally posted by rnealejr
What is the error message ? Look in the sql server log file as well as the event viewer for additional error messages.

Actually, in the SQL Server log there is nothing since no connection was established from the client side.|||Originally posted by wellsound
Actually, in the SQL Server log there is nothing since no connection was established from the client side.

Well then it's not a sql server problem or issue...|||Yes I knew that, but since this is a Powerbuilder-client app with SQL Server backend, I have known that there are problems with the 16 bit ODBC driver that Powerbuilder used in their earlier version which is what the client app is built on, that is Powerbuilder Version 4.0 and that is exactly why I thought of asking in this forum to see if someone may have a related answer to that. But thanks for your reply.|||Is it using a dsn connection ? If so, what are the properties - and have you tried updating the driver and use that dsn ?|||I think I've seen this error before when a variable or column is unable toa accept a value submitted to it. Is there a procedure that runs when the user logs in, and are there any parameters passed to the procedure?

blindman|||Originally posted by rnealejr
Is it using a dsn connection ? If so, what are the properties - and have you tried updating the driver and use that dsn ? This is what we have in the configuration file
[<name of the sytem]
DBMS=SYB SQL Server v4.x
ServerName=, server name>
Database= <database name>
DbParm=appname='name of the app>',release='4.2'

We made a number of changes to this such as change the SYB SQL Server v4.x to DSN and on the DbParm we have used = dsn=<data source name> database = <database> name

None of these seem to work. The only thing we had was the client user interface opens up but no data comes up.