The use of script provided by third party for the use of free software is becoming more and more widespread.
With regards to this a series of tips are described below which can save you time in case of any problems you may encounter while carrying them out with the use of the mssql.aruba.it panel.
In particular these suggestions avoid problems relating to script size and permission management which Aruba uses for the users who purchase the MS Sql Server service.
Description of the parts which form a script (usually provided as a file with .sql extension)
The parts which form a script are usually 3:
The first part usually consists of a series of instructions which are needed for the cancellation of the tables which you have to create if these already exist.
This is the part which if the database is empty can be omitted without any problems. In any case you need to pay attention before carrying out this part because if the table names to be created coincide with the tables already created, which perhaps have a different function, you risk loosing the data unnecessarily.
The code is described as follows (in the example the table to be created is called MenuDefinitions):
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MenuDefinitions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [<UTENTESQL>].[MenuDefinitions]
Please note that the owner, as described in the connected article, must be explicitly replaced with the sql user name which has been given to you by Aruba therefore the user [dbo] becomes for e.g. [MSSql10059].
Also note that the command select * from dbo.sysobjects where id = object_id must remain unvaried as the system objects obligatorily have an access in select at public level (guest), according to the functioning of Ms Sql Server.
Therefore you must never change the system objects owner [dbo] recognizable by the suffix sys.... (sysobjects) as the consequence is the error
Invalid object name ‘MSSql10059.sysobjects'
Therefore you need to change dbo only for the objects which must form part of your own database
The following part is relevant to the creation of tables , index, foreign key, stored procedure etc...
Therefore this part consists mainly of script such as CREATE and ALTER e.g.:
CREATE TABLE [MSSql10059].[ClickLog] (
[ClickLogId] [int] IDENTITY (1, 1) NOT NULL ,
[TableName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ItemId] [int] NOT NULL ,
[DateTime] [datetime] NOT NULL ,
[UserId] [int] NULL
) ON [PRIMARY]
ALTER TABLE [MSSql10059].[ClickLog] WITH NOCHECK ADD
CONSTRAINT [PK_ClickLog] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
In this case it is obligatory to use the sql owner user which Aruba communicates during the activation otherwise the several commands will fail. If in fact we leave the code unchanged with [dbo] we will obtain the error:
Specified owner name 'dbo' either does not exist or you do not have permission to use it.
The last part usually consists of insert query,update etc... which are used to initialize the various tables with data e.g.:
INSERT INTO [MSSql10059].[CodeCountry] ([Code], [Description]) VALUES ('SH', 'St. Helena')
INSERT INTO [MSSql10059].[CodeCountry] ([Code], [Description]) VALUES ('SI', 'Slovenia')
Even here, as in the code of all the applications for every sql or transact sql command carried out on objects of your own db, you will need to use the sql user instead of [dbo] otherwise you will encounter the error:
Invalid object name 'dbo.CodeCountry'
Importance of the limit of lines within a script:
Given the web interface and the language used by the application mssql.aruba.it you need to limit the maximum number of lines within a script to no more than 7000 7500 in order to carry out correctly the sql scripts.
With regard to this we advise you to subdivide the only sql file in files of about 7000 7500 lines.