problem with query where column name is the same as a keyword

hi

I am having trouble with the following query within my store procedure.
as you can see, i am making an union of 2 separate queries.

in the 2nd part of the union, i encounter a column in the database where the column name is the same as the keyword "desc"

is there a way which i can get around this, or is there any other way that i can sepecify the column? (excluding the possibility of using *)

CREATE PROCEDURE topcat.getTransHistory
(
@.contact_id numeric(9)
)
AS
BEGIN
DECLARE @.phone_no varchar(255)

set @.phone_no = (select top 1 phone_num from topcat.class_contact where _id = @.contact_id)

select cast(trans_new.trans_date as varchar(50)) date,
'' code,
cast(payment.date_paid as varchar(50)) datepaid,
'' "desc",
case payment.payment_type
when 'cheque' then trans_new.item_total
else ''
end pledged,
'' mail,
case payment.payment_type
when 'cheque' then ''
else trans_new.item_total
end received,
'' receipt
from topcat.class_transaction trans_new left outer join topcat.class_payment payment on trans_new._id = payment.transaction_id
where trans_new.contact_id = @.contact_id
union
select cast(trans_old.date as varchar(50)) "date",
trans_old.code,
cast(trans_old.datepaid as varchar(50)) "datepaid",
trans_old.desc,
cast(trans_old.pledged as varchar(128)),
trans_old.mail,
cast(trans_old.received as varchar(128)),
trans_old.receipt
from topcat.MMTRANS$ trans_old
where phone = @.phone_no

END
GO

Cheers
James :)wrap your column name that is a keyword in square brackets eg [desc]|||thank you
thank you
thank you

you are a life saver, i was goin to change the column name of the table if i couldn't find an alternative way of doing it.

:)|||no worries,... try to avoid keywords in the future would be my advise though... ;)|||i was on a contract once and there converted database had tables and columns all over the place named with keywords.

i will never forget they had a column called select in a table called order.
nightmare to say the least.

Problem with Parallel Query Execution

I have a SQL 7 db with a union query (view), and I'm getting the error, "The
query processor could not start the necessary thread resources for parallel
query execution." This union query has been in place for about two years now
with no problems until just now, though I haven't changed anything. Also, I
have a local copy of the database on my machine, and the query runs fine.
As noted, I haven't changed anything in the query, nor in the SQL settings.
There is a network administrator, so it's possible that he may have changed
a setting, but I don't know what. The query is reproduced below. Any ideas
as to what's going on would be appreciated.
Neil
Main query:
SELECT Tmp.INVCUST, Tmp.SDNBR, Tmp.SDBOOK, Tmp.SDIVCLN,
Tmp.SDPAID, Tmp.SDPRICE, Tmp.SDCOPIES, Tmp.Location,
INVTRY.AUTHILL1, Tmp.INVDATE, INVTRY.SaleSrc,
INVTRY.HoldInit
FROM (SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'P' AS Location
FROM vwInvoiceDet
UNION ALL
SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'N' AS Location
FROM vwInvoiceDetN
UNION ALL
SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'M' AS Location
FROM vwInvoiceDetM) Tmp INNER JOIN
dbo.INVTRY ON Tmp.SDBOOK = dbo.INVTRY.[Index]
vwInvoiceDet:
SELECT tabInvoice.INVDATE, tabInvoice.INVCUST,
SALEDET.SDNBR, SALEDET.SDBOOK, SALEDET.SDINVNUM,
SALEDET.SDPRICE, SALEDET.SDPAID, SALEDET.SDCOPIES,
SALEDET.SDIVCLN, tabInvoice.INVNBR, SALEDET.SDID
FROM dbo.tabInvoice INNER JOIN
dbo.SALEDET ON
dbo.tabInvoice.INVNBR = dbo.SALEDET.SDNBR
(vwInvoiceDetN and vwInvoiceDetM are similar to vwInvoiceDet.)SQL makes a parallel query plan at optimization time. When you tried to run
the query, maybe not all of the processors were available OR there were not
enough threads available ... Perhaps setting MAXDOP to 2 or 3 might help..
This can be done on Enterprise manager, right click your server and go to
the Properties item.. (MAX Degree of Parallelism.)
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Neil Ginsberg" <nrg@.nrgconsult.com> wrote in message
news:0DXNd.2806$UX3.1660@.newsread3.news.pas.earthlink.net...
> I have a SQL 7 db with a union query (view), and I'm getting the error,
"The
> query processor could not start the necessary thread resources for
parallel
> query execution." This union query has been in place for about two years
now
> with no problems until just now, though I haven't changed anything. Also,
I
> have a local copy of the database on my machine, and the query runs fine.
> As noted, I haven't changed anything in the query, nor in the SQL
settings.
> There is a network administrator, so it's possible that he may have
changed
> a setting, but I don't know what. The query is reproduced below. Any ideas
> as to what's going on would be appreciated.
> Neil
> Main query:
> SELECT Tmp.INVCUST, Tmp.SDNBR, Tmp.SDBOOK, Tmp.SDIVCLN,
> Tmp.SDPAID, Tmp.SDPRICE, Tmp.SDCOPIES, Tmp.Location,
> INVTRY.AUTHILL1, Tmp.INVDATE, INVTRY.SaleSrc,
> INVTRY.HoldInit
> FROM (SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
> SDPAID, SDPRICE, SDCOPIES, 'P' AS Location
> FROM vwInvoiceDet
> UNION ALL
> SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
> SDPAID, SDPRICE, SDCOPIES, 'N' AS Location
> FROM vwInvoiceDetN
> UNION ALL
> SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
> SDPAID, SDPRICE, SDCOPIES, 'M' AS Location
> FROM vwInvoiceDetM) Tmp INNER JOIN
> dbo.INVTRY ON Tmp.SDBOOK = dbo.INVTRY.[Index]
> vwInvoiceDet:
> SELECT tabInvoice.INVDATE, tabInvoice.INVCUST,
> SALEDET.SDNBR, SALEDET.SDBOOK, SALEDET.SDINVNUM,
> SALEDET.SDPRICE, SALEDET.SDPAID, SALEDET.SDCOPIES,
> SALEDET.SDIVCLN, tabInvoice.INVNBR, SALEDET.SDID
> FROM dbo.tabInvoice INNER JOIN
> dbo.SALEDET ON
> dbo.tabInvoice.INVNBR = dbo.SALEDET.SDNBR
> (vwInvoiceDetN and vwInvoiceDetM are similar to vwInvoiceDet.)
>|||I tried it just now, after hours, with no one on the system, and the results
were the same.
In any case, I think I resolved it. I stopped the SQL Server and then
restarted it, and the problem cleared up. So I don't know what was going on,
but stopping and restarting definitely cleared up whatever it was.
Thanks,
Neil
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cu9h7q$lkv$1@.news01.intel.com...
> SQL makes a parallel query plan at optimization time. When you tried to
> run
> the query, maybe not all of the processors were available OR there were
> not
> enough threads available ... Perhaps setting MAXDOP to 2 or 3 might help..
> This can be done on Enterprise manager, right click your server and go to
> the Properties item.. (MAX Degree of Parallelism.)
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "Neil Ginsberg" <nrg@.nrgconsult.com> wrote in message
> news:0DXNd.2806$UX3.1660@.newsread3.news.pas.earthlink.net...
> "The
> parallel
> now
> I
> settings.
> changed
>

Problem with Parallel Query Execution

I have a SQL 7 db with a union query (view), and I'm getting the error, "The
query processor could not start the necessary thread resources for parallel
query execution." This union query has been in place for about two years now
with no problems until just now, though I haven't changed anything. Also, I
have a local copy of the database on my machine, and the query runs fine.

As noted, I haven't changed anything in the query, nor in the SQL settings.
There is a network administrator, so it's possible that he may have changed
a setting, but I don't know what. The query is reproduced below. Any ideas
as to what's going on would be appreciated.

Neil

Main query:
SELECT Tmp.INVCUST, Tmp.SDNBR, Tmp.SDBOOK, Tmp.SDIVCLN,
Tmp.SDPAID, Tmp.SDPRICE, Tmp.SDCOPIES, Tmp.Location,
INVTRY.AUTHILL1, Tmp.INVDATE, INVTRY.SaleSrc,
INVTRY.HoldInit
FROM (SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'P' AS Location
FROM vwInvoiceDet
UNION ALL
SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'N' AS Location
FROM vwInvoiceDetN
UNION ALL
SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
SDPAID, SDPRICE, SDCOPIES, 'M' AS Location
FROM vwInvoiceDetM) Tmp INNER JOIN
dbo.INVTRY ON Tmp.SDBOOK = dbo.INVTRY.[Index]

vwInvoiceDet:
SELECT tabInvoice.INVDATE, tabInvoice.INVCUST,
SALEDET.SDNBR, SALEDET.SDBOOK, SALEDET.SDINVNUM,
SALEDET.SDPRICE, SALEDET.SDPAID, SALEDET.SDCOPIES,
SALEDET.SDIVCLN, tabInvoice.INVNBR, SALEDET.SDID
FROM dbo.tabInvoice INNER JOIN
dbo.SALEDET ON
dbo.tabInvoice.INVNBR = dbo.SALEDET.SDNBR

(vwInvoiceDetN and vwInvoiceDetM are similar to vwInvoiceDet.)SQL makes a parallel query plan at optimization time. When you tried to run
the query, maybe not all of the processors were available OR there were not
enough threads available ... Perhaps setting MAXDOP to 2 or 3 might help..
This can be done on Enterprise manager, right click your server and go to
the Properties item.. (MAX Degree of Parallelism.)

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

"Neil Ginsberg" <nrg@.nrgconsult.com> wrote in message
news:0DXNd.2806$UX3.1660@.newsread3.news.pas.earthl ink.net...
> I have a SQL 7 db with a union query (view), and I'm getting the error,
"The
> query processor could not start the necessary thread resources for
parallel
> query execution." This union query has been in place for about two years
now
> with no problems until just now, though I haven't changed anything. Also,
I
> have a local copy of the database on my machine, and the query runs fine.
> As noted, I haven't changed anything in the query, nor in the SQL
settings.
> There is a network administrator, so it's possible that he may have
changed
> a setting, but I don't know what. The query is reproduced below. Any ideas
> as to what's going on would be appreciated.
> Neil
> Main query:
> SELECT Tmp.INVCUST, Tmp.SDNBR, Tmp.SDBOOK, Tmp.SDIVCLN,
> Tmp.SDPAID, Tmp.SDPRICE, Tmp.SDCOPIES, Tmp.Location,
> INVTRY.AUTHILL1, Tmp.INVDATE, INVTRY.SaleSrc,
> INVTRY.HoldInit
> FROM (SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
> SDPAID, SDPRICE, SDCOPIES, 'P' AS Location
> FROM vwInvoiceDet
> UNION ALL
> SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
> SDPAID, SDPRICE, SDCOPIES, 'N' AS Location
> FROM vwInvoiceDetN
> UNION ALL
> SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
> SDPAID, SDPRICE, SDCOPIES, 'M' AS Location
> FROM vwInvoiceDetM) Tmp INNER JOIN
> dbo.INVTRY ON Tmp.SDBOOK = dbo.INVTRY.[Index]
> vwInvoiceDet:
> SELECT tabInvoice.INVDATE, tabInvoice.INVCUST,
> SALEDET.SDNBR, SALEDET.SDBOOK, SALEDET.SDINVNUM,
> SALEDET.SDPRICE, SALEDET.SDPAID, SALEDET.SDCOPIES,
> SALEDET.SDIVCLN, tabInvoice.INVNBR, SALEDET.SDID
> FROM dbo.tabInvoice INNER JOIN
> dbo.SALEDET ON
> dbo.tabInvoice.INVNBR = dbo.SALEDET.SDNBR
> (vwInvoiceDetN and vwInvoiceDetM are similar to vwInvoiceDet.)|||I tried it just now, after hours, with no one on the system, and the results
were the same.

In any case, I think I resolved it. I stopped the SQL Server and then
restarted it, and the problem cleared up. So I don't know what was going on,
but stopping and restarting definitely cleared up whatever it was.

Thanks,

Neil

"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cu9h7q$lkv$1@.news01.intel.com...
> SQL makes a parallel query plan at optimization time. When you tried to
> run
> the query, maybe not all of the processors were available OR there were
> not
> enough threads available ... Perhaps setting MAXDOP to 2 or 3 might help..
> This can be done on Enterprise manager, right click your server and go to
> the Properties item.. (MAX Degree of Parallelism.)
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "Neil Ginsberg" <nrg@.nrgconsult.com> wrote in message
> news:0DXNd.2806$UX3.1660@.newsread3.news.pas.earthl ink.net...
>> I have a SQL 7 db with a union query (view), and I'm getting the error,
> "The
>> query processor could not start the necessary thread resources for
> parallel
>> query execution." This union query has been in place for about two years
> now
>> with no problems until just now, though I haven't changed anything. Also,
> I
>> have a local copy of the database on my machine, and the query runs fine.
>>
>> As noted, I haven't changed anything in the query, nor in the SQL
> settings.
>> There is a network administrator, so it's possible that he may have
> changed
>> a setting, but I don't know what. The query is reproduced below. Any
>> ideas
>> as to what's going on would be appreciated.
>>
>> Neil
>>
>> Main query:
>> SELECT Tmp.INVCUST, Tmp.SDNBR, Tmp.SDBOOK, Tmp.SDIVCLN,
>> Tmp.SDPAID, Tmp.SDPRICE, Tmp.SDCOPIES, Tmp.Location,
>> INVTRY.AUTHILL1, Tmp.INVDATE, INVTRY.SaleSrc,
>> INVTRY.HoldInit
>> FROM (SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
>> SDPAID, SDPRICE, SDCOPIES, 'P' AS Location
>> FROM vwInvoiceDet
>> UNION ALL
>> SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
>> SDPAID, SDPRICE, SDCOPIES, 'N' AS Location
>> FROM vwInvoiceDetN
>> UNION ALL
>> SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,
>> SDPAID, SDPRICE, SDCOPIES, 'M' AS Location
>> FROM vwInvoiceDetM) Tmp INNER JOIN
>> dbo.INVTRY ON Tmp.SDBOOK = dbo.INVTRY.[Index]
>>
>> vwInvoiceDet:
>> SELECT tabInvoice.INVDATE, tabInvoice.INVCUST,
>> SALEDET.SDNBR, SALEDET.SDBOOK, SALEDET.SDINVNUM,
>> SALEDET.SDPRICE, SALEDET.SDPAID, SALEDET.SDCOPIES,
>> SALEDET.SDIVCLN, tabInvoice.INVNBR, SALEDET.SDID
>> FROM dbo.tabInvoice INNER JOIN
>> dbo.SALEDET ON
>> dbo.tabInvoice.INVNBR = dbo.SALEDET.SDNBR
>>
>> (vwInvoiceDetN and vwInvoiceDetM are similar to vwInvoiceDet.)
>>
>>