ALTER<space><space>VIEW not recognized

• 
  What is the error you are getting in the script error screen? Is it a syntax error or a non-schema statement warning? Could you post the view statement the parser is failing on?
  
  Simon C

  Simon C January 13, 2009 08:14

  - 0 +
• 
  The error is: "non-schema statement was ignored" on line 46.
  line 46 reads "ALTER VIEW..."
  
  the file in question is:
  
  /*******************************************************************
  <comment version="1.0">
  <author>bi/sma</author>
  
  <date>21.06.2006</date>
  
  <summary>
  Fuer Adressetiketten. Liefert Name / Vorname des Lernenden,
  sowie die Institutionszeile1, Adresszeile1 und PLZ / Ort des Lehrbetriebs
  </summary>
  
  <remarks>
  Für Bemerkungen ........
  </remarks>
  
  <product>
  <std flag=""/>
  <fh flag=""/>
  <ms flag=""/>
  <bs flag="x"/>
  <ps flag=""/>
  </product>
  
  <multilanguage flag=""/>
  
  <encryption flag="x"/>
  
  <param name="Name 1...">Kommentar ...</param>
  <param name="Name 2...">Kommentar ...</param>
  <param name="Name 3...">Kommentar ...</param>
  <returns></returns>
  
  <performancehistory>
  <detail date="dd.mm.yyyy" Author="xxx">Beschreibung ...</detail>
  <!--<detail date="dd.mm.yyyy" Author="xxx">Beschreibung ...</detail>-->
  </performancehistory>
  
  <changehistory>
  <detail date="04.07.2006" Author="sma">Institutionszeile1 und Adresszeile2 von Lehrbetrieb einfügen</detail>
  <detail date="dd.mm.yyyy" Author="xxx">Beschreibung ...</detail>
  <!--<detail date="dd.mm.yyyy" Author="xxx">Beschreibung ...</detail>-->
  </changehistory>
  </comment>
  *******************************************************************/
  --CREATE VIEW dbo.qryBrn_Rpt_AdressetikettenLernendeMitLehrbetrieb AS
  ALTER VIEW dbo.qryBrn_Rpt_AdressetikettenLernendeMitLehrbetrieb
  WITH ENCRYPTION
  AS
  
  SELECT
  stu.idperson AS IDLernender,
  ISNULL(stu.PersonNachname,'') + ' ' + ISNULL(stu.PersonVorname, '') AS LernenderName,
  CASE
  WHEN ausbildner.PersonFirma2 IS NULL THEN
  ISNULL(ausbildner.PersonFirma1,'')
  ELSE
  ISNULL(ausbildner.PersonFirma1,'') + ' ' + ausbildner.PersonFirma2
  END AS Institutionszeilen,
  ISNULL(ausbildner.PersonAdresse1,'') AS InstitutionAdresszeile1,
  ISNULL(ausbildner.PersonAdresse2, '') AS InstitutionsAdresszeile2,
  ISNULL(ausbildner.PersonPLZ,'') + ' ' + ISNULL(ausbildner.PersonOrt, '') AS AusbildnerPLZOrt
  
  FROM Person AS stu
  LEFT JOIN personengruppierung pg ON stu.idperson = pg.PGMitgliedIDPerson
  AND pg.IDPGTyp = 'AusbVerantw'
  LEFT JOIN person AS ausbildner ON pg.PGChefIDPerson = ausbildner.idperson

  urs.meili January 14, 2009 06:57

  - 0 +
• 
  SQL Compare does not read ALTER VIEW/PROCEDURE/TRIGGER etc statements in a script folder - it only reads schema statements that explicitly create an object.

  Simon C January 14, 2009 07:00

  - 0 +
• 
  this is not true. With SQL Compare 7.1, I have scripts which consist of ALTER VIEW statements. These will be included in the comparison with V7.1, but they are not in V8

  urs.meili January 14, 2009 09:23

  - 0 +
• 
  Sorry, yes. This was a decision made during 8.0 development; that we only read in statements that define a schema; we keep no state. Dealing with ALTER commands for textual objects was an anomaly, as we do not deal with ALTER TABLE etc commands at all. Presumably, you would prefer the 7.1 behaviour?

  Simon C January 14, 2009 12:47

  - 0 +
• 
  Yes, indeed I would prefer 7.1 behaviour. The new behaviour means that our scripts suddenly don't work with SQL Compare anymore, as they used to do. Thus it means that 8.0 is not compatible with 7.1. This is a real "show-stopper" for us.

  urs.meili January 19, 2009 07:06

  - 0 +
• 
  > as we do not deal with ALTER TABLE etc commands at all.
  
  this is also not true. We have lots of ALTER TABLE ... ADD CONSTRAINT commands in our scripts, and they work perfectly with SQL Compare 7.1. It is an absolute *must* that this type of commands still work with V8.0

  urs.meili January 19, 2009 07:09

  - 0 +
• 
  Ah, sorry, I meant we do not deal with ALTER TABLE commands that alter the table structure itself, only commands that add objects or subsidiary features to the table (defaults, constraints, keys, changetracking...)

  Simon C January 19, 2009 07:18

  - 0 +
• 

  urs.meili wrote:

  Yes, indeed I would prefer 7.1 behaviour. The new behaviour means that our scripts suddenly don't work with SQL Compare anymore, as they used to do. Thus it means that 8.0 is not compatible with 7.1. This is a real "show-stopper" for us.

  
  Unfortunately this was a bug in v 7.x. From v8 SQL Compare will no longer consider alter view statements.
  
  SQL Compare is designed for reading in creation statement, and it is not intended to be used to process subsequent change scripts. Script components do not have to be in order, or indeed in a single file. Indeed, if there are many alter statements, not in a single file, which one should take precedence?
  
  One can use SQL Compare together with SQL Server to create a distilled set of SQL scripts, that represent the schema of a database (and not a change script). Concerning the change script, SQL Compare can generate these. As of version 8 it can also generate a change script that can be executed on a live database that is equivalent to a set of creation scripts, i.e. now it will be easier to generate change scripts for live databases based on two script folders (which could be in source control).
  
  Regards,
  Andras

  Andras B January 26, 2009 06:45

  - 0 +
• 
  it's ok for me. I discovered that the behaviour on the ALTER statements was very "weird", some of them will be considered, and some others won't. We will change our script sources to be consistent with V8.

  urs.meili January 27, 2009 08:43

  - 0 +

Add comment

Please sign in to leave a comment.