I have written a SQL query which essentially totals the hours spent on
each case, and calculates the total amount to be billed (by working out
the price of each activity then summing them). To do this, I have put
the rates in a temporary SQL table, and then done inner joins between
the filtered incident entity, the filtered service appointment entity
and my rates table. This works perfectly when I run it in the Query
Analyzer in SQL Server, but when I put this in Visual Studio to try to
generate a nice looking report to use in CRM, it tells me there is a
"Syntax error or access violation."
I can't see that it would be a syntax error, as the code executes
perfectly in the Query Analyzer, but I also can't see why it would be
an access violation.
When I click on the Help button (available in the error message
dialog), it says the error is "Cannot drop the table <tablename>
because it does not exist in the system catalog. There is no drop
table command in the query, so I don't know how to fix this!
I'm guessing it has something to do with my table creation, so this is
an excerpt of the code:

Declare @ServiceRates Table (StartDate datetime NOT NULL, FinishDate
datetime NOT NULL, .... etc )
Insert @ServiceRates VALUES ('20061221', '20061231', ...)
Insert @ServiceRates VALUES ('20061221', '20061231', ...)
etc

Then it is used in the query as follows:
Select fi.ticketnumber, ... ((sa.actualdurationminutes/60.0) *
rates.rate) as cost
from FilteredIncident as Fi
Inner Join FilteredServiceAppointment as sa on fi.incidentid =
sa.regardingobjectid
inner join @ServiceRates as rates on ((fi.contractservicelevelcodename
= rates.ContractLevel) and (sa.new_servicetypename = rates.ServiceType)
and (sa.new_timetypename = rates.TimeType))
group by fi.accountidname, ... etc

Any ideas?