Subqueries are not allowed in this context. Only scalar expressions are allowed My customer is running MS SQL Server 2000.
I completed a project, but I failed to realize that MS SQL Server 2000 will not allow a select inside insert into values thus giving the error:

Subqueries are not allowed in this context. Only scalar expressions are allowed.


Th

insert into table_one (greeting_column, name_column)
values (
         'hello',
         (select column_1 from table_to where name = 'bob')
       )


I'm calling this query from coldfusion10. 
I've already realized a solution utilizing coldFusion10 for this problem by replacing the select statement calling the query and storing the results in a coldFusion list variable, then iterate through a loop that inserts the contents of the CF list variable to its respective record, but this takes much more processing then a simple SQL statement. I've found solution like this:

CREATE PROC whatever
@REC int,
@ChangedIP varchar(15),
@ChangedBY varchar(30)
AS
INSERT INTO table_LOG
SELECT *, GETDATE(), @ChangedID, @ChangedBy FROM table WHERE record = @REC


But I don't think coldFusion will allow this in a query (will try after the weekend)
Is there a way to re-write not using Transact-SQL Variables?
You could use this code instead - just use the SELECT to provide the values, including a fixed literal:

INSERT INTO dbo.table_one (greeting_column, name_column)
   SELECT
      'hello', column_1 
   FROM 
      dbo.table_to 
   WHERE 
      name = 'bob'


You cannot mix the VALUES() and the SELECT methods of providing values for an INSERT - either you can provide literals and SQL variables for a VALUES(.....) list of values - or then you need to use the SELECT instead