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.
| 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. |
 |
|
|
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. |
|
|
|
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? |
|
|
|
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. |
|
|
|
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! |
|
|
|
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! |
|
|
|
|
|
|
|
|
Error: Incorrect syntax near the keyword 'END'.