Useful Microsoft SQL statements

 

In this Blog we cover some useful SQL (Structured Query Language) statements to use with an ARCHIBUS database, using the 

SQL Server Management Studio. You will either need to be logged on as the ‘afm’ user or the ‘sa’ or equivalent user and connected to your ARCHIBUS

database in a new query window.

 


  1. 1.    SQL version

To find the current version of SQL server you are using use the following statement:

 

SELECT serverproperty ('productversion') as 'Product Version', serverproperty ('productlevel') as 'Product Level’, serverproperty ('edition') as 'Edition'

 

 

 

 

 

This can be useful especially when restoring databases from different versions as it is not possible to restore a higher version SQL Server database backup file onto a lower version SQL Server.

 

2. Attaching an ARCHIBUS database

When you restore an ARCHIBUS database the users ‘afm’ and ‘afm_secure’ will also need to be restored:

sp_change_users_login 'Report'






You can use the following statements to restore these orphaned users to the database (you will need to be logged in with the ‘sa’ account or with similar privileges):

                                                  

sp_change_users_login 'Auto_Fix', 'afm'

 

sp_change_users_login 'Auto_Fix', 'afm_secure'

 

The row for user 'afm' will be fixed by updating its login link to a login already in existence.

The number of orphaned users fixed by updating users was 1.

The number of orphaned users fixed by adding new logins and then updating users was 0.

 

  1. 3.    Create a copy of a table

 

To backup an existing table and all its data you can use the following statement:

 

SELECT * into bl_temp from bl

 

  1. 4.    Date and Time

 

Date and time values are stored separately within an ARCHIBUS database even though the field type allows both a date and time to be stored in the same field:

 

SELECT getdate() as datetime

 

2019-08-19 10:55:30.440

 

A date field stored in ARCHIBUS has no time value and a time field requires the date part to be set as ‘1899-12-30’. This is so filtering or searches within the ARCHIBUS grid work correctly.

 

To return just the date part:

 

SELECT convert(char(10),getdate(),120) as date

 

2019-08-19

 

To return the date formatted:

 

SELECT convert(char(11),getdate(),106) as datemonth

 

19 Aug 2019

 

To return just the time:

 

SELECT convert(char(8),getdate(),114) as time

 

11:00:15

 

To return the time in a format acceptable to ARCHIBUS:

 

SELECT '1899-12-30 '+convert(char(8),getdate(),114)+'.000' as time1899

 

1899-12-30 11:00:15.000

 

 

  1. 5.    Database statistics

To find the number of records within each table in order of the greatest first, use the following statement:

 

 

 

SELECT sysobjects.name,sysindexes.rows

FROM sysobjects

RIGHT OUTER JOIN sysindexes ON sysobjects.id = sysindexes.id

WHERE sysobjects.xtype='U' and sysindexes.indid < 2

ORDER BY sysindexes.rows DESC, sysobjects.name

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  1. 6.    Returning the row number

 

 

 

The following statement will return the row number in the query:

 

SELECT ROW_NUMBER() OVER(ORDER BY bl_id) ROW_NUM,bl_id FROM bl

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To restrict to a specific row number, use the following query: 

 

SELECT * from

     (

     SELECT *, ROW_NUMBER() OVER(ORDER BY bl_id) ROW_NUM

     FROM (SELECT bl_id FROM bl) a

     )d

WHERE ROW_NUM BETWEEN 1 and 10

 

 

 

Keep your eyes peeled for next weeks weekly Wednesday blog! If you are interested in this topic and would like to chat to MASS all about it, we are available on 0118 977 8560 or email us at news@mass-plc.com.


Andrew Taylor 


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 



BACK TO BLOG


Computerised Maintenance Management System (CMMS) 11/09/2019

Hello there, Today’s topic is CMMS (or CMMIS, with the I standing for Information), which remains as one of Archibus’ key functional areas of business operations. Within this blog we will...
read more view all blog posts

GET IN TOUCH