RE: [cfaussie] bizarre CF9 sql server error
Can you show us some of the procedure? The error is pretty self explanatory so we will need to see a little more that that -Original Message- From: Stephen M [mailto:sgmul...@gmail.com] Sent: Friday, 13 January 2012 6:37 PM To: cfaussie Subject: [cfaussie] bizarre CF9 sql server error I am calling this stored proc in a sql server 2005. (actually version is 10.50.1617) is that 2005 or 2008? This stored proc runs fine from with SQL Server Management Studio (SSMS). I can compile it and call it and it returns the data. BUT, when I call it from my CF9 app I get this error as below. between the lines. So CF9 is telling me that my stored proc has incorrect syntax even though SQL Server Management Studio thinks it is fine. I've double checked every line of code that has from and there is nothing unusual going on. I even created a new CF user with sysadmin rights just to rule out some kind of permissions error. Because it compiles OK in SSMS I cannot get a line number for this error. Does the CF9 JDBC driver recompile the stored procs before it runs them? == Message Error Executing Database Query. Detail [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'from'. === regards, Stephen -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
[cfaussie] Re: bizarre CF9 sql server error
On Jan 13, 7:17 pm, Steve Onnis st...@cfcentral.com.au wrote: Can you show us some of the procedure? Its about 1200 lines so its not really practical. In any case, SSMS would tell me if it was a SQL syntax error. We've been using this same stored proc for a few years without any problem. All I did was add in some new columns to some temp tables and ColdFusion started to complain. Maybe its the size that's the problem because it was only 800 lines before I started. Too much business logic in the database. The error is pretty self explanatory so we will need to see a little more that that -Original Message- From: Stephen M [mailto:sgmul...@gmail.com] Sent: Friday, 13 January 2012 6:37 PM To: cfaussie Subject: [cfaussie] bizarre CF9 sql server error I am calling this stored proc in a sql server 2005. (actually version is 10.50.1617) is that 2005 or 2008? This stored proc runs fine from with SQL Server Management Studio (SSMS). I can compile it and call it and it returns the data. BUT, when I call it from my CF9 app I get this error as below. between the lines. So CF9 is telling me that my stored proc has incorrect syntax even though SQL Server Management Studio thinks it is fine. I've double checked every line of code that has from and there is nothing unusual going on. I even created a new CF user with sysadmin rights just to rule out some kind of permissions error. Because it compiles OK in SSMS I cannot get a line number for this error. Does the CF9 JDBC driver recompile the stored procs before it runs them? == Message Error Executing Database Query. Detail [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'from'. === regards, Stephen -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Re: bizarre CF9 sql server error
a missed comma (or space/carriage return) when moved to CF? For it to complain before the FROM clause is IMHO the clue. divide and conquer until it's found perhaps? On Sat, Jan 14, 2012 at 10:20 AM, Stephen M sgmul...@gmail.com wrote: On Jan 13, 7:17 pm, Steve Onnis st...@cfcentral.com.au wrote: Can you show us some of the procedure? Its about 1200 lines so its not really practical. In any case, SSMS would tell me if it was a SQL syntax error. We've been using this same stored proc for a few years without any problem. All I did was add in some new columns to some temp tables and ColdFusion started to complain. Maybe its the size that's the problem because it was only 800 lines before I started. Too much business logic in the database. The error is pretty self explanatory so we will need to see a little more that that -Original Message- From: Stephen M [mailto:sgmul...@gmail.com] Sent: Friday, 13 January 2012 6:37 PM To: cfaussie Subject: [cfaussie] bizarre CF9 sql server error I am calling this stored proc in a sql server 2005. (actually version is 10.50.1617) is that 2005 or 2008? This stored proc runs fine from with SQL Server Management Studio (SSMS). I can compile it and call it and it returns the data. BUT, when I call it from my CF9 app I get this error as below. between the lines. So CF9 is telling me that my stored proc has incorrect syntax even though SQL Server Management Studio thinks it is fine. I've double checked every line of code that has from and there is nothing unusual going on. I even created a new CF user with sysadmin rights just to rule out some kind of permissions error. Because it compiles OK in SSMS I cannot get a line number for this error. Does the CF9 JDBC driver recompile the stored procs before it runs them? == Message Error Executing Database Query. Detail [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'from'. === regards, Stephen -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group athttp:// groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Re: bizarre CF9 sql server error
ah adding new columns means cached meta data... does any part of the SP not use explicitly named columns, i.e select * etc z On Sat, Jan 14, 2012 at 11:23 AM, Barry Beattie barry.beat...@gmail.com wrote: a missed comma (or space/carriage return) when moved to CF? For it to complain before the FROM clause is IMHO the clue. divide and conquer until it's found perhaps? On Sat, Jan 14, 2012 at 10:20 AM, Stephen M sgmul...@gmail.com wrote: On Jan 13, 7:17 pm, Steve Onnis st...@cfcentral.com.au wrote: Can you show us some of the procedure? Its about 1200 lines so its not really practical. In any case, SSMS would tell me if it was a SQL syntax error. We've been using this same stored proc for a few years without any problem. All I did was add in some new columns to some temp tables and ColdFusion started to complain. Maybe its the size that's the problem because it was only 800 lines before I started. Too much business logic in the database. The error is pretty self explanatory so we will need to see a little more that that -Original Message- From: Stephen M [mailto:sgmul...@gmail.com] Sent: Friday, 13 January 2012 6:37 PM To: cfaussie Subject: [cfaussie] bizarre CF9 sql server error I am calling this stored proc in a sql server 2005. (actually version is 10.50.1617) is that 2005 or 2008? This stored proc runs fine from with SQL Server Management Studio (SSMS). I can compile it and call it and it returns the data. BUT, when I call it from my CF9 app I get this error as below. between the lines. So CF9 is telling me that my stored proc has incorrect syntax even though SQL Server Management Studio thinks it is fine. I've double checked every line of code that has from and there is nothing unusual going on. I even created a new CF user with sysadmin rights just to rule out some kind of permissions error. Because it compiles OK in SSMS I cannot get a line number for this error. Does the CF9 JDBC driver recompile the stored procs before it runs them? == Message Error Executing Database Query. Detail [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'from'. === regards, Stephen -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- Zac Spitzer Solution Architect / Director Ennoble Consultancy Australia http://www.ennoble.com.au http://zacster.blogspot.com +61 405 847 168 -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
[cfaussie] Re: bizarre CF9 sql server error
On Jan 14, 11:23 am, Barry Beattie barry.beat...@gmail.com wrote: a missed comma (or space/carriage return) when moved to CF? For it to complain before the FROM clause is IMHO the clue. divide and conquer until it's found perhaps? It hasn't moved to CF. It's a stored proc that is called from CF On Sat, Jan 14, 2012 at 10:20 AM, Stephen M sgmul...@gmail.com wrote: On Jan 13, 7:17 pm, Steve Onnis st...@cfcentral.com.au wrote: Can you show us some of the procedure? Its about 1200 lines so its not really practical. In any case, SSMS would tell me if it was a SQL syntax error. We've been using this same stored proc for a few years without any problem. All I did was add in some new columns to some temp tables and ColdFusion started to complain. Maybe its the size that's the problem because it was only 800 lines before I started. Too much business logic in the database. The error is pretty self explanatory so we will need to see a little more that that -Original Message- From: Stephen M [mailto:sgmul...@gmail.com] Sent: Friday, 13 January 2012 6:37 PM To: cfaussie Subject: [cfaussie] bizarre CF9 sql server error I am calling this stored proc in a sql server 2005. (actually version is 10.50.1617) is that 2005 or 2008? This stored proc runs fine from with SQL Server Management Studio (SSMS). I can compile it and call it and it returns the data. BUT, when I call it from my CF9 app I get this error as below. between the lines. So CF9 is telling me that my stored proc has incorrect syntax even though SQL Server Management Studio thinks it is fine. I've double checked every line of code that has from and there is nothing unusual going on. I even created a new CF user with sysadmin rights just to rule out some kind of permissions error. Because it compiles OK in SSMS I cannot get a line number for this error. Does the CF9 JDBC driver recompile the stored procs before it runs them? == Message Error Executing Database Query. Detail [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'from'. === regards, Stephen -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group athttp:// groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
[cfaussie] Re: bizarre CF9 sql server error
On Jan 14, 11:29 am, Zac Spitzer zac.spit...@gmail.com wrote: ah adding new columns means cached meta data... does any part of the SP not use explicitly named columns, i.e select * etc z cached in CF or in SQL Server? So a restart would fix it? I can't restart the SQL until Monday but I can easily restart CF. I should have thought of trying that. On Sat, Jan 14, 2012 at 11:23 AM, Barry Beattie barry.beat...@gmail.com wrote: a missed comma (or space/carriage return) when moved to CF? For it to complain before the FROM clause is IMHO the clue. divide and conquer until it's found perhaps? On Sat, Jan 14, 2012 at 10:20 AM, Stephen M sgmul...@gmail.com wrote: On Jan 13, 7:17 pm, Steve Onnis st...@cfcentral.com.au wrote: Can you show us some of the procedure? Its about 1200 lines so its not really practical. In any case, SSMS would tell me if it was a SQL syntax error. We've been using this same stored proc for a few years without any problem. All I did was add in some new columns to some temp tables and ColdFusion started to complain. Maybe its the size that's the problem because it was only 800 lines before I started. Too much business logic in the database. The error is pretty self explanatory so we will need to see a little more that that -Original Message- From: Stephen M [mailto:sgmul...@gmail.com] Sent: Friday, 13 January 2012 6:37 PM To: cfaussie Subject: [cfaussie] bizarre CF9 sql server error I am calling this stored proc in a sql server 2005. (actually version is 10.50.1617) is that 2005 or 2008? This stored proc runs fine from with SQL Server Management Studio (SSMS). I can compile it and call it and it returns the data. BUT, when I call it from my CF9 app I get this error as below. between the lines. So CF9 is telling me that my stored proc has incorrect syntax even though SQL Server Management Studio thinks it is fine. I've double checked every line of code that has from and there is nothing unusual going on. I even created a new CF user with sysadmin rights just to rule out some kind of permissions error. Because it compiles OK in SSMS I cannot get a line number for this error. Does the CF9 JDBC driver recompile the stored procs before it runs them? == Message Error Executing Database Query. Detail [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'from'. === regards, Stephen -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- Zac Spitzer Solution Architect / Director Ennoble Consultancy Australiahttp://www.ennoble.com.auhttp://zacster.blogspot.com +61 405 847 168 -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
[cfaussie] Re: bizarre CF9 sql server error
Well I finally got SSMS to complain and cough up exactly the same error as CF9. (Sorry CF, I never should have doubted you) There was an extra comma in a select just before the FROM that was the result of a hasty copy and paste when I added the extra columns. But it was inside a piece of dynamic SQL that was being built up so it wasn't being caught in the compile. Although I don't know why it wasn't caught in the execution from within SSMS. However the stored proc still won't run, but I'm not blaming CF9 anymore because I'm getting the error and line number from executing within SSMS. Anyway, thanks for your help, I'm going to log off and try to enjoy what's left of Saturday. regards, Stephen -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
