Config Info
Name Product Database Creation
Version 1.0

Database creation

About

These scripts can be run on the database instance to automate the creation of login and databases. It sets some important parameters on the Database :

  • Recovery Model : Simple
  • Autoshrink : False
  • Location for the files : must not be on C:
  • permissions for the user : dbowner for the tango login

The following parameters should be updated depending on the customer's configuration :

  • login user and password for Tango/04 databases
  • the path to the folder where the files should be stored

SQL Server 2005/2008

This script has been tested and works in SQL Server 2005 and 2008.

/*------Tango/04--------- Script for creation of product databases Last modified 2010/03/04*/ --DATABASES TO CREATE - comma-separated DECLARE @T04_DATABASES NVARCHAR(1000) SELECT @T04_DATABASES= 'Events,SmartConsole,Hist_Events,Dashboards,Reports,WebSmartConsole,AccessServer' --SELECT @T04_DATABASES= 'DB1,DB2,DB3' --for testing --PATH WHERE THE DATABASES WILL BE CREATED --should not be on disk C: DECLARE @T04_DB_FOLDER NVARCHAR(1000) SELECT @T04_DB_FOLDER = 'E:\TangoDB\' --LOGIN THAT WILL BE USED DECLARE @T04_USER NVARCHAR(255) SELECT @T04_USER = 'tango' DECLARE @T04_USER_PASSWORD NVARCHAR(255) SELECT @T04_USER_PASSWORD = 'tango' --Variable declarations DECLARE @CURRENT_DB NVARCHAR(255) DECLARE @DB_NAME NVARCHAR(255) DECLARE @SUBFOLDER NVARCHAR(255) DECLARE @FILENAME NVARCHAR(255) DECLARE @LOGFILENAME NVARCHAR(255) DECLARE @QUERY NVARCHAR(4000) --Create the login PRINT '-Creating login ' + @T04_USER SELECT @QUERY = 'CREATE LOGIN ' + @T04_USER + ' WITH PASSWORD=N''' + @T04_USER_PASSWORD + ''', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF' PRINT @QUERY EXEC (@QUERY) Print 'Done' --Loop for each database WHILE LEN( @T04_DATABASES ) > 0 BEGIN IF CHARINDEX( ',', @T04_DATABASES ) > 0 SELECT @CURRENT_DB = LEFT( @T04_DATABASES, CHARINDEX( ',', @T04_DATABASES ) - 1 ) , @T04_DATABASES = RIGHT( @T04_DATABASES, LEN( @T04_DATABASES ) - CHARINDEX( ',', @T04_DATABASES ) ) ELSE SELECT @CURRENT_DB = @T04_DATABASES, @T04_DATABASES = SPACE(0) --do something for each database PRINT '-Working WITH DB ' + @CURRENT_DB SELECT @DB_NAME = 'T04_' + @CURRENT_DB --SELECT @SUBFOLDER = @T04_DB_FOLDER + @CURRENT_DB + '\' SELECT @SUBFOLDER = @T04_DB_FOLDER SELECT @FILENAME = @SUBFOLDER + @DB_NAME + '_Data.mdf' SELECT @LOGFILENAME = @SUBFOLDER + @DB_NAME + '_Log.ldf' --create database PRINT '--Creating DB ' + @DB_NAME SELECT @QUERY = 'CREATE DATABASE ' + @DB_NAME + ' ON (NAME = ' + @DB_NAME + '_dat, FILENAME=''' + @FILENAME +''' ) LOG ON (NAME = ' + @DB_NAME + '_Log, FILENAME=''' + @LOGFILENAME + ''' )' PRINT @QUERY EXEC (@QUERY) PRINT '--Done' /*Set user 'Tango04' as DB Owner*/ PRINT '--Setting user as dbowner ' + @T04_USER SELECT @QUERY = 'USE ' + @DB_NAME + ' EXEC sp_changedbowner ''' + @T04_USER + ''' ' PRINT @QUERY EXEC (@QUERY) PRINT '--Done' /*Set Auto_Shrink to false and Recovery Model to Simple*/ PRINT '--Setting Recovery model to Simple and autoshrink to false ' SELECT @QUERY = 'ALTER DATABASE ' + @DB_NAME + ' set AUTO_SHRINK OFF, RECOVERY SIMPLE' PRINT @QUERY EXEC (@QUERY) PRINT '--Done' PRINT '-Finished Working with DB ' + @CURRENT_DB END PRINT 'Finished. Please review the messages and check for errors' GO 

 

ODBC DSN Creation

About

The ODBC DSNs of a computer are stored in the Windows Registry. As a consequence, the creation of the DSNs can be automated by importing a registry exportaion file (.reg).

The file contains information about:

  • ODBC driver to use (for example SQL Native Client)
  • the dll of the driver (for example C:\\WINDOWS\\system32\\sqlncli.dll)
  • the database server that the DSNs points to
  • the name of the database the DSN points to
  • the user that we use to test the connection

In order to generate the DSNs, follow these steps :

  1. check that you use the correct ODBC drivers
  2. replace TDESODT with the correct name of the SQL Server instance (Note that it can be something like SERVERNAME\INSTANCENAME, and in Registry syntax, anti-slashes bust be escaped... Therefore, it should appear as SERVERNAME\\INSTANCENAME)
  3. rename the file to xxx.reg
  4. double-click the file to launch the importation
  5. check that the DSNs were properly created

SQL Server 2005 - Native Client

SQL Server Native Client is the ODBC Driver that is usually used to connect to SQL Server 2005.

Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources] "T04_Events"="SQL Native Client" "T04_SmartConsole"="SQL Native Client" "T04_Hist_Events"="SQL Native Client" "T04_Dashboards"="SQL Native Client" "T04_Reports"="SQL Native Client" "T04_WebSmartConsole"="SQL Native Client" "T04_AccessServer"="SQL Native Client" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_Events] "Driver"="C:\\WINDOWS\\system32\\sqlncli.dll" "Server"="TDESODT" "Database"="T04_Events" "LastUser"="tango" "Description"="Tango04 ThinkServer Events" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_SmartConsole] "Driver"="C:\\WINDOWS\\system32\\sqlncli.dll" "Server"="TDESODT" "Database"="T04_SmartConsole" "LastUser"="tango" "Description"="Tango04 SmartConsole configuration" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_Hist_Events] "Driver"="C:\\WINDOWS\\system32\\sqlncli.dll" "Server"="TDESODT" "Database"="T04_Hist_Events" "LastUser"="tango" "Description"="Tango04 ThinkServer historical Events" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_Dashboards] "Driver"="C:\\WINDOWS\\system32\\sqlncli.dll" "Server"="TDESODT" "Database"="T04_Dashboards" "LastUser"="tango" "Description"="Tango04 Dashboards statistical data" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_Reports] "Driver"="C:\\WINDOWS\\system32\\sqlncli.dll" "Server"="TDESODT" "Database"="T04_Reports" "LastUser"="tango" "Description"="Tango04 Reports configuration" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_WebSmartConsole] "Driver"="C:\\WINDOWS\\system32\\sqlncli.dll" "Server"="TDESODT" "Database"="T04_WebSmartConsole" "LastUser"="tango" "Description"="Tango04 WebSmartConsole configuration" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_AccessServer] "Driver"="C:\\WINDOWS\\system32\\sqlncli.dll" "Server"="TDESODT" "Database"="T04_AccessServer" "LastUser"="tango" "Description"="Tango04 AccessServer configuration" 

 

SQL Server 2008 - Native Client 10.0

SQL Server Native Client 10.0 is the ODBC Driver that is usually used to connect to SQL Server 2008.

Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources] "T04_Events"="SQL Server Native Client 10.0" "T04_SmartConsole"="SQL Server Native Client 10.0" "T04_Hist_Events"="SQL Server Native Client 10.0" "T04_Dashboards"="SQL Server Native Client 10.0" "T04_Reports"="SQL Server Native Client 10.0" "T04_WebSmartConsole"="SQL Server Native Client 10.0" "T04_AccessServer"="SQL Server Native Client 10.0" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_Events] "Driver"="C:\\WINDOWS\\system32\\sqlncli10.dll" "Server"="SERVERNAME\\INSTANCENAME" "Database"="T04_Events" "LastUser"="tango" "Description"="Tango04 ThinkServer Events" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_SmartConsole] "Driver"="C:\\WINDOWS\\system32\\sqlncli10.dll" "Server"="SERVERNAME\\INSTANCENAME" "Database"="T04_SmartConsole" "LastUser"="tango" "Description"="Tango04 SmartConsole configuration" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_Hist_Events] "Driver"="C:\\WINDOWS\\system32\\sqlncli10.dll" "Server"="SERVERNAME\\INSTANCENAME" "Database"="T04_Hist_Events" "LastUser"="tango" "Description"="Tango04 ThinkServer historical Events" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_Dashboards] "Driver"="C:\\WINDOWS\\system32\\sqlncli10.dll" "Server"="SERVERNAME\\INSTANCENAME" "Database"="T04_Dashboards" "LastUser"="tango" "Description"="Tango04 Dashboards statistical data" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_Reports] "Driver"="C:\\WINDOWS\\system32\\sqlncli10.dll" "Server"="SERVERNAME\\INSTANCENAME" "Database"="T04_Reports" "LastUser"="tango" "Description"="Tango04 Reports configuration" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_WebSmartConsole] "Driver"="C:\\WINDOWS\\system32\\sqlncli10.dll" "Server"="SERVERNAME\\INSTANCENAME" "Database"="T04_WebSmartConsole" "LastUser"="tango" "Description"="Tango04 WebSmartConsole configuration" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_AccessServer] "Driver"="C:\\WINDOWS\\system32\\sqlncli10.dll" "Server"="SERVERNAME\\INSTANCENAME" "Database"="T04_AccessServer" "LastUser"="tango" "Description"="Tango04 AccessServer configuration" 

 

Oracle - Native Client 10.0

Oracle en OraClient10g_home1 is the ODBC Driver that is usually used to connect to an Oracle database.

Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources] "T04_Events"="Oracle en OraClient10g_home1" "T04_SmartConsole"="Oracle en OraClient10g_home1" "T04_Hist_Events"="Oracle en OraClient10g_home1" "T04_Dashboards"="Oracle en OraClient10g_home1" "T04_Reports"="Oracle en OraClient10g_home1" "T04_WebSmartConsole"="Oracle en OraClient10g_home1" "T04_AccessServer"="Oracle en OraClient10g_home1" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_Events] "Driver"="C:\\oracle\\product\\10.2.0\\client_1\\BIN\\SQORA32.DLL" "Attributes"="W" "FetchBufferSize"="64000" "NumericSetting"="NLS" "ForceWCHAR"="F" "FailoverDelay"="10" "FailoverRetryCount"="10" "MetadataIdDefault"="F" "BindAsDATE"="F" "CloseCursor"="F" "EXECSchemaOpt"="" "EXECSyntax"="F" "Application Attributes"="T" "ResultSets"="T" "QueryTimeout"="T" "Failover"="T" "Lobs"="T" "DisableMTS"="T" "DisableDPM"="F" "BatchAutocommitMode"="IfAllSuccessful" "Description"="Monitorización Tango/04" "ServerName"="TNS_NAME_Events" "Password"="" "UserID"="tango04" "DSN"="T04_Events" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_SmartConsole] "Driver"="C:\\oracle\\product\\10.2.0\\client_1\\BIN\\SQORA32.DLL" "Attributes"="W" "FetchBufferSize"="64000" "NumericSetting"="NLS" "ForceWCHAR"="F" "FailoverDelay"="10" "FailoverRetryCount"="10" "MetadataIdDefault"="F" "BindAsDATE"="F" "CloseCursor"="F" "EXECSchemaOpt"="" "EXECSyntax"="F" "Application Attributes"="T" "ResultSets"="T" "QueryTimeout"="T" "Failover"="T" "Lobs"="T" "DisableMTS"="T" "DisableDPM"="F" "BatchAutocommitMode"="IfAllSuccessful" "Description"="Monitorización Tango/04" "ServerName"="TNS_NAME_SmartConsole" "Password"="" "UserID"="tango04" "DSN"="T04_SmartConsole" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_Hist_Events] "Driver"="C:\\oracle\\product\\10.2.0\\client_1\\BIN\\SQORA32.DLL" "Attributes"="W" "FetchBufferSize"="64000" "NumericSetting"="NLS" "ForceWCHAR"="F" "FailoverDelay"="10" "FailoverRetryCount"="10" "MetadataIdDefault"="F" "BindAsDATE"="F" "CloseCursor"="F" "EXECSchemaOpt"="" "EXECSyntax"="F" "Application Attributes"="T" "ResultSets"="T" "QueryTimeout"="T" "Failover"="T" "Lobs"="T" "DisableMTS"="T" "DisableDPM"="F" "BatchAutocommitMode"="IfAllSuccessful" "Description"="Monitorización Tango/04" "ServerName"="TNS_NAME_Hist_Events" "Password"="" "UserID"="tango04" "DSN"="T04_Hist_Events" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\T04_Dashboards] "Driver"="C:\\oracle\\product\\10.2.0\\client_1\\BIN\\SQORA32.DLL" "Attributes"="W" "FetchBufferSize"="64000" "NumericSetting"="NLS" "ForceWCHAR"="F" "FailoverDelay"="10" "FailoverRetryCount"="10" "MetadataIdDefault"="F" "BindAsDATE"="F" "CloseCursor"="F" "EXECSchemaOpt"="" "EXECSyntax"="F" "Application Attributes"="T" "ResultSets"="T" "QueryTimeout"="T" 

Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: October 22, 2018