Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [Sites_tbl]'
Print ' Creation Date : 22.03.2006'
Print ' Autor         : J.Feuerstein'
Print '-------------------------------------------------------------------------'

use [#DB_N#]


Declare @MyActionMsg1 nvarchar (max)
Declare @MyTableName nvarchar (max)
    set @MyTableName='TEST_dell_me_Sites_tbl'
--------------------------------------------------------------------------------
-- verzweigen auf TableCreate: oder TableUpdate:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST_dell_me_Sites_tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
   goto TableUpdate
  END
ELSE
  BEGIN
   goto TableCreate
  END

--------------------------------------------------------------------------------
TableCreate:
  set @MyActionMsg1=
      '-- Table: ' + @MyTableName + ' dose not exist and will be created new'
  Print (@MyActionMsg1)
  CREATE TABLE [dbo].[TEST_dell_me_Sites_tbl] (
        [Id] [uniqueidentifier] NOT NULL ,
        [pSite_ID]       [nvarchar] (50)  NOT NULL ,   -- ReferenzNummer in Oracle DB 99899900
        [SiteAktive]     [bit]            NULL     ,   -- ist diese Site aktive Ja/Nein
        [SiteID]         [nvarchar] (50)  NOT NULL ,   -- JobMgr interne kuerzel für die TCE DB z.B. TCNG
        [pName]          [nvarchar] (255) ,            -- =bezeichung der DB "TEST System 123"
        [DBNetworkAlias] [nvarchar] (50) ,
        [UserName]       [nvarchar] (32) ,
        [Password]       [nvarchar] (32)  NULL ,
        [OracleHome]     [nvarchar] (255) NULL ,
        [OracleBin]      [nvarchar] (255) NULL ,
        [DBProvider]     [nvarchar] (255) NULL ,         --  MSSql= System.Data.SqlClient Oracle=System.Data.OracleClient
        [DBConnectionString]     [nvarchar] (max) NULL , --  Data Source=DbServerName\InstanceName;Initial Catalog=CatalogName;User ID=#UserName#;Password=#Password#;Connection Timeout=30;Persist Security Info=True;
        [Tns_Admin]      [nvarchar] (255) NULL ,
        [ImportItemSearchNames]         [nvarchar] (255) NULL ,
        [ImportItemLastModifyDateBegin] [Datetime] NULL ,
        [ImportItemLastModifyDateEnd]   [Datetime] NULL ,
        [ImportAssembMaxComp]           [int] NULL ,
        [ImportAssembMaxLev]            [int] NULL ,
        [ImportAssembMaxTimeSec]        [int] NULL ,       -- maximale Dauer eines Assembly Importes (neu 19.3.08)
        [ImportMsgAfterNImports]        [int] NULL ,       -- nach vievielen Imports soll eine Ausgabe im InfoFenster ausgegeben werden (neu 19.3.08)
        [ImportLevelType]               [nvarchar] (10) ,  -- ImportlevelType 10 = full 20 = medium 31 = quick1 32 = quick2
        [ImportReadnew]                 [bit] NULL ,     -- sollen die Daten neu gelesen werden neu 21.11.2007
        [ImportDBReconnetWaitTime_mSec] [int] NULL ,     -- DB Reconnect wait time (new 27.12.2008)
        [ImportDBReconnetRetrysMax]     [int] NULL ,     -- DB Reconnect N Retrys  (new 27.12.2008)

        -- entnommen 27.12.2008 J.fes da ersetzt durch ProcessGroups
        -- [ImportObjectNewProcStateMask]  [nvarchar] (11) NULL , -- Standard Status bei neu Import eines Objectes wert = 'NNNNNNNNNNN'
        -- [ImportObjectUpdProcStateMask]  [nvarchar] (11) NULL , -- Standard Status bei update Import eines Objectes wert = '-----------'

        [NativeRoot_DP]                 [nvarchar] (255) ,  -- Rootpath für den Import
        [NativeFileMaskInclude]         [nvarchar] (255) ,  -- Einschluss Liste *.Prt
        [NativeFileMaskExclude]         [nvarchar] (255) ,  -- Ausschluss Liste *XYZ*.prt
        [NativeMaskAp]                  [nvarchar] (255) ,  -- Kennzeichner für Assemply Parts

        [Group]                         [nvarchar] (32) ,
        [Note]                          [nvarchar] (255) ,
         CONSTRAINT                     [_dellMe_Sites_tbl_pk] PRIMARY KEY NONCLUSTERED ([Id]),
         CONSTRAINT                     [_dellMe_pSite_ID_uq] UNIQUE ([pSite_ID]),
         CONSTRAINT                     [_dellMe_SiteID_uq] UNIQUE ([SiteID])
         ) ON [PRIMARY]

  Goto EndeExit

--------------------------------------------------------------------------------
TableUpdate:

  set @MyActionMsg1=
       '-- Table: [' + @MyTableName + '] exist and will be updated'
  Print (@MyActionMsg1)

  Declare @ColumeName nvarchar (256)
  Declare @ColumeNameOld nvarchar (256)
  Declare @ColumeNameNew nvarchar (256)
  Declare @ExecuteStr nvarchar (max)

  -- Alle Updates hierher
  set @ColumeName='ImportAssembMaxTimeSec'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [max duration for importing assembly (new 19.3.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [int] NULL'
      execute (@ExecuteStr)
    end

  set @ColumeName='ObjectPuid'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. PUID of Object (new 2.1.2009 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(15) COLLATE Latin1_General_CS_AS NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='ObjectPuid123'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. PUID of Object (new 2.1.2009 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(15) COLLATE Latin1_General_CS_AS NULL'
      execute (@ExecuteStr)
    end

  set @ColumeName='ObjectPuid345'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. PUID of Object (new 2.1.2009 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(15) COLLATE Latin1_General_CS_AS NULL'
      execute (@ExecuteStr)
    end

--------------------------------------------------------------------------------
EndeExit:
 Print '------------------------------------------------------------------------'
 Print (@MyActionMsg1)
 Print ('   |-> Done on Database: #DB_N#')
 Print '------------------------------------------------------------------------'

GO
