SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Error: Incorrect syntax near the keyword 'END'.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dslaby
Starting Member

2 Posts

Posted - 10/10/2001 :  21:13:20  Show Profile  Reply with Quote
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
15683 Posts

Posted - 10/11/2001 :  13:38:41  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Go to Top of Page

gus169
Starting Member

USA
1 Posts

Posted - 09/07/2004 :  16:57:58  Show Profile  Send gus169 an AOL message  Reply with Quote
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

United Kingdom
86 Posts

Posted - 06/08/2005 :  18:25:22  Show Profile  Reply with Quote
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

South Africa
1 Posts

Posted - 03/27/2006 :  00:14:50  Show Profile  Reply with Quote
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

USA
15683 Posts

Posted - 03/27/2006 :  09:05:35  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
1 Posts

Posted - 10/28/2010 :  12:41:09  Show Profile  Reply with Quote
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

Australia
1 Posts

Posted - 03/25/2011 :  20:06:11  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000