MS support confusion... Help!

I am lost... we are having problems with SQL server and the MS tech keeps
telling me to execute this query, but that is all they tell me... that it is
to help tune the database... but then this query never works because the
table is undeclared, and I am not sure how the exactly want it declared or
what to pass into it or what not... and then targetGroupID is undeclared too
and I don't know what to do there.. the support has been very confuseing...
could someone please help me and tell me whats going on?
INSERT INTO @.tbTmpEffectiveDeploymentAction (
UpdateID,
DeploymentID)
SELECT
UpdateID = U.UpdateID,
DeploymentID = dbo.fnGetEffectiveDeployment(AC.DeploymentID,
TG.DeploymentID, AC.ActionID, TG.ActionID)
FROM
dbo.tbUpdate AS U
LEFT OUTER JOIN dbo.tbDeployment AS TG ON TG.RevisionID =
dbo. fn_GetBestRevisionForEvaluateDeployment(
U.LocalUpdateID, targetGroupID)
AND TG.TargetGroupID = @.targetGroupID
LEFT OUTER JOIN dbo.tbDeployment AS AC ON AC.RevisionID =
dbo. fn_GetBestRevisionForEvaluateDeployment(
U.LocalUpdateID,
'A0A08746-4DBE-4a37-9ADF-9E7652C0B421') AND AC.TargetGroupID =
'A0A08746-4DBE-4a37-9ADF-9E7652C0B421'
WHERE dbo.fnGetEffectiveDeployment(AC.DeploymentID, TG.DeploymentID,
AC.ActionID, TG.ActionID) IS NOT NULL
they say it should return something like this, but not sure how to get it to
the point it returns something like this, and every time i ask support how
to, i get the same thing back "execute the insert query" which ive stated
many times now it doesnt work independently of itself with out somehting
declared... could someone please unconfuse me? thanks!
Rows Executes StmtText
StmtId NodeId Parent PhysicalOp LogicalOp
Argument
DefinedValues
EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList
Warnings Type Parallel EstimateExecutions
-- -- --
-- --
-- -- -- --
--
-- -- -- -- --
--
-- -- --
381 1 Table Insert(OBJECT:(@.tbTmpEffectiveDeployment
Action),
SET:(@.tbTmpEffectiveDeploymentAction.[DeploymentID]=[Expr1003],
@.tbTmpEffectiveDeploymentAction.[UpdateID]=[U].[UpdateID]))
0 0 Table Insert Insert
OBJECT:(@.tbTmpEffectiveDeploymentAction)
,
SET:(@.tbTmpEffectiveDeploymentAction.[DeploymentID]=[Expr1003],
@.tbTmpEffectiveDeploymentAction.[UpdateID]=[U].[UpdateID])
38070.5 0.0100355 0.0380705 15 50.3633
PLAN_ROW 0 1
381 1 |--Table Spool
0 1 0 Table Spool Eager Spool
38070.5 0.968432 0.0137056 27 50.3152
[U].[UpdateID], [Expr1003]
PLAN_ROW 0 1
381 1 |--Top(ROWCOUNT est 0)
0 2 1 Top Top
38070.5 0 0.00380705 27 49.3331
[U].[UpdateID], [Expr1003]
PLAN_ROW 0 1
381 1 |--Compute
Scalar(DEFINE:([Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
[TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID])))
0 3 2 Compute Scalar Compute Scalar
DEFINE:([Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
[TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID]))
[Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
[TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID]) 38070.5 0
0.00380705 27 49.3293 [U].[UpdateID], [Expr1003]
PLAN_ROW 0 1
381 1
|--Filter(WHERE:([dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
[TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID])<>NULL))
0 4 3 Filter Filter
WHERE:([dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
[TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID])<>NULL)
38070.5 0 0.0360352 43 49.3255
[U].[UpdateID], [TG].[DeploymentID], [TG].[ActionID], [AC].[DeploymentID],
[AC].[ActionID] PLAN_ROW 0 1
8210 1 |--Hash Match(Right Outer Join,
HASH:([AC].[RevisionID])=([Expr1006]),
RESIDUAL:([AC].[RevisionID]=[Expr1006]))
0 5 4 Hash Match Right Outer Join
HASH:([AC].[RevisionID])=([Expr1006]),
RESIDUAL:([AC].[RevisionID]=[Expr1006])
40949.1 0 0.329403 43 49.2894
[U].[UpdateID], [TG].[DeploymentID], [TG].[ActionID], [AC].[DeploymentID],
[AC].[ActionID] PLAN_ROW 0 1
7557 1 |--Clustered Index
Scan(OBJECT:([SUSDB].[dbo].[tbDeployment].[c0DeploymentRevision] AS [AC]),
WHERE:([AC].[TargetGroupID]=A0A08746-4DBE-4A37-9ADF-9E7652C0B421))
0 6 5 Clustered Index Scan Clustered Index Scan
OBJECT:([SUSDB].[dbo].[tbDeployment].[c0DeploymentRevision] AS [AC]),
WHERE:([AC].[TargetGroupID]=A0A08746-4DBE-4A37-9ADF-9E7652C0B421)
[AC].[TargetGroupID], [AC].[RevisionID], [AC].[DeploymentID],
[AC].[ActionID] 7564.53
0.635086 0.0289242 371 1.32802 [AC].[TargetGroupID],
[AC].[RevisionID], [AC].[DeploymentID], [AC].[ActionID]
PLAN_ROW 0 1
8202 1 |--Compute
Scalar(DEFINE:([Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateID],
A0A08746-4DBE-4A37-9ADF-9E7652C0B421)))
0 7 5 Compute Scalar Compute Scalar
DEFINE:([Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateID],
A0A08746-4DBE-4A37-9ADF-9E7652C0B421))
[Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateID],
A0A08746-4DBE-4A37-9ADF-9E7652C0B421) 17597 0 0.0017597
35 47.6068 [U].[UpdateID], [TG].[DeploymentID],
[TG].[ActionID], [Expr1006] PLAN_ROW
0 1
8202 1 |--Nested Loops(Left
Outer Join, OUTER REFERENCES:([U].[LocalUpdateID]) WITH PREFETCH)
0 8 7 Nested Loops Left Outer Join
OUTER REFERENCES:([U].[LocalUpdateID]) WITH PREFETCH
17597 0 0.0735556 408 47.605
[U].[LocalUpdateID], [U].[UpdateID], [TG].[DeploymentID], [TG].[ActionID]
PLAN_ROW 0 1
8202 1 |--Index
Scan(OBJECT:([SUSDB].[dbo].[tbUpdate].[UQ__tbUpdate__43D61337] AS [U]))
0 10 8 Index Scan Index Scan
OBJECT:([SUSDB].[dbo].[tbUpdate].[UQ__tbUpdate__43D61337] AS [U])
[U].[LocalUpdateID], [U].[UpdateID]
8202 0.0605415 0.0091007 45 0.0696422
[U].[LocalUpdateID], [U].[UpdateID]
PLAN_ROW 0 1
1812 8202
|--Filter(WHERE:([TG].[TargetGroupID]=[@.targetGroupID]))
0 11 8 Filter Filter
WHERE:([TG].[TargetGroupID]=[@.targetGroupID])
2.14546 0 2.45903E-0 371 47.4618
[TG].[DeploymentID], [TG].[ActionID]
PLAN_ROW 0 8202
50347 8202 |--Bookmark
Lookup(BOOKMARK:([Bmk1001]), OBJECT:([SUSDB].[dbo].[tbDeployment] AS [TG]))
0 12 11 Bookmark Lookup Bookmark Lookup
BOOKMARK:([Bmk1001]), OBJECT:([SUSDB].[dbo].[tbDeployment] AS [TG])
[TG].[TargetGroupID], [TG].[DeploymentID], [TG].[ActionID]
5.12297 0.0156249 5.63527E-0 371 47.4417
[TG].[TargetGroupID], [TG].[DeploymentID], [TG].[ActionID]
PLAN_ROW 0 8202
50347 8202
|--Index S(OBJECT:([SUSDB].[dbo].[tbDeployment].[nc1DeploymentRevision]
AS [TG]),
SEEK:([TG].[RevisionID]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateID],
[@.targetGroupID])) ORDERED FORWARD) 0 13 12 Index
S Index S
OBJECT:([SUSDB].[dbo].[tbDeployment].[nc1DeploymentRevision] AS [TG]),
SEEK:([TG].[RevisionID]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateID],
[@.targetGroupID])) ORDERED FORWARD [Bmk1001]
5.12297 0.00320348 8.41609E-0 42 17.6293 [Bmk1001]
PLAN_ROW 0 8202Let me guess...
"My name is Ahmed Akbar, but you may call me George"
Right? LOL
"Brian Henry" <brianiupmsdn@.newsgroups.nospam> wrote in message
news:%23a04ebbQFHA.3816@.TK2MSFTNGP10.phx.gbl...
> I am lost... we are having problems with SQL server and the MS tech keeps
> telling me to execute this query, but that is all they tell me... that it
is
> to help tune the database... but then this query never works because the
> table is undeclared, and I am not sure how the exactly want it declared or
> what to pass into it or what not... and then targetGroupID is undeclared
too
> and I don't know what to do there.. the support has been very
confuseing...
> could someone please help me and tell me whats going on?
> INSERT INTO @.tbTmpEffectiveDeploymentAction (
> UpdateID,
> DeploymentID)
> SELECT
> UpdateID = U.UpdateID,
> DeploymentID = dbo.fnGetEffectiveDeployment(AC.DeploymentID,
> TG.DeploymentID, AC.ActionID, TG.ActionID)
> FROM
> dbo.tbUpdate AS U
> LEFT OUTER JOIN dbo.tbDeployment AS TG ON TG.RevisionID =
> dbo. fn_GetBestRevisionForEvaluateDeployment(
U.LocalUpdateID,
targetGroupID)
> AND TG.TargetGroupID = @.targetGroupID
> LEFT OUTER JOIN dbo.tbDeployment AS AC ON AC.RevisionID =
> dbo. fn_GetBestRevisionForEvaluateDeployment(
U.LocalUpdateID,
> 'A0A08746-4DBE-4a37-9ADF-9E7652C0B421') AND AC.TargetGroupID =
> 'A0A08746-4DBE-4a37-9ADF-9E7652C0B421'
> WHERE dbo.fnGetEffectiveDeployment(AC.DeploymentID, TG.DeploymentID,
> AC.ActionID, TG.ActionID) IS NOT NULL
>
> they say it should return something like this, but not sure how to get it
to
> the point it returns something like this, and every time i ask support how
> to, i get the same thing back "execute the insert query" which ive stated
> many times now it doesnt work independently of itself with out somehting
> declared... could someone please unconfuse me? thanks!
>
> Rows Executes StmtText
> StmtId NodeId Parent PhysicalOp LogicalOp
> Argument
> DefinedValues
> EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList
> Warnings Type Parallel EstimateExecutions
> -- -- --
>
> -- --
> -- -- -- --
>
> --
> --
--
> -- -- -- -- --
> --
-
> -- -- --
> 381 1 Table
Insert(OBJECT:(@.tbTmpEffectiveDeployment
Action),
> SET:(@.tbTmpEffectiveDeploymentAction.[DeploymentID]=[Expr1003],
> @.tbTmpEffectiveDeploymentAction.[UpdateID]=[U].[UpdateID]))
> 0 0 Table Insert Insert
> OBJECT:(@.tbTmpEffectiveDeploymentAction)
,
> SET:(@.tbTmpEffectiveDeploymentAction.[DeploymentID]=[Expr1003],
> @.tbTmpEffectiveDeploymentAction.[UpdateID]=[U].[UpdateID])
> 38070.5 0.0100355 0.0380705 15 50.3633
> PLAN_ROW 0 1
> 381 1 |--Table Spool
> 0 1 0 Table Spool Eager Spool
> 38070.5 0.968432 0.0137056 27 50.3152
> [U].[UpdateID], [Expr1003]
> PLAN_ROW 0 1
> 381 1 |--Top(ROWCOUNT est 0)
> 0 2 1 Top Top
> 38070.5 0 0.00380705 27 49.3331
> [U].[UpdateID], [Expr1003]
> PLAN_ROW 0 1
> 381 1 |--Compute
>
Scalar(DEFINE:([Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentI
D],
> [TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID])))
> 0 3 2 Compute Scalar Compute Scalar
> DEFINE:([Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
> [TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID]))
> [Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
> [TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID]) 38070.5 0
> 0.00380705 27 49.3293 [U].[UpdateID], [Expr1003]
> PLAN_ROW 0 1
> 381 1
> |--Filter(WHERE:([dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
> [TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID])<>NULL))
> 0 4 3 Filter Filter
> WHERE:([dbo].[fnGetEffectiveDeployment]([AC].[DeploymentID],
> [TG].[DeploymentID], [AC].[ActionID], [TG].[ActionID])<>NULL)
> 38070.5 0 0.0360352 43 49.3255
> [U].[UpdateID], [TG].[DeploymentID], [TG].[ActionID], [AC].[DeploymentID],
> [AC].[ActionID] PLAN_ROW 0 1
> 8210 1 |--Hash Match(Right Outer
Join,
> HASH:([AC].[RevisionID])=([Expr1006]),
> RESIDUAL:([AC].[RevisionID]=[Expr1006]))
> 0 5 4 Hash Match Right Outer Join
> HASH:([AC].[RevisionID])=([Expr1006]),
> RESIDUAL:([AC].[RevisionID]=[Expr1006])
> 40949.1 0 0.329403 43 49.2894
> [U].[UpdateID], [TG].[DeploymentID], [TG].[ActionID], [AC].[DeploymentID],
> [AC].[ActionID] PLAN_ROW 0 1
> 7557 1 |--Clustered Index
> Scan(OBJECT:([SUSDB].[dbo].[tbDeployment].[c0DeploymentRevision] AS [AC]),
> WHERE:([AC].[TargetGroupID]=A0A08746-4DBE-4A37-9ADF-9E7652C0B421))
> 0 6 5 Clustered Index Scan Clustered Index Scan
> OBJECT:([SUSDB].[dbo].[tbDeployment].[c0DeploymentRevision] AS [AC]),
> WHERE:([AC].[TargetGroupID]=A0A08746-4DBE-4A37-9ADF-9E7652C0B421)
> [AC].[TargetGroupID], [AC].[RevisionID], [AC].[DeploymentID],
> [AC].[ActionID] 7564.53
> 0.635086 0.0289242 371 1.32802 [AC].[TargetGroupID],
> [AC].[RevisionID], [AC].[DeploymentID], [AC].[ActionID]
> PLAN_ROW 0 1
> 8202 1 |--Compute
>
Scalar(DEFINE:([Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U
].[LocalUpdateID],
> A0A08746-4DBE-4A37-9ADF-9E7652C0B421)))
> 0 7 5 Compute Scalar Compute Scalar
>
DEFINE:([Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[Loca
lUpdateID],
> A0A08746-4DBE-4A37-9ADF-9E7652C0B421))
>
[Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateI
D],
> A0A08746-4DBE-4A37-9ADF-9E7652C0B421) 17597 0 0.0017597
> 35 47.6068 [U].[UpdateID], [TG].[DeploymentID],
> [TG].[ActionID], [Expr1006] PLAN_ROW
> 0 1
> 8202 1 |--Nested Loops(Left
> Outer Join, OUTER REFERENCES:([U].[LocalUpdateID]) WITH PREFETCH)
> 0 8 7 Nested Loops Left Outer Join
> OUTER REFERENCES:([U].[LocalUpdateID]) WITH PREFETCH
> 17597 0 0.0735556 408 47.605
> [U].[LocalUpdateID], [U].[UpdateID], [TG].[DeploymentID], [TG].[ActionID]
> PLAN_ROW 0 1
> 8202 1 |--Index
> Scan(OBJECT:([SUSDB].[dbo].[tbUpdate].[UQ__tbUpdate__43D61337] AS [U]))
> 0 10 8 Index Scan Index Scan
> OBJECT:([SUSDB].[dbo].[tbUpdate].[UQ__tbUpdate__43D61337] AS [U])
> [U].[LocalUpdateID], [U].[UpdateID]
> 8202 0.0605415 0.0091007 45 0.0696422
> [U].[LocalUpdateID], [U].[UpdateID]
> PLAN_ROW 0 1
> 1812 8202
> |--Filter(WHERE:([TG].[TargetGroupID]=[@.targetGroupID]))
> 0 11 8 Filter Filter
> WHERE:([TG].[TargetGroupID]=[@.targetGroupID])
> 2.14546 0 2.45903E-0 371 47.4618
> [TG].[DeploymentID], [TG].[ActionID]
> PLAN_ROW 0 8202
> 50347 8202
|--Bookmark
> Lookup(BOOKMARK:([Bmk1001]), OBJECT:([SUSDB].[dbo].[tbDeployment] AS
[TG]))
> 0 12 11 Bookmark Lookup Bookmark Lookup
> BOOKMARK:([Bmk1001]), OBJECT:([SUSDB].[dbo].[tbDeployment] AS [TG])
> [TG].[TargetGroupID], [TG].[DeploymentID], [TG].[ActionID]
> 5.12297 0.0156249 5.63527E-0 371 47.4417
> [TG].[TargetGroupID], [TG].[DeploymentID], [TG].[ActionID]
> PLAN_ROW 0 8202
> 50347 8202
> |--Index S(OBJECT:([SUSDB].[dbo].[tbDeployment].[nc1DeploymentRevision]
> AS [TG]),
>
SEEK:([TG].[RevisionID]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].
[LocalUpdateID],
> [@.targetGroupID])) ORDERED FORWARD) 0 13 12 Index
> S Index S
> OBJECT:([SUSDB].[dbo].[tbDeployment].[nc1DeploymentRevision] AS [TG]),
>
SEEK:([TG].[RevisionID]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].
[LocalUpdateID],
> [@.targetGroupID])) ORDERED FORWARD [Bmk1001]
> 5.12297 0.00320348 8.41609E-0 42 17.6293 [Bmk1001]
> PLAN_ROW 0 8202
>|||something like that...
"Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
news:%237PCTjbQFHA.580@.TK2MSFTNGP15.phx.gbl...
> Let me guess...
> "My name is Ahmed Akbar, but you may call me George"
> Right? LOL
> "Brian Henry" <brianiupmsdn@.newsgroups.nospam> wrote in message
> news:%23a04ebbQFHA.3816@.TK2MSFTNGP10.phx.gbl...
> is
> too
> confuseing...
> targetGroupID)
> to
> --
> -
> Insert(OBJECT:(@.tbTmpEffectiveDeployment
Action),
> Scalar(DEFINE:([Expr1003]=[dbo].[fnGetEffectiveDeployment]([AC].[DeploymentI
> D],
> Join,
> Scalar(DEFINE:([Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U
> ].[LocalUpdateID],
> DEFINE:([Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[Loca
> lUpdateID],
> [Expr1006]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].[LocalUpdateI
> D],
> |--Bookmark
> [TG]))
> SEEK:([TG].[RevisionID]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].
> [LocalUpdateID],
> SEEK:([TG].[RevisionID]=[dbo]. [fn_GetBestRevisionForEvaluateDeployment
]([U].
> [LocalUpdateID],
>

MS SubQuery Changes??

Has anyone seen where subqueries collapse into a sum? I have code like the following, which has been running fine for over a year:
UPDATE Reports..DataStats
SET Vendors_Cnt = (SELECT COUNT(*) FROM vVendors__AllRecords),
Vendors_Audit_Cnt = (SELECT COUNT(*) FROM vVendors_InvAudit),
Vendors_Rpts_Cnt = (SELECT COUNT(*) FROM vVendors_Inv12mo),
Vendors_InvUnused = (SELECT COUNT(*) FROM vVendors_InvUnused),
Vendors_InvOne = (SELECT COUNT(*) FROM vVendors_InvOne),
Vendors_InvMulti = (SELECT COUNT(*) FROM vVendors_InvMulti),
Vendors_InvUnpaid = (SELECT COUNT(*) FROM vVendors_InvUnpaid),
Vendors_InvNewer = (SELECT COUNT(*) FROM vVendors_InvNewer),
Vendors_Inv12mo = (SELECT COUNT(*) FROM vVendors_Inv12mo),
Vendors_InvPrior = (SELECT COUNT(*) FROM vVendors_InvPrior),
Vendors_InvSkipYear = (SELECT COUNT(*) FROM vVendors_InvSkipYear),
Vendors_Known = (SELECT COUNT(*) FROM vVendors_Known),
Vendors_Orphaned = (SELECT COUNT(*) FROM vVendors_Orphaned),
Vendors_Active = (SELECT COUNT(*) FROM vVendors_Active),
Vendors_Inactive = (SELECT COUNT(*) FROM vVendors_Inactive),
Vendors_Excluded = (SELECT COUNT(*) FROM vVendors_Excluded)
WHERE (AuditName = @.AuditName)
But now it is generating overflows...and is not equivalent to (ignoring the obvious UPDATE vs. return differences for illustration):
SELECT COUNT(*) FROM vVendors__AllRecords
SELECT COUNT(*) FROM vVendors_InvAudit
SELECT COUNT(*) FROM vVendors_Inv12mo
SELECT COUNT(*) FROM vVendors_InvUnused
SELECT COUNT(*) FROM vVendors_InvOne
SELECT COUNT(*) FROM vVendors_InvMulti
SELECT COUNT(*) FROM vVendors_InvUnpaid
SELECT COUNT(*) FROM vVendors_InvNewer
SELECT COUNT(*) FROM vVendors_Inv12mo
SELECT COUNT(*) FROM vVendors_InvPrior
SELECT COUNT(*) FROM vVendors_InvSkipYear
SELECT COUNT(*) FROM vVendors_Known
SELECT COUNT(*) FROM vVendors_Orphaned
SELECT COUNT(*) FROM vVendors_Active
SELECT COUNT(*) FROM vVendors_Inactive
SELECT COUNT(*) FROM vVendors_Excluded
This appears to have started around the beginning of May. Anyone else suffer after patches?

I am not sure but all SQL Server aggregate functions ignore NULLs except COUNT(*) so it maybe an unknown which is ANSI NULL is creating the overflow. Try the link below for dealing with SQL Server NULLs. Hope this helps.
http://www.akadia.com/services/dealing_with_null_values.html|||How high do those counts go? What are the datatypes for thosecolumns storing the counts? Have you exceeded the integer limitof 2,147,483,647?|||The failed SUM appears to be 44 million. The correct values should each be under 100 thousand. Really wierd!|||Try SELECT SUM instead of COUNT(*) see if it makes a difference. Hope this helps.|||

RLyda wrote:

The failed SUM appears to be 44 million. Thecorrect values should each be under 100 thousand. Reallywierd!

What is the data type for, say, the Vendors_Audit_Cntcolumn?sql

MS SQLServer: Left/Right Join doesn't work when using aggregate functions

Hi.
I'm using MS SQLServer and I have noticed that when I use aggregate
functions on queries that use outer joins, the results varies depending
on the syntax that I use. If I use the syntax:
from table1 left join table2 on table1.a = table2.a
it doesn't seem to work properly, but if I use the syntax:
from table1, table2 where table1.a *= table2.a
it seems to work fine.
I would like to know if there's something that I'm doing wrong, if this
is some bug for which there is some solution, or if just the way it is.
In order to be more clear, I will show you some simple code that reflect
that.
Suppose we have the following 2 tables whith the corresponding data:
create table p1 ( id int primary key, s_name char(40) )
insert p1 values (1, 'Rick')
insert p1 values (2, 'Mary')
insert p1 values (3, 'Peter')
create table p2 (id int, region int, sales money, primary key (id,
region))
insert p2 values (1, 1, 2000)
insert p2 values (1, 2, 1000)
insert p2 values (1, 3, 1500)
insert p2 values (3, 1, 750)
insert p2 values (3, 2, 1000)
If I use a query without aggregate functions, both syntax return the
same result:
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
select p1.s_name, p2.sales
from p1, p2
where p1.id *= p2.id
Both of them return:
s_namesales
Rick2000.00
Rick1000.00
Rick1500.00
MaryNull
Peter750.00
Peter1000.00
But if I use Sum(), the results are different:
select p1.s_name, sum(p2.sales) as sales
from p1 left outer join p2 on p1.id = p2.id
group by p1.s_name
s_namesales
Rick4500.00
Peter1750.00
select p1.s_name, sum(p2.sales) as sales
from p1, p2
where p1.id *= p2.id
group by p1.s_name
s_namesales
Rick4500.00
MaryNull
Peter1750.00
In my opinion, the correct result is the last one, but the syntax that
Microsoft recommends to use is the other one.
Can someone explain that to me?
Thanks in advance.
Donald
*** Sent via Developersdex http://www.codecomments.com ***
You can get the result you want with:
SELECT s_name, SUM(sales) as sales FROM (
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
) a
group by s_name
-- or
SELECT p1.s_name, a.sales
FROM p1
LEFT OUTER JOIN (
SELECT id, SUM(sales) AS sales
FROM p2
GROUP BY id) a
ON p1.id = a.id
-- or the simplest (but somewhat worse performing on large sets), GROUP BY
ALL:
select p1.s_name, SUM(p2.sales) AS sales
from p1 left outer join p2 on p1.id = p2.id
GROUP BY ALL p1.s_name
Joe Celko give a rundown of the disadvantages of *= here:
http://groups.google.co.uk/groups?hl...phx.gbl&rnum=7
Jacco Schalkwijk
SQL Server MVP
"Donald" <don382w@.hotmail.com> wrote in message
news:%23$ZyTiKSFHA.2132@.TK2MSFTNGP14.phx.gbl...
> Hi.
> I'm using MS SQLServer and I have noticed that when I use aggregate
> functions on queries that use outer joins, the results varies depending
> on the syntax that I use. If I use the syntax:
> from table1 left join table2 on table1.a = table2.a
> it doesn't seem to work properly, but if I use the syntax:
> from table1, table2 where table1.a *= table2.a
> it seems to work fine.
> I would like to know if there's something that I'm doing wrong, if this
> is some bug for which there is some solution, or if just the way it is.
> In order to be more clear, I will show you some simple code that reflect
> that.
> Suppose we have the following 2 tables whith the corresponding data:
> create table p1 ( id int primary key, s_name char(40) )
> insert p1 values (1, 'Rick')
> insert p1 values (2, 'Mary')
> insert p1 values (3, 'Peter')
> create table p2 (id int, region int, sales money, primary key (id,
> region))
> insert p2 values (1, 1, 2000)
> insert p2 values (1, 2, 1000)
> insert p2 values (1, 3, 1500)
> insert p2 values (3, 1, 750)
> insert p2 values (3, 2, 1000)
> If I use a query without aggregate functions, both syntax return the
> same result:
> select p1.s_name, p2.sales
> from p1 left outer join p2 on p1.id = p2.id
> select p1.s_name, p2.sales
> from p1, p2
> where p1.id *= p2.id
> Both of them return:
> s_name sales
> -- --
> Rick 2000.00
> Rick 1000.00
> Rick 1500.00
> Mary Null
> Peter 750.00
> Peter 1000.00
> But if I use Sum(), the results are different:
> select p1.s_name, sum(p2.sales) as sales
> from p1 left outer join p2 on p1.id = p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Peter 1750.00
> select p1.s_name, sum(p2.sales) as sales
> from p1, p2
> where p1.id *= p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Mary Null
> Peter 1750.00
> In my opinion, the correct result is the last one, but the syntax that
> Microsoft recommends to use is the other one.
> Can someone explain that to me?
> Thanks in advance.
> Donald
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Yes, I am running SQL-Server 2000. I will check which service pack, if
any, is installed. I already downloaded the service pack 3a to install.
Thanks for everything,
Donald
*** Sent via Developersdex http://www.codecomments.com ***

MS SQLServer: Left/Right Join doesn't work when using aggregate functions

Hi.
I'm using MS SQLServer and I have noticed that when I use aggregate
functions on queries that use outer joins, the results varies depending
on the syntax that I use. If I use the syntax:
from table1 left join table2 on table1.a = table2.a
it doesn't seem to work properly, but if I use the syntax:
from table1, table2 where table1.a *= table2.a
it seems to work fine.
I would like to know if there's something that I'm doing wrong, if this
is some bug for which there is some solution, or if just the way it is.
In order to be more clear, I will show you some simple code that reflect
that.
Suppose we have the following 2 tables whith the corresponding data:
create table p1 ( id int primary key, s_name char(40) )
insert p1 values (1, 'Rick')
insert p1 values (2, 'Mary')
insert p1 values (3, 'Peter')
create table p2 (id int, region int, sales money, primary key (id,
region))
insert p2 values (1, 1, 2000)
insert p2 values (1, 2, 1000)
insert p2 values (1, 3, 1500)
insert p2 values (3, 1, 750)
insert p2 values (3, 2, 1000)
If I use a query without aggregate functions, both syntax return the
same result:
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
select p1.s_name, p2.sales
from p1, p2
where p1.id *= p2.id
Both of them return:
s_name sales
-- --
Rick 2000.00
Rick 1000.00
Rick 1500.00
Mary Null
Peter 750.00
Peter 1000.00
But if I use Sum(), the results are different:
select p1.s_name, sum(p2.sales) as sales
from p1 left outer join p2 on p1.id = p2.id
group by p1.s_name
s_name sales
-- --
Rick 4500.00
Peter 1750.00
select p1.s_name, sum(p2.sales) as sales
from p1, p2
where p1.id *= p2.id
group by p1.s_name
s_name sales
-- --
Rick 4500.00
Mary Null
Peter 1750.00
In my opinion, the correct result is the last one, but the syntax that
Microsoft recommends to use is the other one.
Can someone explain that to me?
Thanks in advance.
Donald
*** Sent via Developersdex http://www.developersdex.com ***You can get the result you want with:
SELECT s_name, SUM(sales) as sales FROM (
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
) a
group by s_name
-- or
SELECT p1.s_name, a.sales
FROM p1
LEFT OUTER JOIN (
SELECT id, SUM(sales) AS sales
FROM p2
GROUP BY id) a
ON p1.id = a.id
-- or the simplest (but somewhat worse performing on large sets), GROUP BY
ALL:
select p1.s_name, SUM(p2.sales) AS sales
from p1 left outer join p2 on p1.id = p2.id
GROUP BY ALL p1.s_name
Joe Celko give a rundown of the disadvantages of *= here:
http://groups.google.co.uk/groups?hl=en&lr=&selm=%23Uogl7zREHA.628%40TK2MSFTNGP11.phx.gbl&rnum=7
--
Jacco Schalkwijk
SQL Server MVP
"Donald" <don382w@.hotmail.com> wrote in message
news:%23$ZyTiKSFHA.2132@.TK2MSFTNGP14.phx.gbl...
> Hi.
> I'm using MS SQLServer and I have noticed that when I use aggregate
> functions on queries that use outer joins, the results varies depending
> on the syntax that I use. If I use the syntax:
> from table1 left join table2 on table1.a = table2.a
> it doesn't seem to work properly, but if I use the syntax:
> from table1, table2 where table1.a *= table2.a
> it seems to work fine.
> I would like to know if there's something that I'm doing wrong, if this
> is some bug for which there is some solution, or if just the way it is.
> In order to be more clear, I will show you some simple code that reflect
> that.
> Suppose we have the following 2 tables whith the corresponding data:
> create table p1 ( id int primary key, s_name char(40) )
> insert p1 values (1, 'Rick')
> insert p1 values (2, 'Mary')
> insert p1 values (3, 'Peter')
> create table p2 (id int, region int, sales money, primary key (id,
> region))
> insert p2 values (1, 1, 2000)
> insert p2 values (1, 2, 1000)
> insert p2 values (1, 3, 1500)
> insert p2 values (3, 1, 750)
> insert p2 values (3, 2, 1000)
> If I use a query without aggregate functions, both syntax return the
> same result:
> select p1.s_name, p2.sales
> from p1 left outer join p2 on p1.id = p2.id
> select p1.s_name, p2.sales
> from p1, p2
> where p1.id *= p2.id
> Both of them return:
> s_name sales
> -- --
> Rick 2000.00
> Rick 1000.00
> Rick 1500.00
> Mary Null
> Peter 750.00
> Peter 1000.00
> But if I use Sum(), the results are different:
> select p1.s_name, sum(p2.sales) as sales
> from p1 left outer join p2 on p1.id = p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Peter 1750.00
> select p1.s_name, sum(p2.sales) as sales
> from p1, p2
> where p1.id *= p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Mary Null
> Peter 1750.00
> In my opinion, the correct result is the last one, but the syntax that
> Microsoft recommends to use is the other one.
> Can someone explain that to me?
> Thanks in advance.
> Donald
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Donald,
Are you running SQL-Server 2000? Make sure you have the latest service
pack installed (SP3a).
If I run the query
select p1.s_name, sum(p2.sales) as sales
from p1 left outer join p2 on p1.id = p2.id
group by p1.s_name
it correctly returns
s_name sales
--- --
Mary NULL
Peter 1750.0000
Rick 4500.0000
(3 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET
operation.
HTH,
Gert-Jan
Donald wrote:
> Hi.
> I'm using MS SQLServer and I have noticed that when I use aggregate
> functions on queries that use outer joins, the results varies depending
> on the syntax that I use. If I use the syntax:
> from table1 left join table2 on table1.a = table2.a
> it doesn't seem to work properly, but if I use the syntax:
> from table1, table2 where table1.a *= table2.a
> it seems to work fine.
> I would like to know if there's something that I'm doing wrong, if this
> is some bug for which there is some solution, or if just the way it is.
> In order to be more clear, I will show you some simple code that reflect
> that.
> Suppose we have the following 2 tables whith the corresponding data:
> create table p1 ( id int primary key, s_name char(40) )
> insert p1 values (1, 'Rick')
> insert p1 values (2, 'Mary')
> insert p1 values (3, 'Peter')
> create table p2 (id int, region int, sales money, primary key (id,
> region))
> insert p2 values (1, 1, 2000)
> insert p2 values (1, 2, 1000)
> insert p2 values (1, 3, 1500)
> insert p2 values (3, 1, 750)
> insert p2 values (3, 2, 1000)
> If I use a query without aggregate functions, both syntax return the
> same result:
> select p1.s_name, p2.sales
> from p1 left outer join p2 on p1.id = p2.id
> select p1.s_name, p2.sales
> from p1, p2
> where p1.id *= p2.id
> Both of them return:
> s_name sales
> -- --
> Rick 2000.00
> Rick 1000.00
> Rick 1500.00
> Mary Null
> Peter 750.00
> Peter 1000.00
> But if I use Sum(), the results are different:
> select p1.s_name, sum(p2.sales) as sales
> from p1 left outer join p2 on p1.id = p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Peter 1750.00
> select p1.s_name, sum(p2.sales) as sales
> from p1, p2
> where p1.id *= p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Mary Null
> Peter 1750.00
> In my opinion, the correct result is the last one, but the syntax that
> Microsoft recommends to use is the other one.
> Can someone explain that to me?
> Thanks in advance.
> Donald
> *** Sent via Developersdex http://www.developersdex.com ***

MS SQLServer: Left/Right Join doesn't work when using aggregate functions

Hi.
I'm using MS SQLServer and I have noticed that when I use aggregate
functions on queries that use outer joins, the results varies depending
on the syntax that I use. If I use the syntax:
from table1 left join table2 on table1.a = table2.a
it doesn't seem to work properly, but if I use the syntax:
from table1, table2 where table1.a *= table2.a
it seems to work fine.
I would like to know if there's something that I'm doing wrong, if this
is some bug for which there is some solution, or if just the way it is.
In order to be more clear, I will show you some simple code that reflect
that.
Suppose we have the following 2 tables whith the corresponding data:
create table p1 ( id int primary key, s_name char(40) )
insert p1 values (1, 'Rick')
insert p1 values (2, 'Mary')
insert p1 values (3, 'Peter')
create table p2 (id int, region int, sales money, primary key (id,
region))
insert p2 values (1, 1, 2000)
insert p2 values (1, 2, 1000)
insert p2 values (1, 3, 1500)
insert p2 values (3, 1, 750)
insert p2 values (3, 2, 1000)
If I use a query without aggregate functions, both syntax return the
same result:
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
select p1.s_name, p2.sales
from p1, p2
where p1.id *= p2.id
Both of them return:
s_name sales
-- --
Rick 2000.00
Rick 1000.00
Rick 1500.00
Mary Null
Peter 750.00
Peter 1000.00
But if I use Sum(), the results are different:
select p1.s_name, sum(p2.sales) as sales
from p1 left outer join p2 on p1.id = p2.id
group by p1.s_name
s_name sales
-- --
Rick 4500.00
Peter 1750.00
select p1.s_name, sum(p2.sales) as sales
from p1, p2
where p1.id *= p2.id
group by p1.s_name
s_name sales
-- --
Rick 4500.00
Mary Null
Peter 1750.00
In my opinion, the correct result is the last one, but the syntax that
Microsoft recommends to use is the other one.
Can someone explain that to me?
Thanks in advance.
Donald
*** Sent via Developersdex http://www.codecomments.com ***You can get the result you want with:
SELECT s_name, SUM(sales) as sales FROM (
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
) a
group by s_name
-- or
SELECT p1.s_name, a.sales
FROM p1
LEFT OUTER JOIN (
SELECT id, SUM(sales) AS sales
FROM p2
GROUP BY id) a
ON p1.id = a.id
-- or the simplest (but somewhat worse performing on large sets), GROUP BY
ALL:
select p1.s_name, SUM(p2.sales) AS sales
from p1 left outer join p2 on p1.id = p2.id
GROUP BY ALL p1.s_name
Joe Celko give a rundown of the disadvantages of *= here:
phx.gbl&rnum=7" target="_blank">http://groups.google.co.uk/groups?h...
phx.gbl&rnum=7
Jacco Schalkwijk
SQL Server MVP
"Donald" <don382w@.hotmail.com> wrote in message
news:%23$ZyTiKSFHA.2132@.TK2MSFTNGP14.phx.gbl...
> Hi.
> I'm using MS SQLServer and I have noticed that when I use aggregate
> functions on queries that use outer joins, the results varies depending
> on the syntax that I use. If I use the syntax:
> from table1 left join table2 on table1.a = table2.a
> it doesn't seem to work properly, but if I use the syntax:
> from table1, table2 where table1.a *= table2.a
> it seems to work fine.
> I would like to know if there's something that I'm doing wrong, if this
> is some bug for which there is some solution, or if just the way it is.
> In order to be more clear, I will show you some simple code that reflect
> that.
> Suppose we have the following 2 tables whith the corresponding data:
> create table p1 ( id int primary key, s_name char(40) )
> insert p1 values (1, 'Rick')
> insert p1 values (2, 'Mary')
> insert p1 values (3, 'Peter')
> create table p2 (id int, region int, sales money, primary key (id,
> region))
> insert p2 values (1, 1, 2000)
> insert p2 values (1, 2, 1000)
> insert p2 values (1, 3, 1500)
> insert p2 values (3, 1, 750)
> insert p2 values (3, 2, 1000)
> If I use a query without aggregate functions, both syntax return the
> same result:
> select p1.s_name, p2.sales
> from p1 left outer join p2 on p1.id = p2.id
> select p1.s_name, p2.sales
> from p1, p2
> where p1.id *= p2.id
> Both of them return:
> s_name sales
> -- --
> Rick 2000.00
> Rick 1000.00
> Rick 1500.00
> Mary Null
> Peter 750.00
> Peter 1000.00
> But if I use Sum(), the results are different:
> select p1.s_name, sum(p2.sales) as sales
> from p1 left outer join p2 on p1.id = p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Peter 1750.00
> select p1.s_name, sum(p2.sales) as sales
> from p1, p2
> where p1.id *= p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Mary Null
> Peter 1750.00
> In my opinion, the correct result is the last one, but the syntax that
> Microsoft recommends to use is the other one.
> Can someone explain that to me?
> Thanks in advance.
> Donald
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Yes, I am running SQL-Server 2000. I will check which service pack, if
any, is installed. I already downloaded the service pack 3a to install.
Thanks for everything,
Donald
*** Sent via Developersdex http://www.codecomments.com ***

MS SQLServer or Pervasive SQL 2000i

Hi All,

Thanks in advance for the help.

We are considering a new Project. This project is going to be totally
web based.

Currently my company is using Pervasive SQL 2000i for there ERP needs.
Will the existing Pervasive SQL 2000i itself serve the purpose OR
should I consider moving to MS SQL Server.

Do assist me in terms of development also i.e. Is it easier to code
with Pervasive SQL 2000i or with MS SQL Server.

Regards
SamsonHi All,

Is there anybody who could help me with the below query.

Regards,
Samson
samsonesau@.gmail.com wrote:

Quote:

Originally Posted by

Hi All,
>
Thanks in advance for the help.
>
>
We are considering a new Project. This project is going to be totally
web based.
>
>
Currently my company is using Pervasive SQL 2000i for there ERP needs.
Will the existing Pervasive SQL 2000i itself serve the purpose OR
should I consider moving to MS SQL Server.
>
Do assist me in terms of development also i.e. Is it easier to code
with Pervasive SQL 2000i or with MS SQL Server.
>
>
Regards
Samson

MS SQL-Server Express licensing

Hi,
SQL Server is free to use (even in your case) :-)
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
I know you meant "SQL Server Express".
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168981745.677916.258230@.v45g2000cwv.googlegr oups.com...
> Hi,
> SQL Server is free to use (even in your case) :-)
> HTH, jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
|||Sure, I meant SQL Server Express for sure.
sql