ODBC error when runnig report on CR 8.5

[rahulroy08]

Hi all,

I'm using cyrstal Reprots 8.5 and SQL Server 2005. When I run the report I get an error

ODBC error:[Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect syntax near '{'

I'm not sure what it is. Can you please help me.


Thanks,


Rahul.

 

[dgillz]

Please post the formula that it is erroring out on, as well as indicating where the cursor is.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[rahulroy08]

It gives me that error when I run the report. And how do I figure out where exactly I'm getting such an error.

 

[Turkbear]

Hi,
Try looking at the SQL generated by the report:
Use the 'Show Sql' option under the Database menu.


It appears that some character is preceeding a field name,





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rahulroy08]

Hi

SELECT
ALLOCATE."DATE_ALLOC", ALLOCATE."MINACCRATE",
ROOMNITE."BUS_ID", ROOMNITE."ROOMDATE", ROOMNITE."ROOMDEF_ID", ROOMNITE."SYSROOM_ID", ROOMNITE."SING_RATE", ROOMNITE."DOUB_RATE", ROOMNITE."TRIP_RATE", ROOMNITE."QUAD_RATE", ROOMNITE."SINGLE_QTY", ROOMNITE."DOUBLE_QTY", ROOMNITE."TRIPLE_QTY", ROOMNITE."QUAD_QTY",
PERFBUCKET."BNUM_1", PERFBUCKET."BNUM_2",
BUSINESS."BUS_ID", BUSINESS."BUS_GID", BUSINESS."BUSSTSYNAB", BUSINESS."ABBREV", BUSINESS."AVG_RATE", BUSINESS."CREATE_DAT", BUSINESS."LOST_SYSID", BUSINESS."PICKUP_YN",
BUSSTSYN."BUSSTSYNTX",
PERSON."PER_INIT",
MRKETSEG."MARKET_ABV"
FROM
ALLOCATE --(*vendor(Microsoft),product(ODBC) oj LEFT OUTER JOIN UBUCKET ON ALLOCATE.DATE_ALLOC=UBUCKET.BDATE_1 AND UBUCKET.UKEY_USER=0 AND UBUCKET.BNUM_1=1 LEFT OUTER JOIN DATECOLL ON ALLOCATE.DATE_ALLOC = DATECOLL.START_DATE LEFT OUTER JOIN ROOMNITE ON ALLOCATE.DATE_ALLOC = ROOMNITE.ROOMDATE LEFT OUTER JOIN BUSINESS ON ROOMNITE.BUS_ID = BUSINESS.BUS_ID AND ROOMNITE.BUS_GID = BUSINESS.BUS_GID LEFT OUTER JOIN BUSSTSYN BUSSTSYN ON BUSINESS.BUSSTSYNID = BUSSTSYN.BUSSTSYNID AND BUSINESS.BUSSTSYGID = BUSSTSYN.BUSSTSYGID LEFT OUTER JOIN BUSTYSYN BUSTYSYN ON BUSINESS.TY_SYN_ID = BUSTYSYN.TY_SYN_ID AND BUSINESS.TYSYN_GID = BUSTYSYN.TYSYN_GID LEFT OUTER JOIN PERSON ON BUSINESS.BOOKEDBY = PERSON.PERSON_ID AND BUSINESS.BOOKEDBYG = PERSON.PERSON_GID LEFT OUTER JOIN ORGANIZ ON BUSINESS.CLIENT = ORGANIZ.ORG_ID AND BUSINESS.CLIENTG = ORGANIZ.ORG_GID LEFT OUTER JOIN MRKETSEG ON ORGANIZ.MKT_SEG_ID = MRKETSEG.MKT_SEG_ID AND ORGANIZ.MKTSEG_GID = MRKETSEG.MKTSEG_GID*)--
WHERE
allocate.date_alloc >= {D '1998-01-01'} and allocate.date_alloc < {D '1998-02-01'} and BUSTYSYN.SYSBUSTYID <> 1


This is my piece of code but I'm not sure where the error occurs....


Rahul

 

[Turkbear]

Hi,
Given that error , it is apparently somewhere near this:

 allocate.date_alloc >= {D '1998-01-01'} and allocate.date_alloc < {D '1998-02-01'} and BUSTYSYN.SYSBUSTYID <> 1

Can you run the SQL directly?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rahulroy08]

yes i tried that as well. And when i did that it gives me an error at a different place.

ALLOCATE --(*vendor(Microsoft),product(ODBC) oj LEFT OUTER JOIN UBUCKET ON ALLOCATE.DATE_ALLOC=UBUCKET.BDATE_1 AND UBUCKET.UKEY_USER=0 AND UBUCKET.BNUM_1=1 LEFT OUTER JOIN DATECOLL ON ALLOCATE.DATE_ALLOC = DATECOLL.START_DATE LEFT OUTER JOIN ROOMNITE ON ALLOCATE.DATE_ALLOC = ROOMNITE.ROOMDATE LEFT OUTER JOIN BUSINESS ON ROOMNITE.BUS_ID = BUSINESS.BUS_ID AND ROOMNITE.BUS_GID = BUSINESS.BUS_GID LEFT OUTER JOIN BUSSTSYN BUSSTSYN ON BUSINESS.BUSSTSYNID = BUSSTSYN.BUSSTSYNID AND BUSINESS.BUSSTSYGID = BUSSTSYN.BUSSTSYGID LEFT OUTER JOIN BUSTYSYN BUSTYSYN ON BUSINESS.TY_SYN_ID = BUSTYSYN.TY_SYN_ID AND BUSINESS.TYSYN_GID = BUSTYSYN.TYSYN_GID LEFT OUTER JOIN  PERSON ON BUSINESS.BOOKEDBY = PERSON.PERSON_ID AND BUSINESS.BOOKEDBYG = PERSON.PERSON_GID LEFT OUTER JOIN ORGANIZ ON BUSINESS.CLIENT = ORGANIZ.ORG_ID AND BUSINESS.CLIENTG = ORGANIZ.ORG_GID LEFT OUTER JOIN MRKETSEG ON ORGANIZ.MKT_SEG_ID = MRKETSEG.MKT_SEG_ID AND ORGANIZ.MKTSEG_GID = MRKETSEG.MKTSEG_GID*)--

it gives me the same error saying

"Incorrect syntax near '--(*'."

Please Help!

Thanks,

Rahul.

 

[lbass]

Where are you getting this code? Are you copying and pasting this from database->show SQL query? I'm surprised you could get that kind of result since you cannot manually change anything directly in the SQL query except the where clause in 8.5.

-LB

 

[rahulroy08]

Yes. I'm getting it from the Show SQL query itself.

Also if you can look at the complete code above, the initial error occurs in the where clause of the query.

But when I run the query in the SQL Server then it gives me an error at a different location. I dont understand what it is.


Please help.


Your help is appreciated.



Thanks


Rahul.

 

[lbass]

Please copy and paste your record selection formula from report->selection formula->record into this thread.

It might also help if you explain how you have linked your tables in database->visual linking expert. I've never seen a from clause that looks quite like yours, although that may be my inexperience.

-LB

 

[rahulroy08]

It doesnt have any formula in the record selection (This report has been developed by someone else and I'm trying to customise it). I also have two other reports which give me the same error.

And the tables are linked by key (few tables with equi join and the others by left outer join).

Also this report has 3 sub-reports (I hope this information helps).


Your help is appreciated.



Rahul.

 

[Turkbear]

Hi,
This part:

ALLOCATE --(*vendor(Microsoft),product(ODBC)

Looks bvery strange - I have never seen that syntax when specifying a table or a datasource...It is clearly where the error is being triggered..

Contact the developer and ask what it is supposed to do..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rahulroy08]

Hi

As of now I dont think I can get hold of him. But have to figure out some way to get this report to work.

So, I think I will be working on this. Any suggestions and advices are welcome.

Your help is always appreciated.


Thanks,


Rahul.

 

[Turkbear]

Hi,
as a test, remove the
--(*vendor(Microsoft),product(ODBC)

and

*)--

parts and try running it directly....






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rahulroy08]

hi

I have eliminated that and customised the code based on the SQL queries for some other reports which are successful.

SELECT
    ALLOCATE."DATE_ALLOC", ALLOCATE."MINACCRATE",
    ROOMNITE."BUS_ID", ROOMNITE."ROOMDATE", ROOMNITE."ROOMDEF_ID", ROOMNITE."SYSROOM_ID", ROOMNITE."SING_RATE", ROOMNITE."DOUB_RATE", ROOMNITE."TRIP_RATE", ROOMNITE."QUAD_RATE", ROOMNITE."SINGLE_QTY", ROOMNITE."DOUBLE_QTY", ROOMNITE."TRIPLE_QTY", ROOMNITE."QUAD_QTY",
    PERFBUCKET."BNUM_1", PERFBUCKET."BNUM_2",
    BUSINESS."BUS_ID", BUSINESS."BUS_GID", BUSINESS."BUSSTSYNAB", BUSINESS."ABBREV", BUSINESS."AVG_RATE", BUSINESS."CREATE_DAT", BUSINESS."LOST_SYSID", BUSINESS."PICKUP_YN",
    BUSSTSYN."BUSSTSYNTX",
    PERSON."PER_INIT",
    MRKETSEG."MARKET_ABV"
FROM
  {oj ((("NSS_REPORT"."dbo".ALLOCATE LEFT OUTER JOIN "NSS_REPORT"."dbo".UBUCKET ON "NSS_REPORT"."dbo".ALLOCATE.DATE_ALLOC="NSS_REPORT"."dbo".UBUCKET.BDATE_1 AND UBUCKET.UKEY_USER=0 AND UBUCKET.BNUM_1=1 ) 
         "NSS_REPORT"."dbo".ALLOCATE LEFT OUTER JOIN "NSS_REPORT"."dbo".DATECOLL ON "NSS_REPORT"."dbo".ALLOCATE.DATE_ALLOC = "NSS_REPORT"."dbo".DATECOLL.START_DATE)
         "NSS_REPORT"."dbo".ALLOCATE LEFT OUTER JOIN "NSS_REPORT"."dbo".ROOMNITE ON "NSS_REPORT"."dbo".ALLOCATE.DATE_ALLOC = "NSS_REPORT"."dbo".ROOMNITE.ROOMDATE)}
 
 {oj ("NSS_REPORT"."dbo".ROOMNITE LEFT OUTER JOIN "NSS_REPORT"."dbo".BUSINESS ON "NSS_REPORT"."dbo".ROOMNITE.BUS_ID = "NSS_REPORT"."dbo".BUSINESS.BUS_ID AND "NSS_REPORT"."dbo".ROOMNITE.BUS_GID = "NSS_REPORT"."dbo".BUSINESS.BUS_GID)
   ("NSS_REPORT"."dbo".BUSINESS LEFT OUTER JOIN "NSS_REPORT"."dbo".BUSSTSYN ON "NSS_REPORT"."dbo".BUSINESS.BUSSTSYNID = "NSS_REPORT"."dbo".BUSSTSYN.BUSSTSYNID AND "NSS_REPORT"."dbo".BUSINESS.BUSSTSYGID = "NSS_REPORT"."dbo".BUSSTSYN.BUSSTSYGID)
   ("NSS_REPORT"."dbo".BUSINESS LEFT OUTER JOIN "NSS_REPORT"."dbo".BUSTYSYN ON "NSS_REPORT"."dbo".BUSINESS.TY_SYN_ID = "NSS_REPORT"."dbo".BUSTYSYN.TY_SYN_ID AND "NSS_REPORT"."dbo".BUSINESS.TYSYN_GID = "NSS_REPORT"."dbo".BUSTYSYN.TYSYN_GID)
   ("NSS_REPORT"."dbo".BUSINESS LEFT OUTER JOIN  "NSS_REPORT"."dbo".PERSON ON "NSS_REPORT"."dbo".BUSINESS.BOOKEDBY = "NSS_REPORT"."dbo".PERSON.PERSON_ID AND "NSS_REPORT"."dbo".BUSINESS.BOOKEDBYG = "NSS_REPORT"."dbo".PERSON.PERSON_GID)
   ("NSS_REPORT"."dbo".BUSINESS LEFT OUTER JOIN "NSS_REPORT"."dbo".ORGANIZ ON "NSS_REPORT"."dbo".BUSINESS.CLIENT = "NSS_REPORT"."dbo".ORGANIZ.ORG_ID AND "NSS_REPORT"."dbo".BUSINESS.CLIENTG = "NSS_REPORT"."dbo".ORGANIZ.ORG_GID) }
 
  {oj ("NSS_REPORT"."dbo".ORGANIZ LEFT OUTER JOIN "NSS_REPORT"."dbo".MRKETSEG ON "NSS_REPORT"."dbo".ORGANIZ.MKT_SEG_ID = "NSS_REPORT"."dbo".MRKETSEG.MKT_SEG_ID AND "NSS_REPORT"."dbo".ORGANIZ.MKTSEG_GID = "NSS_REPORT"."dbo".MRKETSEG.MKTSEG_GID)  }
WHERE
    allocate.date_alloc >= {D '1998-01-01'} and allocate.date_alloc < {D '1998-02-01'} and BUSTYSYN.SYSBUSTYID <> 1

But this time when I run it on the SQL Server it still gives me the error.

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'NSS_REPORT'.

Please Help.