1 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Permissions problem with XML Report

I've got an XML Report that creates a temporary table. This report worked prior to 6.10 but since upgrading has stopped working. I suspect it is something to do with the temp table. When I run it within Recorder I get the following error:

There is an error in the SQL code. The error message is: 'Incorrect syntax near the keywork 'INTO''

The code it is referring to is selecting into the temp table. Here's the full code. If I run the query directly against the db (while in SSMS), it works fine, which is why I suspect it is a permissions thing.

Any suggestions on how to fix this?

Thanks,
Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

2 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: Permissions problem with XML Report

Hi Charles

I think John van Breda is out of the office this week, but we could ask him whether he wouldn't mind taking a quick look when he gets back.

Best wishes,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

Sarah Shaw
Biodiversity Information Assistant
JNCC

3 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Permissions problem with XML Report

That would be great, thanks.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

4 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Permissions problem with XML Report

Here's some info for you. In order for the report to be used, Recorder converts the final SELECT statement in the report into a SELECT INTO statement by inserting INTO #REPORT_OUTPUT before the last FROM in the SQL. Unfortunately this approach is a bit simplistic so for the SQL in this report it inserts the INTO into the subquery in the last main statement, breaking the report.

I can't check that this fixes it because I don't have VW_TAXON_GROUP on my database but the way to fix it would be to use SELECT ... INTO to convert the last SQL statement's subquery into a temp table first, or perhaps you could rewrite this part of the SQL to avoid the subquery?

This code did change in version 6.9.3 though looking at it its not obvious why the change should break anything.

I hope that helps,

John van Breda
Biodiverse IT

johnvanbreda's Website