Recent Creating View

I know that most of the persons could not get me exactly
so i am again going to clarify my problem.
I want to create tables and views from inside the VB-code
using SQL-script. I can do this by reading a text (script)
file, storing the string in a variable say MYSQLSTR and
running it like CN.EXECUTE MYSQLSTR inside my code. Now I
have created the sql-script from enterprise manager which
includes GO for each create statement of the table. I run
it from inside my VB-Code. error raised which says 'GO' is
not recognizable. OK. I removed 'GO' from the file and run
it again as i described above. It is running fine and
creating tables in the database. This file, after removing
all the 'GOs' runs inside the query analyser too.
Now when I do this with VIEWS, same problems come and I
remove 'GO' but this time it says like (even inside query
analyser):
Server: Msg 156, Level 15, State 1, Procedure View1, Line
10
Incorrect syntax near the keyword 'SET'.
Server: Msg 111, Level 15, State 1, Procedure DmView1,
Line 16
'CREATE VIEW' must be the first statement in a query batch.
Now I have to create views at one go as i create all the
tables at one go by running the SQL-script through my code.
ur suggestions are welcome.
Billi98Please don't re-post. I provided an answer in your prior post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:4c1001c42c3e$3d1d2050$a301280a@.phx.gbl...
> I know that most of the persons could not get me exactly
> so i am again going to clarify my problem.
> I want to create tables and views from inside the VB-code
> using SQL-script. I can do this by reading a text (script)
> file, storing the string in a variable say MYSQLSTR and
> running it like CN.EXECUTE MYSQLSTR inside my code. Now I
> have created the sql-script from enterprise manager which
> includes GO for each create statement of the table. I run
> it from inside my VB-Code. error raised which says 'GO' is
> not recognizable. OK. I removed 'GO' from the file and run
> it again as i described above. It is running fine and
> creating tables in the database. This file, after removing
> all the 'GOs' runs inside the query analyser too.
> Now when I do this with VIEWS, same problems come and I
> remove 'GO' but this time it says like (even inside query
> analyser):
> Server: Msg 156, Level 15, State 1, Procedure View1, Line
> 10
> Incorrect syntax near the keyword 'SET'.
> Server: Msg 111, Level 15, State 1, Procedure DmView1,
> Line 16
> 'CREATE VIEW' must be the first statement in a query batch.
> Now I have to create views at one go as i create all the
> tables at one go by running the SQL-script through my code.
> ur suggestions are welcome.
> Billi98
>|||Hi Bill,
Ok then 'Why the go statement didn't work'
The reason is that the GO statement only works through
osql and isql (or in this case Query Analyser), as your
running this through VB, the go statement isn't supported
so it doesn't work.
The second error is a standard sql error, which I don't
think has anything to do with the go statement.
Could you possibly post the first 30 lines of your code ?
Thanks
J
>--Original Message--
>I know that most of the persons could not get me exactly
>so i am again going to clarify my problem.
>I want to create tables and views from inside the VB-code
>using SQL-script. I can do this by reading a text
(script)
>file, storing the string in a variable say MYSQLSTR and
>running it like CN.EXECUTE MYSQLSTR inside my code. Now I
>have created the sql-script from enterprise manager which
>includes GO for each create statement of the table. I run
>it from inside my VB-Code. error raised which says 'GO'
is
>not recognizable. OK. I removed 'GO' from the file and
run
>it again as i described above. It is running fine and
>creating tables in the database. This file, after
removing
>all the 'GOs' runs inside the query analyser too.
>Now when I do this with VIEWS, same problems come and I
>remove 'GO' but this time it says like (even inside query
>analyser):
>Server: Msg 156, Level 15, State 1, Procedure View1, Line
>10
>Incorrect syntax near the keyword 'SET'.
>Server: Msg 111, Level 15, State 1, Procedure DmView1,
>Line 16
>'CREATE VIEW' must be the first statement in a query
batch.
>Now I have to create views at one go as i create all the
>tables at one go by running the SQL-script through my
code.
>ur suggestions are welcome.
>Billi98
>.
>

Recent Creating View

I know that most of the persons could not get me exactly
so i am again going to clarify my problem.
I want to create tables and views from inside the VB-code
using SQL-script. I can do this by reading a text (script)
file, storing the string in a variable say MYSQLSTR and
running it like CN.EXECUTE MYSQLSTR inside my code. Now I
have created the sql-script from enterprise manager which
includes GO for each create statement of the table. I run
it from inside my VB-Code. error raised which says 'GO' is
not recognizable. OK. I removed 'GO' from the file and run
it again as i described above. It is running fine and
creating tables in the database. This file, after removing
all the 'GOs' runs inside the query analyser too.
Now when I do this with VIEWS, same problems come and I
remove 'GO' but this time it says like (even inside query
analyser):
Server: Msg 156, Level 15, State 1, Procedure View1, Line
10
Incorrect syntax near the keyword 'SET'.
Server: Msg 111, Level 15, State 1, Procedure DmView1,
Line 16
'CREATE VIEW' must be the first statement in a query batch.
Now I have to create views at one go as i create all the
tables at one go by running the SQL-script through my code.
ur suggestions are welcome.
Billi98
Please don't re-post. I provided an answer in your prior post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:4c1001c42c3e$3d1d2050$a301280a@.phx.gbl...
> I know that most of the persons could not get me exactly
> so i am again going to clarify my problem.
> I want to create tables and views from inside the VB-code
> using SQL-script. I can do this by reading a text (script)
> file, storing the string in a variable say MYSQLSTR and
> running it like CN.EXECUTE MYSQLSTR inside my code. Now I
> have created the sql-script from enterprise manager which
> includes GO for each create statement of the table. I run
> it from inside my VB-Code. error raised which says 'GO' is
> not recognizable. OK. I removed 'GO' from the file and run
> it again as i described above. It is running fine and
> creating tables in the database. This file, after removing
> all the 'GOs' runs inside the query analyser too.
> Now when I do this with VIEWS, same problems come and I
> remove 'GO' but this time it says like (even inside query
> analyser):
> Server: Msg 156, Level 15, State 1, Procedure View1, Line
> 10
> Incorrect syntax near the keyword 'SET'.
> Server: Msg 111, Level 15, State 1, Procedure DmView1,
> Line 16
> 'CREATE VIEW' must be the first statement in a query batch.
> Now I have to create views at one go as i create all the
> tables at one go by running the SQL-script through my code.
> ur suggestions are welcome.
> Billi98
>

Recent Creating View

I know that most of the persons could not get me exactly
so i am again going to clarify my problem.
I want to create tables and views from inside the VB-code
using SQL-script. I can do this by reading a text (script)
file, storing the string in a variable say MYSQLSTR and
running it like CN.EXECUTE MYSQLSTR inside my code. Now I
have created the sql-script from enterprise manager which
includes GO for each create statement of the table. I run
it from inside my VB-Code. error raised which says 'GO' is
not recognizable. OK. I removed 'GO' from the file and run
it again as i described above. It is running fine and
creating tables in the database. This file, after removing
all the 'GOs' runs inside the query analyser too.
Now when I do this with VIEWS, same problems come and I
remove 'GO' but this time it says like (even inside query
analyser):
Server: Msg 156, Level 15, State 1, Procedure View1, Line
10
Incorrect syntax near the keyword 'SET'.
Server: Msg 111, Level 15, State 1, Procedure DmView1,
Line 16
'CREATE VIEW' must be the first statement in a query batch.
Now I have to create views at one go as i create all the
tables at one go by running the SQL-script through my code.
ur suggestions are welcome.
Billi98Please don't re-post. I provided an answer in your prior post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:4c1001c42c3e$3d1d2050$a301280a@.phx.gbl...
> I know that most of the persons could not get me exactly
> so i am again going to clarify my problem.
> I want to create tables and views from inside the VB-code
> using SQL-script. I can do this by reading a text (script)
> file, storing the string in a variable say MYSQLSTR and
> running it like CN.EXECUTE MYSQLSTR inside my code. Now I
> have created the sql-script from enterprise manager which
> includes GO for each create statement of the table. I run
> it from inside my VB-Code. error raised which says 'GO' is
> not recognizable. OK. I removed 'GO' from the file and run
> it again as i described above. It is running fine and
> creating tables in the database. This file, after removing
> all the 'GOs' runs inside the query analyser too.
> Now when I do this with VIEWS, same problems come and I
> remove 'GO' but this time it says like (even inside query
> analyser):
> Server: Msg 156, Level 15, State 1, Procedure View1, Line
> 10
> Incorrect syntax near the keyword 'SET'.
> Server: Msg 111, Level 15, State 1, Procedure DmView1,
> Line 16
> 'CREATE VIEW' must be the first statement in a query batch.
> Now I have to create views at one go as i create all the
> tables at one go by running the SQL-script through my code.
> ur suggestions are welcome.
> Billi98
>sql

Reality check...

Why the "top 100 percent"? Or am I just retarded?
CREATE VIEW dbo.SmdsProductList
AS
SELECT TOP 100 PERCENT
{whole bunch of columns and expressions}
FROM
{whole bunch of joins}
WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL
ORDER BY SomeOtherThing
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneIt's the only way to put an order by on a view.
"Mike Labosh" wrote:

> Why the "top 100 percent"? Or am I just retarded?
> CREATE VIEW dbo.SmdsProductList
> AS
> SELECT TOP 100 PERCENT
> {whole bunch of columns and expressions}
> FROM
> {whole bunch of joins}
> WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL
> ORDER BY SomeOtherThing
>
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>
>|||'Top' is required because 'ORDER BY SomeOtherThing' has been used in the
Select Statement of the view. Here is related information from BOL (ORDER BY
clause, sorting rows):
'The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified.'
Cheers and Feel happy :)
"Mike Labosh" wrote:

> Why the "top 100 percent"? Or am I just retarded?
> CREATE VIEW dbo.SmdsProductList
> AS
> SELECT TOP 100 PERCENT
> {whole bunch of columns and expressions}
> FROM
> {whole bunch of joins}
> WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL
> ORDER BY SomeOtherThing
>
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>
>|||So they can put ORDER BY in the view (SQL Server won't allow you to put
ORDER BY in the VIEW unless you also specify a TOP clause).
Now why the person would need to have ORDER BY in the view, your guess is as
good as mine. Probably because they expect SELECT * FROM View to return
rows in the order they specified in the view's ORDER BY clause. But IIRC
that is not guaranteed, the optimizer is still free to return the rows in
any order it sees fit, since the actual query does not enforce any
constraint on the order (and will be more likely to in parallel operations,
which is why this behavior is seldom, if ever, planned for, when developing
on a 1-cpu box).
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:ew5LQmQ9FHA.3908@.TK2MSFTNGP10.phx.gbl...
> Why the "top 100 percent"? Or am I just retarded?
> CREATE VIEW dbo.SmdsProductList
> AS
> SELECT TOP 100 PERCENT
> {whole bunch of columns and expressions}
> FROM
> {whole bunch of joins}
> WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL
> ORDER BY SomeOtherThing
>
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||In addition to what the others have answered... don't order views.
When you select from a view, then add the Order By clause.
One reason is that this is an SQL addition to the standards.
Why? because Acces allows it.
I know how much you love Acces developpers so this should be a good reason
for you not to use it.
The other is that you may want to select from the view with a different
Order By clause.
In this case, you just ordered in the View for nothing.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:ew5LQmQ9FHA.3908@.TK2MSFTNGP10.phx.gbl...
> Why the "top 100 percent"? Or am I just retarded?
> CREATE VIEW dbo.SmdsProductList
> AS
> SELECT TOP 100 PERCENT
> {whole bunch of columns and expressions}
> FROM
> {whole bunch of joins}
> WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL
> ORDER BY SomeOtherThing
>
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> It's the only way to put an order by on a view.
DOH! Wow, and I've known that for years, too. Now I feel like an end-user.
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||> Why? because Acces allows it.
> I know how much you love Acces developpers so this should be a good reason
> for you not to use it.
> The other is that you may want to select from the view with a different
> Order By clause.
> In this case, you just ordered in the View for nothing.
heh. That's ok, I don't use this view anyway, I'm just trying to figure it
out. It's obviously something that somebody dragged-n-dropped together with
EM's designer. Looks like a window full of spaghetti.
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||And a nice way of inviting tempdb to the Query party.
ML|||On Tue, 29 Nov 2005 12:23:04 -0500, Mike Labosh wrote:

>Why the "top 100 percent"? Or am I just retarded?
>CREATE VIEW dbo.SmdsProductList
>AS
>SELECT TOP 100 PERCENT
> {whole bunch of columns and expressions}
>FROM
> {whole bunch of joins}
>WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL
>ORDER BY SomeOtherThing
Hi Mike,
You're not retarded. This view is. Views, like tables, are by definition
NOT ordered.
ORDER BY in a VIEW is not permitted.
TOP ... ORDER BY is permitted in a view - has to be, to make sure that
the results of the TOP is predictable.
As a coincidal side-effect, SQL Server 2000 usually returns the rows in
the same order when selecting without ORDER BY from the view. Bad DBAs
and developers have abused this by using TOP 100 PERCENT ... ORDER BY in
a view.
I've already read at least one report of a view "losing it's ordering"
in SQL Server 2005. Who knows - the same might happen when the next SP
for 2000 appears.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)