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 |
|
orozcoc
Starting Member
13 Posts |
 Posted - 2002-09-19 : 12:32:16
|
| I implemented this script http://www.sqlteam.com/item.asp?ItemID=2955 and got this error message from an ASP .NET page: Incorrect syntax near the keyword 'END'. The END corresponds to the CASE statements that are dynamically generated by the SP. The line it references in the aspx page is the ExecuteReader(). The stored procedure works perfectly from within SQL Server, I suspect is an ADO problem/bug. Anyone has any ideas ? or comments on this problem ? Thanks a lot |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-19 : 12:41:35
|
| Most likely you have too many pivot columns and the generated SQL is exceeding the maximum length of 8000 characters. Every example I've seen of this error ultimately came back to that cause. You can test this by PRINTing the @select variable instead of EXECing it, and copying/pasting the sql into a query analyzer window to check the syntax.Read the comments section of the article, there are some workarounds that people have used to alleviate this problem.Out of curiosity, how many pivoted columns do you have? |
 |
|
|
orozcoc
Starting Member
13 Posts |
Posted - 2002-09-19 : 12:43:20
|
| Here's the select I'm sending to the SP:parSelect.Value = "SELECT MouseId, DateOftest, ExpNumber, Background, Generation, Age, Source, Sex, max(ExpNumber) NoOfExp FROM Cross_Tab_1 where Expnumber >= 1 AND AssayId = 1 AND Baseline = 1 GROUP BY MouseId, ExpNumber, DateOfTest, Background, Generation, Age, Source, Sex Order by DateOftest DESC" |
|
|
|
orozcoc
Starting Member
13 Posts |
Posted - 2002-09-19 : 12:44:46
|
| I did the printing of the output from the SP and it worked fine on SQL Analyzer, Im going to try now with less columns. |
|
|
|
orozcoc
Starting Member
13 Posts |
Posted - 2002-09-19 : 13:27:53
|
| Found the problem: SP permissions on SQL Server, my regular web user doesn't have enough permissions to do all the tasks the SP needs to do, I'm incrementing security access to the web user on the SP, let's see if that works. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-19 : 13:36:49
|
| Oooooo, yeah that might be a problem.Also make sure that the users have CREATE TABLE permissions on tempdb, I remember being bitten by that a few times. |
|
|
|
|
|
|
|
|
Crosstab/ADO bug ?