Конфликт инструкции INSERT с ограничением FOREIGN KEY "FK_Login_UserTypes". Конфликт произошел в базе данных "master", таблица "dbo.UserTypes", column 'UserTypeID'.
SET DATEFORMAT DMY
Declare @Err int, @Cont int
Set @Cont = 1
BEGIN TRAN
While @Cont = 1
Begin
-- Проверка на наличие и добавление русского языка
if not exists(Select * From Languages Where LangID = 1)
Insert Into Languages ([LangID], LangName) Values (1, 'Русский')
-- Копирование автокоманд на срабатывание датчиков
SET IDENTITY_INSERT SensCommands ON
Insert Into SensCommands
(CommandID, [On], [Off], Comment)
Select [ID], [ON], [OFF], COMMENT
From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\BNComplex\DataBase\ComplexInfo.mdb'; 'admin'; '', SensCommands) SensComForConversion
set @Err = @@ERROR
SET IDENTITY_INSERT SensCommands OFF
if @Err != 0 break
-- Копирование стандартных схем датчиков
Insert Into Sensors
(SensSchema, SensNum, SensName, SensNameShort, Reaction, Every, CommandID)
Select SensShema, SensNum, SensName, SensNameShort, Reaction, Every, case when [ID] = 0 then NULL else [ID] end
From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\BNComplex\DataBase\ComplexInfo.mdb'; 'admin'; '', Sensors) SensForConversion
set @Err = @@ERROR
if @Err != 0 break
-- Копирование областей
SET IDENTITY_INSERT AreaDescr ON
Insert Into AreaDescr (
AreaID, Describtion)
Select AREA, DESCRIBTION
From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\BNComplex\DataBase\ComplexInfo.mdb'; 'admin'; '', AreaDescr) AreasForConversion
set @Err = @@ERROR
SET IDENTITY_INSERT AreaDescr OFF
if @Err != 0 break
-- Копирование пользователей
SET IDENTITY_INSERT [Login] ON
Insert Into [Login] (
LoginID, [User], Pass, [State], [Type], AreaID, [Param], TechComment, LastTime, SendInt, Version,
AppType, AppVer, AreaAdmin, PersistCon, [LangID]
)
Select
UNIQID, [USER], PASS,
case when ([STATE] & 1) = 1 then 1 else 0 end,
[TYPE], AREA,
[PARAM] & 0x3FFFFFFC,
COMMENT, LASTTIME,
isnull(SENDINT,0), VERSION, APPTYPE, APPVER,
case when ([PARAM] & 1) = 1 then 1 else 0 end,
case when ([PARAM] & 2) = 2 then 1 else 0 end,
1
From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\BNComplex\DataBase\ComplexInfo.mdb'; 'admin'; '', Login) Users
set @Err = @@ERROR
SET IDENTITY_INSERT [Login] OFF
if @Err != 0 break
-- Копирование таблицы типов оборудования
Insert Into EquipDescr
(EquipID, Describtion, DefSensSchemaID, DefSubParam, CanCreate, TableName, ClearOdometr, SpyID)
Select
EQUIPID, DESCRIBTION, DEFSENSSCHEMA, DEFSUBPARAM, CANCREATE, TABLENAME, CLEARODOMETR,
case when SPYID = 0 then NULL else SPYID end
From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\BNComplex\DataBase\ComplexInfo.mdb'; 'admin'; '', EquipDescr) EquipForConversion
set @Err = @@ERROR
if @Err != 0 break
-- Из таблицы приборов создание таблицы приборов, объектов и связей между ними
Declare @DevNum varchar(15), @EquipID int, @State int, @SensSchema int, @AreaID int, @Name nvarchar(32),
@Comment nvarchar(32), @DateAdd datetime, @PhoneNum varchar(15)
Declare Dev Cursor local fast_forward for
Select DEVID, EQUIPID, [STATE], SENSSCHEMA, AREA, [NAME], COMMENT, [DATEADD], PHONENUM
From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\BNComplex\DataBase\ComplexInfo.mdb'; 'admin'; '', Devices) DevicesForConversion
Open Dev
While 1=1
Begin
Fetch Next From Dev Into @DevNum, @EquipID, @State, @SensSchema, @AreaID, @Name, @Comment, @DateAdd, @PhoneNum
if @@fetch_status != 0 break
Declare @ObjID int, @DeviceID int
Insert Into Devices (
DevNum, AreaID, [State], EquipID, [DateAdd], TechComment, PhoneNum)
Values (
@DevNum, @AreaID,
case when @State = 1 then 1 else 0 end,
@EquipID,
isnull(@DateAdd, GetUTCDate()),
@Comment, @PhoneNum)
Set @Err = @@ERROR
if @Err != 0 break
Set @DeviceID = scope_identity()
Insert Into Objects (
AreaID, DefaultName, SensSchema, TechComment, [Status], [DateAdd])
Values (
@AreaID, @Name, @SensSchema, @Comment,
case when @State = 1 then 1 else 0 end,
isnull(@DateAdd, GetUTCDate()))
Set @Err = @@ERROR
if @Err != 0 break
Set @ObjID = scope_identity()
Insert Into DevicesOnObjects (
DeviceId, Objectid, MainDevice)
Values (
@DeviceID, @ObjID, 1)
Set @Err = @@ERROR
if @Err != 0 break
End
Close Dev
Deallocate Dev
if @Err != 0 break
-- Крпирование подписок
Insert Into Subscriptions (
ObjectID, LoginID, Start, [End], Interval, [Event], EventType, [DateAdd], Busy, LastTime, [Param])
Select
DOO.ObjectID, S.[USER],
isnull(S.START, '01-06-2008 00:00:00'),
isnull(S.[END], '01-01-2035 00:00:00'),
S.INTERVAL,
case when [Event] = '*' then 0 else cast(S.EVENT as int) end,
case when D.EquipID in (4, 6, 10, 11, 12) then 1 else 0 end,
isnull(S.[DATEADD], GetUTCDate()),
0, S.LASTTIME, S.PARAM
From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\BNComplex\DataBase\ComplexInfo.mdb'; 'admin'; '', Subscribtions) S
Inner Join Devices D on
D.DevNum = S.DEVID
Inner Join DevicesOnObjects DOO on
DOO.DeviceID = D.DeviceID
Set @Err = @@ERROR
if @Err != 0 break
-- Копирование индивидуальных датчиков объектов
Insert Into ObjectsSensors (
ObjectID, SENSNUM, SensName, SensShortName, Reaction, Every, CommandID)
Select
DOO.ObjectID, DS.SENSNUM, DS.SensName, DS.SensNameShort, DS.Reaction, DS.Every,
case when DS.[ID] = 0 then NULL else DS.[ID] end
From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\BNComplex\DataBase\ComplexInfo.mdb'; 'admin'; '', DevSensors) DS
Inner Join Devices D on
D.DevNum = DS.DEVID
Inner Join DevicesOnObjects DOO on
DOO.DeviceID = D.DeviceID
Set @Err = @@ERROR
if @Err != 0 break
-- Копироване ключей активации
Insert Into ActivKeys (
[Key], LoginID, DateActiv, Comment)
Select [KEY], [PARAM], DATEACTIV, COMMENT
From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\BNComplex\DataBase\ComplexInfo.mdb'; 'admin'; '', ActivKeys) UsersKeys
set @Err = @@ERROR
if @Err != 0 break
-- Копирование групп синхронизации ДП
Insert Into SyncGrp (
LoginID, SGroupID, [Param])
Select
UNIQID, SGROUPID, [PARAM]
From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\BNComplex\DataBase\ComplexInfo.mdb'; 'admin'; '', SyncGrp) SGrp
set @Err = @@ERROR
if @Err != 0 break
-- Копирование последних координат
Insert Into ObjectsCoords (
ObjectID, DeviceID, NavTime, Lat, Long, Speed, Course, Sensors, SensType, Valid, Analog, FirstTime,
LastAccessTime, [Text], Dist, Geoaz, Dir)
Select
DOO.ObjectID, D.DeviceID, LC.NAVTIME,
case when LC.LAT > 90.0 then LC.LAT-92.0
when LC.LAT < -90.0 then LC.LAT+92.0
else LC.LAT
end,
LC.LONG, LC.SPEED, LC.COURSE, 0,
case when D.EquipID in (4, 6, 10, 11, 12) then 1 else 0 end,
case when LC.LAT > 90.0 or LC.LAT < -90.0 then 0 else 1 end,
isnull(LC.ANALOG, 0.0),
isnull(LC.FIRSTTIME, LC.NAVTIME),
LC.LastAccessTime, LC.[TEXT],
case when LC.DIST = 0 then NULL else LC.DIST end,
case when LC.GEOAZ = 0 then NULL else LC.GEOAZ end,
case when LC.DIR = 0 then NULL else LC.DIR end
From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\BNComplex\DataBase\ComplexInfo.mdb'; 'admin'; '', LastCoords) LC
Inner Join Devices D on
D.DevNum = LC.DEVID
Inner Join DevicesOnObjects DOO on
DOO.DeviceID = D.DeviceID
Set @Err = @@ERROR
if @Err != 0 break
Set @Cont = 0
End
if @Err = 0
COMMIT TRAN
else
ROLLBACK