[MS SQLS2005] [script]

**vincent magnin** · 16/02/2007, 13h38

Bonjour, je vous fait part d'un problème que je rencontre actuellement sur mon serveur SQL 2005.
Je dois en effet mettre en place une architecture réseau fonctionnant sur le logiciel d'infogérance/télédistribution Vision64.

Ce logiciel demande donc l'accès à une base de données, et au préalable cette base doit être créée sur notre SGBD favori, pour ce faire un script est donné.

Or, le problème est que Vision64 n'est pas des plus contemporains et que le script fourni par l'éditeur est fait pour SQL Server 2000. Donc lorque je lance le batch il me dit "isql" n'est pas reconnu en tant que commande interne. Après m'être quelque peu renseigné (je n'y connait absolument rien en scripting SQL Server :s) j'ai appris que "sqlcmd" était devenu norme.

J'aimerais donc savoir s'il était possible de traduire ce script pour SQL server 2000, afin qu'il soit utilisable sur SQL server 2005

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
@echo off
echo Installation of VISION64 tables
echo.
 
if (%5)==() goto Param_Error
 
set HOST=%1
set SYST_PASSWD=%2
set DB_NAME=%3
set DB_USER=vision64
set DB_PASSWD=vision64
set DB_COLLATE=%4
set DB_PATH=%5
 
if not "%6" == "" set DB_USER=%6
if not "%7" == "" set DB_PASSWD=%7
 
set SQLQUERY="IF EXISTS(SELECT name FROM sysdatabases WHERE name = '%DB_NAME%')
set SQLQUERY=%SQLQUERY% BEGIN
set SQLQUERY=%SQLQUERY%   RAISERROR ('%DB_NAME% data base already exists.', 16, 1)
set SQLQUERY=%SQLQUERY% END
set SQLQUERY=%SQLQUERY% ELSE IF EXISTS(SELECT name FROM syslogins WHERE name = '%DB_USER%')
set SQLQUERY=%SQLQUERY% BEGIN
set SQLQUERY=%SQLQUERY%   RAISERROR ('%DB_USER% login already exists.', 16, 1)
set SQLQUERY=%SQLQUERY% END"
isql -U sa -P %SYST_PASSWD% -S %HOST% -d master -n -b -Q %SQLQUERY% -o db_or_user_presence.log
if ERRORLEVEL 1 goto Creation_Error
 
:Step1
echo.
echo Creation of '%DB_NAME%' database. Please wait...
 
 
 
 
set SQLQUERY="CREATE DATABASE %DB_NAME% ON PRIMARY
set SQLQUERY=%SQLQUERY% ( NAME = V64_DB, FILENAME = '%DB_PATH%\V64_db.mdf', SIZE = 16, MAXSIZE = UNLIMITED, FILEGROWTH = 16 ),
set SQLQUERY=%SQLQUERY% FILEGROUP V64_DATA ( NAME = V64_data1, FILENAME = '%DB_PATH%\V64_data1.mdf', SIZE = 128, MAXSIZE = UNLIMITED, FILEGROWTH = 32 ), ( NAME = V64_data2, FILENAME = '%DB_PATH%\V64_data2.mdf', SIZE = 128, MAXSIZE = UNLIMITED, FILEGROWTH = 32 ), 
set SQLQUERY=%SQLQUERY% FILEGROUP V64_INDEX ( NAME = V64_index1, FILENAME = '%DB_PATH%\V64_index1.mdf', SIZE = 64, MAXSIZE = UNLIMITED, FILEGROWTH = 32 ), ( NAME = V64_index2, FILENAME = '%DB_PATH%\V64_index2.mdf', SIZE = 64, MAXSIZE = UNLIMITED, FILEGROWTH = 32 ),
set SQLQUERY=%SQLQUERY% FILEGROUP V64_IMAGE_TREE ( NAME = V64_image_tree1, FILENAME = '%DB_PATH%\V64_image_tree1.mdf', SIZE = 16, MAXSIZE = UNLIMITED, FILEGROWTH = 64 )
set SQLQUERY=%SQLQUERY% COLLATE %DB_COLLATE%"
 
 
isql -U sa -P %SYST_PASSWD% -S %HOST% -d master -n  -Q %SQLQUERY%  -o create_db_part1.log
 
 
set SQLQUERY="ALTER DATABASE %DB_NAME% ADD FILEGROUP V64_IMAGE_ATTR 
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILE ( NAME = V64_image_attr1, FILENAME = '%DB_PATH%\V64_image_attr1.mdf', SIZE = 16, MAXSIZE = UNLIMITED, FILEGROWTH = 32) TO FILEGROUP V64_IMAGE_ATTR
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILEGROUP V64_IMAGE_DMI
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILE ( NAME = V64_image_dmi1, FILENAME = '%DB_PATH%\V64_image_dmi1.mdf', SIZE = 64, MAXSIZE = UNLIMITED, FILEGROWTH = 16 ) TO FILEGROUP V64_IMAGE_DMI
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILEGROUP V64_IMAGE_DIAG
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILE ( NAME = V64_image_diag1, FILENAME = '%DB_PATH%\V64_image_diag1.mdf', SIZE = 32, MAXSIZE = UNLIMITED, FILEGROWTH = 8 ) TO FILEGROUP V64_IMAGE_DIAG"
 
 
isql -U sa -P %SYST_PASSWD% -S %HOST% -d master -n  -Q %SQLQUERY%  -o create_db_part2.log
 
 
set SQLQUERY="ALTER DATABASE %DB_NAME% ADD FILEGROUP V64_IMAGE_SOFT
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILE ( NAME = V64_image_soft1, FILENAME = '%DB_PATH%\V64_image_soft1.mdf', SIZE = 2, MAXSIZE = UNLIMITED, FILEGROWTH = 1 ) TO FILEGROUP V64_IMAGE_SOFT
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILEGROUP V64_IMAGE_SNMP
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILE ( NAME = V64_image_snmp1, FILENAME = '%DB_PATH%\V64_image_snmp1.mdf', SIZE = 16, MAXSIZE = UNLIMITED, FILEGROWTH = 32 ) TO FILEGROUP V64_IMAGE_SNMP
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILEGROUP V64_IMAGE_CONF
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILE ( NAME = V64_image_conf1, FILENAME = '%DB_PATH%\V64_image_conf1.mdf', SIZE = 2, MAXSIZE = UNLIMITED, FILEGROWTH = 1 ) TO FILEGROUP V64_IMAGE_CONF"
 
 
 
isql -U sa -P %SYST_PASSWD% -S %HOST% -d master -n  -Q %SQLQUERY%  -o create_db_part3.log
 
 
set SQLQUERY="ALTER DATABASE %DB_NAME% ADD FILEGROUP V64_IMAGE_LDAP
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILE ( NAME = V64_image_ldap1, FILENAME = '%DB_PATH%\V64_image_ldap1.mdf', SIZE = 2, MAXSIZE = UNLIMITED, FILEGROWTH = 1 ) TO FILEGROUP V64_IMAGE_LDAP
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILEGROUP V64_IMAGE_LOG
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILE ( NAME = V64_image_log1, FILENAME = '%DB_PATH%\V64_image_log1.mdf', SIZE = 16, MAXSIZE = UNLIMITED, FILEGROWTH = 4 ) TO FILEGROUP V64_IMAGE_LOG
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILEGROUP V64_IMAGE_SCRIPT
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILE ( NAME = V64_image_script1, FILENAME = '%DB_PATH%\V64_image_script1.mdf', SIZE = 16, MAXSIZE = UNLIMITED, FILEGROWTH = 4 ) TO FILEGROUP V64_IMAGE_SCRIPT"
 
 
isql -U sa -P %SYST_PASSWD% -S %HOST% -d master -n  -Q %SQLQUERY%  -o create_db_part4.log
 
 
set SQLQUERY="ALTER DATABASE %DB_NAME% ADD FILEGROUP V64_IMAGE_TALLY
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILE ( NAME = V64_image_tally1, FILENAME = '%DB_PATH%\V64_image_tally1.mdf', SIZE = 16, MAXSIZE = UNLIMITED, FILEGROWTH = 4 ) TO FILEGROUP V64_IMAGE_TALLY
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILEGROUP V64_IMAGE_SYSF
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILE ( NAME = V64_image_sysf1, FILENAME = '%DB_PATH%\V64_image_sysf1.mdf', SIZE = 2, MAXSIZE = UNLIMITED, FILEGROWTH = 1 ) TO FILEGROUP V64_IMAGE_SYSF
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILEGROUP V64_IMAGE
set SQLQUERY=%SQLQUERY% ALTER DATABASE %DB_NAME% ADD FILE ( NAME = V64_image1, FILENAME = '%DB_PATH%\V64_image1.mdf', SIZE = 64, MAXSIZE = UNLIMITED, FILEGROWTH = 8 ) TO FILEGROUP V64_IMAGE"
 
isql -U sa -P %SYST_PASSWD% -S %HOST% -d master -n  -Q %SQLQUERY%  -o create_db_part5.log
 
 
:Step2
echo.
echo Creation of '%DB_USER%' login. Please wait...
 
set SQLQUERY="EXECUTE sp_addlogin '%DB_USER%','%DB_PASSWD%', @defdb = %DB_NAME%
set SQLQUERY=%SQLQUERY% EXECUTE sp_grantdbaccess '%DB_USER%'
set SQLQUERY=%SQLQUERY% EXECUTE sp_addrolemember 'db_owner','%DB_USER%'"
isql -U sa -P %SYST_PASSWD% -S %HOST% -d %DB_NAME% -n -Q %SQLQUERY% -o user_creation.log
 
 
:Step3
echo.
echo Creation of '%DB_NAME%' database tables. Please wait...
 
isql -U %DB_USER% -P %DB_PASSWD% -S %HOST% -n -i schema_sqlserver.sql -o schema_sqlserver.log
goto Done
 
:Creation_Error
echo '%DB_NAME%' database creation failed. See occured errors in the db_or_user_presence.log file...
echo.
goto Done
 
:Param_Error
echo Error, the following parameters are missing:
if "%1" == "" @echo [PARAMETER 1]=[Sql Server Hostname (or IP address)]
if "%2" == "" @echo [PARAMETER 2]=[System Password]
if "%3" == "" @echo [PARAMETER 3]=[Data Base Name]
if "%6" == "" @echo [PARAMETER 6]=[Data Base Collation Type]
if "%6" == "" @echo [PARAMETER 6]=[Groupfile path]
if "%4" == "" @echo [PARAMETER 4]=[Connection name]
if "%5" == "" @echo [PARAMETER 5]=[Connection Password]
echo.
 
set USER_ARGS=
:Loop
if (%1)==() goto Parsed
set USER_ARGS=%USER_ARGS% %1
shift
goto Loop
:Parsed
echo Read parameters : %USER_ARGS%
echo.
goto Done
 
:Done
pause
exit