Why cant we execute the same SQL script in Visual Studio?

Why cant we execute the same SQL script in Visual Studio?

• haryindsfjdbf

  Did you try to remove GO statements, not just for test.
  
  

• davco

  Hi Imesh,

  We need a bit more information to help with this, for starters, how about the script you're trying to run, the steps you're following in VS and the error you're getting

  Regards,

  Mike

  
  
  

• mirolslaw

  Hi Gary,
  
  Thanks a lot for your reply, I tried to execute the script again by commenting the "GO" statements but it still give errors. Anyway as you said I believe that the problem is with those "GO" statements and I will look into that to find a solution. What do you think
  
  DMan1 Wrote:
        "Instead of trying to actually run the same script from the IDE, try and execute it as a stored procedure using a command object "
  
  In this scenario I cannot use a command object, the requirement is to run it as a sql script using a database project.
  
  Best regards,
  Imesh.
  
  

• Frances83

  Hi Mike,
  
  This is the script:
  
  IF EXISTS (
  SELECT *
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE SPECIFIC_NAME = 'Insert_'
  )
  DROP PROCEDURE Insert_
  GO
  CREATE PROCEDURE Insert_
  @CategoryID_ INT,
  @CategoryName_ VARCHAR(200),
  @Picture_ VARCHAR(200),
  @HasSubCategories_ VARCHAR(100)
  AS
  INSERT INTO Categories_TAB
  (
  CategoryID,
  CategoryName,
  Picture,
  HasSubCategories
  )
  VALUES
  (
  @CategoryID_,
  @CategoryName_,
  @Picture_,
  @HasSubCategories_
  );
  GO
  
  ---------------------------------------------------------------
  IF EXISTS (
  SELECT *
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE SPECIFIC_NAME = 'Update_'
  )
  DROP PROCEDURE Update_
  GO
  CREATE PROCEDURE Update_
  @CategoryID_ INT,
  @CategoryName_ VARCHAR(200),
  @Picture_ VARCHAR(200),
  @HasSubCategories_ VARCHAR(100),
  @CategoryID INT
  AS
  UPDATE Categories_TAB
  SET CategoryID = @CategoryID_,
  CategoryName = @CategoryName_,
  Picture = @Picture_,
  HasSubCategories = @HasSubCategories_
  WHERE CategoryID = @CategoryID ;
  GO
  
  ---------------------------------------------------------------
  IF EXISTS (
  SELECT *
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE SPECIFIC_NAME = 'Delete_'
  )
  DROP PROCEDURE Delete_
  GO
  CREATE PROCEDURE Delete_
  @CategoryID INT
  AS
  DELETE
  FROM Categories_TAB
  WHERE CategoryID = @CategoryID ;
  GO
  
  This script executes perfectly in SQL Server 2005 Management Studio Express but it gives these errors in Visual Studio:
  
  Incorrect syntax near 'GO'.
  Incorrect syntax near 'GO'.
  Incorrect syntax near 'GO'.
  Must declare the scalar variable "@CategoryID".
  Incorrect syntax near 'GO'.
  Must declare the scalar variable "@CategoryID".
  No rows affected.
  (0 row(s) returned)
  
  What I thinking was, why does it parse the same SQL script in those two IDEs in two different ways.
  
  Best regards,
  Imesh
  
  

• laiseng

  Hi Mike,

  I think you have move this thread to Visual Basic section but i'm using C# (actually I forgot to mention it before since it was regarding a SQL Server DB project).

  Anyway in VS I created a new C# solution and add a new SQL Server Database project. Then inside that I created the above given SQL script. Then I right click on the sql file in the solution explorer and click on Debug (Debug script. as I remember). Thats what I did in VS to deploy it.

  Best regards,

  Imesh

  
  

• SDavis7813

  Hi Imesh,

  Given the format you've used, yes, I believe the VS IDE parses queries differently, but I'm not really an expert on VS. I'm not sure where you're trying to run this query in VS, it might help to know what exactly you have open in VS when you're trying to do this.

  Management Studio and Visual Studio are two different products, created by two different groups designed to do two different things. It's not surprising that they work differently. GO is the keywork in Management Studio that tells the parser to break commands up. In VS, the context is usually about creating a specific object, not running ad hoc strings of SQL commands.

  For good measure, I'm going to move this question into a VS forum where you'll be working with the right experts.

  Mike

  
  
  

• dgolds

  Instead of trying to actually run the same script from the IDE, try and execute it as a stored procedure using a command object
  
  

• TedCullen

  Re: the GO syntax errors:
  
  From the SQL 2000 BOL:
  
  

  GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer.

  
  

  SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. SQL Query Analyzer and the osql and isql command prompt utilities implement GO differently. For more information, see osql Utility, isql Utility, and SQL Query Analyzer.
  

  
  

  I ran across this when trying to execute a sql script generated from ApexSQLDiff, using a SQLCommand object in C# 2005. I modified my script, using a StringBuilder to replace all instances of "\nGO" with "\n--GO," and the script ran fine.

  
  

  I haven't looked at the rest of your script, but I'm guessing the other variable errors will fall in line after the GOs are removed.

  Gary
  

  
  
• Why cant we execute the same SQL script in Visual Studio?