Execute SQL Task - how to ignore error

• will75

  Mr or Mrs. 500

  Points: 575

  More actions

  January 21, 2015 at 11:07 am

  #397909

  I am trying to run multiple SQL statement as following

  drop index XXX on XX

  go

  drop index XXX on XX

  go

  drop index XXX on XX

  go

  but it does not recognize "go" and will not go to the second statement if the first index does not exist. What am I missing?

  Thanks!

• Lowell

  SSC Guru

  Points: 323482

  More actions

  January 21, 2015 at 11:10 am

  #1771941

  remove the GO statements.

  GO statments are not a SQL command, but instead something that SQL Server Management Studio uses to split batches.

  also, i'd recommend bullet proofing your code like this using EXISTs:

  IF EXISTS(SELECT * FROM sys.indexes

  WHERE name = 'IX_One'

  AND OBJECT_NAME(OBJECT_ID) = 'PatientInsurance')

  DROP INDEX IX_One ON PatientInsurance

  IF EXISTS(SELECT * FROM sys.indexes

  WHERE name = 'IX_Two'

  AND OBJECT_NAME(OBJECT_ID) = 'PatientInsurance')

  DROP INDEX IX_Two ON PatientInsurance

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!