"Top 10" query

How do I do this in SQL?
I have a table I'm querying with the following fields (simplified
here):
facility_name char(15), drug_product char(64), total_cost money
How can I produce the following in SQL so my result can answer the
question
What are the top 10 drugs dispensed by facility_id when looking at
total_cost?
output should look like this:
facility_id drug_product total_cost
houston Simvastatin 500
houston Sertraline 400
...
next 8 for houston
...
chicago Epoetin 1000
chicago Atorvastatin 800
...
next 8 for chicago
...
etc.
I'm thinking this has to done in more than one statement since TOP will
not work here (as far as I can see) I miss the sequential record
"file" environment of foxpro/access here. I can do this in either of
those 2 apps with about 1 min of coding but I want to learn how to do
this in a SQL tbl.Without DDLs, here is a skeleton query:
SELECT * -- use column names
FROM tbl t1
WHERE ( SELECT COUNT( * )
FROM tbl t2
WHERE t2.facility_id = t1.facility_id
AND t2.total_cost >= t1.total_cost ) <= 10 ;
You have not specified how you'd want to resolve the ties. If ties are
involved, change the correlation like:
WHERE t2.facility_id = t1.facility_id
AND ( t2.drug_product = t1.drug_product
AND t2.total_cost >= t1.total_cost )
OR t2.total_cost >= t1.total_cost )
In t-SQL you can easily use a TOP clause with ORDER BY to achieve similar
results. You may also want to refer to www.aspfaq.com/2120 for some similar
ideas
Anith|||Supposing there are no ties, then:
select a.*
from t as a
where (select count(*) from t as b where b.facility_name = a.facility_name
and b.total_cost <= a.total_cost) <= 10
AMB
"mwrobe" wrote:

> How do I do this in SQL?
> I have a table I'm querying with the following fields (simplified
> here):
> facility_name char(15), drug_product char(64), total_cost money
> How can I produce the following in SQL so my result can answer the
> question
> What are the top 10 drugs dispensed by facility_id when looking at
> total_cost?
> output should look like this:
> facility_id drug_product total_cost
> houston Simvastatin 500
> houston Sertraline 400
> ...
> next 8 for houston
> ...
> chicago Epoetin 1000
> chicago Atorvastatin 800
> ...
> next 8 for chicago
> ...
> etc.
> I'm thinking this has to done in more than one statement since TOP will
> not work here (as far as I can see) I miss the sequential record
> "file" environment of foxpro/access here. I can do this in either of
> those 2 apps with about 1 min of coding but I want to learn how to do
> this in a SQL tbl.
>|||Thank you both gentlemen. It looks like I need to read up and
experiment more on subqueries. Thanks for the link as well.

"There is insufficient system memory to run this query"

Hi
Within a day of the sql server service being up we're getting memory errors
returned from SQL Server by the applications that use it and query analyser.
I suspect that one of our sp's or jobs is leaking memory. Does anyone know
how i can test which one it is?
Win2k sp4, SQL Server 2000 sp3
Thanks
MattThese articles may help you.
http://www.sql-server-performance.c...nce_audit10.asp
+
http://support.microsoft.com/?id=271624
http://support.microsoft.com/defaul...kb;en-us;316749
http://www.sqlservercentral.com/col...rfinfotable.asp
"Matt" wrote:

> Hi
> Within a day of the sql server service being up we're getting memory error
s
> returned from SQL Server by the applications that use it and query analyse
r.
> I suspect that one of our sp's or jobs is leaking memory. Does anyone kno
w
> how i can test which one it is?
> Win2k sp4, SQL Server 2000 sp3
> Thanks
> Matt
>
>

"Simple" query help

Hello!
I would like some help to figure out how to write a specific update query.
What I'm trying to do:
I have one table with these columns:
no (primary key or clustered?)
object (primary key or clustered?)
cid
usergroup1
usergroup2
Lets say the table I would like to upgrade is called obj1 and the table I'm
getting the value to set from is called obj2.
This is in real the same table and it's called objectx.
I would like to set cid for the records with no=7 in obj1 to the value of
the records in obj2 with:
obj2.no=1,
obj2.usergroup2=obj1.object
the value should be set to obj2.usergroup1
The obj2.usergroup2 values could be found several times.
Any idea how to write this query?
Regards MagnusOn Fri, 9 Dec 2005 12:16:41 +0100, Magnus Blomberg wrote:

>Hello!
>I would like some help to figure out how to write a specific update query.
>What I'm trying to do:
>I have one table with these columns:
>no (primary key or clustered?)
>object (primary key or clustered?)
>cid
>usergroup1
>usergroup2
>Lets say the table I would like to upgrade is called obj1 and the table I'm
>getting the value to set from is called obj2.
>This is in real the same table and it's called objectx.
>I would like to set cid for the records with no=7 in obj1 to the value of
>the records in obj2 with:
>obj2.no=1,
>obj2.usergroup2=obj1.object
>the value should be set to obj2.usergroup1
>The obj2.usergroup2 values could be found several times.
>Any idea how to write this query?
>Regards Magnus
>
Hi Magnus,
Before writing the query, the specifications should be clear. You say
that obj2.usergroup2 can be found several times. That means that there
might be more than one obj2.usergroup1. Which one of these should be
used to set obj1.cid'
If you need the lowest value, try if this works:
UPDATE objectx
SET cid = (SELECT MIN(obj2.usergroup1)
FROM objectx AS obj2
WHERE obj2.no = 1
AND obj2.usergroup2 = objectx.object)
WHERE no = 7
(untested - if you prefer a tested reply or if this doesn;t work, then
please check www.aspfaq.com/5006 to find out how to provide clear specs
and test data).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

"Show Execution Plan" and misleading query costs...

Hi All,

I'm a relative newbie to SQL Server, so please forgive me if this is a
daft question...

When I set "Show Execution Plan" on in Query Analyzer, and execute a
(fairly complex) sproc, I note that a particular query is reported as
having a query cost of "71% relative to the batch" - however, this is
nowhere near the slowest executing query in the batch - other queries
which take over twice as long are reported as having costs in the
order of a few percent each.

Am I misreading the execution plan? Note that I'm looking at the
graphical plan, and am not reading the 'estimated' plan - I'm using
the one generated from executing the sproc. My expectation was that
this would be based on the execution times of the queries within the
sproc, however, this does not appear to be the case. (Note - I
determined execution times from PRINT statements, using GETDATE() to
determine the current time, down to milliseconds).

Any feedback would be of great assistance... I may well have to
change the way I approach optimizing queries based on these findings.

Thanks,

LemonSmasher.This is just me, others may differ but I never use the graphical outupt.
I run set statistics profile on and set statistics io on and examine
that output for high physical then logical IO's. Examine the actual
query plan for row operations, executions, and eliminate table scans
where they contribute to high row operations and reduce io with
appropriate indexes, subqueries or alternate joins.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||[posted and mailed, please reply in news]

Will Atkinson (LemonSmasher@.hotmail.com) writes:
> When I set "Show Execution Plan" on in Query Analyzer, and execute a
> (fairly complex) sproc, I note that a particular query is reported as
> having a query cost of "71% relative to the batch" - however, this is
> nowhere near the slowest executing query in the batch - other queries
> which take over twice as long are reported as having costs in the
> order of a few percent each.

While you are looking at the actual plan, all numbers you see are
estimates from the optimizer. To present the graphical plan, QA sends
the command SET STATISTICS PROFILE ON and this output does include any
statistics about actual execution time.

If you want to see execution times per statement, you can use
SET STATISTICS TIME ON, or run a Profiler trace and include the
events SP:StmtCompleted and SQL:StmtCompleted and the Duration
column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <Xns958CE7ECD3551Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> [posted and mailed, please reply in news]
> Will Atkinson (LemonSmasher@.hotmail.com) writes:
> > When I set "Show Execution Plan" on in Query Analyzer, and execute a
> > (fairly complex) sproc, I note that a particular query is reported as
> > having a query cost of "71% relative to the batch" - however, this is
> > nowhere near the slowest executing query in the batch - other queries
> > which take over twice as long are reported as having costs in the
> > order of a few percent each.
> While you are looking at the actual plan, all numbers you see are
> estimates from the optimizer. To present the graphical plan, QA sends
> the command SET STATISTICS PROFILE ON and this output does include any
> statistics about actual execution time.
> If you want to see execution times per statement, you can use
> SET STATISTICS TIME ON, or run a Profiler trace and include the
> events SP:StmtCompleted and SQL:StmtCompleted and the Duration
> column.

You also need to clear the cache if you want a true test. When you run a
query and then again run it, you may not see any real benefits if the
result/plan is cached from the previous run.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)

"Select TOP " question

I ran two query: one with order by, one does not.
Note that a clustered index has been build on column
DCN, CO_cd. No index on Sta_Rsn_Cd.
1) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
order by Sta_Rsn_Cd
2) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
In BOL
"If a SELECT statement that includes TOP also has an ORDER BY clause,
the rows to be returned are selected from the ordered result set. The
entire result set is built in the specified order and the top n rows in
the ordered result set are returned."
It seems that 2) would run faster than 1). When I run,
1) is faster than 2). I use SQL 2000. Any comments?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!It's hard to say from this. Can you run the SHOWPLAN for each of these
commands and include that here as well.
Rick Sawtell
"YB" <yb@.dex.com> wrote in message
news:eidd8xPoEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I ran two query: one with order by, one does not.
> Note that a clustered index has been build on column
> DCN, CO_cd. No index on Sta_Rsn_Cd.
> 1) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> order by Sta_Rsn_Cd
> 2) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> In BOL
> "If a SELECT statement that includes TOP also has an ORDER BY clause,
> the rows to be returned are selected from the ordered result set. The
> entire result set is built in the specified order and the top n rows in
> the ordered result set are returned."
> It seems that 2) would run faster than 1). When I run,
> 1) is faster than 2). I use SQL 2000. Any comments?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

"Select TOP " question

I ran two query: one with order by, one does not.
Note that a clustered index has been build on column
DCN, CO_cd. No index on Sta_Rsn_Cd.
1) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
order by Sta_Rsn_Cd
2) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
In BOL
"If a SELECT statement that includes TOP also has an ORDER BY clause,
the rows to be returned are selected from the ordered result set. The
entire result set is built in the specified order and the top n rows in
the ordered result set are returned."
It seems that 2) would run faster than 1). When I run,
1) is faster than 2). I use SQL 2000. Any comments?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
It's hard to say from this. Can you run the SHOWPLAN for each of these
commands and include that here as well.
Rick Sawtell
"YB" <yb@.dex.com> wrote in message
news:eidd8xPoEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I ran two query: one with order by, one does not.
> Note that a clustered index has been build on column
> DCN, CO_cd. No index on Sta_Rsn_Cd.
> 1) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> order by Sta_Rsn_Cd
> 2) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> In BOL
> "If a SELECT statement that includes TOP also has an ORDER BY clause,
> the rows to be returned are selected from the ordered result set. The
> entire result set is built in the specified order and the top n rows in
> the ordered result set are returned."
> It seems that 2) would run faster than 1). When I run,
> 1) is faster than 2). I use SQL 2000. Any comments?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

"SELECT DISTINCT and ORDER BY"-problem

Hi!

I can't seem to get my query to work. It returns (translated from Swedish):

Microsoft JET Database Engine (0x80004005)
The ORDER BY-instruction (myDate)..."doesn't work with"...DISTINCT.

This is my query (somewhat simplified):

set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT DISTINCT DATEPART(year, myDate) AS 'myColumn_alias' FROM myDB"
sql=sql & " WHERE myName='" & myName & "'"
sql=sql & " ORDER BY myColumn_alias"
rs.Open sql,conn

Is it because I have a WHERE-clause? If so, how should I design my query (I need only the years to populate a "select from" dropdownbox)? Or is it something else?

ThanksI don't think you can refer to a column alias the samw way you refer to a column.

Try:

ORDER BY DATEPART(year, myDate)|||I've tried that, but then I get the message that too few parameters are given and that 1 is expected.

:(|||All I had to do was to use YEAR(myDate) instead of DATEPART(year, myDate). Now it works!

"Save" and "Open" in Query Designer

As far as I can tell, there is no way to save a query and open it later
in Query Designer, right?
As far as I know, outside of the flawed and obsolete MSQuery, there is
no ad hoc data editor in SQL Server 2000 tools besides Query Designer,
right?
By now, you probably see what I'm getting at- I need a tool like Query
Designer that can be used to edit data without having to go through the
overhead of recomposing the query or cutting and pasting it into Query
Designer. My conception of it would be to have something like the DTS
folder in EM for queries where they could be created or opened.
Just wanted to check that I am not missing something.
Thanks,
JP
SocSecTrainWreck@.earthlink.net wrote:
> As far as I can tell, there is no way to save a query and open it later
> in Query Designer, right?
> As far as I know, outside of the flawed and obsolete MSQuery, there is
> no ad hoc data editor in SQL Server 2000 tools besides Query Designer,
> right?
> By now, you probably see what I'm getting at- I need a tool like Query
> Designer that can be used to edit data without having to go through the
> overhead of recomposing the query or cutting and pasting it into Query
> Designer. My conception of it would be to have something like the DTS
> folder in EM for queries where they could be created or opened.
> Just wanted to check that I am not missing something.
> Thanks,
> JP
>
Use Query Analyzer. That's what it's for. And you can save your
scripts and rerun them whenever you want to.
Simon Worth
|||Simon Worth wrote:[vbcol=seagreen]
> SocSecTrainWreck@.earthlink.net wrote:
later[vbcol=seagreen]
is[vbcol=seagreen]
Designer,[vbcol=seagreen]
Query[vbcol=seagreen]
the[vbcol=seagreen]
Query[vbcol=seagreen]
DTS
> Use Query Analyzer. That's what it's for. And you can save your
> scripts and rerun them whenever you want to.
I guess I was missing something. All these years of using
ISQL/ISQLW/Query Analyzer and I never realized that you could edit data
with it. Thanks.
JP
|||SocSecTrainWreck@.earthlink.net wrote:
> Simon Worth wrote:
>
> later
>
> is
>
> Designer,
>
> Query
>
> the
>
> Query
>
> DTS
>
> I guess I was missing something. All these years of using
> ISQL/ISQLW/Query Analyzer and I never realized that you could edit data
> with it. Thanks.
> JP
>
You can edit it as long as there is a primary key supplied within the query.
Simon Worth
|||I think I misunderstood what you were saying.
When you refer to Query Designer, what tool are you referring to? The
tool that is built into EM (IE right click table, goto Open Table > Query)?
In Query Analyzer, if you right click a table and click Open, it will
return the contents of the table. You can modify the data within the
table if there is a primary key assigned.
I'm not sure if you can edit views through QA, but I doubt it.
Sorry for the confusion there.
Simon Worth
Simon Worth wrote:
> SocSecTrainWreck@.earthlink.net wrote:
> You can edit it as long as there is a primary key supplied within the
> query.
>
|||Simon Worth wrote:
> I think I misunderstood what you were saying.
> When you refer to Query Designer, what tool are you referring to?
The
> tool that is built into EM (IE right click table, goto Open Table >
Query)?
That's the one.

> In Query Analyzer, if you right click a table and click Open, it will

> return the contents of the table. You can modify the data within the

> table if there is a primary key assigned.
I don't see how either one will allow you to open a saved select
statement on a table, run it, and then edit the results. In the Query
Designer in EM you can edit the SQL statement to return whatever data
you want and then edit the result set. Changes are saved as you move
from one cell to another. But you can't save the select statement and
then reopen it later if you need to repeat the process for whatever
reason.
I was not aware that there was any way to edit tables in QA; is there
any way at all to restrict or order the results?

> I'm not sure if you can edit views through QA, but I doubt it.
I didn't want to have to create actual views. I wanted to be able to
save the select statement to a file that I could open and run.
|||QA won't let you run a script and edit the results, so that won't work.
But an option could be (not a good one however), to save the SQL script
in any text editor, and when you want to edit again in Query Designer,
just copy/paste the script from the text file into the SQL pane in Query
Designer.
Simon Worth
SocSecTrainWreck@.earthlink.net wrote:
> Simon Worth wrote:
>
> The
>
> Query)?
> That's the one.
>
>
>
>
> I don't see how either one will allow you to open a saved select
> statement on a table, run it, and then edit the results. In the Query
> Designer in EM you can edit the SQL statement to return whatever data
> you want and then edit the result set. Changes are saved as you move
> from one cell to another. But you can't save the select statement and
> then reopen it later if you need to repeat the process for whatever
> reason.
> I was not aware that there was any way to edit tables in QA; is there
> any way at all to restrict or order the results?
>
>
> I didn't want to have to create actual views. I wanted to be able to
> save the select statement to a file that I could open and run.
>
|||Enterprise Manager has never been build to edit data. Either you'll have to
use the application that's using the database, or you'll have to use Query
Analyser and then learn SQL so you are able to edit/update the data the way
you need.
Personally I'd say that editing data "outside" you application, is only for
the cases where you need to do mass changes of data or something has went
wrong with the "logic" in the data, so the application isn't able to handle
the data. In both cases you should only mess with the data manually if you
know SQL - and then it's no problem to run the proper commands in Query
Analyzer...:-).
Regards
Steen
SocSecTrainWreck@.earthlink.net wrote:
> Simon Worth wrote:
> That's the one.
>
>
> I don't see how either one will allow you to open a saved select
> statement on a table, run it, and then edit the results. In the Query
> Designer in EM you can edit the SQL statement to return whatever data
> you want and then edit the result set. Changes are saved as you move
> from one cell to another. But you can't save the select statement and
> then reopen it later if you need to repeat the process for whatever
> reason.
> I was not aware that there was any way to edit tables in QA; is there
> any way at all to restrict or order the results?
>
> I didn't want to have to create actual views. I wanted to be able to
> save the select statement to a file that I could open and run.

"REPLACE" used in query

I'm not sure how to use REPLACE here. The Query following REPLACE returns a string whose format is A B C D and I'm trying to convert it to 'A','B','C','D' I'm doing something wrong because query analyzer doesn't like something about the way I've written this.

WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @.UserID) AND (Portfolio_Name = @.Portfolio_Name),'''',''')

Entire SPROC
------------------------------
CREATE PROCEDURE _premium_BSH (@.Portfolio_Name NVarChar (50), @.UserID int, @.Symbol VarChar (1500)) AS

SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal,
a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net AS [Net Margin],
a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo, a_Hyperlinks.MSN_10Qs AS Financials, a_Hyperlinks.MSN_events AS Events,
a_Hyperlinks.StockCharts AS Technicals
FROM a_Financials INNER JOIN
a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.Yahoo_Main INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN
a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @.UserID) AND (Portfolio_Name = @.Portfolio_Name),'''',''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = '2003 Q3')
ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal
GOI see what you're trying to do here - and it will only work with dynamic SQL.

You want to construct a query in the form of:

select ... from ... where Symbol in ('A', 'B', 'C', 'D', 'E')

from a string of 'A B C D E'

You'll need to do this:

declare @.Query varchar(8000)
declare @.Search varchar(100)
set @.Search = 'A B C D E'
set @.Query = 'select ... from ... where Symbol in (''' + replace(@.Search, ' ', ''', ''') + ''')'
exec(@.Query)

Have fun. Just one more thing though, if you can post me your table definitions, I can help you normalise them. They're not normalised from what I can see in your query above.|||post me your table definitions

how do I do that?|||Is this what you mean?

CREATE PROCEDURE _premium_BSH (@.Portfolio_Name NVarChar (50), @.UserID int, @.Symbol VarChar (1500)) AS

declare @.Query varchar(8000)

declare @.Search varchar(1000)

set @.Search = 'SELECT Portfolio_Symbols
FROM a_Users_Portfolios
WHERE (UserID = ''' + @.UserID + ''') AND (Portfolio_Name = ''' + @.Portfolio_Name + ''')'

set @.Query = 'SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal,
a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net AS [Net Margin],
a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo, a_Hyperlinks.MSN_10Qs AS Financials, a_Hyperlinks.MSN_events AS Events,
a_Hyperlinks.StockCharts AS Technicals
FROM a_Financials INNER JOIN
a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.Yahoo_Main INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN
a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
WHERE (a_Name_Symbol.Symbol IN (''' + replace(@.Search, ' ', ''', ''') + ''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = ''2003 Q3'')
ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal'

exec(@.Query)

GO|||No, I mean post me your tables - their names, and columns.

e.g.
TableName (Col1 DataType, Col1 DataType, ...)
...|||imsmart.info

LOL|||Hmm, I'm getting more and more confused as to what your inputs are (e.g. what is an example value of @.Symbol) so I'm going to give up. You get the idea, though? With the dynamic query?|||LOL with what?|||This problem is related to your non-normalised database. Normalise your DB and the whole issue will disappear.

"query notification" problem

I am using query notification and have the following problem:
I tegister once, then change the DB table manually to test. a msg is printed
to the log proving the call back function was called. I can repeat this
several time.
BUT, I then go away for 10 min', and come back. Now, when I change the data
in the DB nothing happens!
any clue how to fix and undertand this?
thanksUPDATE:
I used trace to see what is going on in the database. So I see that I get
"subscription fired" followed by "subscription registered" everytime I do
changes to the database.
After I stop doing any changes to the database for 5 minuts, and re-change
the data there, I see the database fire the "subscription fired" event, but
this time there is no "subscription registered" event. I also do not get the
log printout that I have in my web ASP.NET application, that is supposed to
write a line in the callback function.
So the hear is the deal:
The callback function gets the event fired for the first few times, so I am
doing the registration right. but, somehting happens to the ASP.NET
aplication, or the DB connection or something, that causes the callback
function not to be called after a certain time. anyone has an idea of what
am I up against? why is this happening?
thanks|||Any idea what is this error I get?
The query notification dialog on conversation handle
'{2FD61BF6-44B8-DA11-8B4E-00123F74CFF6}.' closed due to the following error:
'<?xml version="1.0"?><Error
xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Co
de><Description>Cannot
find the remote service
'SqlQueryNotificationService-657d36e5-7654-44a2-8137-5ec5e3417cc4'
because it does not exist.</Description></Error>'.
?|||It means that service named
'SqlQueryNotificationService-657d36e5-7654-44a2-8137-5ec5e3417cc4' does not
exist in the database where the notifications should be delivered to. Seems
like you are adding the broker instance at the end of the service name. The
service name you pass to the SqlNotificationRequest.Options must match the
service name you created in the database.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"csmba" <csmba@.nowhere.com> wrote in message
news:u4e2l3fTGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Any idea what is this error I get?
> The query notification dialog on conversation handle
> '{2FD61BF6-44B8-DA11-8B4E-00123F74CFF6}.' closed due to the following
> error: '<?xml version="1.0"?><Error
> xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</
Code><Description>Cannot
> find the remote service
> 'SqlQueryNotificationService-657d36e5-7654-44a2-8137-5ec5e3417cc4&apo
s;
> because it does not exist.</Description></Error>'.
>
> ?
>

"Query is too Complex"

OK, first let me say that I am no DB person.

But I have a user here who keeps getting this error
whenever she does, whatever it is she does, with
databases...

A google search takes me to various forums where
I am forced to sign up before I can read any answers.

Interesting note here is that the guy in the office next
to her can run this same query without any problems.

So, can this be a hardware issue?
If I threw more RAM at this problem would that do?

Or is this strickly a coding issue?
Why would one machine run this query ok, yet another
machine generates this error?

Any pointers that you guys could throw my way would
be greatly appreciated.
Thanks

*$Starbuck,

I am afraid you haven't provided enough information to be of help. What
error is she getting? What statement is she running? etc.

By the way, you can search the archive of the public Microsoft
SQL-Server forums on Google (without registration). Have a look at
http://groups.google.com/groups?hl=...ublic.sqlserver

Hope this helps,
Gert-Jan

Starbuck wrote:
> OK, first let me say that I am no DB person.
> But I have a user here who keeps getting this error
> whenever she does, whatever it is she does, with
> databases...
> A google search takes me to various forums where
> I am forced to sign up before I can read any answers.
> Interesting note here is that the guy in the office next
> to her can run this same query without any problems.
> So, can this be a hardware issue?
> If I threw more RAM at this problem would that do?
> Or is this strickly a coding issue?
> Why would one machine run this query ok, yet another
> machine generates this error?
> Any pointers that you guys could throw my way would
> be greatly appreciated.
> Thanks
> *$

--
(Please reply only to the newsgroup)|||The error message is "Query is too complex".

The Query that is being run combines 2 tables into
a table query. There is only one join.

The issue seems to be that this query runs on another
machine just fine... They have similar hardware,
and software, but it fails with a "Query is too complex"
error on her machine.

We dont feel that this is in fact a "complex" query.

Point is, I need this to run on her machine, but I am
unable to determine what the differences are.

Could this be a connection issue?

I'm really shooting in the dark here, so any suggestions
are greatly appreciated.

thanks again.
*$

On Thu, 05 Aug 2004 19:43:08 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:

>Starbuck,
>I am afraid you haven't provided enough information to be of help. What
>error is she getting? What statement is she running? etc.
>By the way, you can search the archive of the public Microsoft
>SQL-Server forums on Google (without registration). Have a look at
>http://groups.google.com/groups?hl=...ublic.sqlserver
>Hope this helps,
>Gert-Jan
>
>Starbuck wrote:
>>
>> OK, first let me say that I am no DB person.
>>
>> But I have a user here who keeps getting this error
>> whenever she does, whatever it is she does, with
>> databases...
>>
>> A google search takes me to various forums where
>> I am forced to sign up before I can read any answers.
>>
>> Interesting note here is that the guy in the office next
>> to her can run this same query without any problems.
>>
>> So, can this be a hardware issue?
>> If I threw more RAM at this problem would that do?
>>
>> Or is this strickly a coding issue?
>> Why would one machine run this query ok, yet another
>> machine generates this error?
>>
>> Any pointers that you guys could throw my way would
>> be greatly appreciated.
>> Thanks
>>
>> *$|||Starbuck (Starbuck@.BogusDomain.com) writes:
> The error message is "Query is too complex".
> The Query that is being run combines 2 tables into
> a table query. There is only one join.
> The issue seems to be that this query runs on another
> machine just fine... They have similar hardware,
> and software, but it fails with a "Query is too complex"
> error on her machine.
> We dont feel that this is in fact a "complex" query.
> Point is, I need this to run on her machine, but I am
> unable to determine what the differences are.
> Could this be a connection issue?
> I'm really shooting in the dark here, so any suggestions
> are greatly appreciated.

If you are in the dark, guess how dark we are in, who don't even know
what environment you are using, or how the query looks like.

That much I can tell, that the message "Query is too complex" does not
appear in master..sysmessages, so it is not likely to be a message from
SQL Server. To be able to assist further we need to see the query,
we need to know what environment the user gets this error in, and we
need to know which database engine you are working with. (It should be
MS SQL Server, else you are posting to the wrong newsgroup.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Starbuck" <Starbuck@.BogusDomain.com> wrote in message
news:spk4h0t52bf29a93mcpvbb1pjs7sa3hqk5@.4ax.com...
> OK, first let me say that I am no DB person.
> But I have a user here who keeps getting this error
> whenever she does, whatever it is she does, with
> databases...
> A google search takes me to various forums where
> I am forced to sign up before I can read any answers.
> Interesting note here is that the guy in the office next
> to her can run this same query without any problems.
> So, can this be a hardware issue?
> If I threw more RAM at this problem would that do?
> Or is this strickly a coding issue?
> Why would one machine run this query ok, yet another
> machine generates this error?
> Any pointers that you guys could throw my way would
> be greatly appreciated.
> Thanks
> *$

I've never heard of this error before - as Erland pointed out, this is not a
native MSSQL error - however some Googling suggests that it is likely to be
either an MS Access issue, or something related to some very old data access
components (the most recent exact hit for this error in Google newsgroups is
in the year 2000). For example:

http://support.microsoft.com/defaul...&NoWebContent=1

http://support.microsoft.com/defaul...&NoWebContent=1

http://www.dotnet247.com/247referen.../43/216266.aspx

http://www.google.com/search?source...ery+too+complex

Without more information, however, it's very difficult for anyone to give
any good advice. What is the database (Access, MSSQL), what is the client
(Access, in-house app, third-party app), what are the operating systems, the
servicepacks, how is the query submitted, what is the definition of the
query and tables, what difference is there between the workstations where
the query works and does not (OS, hardware) etc.

My best guess is that you are querying an MS Access database, and the "guy
in the next office" has a more recent Windows version than the user with the
error (and therefore more recent data access components), but that's purely
speculation and very likely to be wrong. If you're not using MSSQL, then as
Erland said, you will get a better response in a forum dedicated to your
database platform.

Simon

"Query Cost (relative to the batch)" in Query Analyzer

Hi,
I'm trying to troubleshoot a slow-running query which is part of a
stored procedure. When I run the query as it is - it takes 53 seconds
to run.
--
Example original query:
SELECT DISTINCT SP.SP_ID
FROM StandardProject SP
INNER JOIN Classes cls ON SP.Cls_ID = cls.Cls_ID
INNER JOIN Groups ON cls.Cls_ID = Groups.Cls_ID
INNER JOIN ClassesGroups ON ClassesGroups.groupid = Groups.groupid
WHERE ClassesGroups.StudentID = 7615
AND ((AccessReport & 2) > 0 OR (AccessReport & 8) > 0)
AND SP.ProjectType = 0
AND Groups.status = 1
AND exists (SELECT SP_ID FROM Schedules WHERE SP_ID = SP.SP_ID)
--
If I removed the most time-consuming inner join tables and set this
part to insert values needed by the rest of the query into a table
variable and ran both queries (INSERT INTO table variable and the
SELECT), the query finishes in 0 seconds.
I've used the "Query Cost (relative to the batch)" in Query Analyzer
counter to analyze the performance of queries in the past (this value
displays when Show Execution Plan is set). In this case I'm
because when I run the original SELECT query and the modified query
(INSERT INTO table variable and then SELECT) - it says the Query Cost
relative to the batch for the first query is 35%, the INSERT = 64%, the
SELECT = 1%.
Why does the first query COST only 35% when it takes the longest to
finish? (53 seconds as opposed to under 1 second)? Does this mean that
the first query will perform better under load?
--
According to SQL Server Performance.com:
"Sometimes, it is valuable to compare two more queries at the same
time, checking to see which one is the better performer. This is
especially true if you have a query that you have rewritten two or more
different ways, and you want to find out which variation of the query
is the most efficient.
While you can analyze each query, one at a time in Query Analyzer, one
trick you should consider trying is to place all of the queries you
want to compare in Query Analyzer, turn on "Show Execution Plan," then
run all the queries at once. When you do this, the "Execution Plan"
window will divide into a separate window for each query ran, allowing
you to compare their execution plans much easier. In addition, in each
execution plan window, you will see a "Query Cost (relative to the
batch)" number. What this number is telling you is how long each query
took as a percentage of the total amount of time it took all of the
queries to run. The query with the lowest number is the query that
performed the quickest, and so on.
As you can imagine, this information can make it much easier to compare
different queries."
--
So what else should I be taking into account? What am I missing here?
Thank you,
Smitha
SQL Server DBAsmithabreddy@.gmail.com,
When comparing two or more queries, it is fair to:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
before each one is executed. (DO NOT DO THIS ON A PRODUCTION SERVER)
AMB
"smithabreddy@.gmail.com" wrote:

> Hi,
> I'm trying to troubleshoot a slow-running query which is part of a
> stored procedure. When I run the query as it is - it takes 53 seconds
> to run.
> --
> Example original query:
> SELECT DISTINCT SP.SP_ID
> FROM StandardProject SP
> INNER JOIN Classes cls ON SP.Cls_ID = cls.Cls_ID
> INNER JOIN Groups ON cls.Cls_ID = Groups.Cls_ID
> INNER JOIN ClassesGroups ON ClassesGroups.groupid = Groups.groupid
> WHERE ClassesGroups.StudentID = 7615
> AND ((AccessReport & 2) > 0 OR (AccessReport & 8) > 0)
> AND SP.ProjectType = 0
> AND Groups.status = 1
> AND exists (SELECT SP_ID FROM Schedules WHERE SP_ID = SP.SP_ID)
> --
> If I removed the most time-consuming inner join tables and set this
> part to insert values needed by the rest of the query into a table
> variable and ran both queries (INSERT INTO table variable and the
> SELECT), the query finishes in 0 seconds.
> I've used the "Query Cost (relative to the batch)" in Query Analyzer
> counter to analyze the performance of queries in the past (this value
> displays when Show Execution Plan is set). In this case I'm
> because when I run the original SELECT query and the modified query
> (INSERT INTO table variable and then SELECT) - it says the Query Cost
> relative to the batch for the first query is 35%, the INSERT = 64%, the
> SELECT = 1%.
> Why does the first query COST only 35% when it takes the longest to
> finish? (53 seconds as opposed to under 1 second)? Does this mean that
> the first query will perform better under load?
> --
> According to SQL Server Performance.com:
> "Sometimes, it is valuable to compare two more queries at the same
> time, checking to see which one is the better performer. This is
> especially true if you have a query that you have rewritten two or more
> different ways, and you want to find out which variation of the query
> is the most efficient.
> While you can analyze each query, one at a time in Query Analyzer, one
> trick you should consider trying is to place all of the queries you
> want to compare in Query Analyzer, turn on "Show Execution Plan," then
> run all the queries at once. When you do this, the "Execution Plan"
> window will divide into a separate window for each query ran, allowing
> you to compare their execution plans much easier. In addition, in each
> execution plan window, you will see a "Query Cost (relative to the
> batch)" number. What this number is telling you is how long each query
> took as a percentage of the total amount of time it took all of the
> queries to run. The query with the lowest number is the query that
> performed the quickest, and so on.
> As you can imagine, this information can make it much easier to compare
> different queries."
> --
> So what else should I be taking into account? What am I missing here?
> Thank you,
> Smitha
> SQL Server DBA
>|||I performed the compare after running both DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS.|||But did you run this between each query, or before the entire batch?
<smithabreddy@.gmail.com> wrote in message
news:1147268627.698501.322300@.v46g2000cwv.googlegroups.com...
> I performed the compare after running both DBCC FREEPROCCACHE and DBCC
> DROPCLEANBUFFERS.
>|||To capture relative cost information (compare the cost of both
approaches), I ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS first
and then ran both batches. This is where I get cost information for
the 53 seconds duration query = 34% and cost for the under 1 second
query = 76%.
I then ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS, ran the
original query. Took 1.05 minutes to run the first time and 54 seconds
to run the second time.
Ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS again, then ran the
modified query (with INSERT INTO table variable and then SELECT). Took
6 seconds to run the first time and 0 seconds to run the second time.
Basically -
I'd been relying on this measure (relative cost for each query in a
batch) somewhat to determine which query is more efficient. And when
both queries return values in under 1 second - I figured it would be
better to go with the lower cost option. But what happens in a
situation such as this where the lower cost query take SO much longer
to run?
I review the output from SET STATISTICS IO ON, Query execution time and
Execution plan to determine if a query has the most efficient
construct. This relative cost information seems counter-intuitive to
me...what rules of thumb should I use to guage query efficiency?|||This is puzzling, and I don't really know the answer, but off the top of my
head I can think of a possibility. This is more a guess than anything, so
take it as such.
I believe the cost considers IO, Memory, and CPU, among other things. I
don't think run time is necessarily as big an issue as the other resources.
It is possible that the faster query is using much more of one of these
resources, which results in the higher cost calculation, even though it
finishes in less time.
i.e. (these numbers are completely bogus, included only to illustrate the
idea):
Query1 uses a lot of IO (5,000) and CPU (5,000), but virtually no memory
(10), and finishes in 60 seconds.
Query2 uses minimal IO (10) and CPU (10) but a lot of memory (10,000).
Because it is using memory without the more time consuming IO or CPU usage,
everything is much faster.
Now, the example above is highly flawed and over simplified, but hopefully
it illustrates my point anyway.
The faster query may actually use more resources over all, and although it
runs faster in test, it may perform slower under peak usage (depending on
your available resources).
I have seen cases where a query would run in 30 seconds, but use 100 percent
of the CPU and make all other transactions grind to a halt, where a slight
change to the query would make it run in 60 seconds on 5 percent CPU and
everything else continued to run without issue.
I guess the point is that run time by itself is not always the best
indication of efficiency, and there is not a simple answer.
<smithabreddy@.gmail.com> wrote in message
news:1147280303.752842.58360@.v46g2000cwv.googlegroups.com...
> To capture relative cost information (compare the cost of both
> approaches), I ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS first
> and then ran both batches. This is where I get cost information for
> the 53 seconds duration query = 34% and cost for the under 1 second
> query = 76%.
> I then ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS, ran the
> original query. Took 1.05 minutes to run the first time and 54 seconds
> to run the second time.
> Ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS again, then ran the
> modified query (with INSERT INTO table variable and then SELECT). Took
> 6 seconds to run the first time and 0 seconds to run the second time.
> Basically -
> I'd been relying on this measure (relative cost for each query in a
> batch) somewhat to determine which query is more efficient. And when
> both queries return values in under 1 second - I figured it would be
> better to go with the lower cost option. But what happens in a
> situation such as this where the lower cost query take SO much longer
> to run?
> I review the output from SET STATISTICS IO ON, Query execution time and
> Execution plan to determine if a query has the most efficient
> construct. This relative cost information seems counter-intuitive to
> me...what rules of thumb should I use to guage query efficiency?
>|||Makes sense...although I've found quite often that something making
sense to me doesn't amount to a hill of beans when dealing with the
OPTIMIZER... :)
I will check this out. Thanks very much.|||Please let me know what you find. I am curious to see if my theory holds
any water.
<smithabreddy@.gmail.com> wrote in message
news:1147289299.238269.87340@.g10g2000cwb.googlegroups.com...
> Makes sense...although I've found quite often that something making
> sense to me doesn't amount to a hill of beans when dealing with the
> OPTIMIZER... :)
> I will check this out. Thanks very much.
>|||So the optimizer has estimated that the first query will use 34% of the
total elapsed time and the second query the remaining 76%, but in
reality the first query is taking 98% of the time and the second query
2%.
There could be a couple of things going on:
a) the table statistics might be out of date. This could easily explain
the incorrect relative cost, and cause the optimizer to choose a
suboptimal plan. When in doubt run UPDATE STATISTICS on all tables in
the query, preferably WITH FULLSCAN.
b) the data distribution of one the the table columns could be very
a-typical. If the optimizer has no usuable statistics for that column
and the column is used in the predicates, then this could result in a
poor query plan. This could be the case with column AccessReport when
evaluating the expression ((AccessReport & 2) > 0 OR (AccessReport & 8)
> 0). If you know how to read query plans, you might be able to determine if that is
the case. Also, the IO statistics would show a marked difference between the fast a
nd slow query with respect to that table.
c) maybe the original query runs into a bug/flaw in the optimizer. For
example the inappropriate use of parallellism (when in doubt, add OPTION
(MAXDOP 1) to the query).
By the way, you can rewrite
((AccessReport & 2) > 0 OR (AccessReport & 8) > 0)
as
AccessReport & (8+2) > 0
which might run a tiny bit faster.
Also, if the column SP.ProjectType or Groups.status is of data type bit,
then make sure you explicitely convert the literal to a bit. For
example, if Groups.status is a bit, then write
AND Groups.status = CAST(1 as bit)
And finally, if column StandardProject.SP_ID is unique, then you can
eliminate the DISTINCT keyword by replacing the joins that could cause
duplicates by pushing them to the EXISTS clause.
HTH,
Gert-Jan
smithabreddy@.gmail.com wrote:
> To capture relative cost information (compare the cost of both
> approaches), I ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS first
> and then ran both batches. This is where I get cost information for
> the 53 seconds duration query = 34% and cost for the under 1 second
> query = 76%.
> I then ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS, ran the
> original query. Took 1.05 minutes to run the first time and 54 seconds
> to run the second time.
> Ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS again, then ran the
> modified query (with INSERT INTO table variable and then SELECT). Took
> 6 seconds to run the first time and 0 seconds to run the second time.
> Basically -
> I'd been relying on this measure (relative cost for each query in a
> batch) somewhat to determine which query is more efficient. And when
> both queries return values in under 1 second - I figured it would be
> better to go with the lower cost option. But what happens in a
> situation such as this where the lower cost query take SO much longer
> to run?
> I review the output from SET STATISTICS IO ON, Query execution time and
> Execution plan to determine if a query has the most efficient
> construct. This relative cost information seems counter-intuitive to
> me...what rules of thumb should I use to guage query efficiency?|||a) the table statistics might be out of date. This could easily explain
the incorrect relative cost, and cause the optimizer to choose a
suboptimal plan. When in doubt run UPDATE STATISTICS on all tables in
the query, preferably WITH FULLSCAN.
--> No difference after updating stats with full scan for all tables
involved. Issued DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS before
executing the queries.
b) Wouldn't the stats below suggest that the table variable option is
more efficient?
Fast Query: IO Stats for Groups:
Table 'Groups'. Scan count 0, logical reads 111026, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Slow Query: IO Stats for Groups:
Table 'Groups'. Scan count 0, logical reads 27419652, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
When I looked at the costs associated with the various nodes in the
execution plan - the one cost that is consistently higher than the
others for the fast query is: Estimated Operator Cost which then causes
the Estimated Subtree Cost for that node to be higher. According to
SQL Server 2005 BOL:
Estimated Operator Cost = The cost to the query optimizer for executing
this operation. The cost of this operation as a percentage of the total
cost of the query is displayed in parentheses. Because the query engine
selects the most efficient operation to perform the query or execute
the statement, this value should be as low as possible.
Estimated Subtree Cost = The total cost to the query optimizer for
executing this operation and all operations preceding it in the same
subtree.
Does this mean that this is just an estimation or that this is a real
cost which will affect the SP each time it executes?
--
Thanks again.

"query contained only ignored words "?

When I try to search a word by an ASP web page, it returns error '80040e14
, The query contained only ignored words'. I know the question maybe asked
for many many times and I do search the Internet but cannot find the answer
right for me.
I use Windows 2003 SP1 and SQL server 2000, all are Chinese Simplified
Edition. According to some articles, I empty the noise.chs in the C:
\windows\system32 and C:\Program Files\Common Files\System\MSSearch\Data
\Config, then re-index the fulltext. But the error still exists. Pls help
me, thanks.
Hi. I've got the answer. I add a space to the noise.chs instead of clear
all the content. thanks!
mizi <haha@.haha.com> wrote in
news:Xns9725D5AB6E7B0hahahahacom@.207.46.248.16:

> When I try to search a word by an ASP web page, it returns error
> '80040e14 , The query contained only ignored words'. I know the
> question maybe asked for many many times and I do search the Internet
> but cannot find the answer right for me.
> I use Windows 2003 SP1 and SQL server 2000, all are Chinese Simplified
> Edition. According to some articles, I empty the noise.chs in the C:
> \windows\system32 and C:\Program Files\Common
> Files\System\MSSearch\Data \Config, then re-index the fulltext. But
> the error still exists. Pls help me, thanks.
>

"Order by" by parameter in stored procedure

I would like to pass the name of a column to a stored procedure, so the
result of the query would be ordered by that column.
I tried this:
CREATE Procedure ProductsByTab
(
@.TabID int,
@.Order nvarchar (50)
)
AS
SELECT
*
FROM
Product
WHERE
TabID = @.TabID
ORDER BY
@.Order
GO
but I get this message:
Error 1008: The SELECT item identified by the ORDER BY number 1 contains a
variable as part of the expression identifying a column position. Variables
are only allowed when ordering by an expression referencing a column name.
Is it possible to do what I want? What am I doing wrong?
Thank you.i have the same thing but i use a different approach, i pass a flag
representing the numeric order of the field
select * from ttt
order by case @.flag when 1 then name when 2 then description else '' end,
case @.flag when 3 then amount else 0 end,name,description,amount
"Carlos Santos" wrote:

> I would like to pass the name of a column to a stored procedure, so the
> result of the query would be ordered by that column.
> I tried this:
> CREATE Procedure ProductsByTab
> (
> @.TabID int,
> @.Order nvarchar (50)
> )
> AS
> SELECT
> *
> FROM
> Product
> WHERE
> TabID = @.TabID
> ORDER BY
> @.Order
> GO
> but I get this message:
> Error 1008: The SELECT item identified by the ORDER BY number 1 contains a
> variable as part of the expression identifying a column position. Variable
s
> are only allowed when ordering by an expression referencing a column name.
> Is it possible to do what I want? What am I doing wrong?
> Thank you.|||How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"Carlos Santos" wrote:

> I would like to pass the name of a column to a stored procedure, so the
> result of the query would be ordered by that column.
> I tried this:
> CREATE Procedure ProductsByTab
> (
> @.TabID int,
> @.Order nvarchar (50)
> )
> AS
> SELECT
> *
> FROM
> Product
> WHERE
> TabID = @.TabID
> ORDER BY
> @.Order
> GO
> but I get this message:
> Error 1008: The SELECT item identified by the ORDER BY number 1 contains a
> variable as part of the expression identifying a column position. Variable
s
> are only allowed when ordering by an expression referencing a column name.
> Is it possible to do what I want? What am I doing wrong?
> Thank you.|||Thanks! Your replies were absolutely efective.

"Open Table Window" is read-only under SQL Query Analyzer

I have installed SQL Server 2000 with option "Client Tools Only"
I try to edit data using SQL Query Analyzer with Trusted Connection. I click on 'Open' of right-click on table objects. A "Open Table Window" is opened as read-only!!! "Add" and "Delete" are grey out!! I can't edit data.
I am trying to edit data using Server Explorer under Microsoft .NET with Trusted Connection. It works!!!
Help, Help!
Maybe the table does not have a Primary Key, check this first.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Happy Programmer" <anonymous@.discussions.microsoft.com> wrote in message
news:9D59D138-BFAE-4CAB-A2C1-FF535B42F2C7@.microsoft.com...
> I have installed SQL Server 2000 with option "Client Tools Only"
> I try to edit data using SQL Query Analyzer with Trusted Connection. I
click on 'Open' of right-click on table objects. A "Open Table Window" is
opened as read-only!!! "Add" and "Delete" are grey out!! I can't edit
data.
> I am trying to edit data using Server Explorer under Microsoft .NET with
Trusted Connection. It works!!!
> Help, Help!
|||I believe that you will only be able to modify data from the Open Table
function in Query Analyzer if the table has a primary key or unique
constraint or a unique index.
Steve Kass
Drew University
Happy Programmer wrote:

>I have installed SQL Server 2000 with option "Client Tools Only"
>I try to edit data using SQL Query Analyzer with Trusted Connection. I click on 'Open' of right-click on table objects. A "Open Table Window" is opened as read-only!!! "Add" and "Delete" are grey out!! I can't edit data.
>I am trying to edit data using Server Explorer under Microsoft .NET with Trusted Connection. It works!!!
>Help, Help!
>
|||I've tried it. It is able to modify if the table has primary key or unique index.
But is able to edit data using Server Explorer under Microsoft .NET with Trusted Connection even it has not any key or index.
Anyway, thanks a lot~!

"Max row" in each group

I'm having trouble developing a query that will group on a given set of
columns and, for each group, will also display the other columns in the
"max row" for that group. Let me illustrate what I mean:
CREATE TABLE [dbo].[GroupTest] (
[testID] [int] IDENTITY (1, 1) NOT NULL ,
[office] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startDate] [datetime] NOT NULL ,
[status] [int] NOT NULL ,
[endDate] [datetime] NULL ,
[reportDate] [datetime] NOT NULL ,
[amount] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[GroupTest] ADD
CONSTRAINT [PK_GroupTest] PRIMARY KEY CLUSTERED
(
[testID]
) ON [PRIMARY]
GO
Each row in this table represents a status update in an issue tracking
system. (The actual table has more columns. This is a subset for
illustrative purposes.) None of the columns is unique other than testID.
I am being asked to produce a query for each combination of {office,
startDate} will include the row matching that combination that has the
greatest value in the status column. So if office='ABC' and
startDate='2005-11-30' and there are two rows matching these values, one
with status=3 and one with status=4, the query should include the row
having status=4.
If the combination {office, startDate, status} were unique in this
table, the following query would do the job:
SELECT
G0.office, G0.startDate, G0.status,
G0.endDate, G0.reportDate, G0.amount
FROM GroupTest G0
INNER JOIN
(SELECT office, startDate, max(status) maxStatus
FROM GroupTest GROUP BY office, startDate) G1
ON G0.office = G1.office
AND G0.startDate = G1.startDate
AND G0.status = G1.maxStatus
order by G0.office, G0.startDate
But that's not the case. I asked the customer what to do. For their
purposes, the report will be fine if it can show them an "illustrative"
row--in other words, if more than one row in the table matches a given
combination {office, startDate, status}, the query should return one of
them arbitrarily. But I'm not figuring out how to do that. I guess I'd
like to figure out how to retrieve the "maximum row" for each {office,
startDate} combination, or something similar. Can anyone help?
I could punt and go with just displaying the maximum over each of the
columns on the right-hand side (status, endDate, reportDate, amount),
each taken independently:
SELECT
G0.office, G0.startDate, G0.status,
G0.endDate, G0.reportDate, G0.amount
FROM GroupTest G0
INNER JOIN
(SELECT office, startDate, max(status) maxStatus,
max(endDate) maxEndDate,
max(reportDate) maxReportDate,
max(amount) maxAmount
FROM GroupTest GROUP BY office, startDate) G1
ON G0.office = G1.office
AND G0.startDate = G1.startDate
AND G0.status = G1.maxStatus
AND G0.endDate = G1.maxEndDate
AND G0.reportDate = G1.maxReportDate
AND G0.amount = G1.maxAmount
order by G0.office, G0.startDate
But the rows produced by that query aren't "coherent": For a given
{office, startDate} combination, the status could be from one row, the
startDate could be from another, the endDate from another, etc. Each row
in my query should match an actual row in the table.I've moved on from my previous question, having figured out an approach.
However, I'm stuck on that approach because I'm getting unexpected
results. I'm asking about that in a new thread.

"Live" queries in sql 2005 ?

A colleague was telling me the other day that in Sql Server 2005 there is a
special type of query you can do, whereby it runs in the background and
brings back results automatically, whenever they change. He referred to it
as something called a "live" query, but I'm not sure that's correct
terminology.
In theory you could run such a query, and sit back and watch the results
change in Query Analyser (or wherever). For example if you were monitoring
total sales, you could literally run the query and it would "auto update" in
front of your eyes, as sales increased.
Then you could theoretically write a client application with, say, a grid
which was bound to the "live query" and updated itself magically.
One could write a client app which polled the database every 2 seconds or
whatever, but that's not as efficient as picking up data *only* when it
changes.
Obviously it depends on the client limitations, but has anyone heard of such
a thing in SQL 2005?
Thanks,
Owen"Owen" <spam@.spam.com> wrote in message
news:lZOdnfeFS9oemgbZnZ2dnUVZ8smdnZ2d@.pi
pex.net...
>A colleague was telling me the other day that in Sql Server 2005 there is a
>special type of query you can do, whereby it runs in the background and
>brings back results automatically, whenever they change. He referred to it
>as something called a "live" query, but I'm not sure that's correct
>terminology.
>
Query Notification.
Query Notifications in ADO.NET 2.0
http://msdn.microsoft.com/library/d...otification.asp
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uaX3ADllGHA.3740@.TK2MSFTNGP02.phx.gbl...
> "Owen" <spam@.spam.com> wrote in message
> news:lZOdnfeFS9oemgbZnZ2dnUVZ8smdnZ2d@.pi
pex.net...
> Query Notification.
> Query Notifications in ADO.NET 2.0
> http://msdn.microsoft.com/library/d...otification.asp
> David
Sounds about right, thanks very much indeed. !
Owen.
http://www.riptheuglybluevoidfromyourhead.com

"Input string was not in a correct format" when trying to searcg

hi

i have a database with some data in it and iam using full-text-search to search through my data and the search query works fine in sql server manamgnet studio. so my problem is in my web application, i have a textbox where i enter e.g car and click the search button, the button executes the search query but i recive an error"Input string was not in a correct format". here is the code that the button executes:

ProtectedSub searchButton_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

searchSqlDataSource.SelectCommand =

"Select ID, title, FROM myTable WHERE CONTAINS(description, @.search)"

searchSqlDataSource.SelectParameters.Add(

"search", searchTextBox.Text)

searchSqlDataSource.DataBind()EndSub

tnx in advac

can you set the value of a label for instance to the value of the input string so that you can see the actual string? or have you tried stepping through the code and see what the actual string is that is getting sent. if you have access to a sql management studio, you can post the string into the query window and see what is incorrect with the syntax. give those a try and good luck! -- jp|||

I often get that error message when the text contains a semicolon ";". i.e. searchTextBox.text = "johnny went home; vivi left town."

My only resolve was to filter for the semicolon, but I'm sure there's a better way to do it.

Johnny

"IF"s in a view?

I Access SQL one can imbed "IF" statements in a select query (or view).
In SQL Server, Microsoft seems to only allow the use of control statements such as IF and CASE in Stored Procedures but not in a view.

Question: Is there any way to use "IF" or "CASE" in a view? If a stored procdure is the best route, how do i access the results set afterwards?Case works fine - please post the view you tried to create.|||Here is the SQL fragment converted from ACCESS IIF's to SQL Server CASE. I don't have access to the SQL Server at the moment but I will try out your suggestion first chance I get.

CASE
WHEN [IncidentHeaderDynamicsCustNmbr] Like '99900%' THEN
[CustName],
[CntcPrsn],
[StmtName],
[Address1],
[Address2],
[City],
[State],
[Country],
[Zip],
[Phone1],
[Phone2],
[Fax],
WHEN IsNull(IncidentHeaderSiteID])=True And IsNull(IncidentHeaderClientAddressID])=True) THEN
[CustName],
[CntcPrsn],
[StmtName],
[Address1],
[Address2],
[City],
[State],
[Country],
[Zip],
[Phone1],
[Phone2],
[Fax],
ELSE
Trim([IncidentHeaderSiteCustName]))) AS InvoiceHeaderSiteCustName,
Trim([IncidentHeaderSiteCntcPrsn]))) AS InvoiceHeaderSiteCntcPrsn,
Trim([IncidentHeaderSiteStmtName]))) AS InvoiceHeaderSiteStmtName,
Trim([IncidentHeaderSiteAddress1]))) AS InvoiceHeaderSiteAddress1,
Trim([IncidentHeaderSiteAddress2]))) AS InvoiceHeaderSiteAddress2,
Trim([IncidentHeaderSiteCity]))) AS InvoiceHeaderSiteCity,
Trim([IncidentHeaderSiteState]))) AS InvoiceHeaderSiteState,
Trim([IncidentHeaderSiteCountry]))) AS InvoiceHeaderSiteCountry,
Trim([IncidentHeaderSiteZip]))) AS InvoiceHeaderSiteZip,
Trim([IncidentHeaderSitePhone1]))) AS InvoiceHeaderSitePhone1,
Trim([IncidentHeaderSitePhone2]))) AS InvoiceHeaderSitePhone2,
Trim([IncidentHeaderSiteFax]))) AS InvoiceHeaderSiteFax,
END|||The problem looks like with your syntax (check out BOL) - the case statement goes as follows:

CASE input_expression
WHEN when_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END

or

CASE
WHEN Boolean_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END

Anyway, you have case when - but you are missing the end statement - Maybe you are thinking you can return multiple results - You will have to test for each case.

"Filling in the gaps" with a single-line query

Hi,
I've got the following scenario:
Files are being stored in a database, with a number of name-value
pairs associated with each file. This happens by storing the files in
one table (File), the list of property names in another table
(FileMetaDataSchema) and the values of properties in a third table
(FileMetaData), which references both File and FileMetaDataSchema.
The frontend of my application assumes there is a record for each
property of each file in the FileMetaData table, even if the value is
an empty string. In other words, if i have a list of 3 properties and
2 files, FileMetaData will contain 6 records.
Due to a bug in the system, this does not always happen. Suppose one
adds a new property and neglects to insert the "blank" records for the
new properties for all the files, or a new file is added, but the
associated meta-data records are not... (why and how this happens is
not the topic of discussion, so don't worry about that).
I have written a sql script to insert all the missing "blank" records,
but I feel it is very clumsy and intuitively, I just know there must
be a simpler way, my knowledge is just too limited. What it does is,
it iterates (using cursors) through all the files and all the
properties, checks if there is a record for each combination FileID
and FileMetaDataSchemaID and if not, it inserts one. I am looking for
a better way out of curiosity, for my own benefit.
Here's the script and thanks for any input:
DECLARE MetadataSchemaCursor CURSOR FOR
SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
DECLARE @.FileMetaDataSchemaID INT,
@.FileID INT
OPEN MetadataSchemaCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DECLARE FileCursor CURSOR FOR
SELECT FileID FROM [File]
OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @.FileID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
FileMetaDataSchemaID = @.FileMetaDataSchemaID)
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
FETCH NEXT FROM FileCursor INTO @.FileID
END
CLOSE FileCursor
DEALLOCATE FileCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
END
CLOSE MetadataSchemaCursor
DEALLOCATE MetadataSchemaCursor>I just know there must
>be a simpler way, my knowledge is just too limited.
You are correct, there is a simpler way.
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
SELECT A.FileID, B.FileMetaDataSchemaID, ''
FROM [File] as A
CROSS
JOIN FileMetaDataSchema as B
WHERE NOT EXISTS
(select * from FileMetaData as X
where A.FileID = X.FileID
and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:48:53 -0800, "Velislav" <vgebrev@.gmail.com> wrote:

>Hi,
>I've got the following scenario:
>Files are being stored in a database, with a number of name-value
>pairs associated with each file. This happens by storing the files in
>one table (File), the list of property names in another table
>(FileMetaDataSchema) and the values of properties in a third table
>(FileMetaData), which references both File and FileMetaDataSchema.
>The frontend of my application assumes there is a record for each
>property of each file in the FileMetaData table, even if the value is
>an empty string. In other words, if i have a list of 3 properties and
>2 files, FileMetaData will contain 6 records.
>Due to a bug in the system, this does not always happen. Suppose one
>adds a new property and neglects to insert the "blank" records for the
>new properties for all the files, or a new file is added, but the
>associated meta-data records are not... (why and how this happens is
>not the topic of discussion, so don't worry about that).
>I have written a sql script to insert all the missing "blank" records,
>but I feel it is very clumsy and intuitively, I just know there must
>be a simpler way, my knowledge is just too limited. What it does is,
>it iterates (using cursors) through all the files and all the
>properties, checks if there is a record for each combination FileID
>and FileMetaDataSchemaID and if not, it inserts one. I am looking for
>a better way out of curiosity, for my own benefit.
>Here's the script and thanks for any input:
>DECLARE MetadataSchemaCursor CURSOR FOR
> SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
>DECLARE @.FileMetaDataSchemaID INT,
> @.FileID INT
>OPEN MetadataSchemaCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>WHILE (@.@.FETCH_STATUS = 0)
>BEGIN
> DECLARE FileCursor CURSOR FOR
> SELECT FileID FROM [File]
> OPEN FileCursor
> FETCH NEXT FROM FileCursor INTO @.FileID
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
>FileMetaDataSchemaID = @.FileMetaDataSchemaID)
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
>PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
> FETCH NEXT FROM FileCursor INTO @.FileID
> END
> CLOSE FileCursor
> DEALLOCATE FileCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>END
>CLOSE MetadataSchemaCursor
>DEALLOCATE MetadataSchemaCursor|||On Feb 22, 5:42 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> You are correct, there is a simpler way.
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
> SELECT A.FileID, B.FileMetaDataSchemaID, ''
> FROM [File] as A
> CROSS
> JOIN FileMetaDataSchema as B
> WHERE NOT EXISTS
> (select * from FileMetaData as X
> where A.FileID = X.FileID
> and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
> Roy Harvey
> Beacon Falls, CT
>
Thank you
Note to self - look up cross joins.