question

timwj62 avatar image
timwj62 asked

SQL query works in MSMS but not in SQLCMD

Thank you in advance for any help you might provide to me. I am a newbie in SQL script and queries writing. I created a simple script to push the results to an html file/output. When I execute the query from the sql management studio it works perfectly. I want to put the query into the scheduler and was using sqlcmd. It gives me a syntax message: Msg 102, Level 15, State 1, Server SAUDER-SQL2014, Line 23 Incorrect syntax near 'Description'. Any ideas? Here is the script: USE OVOLIVE; :out c:\support\myhtmltest.html Declare @Body varchar(max), @TableHead varchar(max), @TableTail varchar(max) Set NoCount On; Set @TableTail = ''; Set @TableHead = '' + 'Production Item Line Up'+ ' ' + '' + ' ' + ' ID' + ' Product Name' + ' UM' + ' Qty' + ' Posted' SELECT @Body = (SELECT Row_Number() Over(Order By "R_W_ Sauder, Inc_$Line Component"."Description") % 2 AS [TRRow], "R_W_ Sauder, Inc_$Line Component"."Description" AS [TD], "R_W_ Sauder, Inc_$OP Output Line"."Description" AS [TD], "R_W_ Sauder, Inc_$OP Output Line"."Unit of Measure Code" AS [TD], CAST("R_W_ Sauder, Inc_$OP Output Line".Quantity AS decimal (10,0)) AS [TD], CAST("R_W_ Sauder, Inc_$OP Output Line"."Posted Quantity" AS decimal (10,0)) AS [TD] FROM "R_W_ Sauder, Inc_$Line Component" , "R_W_ Sauder, Inc_$OP Output Line" WHERE "R_W_ Sauder, Inc_$Line Component"."Actual Lot No_" = "R_W_ Sauder, Inc_$OP Output Line"."OVO Lot No_" AND (("R_W_ Sauder, Inc_$Line Component"."Main Component"='LT1') AND ("R_W_ Sauder, Inc_$Line Component"."Actual Lot No_"<>'')) For XML raw('tr'), Elements) -- Replace the entity codes and row numbers Set @Body = Replace(@Body, '_x0020_', space(1)) Set @Body = Replace(@Body, '_x003D_', '=') Set @Body = Replace(@Body, '1', '') Set @Body = Replace(@Body, '0', '') Set @Body = Replace(@Body, '01-01', 'X') Select @Body = @TableHead + @Body + @TableTail -- return output Select @Body
sqlcmd
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jason_clark03 avatar image
jason_clark03 answered
Try to assign it to a local variable and pass the assigned local variable as the parameter.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

timwj62 avatar image
timwj62 answered
jason, Thank you very much for the feedback. I am sorry but I do not understand what your asking. Can you give me a little more details on this?,Jason thank you for the reply. I am sorry but I do not understand what your telling me to try. Is it possoible to get a little more details?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.