מה תרצה/י לחפש?
< All Topics
Print

SQL Tables were not created after Clean Install of the app

Posted
Updated
ByArtur
SQL Tables were not Created after Clean Install

SQL Tables were not Created after Clean Install

1. Issue Overview

During a clean installation of the PrintBOS application, the required SQL tables were not created automatically. This issue can prevent the application from functioning as expected. To resolve this, you need to manually create the missing tables using the provided SQL script.

2. Steps to Resolve

Step 1: Ensure Database Connection

Before running the SQL script, ensure that your database connection is properly configured:

  • Verify the connection string and user permissions.
  • Ensure the user has sufficient privileges, such as db_owner, to execute the script.

Step 2: Run the SQL Script

Execute the following SQL script in your SQL Server Management Studio (SSMS) to create the missing tables:


USE [PrintBOS]
GO
/****** Object:  Table [dbo].[AdvancedJobs]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AdvancedJobs](
	[AdvJobId] [int] NULL,
	[AdvPackageId] [int] NULL,
	[AdvBlock] [int] NULL,
	[JobId] [int] NULL,
	[RequestId] [int] NULL,
	[JobState] [int] NULL,
	[IntermediateFile] [nvarchar](255) NULL,
	[OutputId] [int] NULL,
	[DestinationType] [int] NULL,
	[MetaData] [ntext] NULL,
	[AddInfoString] [nvarchar](512) NULL,
	[AddInfoNumber] [int] NULL,
	[GroupOperationMask] [int] NULL,
	[CREATETIME] [datetime] NULL,
	[LASTUPDATE] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[AutoNumbers]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AutoNumbers](
	[JobId] [int] NULL,
	[AdvJobId] [int] NULL,
	[AdvPackageId] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[cities]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cities](
	[CityId] [varchar](64) NOT NULL,
	[CityName] [varchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ControlData]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ControlData](
	[JobId] [int] NULL,
	[ParamName] [nvarchar](255) NULL,
	[PrintBOSServerIP] [nvarchar](255) NULL,
	[ParamValue] [nvarchar](255) NULL,
	[CREATETIME] [datetime] NULL,
	[LASTUPDATE] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DestinationQueueManager]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DestinationQueueManager](
	[Email] [nvarchar](255) NULL,
	[JobID] [nvarchar](255) NULL,
	[FileName] [nvarchar](255) NULL,
	[IsReady] [int] NULL,
	[StatusMSG] [nvarchar](255) NULL,
	[ReturnCode] [nvarchar](255) NULL,
	[TryCount] [int] NULL,
	[CREATETIME] [datetime] NULL,
	[LASTUPDATE] [datetime] NULL,
	[ScheduleTime] [datetime] NULL,
	[pdfuniqid] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[EmailInfo]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmailInfo](
	[EmailID] [varchar](64) NOT NULL,
	[JobID] [varchar](32) NULL,
	[SentMessageID] [varchar](1024) NULL,
	[FileName] [varchar](255) NULL,
	[CTEMetaData]  NULL,
	[StatusCode] [int] NOT NULL,
	[StatusMSG]  NULL,
	[TryCount] [int] NULL,
	[ScheduleTime] [datetime] NULL,
	[SENDTIME] [datetime] NULL,
	[CREATETIME] [datetime] NOT NULL,
	[LASTUPDATE] [datetime] NOT NULL,
 CONSTRAINT [PK_EmailInfo] PRIMARY KEY CLUSTERED 
(
	[EmailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[EmailProcessNotification]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmailProcessNotification](
	[EmailID] [varchar](64) NOT NULL,
	[NotificationCode] [int] NOT NULL,
	[NotificationLevel] [varchar](32) NULL,
	[NotificationMSG]  NULL,
	[CREATETIME] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[EventLog]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EventLog](
	[Number] [int] IDENTITY(1,1) NOT NULL,
	[Severity] [int] NULL,
	[Time] [datetime] NULL,
	[Message]  NULL,
	[ModuleName] [varchar](255) NULL,
	[FileName] [varchar](255) NULL,
	[LineNumber] [int] NULL,
	[IPAddress] [varchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[HTMLMetadata]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HTMLMetadata](
	[JobId] [int] NULL,
	[GUID] [varchar](255) NOT NULL,
	[PSW] [varchar](255) NULL,
	[LoginCount] [int] NULL,
	[FailCount] [int] NULL,
	[StatusCode] [int] NULL,
	[SLA] [varchar](8000) NULL,
	[PackageId] [varchar](255) NULL,
	[IndexInPackage] [int] NULL,
	[FormTitle] [varchar](255) NULL,
	[RequestId] [varchar](255) NULL,
	[Is2FA] [int] NOT NULL,
	[IsNeedValidate] [int] NOT NULL,
	[Phone] [varchar](255) NULL,
	[FourDigits] [varchar](255) NULL,
	[CODETIME] [datetime] NULL,
	[SUSPENSIONTIME] [datetime] NULL,
	[RequestsCount] [int] NULL,
	[PBParameter] [varchar](255) NULL,
	[UserDef1] [varchar](255) NULL,
	[UserDef2] [varchar](255) NULL,
	[UserDef3] [varchar](255) NULL,
	[UserDef4] [varchar](255) NULL,
	[UserDef5] [varchar](255) NULL,
	[UserDef6] [varchar](255) NULL,
	[UserDef7] [varchar](255) NULL,
	[UserDef8] [varchar](255) NULL,
	[UserDef9] [varchar](255) NULL,
	[UserDef10] [varchar](255) NULL,
	[UserDef11] [datetime] NULL,
	[UserDef12] [datetime] NULL,
	[CREATETIME] [datetime] NULL,
	[LASTUPDATE] [datetime] NULL,
	[UserDef13] [varchar](100) NULL,
	[PackageType] [varchar](255) NULL,
	[ORIGINALJOBDATA] [varchar](max) NULL,
	[SavedJson]  NULL,
 CONSTRAINT [PK_HTMLMetadata] PRIMARY KEY CLUSTERED 
(
	[GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[JOBS]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JOBS](
	[JobId] [int] NULL,
	[PrintBOSServerIP] [varchar](255) NULL,
	[RequestId] [int] NULL,
	[JobState] [int] NULL,
	[OutputReady] [int] NULL,
	[QueueName] [varchar](255) NULL,
	[UserName] [varchar](255) NULL,
	[JobName] [varchar](255) NULL,
	[JobFileName] [varchar](255) NULL,
	[JobSize] [float] NULL,
	[StatusCode] [varchar](255) NULL,
	[StatusMessage] [varchar](255) NULL,
	[CREATETIME] [datetime] NULL,
	[LASTUPDATE] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[OutputInfoItems]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OutputInfoItems](
	[JobId] [int] NULL,
	[OutputId] [int] NULL,
	[ParamName] [nvarchar](255) NULL,
	[ParamValue] [nvarchar](255) NULL,
	[CREATETIME] [datetime] NULL,
	[LASTUPDATE] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Outputs]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Outputs](
	[JobId] [int] NULL,
	[RequestId] [int] NULL,
	[OutputId] [int] NULL,
	[OutputFilePath] [nvarchar](255) NULL,
	[OutputFormat] [nvarchar](255) NULL,
	[StatusCode] [nvarchar](255) NULL,
	[StatusMessage] [nvarchar](255) NULL,
	[TypeOfTransmission] [nvarchar](255) NULL,
	[CompressedContent] [int] NULL,
	[ExcludeFromResponse] [int] NULL,
	[OutputType] [int] NULL,
	[TotalPages] [int] NULL,
	[CREATETIME] [datetime] NULL,
	[LASTUPDATE] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RenderDetails]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RenderDetails](
	[JobId] [int] NULL,
	[OutputId] [int] NULL,
	[StatusCode] [varchar](255) NULL,
	[StatusSeverity] [int] NULL,
	[StatusMessage]  NULL,
	[TemplateName] [varchar](255) NULL,
	[CREATETIME] [datetime] NULL,
	[LASTUPDATE] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[streets]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[streets](
	[CityId] [varchar](64) NOT NULL,
	[StreetName] [varchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TemplateInfo]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TemplateInfo](
	[TemplateFileName] [nvarchar](255) NULL,
	[TemplateType] [int] NULL,
	[SectionName] [nvarchar](255) NULL,
	[ParamName] [nvarchar](255) NULL,
	[Value] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TemplatesCatalog]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TemplatesCatalog](
	[TemplateID] [int] NOT NULL,
	[TemplateName] [nchar](256) NOT NULL,
	[TemplateData] [nvarchar](max) NULL,
	[TemplateAdditionalData] [nvarchar](max) NULL,
	[IsInCatalog] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TraceLog]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TraceLog](
	[Severity] [int] NULL,
	[Time] [datetime] NULL,
	[Message]  NULL,
	[ApplicationName] [varchar](255) NULL,
	[ComponentName] [varchar](255) NULL,
	[Job] [int] NULL,
	[Template] [varchar](255) NULL,
	[IPAddress] [varchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ValidateFormData]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ValidateFormData](
	[GUID] [varchar](255) NOT NULL,
	[JobID] [int] NULL,
	[MetaData]  NULL,
	[CREATETIME] [datetime] NULL,
	[LASTUPDATE] [datetime] NULL,
 CONSTRAINT [PK_ValidateMetaData] PRIMARY KEY CLUSTERED 
(
	[GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[AutoNumbers] ADD  CONSTRAINT [DF_AutoNumbers_JobId]  DEFAULT ((0)) FOR [JobId]
GO
ALTER TABLE [dbo].[AutoNumbers] ADD  DEFAULT ((0)) FOR [AdvJobId]
GO
ALTER TABLE [dbo].[AutoNumbers] ADD  DEFAULT ((0)) FOR [AdvPackageId]
GO
ALTER TABLE [dbo].[HTMLMetadata] ADD  DEFAULT ((1)) FOR [IsNeedValidate]
GO
/****** Object:  StoredProcedure [dbo].[UpdateWithSelect]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[UpdateWithSelect] @JobIDIn int
AS
BEGIN
      DECLARE @Num_of_Records INT
      DECLARE @PackageId INT
      DECLARE @NumOfDocs INT
	  DECLARE  @result INT


      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
      BEGIN TRANSACTION;

	  EXEC @result = sp_getapplock @Resource = 'UpdateWithSelect', @LockMode = 'Exclusive', @DbPrincipal = 'dbo';
      
        BEGIN TRY
			SELECT @PackageId = PackageId FROM [PrintBOS].[dbo].[PBDIGITAL_PRINTS] where JobId=@JobIDIn
			SELECT @NumOfDocs = NumOfDocs FROM [PrintBOS].[dbo].[PBDIGITAL_PRINTS] where JobId=@JobIDIn
                                
			SELECT @Num_of_Records = COUNT (JobId) FROM [PrintBOS].[dbo].[PBDIGITAL_PRINTS] WHERE PackageId=@PackageId AND PrintStatus = 1 
                                
                IF @Num_of_Records = @NumOfDocs
                BEGIN
				UPDATE [PrintBOS].[dbo].[PBDIGITAL_PRINTS] SET PrintStatus=2, LASTUPDATE=GETDATE() WHERE PackageId=@PackageId AND PrintStatus = 1
	   			SELECT * FROM [PrintBOS].[dbo].[PBDIGITAL_PRINTS] WHERE PackageId=@PackageId AND PrintStatus = 2 ORDER BY DocIndex ASC 
				END
						
          END TRY
        BEGIN CATCH
            
        END CATCH   

		EXEC @result = sp_releaseapplock @Resource = 'UpdateWithSelect';
		COMMIT TRANSACTION
END


GO
/****** Object:  StoredProcedure [dbo].[UpdateWithSelectForPackage]    Script Date: 26/11/2024 11:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





CREATE PROCEDURE [dbo].[UpdateWithSelectForPackage] @PackageIDIn int, @TimeToWaitForAllPackage int
AS
BEGIN
      DECLARE @Num_of_Records INT
      DECLARE @NumOfDocs INT
	  DECLARE  @result INT
	  DECLARE  @NumberOfSeconds INT
	  DECLARE  @Num_of_Records_Finished_In_Last_X_Seconds INT

      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
      BEGIN TRANSACTION;

	  SET @NumberOfSeconds = -@TimeToWaitForAllPackage

	  EXEC @result = sp_getapplock @Resource = 'UpdateWithSelectForPackage', @LockMode = 'Exclusive', @DbPrincipal = 'dbo'

      
        BEGIN TRY
	--		SELECT @PackageId = PackageId FROM [PrintBOS].[dbo].[PBDIGITAL_PRINTS] where JobId=@JobIDIn
			SELECT @NumOfDocs = NumOfDocs FROM [PrintBOS].[dbo].[PBDIGITAL_PRINTS] where PackageId=@PackageIDIn
            
			SELECT @Num_of_Records = COUNT (JobId) FROM [PrintBOS].[dbo].[PBDIGITAL_PRINTS] WHERE PackageId=@PackageIDIn AND PrintStatus = 1 AND (DATEADD(SECOND,-5,GETDATE()) > LASTUPDATE)
                                
                IF @Num_of_Records = @NumOfDocs
                BEGIN
				UPDATE [PrintBOS].[dbo].[PBDIGITAL_PRINTS] SET PrintStatus=2, LASTUPDATE=GETDATE() WHERE PackageId=@PackageIDIn AND PrintStatus = 1
	   			SELECT * FROM [PrintBOS].[dbo].[PBDIGITAL_PRINTS] WHERE PackageId=@PackageIDIn AND PrintStatus = 2 ORDER BY DocIndex ASC 
				END

			SELECT @Num_of_Records_Finished_In_Last_X_Seconds = COUNT (JobId) FROM [PrintBOS].[dbo].[PBDIGITAL_PRINTS] WHERE PackageId=@PackageIDIn AND PrintStatus = 1 AND (DATEADD(SECOND,@NumberOfSeconds,GETDATE()) < LASTUPDATE)

                IF @Num_of_Records < @NumOfDocs AND @Num_of_Records > 0 AND @Num_of_Records_Finished_In_Last_X_Seconds = 0 
                BEGIN
				UPDATE [PrintBOS].[dbo].[PBDIGITAL_PRINTS] SET PrintStatus=2, LASTUPDATE=GETDATE() WHERE PackageId=@PackageIDIn AND PrintStatus = 1
	   			SELECT * FROM [PrintBOS].[dbo].[PBDIGITAL_PRINTS] WHERE PackageId=@PackageIDIn AND PrintStatus = 2 ORDER BY DocIndex ASC 
				END
				
				
						
          END TRY
        BEGIN CATCH
            
        END CATCH   

		EXEC @result = sp_releaseapplock @Resource = 'UpdateWithSelectForPackage'
		COMMIT TRANSACTION
END



GO

Ensure you execute the script in the correct database context (e.g., PrintBOS).

Step 3: Verify Table Creation

After running the script:

  • Check the database in SSMS to ensure the tables are created.
  • Validate that the table structure matches the application requirements.

3. SQL Script Details

Below is the full SQL script required to create the missing tables:


USE [PrintBOS]
GO
/****** Object:  Table [dbo].[AdvancedJobs]    Script Date: 26/11/2024 11:02:29 ******/
CREATE TABLE [dbo].[AdvancedJobs](
    [AdvJobId] [int] NULL,
    [AdvPackageId] [int] NULL,
    ...
);
-- Additional table creation scripts
CREATE TABLE [dbo].[AutoNumbers](
    [JobId] [int] NULL,
    [AdvJobId] [int] NULL,
    ...
);
-- Continue with other table definitions

4. Expected Outcome

Once the script has been successfully executed:

  • The missing tables will be created in the PrintBOS database.
  • The application will be able to access and utilize these tables as expected.

5. Additional Notes

  • Ensure that the database schema version aligns with the application version.
  • Back up the database before executing any scripts to avoid potential data loss.
  • Contact your system administrator if you encounter any errors during script execution.
תוכן עיניינים