Query

Hi
Can someone tell me what is wrong with this query? It says it's ok but just
runs and doesn't time out.
qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "2005/Jan/26" AND
"2005/Feb/09")
AND (Assignee.Name BETWEEN "Beattie Steve" AND "Beattie Steve")
AND (OnBehalf.Name BETWEEN "Beattie Steve" AND "Beattie Steve")', '500'Plz post the code of the sp qfm_GetEventSearchResult.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jaco" <Jaco@.discussions.microsoft.com> wrote in message
news:05265C8E-2C69-4E69-8FFC-CF604D3296C9@.microsoft.com...
> Hi
> Can someone tell me what is wrong with this query? It says it's ok but
> just
> runs and doesn't time out.
> qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "2005/Jan/26" AND
> "2005/Feb/09")
> AND (Assignee.Name BETWEEN "Beattie Steve" AND "Beattie Steve")
> AND (OnBehalf.Name BETWEEN "Beattie Steve" AND "Beattie Steve")', '500'
>|||sorry -
CREATE PROCEDURE qfm_GetEventSearchResult
@.param nvarchar(4000)= NULL,
@.maxRecords nvarchar(10) = NULL
AS
Declare @.SQL nvarchar(4000)
SET @.param = REPLACE(@.param,CHAR(34),CHAR(39))
set @.SQL = ' Select Distinct Top ' + @.maxRecords
set @.SQL = @.SQL + ' Event.EventID as Event,'
set @.SQL = @.SQL + ' EventProject.ProjectName as Proj,'
set @.SQL = @.SQL + ' Event.EntryDate AS Date,'
set @.SQL = @.SQL + ' dbo.fn_ShortName(1,EventType.Name) as Type,'
set @.SQL = @.SQL + ' Substring (EventStatus.Name,1,3) AS Status,'
--set @.SQL = @.SQL + '
dbo.fn_ModelOrService(Event.EventType,Model.Model,service.Service) as
Descrip,'
--join on express event to grab service or model name
set @.SQL = @.SQL + 'dbo.fn_ModelOrService(Event.EventType,
case when ExpEvent.eventid is null then Model.Model else ExpEvent.Model
end,
case when ExpEvent.eventid is null then service.Service else
ExpEvent.service end)
as Descrip,'
set @.SQL = @.SQL + ' dbo.GeographyName(Event.GeographyID, 1, CHAR(47)) AS
Geography'
set @.SQL = @.SQL + ' FROM Event'
set @.SQL = @.SQL + ' LEFT JOIN qfmWorkTypeLookup as WorkType ON
WorkType.WorkTypeID = Event.WorkTypeID'
set @.SQL = @.SQL + ' LEFT JOIN qfmServiceGroup_ServiceLookup as service on
service.ServiceID = Event.ModelORServiceID'
set @.SQL = @.SQL + ' LEFT JOIN qfmLinksLookup Links ON Links.EventNumber =
Event.EventID' --AG: Fix for Defect 7993
set @.SQL = @.SQL + ' LEFT JOIN qfmAgentOperativeLookup Operative ON
Operative.EventID = Event.EventID' --Rhavy: Fix for Defect 2151
set @.SQL = @.SQL + ' LEFT JOIN Geography ON Geography.GeographyID =
Event.GeographyID'
set @.SQL = @.SQL + ' LEFT JOIN qfmSiteByGeogIDLookup AS G1 ON
Event.GeographyID = G1.GeographyID'
set @.SQL = @.SQL + ' LEFT JOIN qfmGeographyRegionLookup as GeographyRegion ON
GeographyRegion.regionID = G1.regionID' --Fix for defect 7242
set @.SQL = @.SQL + ' LEFT JOIN qfmGeographyAreaLookup as GeographyArea ON
GeographyArea.regionID = GeographyRegion.AreaID'
set @.SQL = @.SQL + ' LEFT JOIN qfmSectorLookup as Region ON Region.regionID =
GeographyArea.SectorID' --Fix for defect 7145
set @.SQL = @.SQL + ' LEFT JOIN qfmGeographySiteTypeLookup SiteType ON
SiteType.GeographyID = Geography.GeographyID' --Fix for Defect 3808
set @.SQL = @.SQL + ' INNER JOIN EventType ON Event.EventType =
EventType.EventTypeID'
set @.SQL = @.SQL + ' LEFT JOIN qfmPriorityListLookup Priority ON
Event.PriorityID = Priority.PriorityID'
set @.SQL = @.SQL + ' LEFT JOIN ContractorType'
set @.SQL = @.SQL + ' RIGHT JOIN Agent ON ContractorType.ContractorTypeID =
Agent.ContractorTypeID ON Event.AgentID = Agent.AgentID'
set @.SQL = @.SQL + ' LEFT JOIN qfmEventStatusLookup EventStatus ON
Event.EventStatusNo = EventStatus.EventStatusID'
set @.SQL = @.SQL + ' LEFT JOIN ClientSatisfaction ON
Event.ClientSatisfactionID = ClientSatisfaction.ClientSatisfactionID'
set @.SQL = @.SQL + ' LEFT JOIN qfmCostCodeLookup CostCode on Event.CostCodeID
= CostCode.CostCodeID'
set @.SQL = @.SQL + ' LEFT JOIN Inventory ON Inventory.InventoryID =
Event.InventoryID' --DD4968
set @.SQL = @.SQL + ' LEFT JOIN qfmEquipmentGroupLookup EquipmentGroup ON
Inventory.EquipmentGroupID = EquipmentGroup.EquipmentGroupID'
set @.SQL = @.SQL + ' LEFT JOIN qfmModelLookup Model ON Inventory.ModelID =
Model.ModelID'
set @.SQL = @.SQL + ' LEFT JOIN qfmDepartmentsLookup Departments ON
Inventory.DepartmentID = Departments.DepartmentsID'
set @.SQL = @.SQL + ' LEFT JOIN qfmDivisionLookup Division on
Division.DivisionID = Inventory.DivisionID'
set @.SQL = @.SQL + ' LEFT JOIN qfmCoverCodeLookup CoverCode ON
Event.CoverCodeID = CoverCode.CoverCodeID'
set @.SQL = @.SQL + ' LEFT JOIN qfmEventProjectlookup Project ON Event.EventID
= Project.EventID'
set @.SQL = @.SQL + ' LEFT JOIN qfmEventProjectListLookup EventProject ON
EventProject.EventsProjectId = Project.EventsProjectId'
set @.SQL = @.SQL + ' LEFT JOIN qfmSiteLocationLookup AS G2 ON
Event.GeographyID = G2.GeographyID'
set @.SQL = @.SQL + ' LEFT JOIN qfmSiteByUnitLookup AS G3 ON Event.GeographyID
= G3.GeographyID'
set @.SQL = @.SQL + ' LEFT JOIN qfmSiteByPartitionLookup G4 ON
Event.GeographyID = G4.GeographyID'
set @.SQL = @.SQL + ' LEFT JOIN qfmAllocationLookup Allocation ON
Event.CostAllocationID = Allocation.AllocationID'
set @.SQL = @.SQL + ' LEFT JOIN qfmPersonnelLookup Personnel ON
Event.ReportedBy = Personnel.Name'
set @.SQL = @.SQL + ' LEFT JOIN qfmOnBehalfLookup OnBehalf ON Event.OnBehalfID
= OnBehalf.PersonnelID'
set @.SQL = @.SQL + ' LEFT JOIN qfmAssigneeLookup Assignee ON Event.AssigneeID
= Assignee.PersonnelID'
set @.SQL = @.SQL + ' LEFT JOIN qfmContractLookup Contract ON Event.ContractID
= Contract.ContractID'
set @.SQL = @.SQL + ' LEFT JOIN qfmIRInvoiceDetailLookup IRInvoiceDetail ON
IRInvoiceDetail.EventID = Event.EventID'
set @.SQL = @.SQL + ' LEFT JOIN PurchaseOrderCost ON PurchaseOrderCost.EventID
= Event.EventID'
set @.SQL = @.SQL + ' LEFT JOIN qfmexpresseventlookup ExpEvent ON
ExpEvent.EventID = Event.EventID'
set @.SQL = @.SQL + ' ' + @.param + ' ORDER BY Event.EventID DESC'
Exec sp_executesql @.SQL
"Roji. P. Thomas" wrote:

> Plz post the code of the sp qfm_GetEventSearchResult.
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Jaco" <Jaco@.discussions.microsoft.com> wrote in message
> news:05265C8E-2C69-4E69-8FFC-CF604D3296C9@.microsoft.com...
>
>|||On Wed, 9 Feb 2005 03:07:02 -0800, Jaco wrote:

>sorry -
>CREATE PROCEDURE qfm_GetEventSearchResult
>@.param nvarchar(4000)= NULL,
>@.maxRecords nvarchar(10) = NULL
>AS
(snip)
Hi Jaco,
Are you really sure that this is the only way to address your needs?
Usually, passing a where clause to a stored procedure is a token of a weak
design. Besides, it is incrediblyu dangerous - your system is wide open to
SQL injection attacks (see http://www.sommarskog.se/dynamic_sql.html for
an excellent explenations of these and other dangers of dynamic SQL).
If I didn't miscount, you are joining a total of THIRTY-SEVEN tables in
this join. And only one of them is joined using inner join, all others are
joined with a left or right outer join. Surely, you didn't _expect_ great
performance from this query?
Some tips that might (or might not - you'll have to try) help you speed
this up:
* Eliminate tables you don't need. You use a dynamically passed where
clause - do you join in some extra tables, just so they can be used in the
where clause if needed? If so, then eliminate them - you're just making
the DB engine do needless extra work.
* Remove the distinct. It's quite possible that you need it because you
join in to many tables; remove the unneeded tables, and the distinct
becomes redundant as well.
* Check the user-defined functions you are using. Could they be a
bottleneck? Remember that SQL Server will call them for each row in the
result set. If 80 duplicate rows get removed because of the DISTINCT, each
of the functions has already been called 80 times. If possible, remove
them or put the function's logic inline (so that SQL Server has more
optimization options).
* If you use a join only to test for existance or non-existance of a row
in a table, change it to [NOT] EXISTS (subquery).
* Change LEFT and RIGHT joins to INNER joins wherever possible.
* Change the way you call this proc. Instead of
qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "2005/Jan/26" AND
"2005/Feb/09")
AND (Assignee.Name BETWEEN "Beattie Steve" AND "Beattie Steve")
AND (OnBehalf.Name BETWEEN "Beattie Steve" AND "Beattie Steve")', '500'
use
qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "20050126" AND
"20050209")
AND (Assignee.Name = "Beattie Steve")
AND (OnBehalf.Name = "Beattie Steve")', '500'
(That is - use an unambiguous date format to prevent misconversions and
use equality instead of BETWEEN, since the upper and lower limit of the
between range are equal anyway)
* Check the execution plan. Which tables are being scanned? Which are
accessed using an index? Which are accessed very often? Maybe adding some
indexes will gain you some performance.
* Manage expectations :-) If you feed SQL Server a humongous query that
joins this amount of tables and calls several functions to boot, you can't
expect great performance. I'm afraid that the compilation of an execution
plan will already take quite some time - just consider the enormous number
of strategies the optimizer will have to choose from!
* And the most important advice: reconsider if you really need this
procedure to operate as it now does.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks.
"Hugo Kornelis" wrote:

> On Wed, 9 Feb 2005 03:07:02 -0800, Jaco wrote:
>
> (snip)
> Hi Jaco,
> Are you really sure that this is the only way to address your needs?
> Usually, passing a where clause to a stored procedure is a token of a weak
> design. Besides, it is incrediblyu dangerous - your system is wide open to
> SQL injection attacks (see http://www.sommarskog.se/dynamic_sql.html for
> an excellent explenations of these and other dangers of dynamic SQL).
> If I didn't miscount, you are joining a total of THIRTY-SEVEN tables in
> this join. And only one of them is joined using inner join, all others are
> joined with a left or right outer join. Surely, you didn't _expect_ great
> performance from this query?
> Some tips that might (or might not - you'll have to try) help you speed
> this up:
> * Eliminate tables you don't need. You use a dynamically passed where
> clause - do you join in some extra tables, just so they can be used in the
> where clause if needed? If so, then eliminate them - you're just making
> the DB engine do needless extra work.
> * Remove the distinct. It's quite possible that you need it because you
> join in to many tables; remove the unneeded tables, and the distinct
> becomes redundant as well.
> * Check the user-defined functions you are using. Could they be a
> bottleneck? Remember that SQL Server will call them for each row in the
> result set. If 80 duplicate rows get removed because of the DISTINCT, each
> of the functions has already been called 80 times. If possible, remove
> them or put the function's logic inline (so that SQL Server has more
> optimization options).
> * If you use a join only to test for existance or non-existance of a row
> in a table, change it to [NOT] EXISTS (subquery).
> * Change LEFT and RIGHT joins to INNER joins wherever possible.
> * Change the way you call this proc. Instead of
> qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "2005/Jan/26" AND
> "2005/Feb/09")
> AND (Assignee.Name BETWEEN "Beattie Steve" AND "Beattie Steve")
> AND (OnBehalf.Name BETWEEN "Beattie Steve" AND "Beattie Steve")', '500'
> use
> qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "20050126" AND
> "20050209")
> AND (Assignee.Name = "Beattie Steve")
> AND (OnBehalf.Name = "Beattie Steve")', '500'
> (That is - use an unambiguous date format to prevent misconversions and
> use equality instead of BETWEEN, since the upper and lower limit of the
> between range are equal anyway)
> * Check the execution plan. Which tables are being scanned? Which are
> accessed using an index? Which are accessed very often? Maybe adding some
> indexes will gain you some performance.
> * Manage expectations :-) If you feed SQL Server a humongous query that
> joins this amount of tables and calls several functions to boot, you can't
> expect great performance. I'm afraid that the compilation of an execution
> plan will already take quite some time - just consider the enormous number
> of strategies the optimizer will have to choose from!
> * And the most important advice: reconsider if you really need this
> procedure to operate as it now does.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>sql

Quality of Displayed charts is poor

I create some charts in VS and they look crisp.
Then deploy, but when I view in the bouser(IE7) they look jaggered.
What am I doing wrong.
Thanks for any adviceOn Nov 7, 8:37 pm, gavin <ga...@.discussions.microsoft.com> wrote:
> I create some charts in VS and they look crisp.
> Then deploy, but when I view in the bouser(IE7) they look jaggered.
> What am I doing wrong.
> Thanks for any advice
This is just a thought, but you might want to shrink the chart control
in the report and see if maybe that improves the resolution, etc. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

QA returning wrong data

SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'

>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
table[vbcol=seagreen]
>
>.
>|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
> Just to make absolutely sure (nothing personal ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
Anith|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'

> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don

>.
>

QA returning wrong data

SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal :) ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'
>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
>> SQL 7.0
>> In QA:
>> "Select name from <table> where date = '20041010'
>> I get a list of 75,000 names. The name "Jones" is not
in
>> the list.
>> However, if in QA:
>> "Select * from <table> where name = 'Jones' and ddate
>> = '20041010'
>> The name "Jones" is returned!
>> I can't figure this out for the life of me. It's a
table
>> with 95 million records and a clustered index based on
>> name,date.
>> Any help appreciated.
>> thx,
>> Don
>
>.
>|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don
>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
>> "Select name from <table> where date = '20041010'
>> I get a list of 75,000 names. The name "Jones" is not
in
>> the list.
> Just to make absolutely sure (nothing personal :) ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
--
Anith|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'
> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don
>.
>

QA returning wrong data

SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.
|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>
|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'

>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
table
>
>.
>
|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in
> Just to make absolutely sure (nothing personal ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>
|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
Anith
|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO
|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?
|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'

> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don

>.
>

QA limitation or simply I am doing anything wrong?

Dear all,
I am trying do a autocontained select but on the results panel appears the
line cut off:
select 'insert into
cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarg
a,nota,dts,repositorio,servdts,idaplicac
ion,duracionult,duracionrel,marcafnu
l,marcabak,horas,frecuencia,responsable,
dias,casoerror,servdesa,docu,marcaff
echa,ficherofecha,ficheronu
l,enespera,ana)
values(' + convert(char(2),idproceso) + ')'
from cargaprocesos where proceso in('ABS_RecuperarCajero')
As ouput:
----
----
----
--
-
insert into
cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarg
a,nota,dts,repositorio,servdts,idaplicac
ion,duracionult,duracionrel,marcafnu
l,marcabak,horas,frecuencia responsable,dias,casoerror,servdesa,docu
,marcaff
e
(1 filas afectadas)
stopped in "marcaffe" field.
Does anyone has suffered any experience with that? Is customizable?
Regards,Change the below setting.
Tools --> Options --> Results --> Maximum Character Per column.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:5619C2E7-B22F-4B59-AD0C-1FCF0B9595FD@.microsoft.com...
> Dear all,
> I am trying do a autocontained select but on the results panel appears the
> line cut off:
> select 'insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarga,no
ta
,dts,repositorio,servdts,idaplicacion,du
racionult,duracionrel,marcafnul,marcabak
,hor
as,frecuencia,responsable,dias,casoerror
,servdesa,docu,marcaffecha,ficherofecha,
fich
ero
nul,enespera,ana)
> values(' + convert(char(2),idproceso) + ')'
> from cargaprocesos where proceso in('ABS_RecuperarCajero')
>
> As ouput:
>
> ----
----
----
--
--
> insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimaca
rga,nota,dts,repositorio,servdts,idaplic
acion,duracionult,duracionrel,marcaf
nul,marcabak,horas,frecuencia
> responsable,dias,casoerror,servdesa,docu
,marcaffe
> (1 filas afectadas)
> stopped in "marcaffe" field.
> Does anyone has suffered any experience with that? Is customizable?
> Regards,|||Hi
The problem is with the buffer size:
just goto Tools > Options
on the 'Results' tab, change the maximum characters per column from 256 to
some 8000.
the u can see the expected result:
aslo modift the query as:
select 'insert into
cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarg
a,nota,dts,repositorio,servdts,idaplicac
ion,duracionult,duracionrel,marcafnu
l,marcabak,horas,frecuencia,responsable,
dias,casoerror,servdesa,docu,marcaff
echa,ficherofecha,ficheronu
l,enespera,ana)
values(' + convert(char(2),idproceso) + ')'
from cargaprocesos where proceso in('ABS_RecuperarCajero') as [Result]
to avoid the - across 3 lines
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Enric" wrote:

> Dear all,
> I am trying do a autocontained select but on the results panel appears the
> line cut off:
> select 'insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarga,no
ta
,dts,repositorio,servdts,idaplicacion,du
racionult,duracionrel,marcafnul,marcabak
,hor
as,frecuencia,responsable,dias,casoerror
,servdesa,docu,marcaffecha,ficherofecha,
fich
ero
nul,enespera,ana)
> values(' + convert(char(2),idproceso) + ')'
> from cargaprocesos where proceso in('ABS_RecuperarCajero')
>
> As ouput:
>
> ----
----
----
--
--
> insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimaca
rga,nota,dts,repositorio,servdts,idaplic
acion,duracionult,duracionrel,marcaf
nul,marcabak,horas,frecuencia responsable,dias,casoerror,servdesa,docu
,marca
ffe
> (1 filas afectadas)
> stopped in "marcaffe" field.
> Does anyone has suffered any experience with that? Is customizable?
> Regards,|||Thanks a lot Roji
"Roji. P. Thomas" wrote:

> Change the below setting.
> Tools --> Options --> Results --> Maximum Character Per column.
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:5619C2E7-B22F-4B59-AD0C-1FCF0B9595FD@.microsoft.com...
ronul,enespera,ana)
--
>
>|||oops,
select 'insert into
cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarg
a,nota,dts,repositorio,servdts,idaplicac
ion,duracionult,duracionrel,marcafnu
l,marcabak,horas,frecuencia,responsable,
dias,casoerror,servdesa,docu,marcaff
echa,ficherofecha,ficheronu
l,enespera,ana)
values(' + convert(char(2),idproceso) + ')' as [Result]
from cargaprocesos where proceso in('ABS_RecuperarCajero')
I made a mistake in the query
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Chandra" wrote:

> Hi
> The problem is with the buffer size:
> just goto Tools > Options
> on the 'Results' tab, change the maximum characters per column from 256 to
> some 8000.
> the u can see the expected result:
>
> aslo modift the query as:
> select 'insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarga,no
ta
,dts,repositorio,servdts,idaplicacion,du
racionult,duracionrel,marcafnul,marcabak
,hor
as,frecuencia,responsable,dias,casoerror
,servdesa,docu,marcaffecha,ficherofecha,
fich
ero
nul,enespera,ana)
> values(' + convert(char(2),idproceso) + ')'
> from cargaprocesos where proceso in('ABS_RecuperarCajero') as [Result]
> to avoid the - across 3 lines
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Enric" wrote:
>
ronul,enespera,ana)
--|||hi Chandra, thanks for your input but it doesn't works:
using as [result]
Servidor: mensaje 156, nivel 15, estado 1, l_nea 6
Incorrect syntax near the keyword 'as'.
"Chandra" wrote:

> Hi
> The problem is with the buffer size:
> just goto Tools > Options
> on the 'Results' tab, change the maximum characters per column from 256 to
> some 8000.
> the u can see the expected result:
>
> aslo modift the query as:
> select 'insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarga,no
ta
,dts,repositorio,servdts,idaplicacion,du
racionult,duracionrel,marcafnul,marcabak
,hor
as,frecuencia,responsable,dias,casoerror
,servdesa,docu,marcaffecha,ficherofecha,
fich
ero
nul,enespera,ana)
> values(' + convert(char(2),idproceso) + ')'
> from cargaprocesos where proceso in('ABS_RecuperarCajero') as [Result]
> to avoid the - across 3 lines
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Enric" wrote:
>
ronul,enespera,ana)
--|||It's ok. Take care yourself,
"Chandra" wrote:

> oops,
> select 'insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarga,no
ta
,dts,repositorio,servdts,idaplicacion,du
racionult,duracionrel,marcafnul,marcabak
,hor
as,frecuencia,responsable,dias,casoerror
,servdesa,docu,marcaffecha,ficherofecha,
fich
ero
nul,enespera,ana)
> values(' + convert(char(2),idproceso) + ')' as [Result]
> from cargaprocesos where proceso in('ABS_RecuperarCajero')
> I made a mistake in the query
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Chandra" wrote:
>
ronul,enespera,ana)
heronul,enespera,ana)
--

Q: SQL , what is wrong?

Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does
it not bring anything when Where is there?
Thanks,Ok. I removed [, works fine.
"JIM.H." wrote:
> Hello,
> SELECT dbo.tSp.pID, dbo.tLo.oS
> FROM dbo.tSp INNER JOIN
> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
> WHERE (dbo.tLo.oS = N'[MyText]')
> This works without Where and I see MyText available in oS column. Why does
> it not bring anything when Where is there?
> Thanks,
>|||Just as a follow up the brackets are to use a obejectname in SQL Server,
useful for those developers who use spaces or special reserved words for
their objects (Yeah I know, there are some out there who always do this
:-) )
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:34E4754D-ECA2-42B7-B36A-BA2F42A841FC@.microsoft.com...
> Ok. I removed [, works fine.
> "JIM.H." wrote:
>> Hello,
>> SELECT dbo.tSp.pID, dbo.tLo.oS
>> FROM dbo.tSp INNER JOIN
>> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
>> WHERE (dbo.tLo.oS = N'[MyText]')
>> This works without Where and I see MyText available in oS column. Why
>> does
>> it not bring anything when Where is there?
>> Thanks,|||SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp
INNER JOIN
dbo.tLo
ON dbo.tSp.SpID = dbo.tLo.SpID
AND dbo.tLo.oS = N'MyText'
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:C1220918-D358-4387-9268-94679031B403@.microsoft.com...
> Hello,
> SELECT dbo.tSp.pID, dbo.tLo.oS
> FROM dbo.tSp INNER JOIN
> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
> WHERE (dbo.tLo.oS = N'[MyText]')
> This works without Where and I see MyText available in oS column. Why does
> it not bring anything when Where is there?
> Thanks,
>

Q: SQL , what is wrong?

Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does
it not bring anything when Where is there?
Thanks,Ok. I removed [, works fine.
"JIM.H." wrote:

> Hello,
> SELECT dbo.tSp.pID, dbo.tLo.oS
> FROM dbo.tSp INNER JOIN
> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
> WHERE (dbo.tLo.oS = N'[MyText]')
> This works without Where and I see MyText available in oS column. Why does
> it not bring anything when Where is there?
> Thanks,
>|||Just as a follow up the brackets are to use a obejectname in SQL Server,
useful for those developers who use spaces or special reserved words for
their objects (Yeah I know, there are some out there who always do this
:-) )
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:34E4754D-ECA2-42B7-B36A-BA2F42A841FC@.microsoft.com...[vbcol=seagreen]
> Ok. I removed [, works fine.
> "JIM.H." wrote:
>

Q: SQL , what is wrong?

Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does
it not bring anything when Where is there?
Thanks,
Ok. I removed [, works fine.
"JIM.H." wrote:

> Hello,
> SELECT dbo.tSp.pID, dbo.tLo.oS
> FROM dbo.tSp INNER JOIN
> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
> WHERE (dbo.tLo.oS = N'[MyText]')
> This works without Where and I see MyText available in oS column. Why does
> it not bring anything when Where is there?
> Thanks,
>
|||Just as a follow up the brackets are to use a obejectname in SQL Server,
useful for those developers who use spaces or special reserved words for
their objects (Yeah I know, there are some out there who always do this
:-) )
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:34E4754D-ECA2-42B7-B36A-BA2F42A841FC@.microsoft.com...[vbcol=seagreen]
> Ok. I removed [, works fine.
> "JIM.H." wrote: