Since I need to send the query statement through a http post request there are certain limitations.
1. It should be a one liner
2. it should be created in tempdb since i am going to drop it afterwards.
since SQL server takes CREATE VIEW statement only in new line I am feeding new line characters to the statement. here is the statement:
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10); ('USE tempdb;' +@NewLineChar + 'GO' +@NewLineChar +'CREATE VIEW temp_view AS select name from sys.databases')
This query gives me following error:
Msg 102, Level 15, State 1.
Incorrect syntax near 'USE tempdb;'. (Line 1)
what could be the problem ?
edit: The same query works like this
CREATE VIEW temp_view AS select name from sys.databases
where is the syntax error?