A guide where you can find the answers to our clients' most frequently asked questions about MSSQL Databases
A Stored Procedure is a Database procedure that can carry out complex tasks within the Database. It is a program in SQL or other languages derived from SQL
, for example ASP, ASP.NET or Visual Basic.
epending on the features, it might be broken down into:
- Functions (these accept incoming/outgoing parameters and return a single value)
- Procedures (these accept incoming/outgoing parameters but do not return any value);
- Triggers (which are activated events)
Using Stored Procedures means that:
- The Client can avoid the need to rewrite complex Queries. The procedure is compiled and archived in the Database itself, making sure that performance improves and avoiding the need to exchange an excessive amount of information between the Client and Server.
- You can carry out complex tasks that would not be possible if you were only using SQL queries, as they are written in proprietary SQL versions, which are actual structural languages.
- You can keep libraries of commonly used functions in the database itself.
- You can carry out tasks requiring a different procedure without knowing the structure of a Database or with limited knowledge (this gives the administrator of a Database the advantage of only granting permission for Stored Procedures, avoiding the need to assign edit and/or read permissions).
This saves a considerable amount of network resources, directly in proportion to the amount of code contained in the Stored Procedures and the frequency with which they are invoked.
If you get the following error when executing a query on MSSQL
Error -2147217900 Specified owner name 'dbo' either does not exist or you do not have permission to use it
The owner for all the objects that users create must be the same username as the one associated with the MSSQL service: it cannot be dbo, as users do not have the same permissions as the db owner, this is only possible on their own dedicated server.
So when executing an instruction like this:
CREATE TABLE [dbo].[tbl_nometabella] ( [col_id] [int] IDENTITY (1, 1) NOT NULL , [col_user] [nvarchar] (20) NOT NULL , [col_cat] [int] NULL , [col_sub] [int] NULL , [col_state] [int] NULL , [col_city] [nvarchar] (50) NULL , [col_key] [nvarchar] (100) NULL , [col_type] [nvarchar] (1) NULL , [col_both] [nvarchar] (1) NULL , [col_min] [money] NULL , [col_max] [money] NULL ,) ON [PRIMARY]GO
in CREATE TABLE [dbo].[tbl_nometabella] you will need to replace
the dbo user with your own username for the MSSQL service, for example MSSQL10059 and so
CREATE TABLE [MSSQL10059].[tbl_nometabella]
, direct connection between your PC and the MSSQL Database is not possible using local Database Managers like Enterprise Manager
. To access the Database, you need to use the link at mssql.aruba.it
or possible Tools via scripting from your Domain hosted by Aruba.
If you encounter any problems executing scripts provided by third parties to use free software with the mssql.aruba.it control panel, you need to make sure you have followed the instructions described below, which will help avoid problems for the majority of scripts and when managing permissions that Aruba uses for clients who purchase the MSSQL Server service.
Description of the different parts of a script (usually provided as a file with a .sql extension)
A script is usually made up of the following:
- The first part is usually made up of a series of instructions to delete the tables that need to be created if they already exist.
Care must be taken before executing this part because if the names of the tables to be created are the same as those of tables that have already been created, even if they have a different function, data might be lost 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] GO
The owner will specifically be replaced with the SQL username provided by Aruba when the service was purchased. The user [dbo] becomes e.g. [MSSQL10059].
The select * from dbo.sysobjects where id = object_id command must remain unchanged as the system objects according to how the MSSQL Server functions must have select access at public level (guest). You must never change the owner of the system objects [dbo], recognized by the suffix sys.... (sysobjects) as this will generate the following error:
Invalid object name 'MSSQL10059.sysobjects'
Therefore you must only change dbo for objects that are to be part of the same database
- The next part is about creating tables, indices, external keys, stored procedures etc.
This part is therefore usually made up of CREATE and ALTER scripts, 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]
Here, you must use the SQL User owner, communicated by Aruba at the time of activation, otherwise execution of the different commands will fail.
- The last part is made up of insert, update queries etc... to launch the different tables with data, e.g.:
INSERT INTO [MSSQL10059].[CodeCountry] ([Code], [Description]) VALUES ('SH', 'St. Helena')
INSERT INTO [MSSQL10059].[CodeCountry] ([Code], [Description]) VALUES ('SI', 'Slovenia')
Here too, as is the case for the code of all the applications for any SQL or transact SQL command executed on objects in your db, you need to use the SQL user instead of [dbo], otherwise the following error will occur:
Invalid object name 'dbo.CodeCountry'
Importance of the row limit within a script:
Given the web interface and the language used by the mssql.aruba.it application, in order that the SQL script can be executed correctly, you need to limit the maximum number of lines within a script to no more than 7000 7500.
We therefore recommend subdividing the individual SQL file into several files of approximately 7000 7500 lines.