Thanks for the response. A couple of things here.
* SP is not applicable for this application since this is a generic purpose component
(constraint one, may not have permission to create an SP with a client's db).
* As you know, it's dynamic query, my syntax and flow and yours would come to same
resultset, the slight difference,
the syntax of "select var = ..." should be same as "set var = ..." while I forgot to
mention that I had "set nocount on" on top. To verify it, I tested it using "set var
= ...", got same CF err msg.
It seems quite likely the JDBC driver fails to parse quotes properly.
>> EXEC('select ' + @col1 + ' as col1, ' + @col2 + '
>> as col2, ' + @col3 + ' as col3 from ' + @obj + ' order
>> by ' + @col2);
>
>What your trying to do above.. should really be done in a Stored Procedure..
>If you cant do that.. try to build the SQL Statement first.
>
>i.e
>declare @sqlStmt varchar(500)
>if (@chkCols >= 3)
>
>set @sqlStmt = 'select ' + @col1 + ' as col1, ' + @col2 + 'as col2, ' +
>@col3 + ' as col3 from ' + @obj + ' order by ' + @col2;
>
>else
>
>set @sqlStmt = 'select ' + @col1 + ' as col1, ' + @col2 + '
>as col2, ' + @col3 + ' as col3 from ' + @obj + ' order by ' + @col2;
>
>exec(@sqlStmt)
>
>You might also want to look into "sp_executesql".
>Your problem really might be some casting/invalid quotes.
>
>Joe Eugene
>
>
>> -----Original Message-----
>> From: LI, Chunshen (Don) [mailto:[EMAIL PROTECTED]
>> Sent: Sunday, July 27, 2003 8:21 PM
>> To: CF-Talk
>> Subject: JDBC Driver problem with MS SQL Server 2000
>>
>>
>> My environment, CFMX Prof, Windows XP Pro OS, MS SQL
>> Server 2000 DB server.
>>
>> The following query executes fine with Query Analyzer
>> and the query execution shows up fine by CF debugging,
>> however, I got the following annoying err msg, please
>> note, I must use column alias for referencing later,
>> and if I don't use column alias the JDBC driver seems
>> to be OK but how come? column alias is a simple
>> function with any DBMS, no reason JDBC driver fails to
>> support it. Any one shied some light? Thanks.
>>
>> "Macromedia][SQLServer JDBC
>> Driver][SQLServer]Incorrect syntax near the keyword
>> 'as'.
>> ...
>> <cfquery name="getStuff" datasource="#Attributes.ds#">
>> 136 : set nocount on
>> 137 : declare @obj varchar(150)
>> ------------------------------------------------------------------
>> --------------
>>
>> SQL stmt ommitted here
>>
>> DATASOURCE XXXyyyZZZds
>> VENDORERRORCODE 156
>> SQLSTATE HY000
>>
>>
>> declare @obj varchar(150)
>> declare @col1 varchar(25)
>> declare @col2 varchar(350)
>> declare @col3 varchar(350)
>> declare @chkCols int
>>
>> select @chkCols =
>> (select count(name)
>> from syscolumns
>> where id = 2137058649);
>>
>> select @obj =
>> (select name
>> from sysobjects
>> where id = 2137058649);
>> select @col1 =
>> (select name
>> from syscolumns
>> where id = 2137058649
>> and colid = 1);
>> select @col2 =
>> (select name
>> from syscolumns
>> where id = 2137058649
>> and colid = 2);
>> select @col3 =
>> (select name
>> from syscolumns
>> where id = 2137058649
>> and colid = 3);
>>
>> set nocount off;
>> if (@chkCols >= 3)
>> Begin
>> EXEC('select ' + @col1 + ' as col1, ' + @col2 + '
>> as col2, ' + @col3 + ' as col3 from ' + @obj + ' order
>> by ' + @col2);
>> End
>> else
>> Begin
>> EXEC('select ' + @col1 + ' as col1, ' + @col2 + '
>> as col2 from ' + @obj + ' order by ' + @col2);
>> End
>>
>>
>>
>>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
