Abort a long running query

Hi
I would like to have a long running query aborted by SQL server, such that,
that query does not become a resource Hog and I can catch that in my client
side code and display a nice error message to the user.
Any way I could do that?
I am sorry if this is being posted in the wrong forum
--
MattISTS wrote:
> Hi
> I would like to have a long running query aborted by SQL server,
> such that, that query does not become a resource Hog and I can catch
> that in my client side code and display a nice error message to the
> user.
> Any way I could do that?
> I am sorry if this is being posted in the wrong forum
You can set a query timeout from your client code and roll the
transaction back if you hit the timeout. But that can only be done on
the client. From the server, you can use the SET
QUERY_GOVERNOR_COST_LIMIT server option to limit queries from runnning
that are too costly.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Abort a long running query

Hi
I would like to have a long running query aborted by SQL server, such that,
that query does not become a resource Hog and I can catch that in my client
side code and display a nice error message to the user.
Any way I could do that?
I am sorry if this is being posted in the wrong forum
Matt
ISTS wrote:
> Hi
> I would like to have a long running query aborted by SQL server,
> such that, that query does not become a resource Hog and I can catch
> that in my client side code and display a nice error message to the
> user.
> Any way I could do that?
> I am sorry if this is being posted in the wrong forum
You can set a query timeout from your client code and roll the
transaction back if you hit the timeout. But that can only be done on
the client. From the server, you can use the SET
QUERY_GOVERNOR_COST_LIMIT server option to limit queries from runnning
that are too costly.
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql

Abort a long running query

Hi
I would like to have a long running query aborted by SQL server, such that,
that query does not become a resource Hog and I can catch that in my client
side code and display a nice error message to the user.
Any way I could do that?
I am sorry if this is being posted in the wrong forum
--
MattISTS wrote:
> Hi
> I would like to have a long running query aborted by SQL server,
> such that, that query does not become a resource Hog and I can catch
> that in my client side code and display a nice error message to the
> user.
> Any way I could do that?
> I am sorry if this is being posted in the wrong forum
You can set a query timeout from your client code and roll the
transaction back if you hit the timeout. But that can only be done on
the client. From the server, you can use the SET
QUERY_GOVERNOR_COST_LIMIT server option to limit queries from runnning
that are too costly.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Abend info in SQL Log

Is there a way to get application/sql query abends to appear in the SQL error
log?
For example, we have an application that executes a SQL query. The
application will abend because the query failed (invalid object, Foreign key
conflict ...). Can we change a setting in SQL to see these errors in the
error log?Hi
No. If you need to see failed queries, you need to run SQL Server Profiler.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"vtcs212" <vtcs212@.discussions.microsoft.com> wrote in message
news:72A431E7-5581-435D-A6C9-3253A30692BB@.microsoft.com...
> Is there a way to get application/sql query abends to appear in the SQL
> error
> log?
> For example, we have an application that executes a SQL query. The
> application will abend because the query failed (invalid object, Foreign
> key
> conflict ...). Can we change a setting in SQL to see these errors in the
> error log?

Abend info in SQL Log

Is there a way to get application/sql query abends to appear in the SQL error
log?
For example, we have an application that executes a SQL query. The
application will abend because the query failed (invalid object, Foreign key
conflict ...). Can we change a setting in SQL to see these errors in the
error log?
Hi
No. If you need to see failed queries, you need to run SQL Server Profiler.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"vtcs212" <vtcs212@.discussions.microsoft.com> wrote in message
news:72A431E7-5581-435D-A6C9-3253A30692BB@.microsoft.com...
> Is there a way to get application/sql query abends to appear in the SQL
> error
> log?
> For example, we have an application that executes a SQL query. The
> application will abend because the query failed (invalid object, Foreign
> key
> conflict ...). Can we change a setting in SQL to see these errors in the
> error log?
sql

Abend info in SQL Log

Is there a way to get application/sql query abends to appear in the SQL erro
r
log?
For example, we have an application that executes a SQL query. The
application will abend because the query failed (invalid object, Foreign key
conflict ...). Can we change a setting in SQL to see these errors in the
error log?Hi
No. If you need to see failed queries, you need to run SQL Server Profiler.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"vtcs212" <vtcs212@.discussions.microsoft.com> wrote in message
news:72A431E7-5581-435D-A6C9-3253A30692BB@.microsoft.com...
> Is there a way to get application/sql query abends to appear in the SQL
> error
> log?
> For example, we have an application that executes a SQL query. The
> application will abend because the query failed (invalid object, Foreign
> key
> conflict ...). Can we change a setting in SQL to see these errors in the
> error log?

AARGGHH Fulltext indexing woes...

We're feverishly in QA mode here getting ready to rollout production to
SQL2K5...
There is this one query which is returning the following error:
Msg 7617, Level 16, State 1, Line 1
Query does not reference the full-text indexed table or indexed view.
I'm really at a lost here. What is wrong with this first query'
I don't actually do select tblStock.* in the production version but it
succeeds/fails consistently either way so to shorten it up....
Here is the DDL used to create the catalog...
------
CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
AUTHORIZATION dbo;
CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc, strImagePath,
strSampleMedia, strCreators) KEY INDEX PK_tblStock;
ALTER FULLTEXT INDEX ON tblStock ENABLE;
------
Here is the query that does NOT work (followed by one that does...)
SELECT DISTINCT tblStock.*
FROM dbo.tblStock LEFT OUTER JOIN
dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK = dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
dbo.tblPriceFile ON dbo.tblStock.lngStockPK = dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
dbo.tblCategories ON
dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
LEFT OUTER JOIN
dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK = dbo.tblToolTypes.lngToolTypePK
WHERE (dbo.tblPriceFile.dtmDateApplied = (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
(dbo.tblStock.blnActiveItem = 1)
and freetext(tblStock.*,'marriage')
----
(the next one works)
select distinct * from tblStock where freetext(tblStock.*,'marriage')
----I've discovered that the joins are what cause this. As soon as I add any
join to the simple query below that is working, it then fails.
HELP!!! Why is this?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> We're feverishly in QA mode here getting ready to rollout production to
> SQL2K5...
> There is this one query which is returning the following error:
> Msg 7617, Level 16, State 1, Line 1
> Query does not reference the full-text indexed table or indexed view.
> I'm really at a lost here. What is wrong with this first query'
> I don't actually do select tblStock.* in the production version but it
> succeeds/fails consistently either way so to shorten it up....
> Here is the DDL used to create the catalog...
> ------
> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
> AUTHORIZATION dbo;
> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
> ALTER FULLTEXT INDEX ON tblStock ENABLE;
> ------
> Here is the query that does NOT work (followed by one that does...)
>
> SELECT DISTINCT tblStock.*
> FROM dbo.tblStock LEFT OUTER JOIN
> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK => dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
> dbo.tblPriceFile ON dbo.tblStock.lngStockPK => dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
> dbo.tblCategories ON
> dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
> LEFT OUTER JOIN
> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK => dbo.tblToolTypes.lngToolTypePK
> WHERE (dbo.tblPriceFile.dtmDateApplied => (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
> (dbo.tblStock.blnActiveItem = 1)
> and freetext(tblStock.*,'marriage')
> ----
> (the next one works)
> select distinct * from tblStock where freetext(tblStock.*,'marriage')
> ----
>
>
>|||Hmmm...we found changing the freetext(tblStock.* to be owner qualified
fixed the problem....
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23XNPPrXlGHA.3776@.TK2MSFTNGP03.phx.gbl...
> I've discovered that the joins are what cause this. As soon as I add any
> join to the simple query below that is working, it then fails.
> HELP!!! Why is this?
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
>> We're feverishly in QA mode here getting ready to rollout production to
>> SQL2K5...
>> There is this one query which is returning the following error:
>> Msg 7617, Level 16, State 1, Line 1
>> Query does not reference the full-text indexed table or indexed view.
>> I'm really at a lost here. What is wrong with this first query'
>> I don't actually do select tblStock.* in the production version but it
>> succeeds/fails consistently either way so to shorten it up....
>> Here is the DDL used to create the catalog...
>> ------
>> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
>> AUTHORIZATION dbo;
>> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
>> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
>> ALTER FULLTEXT INDEX ON tblStock ENABLE;
>> ------
>> Here is the query that does NOT work (followed by one that does...)
>>
>> SELECT DISTINCT tblStock.*
>> FROM dbo.tblStock LEFT OUTER JOIN
>> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =>> dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
>> dbo.tblPriceFile ON dbo.tblStock.lngStockPK =>> dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
>> dbo.tblCategories ON
>> dbo.tblCategorizedProducts.lngCategoryFK =>> dbo.tblCategories.lngCategoryPK LEFT OUTER JOIN
>> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =>> dbo.tblToolTypes.lngToolTypePK
>> WHERE (dbo.tblPriceFile.dtmDateApplied =>> (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
>> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
>> (dbo.tblStock.blnActiveItem = 1)
>> and freetext(tblStock.*,'marriage')
>> ----
>> (the next one works)
>> select distinct * from tblStock where freetext(tblStock.*,'marriage')
>> ----
>>
>>
>sql

AARGGHH Fulltext indexing woes...

I've discovered that the joins are what cause this. As soon as I add any
join to the simple query below that is working, it then fails.
HELP!!! Why is this?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> We're feverishly in QA mode here getting ready to rollout production to
> SQL2K5...
> There is this one query which is returning the following error:
> Msg 7617, Level 16, State 1, Line 1
> Query does not reference the full-text indexed table or indexed view.
> I'm really at a lost here. What is wrong with this first query'
> I don't actually do select tblStock.* in the production version but it
> succeeds/fails consistently either way so to shorten it up....
> Here is the DDL used to create the catalog...
> ----
---
> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
> AUTHORIZATION dbo;
> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
> ALTER FULLTEXT INDEX ON tblStock ENABLE;
> ----
---
> Here is the query that does NOT work (followed by one that does...)
>
> SELECT DISTINCT tblStock.*
> FROM dbo.tblStock LEFT OUTER JOIN
> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =
> dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
> dbo.tblPriceFile ON dbo.tblStock.lngStockPK =
> dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
> dbo.tblCategories ON
> dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
> LEFT OUTER JOIN
> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =
> dbo.tblToolTypes.lngToolTypePK
> WHERE (dbo.tblPriceFile.dtmDateApplied =
> (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
> (dbo.tblStock.blnActiveItem = 1)
> and freetext(tblStock.*,'marriage')
> ----
> (the next one works)
> select distinct * from tblStock where freetext(tblStock.*,'marriage')
> ----
--
>
>
>We're feverishly in QA mode here getting ready to rollout production to
SQL2K5...
There is this one query which is returning the following error:
Msg 7617, Level 16, State 1, Line 1
Query does not reference the full-text indexed table or indexed view.
I'm really at a lost here. What is wrong with this first query'
I don't actually do select tblStock.* in the production version but it
succeeds/fails consistently either way so to shorten it up....
Here is the DDL used to create the catalog...
----
----
CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
AUTHORIZATION dbo;
CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc, strImagePath,
strSampleMedia, strCreators) KEY INDEX PK_tblStock;
ALTER FULLTEXT INDEX ON tblStock ENABLE;
----
----
Here is the query that does NOT work (followed by one that does...)
SELECT DISTINCT tblStock.*
FROM dbo.tblStock LEFT OUTER JOIN
dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =
dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
dbo.tblPriceFile ON dbo.tblStock.lngStockPK =
dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
dbo.tblCategories ON
dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
LEFT OUTER JOIN
dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =
dbo.tblToolTypes.lngToolTypePK
WHERE (dbo.tblPriceFile.dtmDateApplied =
(SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
(dbo.tblStock.blnActiveItem = 1)
and freetext(tblStock.*,'marriage')
----
(the next one works)
select distinct * from tblStock where freetext(tblStock.*,'marriage')
----
--|||I've discovered that the joins are what cause this. As soon as I add any
join to the simple query below that is working, it then fails.
HELP!!! Why is this?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> We're feverishly in QA mode here getting ready to rollout production to
> SQL2K5...
> There is this one query which is returning the following error:
> Msg 7617, Level 16, State 1, Line 1
> Query does not reference the full-text indexed table or indexed view.
> I'm really at a lost here. What is wrong with this first query'
> I don't actually do select tblStock.* in the production version but it
> succeeds/fails consistently either way so to shorten it up....
> Here is the DDL used to create the catalog...
> ----
---
> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
> AUTHORIZATION dbo;
> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
> ALTER FULLTEXT INDEX ON tblStock ENABLE;
> ----
---
> Here is the query that does NOT work (followed by one that does...)
>
> SELECT DISTINCT tblStock.*
> FROM dbo.tblStock LEFT OUTER JOIN
> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =
> dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
> dbo.tblPriceFile ON dbo.tblStock.lngStockPK =
> dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
> dbo.tblCategories ON
> dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
> LEFT OUTER JOIN
> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =
> dbo.tblToolTypes.lngToolTypePK
> WHERE (dbo.tblPriceFile.dtmDateApplied =
> (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
> (dbo.tblStock.blnActiveItem = 1)
> and freetext(tblStock.*,'marriage')
> ----
> (the next one works)
> select distinct * from tblStock where freetext(tblStock.*,'marriage')
> ----
--
>
>
>

A, B and C are the same but at least D or E are different (was "noob question")

hi,

just wanted to ask the most efficient way to query for the following:
say i have a table with the following columns A, B, C, D, E.
i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
thank you!

g11DBhi,

just wanted to ask the most efficient way to query for the following:
say i have a table with the following columns A, B, C, D, E.
i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
thank you!

g11DB

is this wat u wanted ?
Select A,B,C,D,E From Table1 Where Exists (Select 1 from Table1 Where (A=B and B=C) and (( A <>E) or (A <>D)))

Gurus, please comment|||I'm afraid your query won't work :o

Firstly, Exists isn't necessary. Secondly, if the condition is true for any of the rows it will display all the rows (the exists statement is not corrolated with the main query). Minor but SELECT * is recommended within an exists statement as the optimser will then chose the best index for the job (doubt it would make much difference here but I like to use it by habit).

Nearly there - a slight reworking:
Select A,B,C,D,E
From Table1
Where A=B and B=C and (A <> E or A <> D)|||Select A,B,C,D,E From Test
Where A=B
and A=C
and ((A=D or A=E)
or D<>E)

hope this works for u.|||Thanks for the suggessions|||Here's some NZDF code that will do what you've asked for:CREATE TABLE g11DB (
A INT
, B INT
, C INT
, D INT
, E INT
)

INSERT INTO g11DB (
A, B, C, D, E)
SELECT 1, 2, 3, 4, 5 UNION ALL
SELECT 1, 1, 1, 4, 5 UNION ALL
SELECT 1, 1, 1, 1, 5 UNION ALL
SELECT 1, 1, 1, 4, 1 UNION ALL
SELECT 1, 2, 1, 4, 1

SELECT *
FROM g11DB
WHERE 0 = A - B
AND A = C
AND A != D
UNION SELECT *
FROM g11DB
WHERE A = B
AND -A = -C
AND 0 != (A - E)-PatP|||Always the skeptic, Pat.|||Thanks guys but it seems i didn't properly state what I wanted... The code I was looking for is something like this (although i'm still wondering if there's some more efficient version...
SELECT IPA.*
FROM dbo.IRS_PAYMENT IPA INNER JOIN
dbo.IRS_PAYMENT IPB ON IPA.ip_employee_id = IPB.ip_employee_id
AND IPA.ip_paydate = IPB.ip_paydate
AND IPA.ip_pay_indicator = IPB.ip_pay_indicator
AND (NOT (IPA.ip_total_tax = IPB.ip_total_tax)
OR NOT (IPA.ip_net_pay = IPA.ip_net_pay))
ORDER BY IPA.ip_employee_id

what's NSDF stand for btw? :)|||How about:SELECT A.*
FROM dbo.IRS_PAYMENT AS A
INNER JOIN dbo.IRS_PAYMENT AS B
ON (B.ip_employee_id = A.ip_employee_id
AND B.ip_paydate = A.ip_paydate
AND B.ip_pay_indicator = A.ip_pay_indicator)
WHERE B.ip_total_tax != A.ip_total_tax
OR B.ip_net_pay != A.ip_net_pay
ORDER BY A.ip_employee_id, A.ip_paydate, A.ip_pay_indicator-PatP

A WHERE in a Union query

Hi, i have a union query that lists all the years from a date field and add the currentyer if its not already listed:

SELECT DISTINCT Cas Yearlist
FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate())
ORDER BY DatePart(yyyy,[PressreleaseDate])

what i need to do is filter it with something along the lines of:

WHERE Yearlist LIKE myvariable

although i know i cant simply use:

WHERE Yearlist

it would have to be something like:

WHERE DatePart(yyyy,[PressreleaseDate]) UNION datepart(yyyy, getdate()) LIKE myvariable

Does anyone know how to write this correctly?Not tested it, but something like this:

select YearList from
(select distinct Cas YearList
from dbo.ViewPressReleases
where YearList like myvariable) SomeNameYouLike
union
select datepart(yyyy, getdate())
order by YourOrderField|||ok thanks,

so now i have

select YearList from (select distinct DatePart(yyyy,[PressreleaseDate]) as YearList
from dbo.ViewPressReleases where Yearlist like stgetstyear) mixedyearlist
union select datepart(yyyy, getdate()) order by Yearlist

but i get an error that Yearlist in there WHERE statment is invalid.

any ideas?|||you can't use your alias in the where clause, so you have to reuse the DATEPART function again. Also, your DATEPART returns an integer value. If you want to perform a like with a wildcard character (%) on it, you can use the DATENAME function which returns a string.|||i actually want the integer. This was my origianal probelm; that you cant use an alias in a where clause, but i cant simply use the "datepart..." as its part of a union query and i need to filter the resulting list of the union query, if i've explained myself clearly.|||no, it isn't clear, luke

please explain again what the WHERE clause is supposed to find|||Originally posted by r937
no, it isn't clear, luke

please explain again what the WHERE clause is supposed to find

Ok...

i have

SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist
FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate()) ORDER BY DatePart(yyyy,[PressreleaseDate])

i want to then filter the resulting list of years by myvariable

so i need to add

WHERE ??? LIKE myvariable

if this was not a union query i would write something like

WHERE DatePart(yyy,[PressreleaseDate]) LIKE my variable

but as it is a union query i asume i have to write somethin like

WHERE (SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate()) ORDER BY DatePart(yyyy,[PressreleaseDate])) LIKE my viable

(which doesnt work) and i know i cant simply use the alias like:

WHERE Yearlist Like myvaiable

so i need to know how to phrase the SQL corrctly in order to filter the list of years.|||no, don't explain in terms of sql, you already tried that ;)

you cannot use LIKE on integers

try explaining it in english

"i want to select only years which ... ?"|||I Think i have it.

SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist
FROM dbo.ViewPressReleases WHERE DatePart(yyyy,[PressreleaseDate]) LIKE stgetstyear UNION SELECT datepart(yyyy, getdate()) WHERE datepart(yyyy, getdate()) LIKE stgetstyear ORDER BY DatePart(yyyy,[PressreleaseDate])

Thanks.sql

A Very Strange Problem

I have a simple query which ran fine for the last 12 months. All of sudden, it's dragging and couldn't finish in 6 hours. I tried to trace down where the problem resided but it's such a simple query there is nothing to break down. By chance, I commented out all the column names and replace with ' SELECT * ', the query finished in 14 seconds. Once I replace the * with column names, it ran over 20 minutes and I had to cancel it because it doesn't seem to be returning any results. Any help would be appreciated. Thanks.DBCC Checktable|||Thanks. I ran the check and there was no erros. Any more that I can try?|||Have a look at the estmated execution plan of both queries. Most likely there is a difference. You may need to either update statistics on some of the underlying tables, or clear the procedure cache.|||The two execution plans are the same. I cleared the procedure cache, but the problem continues. Any more ideas?|||When was the last time the box was bounced?|||If it's so simple, why don't you post the code?|||Are you certain it is the same execution plan? I can not think how the two execution times (< 1 minute vs > 20 minutes) can be reconciled with the same plan. Did you comment out a group by clause, or a bunch of aggregates? Maybe user defined function calls?|||I found what's wrong but not quite sure how to resolve it at this moment. The database is actually replicated from the publisher. There is another subscriber(server) pulling in the exact same data from the publisher. When I ran the same query(with column names) it only took 20 seconds. Then I check the execution plan on that server, sure enough it's totally different. It's 85% bookmark, which means it full use the clustered index. Yet, on the one that runs forever, it is 58% scan. I check all the tables and indexes. There's no difference. I update the stats yet it is still running forever. Any one has any more suggestions? I did restart the box this morning, by the way.|||So on one server the query takes 20 seconds, while on the other, it takes more than 20 minutes? Are the subscriptions exactly the same? Or are both servers getting different slices of the data?|||Both subscriptions are from the same publication, and they are both full subscriptions without any filters at all. One is the main reporting server, the other is a backup right now. The configurations are almost exactly the same. Only the horsepower is different. One is an 8-way box with 20 GB memory, the other is a 4-way box with 8 GB memory. Both run Win 2003 and SQL 2000 sp3a with AWE on. Yet the same query was interpreted differently.|||Can take help of PROFILER to see the activity while running this query.
ANy differences between 2 servers settings such as db-option, recovery model etc.

A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an author.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaking
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply grateful
Kindest Regards
Simon
Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.
|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.
|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I know
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID may
> be null.
> Now, I need to Select all the books, including those that don't have an author.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books that
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a sneaking
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grateful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:

> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I know
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID may
> be null.
> Now, I need to Select all the books, including those that don't have an author.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books that
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a sneaking
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grateful
> Kindest Regards
> Simon
>
>
|||Hello David,

> Simon Harvey wrote:
> Use an OUTER join:
> SELECT Books.*,
> Authors.FullName
> FROM Books
> LEFT OUTER JOIN Authors
> ON Books.AuthorID = Authors.AuthorID ;
> Books can have more than one author though, so I wouldn't expect to
> see authorid in the Books table. More likely it belongs in a third
> table that joins books to multiple authors (conceptually a
> many-to-many relationship).
You are quite right. It was just a fictional example off the top of my head
though. The real thing is somewhat more complicated so I wanted to create
a really simple example that was conceptually the same. Same thing goes with
the whole Select * thing.
It was just a very quick example to save my poor wee fingers! :-)
Thanks to you all for offering advice. I am very grateful
Kindest Regards
Simon

A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an auth
or.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaki
ng
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply gratefu
l
Kindest Regards
SimonHi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:

> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
>
>|||Hello David,

> Simon Harvey wrote:
>
> Use an OUTER join:
> SELECT Books.*,
> Authors.FullName
> FROM Books
> LEFT OUTER JOIN Authors
> ON Books.AuthorID = Authors.AuthorID ;
> Books can have more than one author though, so I wouldn't expect to
> see authorid in the Books table. More likely it belongs in a third
> table that joins books to multiple authors (conceptually a
> many-to-many relationship).
You are quite right. It was just a fictional example off the top of my head
though. The real thing is somewhat more complicated so I wanted to create
a really simple example that was conceptually the same. Same thing goes with
the whole Select * thing.
It was just a very quick example to save my poor wee fingers! :-)
Thanks to you all for offering advice. I am very grateful
Kindest Regards
Simon

A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an auth
or.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaki
ng
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply gratefu
l
Kindest Regards
SimonHi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:

> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
>
>|||Hello David,

> Simon Harvey wrote:
>
> Use an OUTER join:
> SELECT Books.*,
> Authors.FullName
> FROM Books
> LEFT OUTER JOIN Authors
> ON Books.AuthorID = Authors.AuthorID ;
> Books can have more than one author though, so I wouldn't expect to
> see authorid in the Books table. More likely it belongs in a third
> table that joins books to multiple authors (conceptually a
> many-to-many relationship).
You are quite right. It was just a fictional example off the top of my head
though. The real thing is somewhat more complicated so I wanted to create
a really simple example that was conceptually the same. Same thing goes with
the whole Select * thing.
It was just a very quick example to save my poor wee fingers! :-)
Thanks to you all for offering advice. I am very grateful
Kindest Regards
Simon

A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an author.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaking
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply grateful
Kindest Regards
SimoHi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I know
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID may
> be null.
> Now, I need to Select all the books, including those that don't have an author.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books that
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a sneaking
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grateful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I know
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID may
> be null.
> Now, I need to Select all the books, including those that don't have an author.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books that
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a sneaking
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grateful
> Kindest Regards
> Simon
>
>

a very simple question

hi, there this is a very simple question and Im sorry if it sounds to simple to post.

My problem is that my query returns a long result set in a very long row, the row is so long that I cant see all the result, when I try to expand the page, it still isnt enough to see all the results, and scrolling dosnt help either, I tried select all and copying the reults to a file, but all that does is only copy what can be done on screen.

what I would like to know is (1) how can adjust query analyser so that i can see all the reults

(2) is it possible to get the results returned as several rows, rather than one row, thanks

this is my query:

select * from employees
where employeeid = 1
for xml auto, elements

thanks very much for any help

hi,

you can save that to an XML file

and open it with an XML reader/parser

or simply internet explorer

regards,

joey

|||

Hello,

This is the problem due to allocation of maximum char per column.

do one thing open query analysier -> click on tools -> select option

then select the results tab set 8000 for maxi. char per columns.

:-)

|||

Hello,

This is the problem due to allocation of maximum char per column.

do one thing open query analysier -> click on tools -> select option

then select the results tab set 8000 for maxi. char per columns.

and also you cannot see all the entire value in query analyzer. so RtClk in QueryAnalyzer and select "Results to file..." (Short cut Ctrl+Shift+F). while executing that query Query analyzer prompts you to save the results to some file, so give a file name. after Query execution was completed go that file then you can get all the data in each column(Max 8000 characters per column).

sql

a very simple question

hi, there this is a very simple question and Im sorry if it sounds to simple to post.

My problem is that my query returns a long result set in a very long row, the row is so long that I cant see all the result, when I try to expand the page, it still isnt enough to see all the results, and scrolling dosnt help either, I tried select all and copying the reults to a file, but all that does is only copy what can be done on screen.

what I would like to know is (1) how can adjust query analyser so that i can see all the reults

(2) is it possible to get the results returned as several rows, rather than one row, thanks

this is my query:

select * from employees
where employeeid = 1
for xml auto, elements

thanks very much for any help

hi,

you can save that to an XML file

and open it with an XML reader/parser

or simply internet explorer

regards,

joey

|||

Hello,

This is the problem due to allocation of maximum char per column.

do one thing open query analysier -> click on tools -> select option

then select the results tab set 8000 for maxi. char per columns.

:-)

|||

Hello,

This is the problem due to allocation of maximum char per column.

do one thing open query analysier -> click on tools -> select option

then select the results tab set 8000 for maxi. char per columns.

and also you cannot see all the entire value in query analyzer. so RtClk in QueryAnalyzer and select "Results to file..." (Short cut Ctrl+Shift+F). while executing that query Query analyzer prompts you to save the results to some file, so give a file name. after Query execution was completed go that file then you can get all the data in each column(Max 8000 characters per column).

A very beginer question: error in creating table

I am new in T-SQL. I have just create a empty database and would like to create some table in it. So I type the following in SQL Query Analyzer:

CREATE TABLE mydatabasename.mytablename (

some code here
)
But when execute, it give me an error from the first line "Specified owner name 'mydatabasename' either does not exist or you do not have permission to use it."

when I remove mydatabasename it inserts the table in to the master database, not in my database.

How can I insert the table into my database? Should I use SQL Query Analyzer or use others program? I 've tried to use the command "create table" in enterprise manager but it immediately give another error, some what like "could not use empty column name ... "
Seem to be too simple question, but I am really appreciate your helpWhen you use the SQL Query Analyzer do you choose from the dropdown your newly created Database?|||CREATE TABLE mydatabasename.mytablename

should be

CREATE TABLE mydatabasename.dbo.mytablename

A fully qualified name is
servername.databasename.owner.object

You can't skip one like you're doing. You can omit them from left to right, but can't skip a part.|||Thanks all. I will try both, select the newly database and use databasename.dbo.tablename.

Infact, I use to think about using "databasename.dbo.tablename" but then think it 's ridiculous for a try. What a mistake. It's my because my poor English make me sometime not understand the full syntax.

Thank you.

a TSQL question

i want to write a query that will return last created 200 records + record
with the minimum ID.
is there a way to do that in one single Select statement?
Following statement should return the record that has the minimum ID too.
SELECT TOP 200 ID , CREATEDATE
FROM TBL_RECORDS
ORDER BY CREATEDATE DESC
ID column has IDENTITY property and There could be records with negative IDs
which come through synchronization.
Thanks in advance..Well, what do you mean by single select? Union would probably be the best
solution here, why dont you use that?
MC
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:uJjpdS$KGHA.2300@.TK2MSFTNGP15.phx.gbl...
>i want to write a query that will return last created 200 records + record
>with the minimum ID.
> is there a way to do that in one single Select statement?
> Following statement should return the record that has the minimum ID too.
> SELECT TOP 200 ID , CREATEDATE
> FROM TBL_RECORDS
> ORDER BY CREATEDATE DESC
> ID column has IDENTITY property and There could be records with negative
> IDs which come through synchronization.
> Thanks in advance..
>|||Thanks for the answers ,

> Well, what do you mean by single select? Union would probably be the best
> solution here, why dont you use that?
that is what i mean with single select! i plan to use this query in a RDA
application. I am
not sure that RDA's Pull method with "tracking on" option supports the
UNION operator.
Anyway , i will give a try with UNION method,i hope it works :)

a tricky query

Hello

I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).

Example:

(Cod01, 10, 456), (Cod01, 11, 456), (Cod01, 12, 456) <-- Would pass
(Cod02, 10, 1234), (Cod02, 11, 1234), (Cod02, 12, 1234) <-- Would pass

(Cod03, 10, 345), (Cod03, 11, 1677), (Cod03, 12, 981) <-- Would not pass

How can I accomplish that?

Thanks a lot.select myTable.Product_ID
, myTable.Month
, myTable.Value
from myTable
inner
join (
select Product_ID
from myTable
where Month between 10 and 12
group
by Product_ID
having count(distinct Value)
= count(*)
) as these
on these.Product_ID = myTable.Product_ID
where myTable.Month between 10 and 12|||Declare @.monthStart int
Declare @.monthEnd int

Set @.monthStart = 10
Set @.monthEnd = 12

Select myTable.* from myTable
INNER JOIN
(
Select Product_ID from myTable
where [month] between @.monthStart and @.monthEnd
Group by Product_ID, [Value]
having count(Product_ID) = ((@.monthEnd-@.monthStart)+1)
) this ON this.Product_ID= myTable.Product_ID

-------------------