add sql command - parsing error

[SherryBuffalo]

this is my first time using the crystal report 9. I am testing out the sql command function. I have working with ms sql for a while. simple command:

select account.accountid from account where stagecode > 0

must be some kind of syntax error. Could anyone help??
sherry

 

[synapsevampire]

Test the sql first in the Query Analyzer, if it works there, it should work in the Add Command.

I would guess that you have something wrong in the sql and that it's not Crystal related.

-k

 

[dzman1]

1) Make sure there's no Record selection or Group Selection formula.

2) Put check by "Database server is case-insensitive" db advanced option or use ALL CAPS. Set most other DB performance type options OFF.

3) Don't use double quotes (or straight brackets) unless you absolutely have to for an irregularly named object. If you use double quotes, you have to make sure the SQL Server option QUOTED_IDENTIFIER is SET ON (it gets set on and off automatically all the time without notice).


Be more consistent with aliasing than T-SQL requires.

Without alias/tablename (if no common column names in joined tables):

select accountid
from account
where stagecode > 0

With table alias (explicitly declared and used consistently)

select a.accountid
from account a
where a.stagecode > 0

If this is a subreport linked to the main report by a parameter that has to go into the SQL command, the syntax for the parameter varies.

Appearance in main report:
{?Pm-ALIAS.COLUMNNAME}

In SQL Command editor, manually create parameter list entry as:
Pm-ALIAS.COLUMNNAME

Within the SQL Command itself, manually enter parameter as:
'{?Pm-ALIAS.COLUMNNAME}'

Ex:
select a.accountid
from account a
where a.stagecode > '{?Pm-ALIAS.COLUMNNAME}'


If it still doesn't work after all this, you need to see exactly what SQL text is getting sent to SQL Server. Either log it or trace it on the Profiler tool.

Good luck.

 

[SherryBuffalo]

Thank very much for the replies. I am new to Crystal Report and to application (MS CRM). Everything works when I created a new sql connection. Thank you again.