MS Service Pack frustrations: 2012

Friday, March 30, 2012

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_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

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

MS Sqlserver 2005: Displaying only dbs user has a right to?

Hi;

My company just installed MS SQLServer 2005 ( see below the dotted
line ).

When a user logs into management studio all of the databases on the
server are displayed in the right hand column.

What can we do to have only the databases the user has rights to,
display?

Thanks much in advance for any info.

Steve

--------------------
Microsoft SQL Server Management Studio9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML2.6 3.0 4.0 6.0
Microsoft Internet Explorer7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System5.1.2600Steve (tinker123@.gmail.com) writes:

Quote:

Originally Posted by

My company just installed MS SQLServer 2005 ( see below the dotted
line ).
>
When a user logs into management studio all of the databases on the
server are displayed in the right hand column.
>
What can we do to have only the databases the user has rights to,
display?

What you can do is to revoke the permission VIEW ANY DATABASE from public,
or DENY this permission to the users in question. Alas, the user would then only see tempdb and msdb (or was it master?), databases he owns, and
possibly his current database. He would have to issue a USE database in
the dark to get there.

This is not an uncommon question, so I filed a suggestion for an improvement
in SQL Server. You can vote on it at
https://connect.microsoft.com/SQLSe...edbackID=273830
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

MS sqljdbc.jar, v1.1, and Sql 2005 SP 2

Are there any compatibiltiy isses with these two products?

TIA,

Barkingdog

The Microsoft SQL Server 2005 JDBC v1.1 driver supports SQL Server 2005 SP2. It also supports SQL Server 2000.

BTW, as you are trying out v1.1, please know that the public beta of the v1.2 version is also available for download at http://msdn.microsoft.com/data/jdbc. We've made significant improvements in various areas of the driver and would love to hear feedback on how we can continue to improve the driver.

Jimmy

|||

Great Answer......

But I have one more quesiton. In our "drivers" directory we have the sqljdbc.jar file. BUT I can't determine its version. I think it is 1.1 but could be 1.0. How can one tell the version of this jar file?

TIA,

Barkingdog

Ms SQL7 linking problem

i have tried linking all my aspx scripts with ms access, they all worked,now i have switched on to ms sql7...iam not familier with the codes used for sql...my main problem is the data source syntax...in case of access i had given the file address along with extension...eg: C:\Data\Nortwind.mdb...so what should i do in case of sql... i have also given my code below

sub Page_load(sender as object, E as eventargs)

dim objconnection As sqlconnection
dim objcommand as sqlCommand
dim objadapter as sqlDataAdapter
dim objdata As DataSet
dim strsearch as String
dim sqlquery as String

strsearch = tb1.text

if Len(trim(strSearch))>0 then

objConnection = new sqlconnection("Data Source=(localhost);")

sqlquery= "select * from mayank where (name"&strsearch.ToString()&")"

objcommand = new sqlcommand(sqlquery, objconnection)

objadapter = new sqlDataAdapter(objcommand)

objdata = new DataSet()
objadapter.Fill(objdata)

DataGrid1.DataSource = objdata

DataGrid1.DataBind()

objConnection.close

Else
tb1.text= "enter your search here"
end if
end sub

Checkwww.connectionstrings.com

Regards

MS SQL7 (Desktop) error

Hi,
I am getting error 1802, CREATE DATABASE failed. Some file
names listed could not be created. Check previous errors.
I have Windows 2000 professional installed on my PC.
I am installing remedy 5.1 that uses MSSQL server 7 and I
am getting the error above on remedy instalation...
Any idea'
ThanksI suggest you talk to the vendor of remedy. SQL Server returned more error
messages and you really need those error messages to see what the problem
is.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Taiser" <anonymous@.discussions.microsoft.com> wrote in message
news:1b8c01c3ff4d$64d17880$a601280a@.phx.gbl...
> Hi,
> I am getting error 1802, CREATE DATABASE failed. Some file
> names listed could not be created. Check previous errors.
>
> I have Windows 2000 professional installed on my PC.
> I am installing remedy 5.1 that uses MSSQL server 7 and I
> am getting the error above on remedy instalation...
> Any idea'
> Thanks

MS SQL7 (Desktop) error

Hi,
I am getting error 1802, CREATE DATABASE failed. Some file
names listed could not be created. Check previous errors.
I have Windows 2000 professional installed on my PC.
I am installing remedy 5.1 that uses MSSQL server 7 and I
am getting the error above on remedy instalation...
Any idea'
ThanksI suggest you talk to the vendor of remedy. SQL Server returned more error
messages and you really need those error messages to see what the problem
is.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Taiser" <anonymous@.discussions.microsoft.com> wrote in message
news:1b8c01c3ff4d$64d17880$a601280a@.phx.gbl...
> Hi,
> I am getting error 1802, CREATE DATABASE failed. Some file
> names listed could not be created. Check previous errors.
>
> I have Windows 2000 professional installed on my PC.
> I am installing remedy 5.1 that uses MSSQL server 7 and I
> am getting the error above on remedy instalation...
> Any idea'
> Thankssql

MS SQL2005 Updata from SP1 to SP2 Fail

Windows 2003 SP2 Updata MS SQL 2005 from SP1 to SP2 fail.

Hotfix_5.log

07/31/2007 23:15:32.745 ================================================================================
{..........................................

.........................................

........................................}
07/31/2007 23:15:54.236
07/31/2007 23:15:54.246 Product Enumeration Results:
07/31/2007 23:15:54.246 INF File Name: c:\ace3346e16b7688715bb0ff855\HotFixSqlxml4.inf
07/31/2007 23:15:54.246 baselinebuild = 1399
07/31/2007 23:15:54.246 build = 3042
07/31/2007 23:15:54.246 description = SQLXML4
07/31/2007 23:15:54.256 details = Service Pack for Microsoft SQLXML 4.0.
07/31/2007 23:15:54.256 installerlogpath = <PROGRAMFILESDIR>\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix
07/31/2007 23:15:54.256 kbarticle = KB921896
07/31/2007 23:15:54.256 kbarticlehyperlink = http://support.microsoft.com/?kbid=921896
07/31/2007 23:15:54.266 lcid = 1033
07/31/2007 23:15:54.266 legalproductname = SQLXML4
07/31/2007 23:15:54.266 machinetype = x86
07/31/2007 23:15:54.266 package = HotFixSqlxml4
07/31/2007 23:15:54.266 packagetype = Hotfix
07/31/2007 23:15:54.276 productcode = {A188FCCF-E929-494D-B1F1-4313E02ACD52}
07/31/2007 23:15:54.276 productname = Redist9
07/31/2007 23:15:54.276 recommendinstall = 1
07/31/2007 23:15:54.276 relatedlinks = <LCIDSUPPORTDIR>\FinalSQL2005Information.rtf
07/31/2007 23:15:54.276 servicepackname = Microsoft SQL Server 2005 Service Pack 2 Setup
07/31/2007 23:15:54.286 splevel = 2
07/31/2007 23:15:54.286 sqladminprovisioningtool = <PROGRAMFILESDIR_WOW>\Microsoft SQL Server\90\Shared\sqlprov.exe
07/31/2007 23:15:54.286 supportdir = c:\ace3346e16b7688715bb0ff855
07/31/2007 23:15:54.286 upgradecode = {D9CA3D82-6F1B-41A7-8141-B90ACA8F865B}
07/31/2007 23:15:54.286 version = 9
07/31/2007 23:15:54.286
07/31/2007 23:15:54.296 File Group Details: MSI
07/31/2007 23:15:54.296 cachedmsipath = <PROGRAMFILESDIR>\Microsoft SQL Server\90\Setup Bootstrap\Cache
07/31/2007 23:15:54.296 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
07/31/2007 23:15:54.296 File Details: sqlxml4.msi
07/31/2007 23:15:54.296
07/31/2007 23:15:54.296 Instance Details: SQLXML4
07/31/2007 23:15:54.306 fullversion = 9.00.2047.00
07/31/2007 23:15:54.306 lcid = 1033
07/31/2007 23:15:54.306 productcode = {A188FCCF-E929-494D-B1F1-4313E02ACD52}
07/31/2007 23:15:54.306 qfelevel = 2047
07/31/2007 23:15:54.306 sp = -1
07/31/2007 23:15:54.316
07/31/2007 23:15:54.316 Product Enumeration Results:
07/31/2007 23:15:54.316 INF File Name: c:\ace3346e16b7688715bb0ff855\HotFixSqlxml4_x64.inf
07/31/2007 23:15:54.316 baselinebuild = 1399
07/31/2007 23:15:54.316 build = 3042
07/31/2007 23:15:54.316 description = SQLXML4
07/31/2007 23:15:54.326 details = Service Pack for Microsoft SQLXML 4.0.
07/31/2007 23:15:54.326 installerlogpath = <PROGRAMFILESDIR>\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix
07/31/2007 23:15:54.326 kbarticle = KB921896
07/31/2007 23:15:54.326 kbarticlehyperlink = http://support.microsoft.com/?kbid=921896
07/31/2007 23:15:54.326 lcid = 1033
07/31/2007 23:15:54.336 legalproductname = SQLXML4 (64-bit)
07/31/2007 23:15:54.336 machinetype = x64
07/31/2007 23:15:54.336 package = HotFixSqlxml4_x64
07/31/2007 23:15:54.336 packagetype = Hotfix
07/31/2007 23:15:54.336 productname = Redist9
07/31/2007 23:15:54.346 recommendinstall = 1
07/31/2007 23:15:54.346 relatedlinks = <LCIDSUPPORTDIR>\FinalSQL2005Information.rtf
07/31/2007 23:15:54.346 servicepackname = Microsoft SQL Server 2005 Service Pack 2 Setup
07/31/2007 23:15:54.346 splevel = 2
07/31/2007 23:15:54.346 supportdir = c:\ace3346e16b7688715bb0ff855
07/31/2007 23:15:54.356 upgradecode = {F457D8E6-7686-437D-9B17-E21D45CCABD8}
07/31/2007 23:15:54.356 version = 9
07/31/2007 23:15:54.356
07/31/2007 23:15:54.356 File Group Details: MSI
07/31/2007 23:15:54.356 cachedmsipath = <PROGRAMFILESDIR>\Microsoft SQL Server\90\Setup Bootstrap\Cache
07/31/2007 23:15:54.356 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
07/31/2007 23:15:54.366 File Details: sqlxml4_x64.msi
07/31/2007 23:15:54.366
07/31/2007 23:15:54.366 Product Enumeration Results:
07/31/2007 23:15:54.366 INF File Name: c:\ace3346e16b7688715bb0ff855\HotFixSQLServer2005_BC.inf
07/31/2007 23:15:54.366 baselinebuild = 1399
07/31/2007 23:15:54.376 build = 2004
07/31/2007 23:15:54.376 description = Backward Compatibility
07/31/2007 23:15:54.376 details = Service Pack for the Backward Compatibility components, including Data Transformation Services Runtime and SQL-DMO.
07/31/2007 23:15:54.376 installerlogpath = <PROGRAMFILESDIR>\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix
07/31/2007 23:15:54.376 kbarticle = KB921896
07/31/2007 23:15:54.386 kbarticlehyperlink = http://support.microsoft.com/?kbid=921896
07/31/2007 23:15:54.386 lcid = 1033
07/31/2007 23:15:54.386 legalproductname = Microsoft SQL Server 2005 Backward Compatibility
07/31/2007 23:15:54.386 machinetype = x86
07/31/2007 23:15:54.386 package = HotFixSQLServer2005_BC
07/31/2007 23:15:54.396 packagetype = Hotfix
07/31/2007 23:15:54.396 productcode = {2243F21A-E132-44F7-BA13-024D0845C815}
07/31/2007 23:15:54.396 productname = Redist9
07/31/2007 23:15:54.406 recommendinstall = 1
07/31/2007 23:15:54.406 relatedlinks = <LCIDSUPPORTDIR>\FinalSQL2005Information.rtf
07/31/2007 23:15:54.406 servicepackname = Microsoft SQL Server 2005 Service Pack 2 Setup
07/31/2007 23:15:54.406 splevel = 2
07/31/2007 23:15:54.406 sqladminprovisioningtool = <PROGRAMFILESDIR_WOW>\Microsoft SQL Server\90\Shared\sqlprov.exe
07/31/2007 23:15:54.406 supportdir = c:\ace3346e16b7688715bb0ff855
07/31/2007 23:15:54.416 upgradecode = {1E70C6C9-E1B7-4A74-BC8C-8EB5D010CEC9}
07/31/2007 23:15:54.416 version = 9
07/31/2007 23:15:54.416
07/31/2007 23:15:54.416 File Group Details: MSI
07/31/2007 23:15:54.416 cachedmsipath = <PROGRAMFILESDIR>\Microsoft SQL Server\90\Setup Bootstrap\Cache
07/31/2007 23:15:54.416 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
07/31/2007 23:15:54.426 File Details: SQLServer2005_BC.msi
07/31/2007 23:15:54.426
07/31/2007 23:15:54.426 Instance Details: Backward Compatibility
07/31/2007 23:15:54.426 fullversion = 8.05.1704
07/31/2007 23:15:54.436 lcid = 1033
07/31/2007 23:15:54.436 productcode = {2243F21A-E132-44F7-BA13-024D0845C815}
07/31/2007 23:15:54.436 qfelevel = 1704
07/31/2007 23:15:54.436 sp = -1
07/31/2007 23:15:54.436
07/31/2007 23:15:54.446 Product Enumeration Results:
07/31/2007 23:15:54.446 INF File Name: c:\ace3346e16b7688715bb0ff855\HotFixSQLServer2005_BC_x64.inf
07/31/2007 23:15:54.446 baselinebuild = 1399
07/31/2007 23:15:54.446 build = 2004
07/31/2007 23:15:54.446 description = Backward Compatibility
07/31/2007 23:15:54.446 details = Service Pack for the Backward Compatibility components, including Data Transformation Services Runtime and SQL-DMO.
07/31/2007 23:15:54.457 installerlogpath = <PROGRAMFILESDIR>\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix
07/31/2007 23:15:54.457 kbarticle = KB921896
07/31/2007 23:15:54.457 kbarticlehyperlink = http://support.microsoft.com/?kbid=921896
07/31/2007 23:15:54.457 lcid = 1033
07/31/2007 23:15:54.457 legalproductname = Microsoft SQL Server 2005 Backward Compatibility (64-bit)
07/31/2007 23:15:54.467 machinetype = x64
07/31/2007 23:15:54.467 package = HotFixSQLServer2005_BC_x64
07/31/2007 23:15:54.467 packagetype = Hotfix
07/31/2007 23:15:54.467 productname = Redist9
07/31/2007 23:15:54.467 recommendinstall = 1
07/31/2007 23:15:54.467 relatedlinks = <LCIDSUPPORTDIR>\FinalSQL2005Information.rtf
07/31/2007 23:15:54.477 servicepackname = Microsoft SQL Server 2005 Service Pack 2 Setup
07/31/2007 23:15:54.477 splevel = 2
07/31/2007 23:15:54.477 supportdir = c:\ace3346e16b7688715bb0ff855
07/31/2007 23:15:54.477 upgradecode = {7B6BF434-3C72-4DB3-8049-FEF31AEAFF9A}
07/31/2007 23:15:54.477 version = 9
07/31/2007 23:15:54.487
07/31/2007 23:15:54.487 File Group Details: MSI
07/31/2007 23:15:54.487 cachedmsipath = <PROGRAMFILESDIR>\Microsoft SQL Server\90\Setup Bootstrap\Cache
07/31/2007 23:15:54.487 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
07/31/2007 23:15:54.487 File Details: SQLServer2005_BC_x64.msi
07/31/2007 23:15:54.497
07/31/2007 23:15:54.497 Product Enumeration Results:
07/31/2007 23:15:54.497 INF File Name: c:\ace3346e16b7688715bb0ff855\HotFixSqlWriter.inf
07/31/2007 23:15:54.497 baselinebuild = 1399
07/31/2007 23:15:54.497 build = 3042
07/31/2007 23:15:54.497 description = Microsoft SQL Server VSS Writer
07/31/2007 23:15:54.507 details = Service Pack for Microsoft SQL Server VSS Writer.
07/31/2007 23:15:54.507 installerlogpath = <PROGRAMFILESDIR>\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix
07/31/2007 23:15:54.507 kbarticle = KB921896
07/31/2007 23:15:54.507 kbarticlehyperlink = http://support.microsoft.com/?kbid=921896
07/31/2007 23:15:54.517 lcid = 1033
07/31/2007 23:15:54.517 legalproductname = Microsoft SQL Server VSS Writer
07/31/2007 23:15:54.517 machinetype = x86
07/31/2007 23:15:54.517 package = HotFixSqlWriter
07/31/2007 23:15:54.517 packagetype = Hotfix
07/31/2007 23:15:54.527 productcode = {C0D2F614-5CE5-4DCB-8678-E5C9AF7044F8}
07/31/2007 23:15:54.527 productname = Redist9
07/31/2007 23:15:54.527 recommendinstall = 1
07/31/2007 23:15:54.527 relatedlinks = <LCIDSUPPORTDIR>\FinalSQL2005Information.rtf
07/31/2007 23:15:54.527 servicepackname = Microsoft SQL Server 2005 Service Pack 2 Setup
07/31/2007 23:15:54.527 splevel = 2
07/31/2007 23:15:54.527 sqladminprovisioningtool = <PROGRAMFILESDIR_WOW>\Microsoft SQL Server\90\Shared\sqlprov.exe
07/31/2007 23:15:54.537 supportdir = c:\ace3346e16b7688715bb0ff855
07/31/2007 23:15:54.537 upgradecode = {65D8E1DF-6201-4B53-A0F9-E654F8E80F97}
07/31/2007 23:15:54.537 version = 9
07/31/2007 23:15:54.537
07/31/2007 23:15:54.537 File Group Details: MSI
07/31/2007 23:15:54.547 cachedmsipath = <PROGRAMFILESDIR>\Microsoft SQL Server\90\Setup Bootstrap\Cache
07/31/2007 23:15:54.547 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
07/31/2007 23:15:54.547 File Details: SqlWriter.msi
07/31/2007 23:15:54.547
07/31/2007 23:15:54.547 Instance Details: Microsoft SQL Server VSS Writer
07/31/2007 23:15:54.547 fullversion = 9.00.2047.00
07/31/2007 23:15:54.557 lcid = 1033
07/31/2007 23:15:54.557 productcode = {C0D2F614-5CE5-4DCB-8678-E5C9AF7044F8}
07/31/2007 23:15:54.557 qfelevel = 2047
07/31/2007 23:15:54.557 sp = -1
07/31/2007 23:15:54.567
07/31/2007 23:15:54.567 Product Enumeration Results:
07/31/2007 23:15:54.567 INF File Name: c:\ace3346e16b7688715bb0ff855\HotFixSqlWriter_x64.inf
07/31/2007 23:15:54.567 baselinebuild = 1399
07/31/2007 23:15:54.567 build = 3042
07/31/2007 23:15:54.567 description = Microsoft SQL Server VSS Writer
07/31/2007 23:15:54.577 details = Service Pack for Microsoft SQL Server VSS Writer.
07/31/2007 23:15:54.577 installerlogpath = <PROGRAMFILESDIR>\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix
07/31/2007 23:15:54.577 kbarticle = KB921896
07/31/2007 23:15:54.577 kbarticlehyperlink = http://support.microsoft.com/?kbid=921896
07/31/2007 23:15:54.577 lcid = 1033
07/31/2007 23:15:54.577 legalproductname = Microsoft SQL Server VSS Writer (64-bit)
07/31/2007 23:15:54.587 machinetype = x64
07/31/2007 23:15:54.587 package = HotFixSqlWriter_x64
07/31/2007 23:15:54.587 packagetype = Hotfix
07/31/2007 23:15:54.587 productname = Redist9
07/31/2007 23:15:54.587 recommendinstall = 1
07/31/2007 23:15:54.587 relatedlinks = <LCIDSUPPORTDIR>\FinalSQL2005Information.rtf
07/31/2007 23:15:54.597 servicepackname = Microsoft SQL Server 2005 Service Pack 2 Setup
07/31/2007 23:15:54.597 splevel = 2
07/31/2007 23:15:54.597 supportdir = c:\ace3346e16b7688715bb0ff855
07/31/2007 23:15:54.597 upgradecode = {E9031696-DD39-4C25-BAEB-425FF28279EA}
07/31/2007 23:15:54.597 version = 9
07/31/2007 23:15:54.607
07/31/2007 23:15:54.607 File Group Details: MSI
07/31/2007 23:15:54.607 cachedmsipath = <PROGRAMFILESDIR>\Microsoft SQL Server\90\Setup Bootstrap\Cache
07/31/2007 23:15:54.607 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
07/31/2007 23:15:54.607 File Details: SqlWriter_x64.msi
07/31/2007 23:15:54.607
07/31/2007 23:16:19.753 Registry: Opened registry key "System\CurrentControlSet\Control\Session Manager"
07/31/2007 23:16:19.783 Registry: Cannot read registry key value "PendingFileRenameOperations"
07/31/2007 23:16:19.813 Registry: Read registry key value "EnableErrorReporting", DWORD value = 1
07/31/2007 23:16:19.813 Registry: Read registry key value "CustomerFeedBack", DWORD value = 1
07/31/2007 23:16:20.714 Registry: Set registry key value "EnableErrorReporting", DWORD value = 1
07/31/2007 23:16:20.714 Registry: Set registry key value "CustomerFeedBack", DWORD value = 1
07/31/2007 23:16:20.774 Locked file: Checking for locked files
07/31/2007 23:16:45.530 Attempting to pause the 32 bit ngen queue
07/31/2007 23:16:46.371 Installing product: SQLTools9
07/31/2007 23:16:46.381 Installing instance: SQL Tools
07/31/2007 23:16:46.411 Installing target: MSTS0000
07/31/2007 23:16:46.411 Installing file: sqlrun_tools.msp
07/31/2007 23:16:46.451 Copy Engine: Creating MSP install log file at: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQLTools9_Hotfix_KB921896_sqlrun_tools.msp.log
07/31/2007 23:16:46.451 Registry: Opened registry key "Software\Policies\Microsoft\Windows\Installer"
07/31/2007 23:16:46.451 Registry: Cannot read registry key value "Debug"

Somebody help me in this issue..Please....

|||

did you check this,

Registry: Cannot read registry key value "PendingFileRenameOperations"
Registry: Cannot read registry key value "Debug"

dunno if this is the reason....

MS SQL2005 reporting Services + AD. Security question

Hi we run completely Windows shop. So Windows Authentication for Reporting Services is only acceptable solution. Idea is that I assign user in RS manager but they get their security check through AD using Cerberus or whatever it is. We did this setup for intranet web site which consist three machine

PC#1 Domain controller (AD)

PC#2 IIS (intranet web site)

PC#3 MS SQL server. (data)

People logon on web server newer asked for credentials on pull up data from SQL server though IIS being themselves, impersonation.

Now I have setup

PC#1 SSRS, IIS for Reports Server, MS SQL 2k5 for RS metadata etc

PC#2 Domain controller (AD)

PC#3 MS SQL2k (data)

I did same setspn procedure but when I tried to log on Report server it asked me for credentials. Moreover when I provide my logon and password it denies it, accepting only admin credentials.

I went trough few books, number article but nobody describe this scenario in spite it should be most popular solution if network based on Windows.

Any help is really appreciated

I am a way out of my league here, but I don't think it is the Report Server who prompts for credentials but IIS. If you create a web app in IIS configured for Windows authentiction and drop a simple html file in it, i bet you will be prompted for when you try to browse it. As far as what's causing it (excluding configuration issues), you may find the following article useful.|||

Teo,

Thanks a lot. I should read this article monthes ago. It is simple and nice compilation about delegation. I will try my luck with tweaking Report Server config and SPN.

I'll keep posting how I am doing, I think it will be helpfull for everybody who will decide to implement SSRS2k5 similar way

MS SQL2005 express ed + SSIS installation problem

Hi,

I have tried to install SSIS going steps msdn2 using "mssql setup" but my "ssis" option is not available for installation.

Pls, help!
SSIS is not included with the Express Edition, only in Standard, Enterprise, and Developer.|||I have tried to find any of these severs in msdn2 and nothing found. Where I can download developer or enterprise edition from? strange..
|||You can't download it unless you have purchased it.

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
identical table TESTTBL. I need to make sure that tables TESTTBL in
both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
following C# code snippet fails. Is it possible to execute command
across two servers or am I wasting my time? Any help on this will be
appreciated.
string sSql= "delete SERVER-2.TESTDB.TESTTBL where
SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
from SERVER-1.TESTDB.TESTTBL )";
SqlConnection thisConnection1 = null;
SqlConnection thisConnection2 = null;
SqlCommand thisCommand1 = null;
SqlCommand thisCommand2 = null;
thisConnection1 = new SqlConnection(connectionString1);
thisConnection1.Open();
thisConnection2 = new SqlConnection(connectionString2);
thisConnection2.Open();
thisCommand1 = new SqlCommand(sSql, thisConnection1);
try
{
thisCommand1.ExecuteNonQuery();
}
catch (SqlException oE)
{
MessageBox.Show(oE.Message.ToString());
}Have you consider using REPLICATION between tow servers?
<bkasmai@.gmail.com> wrote in message
news:1144070684.346122.72030@.v46g2000cwv.googlegroups.com...
> Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
> identical table TESTTBL. I need to make sure that tables TESTTBL in
> both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
> which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
> would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
> make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
> following C# code snippet fails. Is it possible to execute command
> across two servers or am I wasting my time? Any help on this will be
> appreciated.
> string sSql= "delete SERVER-2.TESTDB.TESTTBL where
> SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
> from SERVER-1.TESTDB.TESTTBL )";
> SqlConnection thisConnection1 = null;
> SqlConnection thisConnection2 = null;
> SqlCommand thisCommand1 = null;
> SqlCommand thisCommand2 = null;
> thisConnection1 = new SqlConnection(connectionString1);
> thisConnection1.Open();
> thisConnection2 = new SqlConnection(connectionString2);
> thisConnection2.Open();
> thisCommand1 = new SqlCommand(sSql, thisConnection1);
> try
> {
> thisCommand1.ExecuteNonQuery();
> }
> catch (SqlException oE)
> {
> MessageBox.Show(oE.Message.ToString());
> }
>|||Yes. Past experience prevent me from using replication.
SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.|||Well, another option is to BACKUP LOG file on the source SERVER and
RESTORE it on destination. But I see the problem here because if I remember
well the destination SERVER must be read only....
<bkasmai@.gmail.com> wrote in message
news:1144073079.759225.46870@.e56g2000cwe.googlegroups.com...
> Yes. Past experience prevent me from using replication.
> SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
> SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.
>|||The sync needs to be done every hour through a application written in
c# !

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
identical table TESTTBL. I need to make sure that tables TESTTBL in
both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
following C# code snippet fails. Is it possible to execute command
across two servers or am I wasting my time? Any help on this will be
appreciated.
string sSql= "delete SERVER-2.TESTDB.TESTTBL where
SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
from SERVER-1.TESTDB.TESTTBL )";
SqlConnection thisConnection1 = null;
SqlConnection thisConnection2 = null;
SqlCommand thisCommand1 = null;
SqlCommand thisCommand2 = null;
thisConnection1 = new SqlConnection(connectionString1);
thisConnection1.Open();
thisConnection2 = new SqlConnection(connectionString2);
thisConnection2.Open();
thisCommand1 = new SqlCommand(sSql, thisConnection1);
try
{
thisCommand1.ExecuteNonQuery();
}
catch (SqlException oE)
{
MessageBox.Show(oE.Message.ToString());
}
Have you consider using REPLICATION between tow servers?
<bkasmai@.gmail.com> wrote in message
news:1144070684.346122.72030@.v46g2000cwv.googlegro ups.com...
> Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
> identical table TESTTBL. I need to make sure that tables TESTTBL in
> both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
> which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
> would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
> make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
> following C# code snippet fails. Is it possible to execute command
> across two servers or am I wasting my time? Any help on this will be
> appreciated.
> string sSql= "delete SERVER-2.TESTDB.TESTTBL where
> SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
> from SERVER-1.TESTDB.TESTTBL )";
> SqlConnection thisConnection1 = null;
> SqlConnection thisConnection2 = null;
> SqlCommand thisCommand1 = null;
> SqlCommand thisCommand2 = null;
> thisConnection1 = new SqlConnection(connectionString1);
> thisConnection1.Open();
> thisConnection2 = new SqlConnection(connectionString2);
> thisConnection2.Open();
> thisCommand1 = new SqlCommand(sSql, thisConnection1);
> try
> {
> thisCommand1.ExecuteNonQuery();
> }
> catch (SqlException oE)
> {
> MessageBox.Show(oE.Message.ToString());
> }
>
|||Yes. Past experience prevent me from using replication.
SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.
|||Well, another option is to BACKUP LOG file on the source SERVER and
RESTORE it on destination. But I see the problem here because if I remember
well the destination SERVER must be read only....
<bkasmai@.gmail.com> wrote in message
news:1144073079.759225.46870@.e56g2000cwe.googlegro ups.com...
> Yes. Past experience prevent me from using replication.
> SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
> SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.
>
|||The sync needs to be done every hour through a application written in
c# !

MS SQL: syntax error in create table sql

Using Management Studio (for MS SQL server 8) I had created a table, given a primary key and created a unique key.
I then had the management studio "script 'create' to file" and it saved an SQL statement (below).
I then dropped/deleted the table and tried to execute the creation statement.
It says "Incorrect syntax near '('. " and references the block connected with the "WITH" statement. (Actually both of them cause the error and removing them gets rid of it.
Is there a way to keep the information contained in the WITH statement in my table creation SQL? Is it even required info?

Code: ( sql )

USE [MyProducts]GO/****** Object: Table [dbo].[Category] Script Date: 10/12/2007 11:22:52 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Category]( [CategoryID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, [CategoryNumber] [int] NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED ( [CategoryID] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY], CONSTRAINT [IX_UniqueCategoryNumber] UNIQUE NONCLUSTERED ( [CategoryNumber] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]) ON [PRIMARY] GOSET ANSI_PADDING OFFUsually you never use With statement while creating a table.

Remove this statement and create table without it then go to table definition and see it should be picked by default.

Good Luck.|||it "appeared" to create correctly without the WITH statements.
I was just bothered by the management studio auto-createing the statement then telling my there were syntax errors. The msdn help on the T-SQL for it shows WITH statements in the smae style.
All well, I'll just remove them.

Thanks.|||You can change some settings but not all.
Most of them are server level settings and can not be changed and just read only.

For example you see ON [PRIMARY] and it allows you to put data on one device and indexes on different devices or even spread tables among several devices but server should see your other devices first before you are trying to create something on them. So it is available but for people who know what they doing
You are doing grate so good luck.

MS SQL/ADP loses records

anyone have this problem? it was reported to me that the records entered over the past 4 weeks are gone. i checked for rights and any sp's that would delete.
they just disappeared? thanks.

Quote:

Originally Posted by jf951

anyone have this problem? it was reported to me that the records entered over the past 4 weeks are gone. i checked for rights and any sp's that would delete.
they just disappeared? thanks.

Your question has been moved into the MS SQL forum.|||

Quote:

Originally Posted by jf951

anyone have this problem? it was reported to me that the records entered over the past 4 weeks are gone. i checked for rights and any sp's that would delete.
they just disappeared? thanks.

Hi there,

It sounds a little weird to me, anyway, by any chance did you or anyone else might have accidently restored old database? Well, this is what i can think of at this time, please check this out. Good luck & Take care.

MS SQL, Using SP in Select

Hi,
I have an SP called mTest which reads like,
Create procedure mtest
as
Begin
Select * from tbSuppliers
end.

Now I want to use the SP in a select statement like

Select * from mTest.

But it is giving me error.
Can any one give me a solution for it. (If it is possible)

Thanks in advance
PillaiOriginally posted by mbpilla
Hi,
I have an SP called mTest which reads like,
Create procedure mtest
as
Begin
Select * from tbSuppliers
end.

Now I want to use the SP in a select statement like

Select * from mTest.

But it is giving me error.
Can any one give me a solution for it. (If it is possible)

Thanks in advance
Pillai

You can not call a stored procedure in a select query but insted if u just call the stored proc as
exec mtest instead of the select query and u get the same output.|||Thanks Harshal, I got the result.

Could u please tell me the difference of trusted connection and untrusted connection.

And one more Question I got while an interview is,

What all types of connections are supported by SQL?

Thanks
Pillai|||Originally posted by mbpilla
Thanks Harshal, I got the result.

Could u please tell me the difference of trusted connection and untrusted connection.

And one more Question I got while an interview is,

What all types of connections are supported by SQL?

Thanks
Pillai
for more information refer to BOL under trusted connections.
From BOL:
A login ID only enables you to connect to an instance of SQL Server. Permissions within specific databases are controlled by user accounts. The database administrator maps your login account to a user account in any database you are authorized to access.
Instances of SQL Server must verify that the login ID supplied on each connection request is authorized to access the instance. This process is called authentication. SQL Server 2000 uses two types of authentication: Windows Authentication and SQL Server Authentication. Each has a different class of login ID.
When you connect, the SQL Server 2000 client software requests a Windows trusted connection to SQL Server 2000. Windows does not open a trusted connection unless the client has logged on successfully using a valid Windows account. The properties of a trusted connection include the Windows NT and Windows 2000 group and user accounts of the client that opened the connection. SQL Server 2000 gets the user account information from the trusted connection properties and matches them against the Windows accounts defined as valid SQL Server 2000 logins. If SQL Server 2000 finds a match, it accepts the connection. When you connect to SQL Server 2000 using Windows 2000 Authentication, your identification is your Windows NT or Windows 2000 group or user account.That is a trusted connection.|||yup it is possible to use a sproc in a select statement using open query ... or opendatasource ...
------------------------
SELECT *
FROM OPENQUERY(SvrName, 'exec sproc')
------------------------

but i believe you will have to add a linked server to your own server.

MS SQL(2000) table export to excel

I′m looking for a script (sql transaction, or function) to export MS
SQL(2000) Table to formated excel sheet (cells width, text format, ...).how about using the DTS
to export that to excel
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Milo?" wrote:

> I′m looking for a script (sql transaction, or function) to export MS
> SQL(2000) Table to formated excel sheet (cells width, text format, ...).|||Milo?,
You also can use BCP command. See Books Online.
Regards,
"Milo?" wrote:

> I′m looking for a script (sql transaction, or function) to export MS
> SQL(2000) Table to formated excel sheet (cells width, text format, ...).|||Thank You Jose,
DTS export ist very easy, for my situation is the best, but it has litlle
problem with text encoding.
Milo?
?Jose G. de Jesus Jr MCP, MCDBA" nap_sal (nap_sala):
> how about using the DTS
> to export that to excel
>
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Milo?" wrote:
>sql

MS SQL vs Versant DB

Is anyone familiar with Versant and how it stacks up against MS SQL as a
development technology. I have a client that currently has an application
developed on Versant and they want to investigate moving to MS SQL.
Any help or direction is appreciated.
Thanks
--
George AdrianAs far as I can tell from a quick look at their web site, the Versant DB
does not use SQL, but instead is queried via an object API. Is that
accurate? If so, the changes to the application's data access code are
going to be massive -- you're going to have to write queries instead of
using objects. I hope that the client's code is well structured and that
they've segregated the data access and business / user interface code enough
that this won't require a complete re-write of the application.
Why do they want to switch, anyway?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"George Adrian" <GeorgeAdrian@.discussions.microsoft.com> wrote in message
news:EEEC9583-E662-4612-9071-001CDE36CF57@.microsoft.com...
> Is anyone familiar with Versant and how it stacks up against MS SQL as a
> development technology. I have a client that currently has an application
> developed on Versant and they want to investigate moving to MS SQL.
> Any help or direction is appreciated.
> Thanks
> --
> George Adrian|||Adam,
thanks for your feedback, yes the database is an object database.
The client wishes to change because the development is costing too much and
the developer is unable to provide any documentation. The community for
development in this field seems to be smaller and therefore more expensive.
that is why they want to change.
--
George Adrian
"Adam Machanic" wrote:

> As far as I can tell from a quick look at their web site, the Versant DB
> does not use SQL, but instead is queried via an object API. Is that
> accurate? If so, the changes to the application's data access code are
> going to be massive -- you're going to have to write queries instead of
> using objects. I hope that the client's code is well structured and that
> they've segregated the data access and business / user interface code enou
gh
> that this won't require a complete re-write of the application.
> Why do they want to switch, anyway?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "George Adrian" <GeorgeAdrian@.discussions.microsoft.com> wrote in message
> news:EEEC9583-E662-4612-9071-001CDE36CF57@.microsoft.com...
>
>

MS SQL Vs ORACLE

I am looking for comparisons between these 2 RDBMS , in
terms of preformance and advenatges vs disadvantages.
Can some one direct me to the resourse / link etc ?Hi Suri.
Many "comparisons" are often one-sided attempts to demonstrate one being
superior in one way or another over the other. This one seems fairly
balanced though:
http://www.dbasupport.com/oracle/ora9i/SQLServer_compare.shtml
Regards,
Greg Linwood
SQL Server MVP
"Suri" <suryakant_shaha@.hotmail.com> wrote in message
news:037401c3afb1$8950ec40$a401280a@.phx.gbl...
> I am looking for comparisons between these 2 RDBMS , in
> terms of preformance and advenatges vs disadvantages.
> Can some one direct me to the resourse / link etc ?
>|||Thanks Greg Linwood
>--Original Message--
>Hi Suri.
>Many "comparisons" are often one-sided attempts to
demonstrate one being
>superior in one way or another over the other. This one
seems fairly
>balanced though:
>http://www.dbasupport.com/oracle/ora9i/SQLServer_compare.s
html
>Regards,
>Greg Linwood
>SQL Server MVP
>"Suri" <suryakant_shaha@.hotmail.com> wrote in message
>news:037401c3afb1$8950ec40$a401280a@.phx.gbl...
>> I am looking for comparisons between these 2 RDBMS , in
>> terms of preformance and advenatges vs disadvantages.
>> Can some one direct me to the resourse / link etc ?
>
>.
>|||Not a bad review but not the whole story. Oracle costs =0A=become better if you are licensing per user rather than =0A=per proc. Also not that in Oracle readers to not block =0A=writers and vice versa. This can lead to better =0A=performance when you are running oltp and dss against the =0A=same database. Also if you go to tpc.org check out the =0A=non-clusterred results. Unix hardware generally scales =0A=vertically much better. Also look at the tpc-h =0A=benchmarks, SQL Server only plays at the shallow end of =0A=the pool. =0A=>--Original Message-- =0A=>Hi Suri. =0A=> =0A=>Many "comparisons" are often one-sided attempts to =0A=demonstrate one being =0A=>superior in one way or another over the other. This one =0A=seems fairly =0A=>balanced though: =0A=> =0A=>http://www.dbasupport.com/oracle/ora9i/=0A=
SQLServer_compare.shtml =0A=> =0A=>Regards, =0A=>Greg Linwood =0A=>SQL Server MVP =0A=> =0A=>"Suri" <suryakant_shaha@.hotmail.com> wrote in message =0A=>news:037401c3afb1$8950ec40$a401280a@.phx.gbl... =0A=>> I am looking for comparisons between these 2 RDBMS , =0A=in =0A=>> terms of preformance and advenatges vs disadvantages. =0A=>> Can some one direct me to the resourse / link etc ? =0A=>> =0A=> =0A=> =0A=>. =0A=>|||Dear Anonymous
1. Expense - SQL Server is cheaper up to 3000 gb source
Transaction Processing Council.
http://www.tpc.org/tpch/results/tpch_price_perf_results.asp
2. Speed, yes for OLAP transactions Oracle is faster
http://www.tpc.org/tpcc/results/tpcc_results.asp?
orderby=hardware
3. E-Commerce however SQL Server is much faster
http://www.tpc.org/tpcw/results/tpcw_results.asp?
orderby=hardware
Secondly your comment that 'SQL Server only plays at the
shallow end of the pool' for tpc-h applications is
misleading. If you check comparatble hardware i.e. non
Unix than SQL Server is faster, and is easily the second
fastest on the board.
The following article has more details than the link posted
http://www.dbasupport.com/oracle/ora9i/SQLServer_compare2.s
html
Please note the conclusion.
Peter
>--Original Message--
>Not a bad review but not the whole story. Oracle costs
>become better if you are licensing per user rather than
>per proc. Also not that in Oracle readers to not block
>writers and vice versa. This can lead to better
>performance when you are running oltp and dss against the
>same database. Also if you go to tpc.org check out the
>non-clusterred results. Unix hardware generally scales
>vertically much better. Also look at the tpc-h
>benchmarks, SQL Server only plays at the shallow end of
>the pool.
>>--Original Message--
>>Hi Suri.
>>Many "comparisons" are often one-sided attempts to
>demonstrate one being
>>superior in one way or another over the other. This one
>seems fairly
>>balanced though:
>>http://www.dbasupport.com/oracle/ora9i/
>SQLServer_compare.shtml
>>Regards,
>>Greg Linwood
>>SQL Server MVP
>>"Suri" <suryakant_shaha@.hotmail.com> wrote in message
>>news:037401c3afb1$8950ec40$a401280a@.phx.gbl...
>> I am looking for comparisons between these 2 RDBMS ,
>in
>> terms of preformance and advenatges vs disadvantages.
>> Can some one direct me to the resourse / link etc ?
>>
>>.
>.
>

MS SQL vs free Databases

Hello

I need to write an application which must use a database system as a backend, but i have not choosed which one to use. MySQL and PostgreSQL has all the features i want. the most important is database performance. anyone has an idea which database performs faster?

thanks in advance

Well, since you're on the MSDN forums, I'll go ahead and recommend the obvious: try SQL Express or MSDE :-)...|||

Hi,

well SQL Server Express is very powerful, fast, and has rich features. There are normally other conditions that have to be kept in mind while decideing, but if you ask people in a MSDN group, I would say go the Express Way :-)

-Jens Suessmeyer.

http://www.sqlserver2005.de

|||

If you are writing an app from scratch, I'd say you are by far best of using .NET development on a SQL Express platform. You are dealing with the latest and most efficient development platform, the most supported platform, and one that is expandable down the road. And it is cheap to get in at entry level

If you hit a home run, you want to at least have the option of scaling up and up all the way to SQL Enterprise with failover clustered servers.

I don't dream too big, do I? ;-)

|||

A major consideration for your choice of database should include not only features and performance, but the development environment. You really need to consider the OS, programming languages, and tools that you want to use. If you have developers who are proficient with Microsoft technologies (i.e., .Net) then SQL Server is an obvious choice. SQL Server and .Net provide everything you need and there is a ton of documentation, sites, books, webcasts, etc. ad nauseam.

PostgreSQL is powerful and interesting. It supports many languages (C, PL/Python, PL/Perl, PL/pgSQL), but development in the open-source environment is a radical shift from the widespread commercial support of MS or Oracle. You will need to be very proficient at coding many things from scratch, finding open-source tools and support sites, etc. Also, while learning PostgreSQL, I found that it's backup and recovery features were years behind Oracle. So, if your application will be enterprise class, you need to closely examine backup and recovery and high availability features. If your production database goes down, "who you gonna call"? (BTW, there are lists you can subscribe to to get email support from the PostgreSQL developers, but it's not "official" support like you get with MS or Oracle.) Check out www.postgresql.org as a starting point. I cannot recommend highly enough the PostgreSQL Bootcamp at www.bignerdranch.com.

I don't know Jack about MySQL.

Good luck and choose wisely!

|||

i can study u plen.

MS SQL Views

Is there any kind of lock on a view in ms sql database?
what happens if one of my users is looking at the view and another one is adding something to the database?
Thanks.The view itself doesn't create any locks, although using the view has the potential to create locks. A view in SQL Server is simply a way to "can" a SELECT statement (with a few restrictions), so the view itself doesn't do any locking. Using the view is subject to exactly the same rules that using the SELECT statements that the view represents, and those rules are applied in exactly the same way regardless of whether you use the view or the SELECT statement.

-PatP|||Well-well-well, the usage of the view via SELECT/INSERT/UPDATE/DELETE as well as when referenced in a JOIN, DOES cause additional TAB lock to be created in syslocks. And if everything was "exactly" the same while comparing SELECT and a view, - then why did "they" come up with such a silly concept? We could just do SELECT ... FROM (SELECT ... FROM (SELECT ... FROM (SELECT ... FROM tbl) a) b) c

Instead, the usage of views (while dangerous depending who's in control) provides a mechanism of abstracting data and data access settings that equally affect the view participants.

To answer the question you need to know what isolation level is being applied while SELECTing/INSERTing from/to the view. SELECT usually produces a TAB and a PAG lock, but may escalate to EXT, while INSERT results in a PAG lock that is not yet visible to the previously invoked SELECT.|||If you want to prevent the view from escalating the lock on the table, you could always try creating another incompatible lock on the table with another spid so that it is never escalated into a table lock. It will run slower, but your select should still run. You can always watch lock escalations with the query profiler, filter the object ID and know exactly when and what is causing the escalation. When you know that, then you know what you need to tune.

Cheers,
-Kilka

MS SQL using TSQL via JAVA jdbc

Hello. I am having trouble finding a descent API for executing TSQL
via JDBC. I have tried the Microsoft driver AND the JDTS driver's but
get the same syntax errors. I am hoping there is a good solution also
for executing scripts using the same. Any help is MUCH
appreciated.Thx.(asufty888@.gmail.com) writes:

Quote:

Originally Posted by

I believe that Datadirect's driver is good. Of course, the price is
radically different than for Microsoft's driver or jTDS.

Then again, if you gets the same syntax errors, maybe the problem is
with your SQL code. Have you checked that it runs OK in Query Analyzer?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 5, 10:15 am, asufty...@.gmail.com wrote:

Quote:

Originally Posted by

*No* JDBC driver will 'execute' T-SQL. They simply send it to the
DBMS*.
I suspect you are confusing the syntax of OSQL (eg: with 'GO' to send
SQL)
with actual T-SQL. Show the JDBC code and SQL and error you get.

Joe Weinstein at BEA Systems

* There is a small, well-defined area of JDBC SQL extensions which
has the driver look for specific generic tags in the SQL for some
functions, and replace them with correct DBMS-specific SQL.sql

Friday, March 30, 2012

MS support confusion... Help!

MS SubQuery Changes??

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

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

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

MS SQLServer or Pervasive SQL 2000i

MS SQL-Server Express licensing

MS Sqlserver 2005: Displaying only dbs user has a right to?

MS sqljdbc.jar, v1.1, and Sql 2005 SP 2

Ms SQL7 linking problem

MS SQL7 (Desktop) error

MS SQL7 (Desktop) error

MS SQL2005 Updata from SP1 to SP2 Fail

MS SQL2005 reporting Services + AD. Security question

PC#1 Domain controller (AD)

PC#2 IIS (intranet web site)

PC#3 MS SQL server. (data)

PC#1 SSRS, IIS for Reports Server, MS SQL 2k5 for RS metadata etc

PC#2 Domain controller (AD)

PC#3 MS SQL2k (data)

MS SQL2005 express ed + SSIS installation problem

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

MS SQL: syntax error in create table sql

MS SQL/ADP loses records

MS SQL, Using SP in Select

MS SQL(2000) table export to excel

MS SQL vs Versant DB

MS SQL Vs ORACLE

MS SQL vs free Databases

MS SQL Views

MS SQL using TSQL via JAVA jdbc

MS Service Pack frustrations

Blog Archive

About Me