Problem with SQL generated from MS Access

SELECT DISTINCT product.product_id AS Expr1
FROM [SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like "*coaster*") AND
((product.display)="y"))]. AS [Alias]

This is the error I get:

The identifier that starts with 'SELECT product.product_id...' is too
long. Maximum lenght is 128

Incorrect syntax near the keyword 'AS'

Any ideas?

ThanksReplace [...]. with (...)
Replace " with '

SELECT DISTINCT product.product_id AS Expr1
FROM (SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like '*coaster*') AND
((product.display)='y'))) AS [Alias]

--
David Portas
SQL Server MVP
--|||That worked great...

Thanks for the help

Problem with SQL from Access

SELECT DISTINCT product.product_id AS Expr1
FROM [SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like "*coaster*") AND
((product.display)="y"))]. AS [Alias]
This is the error I get:
The identifier that starts with 'SELECT product.product_id...' is too
long. Maximum lenght is 128
Incorrect syntax near the keyword 'AS'
Any ideas?
Thanks
FROM [SELECT product.product...
...
((product.display)="y"))]. AS ...
Should be:
FROM (SELECT product.product...
...
((product.display)="y"))). AS ...
Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
for how you enclose a derived table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Allan" <proflicker@.hotmail.com> wrote in message
news:7b5b0602.0409271247.5a340b82@.posting.google.c om...
> SELECT DISTINCT product.product_id AS Expr1
> FROM [SELECT product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
> ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
> coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
> dbo_ssc_product.product_id = product.product_id
> GROUP BY product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> HAVING (((product.item_name) Like "*coaster*") AND
> ((product.display)="y"))]. AS [Alias]
> This is the error I get:
> The identifier that starts with 'SELECT product.product_id...' is too
> long. Maximum lenght is 128
> Incorrect syntax near the keyword 'AS'
>
> Any ideas?
> Thanks
|||Hi Tibor,
thanks for your help, you're right about the parenthesis and I also
found that you need to also remove the dot.
the code below ended up working:
FROM (SELECT product.product...
...
((product.display)="y"))) AS ...
without the dot.
Thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<OCMZv4SpEHA.536@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> FROM [SELECT product.product...
> ...
> ((product.display)="y"))]. AS ...
> Should be:
> FROM (SELECT product.product...
> ...
> ((product.display)="y"))). AS ...
> Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
> for how you enclose a derived table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Allan" <proflicker@.hotmail.com> wrote in message
> news:7b5b0602.0409271247.5a340b82@.posting.google.c om...
sql

Problem with SQL from Access

SELECT DISTINCT product.product_id AS Expr1
FROM [SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like "*coaster*") AND
((product.display)="y"))]. AS [Alias]
This is the error I get:
The identifier that starts with 'SELECT product.product_id...' is too
long. Maximum lenght is 128
Incorrect syntax near the keyword 'AS'
Any ideas?
ThanksFROM [SELECT product.product...
...
((product.display)="y"))]. AS ...
Should be:
FROM (SELECT product.product...
...
((product.display)="y"))). AS ...
Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
for how you enclose a derived table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Allan" <proflicker@.hotmail.com> wrote in message
news:7b5b0602.0409271247.5a340b82@.posting.google.com...
> SELECT DISTINCT product.product_id AS Expr1
> FROM [SELECT product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
> ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
> coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
> dbo_ssc_product.product_id = product.product_id
> GROUP BY product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> HAVING (((product.item_name) Like "*coaster*") AND
> ((product.display)="y"))]. AS [Alias]
> This is the error I get:
> The identifier that starts with 'SELECT product.product_id...' is too
> long. Maximum lenght is 128
> Incorrect syntax near the keyword 'AS'
>
> Any ideas?
> Thanks|||Hi Tibor,
thanks for your help, you're right about the parenthesis and I also
found that you need to also remove the dot.
the code below ended up working:
FROM (SELECT product.product...
...
((product.display)="y"))) AS ...
without the dot.
Thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<OCMZv4SpEHA.536@.TK2MSFTNGP11.phx.gbl>...
> FROM [SELECT product.product...
> ...
> ((product.display)="y"))]. AS ...
> Should be:
> FROM (SELECT product.product...
> ...
> ((product.display)="y"))). AS ...
> Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
> for how you enclose a derived table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Allan" <proflicker@.hotmail.com> wrote in message
> news:7b5b0602.0409271247.5a340b82@.posting.google.com...
> > SELECT DISTINCT product.product_id AS Expr1
> > FROM [SELECT product.product_id, product.item_name, product.display,
> > product.selling_price, product.smallpicture, product.main_size,
> > product.main_dinnerware, dbo_coupon_special.coupon_id,
> > dbo_coupon_special.special_price, coupon.effective_date,
> > coupon.expiration_date, dbo_ssc_product.mc_id
> > FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
> > ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
> > coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
> > dbo_ssc_product.product_id = product.product_id
> > GROUP BY product.product_id, product.item_name, product.display,
> > product.selling_price, product.smallpicture, product.main_size,
> > product.main_dinnerware, dbo_coupon_special.coupon_id,
> > dbo_coupon_special.special_price, coupon.effective_date,
> > coupon.expiration_date, dbo_ssc_product.mc_id
> > HAVING (((product.item_name) Like "*coaster*") AND
> > ((product.display)="y"))]. AS [Alias]
> >
> > This is the error I get:
> >
> > The identifier that starts with 'SELECT product.product_id...' is too
> > long. Maximum lenght is 128
> >
> > Incorrect syntax near the keyword 'AS'
> >
> >
> >
> > Any ideas?
> >
> > Thanks

Problem with sp_executesql

I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below:
execute sp_executesql N'SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE @.au_lname
',
N'@.au_lname nVarChar',
@.au_lname = N'%Cas%'

but It return all rows regardless of changing condition to any value.

But if i don't use sp_executesql like below:

SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE N'%Cas%'

It's correct!

Can anyone tell me why?

ThanksChange your code as follows:

N'@.au_lname nVarChar', -->>> N'@.au_lname nVarChar(5)',|||Thank you very much for feedback!

Problem with query

I have the following sql:
SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm, b.LastNm,
e.Processcatalog,
b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr, i.MaidenNm,
k.caseid, k.comments
FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid
INNER JOIN Processcatalog AS E ON A.process = E.processcatalogid
INNER JOIN Atts AS J ON A.id = J.caseid
INNER JOIN Users AS I ON J.userid = I.Userid
LEFT JOIN Users AS F ON A.empid = F.userid
LEFT JOIN CaseComments AS K ON a.id = k.Caseid
WHERE A.firmid = 2
ORDER BY i.userid
As soon as I added k.comments
I get this error:
Server: Msg 8163, Level 16, State 3, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.
Any ideas ?
AleksTry making the query without the Text field a subquery, aliased as, say Z,
and join it (Z) to the CaseComments table
Select Z.*, K.CaseID, K.comments
From (SELECT distinct a.id,a.caseid, b.userid AlienUserId,
b.FirstNm, b.LastNm, e.Processcatalog,
b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm AS EmployerName,
a.approvaldate, i.userid,i.MailStr, i.MaidenNm
From Cases A
Join Users B On A.alienid = B.userid
Join Processcatalog E On A.process = E.processcatalogid
Join Atts J On A.id = J.caseid
Join Users I On J.userid = I.Userid
Left Join Users F On A.empid = F.userid) As Z
Join CaseComments K On k.Caseid = Z.id
"Aleks" wrote:

> I have the following sql:
> SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm, b.LastN
m,
> e.Processcatalog,
> b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
> f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr, i.MaidenNm
,
> k.caseid, k.comments
> FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid
> INNER JOIN Processcatalog AS E ON A.process = E.processcatalogid
> INNER JOIN Atts AS J ON A.id = J.caseid
> INNER JOIN Users AS I ON J.userid = I.Userid
> LEFT JOIN Users AS F ON A.empid = F.userid
> LEFT JOIN CaseComments AS K ON a.id = k.Caseid
> WHERE A.firmid = 2
> ORDER BY i.userid
> As soon as I added k.comments
> I get this error:
> Server: Msg 8163, Level 16, State 3, Line 1
> The text, ntext, or image data type cannot be selected as DISTINCT.
>
> Any ideas ?
> Aleks
>
>|||uh uh, forgot your Where Clause, and Order By...
Select Z.*, K.CaseID, K.comments
From (SELECT distinct a.id,a.caseid, b.userid AlienUserId,
b.FirstNm, b.LastNm, e.Processcatalog,
b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm AS EmployerName,
a.approvaldate, i.userid,i.MailStr, i.MaidenNm
From Cases A
Join Users B On A.alienid = B.userid
Join Processcatalog E On A.process = E.processcatalogid
Join Atts J On A.id = J.caseid
Join Users I On J.userid = I.Userid
Left Join Users F On A.empid = F.userid
Where A.firmid = 2) As Z
Join CaseComments K On K.Caseid = Z.id
Order By Z.ID
"Aleks" wrote:

> I have the following sql:
> SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm, b.LastN
m,
> e.Processcatalog,
> b.I94Date, a.ExpDate,a.maincase,a.caserelationship,
> f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr, i.MaidenNm
,
> k.caseid, k.comments
> FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid
> INNER JOIN Processcatalog AS E ON A.process = E.processcatalogid
> INNER JOIN Atts AS J ON A.id = J.caseid
> INNER JOIN Users AS I ON J.userid = I.Userid
> LEFT JOIN Users AS F ON A.empid = F.userid
> LEFT JOIN CaseComments AS K ON a.id = k.Caseid
> WHERE A.firmid = 2
> ORDER BY i.userid
> As soon as I added k.comments
> I get this error:
> Server: Msg 8163, Level 16, State 3, Line 1
> The text, ntext, or image data type cannot be selected as DISTINCT.
>
> Any ideas ?
> Aleks
>
>