current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

This is my script in C#: 

exec sp_executesql N'
IF OBJECT_ID(N''RealEstate.vwContract'', N''V'') IS NOT NULL
  DROP VIEW RealEstate.vwContract

CREATE VIEW RealEstate.vwContract
AS
  SELECT RealEstate.Contract.ID .... (Rest of Statement omitted for brevity)

The error shows up:

Msg 111, Level 15, State 1, Line 1
'CREATE VIEW' must be the first statement in a query batch.

Please help me.

share|improve this question
    
Don't you need a GO after dropping a view? –  Dominic Zukiewicz Jun 18 '13 at 9:07
    
I tested this solution but it shows up: Incorrect syntax near 'GO' –  Simin.D. Karbasi Jun 18 '13 at 9:11
    
@Simin.D.Karbasi indeed, GO is a white lie introduced by tools like SSMS; GO is not part of the SQL language, but is used by some tools to split a single file into multiple commands –  Marc Gravell♦ Jun 18 '13 at 9:16
add comment

3 Answers

up vote 1 down vote accepted

Split it into two scripts and run first

IF OBJECT_ID(N''RealEstate.vwContract'', N''V'') IS NOT NULL
   DROP VIEW RealEstate.vwContract

then the rest

share|improve this answer
1  
if you do that then the create view is still not the first operation –  Marc Gravell♦ Jun 18 '13 at 9:14
    
@MarcGravell The way I'm interpreting this answer, the idea is exec sp_executesql N'if ... drop view ...' exec sp_executesql N'create view ...'. That works, even if the two sp_executesqls themselves are in the same batch. It functions the same way as what you've used in your answer. –  hvd Jun 18 '13 at 9:34
    
@hvd that isn't what the answer says, though –  Marc Gravell♦ Jun 18 '13 at 10:06
add comment
up vote 4 down vote

The message speaks for itself; the create view must be the first statement - but you can cheat. My create scripts (if I need to run them from ADO.NET, so without GO) tend to look a lot like:

if not exists(select 1 from sys.tables where name='SomeTable')
begin
    exec('create table SomeTable ....blah not shown');
    -- more to add indexing etc
end
if not exists(select 1 from sys.tables where name='SomeOtherTable')
begin
    exec('create table SomeOtherTable ....blah not shown');
    -- more to add indexing etc
end

You can do the same thing with sys.views. Perhaps, untested:

if exists (select 1 from sys.views where name = 'MyView')
    exec ('drop view MyView');
exec ('create view MyView ...blah not shown...');
share|improve this answer
add comment
up vote 0 down vote

You can change the current database for an open SqlConnection very easily:

 connection.ChangeDatabase("YourDB");

An example:

private static void ConctDatabase(string connectionString)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        MessageBox.Show("Database: {0}", conn.Database);
        conn.ChangeDatabase("Northwind");
        MessageBox.Show("Database: {0}", conn.Database);
    }
}
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.