query help

ok, I am rather a novice when it comes to SQL queries, so please bear with
me.
I will only be running this table on one table.
I have about 10 columns in teh table
just to make things easy to understand, I will try to make this cleaar and
straight forward:
I have table A and I run the following query on it:
Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
clmB is formated in SQL as DateTime
anyway, so I have several rows returned to me.
For example, it will read:
Shipped, 7/14/2007, 82465,8202007
Shipped,7/16/2007,123456,8202007
on-hand,7/14/2007,82465,
on-hand,7/13/2007,12345,
now, if you noticed, the first and forth items are the same, except the 3rd
item says on-hand instead of shipped.
What I am looking for is a query that would NOT include the items that say
ON-HAND, if the other items are teh same.
Meaning that in my example, it would have only listed items 1,2,4
also, when a row is set as on-hand, there are a couple of cells that will
not be populated.
oy, I hope I made it easy enough to understand.
thanks in advanceThis may give you some ideas to get you started.
SELECT colA, colB, colC,
CASE WHEN colA <> 'on-hand'
THEN colD
ELSE NULL
END
FROM TheTable as A
WHERE clmA <> 'on-hand'
OR NOT EXISTS
(SELECT * FROM TheTable as B
WHERE A.clmB = B.clmB
AND A.clmC = B.clmC
AND B.clmA <> 'on-hand')
Roy Harvey
Beacon Falls, CT
On Tue, 21 Aug 2007 13:19:56 -0700, "Johnfli" <john@.ivhs.us> wrote:
>ok, I am rather a novice when it comes to SQL queries, so please bear with
>me.
>I will only be running this table on one table.
>I have about 10 columns in teh table
>just to make things easy to understand, I will try to make this cleaar and
>straight forward:
>I have table A and I run the following query on it:
>Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
>clmB is formated in SQL as DateTime
>anyway, so I have several rows returned to me.
>For example, it will read:
>Shipped, 7/14/2007, 82465,8202007
>Shipped,7/16/2007,123456,8202007
>on-hand,7/14/2007,82465,
>on-hand,7/13/2007,12345,
>
>now, if you noticed, the first and forth items are the same, except the 3rd
>item says on-hand instead of shipped.
>What I am looking for is a query that would NOT include the items that say
>ON-HAND, if the other items are teh same.
>Meaning that in my example, it would have only listed items 1,2,4
>also, when a row is set as on-hand, there are a couple of cells that will
>not be populated.
>oy, I hope I made it easy enough to understand.
>thanks in advance
>|||WOW!!
That was fast, I will give it a shot. Thank you!!
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:krimc3dfu0c6fipivj3v679bt49u0vfkht@.4ax.com...
> This may give you some ideas to get you started.
> SELECT colA, colB, colC,
> CASE WHEN colA <> 'on-hand'
> THEN colD
> ELSE NULL
> END
> FROM TheTable as A
> WHERE clmA <> 'on-hand'
> OR NOT EXISTS
> (SELECT * FROM TheTable as B
> WHERE A.clmB = B.clmB
> AND A.clmC = B.clmC
> AND B.clmA <> 'on-hand')
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Aug 2007 13:19:56 -0700, "Johnfli" <john@.ivhs.us> wrote:
>>ok, I am rather a novice when it comes to SQL queries, so please bear with
>>me.
>>I will only be running this table on one table.
>>I have about 10 columns in teh table
>>just to make things easy to understand, I will try to make this cleaar and
>>straight forward:
>>I have table A and I run the following query on it:
>>Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
>>clmB is formated in SQL as DateTime
>>anyway, so I have several rows returned to me.
>>For example, it will read:
>>Shipped, 7/14/2007, 82465,8202007
>>Shipped,7/16/2007,123456,8202007
>>on-hand,7/14/2007,82465,
>>on-hand,7/13/2007,12345,
>>
>>now, if you noticed, the first and forth items are the same, except the
>>3rd
>>item says on-hand instead of shipped.
>>What I am looking for is a query that would NOT include the items that say
>>ON-HAND, if the other items are teh same.
>>Meaning that in my example, it would have only listed items 1,2,4
>>also, when a row is set as on-hand, there are a couple of cells that will
>>not be populated.
>>oy, I hope I made it easy enough to understand.
>>thanks in advance|||hmmmm
oy, this is kicking my rump here.
haven't gotten it to work, but to try to make it a little more clear...
lets say I have 2 rows, they have almost teh exact same info. The only
difference is that when colA says ON-HAND colD, colE and colF will be null
if colA says SHIPPED all teh other cols will be the same Excpet colD, colE
and colF will not be null
This table gets it's data by reading a txt file once a day with shipment
status info. Normally, I would have it update an exsisting record as the
STATUS (colA) changed from on-hand top shipped. BUT I can't do that becsue
the powers that be, want to be able to see teh records of when it was
on-hand and then when it shipped.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:krimc3dfu0c6fipivj3v679bt49u0vfkht@.4ax.com...
> This may give you some ideas to get you started.
> SELECT colA, colB, colC,
> CASE WHEN colA <> 'on-hand'
> THEN colD
> ELSE NULL
> END
> FROM TheTable as A
> WHERE clmA <> 'on-hand'
> OR NOT EXISTS
> (SELECT * FROM TheTable as B
> WHERE A.clmB = B.clmB
> AND A.clmC = B.clmC
> AND B.clmA <> 'on-hand')
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Aug 2007 13:19:56 -0700, "Johnfli" <john@.ivhs.us> wrote:
>>ok, I am rather a novice when it comes to SQL queries, so please bear with
>>me.
>>I will only be running this table on one table.
>>I have about 10 columns in teh table
>>just to make things easy to understand, I will try to make this cleaar and
>>straight forward:
>>I have table A and I run the following query on it:
>>Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
>>clmB is formated in SQL as DateTime
>>anyway, so I have several rows returned to me.
>>For example, it will read:
>>Shipped, 7/14/2007, 82465,8202007
>>Shipped,7/16/2007,123456,8202007
>>on-hand,7/14/2007,82465,
>>on-hand,7/13/2007,12345,
>>
>>now, if you noticed, the first and forth items are the same, except the
>>3rd
>>item says on-hand instead of shipped.
>>What I am looking for is a query that would NOT include the items that say
>>ON-HAND, if the other items are teh same.
>>Meaning that in my example, it would have only listed items 1,2,4
>>also, when a row is set as on-hand, there are a couple of cells that will
>>not be populated.
>>oy, I hope I made it easy enough to understand.
>>thanks in advance|||OK, I think I got it to work, but now I can't get my DATE clause in there to
work.
clmB is a date, and I want everything from 8/8/2007 and newer
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:krimc3dfu0c6fipivj3v679bt49u0vfkht@.4ax.com...
> This may give you some ideas to get you started.
> SELECT colA, colB, colC,
> CASE WHEN colA <> 'on-hand'
> THEN colD
> ELSE NULL
> END
> FROM TheTable as A
> WHERE clmA <> 'on-hand'
> OR NOT EXISTS
> (SELECT * FROM TheTable as B
> WHERE A.clmB = B.clmB
> AND A.clmC = B.clmC
> AND B.clmA <> 'on-hand')
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Aug 2007 13:19:56 -0700, "Johnfli" <john@.ivhs.us> wrote:
>>ok, I am rather a novice when it comes to SQL queries, so please bear with
>>me.
>>I will only be running this table on one table.
>>I have about 10 columns in teh table
>>just to make things easy to understand, I will try to make this cleaar and
>>straight forward:
>>I have table A and I run the following query on it:
>>Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
>>clmB is formated in SQL as DateTime
>>anyway, so I have several rows returned to me.
>>For example, it will read:
>>Shipped, 7/14/2007, 82465,8202007
>>Shipped,7/16/2007,123456,8202007
>>on-hand,7/14/2007,82465,
>>on-hand,7/13/2007,12345,
>>
>>now, if you noticed, the first and forth items are the same, except the
>>3rd
>>item says on-hand instead of shipped.
>>What I am looking for is a query that would NOT include the items that say
>>ON-HAND, if the other items are teh same.
>>Meaning that in my example, it would have only listed items 1,2,4
>>also, when a row is set as on-hand, there are a couple of cells that will
>>not be populated.
>>oy, I hope I made it easy enough to understand.
>>thanks in advance|||On Tue, 21 Aug 2007 14:03:34 -0700, "Johnfli" <john@.ivhs.us> wrote:
>OK, I think I got it to work, but now I can't get my DATE clause in there to
>work.
>clmB is a date, and I want everything from 8/8/2007 and newer
Hopefully clmB is of the datetime or smalldatetime datatype, in which
case I would expect this to work:
WHERE clmB >= '20070808'
If clmB is a string life is not so simple.
WHERE CONVERT(datetime,clmB) >= '20070808'
The really unfortunate part of that approach is that it can not take
advantage of an index on clmB, as it might if clmB were a datetime
column. Also, if any rows have data in clmB that can NOT be converted
to datetime it will fail. The workaround for THAT might be something
like:
WHERE CASE WHEN ISDATE(clmB) = 1
THEN CONVERT(datetime,clmB)
ELSE NULL
END >= '20070808'
Roy Harvey
Beacon Falls, CT|||as luck would have it, clmB is formated as DateTime.
The query I am running is:
SELECT Status, RecDate, Container, [PO Number], Shipment, Style,
Cartons, Qty, OSDNote, OutTrailer, ShipDate, SentEDI,
CASE WHEN status <> 'ON-hand' THEN sentedi ELSE NULL
END AS Expr1
FROM jcrew A
WHERE (Status <> 'ON-hand') AND (RecDate >= '8/10/2007') OR
(NOT EXISTS
(SELECT *
FROM jcrew AS B
WHERE a.[po number] = B.[po number] AND
a.shipment = B.shipment AND a.style = B.style AND a.cartons = B.cartons AND
a.qty = B.qty AND
b.status <> 'on-hand'))
****Please note that in the CASE statement, SQL adds the AS Expr1 on it's
own. *******
RecDate, is the clomun name that I substituted as clmB.
But when I run the query, it returns all dates, not just the ones 8/10/2007
and newer.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:c3qmc3pb1nckesfjlhg5coaqofckvfc7og@.4ax.com...
> On Tue, 21 Aug 2007 14:03:34 -0700, "Johnfli" <john@.ivhs.us> wrote:
>>OK, I think I got it to work, but now I can't get my DATE clause in there
>>to
>>work.
>>clmB is a date, and I want everything from 8/8/2007 and newer
> Hopefully clmB is of the datetime or smalldatetime datatype, in which
> case I would expect this to work:
> WHERE clmB >= '20070808'
> If clmB is a string life is not so simple.
> WHERE CONVERT(datetime,clmB) >= '20070808'
> The really unfortunate part of that approach is that it can not take
> advantage of an index on clmB, as it might if clmB were a datetime
> column. Also, if any rows have data in clmB that can NOT be converted
> to datetime it will fail. The workaround for THAT might be something
> like:
> WHERE CASE WHEN ISDATE(clmB) = 1
> THEN CONVERT(datetime,clmB)
> ELSE NULL
> END >= '20070808'
> Roy Harvey
> Beacon Falls, CT|||On Wed, 22 Aug 2007 09:11:06 -0700, "Johnfli" <john@.ivhs.us> wrote:
>as luck would have it, clmB is formated as DateTime.
DATETIME is not a format, it is a data type.
>The query I am running is:
>SELECT Status, RecDate, Container, [PO Number], Shipment, Style,
>Cartons, Qty, OSDNote, OutTrailer, ShipDate, SentEDI,
> CASE WHEN status <> 'ON-hand' THEN sentedi ELSE NULL
>END AS Expr1
>FROM jcrew A
>WHERE (Status <> 'ON-hand') AND (RecDate >= '8/10/2007') OR
> (NOT EXISTS
> (SELECT *
> FROM jcrew AS B
> WHERE a.[po number] = B.[po number] AND
>a.shipment = B.shipment AND a.style = B.style AND a.cartons = B.cartons AND
>a.qty = B.qty AND
> b.status <> 'on-hand'))
>****Please note that in the CASE statement, SQL adds the AS Expr1 on it's
>own. *******
Some front-end tool adds the AS Expr1, not SQL Server.
And now I see the problem with filter on date: the OR test. I don't
know what was intended, but I think the problem is that the OR test
for the NOT EXISTS is an alternative to both the Status test and the
RecDate test. If the NOT EXISTS test passes, the row passes. When
you have AND mixed with OR, always use (parentheses) to make clear
what works with what.
Roy Harvey
Beacon Falls, CT

Query help

I am trying to find invalid dates in a column after loading from a text file
but the queries I have doesn't give me any result.
Because of the error, I have loaded column as a varchar rather than datetime
so that I can find the invalid dates. My query is:
Select col from dbo.mytable
where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- For
month
-----
Select col from dbo.mytable
where substr(col, 4, 2) NOT in
(01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31) -- For Day
-----
Select col from dbo.mytable
where substr(col, 7, 2) NOT in (19,20) -- For year
-----
Typical dates in the text file are
01/12/1958
09/05/2007
04/23/1978
12/28/2003
01/01/1900
If I try to change the column data type from varchar to datetime I get an
error like 'can not convert to datetime, date is out of range'.
Thanks for any help.Well, I can think of a procedural approach.
Load the data as you have done and create a table variable with a datetime
column.
For each row, attempt an insert, or update to the table variable (put a
dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that key
somewhere else. Or on success, move the row to the final table, or choose
your own logic.
All that said, I was pretty sure DTS did this kind of thing for you.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
>I am trying to find invalid dates in a column after loading from a text
>file
> but the queries I have doesn't give me any result.
> Because of the error, I have loaded column as a varchar rather than
> datetime
> so that I can find the invalid dates. My query is:
> Select col from dbo.mytable
> where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
> For
> month
> -----
> Select col from dbo.mytable
> where substr(col, 4, 2) NOT in
> (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
> -- For Day
> -----
> Select col from dbo.mytable
> where substr(col, 7, 2) NOT in (19,20) -- For year
> -----
> Typical dates in the text file are
> 01/12/1958
> 09/05/2007
> 04/23/1978
> 12/28/2003
> 01/01/1900
> If I try to change the column data type from varchar to datetime I get an
> error like 'can not convert to datetime, date is out of range'.
> Thanks for any help.|||The message from DTS is not what the data is. It is something like this (I
don't have the exact error):
Can not insert data Source column (Column number 70) data type string -
destination column (column_name) data type
DATE..................something like that which doesn't tell you what
the exact invalid date is. If the data is small, you can scroll it up and
down and find it but when it is 2.4 million rows, it is hard to find it
manually or when the text file is ~800 MB.........
"Jay" wrote:
> Well, I can think of a procedural approach.
> Load the data as you have done and create a table variable with a datetime
> column.
> For each row, attempt an insert, or update to the table variable (put a
> dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that key
> somewhere else. Or on success, move the row to the final table, or choose
> your own logic.
> All that said, I was pretty sure DTS did this kind of thing for you.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
> >I am trying to find invalid dates in a column after loading from a text
> >file
> > but the queries I have doesn't give me any result.
> >
> > Because of the error, I have loaded column as a varchar rather than
> > datetime
> > so that I can find the invalid dates. My query is:
> >
> > Select col from dbo.mytable
> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
> > For
> > month
> > -----
> > Select col from dbo.mytable
> > where substr(col, 4, 2) NOT in
> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
> > -- For Day
> > -----
> > Select col from dbo.mytable
> > where substr(col, 7, 2) NOT in (19,20) -- For year
> > -----
> >
> > Typical dates in the text file are
> >
> > 01/12/1958
> > 09/05/2007
> > 04/23/1978
> > 12/28/2003
> > 01/01/1900
> >
> > If I try to change the column data type from varchar to datetime I get an
> > error like 'can not convert to datetime, date is out of range'.
> >
> > Thanks for any help.
>
>|||I've only used DTS when I had full control of the complete data stream, but
I though I remembered something about exceptions.
Still, if that isn't working for you and no one can help you with exception
filters in DTS, write the import filter yourself.
You could also try:
select *
from table
where datestr not LIKE '[0-1][0-9]/[[0-3][0-9]/[1-2][09][0-9][0-9]'
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:91B0F853-FD70-415D-A31E-17CE8EDC4E0E@.microsoft.com...
> The message from DTS is not what the data is. It is something like this (I
> don't have the exact error):
> Can not insert data Source column (Column number 70) data type string -
> destination column (column_name) data type
> DATE..................something like that which doesn't tell you what
> the exact invalid date is. If the data is small, you can scroll it up and
> down and find it but when it is 2.4 million rows, it is hard to find it
> manually or when the text file is ~800 MB.........
>
> "Jay" wrote:
>> Well, I can think of a procedural approach.
>> Load the data as you have done and create a table variable with a
>> datetime
>> column.
>> For each row, attempt an insert, or update to the table variable (put a
>> dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that
>> key
>> somewhere else. Or on success, move the row to the final table, or choose
>> your own logic.
>> All that said, I was pretty sure DTS did this kind of thing for you.
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
>> >I am trying to find invalid dates in a column after loading from a text
>> >file
>> > but the queries I have doesn't give me any result.
>> >
>> > Because of the error, I have loaded column as a varchar rather than
>> > datetime
>> > so that I can find the invalid dates. My query is:
>> >
>> > Select col from dbo.mytable
>> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
>> > For
>> > month
>> > -----
>> > Select col from dbo.mytable
>> > where substr(col, 4, 2) NOT in
>> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
>> > -- For Day
>> > -----
>> > Select col from dbo.mytable
>> > where substr(col, 7, 2) NOT in (19,20) -- For year
>> > -----
>> >
>> > Typical dates in the text file are
>> >
>> > 01/12/1958
>> > 09/05/2007
>> > 04/23/1978
>> > 12/28/2003
>> > 01/01/1900
>> >
>> > If I try to change the column data type from varchar to datetime I get
>> > an
>> > error like 'can not convert to datetime, date is out of range'.
>> >
>> > Thanks for any help.
>>|||On Aug 28, 4:40 am, DXC <D...@.discussions.microsoft.com> wrote:
> I am trying to find invalid dates in a column after loading from a text f=ile
> but the queries I have doesn't give me any result.
> Because of the error, I have loaded column as a varchar rather than datet=ime
> so that I can find the invalid dates. My query is:
> Select col from dbo.mytable
> where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- F=or
> month
> ----=--=AD--
> Select col from dbo.mytable
> where substr(col, 4, 2) NOT in
> (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,=25=AD,26,27,28,29,30,31) -- For Day
> ----=--=AD--
> Select col from dbo.mytable
> where substr(col, 7, 2) NOT in (19,20) -- For year
> ----=--=AD--
> Typical dates in the text file are
> 01/12/1958
> 09/05/2007
> 04/23/1978
> 12/28/2003
> 01/01/1900
> If I try to change the column data type from varchar to datetime I get an
> error like 'can not convert to datetime, date is out of range'.
> Thanks for any help.
Hi, there is an isdate function. Find the invalid dates and handle
them separately. HTH.|||That did it............Thanks..........
"SB" wrote:
> On Aug 28, 4:40 am, DXC <D...@.discussions.microsoft.com> wrote:
> > I am trying to find invalid dates in a column after loading from a text file
> > but the queries I have doesn't give me any result.
> >
> > Because of the error, I have loaded column as a varchar rather than datetime
> > so that I can find the invalid dates. My query is:
> >
> > Select col from dbo.mytable
> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- For
> > month
> > ----
> > Select col from dbo.mytable
> > where substr(col, 4, 2) NOT in
> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25-,26,27,28,29,30,31) -- For Day
> > ----
> > Select col from dbo.mytable
> > where substr(col, 7, 2) NOT in (19,20) -- For year
> > ----
> >
> > Typical dates in the text file are
> >
> > 01/12/1958
> > 09/05/2007
> > 04/23/1978
> > 12/28/2003
> > 01/01/1900
> >
> > If I try to change the column data type from varchar to datetime I get an
> > error like 'can not convert to datetime, date is out of range'.
> >
> > Thanks for any help.
> Hi, there is an isdate function. Find the invalid dates and handle
> them separately. HTH.
>

Query Hangs when Parameters are scaled down.

I have a weird problem in Sql Server 2005 that I need some help on.
Below
you'll find two queries. Both queries are exactly the same; they query
the
same database, query for the same info, same sql statement, same where
clause... The first query (with the longer date range) runs perfectly
against my databases. The
second query is as I've said exactly the same, the only difference is
that
the date range is much smaller and is infact included in the first
query. The
problem with this query is that it seems to churn away but do nothing.
It
seems to execute my query but it never finishes. Last time I cancelled
it after +2 hours. Does anybody why I see this behavior? Is there
something that I can do about it? Since it never seems to finish, I'm
not able to generate a execute plan for it to see what the problem is.
Thanks in advance :P
THE SANE QUERY:
------
select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
sum(T2."TRANSACTION_AMT") AS "c5"
from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT " T2,
"Accounting"."ACCOUNTING"."TIME_DIM" T1,
"Accounting"."ACCOUNTING"."GL_DIM" T4,
"Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
"Accounting"."ACCOUNTING"."LOCATION_SUPER_TABL E" T3 on
substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" =
T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" =
2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
('430', '431') and T2."TRANS_POST_DATE" between '2006-07-01
00:00:00.000' and '2006-12-31 00:00:00.000'
group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
order by 1 asc , 2 asc , 3 asc
------
THE BAD QUERY:
------
select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
sum(T2."TRANSACTION_AMT") AS "c5"
from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT " T2,
"Accounting"."ACCOUNTING"."TIME_DIM" T1,
"Accounting"."ACCOUNTING"."GL_DIM" T4,
"Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
"Accounting"."ACCOUNTING"."LOCATION_SUPER_TABL E" T3 on
substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" =
T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" =
2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
('430', '431') and T2."TRANS_POST_DATE" between '2006-12-01
00:00:00.000' and '2006-12-31 00:00:00.000'
group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
order by 1 asc , 2 asc , 3 asc
P.S.
I have already reindexed the tables, cleared the cached query plans and
updated the statistics with no change in the outcome.
|||swaroop.atre@.gmail.com wrote:
> I have a weird problem in Sql Server 2005 that I need some help on.
> Below
> you'll find two queries. Both queries are exactly the same; they query
> the
> same database, query for the same info, same sql statement, same where
> clause... The first query (with the longer date range) runs perfectly
> against my databases. The
> second query is as I've said exactly the same, the only difference is
> that
> the date range is much smaller and is infact included in the first
> query. The
> problem with this query is that it seems to churn away but do nothing.
> It
> seems to execute my query but it never finishes. Last time I cancelled
> it after +2 hours. Does anybody why I see this behavior? Is there
> something that I can do about it? Since it never seems to finish, I'm
> not able to generate a execute plan for it to see what the problem is.
> Thanks in advance :P
> THE SANE QUERY:
> ------
> select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
> T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
> sum(T2."TRANSACTION_AMT") AS "c5"
> from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT " T2,
> "Accounting"."ACCOUNTING"."TIME_DIM" T1,
> "Accounting"."ACCOUNTING"."GL_DIM" T4,
> "Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
> "Accounting"."ACCOUNTING"."LOCATION_SUPER_TABL E" T3 on
> substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
> where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" =
> T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" =
> 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
> ('430', '431') and T2."TRANS_POST_DATE" between '2006-07-01
> 00:00:00.000' and '2006-12-31 00:00:00.000'
> group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
> order by 1 asc , 2 asc , 3 asc
> ------
> THE BAD QUERY:
> ------
> select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
> T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
> sum(T2."TRANSACTION_AMT") AS "c5"
> from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT " T2,
> "Accounting"."ACCOUNTING"."TIME_DIM" T1,
> "Accounting"."ACCOUNTING"."GL_DIM" T4,
> "Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
> "Accounting"."ACCOUNTING"."LOCATION_SUPER_TABL E" T3 on
> substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
> where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" =
> T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" =
> 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
> ('430', '431') and T2."TRANS_POST_DATE" between '2006-12-01
> 00:00:00.000' and '2006-12-31 00:00:00.000'
> group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
> order by 1 asc , 2 asc , 3 asc
>
Compare the ESTIMATED execution plans for the two queries - what's the
difference between them? Where is the time being spent?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Tracy,
They both have the exact same execution plan (which they should
considering its just a change in the value for a conditional
expression). The expected usage is largely with the lookup for the
transaction data table which uses a clustered index.
Swaroop
Tracy McKibben wrote:
> swaroop.atre@.gmail.com wrote:
> Compare the ESTIMATED execution plans for the two queries - what's the
> difference between them? Where is the time being spent?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||UPDATE:
The execution plans changed once I updated the statistics on the dbases
again.
swaroop.atre@.gmail.com wrote:[vbcol=seagreen]
> Tracy,
> They both have the exact same execution plan (which they should
> considering its just a change in the value for a conditional
> expression). The expected usage is largely with the lookup for the
> transaction data table which uses a clustered index.
> Swaroop
>
> Tracy McKibben wrote:
|||swaroop.atre@.gmail.com wrote:
> UPDATE:
> The execution plans changed once I updated the statistics on the dbases
> again.
>
Did the performance change as well?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Adding in the index on one of the tables altered the performance.
However on two other queries with the same problem they have no effect.
All tables being used are indexed.
Tracy McKibben wrote:
> swaroop.atre@.gmail.com wrote:
> Did the performance change as well?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||swaroop.atre@.gmail.com wrote:
> Adding in the index on one of the tables altered the performance.
> However on two other queries with the same problem they have no effect.
> All tables being used are indexed.
Review the execution plans. The queries may "appear" to have the same
problem, i.e. slow performance, but their indexing needs may be totally
different.
A good index will allow the WHERE clause to filter out unwanted rows as
quickly as possible, and may even provide all of the data necessary to
prevent a second trip to the table.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||The query is actually making a single trip to each table that it needs
information from.
The query in a jist gathers information from six tables the biggest one
being 150M records or so with a clustered unique index while the other
5 have non clustered non unique indexes. As long as the query looks for
data for the month of dec it never returns (the date constraint is
contained in the big table) however if i expand the range to include
dec but not just dec it comes back with the data i seek. The execution
plans for the two are very different and I think that might be because
the new one uses the indexes while the old one in the cache dosnt.
Tracy McKibben wrote:
> swaroop.atre@.gmail.com wrote:
> Review the execution plans. The queries may "appear" to have the same
> problem, i.e. slow performance, but their indexing needs may be totally
> different.
> A good index will allow the WHERE clause to filter out unwanted rows as
> quickly as possible, and may even provide all of the data necessary to
> prevent a second trip to the table.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Query Hangs when Parameters are scaled down.

I have a weird problem in Sql Server 2005 that I need some help on.
Below
you'll find two queries. Both queries are exactly the same; they query
the
same database, query for the same info, same sql statement, same where
clause... The first query (with the longer date range) runs perfectly
against my databases. The
second query is as I've said exactly the same, the only difference is
that
the date range is much smaller and is infact included in the first
query. The
problem with this query is that it seems to churn away but do nothing.
It
seems to execute my query but it never finishes. Last time I cancelled
it after +2 hours. Does anybody why I see this behavior? Is there
something that I can do about it? Since it never seems to finish, I'm
not able to generate a execute plan for it to see what the problem is.
Thanks in advance :P
THE SANE QUERY:
----
----
select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
sum(T2."TRANSACTION_AMT") AS "c5"
from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT" T2,
"Accounting"."ACCOUNTING"."TIME_DIM" T1,
"Accounting"."ACCOUNTING"."GL_DIM" T4,
"Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
"Accounting"."ACCOUNTING"."LOCATION_SUPER_TABLE" T3 on
substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" =
T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" =
2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
('430', '431') and T2."TRANS_POST_DATE" between '2006-07-01
00:00:00.000' and '2006-12-31 00:00:00.000'
group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
order by 1 asc , 2 asc , 3 asc
----
---
THE BAD QUERY:
----
---
select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
sum(T2."TRANSACTION_AMT") AS "c5"
from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT" T2,
"Accounting"."ACCOUNTING"."TIME_DIM" T1,
"Accounting"."ACCOUNTING"."GL_DIM" T4,
"Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
"Accounting"."ACCOUNTING"."LOCATION_SUPER_TABLE" T3 on
substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" =
T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" =
2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
('430', '431') and T2."TRANS_POST_DATE" between '2006-12-01
00:00:00.000' and '2006-12-31 00:00:00.000'
group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
order by 1 asc , 2 asc , 3 ascP.S.
I have already reindexed the tables, cleared the cached query plans and
updated the statistics with no change in the outcome.|||swaroop.atre@.gmail.com wrote:
> I have a weird problem in Sql Server 2005 that I need some help on.
> Below
> you'll find two queries. Both queries are exactly the same; they query
> the
> same database, query for the same info, same sql statement, same where
> clause... The first query (with the longer date range) runs perfectly
> against my databases. The
> second query is as I've said exactly the same, the only difference is
> that
> the date range is much smaller and is infact included in the first
> query. The
> problem with this query is that it seems to churn away but do nothing.
> It
> seems to execute my query but it never finishes. Last time I cancelled
> it after +2 hours. Does anybody why I see this behavior? Is there
> something that I can do about it? Since it never seems to finish, I'm
> not able to generate a execute plan for it to see what the problem is.
> Thanks in advance :P
> THE SANE QUERY:
> ----
----
> select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
> T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
> sum(T2."TRANSACTION_AMT") AS "c5"
> from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT" T2,
> "Accounting"."ACCOUNTING"."TIME_DIM" T1,
> "Accounting"."ACCOUNTING"."GL_DIM" T4,
> "Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
> "Accounting"."ACCOUNTING"."LOCATION_SUPER_TABLE" T3 on
> substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
> where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" =
> T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" =
> 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
> ('430', '431') and T2."TRANS_POST_DATE" between '2006-07-01
> 00:00:00.000' and '2006-12-31 00:00:00.000'
> group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
> order by 1 asc , 2 asc , 3 asc
> ----
---
> THE BAD QUERY:
> ----
---
> select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
> T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
> sum(T2."TRANSACTION_AMT") AS "c5"
> from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT" T2,
> "Accounting"."ACCOUNTING"."TIME_DIM" T1,
> "Accounting"."ACCOUNTING"."GL_DIM" T4,
> "Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
> "Accounting"."ACCOUNTING"."LOCATION_SUPER_TABLE" T3 on
> substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
> where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" =
> T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" =
> 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
> ('430', '431') and T2."TRANS_POST_DATE" between '2006-12-01
> 00:00:00.000' and '2006-12-31 00:00:00.000'
> group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
> order by 1 asc , 2 asc , 3 asc
>
Compare the ESTIMATED execution plans for the two queries - what's the
difference between them? Where is the time being spent?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
They both have the exact same execution plan (which they should
considering its just a change in the value for a conditional
expression). The expected usage is largely with the lookup for the
transaction data table which uses a clustered index.
Swaroop
Tracy McKibben wrote:
> swaroop.atre@.gmail.com wrote:
> Compare the ESTIMATED execution plans for the two queries - what's the
> difference between them? Where is the time being spent?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||UPDATE:
The execution plans changed once I updated the statistics on the dbases
again.
swaroop.atre@.gmail.com wrote:[vbcol=seagreen]
> Tracy,
> They both have the exact same execution plan (which they should
> considering its just a change in the value for a conditional
> expression). The expected usage is largely with the lookup for the
> transaction data table which uses a clustered index.
> Swaroop
>
> Tracy McKibben wrote:|||swaroop.atre@.gmail.com wrote:
> UPDATE:
> The execution plans changed once I updated the statistics on the dbases
> again.
>
Did the performance change as well?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Adding in the index on one of the tables altered the performance.
However on two other queries with the same problem they have no effect.
All tables being used are indexed.
Tracy McKibben wrote:
> swaroop.atre@.gmail.com wrote:
> Did the performance change as well?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||swaroop.atre@.gmail.com wrote:
> Adding in the index on one of the tables altered the performance.
> However on two other queries with the same problem they have no effect.
> All tables being used are indexed.
Review the execution plans. The queries may "appear" to have the same
problem, i.e. slow performance, but their indexing needs may be totally
different.
A good index will allow the WHERE clause to filter out unwanted rows as
quickly as possible, and may even provide all of the data necessary to
prevent a second trip to the table.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The query is actually making a single trip to each table that it needs
information from.
The query in a jist gathers information from six tables the biggest one
being 150M records or so with a clustered unique index while the other
5 have non clustered non unique indexes. As long as the query looks for
data for the month of dec it never returns (the date constraint is
contained in the big table) however if i expand the range to include
dec but not just dec it comes back with the data i seek. The execution
plans for the two are very different and I think that might be because
the new one uses the indexes while the old one in the cache dosnt.
Tracy McKibben wrote:
> swaroop.atre@.gmail.com wrote:
> Review the execution plans. The queries may "appear" to have the same
> problem, i.e. slow performance, but their indexing needs may be totally
> different.
> A good index will allow the WHERE clause to filter out unwanted rows as
> quickly as possible, and may even provide all of the data necessary to
> prevent a second trip to the table.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Query Hangs when Parameters are scaled down.

I have a weird problem in Sql Server 2005 that I need some help on.
Below
you'll find two queries. Both queries are exactly the same; they query
the
same database, query for the same info, same sql statement, same where
clause... The first query (with the longer date range) runs perfectly
against my databases. The
second query is as I've said exactly the same, the only difference is
that
the date range is much smaller and is infact included in the first
query. The
problem with this query is that it seems to churn away but do nothing.
It
seems to execute my query but it never finishes. Last time I cancelled
it after +2 hours. Does anybody why I see this behavior? Is there
something that I can do about it? Since it never seems to finish, I'm
not able to generate a execute plan for it to see what the problem is.
Thanks in advance :P
THE SANE QUERY:
------
select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
sum(T2."TRANSACTION_AMT") AS "c5"
from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT" T2,
"Accounting"."ACCOUNTING"."TIME_DIM" T1,
"Accounting"."ACCOUNTING"."GL_DIM" T4,
"Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
"Accounting"."ACCOUNTING"."LOCATION_SUPER_TABLE" T3 on
substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" = T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" = 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
('430', '431') and T2."TRANS_POST_DATE" between '2006-07-01
00:00:00.000' and '2006-12-31 00:00:00.000'
group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
order by 1 asc , 2 asc , 3 asc
------
THE BAD QUERY:
------
select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
sum(T2."TRANSACTION_AMT") AS "c5"
from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT" T2,
"Accounting"."ACCOUNTING"."TIME_DIM" T1,
"Accounting"."ACCOUNTING"."GL_DIM" T4,
"Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
"Accounting"."ACCOUNTING"."LOCATION_SUPER_TABLE" T3 on
substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" = T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" = 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
('430', '431') and T2."TRANS_POST_DATE" between '2006-12-01
00:00:00.000' and '2006-12-31 00:00:00.000'
group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
order by 1 asc , 2 asc , 3 ascP.S.
I have already reindexed the tables, cleared the cached query plans and
updated the statistics with no change in the outcome.|||swaroop.atre@.gmail.com wrote:
> I have a weird problem in Sql Server 2005 that I need some help on.
> Below
> you'll find two queries. Both queries are exactly the same; they query
> the
> same database, query for the same info, same sql statement, same where
> clause... The first query (with the longer date range) runs perfectly
> against my databases. The
> second query is as I've said exactly the same, the only difference is
> that
> the date range is much smaller and is infact included in the first
> query. The
> problem with this query is that it seems to churn away but do nothing.
> It
> seems to execute my query but it never finishes. Last time I cancelled
> it after +2 hours. Does anybody why I see this behavior? Is there
> something that I can do about it? Since it never seems to finish, I'm
> not able to generate a execute plan for it to see what the problem is.
> Thanks in advance :P
> THE SANE QUERY:
> ------
> select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
> T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
> sum(T2."TRANSACTION_AMT") AS "c5"
> from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT" T2,
> "Accounting"."ACCOUNTING"."TIME_DIM" T1,
> "Accounting"."ACCOUNTING"."GL_DIM" T4,
> "Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
> "Accounting"."ACCOUNTING"."LOCATION_SUPER_TABLE" T3 on
> substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
> where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" => T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" => 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
> ('430', '431') and T2."TRANS_POST_DATE" between '2006-07-01
> 00:00:00.000' and '2006-12-31 00:00:00.000'
> group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
> order by 1 asc , 2 asc , 3 asc
> ------
> THE BAD QUERY:
> ------
> select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
> T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
> sum(T2."TRANSACTION_AMT") AS "c5"
> from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT" T2,
> "Accounting"."ACCOUNTING"."TIME_DIM" T1,
> "Accounting"."ACCOUNTING"."GL_DIM" T4,
> "Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
> "Accounting"."ACCOUNTING"."LOCATION_SUPER_TABLE" T3 on
> substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
> where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" => T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" => 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
> ('430', '431') and T2."TRANS_POST_DATE" between '2006-12-01
> 00:00:00.000' and '2006-12-31 00:00:00.000'
> group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
> order by 1 asc , 2 asc , 3 asc
>
Compare the ESTIMATED execution plans for the two queries - what's the
difference between them? Where is the time being spent?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
They both have the exact same execution plan (which they should
considering its just a change in the value for a conditional
expression). The expected usage is largely with the lookup for the
transaction data table which uses a clustered index.
Swaroop
Tracy McKibben wrote:
> swaroop.atre@.gmail.com wrote:
> > I have a weird problem in Sql Server 2005 that I need some help on.
> > Below
> > you'll find two queries. Both queries are exactly the same; they query
> > the
> > same database, query for the same info, same sql statement, same where
> > clause... The first query (with the longer date range) runs perfectly
> > against my databases. The
> > second query is as I've said exactly the same, the only difference is
> > that
> > the date range is much smaller and is infact included in the first
> > query. The
> > problem with this query is that it seems to churn away but do nothing.
> > It
> > seems to execute my query but it never finishes. Last time I cancelled
> > it after +2 hours. Does anybody why I see this behavior? Is there
> > something that I can do about it? Since it never seems to finish, I'm
> > not able to generate a execute plan for it to see what the problem is.
> > Thanks in advance :P
> >
> > THE SANE QUERY:
> > ------
> > select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
> > T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
> > sum(T2."TRANSACTION_AMT") AS "c5"
> >
> > from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT" T2,
> > "Accounting"."ACCOUNTING"."TIME_DIM" T1,
> > "Accounting"."ACCOUNTING"."GL_DIM" T4,
> > "Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
> > "Accounting"."ACCOUNTING"."LOCATION_SUPER_TABLE" T3 on
> > substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
> >
> > where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" => > T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" => > 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
> > ('430', '431') and T2."TRANS_POST_DATE" between '2006-07-01
> > 00:00:00.000' and '2006-12-31 00:00:00.000'
> >
> > group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
> >
> > order by 1 asc , 2 asc , 3 asc
> > ------
> >
> > THE BAD QUERY:
> > ------
> > select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
> > T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
> > sum(T2."TRANSACTION_AMT") AS "c5"
> >
> > from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT" T2,
> > "Accounting"."ACCOUNTING"."TIME_DIM" T1,
> > "Accounting"."ACCOUNTING"."GL_DIM" T4,
> > "Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
> > "Accounting"."ACCOUNTING"."LOCATION_SUPER_TABLE" T3 on
> > substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
> >
> > where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" => > T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" => > 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
> > ('430', '431') and T2."TRANS_POST_DATE" between '2006-12-01
> > 00:00:00.000' and '2006-12-31 00:00:00.000'
> >
> > group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
> >
> > order by 1 asc , 2 asc , 3 asc
> >
> Compare the ESTIMATED execution plans for the two queries - what's the
> difference between them? Where is the time being spent?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||UPDATE:
The execution plans changed once I updated the statistics on the dbases
again.
swaroop.atre@.gmail.com wrote:
> Tracy,
> They both have the exact same execution plan (which they should
> considering its just a change in the value for a conditional
> expression). The expected usage is largely with the lookup for the
> transaction data table which uses a clustered index.
> Swaroop
>
> Tracy McKibben wrote:
> > swaroop.atre@.gmail.com wrote:
> > > I have a weird problem in Sql Server 2005 that I need some help on.
> > > Below
> > > you'll find two queries. Both queries are exactly the same; they query
> > > the
> > > same database, query for the same info, same sql statement, same where
> > > clause... The first query (with the longer date range) runs perfectly
> > > against my databases. The
> > > second query is as I've said exactly the same, the only difference is
> > > that
> > > the date range is much smaller and is infact included in the first
> > > query. The
> > > problem with this query is that it seems to churn away but do nothing.
> > > It
> > > seems to execute my query but it never finishes. Last time I cancelled
> > > it after +2 hours. Does anybody why I see this behavior? Is there
> > > something that I can do about it? Since it never seems to finish, I'm
> > > not able to generate a execute plan for it to see what the problem is.
> > > Thanks in advance :P
> > >
> > > THE SANE QUERY:
> > > ------
> > > select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
> > > T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
> > > sum(T2."TRANSACTION_AMT") AS "c5"
> > >
> > > from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT" T2,
> > > "Accounting"."ACCOUNTING"."TIME_DIM" T1,
> > > "Accounting"."ACCOUNTING"."GL_DIM" T4,
> > > "Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
> > > "Accounting"."ACCOUNTING"."LOCATION_SUPER_TABLE" T3 on
> > > substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
> > >
> > > where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" => > > T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" => > > 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
> > > ('430', '431') and T2."TRANS_POST_DATE" between '2006-07-01
> > > 00:00:00.000' and '2006-12-31 00:00:00.000'
> > >
> > > group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
> > >
> > > order by 1 asc , 2 asc , 3 asc
> > > ------
> > >
> > > THE BAD QUERY:
> > > ------
> > > select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
> > > T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
> > > sum(T2."TRANSACTION_AMT") AS "c5"
> > >
> > > from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT" T2,
> > > "Accounting"."ACCOUNTING"."TIME_DIM" T1,
> > > "Accounting"."ACCOUNTING"."GL_DIM" T4,
> > > "Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
> > > "Accounting"."ACCOUNTING"."LOCATION_SUPER_TABLE" T3 on
> > > substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
> > >
> > > where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" => > > T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" => > > 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
> > > ('430', '431') and T2."TRANS_POST_DATE" between '2006-12-01
> > > 00:00:00.000' and '2006-12-31 00:00:00.000'
> > >
> > > group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
> > >
> > > order by 1 asc , 2 asc , 3 asc
> > >
> >
> > Compare the ESTIMATED execution plans for the two queries - what's the
> > difference between them? Where is the time being spent?
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com|||swaroop.atre@.gmail.com wrote:
> UPDATE:
> The execution plans changed once I updated the statistics on the dbases
> again.
>
Did the performance change as well?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Adding in the index on one of the tables altered the performance.
However on two other queries with the same problem they have no effect.
All tables being used are indexed.
Tracy McKibben wrote:
> swaroop.atre@.gmail.com wrote:
> > UPDATE:
> >
> > The execution plans changed once I updated the statistics on the dbases
> > again.
> >
> Did the performance change as well?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||swaroop.atre@.gmail.com wrote:
> Adding in the index on one of the tables altered the performance.
> However on two other queries with the same problem they have no effect.
> All tables being used are indexed.
Review the execution plans. The queries may "appear" to have the same
problem, i.e. slow performance, but their indexing needs may be totally
different.
A good index will allow the WHERE clause to filter out unwanted rows as
quickly as possible, and may even provide all of the data necessary to
prevent a second trip to the table.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The query is actually making a single trip to each table that it needs
information from.
The query in a jist gathers information from six tables the biggest one
being 150M records or so with a clustered unique index while the other
5 have non clustered non unique indexes. As long as the query looks for
data for the month of dec it never returns (the date constraint is
contained in the big table) however if i expand the range to include
dec but not just dec it comes back with the data i seek. The execution
plans for the two are very different and I think that might be because
the new one uses the indexes while the old one in the cache dosnt.
Tracy McKibben wrote:
> swaroop.atre@.gmail.com wrote:
> > Adding in the index on one of the tables altered the performance.
> >
> > However on two other queries with the same problem they have no effect.
> > All tables being used are indexed.
> Review the execution plans. The queries may "appear" to have the same
> problem, i.e. slow performance, but their indexing needs may be totally
> different.
> A good index will allow the WHERE clause to filter out unwanted rows as
> quickly as possible, and may even provide all of the data necessary to
> prevent a second trip to the table.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.comsql

query governor for users?

How to make only some users not to run long running blocking queries ?
TIAHi
You could SET DEADLOCK_PRIORITY to low and a QUERY_GOVERNOR_COST_LIMIT,
setting ROWCOUNT and a LOCK_TIMEOUT may also be options.
John
"DallasBlue" wrote:

> How to make only some users not to run long running blocking queries ?
> TIA
>sql

query governor for users?

How to make only some users not to run long running blocking queries ?
TIAHi
You could SET DEADLOCK_PRIORITY to low and a QUERY_GOVERNOR_COST_LIMIT,
setting ROWCOUNT and a LOCK_TIMEOUT may also be options.
John
"DallasBlue" wrote:
> How to make only some users not to run long running blocking queries ?
> TIA
>

Query Governor

Hello all,
Im setting up a SQL-Server and there is an option named Query
Governor to avoid queries to exceed a specific cost. How is this cost
measured ? Milliseconds ?
best regards,
Evandro
You can set this in enterprise manager under server properties using the
server settings tab.
Or
EXEC sp_configure 'show advanced option', '1'
exec sp_configure N'query governor cost limit', 100 (or the max elapsed time
in seconds)
http://www.schemamania.org/jkl/books..._server_51.htm
http://msdn.microsoft.com/library/de...onfig_73u6.asp
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:eBM64z28EHA.1396@.tk2msftngp13.phx.gbl...
> Hello all,
> Im setting up a SQL-Server and there is an option named Query
> Governor to avoid queries to exceed a specific cost. How is this cost
> measured ? Milliseconds ?
>
> best regards,
> Evandro
>
|||Hi Evandro,
EXEC sp_configure N'query governor cost limit', 100 is a server-wide
setting. Unless you have a very specific reason, do not set this option.
At the client level, statementwise one can use
SET QUERY_GOVERNOR_COST_LIMIT
Thanks
Yogish

Query Governor

Anybody out there have any luck (good or bad) using the server-level Query
Governor? We've tried to implement it, but when we run queries (primarily
selects) to test it, it doesn't seem to engage. Is there a source of better
info than BOL on what Query Governor actually does?
The "query governor cost limit" option allows you to limit the maximum length
a query can run on a server, and is one of the few SQL Server configuration
options that I endorse. For example, let's say that some of the users of your
server like to run very long-running queries that really hurt the performance
of your server. By setting this option, you could prevent them from running
any queries that exceeded, say 300 seconds (or whatever number you pick). The
default value for this setting is "0", which means that there are no limits
to how long a query can run.
The value you set for this option is approximate, and is based on how long
the Query Optimizer estimates the query will run. If the estimate is more
than the time you have specified, the query won't run at all, producing an
error instead. This can save a lot of valuable server resources.
On the other hand, users can get real unhappy with you if they can't run the
queries then have to run in order to do their job. What you might consider
doing is helping those users to write more efficient queries. That way,
everyone will be happy.
If this setting is set to "0", consider adding a value here and see what
happens. Just don't make it too small. You might consider starting with value
of about 600 seconds and see what happens. If that is OK, then try 500
seconds, and so on, until you find out when users start complaining.
"Tim Brown, DAC DBA" wrote:

> Anybody out there have any luck (good or bad) using the server-level Query
> Governor? We've tried to implement it, but when we run queries (primarily
> selects) to test it, it doesn't seem to engage. Is there a source of better
> info than BOL on what Query Governor actually does?
|||Another thing to know is that the governer makes the go/nogo decision after
the query has been optimized and before the query is run... It is possible
that the optimizers cost estimate is incorrect. The governer will NOT stop a
query that actually runs longer than the limit...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<Tim Brown>; "DAC DBA" <Tim Brown, DAC DBA@.discussions.microsoft.com> wrote
in message news:7855542F-3805-45B2-96C2-11B386C580C9@.microsoft.com...
> Anybody out there have any luck (good or bad) using the server-level Query
> Governor? We've tried to implement it, but when we run queries (primarily
> selects) to test it, it doesn't seem to engage. Is there a source of
better
> info than BOL on what Query Governor actually does?

Query Governor

Hello all,
I´m setting up a SQL-Server and there is an option named Query
Governor to avoid queries to exceed a specific cost. How is this cost
measured ? Milliseconds ?
best regards,
EvandroYou can set this in enterprise manager under server properties using the
server settings tab.
Or
EXEC sp_configure 'show advanced option', '1'
exec sp_configure N'query governor cost limit', 100 (or the max elapsed time
in seconds)
http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/1_server_51.htm
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_73u6.asp
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:eBM64z28EHA.1396@.tk2msftngp13.phx.gbl...
> Hello all,
> I´m setting up a SQL-Server and there is an option named Query
> Governor to avoid queries to exceed a specific cost. How is this cost
> measured ? Milliseconds ?
>
> best regards,
> Evandro
>|||Hi Evandro,
EXEC sp_configure N'query governor cost limit', 100 is a server-wide
setting. Unless you have a very specific reason, do not set this option.
At the client level, statementwise one can use
SET QUERY_GOVERNOR_COST_LIMIT
--
Thanks
Yogishsql

Query Governor

Anybody out there have any luck (good or bad) using the server-level Query
Governor? We've tried to implement it, but when we run queries (primarily
selects) to test it, it doesn't seem to engage. Is there a source of better
info than BOL on what Query Governor actually does?The "query governor cost limit" option allows you to limit the maximum lengt
h
a query can run on a server, and is one of the few SQL Server configuration
options that I endorse. For example, let's say that some of the users of you
r
server like to run very long-running queries that really hurt the performanc
e
of your server. By setting this option, you could prevent them from running
any queries that exceeded, say 300 seconds (or whatever number you pick). Th
e
default value for this setting is "0", which means that there are no limits
to how long a query can run.
The value you set for this option is approximate, and is based on how long
the Query Optimizer estimates the query will run. If the estimate is more
than the time you have specified, the query won't run at all, producing an
error instead. This can save a lot of valuable server resources.
On the other hand, users can get real unhappy with you if they can't run the
queries then have to run in order to do their job. What you might consider
doing is helping those users to write more efficient queries. That way,
everyone will be happy.
If this setting is set to "0", consider adding a value here and see what
happens. Just don't make it too small. You might consider starting with valu
e
of about 600 seconds and see what happens. If that is OK, then try 500
seconds, and so on, until you find out when users start complaining.
"Tim Brown, DAC DBA" wrote:

> Anybody out there have any luck (good or bad) using the server-level Query
> Governor? We've tried to implement it, but when we run queries (primarily
> selects) to test it, it doesn't seem to engage. Is there a source of bett
er
> info than BOL on what Query Governor actually does?|||Another thing to know is that the governer makes the go/nogo decision after
the query has been optimized and before the query is run... It is possible
that the optimizers cost estimate is incorrect. The governer will NOT stop a
query that actually runs longer than the limit...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<Tim Brown>; "DAC DBA" <Tim Brown, DAC DBA@.discussions.microsoft.com> wrote
in message news:7855542F-3805-45B2-96C2-11B386C580C9@.microsoft.com...
> Anybody out there have any luck (good or bad) using the server-level Query
> Governor? We've tried to implement it, but when we run queries (primarily
> selects) to test it, it doesn't seem to engage. Is there a source of
better
> info than BOL on what Query Governor actually does?

Query Governor

Hello all,
Im setting up a SQL-Server and there is an option named Query
Governor to avoid queries to exceed a specific cost. How is this cost
measured ? Milliseconds ?
best regards,
EvandroYou can set this in enterprise manager under server properties using the
server settings tab.
Or
EXEC sp_configure 'show advanced option', '1'
exec sp_configure N'query governor cost limit', 100 (or the max elapsed time
in seconds)
http://www.schemamania.org/jkl/book...1_server_51.htm
http://msdn.microsoft.com/library/d... />
g_73u6.asp
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:eBM64z28EHA.1396@.tk2msftngp13.phx.gbl...
> Hello all,
> Im setting up a SQL-Server and there is an option named Query
> Governor to avoid queries to exceed a specific cost. How is this cost
> measured ? Milliseconds ?
>
> best regards,
> Evandro
>|||Hi Evandro,
EXEC sp_configure N'query governor cost limit', 100 is a server-wide
setting. Unless you have a very specific reason, do not set this option.
At the client level, statementwise one can use
SET QUERY_GOVERNOR_COST_LIMIT
Thanks
Yogish

query generator

hi,
can u plz recomend some utility that generates queries against SQL Server,
something w/ GUI, where one can say click on some fields and get a SELECT
statement
thanks> can u plz recomend some utility that generates queries against SQL Server,
> something w/ GUI, where one can say click on some fields and get a SELECT
> statement
MS Query tool in included in Excel. Just go to Data - Import External Data -
New Database Query and the tool will be started. If it is not installed yet,
it should install on first usage.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Check out free PowerQuery, or DataStudio from AgileInfoSoftware
http://www.agileinfollc.com
"Bruce" <no@.spam.com> wrote in message
news:%23X%235S$hpFHA.1204@.TK2MSFTNGP12.phx.gbl...
> hi,
> can u plz recomend some utility that generates queries against SQL Server,
> something w/ GUI, where one can say click on some fields and get a SELECT
> statement
> thanks
>sql

query generator

hi,
can u plz recomend some utility that generates queries against SQL Server,
something w/ GUI, where one can say click on some fields and get a SELECT
statement
thanks> can u plz recomend some utility that generates queries against SQL Server,
> something w/ GUI, where one can say click on some fields and get a SELECT
> statement
MS Query tool in included in Excel. Just go to Data - Import External Data -
New Database Query and the tool will be started. If it is not installed yet,
it should install on first usage.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Check out free PowerQuery, or DataStudio from AgileInfoSoftware
http://www.agileinfollc.com
"Bruce" <no@.spam.com> wrote in message
news:%23X%235S$hpFHA.1204@.TK2MSFTNGP12.phx.gbl...
> hi,
> can u plz recomend some utility that generates queries against SQL Server,
> something w/ GUI, where one can say click on some fields and get a SELECT
> statement
> thanks
>

query generator

hi,
can u plz recomend some utility that generates queries against SQL Server,
something w/ GUI, where one can say click on some fields and get a SELECT
statement
thanks
> can u plz recomend some utility that generates queries against SQL Server,
> something w/ GUI, where one can say click on some fields and get a SELECT
> statement
MS Query tool in included in Excel. Just go to Data - Import External Data -
New Database Query and the tool will be started. If it is not installed yet,
it should install on first usage.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Check out free PowerQuery, or DataStudio from AgileInfoSoftware
http://www.agileinfollc.com
"Bruce" <no@.spam.com> wrote in message
news:%23X%235S$hpFHA.1204@.TK2MSFTNGP12.phx.gbl...
> hi,
> can u plz recomend some utility that generates queries against SQL Server,
> something w/ GUI, where one can say click on some fields and get a SELECT
> statement
> thanks
>

Query for results without using Expr

I'm working with SQL Server 2005.

I am new to sql and am trying to learn it and understand how to use the queries.

I can query for basic information, no problem but I am trying something more

complex and am having trouble. I'm trying to return up to 10 records but every

time I test in my browser I only see the first record. I dont understand why I

can't see more then just that one record.

There are three tables. The names & descriptions and URL and Links are part of

the same table. They are called:

TableMain
TableNames
TableDescriptions
TableURL
TableLinks

So the names and descripts are in one table and URL and Links in another.

Here is the sql query code:

[CODE]
strSQL = "SELECT *, TableMain.ID AS Expr1, TableMain.Unit AS Expr2,
TableMain.Air_Date AS Expr3, TableNames.ID AS Expr4, Table.Names AS Expr5,
Table.Descriptions AS Expr6, TableLinks.URL AS Expr7 FROM TableMain CROSS JOIN
TableNames CROSS JOIN TableLinks WHERE (DATEDIFF(day, GETDATE(), Table.Air_Date) = 0) AND (Table.Air_Date <= GETDATE())"
[/CODE]

I was told that I dont really need to use Expr but every time I pull them out of

this code segment and re-paste into the query editor then click on Run, it puts

them back in and shows the correct result. It's working ok in the query editor so

why wont it show me more then just the first record inside of TableNames when

tested from a browser?

I was told the other day that it was because I wasn't looping through the

records. But I've tried using:

<%Do While Not RS.EOF%>

my html / asp display code in here....

<strong><% Response.Write RS ("TableName") %> </strong> <% Response.Write

RS("TableDescription") %><br /><br />
Related Links:<br />
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>

<%
rs.MoveNext
Loop
%>

Please any help with this would be great

First, let me respond to the issue of 'Expr#'. This is an 'Alias', an assigned name for a column that doesn't have a name. When you use [ SELECT * ], you are asking for all columns in the table, and then when you list the columns, as in [ TableMain.ID ], it produces another column with the same name. Two columns having the same name are not allowed, so an 'Alias' is assigned for one of them.

Listing the columns, and NOT using [ SELECT * ] is the preferred method.

Now about the use of CROSS JOINS. A CROSS JOIN produces every possible combination from the two tables in the JOIN. If one table has 1000 rows, and the other table has 1000 rows, a CROSS JOIN will produce 1,000,000 rows. And if you add a thrid table as a CROSS JOIN, and it has only 100 rows, the result is 100,000,000 rows. Are you sure that is your intention?

About displaying 10 records. Are you using a grid control? How are you displaying the records?

sql

Query for Last Date in a table

I am new to creating SQL queries and need some help.

I am trying to query from three tables.
1 - A table with a list of Drawings (DRAWINGS)
2 - A table with the Revision history of the Drawings (REVISIONS)
3 - A table with the list of Projects (WRI_SITE_PRJS)

The result I am looking for:
Name of Drawing (I want to return the drawings that don't have a revision as well)
Last Drawing Revision
Date of Revision
Project that the drawing belongs on

The below query I created in access and the result was exactly what I was expecting.

SELECT DRAWINGLIST.NAME, DRAWINGLIST.WRI_DESCRIPTION AS [Drawing Title], LastRev.LastOfREVISIONNAME AS Rev, LastDate.LastOfREVISIONDATE AS [Rev Date], WRI_SITE_PRJS.TITLE AS Project
FROM LastRev RIGHT JOIN (LastDate RIGHT JOIN (DRAWINGLIST INNER JOIN WRI_SITE_PRJS ON DRAWINGLIST.WPRJID = WRI_SITE_PRJS.WPRJID) ON LastDate.NAME = DRAWINGLIST.NAME) ON LastRev.NAME = DRAWINGLIST.NAME
ORDER BY DRAWINGLIST.NAME;

I tried getting the same result in SQL and find that I can't GROUP BY the LAST date in a table. The result I'm getting is all the revisions for the drawing.

My question is: How do I query for the LAST date or revision in a table.

I'm using Microsoft SQL Server 2003

Some help is greatly appreciated.
OS

Quote:

Originally Posted by oszapped

I am new to creating SQL queries and need some help.

I am trying to query from three tables.
1 - A table with a list of Drawings (DRAWINGS)
2 - A table with the Revision history of the Drawings (REVISIONS)
3 - A table with the list of Projects (WRI_SITE_PRJS)

The result I am looking for:
Name of Drawing (I want to return the drawings that don't have a revision as well)
Last Drawing Revision
Date of Revision
Project that the drawing belongs on

The below query I created in access and the result was exactly what I was expecting.

SELECT DRAWINGLIST.NAME, DRAWINGLIST.WRI_DESCRIPTION AS [Drawing Title], LastRev.LastOfREVISIONNAME AS Rev, LastDate.LastOfREVISIONDATE AS [Rev Date], WRI_SITE_PRJS.TITLE AS Project
FROM LastRev RIGHT JOIN (LastDate RIGHT JOIN (DRAWINGLIST INNER JOIN WRI_SITE_PRJS ON DRAWINGLIST.WPRJID = WRI_SITE_PRJS.WPRJID) ON LastDate.NAME = DRAWINGLIST.NAME) ON LastRev.NAME = DRAWINGLIST.NAME
ORDER BY DRAWINGLIST.NAME;

I tried getting the same result in SQL and find that I can't GROUP BY the LAST date in a table. The result I'm getting is all the revisions for the drawing.

My question is: How do I query for the LAST date or revision in a table.

I'm using Microsoft SQL Server 2003

Some help is greatly appreciated.
OS

I have never used SQL Server 2003
but i would pressume it would be order by LastDate.LastOfREVISIONDATE DESC|||

Quote:

Originally Posted by oszapped

I am new to creating SQL queries and need some help.

I am trying to query from three tables.
1 - A table with a list of Drawings (DRAWINGS)
2 - A table with the Revision history of the Drawings (REVISIONS)
3 - A table with the list of Projects (WRI_SITE_PRJS)

The result I am looking for:
Name of Drawing (I want to return the drawings that don't have a revision as well)
Last Drawing Revision
Date of Revision
Project that the drawing belongs on

The below query I created in access and the result was exactly what I was expecting.

SELECT DRAWINGLIST.NAME, DRAWINGLIST.WRI_DESCRIPTION AS [Drawing Title], LastRev.LastOfREVISIONNAME AS Rev, LastDate.LastOfREVISIONDATE AS [Rev Date], WRI_SITE_PRJS.TITLE AS Project
FROM LastRev RIGHT JOIN (LastDate RIGHT JOIN (DRAWINGLIST INNER JOIN WRI_SITE_PRJS ON DRAWINGLIST.WPRJID = WRI_SITE_PRJS.WPRJID) ON LastDate.NAME = DRAWINGLIST.NAME) ON LastRev.NAME = DRAWINGLIST.NAME
ORDER BY DRAWINGLIST.NAME;

I tried getting the same result in SQL and find that I can't GROUP BY the LAST date in a table. The result I'm getting is all the revisions for the drawing.

My question is: How do I query for the LAST date or revision in a table.

I'm using Microsoft SQL Server 2003

Some help is greatly appreciated.
OS

Use Max() on the date column and group by everything else.

query failure

I've got an email from one of the users complaining about query failure. He
is doing queries using MS ACCESS. He is getting the following error message:
ODBC - call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not allocate new page
for database 'TEMPDB'. There are no more pages available in filegroup
DEFAULT. Space can be created by dropping objects, adding additional files,
or allowing file growth. (#1101)
However, the TEMPDB is 60MB in size with 59MB free. It has unrestricted
growth as well. Any suggestions?
Thanks in advance,
Antonin
The answer is in the message you've got. TempDB has grown and there is no disk space to continue growth.
|||Thanks Ray. I cleaned up the disk and the problem is gone.
Antonin
"Ray D" <ray_d@.mail.ru> wrote in message
news:05B82F27-73AB-4EAA-A10E-4A46A67C71D2@.microsoft.com...
> The answer is in the message you've got. TempDB has grown and there is no
disk space to continue growth.

query failure

I've got an email from one of the users complaining about query failure. He
is doing queries using MS ACCESS. He is getting the following error message:
ODBC - call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not allocate new page
for database 'TEMPDB'. There are no more pages available in filegroup
DEFAULT. Space can be created by dropping objects, adding additional files,
or allowing file growth. (#1101)
However, the TEMPDB is 60MB in size with 59MB free. It has unrestricted
growth as well. Any suggestions?
Thanks in advance,
AntoninThe answer is in the message you've got. TempDB has grown and there is no disk space to continue growth.|||Thanks Ray. I cleaned up the disk and the problem is gone.
Antonin
"Ray D" <ray_d@.mail.ru> wrote in message
news:05B82F27-73AB-4EAA-A10E-4A46A67C71D2@.microsoft.com...
> The answer is in the message you've got. TempDB has grown and there is no
disk space to continue growth.

query failure

I've got an email from one of the users complaining about query failure. He
is doing queries using MS ACCESS. He is getting the following error message:
ODBC - call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not alloca
te new page
for database 'TEMPDB'. There are no more pages available in filegroup
DEFAULT. Space can be created by dropping objects, adding additional files,
or allowing file growth. (#1101)
However, the TEMPDB is 60MB in size with 59MB free. It has unrestricted
growth as well. Any suggestions?
Thanks in advance,
AntoninThe answer is in the message you've got. TempDB has grown and there is no di
sk space to continue growth.|||Thanks Ray. I cleaned up the disk and the problem is gone.
Antonin
"Ray D" <ray_d@.mail.ru> wrote in message
news:05B82F27-73AB-4EAA-A10E-4A46A67C71D2@.microsoft.com...
> The answer is in the message you've got. TempDB has grown and there is no
disk space to continue growth.