| Author |
 Topic  |
|
|
dslaby
Starting Member
2 Posts |
 Posted - 10/10/2001 : 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?
|
|
|
robvolk
Most Valuable Yak
USA
15558 Posts |
Posted - 10/11/2001 : 13:38:41
|
Did query analyzer say exactly where the syntax error occurred? Everything looks fine to me.
Try replacing the EXEC (@select) to SELECT @select and review the SQL statement that the procedure generates. The only thing I can imagine causing a problem is an embedded apostrophe or comma in the data. Also, if you have a lot of componentID values (300+), the SQL statement is probably exceeding the 8000 character limit.
Read the comments on the original article, someone has posted code that handles the 8000 character limit:
http://www.sqlteam.com/item.asp?ItemID=2955
|
 |
|
|
gus169
Starting Member
USA
1 Posts |
Posted - 09/07/2004 : 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
United Kingdom
86 Posts |
Posted - 06/08/2005 : 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
South Africa
1 Posts |
Posted - 03/27/2006 : 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
USA
15558 Posts |
Posted - 03/27/2006 : 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
USA
1 Posts |
Posted - 10/28/2010 : 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
Australia
1 Posts |
Posted - 03/25/2011 : 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! |
|
|
| |
Topic |
|
Error: Incorrect syntax near the keyword 'END'.
