Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Error: Incorrect syntax near the keyword 'END'.

Author  Topic 

dslaby
Starting Member

2 Posts

Posted - 2001-10-10 : 21:13:20
I have a single table in which I use the following statement in SQL Query Analyser:

EXECUTE sp_crosstab 'select providerID from tblEmploymentSummary group by ProviderID', 'sum(BillAmount)', 'ComponentID', 'tblEmploymentSummary'

I cut and pasted your store procedure. What could be causing the incorrect syntax error on execute?

gus169
Starting Member

1 Post

Posted - 2004-09-07 : 16:57:58
This is probably too late, but what I found is that the problem might lay in the permission in the tempdb database. The crosstab stored procedures calls 'tempdb.information_schema' to figure out what delimiter is needed for the case statements. Now assuming that you a user with minimum permissions is calling sp_crosstab chances are they will not have permissions to access 'tempdb.information_schema'. Therefore, my solution was to add the user to the tempdb database and give them the database role of db_datareader. I hope this helps.
Go to Top of Page

mparter
Yak Posting Veteran

86 Posts

Posted - 2005-06-08 : 18:25:22
Glad I found this as I was running into this issue aswell. The SP ran fine in Query Analyzer but when ran in my ASP.NET app, it failed with the same error message.

When I gave the user datareader permissions on the tempdb database as described, it worked fine

Thanks for sharing the result.
Go to Top of Page

ctudorprice
Starting Member

1 Post

Posted - 2006-03-27 : 00:14:50
I've been running into a problem where, if SQLServer is restarted, the permissions that I set on the tempdb database reset to their defaults and thus I can only get this to work after resetting the permissions again. We restart the server every week or so... so this is a pain. Any ideas?
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-03-27 : 09:05:35
Add the appropriate permissions to the model database. Tempdb is built from model when SQL Server is restarted, and will inherit objects, permissions, etc. from it.
Go to Top of Page

BethW
Starting Member

1 Post

Posted - 2010-10-28 : 12:41:09
quote:
Originally posted by mparter

Glad I found this as I was running into this issue aswell. The SP ran fine in Query Analyzer but when ran in my ASP.NET app, it failed with the same error message.

When I gave the user datareader permissions on the tempdb database as described, it worked fine

Thanks for sharing the result.



Even 5 years later, you solved a problem that I spent days trying to figure out. Thanks!
Go to Top of Page

DanFindley
Starting Member

1 Post

Posted - 2011-03-25 : 20:06:11
quote:
Originally posted by gus169

This is probably too late, but what I found is that the problem might lay in the permission in the tempdb database. The crosstab stored procedures calls 'tempdb.information_schema' to figure out what delimiter is needed for the case statements. Now assuming that you a user with minimum permissions is calling sp_crosstab chances are they will not have permissions to access 'tempdb.information_schema'. Therefore, my solution was to add the user to the tempdb database and give them the database role of db_datareader. I hope this helps.



7 Years later, a google for Incorrect syntax near the keyword 'END' puts this result at the top, and it fixed my problem perfect. Thanks all!
Go to Top of Page
   

- Advertisement -