Query a Date Filed in SQL Server 2000

I want to query a date filed in SQL Server 2000 which its default value is set to GetDate().
My problem is that the data filed saves data and time for example "3/26/2003 5:34:34 PM", Now when I query the table and:

SELECT * FROM tblX WHERE Date='3/26/03' it won't return the record.

However if I use the below:

SELECT * FROM tblX WHERE Date='3/26/2003 5:34:34 PM'

It will return the record.

What should I do so that when I use only date and not time it retuns all records on the specified Data ignoring the time?

Thanksessentialy you want all the records for Mar 26th?

1. If you don't need time only store the date portion of GetDate(). Probably not an option.

select convert(varchar,getdate(),101)

2. Remove the time component in your date attribute in your search. This approach will probably cause a table scan.

WHERE convert(varchar,date,101)='3/26/2003'

3. Use the between test. Some times can be extra work.

WHERE date between '3/26/2003' and '3/26/2003 23:59:59'

query 2 server at the same time

can some one please tell me with an example,how to query data from two db server in the same tsql query.

Thanks in advace

You have to setup a linked server and then use full 4 parts to query the linked server.

SELECT *
FROM linkserver.dbname.schema.tablename
JOIN tablename

Query -- 11 seconds in SSMS but times out after 30 minutes in

We're having a weird problem -- a particular report will time out
after 30 minutes running in Reporting Services. However, if we grab
the query from Profiler and run it in SSMS, it runs in about 11
seconds. The profiler trace shows an error of 2 - Abort.
The query itself is found below -- I won't include DDL because each of
the items in the FROM clause is a view that is very long.
Any explanation as to why the query would take so long when running
from reporting services?
Our environment -- SQL 2005 Standard x64, SP2 (9.00.3054.00 / Build
3790). Running on Windows Server 2003 Enterprise x64 SP2.
There are some things I'd like to clean up after seeing it, but
nonetheless it still runs pretty quick in SSMS.
SELECT
e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2
WHEN 3 THEN b.desc_3
WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN
b.desc_6
WHEN 7 THEN b.desc_7 ELSE b.desc_8 END AS Plan_Option
, e.SHORT_NAME, e.SSN
, SUM(bck.COST)
, e.STATUS
,case when e.terminated ='1753-01-01 00:00:00.000' then ' '
else LEFT( CONVERT(varchar,e.terminated, 120), 10) end as term_date
FROM
ahr_custom.sls.nocorp_ckreg ck
inner join ahr_custom.sls.nocorp_prckreg p ON p.ck_no = ck.ck_no AND p.ck_style = ck.ck_style AND p.company = ck.company
inner join ahr_custom.sls.nocorp_beckreg bck ON p.ck_no = bck.ck_no AND p.ck_style = bck.ck_style AND p.company = bck.company
inner join ahr_custom.sls.nocorp_beneplan b on b.company = bck.company and b.plan_no = bck.plan_no
inner join ahr_custom.sls.nocorp_employee e on e.company = ck.company and e.emp_no = p.emp_no
inner join ahr_custom.sls.nocorp_empbene eb on bck.company = eb.company and e.emp_no = eb.emp_no and eb.plan_no = bck.plan_no
inner join ahr_custom.sls.nocorp_clients c on ck.company = c.company and c.client_no = e.client_no1
WHERE
bck.PLAN_NO IN (20676)
AND p.COMPANY IN ('OK','CA','C2','CO','TX')
and Left(CONVERT(varchar,ck.ck_date,120),10)
between cast(year(getdate()-20) as varchar(4)) +'-'+ (right( '0'
+cast(month(getdate()-20) as varchar(2)),2))+ '-01'
and LEFT( CONVERT(varchar,
ahr_custom.dbo.lastdayofmonth(getdate()-20) , 120), 10)
GROUP BY
e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2 WHEN 3
THEN b.desc_3
WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN b.desc_6
WHEN 7 THEN b.desc_7 ELSE b.desc_8 END
, e.SHORT_NAME, e.SSN
,e.STATUS
,case when e.terminated ='1753-01-01 00:00:00.000' then ' ' else
LEFT( CONVERT(varchar,e.terminated, 120), 10) endHow many rows of data is returned?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<stephen.lee.moore@.gmail.com> wrote in message
news:ac49df7d-bd90-4f6d-b7c8-44012e8e9bb3@.w56g2000hsf.googlegroups.com...
> We're having a weird problem -- a particular report will time out
> after 30 minutes running in Reporting Services. However, if we grab
> the query from Profiler and run it in SSMS, it runs in about 11
> seconds. The profiler trace shows an error of 2 - Abort.
> The query itself is found below -- I won't include DDL because each of
> the items in the FROM clause is a view that is very long.
> Any explanation as to why the query would take so long when running
> from reporting services?
> Our environment -- SQL 2005 Standard x64, SP2 (9.00.3054.00 / Build
> 3790). Running on Windows Server 2003 Enterprise x64 SP2.
> There are some things I'd like to clean up after seeing it, but
> nonetheless it still runs pretty quick in SSMS.
> SELECT
> e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
> ,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2
> WHEN 3 THEN b.desc_3
> WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN
> b.desc_6
> WHEN 7 THEN b.desc_7 ELSE b.desc_8 END AS Plan_Option
> , e.SHORT_NAME, e.SSN
> , SUM(bck.COST)
> , e.STATUS
> ,case when e.terminated ='1753-01-01 00:00:00.000' then ' '
> else LEFT( CONVERT(varchar,e.terminated, 120), 10) end as term_date
> FROM
> ahr_custom.sls.nocorp_ckreg ck
> inner join ahr_custom.sls.nocorp_prckreg p ON p.ck_no => ck.ck_no AND p.ck_style = ck.ck_style AND p.company = ck.company
> inner join ahr_custom.sls.nocorp_beckreg bck ON p.ck_no => bck.ck_no AND p.ck_style = bck.ck_style AND p.company = bck.company
> inner join ahr_custom.sls.nocorp_beneplan b on b.company => bck.company and b.plan_no = bck.plan_no
> inner join ahr_custom.sls.nocorp_employee e on e.company => ck.company and e.emp_no = p.emp_no
> inner join ahr_custom.sls.nocorp_empbene eb on bck.company => eb.company and e.emp_no = eb.emp_no and eb.plan_no = bck.plan_no
> inner join ahr_custom.sls.nocorp_clients c on ck.company => c.company and c.client_no = e.client_no1
> WHERE
> bck.PLAN_NO IN (20676)
> AND p.COMPANY IN ('OK','CA','C2','CO','TX')
> and Left(CONVERT(varchar,ck.ck_date,120),10)
> between cast(year(getdate()-20) as varchar(4)) +'-'+ (right( '0'
> +cast(month(getdate()-20) as varchar(2)),2))+ '-01'
> and LEFT( CONVERT(varchar,
> ahr_custom.dbo.lastdayofmonth(getdate()-20) , 120), 10)
> GROUP BY
> e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
> ,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2 WHEN 3
> THEN b.desc_3
> WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN b.desc_6
> WHEN 7 THEN b.desc_7 ELSE b.desc_8 END
> , e.SHORT_NAME, e.SSN
> ,e.STATUS
> ,case when e.terminated ='1753-01-01 00:00:00.000' then ' ' else
> LEFT( CONVERT(varchar,e.terminated, 120), 10) end|||On Dec 4, 11:07 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> How many rows of data is returned?
502 rows|||OK, number of rows is not a problem. My guess is that you are having an
issue with the query plan being different.
First, can you put this in a stored procedure and then call that from both
places and see if that makes a difference?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<stephen.lee.moore@.gmail.com> wrote in message
news:90230c5c-f7c7-4592-8b44-83ce3a381729@.s12g2000prg.googlegroups.com...
> On Dec 4, 11:07 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> How many rows of data is returned?
> 502 rows|||> First, can you put this in a stored procedure and then call that from both
> places and see if that makes a difference?
Wow -- good suggestion! That seemed to fix it.
Any ideas why in the world would a different query plan be generated
from Reporting Services than from SSMS? It seems like either A)
Profiler isn't giving me the right query that's being executed via
Reporting Services or B) different query plans are being generated for
the same SQL statement depending on the calling application. Neither
of those make any sense to me.
Thanks again for your help.|||I have seen this with other people but with stored procedures not SQL. If a
stored procedure acts differently then you can add a With Recompile to fix
the problem. I really don't understand how the same SQL can act different
but that is what I thought was happening to you.
Bruce
<stephen.lee.moore@.gmail.com> wrote in message
news:2cd5adad-6a86-414b-9840-2b3b05e58c31@.j44g2000hsj.googlegroups.com...
>> First, can you put this in a stored procedure and then call that from
>> both
>> places and see if that makes a difference?
> Wow -- good suggestion! That seemed to fix it.
> Any ideas why in the world would a different query plan be generated
> from Reporting Services than from SSMS? It seems like either A)
> Profiler isn't giving me the right query that's being executed via
> Reporting Services or B) different query plans are being generated for
> the same SQL statement depending on the calling application. Neither
> of those make any sense to me.
> Thanks again for your help.

Query

I am trying to make a query to get the date and time a row and the date and
time from the next row:
Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs Note_Text
Batch_Text Lot_Text Author Batch_Note Line
0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
This is what I came up with.
USE Iconics_Data
Select Source_TDate as 'Start Date'
From IHZONE1_Notes
where Batch_text='5' and Lot_Text='2443'
I get
Start Date
2005-07-11 14:59:20.000
I also need the query to give me a column
End Date
2005-07-11 15:00:50.00
How could I do this?
I got it wirking.
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
<Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>I am trying to make a query to get the date and time a row and the date and
>time from the next row:
> Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs
> Note_Text Batch_Text Lot_Text Author Batch_Note Line
> 0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
> 0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
> 0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
> 0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
> This is what I came up with.
> USE Iconics_Data
> Select Source_TDate as 'Start Date'
> From IHZONE1_Notes
> where Batch_text='5' and Lot_Text='2443'
> I get
> Start Date
> 2005-07-11 14:59:20.000
> I also need the query to give me a column
> End Date
> 2005-07-11 15:00:50.00
> How could I do this?
>
|||A little typo:
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N2.Batch_text='5' and N2.Lot_Text='9987'
"Chris Gay" <ChrisGay@.RMTWoodworth.com> wrote in message
news:ekWcBC0hFHA.328@.tk2msftngp13.phx.gbl...
>I got it wirking.
> Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
> From IHZONE1_Notes N1, IHZONE1_Notes N2
> Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
>
> <Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>

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

Query

I am trying to make a query to get the date and time a row and the date and
time from the next row:
Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs Note_Text
Batch_Text Lot_Text Author Batch_Note Line
0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
This is what I came up with.
USE Iconics_Data
Select Source_TDate as 'Start Date'
From IHZONE1_Notes
where Batch_text='5' and Lot_Text='2443'
I get
Start Date
2005-07-11 14:59:20.000
I also need the query to give me a column
End Date
2005-07-11 15:00:50.00
How could I do this?I got it wirking.
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
<Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>I am trying to make a query to get the date and time a row and the date and
>time from the next row:
> Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs
> Note_Text Batch_Text Lot_Text Author Batch_Note Line
> 0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
> 0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
> 0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
> 0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
> This is what I came up with.
> USE Iconics_Data
> Select Source_TDate as 'Start Date'
> From IHZONE1_Notes
> where Batch_text='5' and Lot_Text='2443'
> I get
> Start Date
> 2005-07-11 14:59:20.000
> I also need the query to give me a column
> End Date
> 2005-07-11 15:00:50.00
> How could I do this?
>|||A little typo:
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N2.Batch_text='5' and N2.Lot_Text='9987'
"Chris Gay" <ChrisGay@.RMTWoodworth.com> wrote in message
news:ekWcBC0hFHA.328@.tk2msftngp13.phx.gbl...
>I got it wirking.
> Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
> From IHZONE1_Notes N1, IHZONE1_Notes N2
> Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
>
> <Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>>I am trying to make a query to get the date and time a row and the date
>>and time from the next row:
>> Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs
>> Note_Text Batch_Text Lot_Text Author Batch_Note Line
>> 0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
>> 0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
>> 0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
>> 0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
>> This is what I came up with.
>> USE Iconics_Data
>> Select Source_TDate as 'Start Date'
>> From IHZONE1_Notes
>> where Batch_text='5' and Lot_Text='2443'
>> I get
>> Start Date
>> 2005-07-11 14:59:20.000
>> I also need the query to give me a column
>> End Date
>> 2005-07-11 15:00:50.00
>> How could I do this?
>sql

Query

I am trying to make a query to get the date and time a row and the date and
time from the next row:
Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs Note_Text
Batch_Text Lot_Text Author Batch_Note Line
0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
This is what I came up with.
USE Iconics_Data
Select Source_TDate as 'Start Date'
From IHZONE1_Notes
where Batch_text='5' and Lot_Text='2443'
I get
Start Date
2005-07-11 14:59:20.000
I also need the query to give me a column
End Date
2005-07-11 15:00:50.00
How could I do this?I got it wirking.
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
<Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>I am trying to make a query to get the date and time a row and the date and
>time from the next row:
> Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs
> Note_Text Batch_Text Lot_Text Author Batch_Note Line
> 0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
> 0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
> 0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
> 0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
> This is what I came up with.
> USE Iconics_Data
> Select Source_TDate as 'Start Date'
> From IHZONE1_Notes
> where Batch_text='5' and Lot_Text='2443'
> I get
> Start Date
> 2005-07-11 14:59:20.000
> I also need the query to give me a column
> End Date
> 2005-07-11 15:00:50.00
> How could I do this?
>|||A little typo:
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N2.Batch_text='5' and N2.Lot_Text='9987'
"Chris Gay" <ChrisGay@.RMTWoodworth.com> wrote in message
news:ekWcBC0hFHA.328@.tk2msftngp13.phx.gbl...
>I got it wirking.
> Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
> From IHZONE1_Notes N1, IHZONE1_Notes N2
> Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
>
> <Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>

querry question

I have created a small querry and for some reason my container (datalist) is not reconizing the company name This is also the first time that i have created a querry with two select statements in the sproc... Does it make a difference if there is two select statements in a querry?? When populating a datalist? Not sure if i need to seperate the two statements some kind of way.. Is this ok to use with datalist? Erik.. ALTER PROCEDURE Get_Information_For_Datalist_Global_ @.IDProduct AS INT, @.IDCompany AS INT AS SELECT Product.ProductName, Product.ProductID,Product.ProductImage, Product.ProductDescription, SprocsTable.* FROM Product INNER JOIN SprocsTable ON Product.ProductID = SprocsTable.ProductID WHERE Product.ProductID = @.IDProduct SELECT Company.CompanyID, Company.CompanyName FROM COMPANY WHERE COMPANY.COMPANYID = @.IDCompany

You cannot bind two queries to the same repeater. Using a DataReader bind the first result, and then call .NextResult and then bind the dataReader to a second repeater.

queries take a long time after sp2 installation

Has anyone had thsi issue, if so what was the fix?

We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.

it went from running in 2 minutes to now taking 3 hours and 29 minutes to run. Can someone help?

Is it only certain queries running slower or is it all queries running slower? What high-level symptoms are you seeing? Does it look like CPU pressure, memory pressure, I/O pressure, blocking, etc.?

What kind of results do you get when you run these DMV queries?

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)

,'%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)

,'%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

FROM sys.dm_os_wait_stats

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

-- Isolate top waits

WITH Waits AS

(

SELECT

wait_type,

wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

FROM sys.dm_os_wait_stats

WHERE wait_type NOT LIKE '%SLEEP%'

-- filter out additional irrelevant waits

)

SELECT

W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold

ORDER BY W1.rn;

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROM sys.dm_exec_requests AS blocked_query

INNER JOIN sys.dm_exec_requests AS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNER JOIN sys.dm_os_waiting_tasks AS waits

ON waits.session_id = blocking_query.session_id

|||After the SP2 Upgrade did you update the stats and index rebuilds on the tables?. You might want to perform some optimization and see if the problem still persists.|||

If you have not done so already, try running sp_UpdateStats on your database(s). This will run UPDATE STATISTICS with the default 10% sample rate on every table in the database (which may take a while, depending on your hardware and db size).

You should always do that after upgrading to SQL Server 2005

Rebuilding (as opposed to reorganizing) indexes will also automatically update statistics.

|||Hi,

We have experienced many slowness and trouble since the SP2 was installed.
Have a look at theses 2 KB:
http://support.microsoft.com/default.aspx/kb/937745
And
http://support.microsoft.com/default.aspx/kb/933564/en-us

Last point, run the following query when your server is slow:

select type, sum(single_pages_kb+multi_pages_kb) 'total memory' from sys.dm_os_memory_clerks ORDER by 2 DESC

This will help you to know what cache type is getting most of your SQL Ram.
If it's USERSTORE_TOKENPERM, consider using Trace flag 4618. It has had good impact on our system, but still not correct the case. I have conf call with MS Support on Monday about that point and will answer in this post if I have anymore informations.

Regards,
Jeremy
|||

Jeremy - Just wondering if you had any updates. We are currently hitting this issue. The clearing of ('TokenAndPermUserStore') does not work 100% of the time. We've experienced times when the system is so busy that we can not clear it fast enough.

We are considering using the trace flag.

Thanks -

Sam.

queries take a long time after sp2 installation

Has anyone had thsi issue, if so what was the fix?

We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.

it went from running in 2 minutes to now taking 3 hours and 29 minutes to run. Can someone help?

Is it only certain queries running slower or is it all queries running slower? What high-level symptoms are you seeing? Does it look like CPU pressure, memory pressure, I/O pressure, blocking, etc.?

What kind of results do you get when you run these DMV queries?

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)

,'%signal (cpu) waits'=CAST(100.0 *SUM(signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)

,'%resource waits'=CAST(100.0 *SUM(wait_time_ms - signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

FROMsys.dm_os_wait_stats

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROMsys.dm_os_schedulers

WHERE scheduler_id < 255

-- Isolate top waits

WITH Waits AS

(

SELECT

wait_type,

wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms /SUM(wait_time_ms)OVER()AS pct,

ROW_NUMBER()OVER(ORDERBY wait_time_ms DESC)AS rn

FROMsys.dm_os_wait_stats

WHERE wait_type NOTLIKE'%SLEEP%'

-- filter out additional irrelevant waits

)

SELECT

W1.wait_type,

CAST(W1.wait_time_s ASDECIMAL(12, 2))AS wait_time_s,

CAST(W1.pct ASDECIMAL(12, 2))AS pct,

CAST(SUM(W2.pct)ASDECIMAL(12, 2))AS running_pct

FROM Waits AS W1

INNERJOIN Waits AS W2

ON W2.rn <= W1.rn

GROUPBY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVINGSUM(W2.pct)- W1.pct < 90 -- percentage threshold

ORDERBY W1.rn;

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROMsys.dm_exec_requestsAS blocked_query

INNERJOINsys.dm_exec_requestsAS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSSAPPLY

(SELECT*

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSSAPPLY

(SELECT*

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNERJOINsys.dm_os_waiting_tasksAS waits

ON waits.session_id = blocking_query.session_id

|||After the SP2 Upgrade did you update the stats and index rebuilds on the tables?. You might want to perform some optimization and see if the problem still persists.|||

If you have not done so already, try running sp_UpdateStats on your database(s). This will run UPDATE STATISTICS with the default 10% sample rate on every table in the database (which may take a while, depending on your hardware and db size).

You should always do that after upgrading to SQL Server 2005

Rebuilding (as opposed to reorganizing) indexes will also automatically update statistics.

|||Hi,

We have experienced many slowness and trouble since the SP2 was installed.
Have a look at theses 2 KB:
http://support.microsoft.com/default.aspx/kb/937745
And
http://support.microsoft.com/default.aspx/kb/933564/en-us

Last point, run the following query when your server is slow:

select type, sum(single_pages_kb+multi_pages_kb) 'total memory' from sys.dm_os_memory_clerks ORDER by 2 DESC

This will help you to know what cache type is getting most of your SQL Ram.
If it's USERSTORE_TOKENPERM, consider using Trace flag 4618. It has had good impact on our system, but still not correct the case. I have conf call with MS Support on Monday about that point and will answer in this post if I have anymore informations.

Regards,
Jeremy
|||

Jeremy - Just wondering if you had any updates. We are currently hitting this issue. The clearing of ('TokenAndPermUserStore') does not work 100% of the time. We've experienced times when the system is so busy that we can not clear it fast enough.

We are considering using the trace flag.

Thanks -

Sam.

queries take a long time after sp2 installation

Has anyone had thsi issue, if so what was the fix?

We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.

it went from running in 2 minutes to now taking 3 hours and 29 minutes to run. Can someone help?

Is it only certain queries running slower or is it all queries running slower? What high-level symptoms are you seeing? Does it look like CPU pressure, memory pressure, I/O pressure, blocking, etc.?

What kind of results do you get when you run these DMV queries?

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)

,'%signal (cpu) waits'=CAST(100.0 *SUM(signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)

,'%resource waits'=CAST(100.0 *SUM(wait_time_ms - signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

FROMsys.dm_os_wait_stats

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROMsys.dm_os_schedulers

WHERE scheduler_id < 255

-- Isolate top waits

WITH Waits AS

(

SELECT

wait_type,

wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms /SUM(wait_time_ms)OVER()AS pct,

ROW_NUMBER()OVER(ORDERBY wait_time_ms DESC)AS rn

FROMsys.dm_os_wait_stats

WHERE wait_type NOTLIKE'%SLEEP%'

-- filter out additional irrelevant waits

)

SELECT

W1.wait_type,

CAST(W1.wait_time_s ASDECIMAL(12, 2))AS wait_time_s,

CAST(W1.pct ASDECIMAL(12, 2))AS pct,

CAST(SUM(W2.pct)ASDECIMAL(12, 2))AS running_pct

FROM Waits AS W1

INNERJOIN Waits AS W2

ON W2.rn <= W1.rn

GROUPBY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVINGSUM(W2.pct)- W1.pct < 90 -- percentage threshold

ORDERBY W1.rn;

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROMsys.dm_exec_requestsAS blocked_query

INNERJOINsys.dm_exec_requestsAS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSSAPPLY

(SELECT*

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSSAPPLY

(SELECT*

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNERJOINsys.dm_os_waiting_tasksAS waits

ON waits.session_id = blocking_query.session_id

|||After the SP2 Upgrade did you update the stats and index rebuilds on the tables?. You might want to perform some optimization and see if the problem still persists.|||

If you have not done so already, try running sp_UpdateStats on your database(s). This will run UPDATE STATISTICS with the default 10% sample rate on every table in the database (which may take a while, depending on your hardware and db size).

You should always do that after upgrading to SQL Server 2005

Rebuilding (as opposed to reorganizing) indexes will also automatically update statistics.

|||Hi,

We have experienced many slowness and trouble since the SP2 was installed.
Have a look at theses 2 KB:
http://support.microsoft.com/default.aspx/kb/937745
And
http://support.microsoft.com/default.aspx/kb/933564/en-us

Last point, run the following query when your server is slow:

select type, sum(single_pages_kb+multi_pages_kb) 'total memory' from sys.dm_os_memory_clerks ORDER by 2 DESC

This will help you to know what cache type is getting most of your SQL Ram.
If it's USERSTORE_TOKENPERM, consider using Trace flag 4618. It has had good impact on our system, but still not correct the case. I have conf call with MS Support on Monday about that point and will answer in this post if I have anymore informations.

Regards,
Jeremy
|||

Jeremy - Just wondering if you had any updates. We are currently hitting this issue. The clearing of ('TokenAndPermUserStore') does not work 100% of the time. We've experienced times when the system is so busy that we can not clear it fast enough.

We are considering using the trace flag.

Thanks -

Sam.

Queries run faster with replication then without

Hello everyone,
I have a situation where I am using a Cursor to retrieve records from one table and inserting those records one at a time into another table. When I perform this action without replication, it takes longer to insert the 474 records into this table then it
does with replication. The table that is being inserted to is the table that is being replicated. It doesn't make sense. You would think that using replication would slow down this action. By contrast, if I use a simple INSERT statement without using a c
ursor (ie: Insert table2 select * from table1), the process runs much faster without replication then with replication. Any idea why?
Thanks!!
this is counter intuitive, unless the replication process is bringing the
database pages off disk and into cache thereby resulting in faster reads.
"Nupee" <anonymous@.discussions.microsoft.com> wrote in message
news:C5EDAB30-AB12-46BA-812B-1C0A6EB3562F@.microsoft.com...
> Hello everyone,
> I have a situation where I am using a Cursor to retrieve records from one
table and inserting those records one at a time into another table. When I
perform this action without replication, it takes longer to insert the 474
records into this table then it does with replication. The table that is
being inserted to is the table that is being replicated. It doesn't make
sense. You would think that using replication would slow down this action.
By contrast, if I use a simple INSERT statement without using a cursor (ie:
Insert table2 select * from table1), the process runs much faster without
replication then with replication. Any idea why?
> Thanks!!
|||Is it possible that it has something to do with the system procedures, and triggers that are added to the database through replication configuration? Either that or is there any documentation on the effects of replication of Cursors?
Thanks!!
-- Hilary Cotter wrote: --
this is counter intuitive, unless the replication process is bringing the
database pages off disk and into cache thereby resulting in faster reads.
"Nupee" <anonymous@.discussions.microsoft.com> wrote in message
news:C5EDAB30-AB12-46BA-812B-1C0A6EB3562F@.microsoft.com...[vbcol=seagreen]
> Hello everyone,
table and inserting those records one at a time into another table. When I
perform this action without replication, it takes longer to insert the 474
records into this table then it does with replication. The table that is
being inserted to is the table that is being replicated. It doesn't make
sense. You would think that using replication would slow down this action.
By contrast, if I use a simple INSERT statement without using a cursor (ie:
Insert table2 select * from table1), the process runs much faster without
replication then with replication. Any idea why?[vbcol=seagreen]

queries hanging?

I am running SQL2000, occasionally queries take a huge amount of time to
process, for some reason they seem to pause for a couple of minutes. For
example, the following query normally executes in a couple of seconds...
SELECT count(*)
FROM object ob
JOIN quantity qt ON ob.objid=qt.objid
JOIN site st ON qt.siteid=st.siteid
JOIN cussite cs ON cs.siteid=st.siteid
WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
but for some reason, today it is taking over 10 minutes!!
Running sp_lock2 reveals..
TABLE LOCKTYPE MODE
OBJECT TAB Sch-S
QUANTITY TAB Sch-S
SITE TAB Sch-S
CUSSITE TAB Sch-S
The query just sits there with those locks for over 10 minutes before coming
back with the data.
Any help much appreciated...
MarkFirst, check the query plans. Is the query using the index? Your statistic
s
might not be up to date.
Second, is there something else on that machine using all the processor and
disk? If something else is hogging all of the resources, then your query is
just going to have to wait.
I hope this helps.
--
Russel Loski, MCSD.Net
"Mark Baldwin" wrote:

> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before comi
ng
> back with the data.
> Any help much appreciated...
> Mark
>
>|||Mark Baldwin wrote:
> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before comi
ng
> back with the data.
> Any help much appreciated...
> Mark
>
Review the execution plan, make sure the proper indexes are there to
support the query, check for blocking, all the typical things...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I wish I could review the execution plan! In query analyser, selecting
"display estimated execution plan" with noexec or simply ticking the
"display estimated execution plan" option results in a long pause (10minutes
or more) before coming back with an empty execution plan and no error.
This is the same pause that affects my queries. So it's not the query thats
taking the time, its the pre processing of the query.
Best regards
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45B63900.5090009@.realsqlguy.com...
> Mark Baldwin wrote:
> Review the execution plan, make sure the proper indexes are there to
> support the query, check for blocking, all the typical things...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minut
es
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query that
s
> taking the time, its the pre processing of the query.
>
Are your statistics current? Maybe something here will help:
http://groups.google.com/group/micr...7eea6596a0b6011
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Look at your Task Manager on the *client*. Is something taking up a lot
of CPU on it? What about the server?
-Dave
Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minut
es
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query that
s
> taking the time, its the pre processing of the query.
>
-Dave Markle
http://www.markleconsulting.com/blog

queries hanging?

I am running SQL2000, occasionally queries take a huge amount of time to
process, for some reason they seem to pause for a couple of minutes. For
example, the following query normally executes in a couple of seconds...
SELECT count(*)
FROM object ob
JOIN quantity qt ON ob.objid=qt.objid
JOIN site st ON qt.siteid=st.siteid
JOIN cussite cs ON cs.siteid=st.siteid
WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
but for some reason, today it is taking over 10 minutes!!
Running sp_lock2 reveals..
TABLE LOCKTYPE MODE
OBJECT TAB Sch-S
QUANTITY TAB Sch-S
SITE TAB Sch-S
CUSSITE TAB Sch-S
The query just sits there with those locks for over 10 minutes before coming
back with the data.
Any help much appreciated...
MarkMark Baldwin wrote:
> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before coming
> back with the data.
> Any help much appreciated...
> Mark
>
Review the execution plan, make sure the proper indexes are there to
support the query, check for blocking, all the typical things...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I wish I could review the execution plan! In query analyser, selecting
"display estimated execution plan" with noexec or simply ticking the
"display estimated execution plan" option results in a long pause (10minutes
or more) before coming back with an empty execution plan and no error.
This is the same pause that affects my queries. So it's not the query thats
taking the time, its the pre processing of the query.
--
Best regards
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45B63900.5090009@.realsqlguy.com...
> Mark Baldwin wrote:
>> I am running SQL2000, occasionally queries take a huge amount of time to
>> process, for some reason they seem to pause for a couple of minutes. For
>> example, the following query normally executes in a couple of seconds...
>> SELECT count(*)
>> FROM object ob
>> JOIN quantity qt ON ob.objid=qt.objid
>> JOIN site st ON qt.siteid=st.siteid
>> JOIN cussite cs ON cs.siteid=st.siteid
>> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
>> but for some reason, today it is taking over 10 minutes!!
>> Running sp_lock2 reveals..
>> TABLE LOCKTYPE MODE
>> OBJECT TAB Sch-S
>> QUANTITY TAB Sch-S
>> SITE TAB Sch-S
>> CUSSITE TAB Sch-S
>> The query just sits there with those locks for over 10 minutes before
>> coming back with the data.
>> Any help much appreciated...
>> Mark
> Review the execution plan, make sure the proper indexes are there to
> support the query, check for blocking, all the typical things...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minutes
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query thats
> taking the time, its the pre processing of the query.
>
Are your statistics current? Maybe something here will help:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/90a81b1ebd25891d/c7eea6596a0b6011
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Look at your Task Manager on the *client*. Is something taking up a lot
of CPU on it? What about the server?
-Dave
Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minutes
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query thats
> taking the time, its the pre processing of the query.
>
-Dave Markle
http://www.markleconsulting.com/blogsql

queries hanging?

I am running SQL2000, occasionally queries take a huge amount of time to
process, for some reason they seem to pause for a couple of minutes. For
example, the following query normally executes in a couple of seconds...
SELECT count(*)
FROM object ob
JOIN quantity qt ON ob.objid=qt.objid
JOIN site st ON qt.siteid=st.siteid
JOIN cussite cs ON cs.siteid=st.siteid
WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
but for some reason, today it is taking over 10 minutes!!
Running sp_lock2 reveals..
TABLE LOCKTYPE MODE
OBJECT TAB Sch-S
QUANTITY TAB Sch-S
SITE TAB Sch-S
CUSSITE TAB Sch-S
The query just sits there with those locks for over 10 minutes before coming
back with the data.
Any help much appreciated...
Mark
First, check the query plans. Is the query using the index? Your statistics
might not be up to date.
Second, is there something else on that machine using all the processor and
disk? If something else is hogging all of the resources, then your query is
just going to have to wait.
I hope this helps.
Russel Loski, MCSD.Net
"Mark Baldwin" wrote:

> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before coming
> back with the data.
> Any help much appreciated...
> Mark
>
>
|||Mark Baldwin wrote:
> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before coming
> back with the data.
> Any help much appreciated...
> Mark
>
Review the execution plan, make sure the proper indexes are there to
support the query, check for blocking, all the typical things...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I wish I could review the execution plan! In query analyser, selecting
"display estimated execution plan" with noexec or simply ticking the
"display estimated execution plan" option results in a long pause (10minutes
or more) before coming back with an empty execution plan and no error.
This is the same pause that affects my queries. So it's not the query thats
taking the time, its the pre processing of the query.
Best regards
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45B63900.5090009@.realsqlguy.com...
> Mark Baldwin wrote:
> Review the execution plan, make sure the proper indexes are there to
> support the query, check for blocking, all the typical things...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minutes
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query thats
> taking the time, its the pre processing of the query.
>
Are your statistics current? Maybe something here will help:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/90a81b1ebd25891d/c7eea6596a0b6011
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Look at your Task Manager on the *client*. Is something taking up a lot
of CPU on it? What about the server?
-Dave
Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minutes
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query thats
> taking the time, its the pre processing of the query.
>
-Dave Markle
http://www.markleconsulting.com/blog

Queried parameters

Hello,

I need to be able to set the date parameters of a report dynamically when it is run based on system time. The problem I am having is being able to compare the dates (StartDate & EndDate) against [Service Date 1]. Essentially this report will only pull the current month's data.

The date fields being created with the GETDATE, DATEADD & DATEDIFF functions are working correctly. Do I need to create a separate dataset to be able to run the parameters automatically in the actual report?

Any help would be greatly appreciated!

SELECT TodaysDate =GetDate()-2,dbo.[Billing Detail].[Service Date 1], DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0) AS StartDate, DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0)) AS EndDate, dbo.[Billing Detail].Billing, dbo.[Billing Detail].Chart, dbo.[Billing Detail].Item,
dbo.[Billing Detail].[Sub Item], dbo.Patient.[Patient Code], dbo.Patient.[Patient Type], dbo.[Billing Header].Charges, dbo.Practice.Name
FROM dbo.[Billing Detail] INNER JOIN
dbo.Patient ON dbo.[Billing Detail].Chart = dbo.Patient.[Chart Number] INNER JOIN
dbo.[Billing Header] ON dbo.[Billing Detail].Billing = dbo.[Billing Header].Billing CROSS JOIN
dbo.Practice
WHERE (dbo.[Billing Detail].Item = 0) AND (dbo.[Billing Detail].[Sub Item] = 0) AND (dbo.[Billing Detail].[Service Date 1] Between StartDate AND EndDate

Phorest,

You should be able to add the parameters to your query. If you are going against SQL Server, you can replace your parameters with @.StartDate AND @.EndDate. Then in the properies of the dataset, you can assign those parameters to Parameters!StartDate.Value and Parameters!EndDate.Value, respectively.

Jessica

|||

Thanks for your reply!

OK,

I think what I need to do is write the expression as a non-queried default value. However when I paste in what I know works in SQL Management Studio it returns an error "Name 'mm' is not declared"

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

I tried putting an integer after DATEADD(mm, X , 102 DATEDIFF... but i can't get beyond intellisense. How can I fix my expression to work with Reporting Services?

What I need is to have expressions to choose the first day of the month to the last day of the same month compared to NOW()

|||

Apparently that is the trick to use non-queried default values as an expression, However what I posted yesterday will not work as an expression due to the expressions limitations in SSRS:

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

Now I am using:

<@.StartDate> =DATEADD("D", -30, NOW())

<@.EndDate> =DATEADD("D", 1, NOW())

After much searching and experimentation I can get this to work well, but it isn't exactly what I want. Does any one have any tips as to being able to write the expression to select the first day of the current month and last day of the month?

It seems to be just beyond my grasp at this time...

Thanks!

|||

Phorest,

I'm afraid I misunderstood what you're trying to do. If you want a query that returns rows where the [Billing Detail].[Service Date 1] is between the start and the end of the current month, you can do that all in SQL.

It would look something similar to:

WHERE dbo.[Billing Detail].[Service Date 1]

BETWEEN dateadd(mm, datediff(mm,0,getdate()), 0)

AND dateadd(ms,-3,dateadd(mm, datediff(m,0,getdate() ) + 1, 0))

Does that work for you?

Jessica

|||

I'll have to try that in the SQL, though I was more after an expression more as a datetime datatype so it picks all the dates in the current month only and the user can then adjust the parameter manually after the initial running of the report if they so choose.

Thanks!

|||

I found what I was looking for here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1581230&SiteID=1

In the Report Parameters properties I set the DataType to DateTime and using the Default Values, Non-Queried radio button set the expressions like the following:

@.StartDate =DateSerial(Year(NOW()), Month(NOW)) +0,1) gives me the first date of the current month.

@.EndDate =DateSerial(Year(NOW()), Month(NOW)) +1,0) gives me the last date of the current month.

All is wellnow!

Quaterly Data From Selected Range

Hi all,

I have one table with period data time and composite integer
now i want to select the sum(composite) for quaterly like
if user selects 200001 to 200303 i want all the quater which fall under the selection/
in this we have q1.q2.q3.q4 of 2000 and
q1.q2.q3.q4 of 2001 and q1.q2.q3.q4 of 2002 and q1 2003...i want all those quaters..each one row...
so how to do that let me know the query ...did you try this (http://dbforums.com/showthread.php?threadid=751460) ?

rudy|||Like R937 said (with one corection of mine), try this:

select year(period) as year,cast( (month(period)+2) / 3 as integer ) as quarter, sum(composite)
from cdh_price_gap
group by year(period),cast( (month(period)+2) / 3 as integer )

or

select year(period) as year,cast( ((month(period)-0.3)/ 3)+1 as integer ) as quarter, sum(composite)
from cdh_price_gap
group by year(period),cast( ((month(period)-0.3)/ 3)+1 as integer )

IONUT CALIN

PS
From what I understant you need you need separate sums for q1 2002 and q2 2003 (in this case you alo have to group by year of date field)

Good Luck!

Quarter in the group by

How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank youHi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegroups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
> > How can I create stored procedure with data grouped by quarter?
> > I am converting database from Access to SQL and learn SQL in the
same
> > time.
> > In Access that is simple because there is function for that.
> >
> > I was looking in the different news groups and found few answers
like
> > truncating data, but cannot find it it T-SQL.
> >
> > Thank you
> >
> >

Qualified Joins

I need a query that compares two tables with times in them and returns only
the common records with the lowest time.
Table1
recordID ArrivalTime
12345 12:01am
12346 12:30am
12347 12:45am
Table2
recordID ArrivalTime
12345 12:03am
12346 12:29am
12347 12:44am
The result should be
recordID ArrivalTime
12345 12:01am
12346 12:29am
12347 12:44am
Can someone point me in the right direction please?Hi Dave,
SELECT recordID,MIN(ArrivalTime)
FROM
(
SELECT recordID,ArrivalTime
FROM TABLE1
UNION
SELECT recordID,ArrivalTime
FROM TABLE2
) SubQuery
GROUP BY recordID
--OR
SELECT
recordID ,
CASE WHEN T1.ArrivalTime < T2.ArrivalTime THEN T1.ArrivalTime
ELSE T2.ArrivalTime END AS ArrivalTime
FROM TABLE1 T1
FULL OUTER JOIN TABLE2 T2
ON T1.recordid = T2.recordid
HTH, jens Suessmeyer.|||The 2nd query doesn't handle nulls...
SELECT
COALESCE( T1.recordid , T2.recordid ) AS recordid
, (CASE
WHEN T2.ArrivalTime IS NULL OR T1.ArrivalTime < T2.ArrivalTime ) THEN
T1.ArrivalTime
ELSE T2.ArrivalTime
END) AS ArrivalTime
FROM TABLE1 T1
FULL OUTER JOIN TABLE2 T2
ON T1.recordid = T2.recordid
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1131981403.240416.108000@.g47g2000cwa.googlegroups.com...
> Hi Dave,
>
> SELECT recordID,MIN(ArrivalTime)
> FROM
> (
> SELECT recordID,ArrivalTime
> FROM TABLE1
> UNION
> SELECT recordID,ArrivalTime
> FROM TABLE2
> ) SubQuery
> GROUP BY recordID
>
> --OR
> SELECT
> recordID ,
> CASE WHEN T1.ArrivalTime < T2.ArrivalTime THEN T1.ArrivalTime
> ELSE T2.ArrivalTime END AS ArrivalTime
> FROM TABLE1 T1
> FULL OUTER JOIN TABLE2 T2
> ON T1.recordid = T2.recordid
>
> HTH, jens Suessmeyer.
>

QA database change take too much time

Hi,
MSSQL 2K, SP4 on XP sp2
While connected to local SQL server, when I click on DatabBase drop down
Combo in Query Analyzer, it takes around 10+ seconds before displaying the
databases to select, however if I connect to another SQL server on my office
LAN, then list appears with no time.
appreciate your help to fix this.
Thanks
Falik
To add on to Kalen;
You might be you have enabled the database option "AUTOCLOSE". THis will
close
the MDF and LDF as soon as the last user logs of the database. Again the MDF
and LDF will be opened once a user logins to the database.
How to check this option is checked:-
1. Enterprise manager -- Databases -- Select the database
2. Right click and select properties -- Choose options
3. Chek whether AUTOCLOSE option is "checked". If yes then remove it
This will ensure that MDF and LDF will never closed as soon as last user
logs off.
Thanks
Hari
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>