Development/Creating a viewscript

Creating a viewscript

In order to create or update a view:

• Design (or edit) the view in SSMS. Once you are satisfied with the view; create a viewscript by using the "DROP and CREATE" to clipboard command. (Right click on saved view in the object explorer, => Script View as => DROP And CREATE To => Clipboard)

• Create (or edit) your viewscript file in Visual Studio. All the view scripts are kept in the SQL project in the "ViewScripts" directory. Your view name (and filename) should start with "view". The file type is SQL. There is no view template, but the file should have some of the same header as any other SQL file (see Common/Support/ProgrammingRelated/FileTemplates/spTemplate.sql). Included fields should be:
  • Copyright
  • File
  • Name
  • Desc (description)
  • Auth (author)
  • Date
  • Change History

• Paste the clipboard code from SSMS into the section after the header comments.

• Delete the segments that set the database. (e.g.

  USE [Lygaeoidea1]

  GO

  ) There will be one before and after the code that drops the view if it exists.

• The code that sets ANSI_NULLS and QUOTED_IDENTIFIER should remain.

• The actual "CREATE VIEW" code must be modified. The tool to update database objects run from ApexTaxon does not handle a "CREATE VIEW" command properly. It must be modified into an "EXEC" command. Prepend "CREATE VIEW" with

  EXEC dbo.sp_executesql @statement = N'

  and don't forget to add the trailing " ' " to the end of the command (just above the next "GO").