Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
Добрый день.
Версия MS-SQL

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )

После написания процедуры возникает ошибка на изменения БД. Если нужна процедура,то предоставлю - она просто большая.
Не могу понять,почему не проходит изменение.Подскажите, где посмотреть ошибку?Есть ли логи?
12 май 17, 12:28    [20475805]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
Прошу прощения ошибка собственно

1/5069: ALTER DATABASE statement failed.
12 май 17, 12:33    [20475844]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7663
Максим Александровитч,

А что в самом стейтменте написано?
Это не весь текст ошибки. Процедура каким образом выполняется?
12 май 17, 12:37    [20475872]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
Владислав Колосов,

Вот стейтмент
EXEC [dbo].[pdt_Inventory_Main] @Session_id = 14231, @TerminalAddress = 'PC-FAAF1B44-93F5F257'
Что значит каким образом выполняется?Я просто не понял.
Могу процедуру привести
12 май 17, 12:46    [20475928]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
o-o
Guest
открываете в окне студии текст процедуры,
Ctrl + F, alter database, и сюда покажите, что делает.
всю команду ALTER DATABASE копируйте сюда
12 май 17, 12:49    [20475952]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
o-o,

Так.Я понял.Все немного сложнее.
Суть процедуры-это оперативная инвентаризация,т.е. изменение остатков в таблицах БД.
Вот она
+
alter procedure [dbo].[pdt_Inventory_Main]       
(      
 @TerminalAddress varchar(200) = NULL,      
 @Session_id int = NULL      
) as begin      
 declare @IsNormalSession int      
 exec pdt_CheckSession      
   @Session_id = @Session_id,      
   @TerminalAddress = @TerminalAddress,      
   @IsNormal = @IsNormalSession output      
      
 if isnull(@IsNormalSession, 0) = 0      
 begin      
  exec pdt_ErrorMessage      
    @TerminalAddress = @TerminalAddress,      
    @Session_id = @Session_id,      
    @Line1 = 'ВАША СЕССИЯ',      
    @Line2 = 'ЗАБЛОКИРОВАНА!',      
    @Line3 = '',      
    @Line4 = 'СРОЧНО ПОДОЙДИТЕ',      
    @Line5 = 'К ОПЕРАТОРУ!',      
    @ReturnProc = ''      
  return;      
 end      
      
 declare @SchemaName varchar(500) = OBJECT_SCHEMA_NAME(@@PROCID);      
 declare @ProcName varchar(500) = OBJECT_NAME(@@PROCID);      
 declare @CurrentProc varchar(500)      
 set @CurrentProc = '[' + @SchemaName + '].[' + @ProcName + ']'      
      
 declare @InventoryTaskProc varchar(300)      
 set @InventoryTaskProc = '[dbo].[pdt_Inventory_Task]'      
      
 declare @ProductionResource_id int        
 select @ProductionResource_id = ProductionResource_id from ProductionResourceSessions with (nolock)       
 where tid = @Session_id      
      
 -- в дальнейшем предполагается использовать TechnoQuery      
 declare @StrategyProcedure varchar(300)      
 select top 1      
  @StrategyProcedure = StrategyProcedure      
 from InventoryMaterialStrategy with(nolock)      
 where IsActive = 1      
      
 -------------------------------      
 DECLARE @ThisGUID varchar(50);      
 EXEC dbo.pdt_GetTerminalGUID      
    @TerminalAddress = @TerminalAddress,      
    @GUID = @ThisGUID output      
        
  DECLARE @terminalvariables AS dbo.VarList;      
  -------------------------------      
      
 declare @NextMode varchar(200), @Task_id int, @TaskLocation_id int, @TaskStorageObject_id int,      
  @TaskMaterial_id int, @StockType_id int, @Inventory_Type int, @DiscountCategory_id int,      
  @LocationBarcode varchar(200), @StorageObjectBarcode varchar(200), @StorageObject_id int,      
  @StorageUnit_id int, @BoxStorageObject_id int, @Material_id int, @MaterialUnit_id int,      
  @BaseQuantity decimal(25,6), @BarcodeObject_id int, @StockCategory_id int, @MaterialWeight decimal(25,6), @OwnerDebtor_id int      
      
 SELECT       
  @NextMode = pvt.[NextMode],      
  @Task_id = pvt.[Task_id],      
  @TaskLocation_id = pvt.[TaskLocation_id],      
  @TaskStorageObject_id = pvt.[TaskStorageObject_id],      
  @TaskMaterial_id = pvt.[TaskMaterial_id],      
  @StockType_id = pvt.[MaterialIn.StockType_id],      
  @Inventory_Type = pvt.[Inventory_Type],      
  @DiscountCategory_id = pvt.[MaterialIn.DiscountCategory_id],      
  @LocationBarcode = pvt.[LocationBarcode],      
  @StorageObjectBarcode = pvt.[StorageObjectBarcode],      
  @StorageObject_id = pvt.[StorageObject_id],      
  @StorageUnit_id = pvt.[StorageUnit_id],      
  @BoxStorageObject_id = pvt.[BoxStorageObject_id],      
  @Material_id = pvt.[MaterialIn.Material_id],      
  @MaterialUnit_id = pvt.[MaterialIn.MaterialUnit_id],      
  @BaseQuantity = pvt.[MaterialIn.BaseQuantity],      
  @BarcodeObject_id = pvt.[MaterialIn.BarcodeObject_id],      
  @StockCategory_id = pvt.[MaterialIn.StockCategory_id],      
  @MaterialWeight = pvt.[MaterialIn.NettoWeight],      
  @OwnerDebtor_id = pvt.[MaterialIn.OwnerDebtor_id]      
 FROM dbo.pdt_TerminalVariables(@ThisGUID, default) AS tv       
 PIVOT (MAX(tv.VariableValue) FOR tv.VariableName IN (      
 [NextMode],[Task_id],[TaskLocation_id],[TaskStorageObject_id],      
 [TaskMaterial_id],[MaterialIn.StockType_id],[Inventory_Type],      
 [MaterialIn.DiscountCategory_id],[LocationBarcode],[StorageObjectBarcode],      
 [StorageObject_id],[StorageUnit_id],[BoxStorageObject_id],[MaterialIn.Material_id],      
 [MaterialIn.MaterialUnit_id],[MaterialIn.BaseQuantity],[MaterialIn.BarcodeObject_id],      
 [MaterialIn.StockCategory_id],[MaterialIn.NettoWeight],[MaterialIn.OwnerDebtor_id]      
 )) AS pvt      
      
 set @NextMode = isnull(@NextMode, 'ScanLocation')      
      
 declare @LocationName varchar(200)      
 set @LocationName = dbo.LocationName(@TaskLocation_id)      
       
 declare @StorageObjectName varchar(200)      
 set @StorageObjectName = dbo.ObjectName(isnull(@TaskStorageObject_id, @StorageObject_id))      
/*      
 declare @MaterialName varchar(200)      
 select @MaterialName = NameRU from Materials with (nolock)      
 where tid = isnull(@TaskMaterial_id, @Material_id)      
*/       
 declare @Objects table (      
 dY int identity(0,1),      
 ObjectName varchar(200)      
 )      
 insert into @Objects (ObjectName)      
 select dbo.ObjectName(tid) from StorageObjects with (nolock)      
 where Location_id = @TaskLocation_id and BottomStorageObject_id is null      
 order by tid      
       
 declare @Rows table (Row_id int not NULL)      
 declare @Prompt varchar(100), @PackWeight decimal(25,6), @BottomStorageObject_id int, @SystemTransaction_id int,      
   @ExitKey varchar(50), @Quantity decimal(25,6), @Transaction_id int, @IsAutoTask int, @IsSelf int, @MasterStorageObject_id int      
      
 if @NextMode = 'ScanLocation'      
 begin      
  if (select isnull(IsFixed,0) from StorageObjects with (nolock)      
   where tid = @TaskStorageObject_id) = 1 begin      
   exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'NextMode',      
     @VariableValue = 'GetStorageObject'      
      
   select [NEXTPROC] = @CurrentProc      
   return;      
  end      
      
  exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'LocationBarcode',      
    @VariableValue = NULL      
      
  exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'NextMode',      
    @VariableValue = 'CheckLocation'      
      
  select      
  [PRINTMODE] = 'CLEAR', [PRINTMODE] = 'CLSCR', [PRINTMODE] = 'INVERSE', [PRINTMODE] = 'CENTERED',      
  [XCOOR] = 0, [YCOOR] = 00, [TEXT] = 'ИНВЕНТАРИЗАЦИЯ', [METHOD] = 'PRINT',      
  [PRINTMODE] = 'CLEAR', [PRINTMODE] = 'NORMAL', [PRINTMODE] = 'CENTERED',      
  [XCOOR] = 0, [YCOOR] = 04, [TEXT] = 'СКАНИРУЙТЕ ШТРИХКОД', [METHOD] = 'PRINT',      
  [XCOOR] = 0, [YCOOR] = 05, [TEXT] = 'МЕСТА ХРАНЕНИЯ', [METHOD] = 'PRINT',      
  [XCOOR] = 0, [YCOOR] = 07, [TEXT] = @LocationName, [METHOD] = 'PRINT',      
  [PRINTMODE] = 'CLEAR', [PRINTMODE] = 'INVERSE', [PRINTMODE] = 'CENTERED',      
  [XCOOR] = 0, [YCOOR] = 14, [TKEY] = 'X', [TEXT] = '<X> - ВЫХОД', [METHOD] = 'PRINT',      
  [VARIABLE] = 'LocationBarcode', [METHOD] = 'WAITSCAN', [NEXTPROC] = @CurrentProc      
  return;      
 end      
      
 if @NextMode = 'CheckLocation'      
 begin      
  if isnull(@LocationBarcode, '') in ('x', 'X')      
  begin      
   select @Transaction_id = a.Transaction_id, @IsAutoTask = c.IsAutoTask, @IsSelf = a.IsSelf      
   from InventoryTasks as a with (nolock)      
   join hdr_ObjectInventory as b with (nolock) on a.Transaction_id = b.Transaction_id      
   join InventoryTypes as c with (nolock) on b.InventoryType_id = c.tid      
   where a.tid = @Task_id      
        
   if isnull(@IsAutoTask, 0) = 1 begin      
    update InventoryTasks set TaskDate = NULL, ProductionResource_id = NULL      
    where tid = @Task_id      
   end      
         
   if isnull(@IsSelf, 0) = 1 begin      
    delete from InventoryTasks where tid = @Task_id      
          
    update Transactions set TransactionStatus = 1      
    where tid = @Transaction_id      
   end      
         
   select [NEXTPROC] = 'pdt_Menu'      
   return;      
  end      
        
  if (      
   select count(tid) from Locations with (nolock)      
   where tid = @TaskLocation_id and LocationBarcode = @LocationBarcode      
  ) = 0      
  begin      
   exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'NextMode',      
     @VariableValue = 'ScanLocation'      
    
   exec pdt_ErrorMessage      
     @TerminalAddress = @TerminalAddress,      
     @Session_id = @Session_id,      
     @Line1 = 'НЕВЕРНЫЙ ШТРИХКОД',      
     @Line2 = 'МЕСТА ХРАНЕНИЯ',      
     @Line3 = '',      
     @Line4 = @LocationName,      
     @ReturnProc = @CurrentProc      
   return;      
  end      
      
  exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'NextMode',      
    @VariableValue = 'GetStorageObject'      
          
  select [NEXTPROC] = @CurrentProc      
  return;      
 end      
      
 if @NextMode = 'GetStorageObject'      
 begin      
  --if (      
  -- select count(tid) from StorageObjects with (nolock)      
  -- where Location_id = @TaskLocation_id      
  --) = 1 and (      
  -- select count(tid) from StorageObjects with (nolock)      
  -- where Location_id = @TaskLocation_id and isnull(IsFixed, 0) = 1 and isnull(IsSystem, 0) = 0      
  --) = 1      
  --begin      
  -- select top 1       
  -- @TaskStorageObject_id = tid      
  -- from StorageObjects with (nolock)      
  -- where Location_id = @TaskLocation_id      
         
  -- update InventoryTasks set StorageObject_id = @TaskStorageObject_id      
  -- where tid = @Task_id      
      
  -- delete from @terminalvariables      
       
  -- INSERT @terminalvariables (VariableName, VariableValue)      
  -- VALUES      
  --  ('TaskStorageObject_id',cast(@TaskStorageObject_id as varchar(20))),      
  --  ('StorageObject_id',cast(@TaskStorageObject_id as varchar(20))),      
  --  ('NextMode','GetStockType');      
      
  -- EXEC [dbo].[pdt_SetTerminalVariables] @PDTGuid = @ThisGUID, @Variables = @terminalvariables;      
            
  --  select [NEXTPROC] = @CurrentProc      
  --  return;      
  --end      
      
  delete from @terminalvariables      
       
  INSERT @terminalvariables (VariableName, VariableValue)      
  VALUES      
   ('StorageObjectBarcode',NULL),      
   ('StorageObject_id',NULL),      
   ('NextMode','SetStorageObject');      
      
  EXEC [dbo].[pdt_SetTerminalVariables] @PDTGuid = @ThisGUID, @Variables = @terminalvariables;      
      
  select      
  [PRINTMODE] = 'CLEAR', [PRINTMODE] = 'CLSCR', [PRINTMODE] = 'INVERSE', [PRINTMODE] = 'CENTERED',      
  [XCOOR] = 0, [YCOOR] = 00, [TEXT] = 'ИНВЕНТАРИЗАЦИЯ', [METHOD] = 'PRINT',      
  [PRINTMODE] = 'CLEAR', [PRINTMODE] = 'NORMAL', [PRINTMODE] = 'CENTERED'      
  if  (select isnull(IsFixed,0) from StorageObjects with (nolock)      
   where tid = @TaskStorageObject_id) = 1 begin      
  select      
  [XCOOR] = 0, [YCOOR] = 04, [TEXT] = 'СКАНИРУЙТЕ ШТРИХКОД', [METHOD] = 'PRINT',      
  [XCOOR] = 0, [YCOOR] = 05, [TEXT] = 'МЕСТА ХРАНЕНИЯ', [METHOD] = 'PRINT',      
  [XCOOR] = 0, [YCOOR] = 07, [TEXT] = @LocationName + ' ' + dbo.ObjectName(@TaskStorageObject_id), [METHOD] = 'PRINT'      
  end else begin      
  select      
  [XCOOR] = 0, [YCOOR] = 01, [TEXT] = @LocationName, [METHOD] = 'PRINT',      
  [XCOOR] = 0, [YCOOR] = 04, [TEXT] = 'СКАНИРУЙТЕ ШТРИХКОД', [METHOD] = 'PRINT',      
  [XCOOR] = 0, [YCOOR] = 05, [TEXT] = 'ОБЪЕКТА СКЛАДИРОВАНИЯ', [METHOD] = 'PRINT'      
        
  if @TaskStorageObject_id is not NULL      
   select      
   [XCOOR] = 0, [YCOOR] = 07, [TEXT] = @StorageObjectName, [METHOD] = 'PRINT'      
  else if (      
   select count(*) from @Objects      
  ) = 0       
   select      
   [XCOOR] = 0, [YCOOR] = 07, [TEXT] = 'ячейка пуста', [METHOD] = 'PRINT'      
  else begin      
   select      
   [XCOOR] = 0, [YCOOR] = 07, [TEXT] = 'в ячейке числится:', [METHOD] = 'PRINT'      
   select top 3      
   [XCOOR] = 0, [YCOOR] = 08 + dY, [TEXT] = ObjectName, [METHOD] = 'PRINT'      
   from @Objects      
  end      
  select      
  [XCOOR] = 0, [YCOOR] = 12, [TKEY] = '0', [TEXT] = '<0> - ЗАВЕРШИТЬ', [METHOD] = 'PRINT'      
  end      
  select      
  [PRINTMODE] = 'CLEAR', [PRINTMODE] = 'INVERSE', [PRINTMODE] = 'CENTERED',      
  [XCOOR] = 0, [YCOOR] = 14, [TKEY] = 'X', [TEXT] = '<X> - ВЫХОД', [METHOD] = 'PRINT',      
  [VARIABLE] = 'StorageObjectBarcode', [METHOD] = 'WAITSCAN', [NEXTPROC] = @CurrentProc      
  return;      
 end      
      
 if @NextMode = 'SetStorageObject'      
 begin      
  if isnull(@StorageObjectBarcode, '') in ('x', 'X')      
  begin      
   select @Transaction_id = a.Transaction_id, @IsAutoTask = c.IsAutoTask, @IsSelf = a.IsSelf      
   from InventoryTasks as a with (nolock)      
   join hdr_ObjectInventory as b with (nolock) on a.Transaction_id = b.Transaction_id      
   join InventoryTypes as c with (nolock) on b.InventoryType_id = c.tid      
   where a.tid = @Task_id      
        
   if isnull(@IsAutoTask, 0) = 1      
   begin      
    update InventoryTasks set TaskDate = NULL, ProductionResource_id = NULL      
    where tid = @Task_id      
   end      
         
   if isnull(@IsSelf, 0) = 1      
   begin      
    delete from InventoryTasks where tid = @Task_id      
          
    update Transactions set TransactionStatus = 1      
    where tid = @Transaction_id      
   end      
      
   select [NEXTPROC] = 'pdt_Menu'      
   return;      
  end      
      
  if isnull(@StorageObjectBarcode, '') in ('0')      
  begin      
   exec tsk_SetInventoryJob      
     @Session_id = @Session_id,      
     @Task_id = @Task_id      
      
   if (select COUNT(*) from WarehouseSummary as a with (nolock)      
    join StorageObjects as b with (nolock) on a.StorageObject_id = b.tid and a.BaseQuantity > 0      
    where b.Location_id = @TaskLocation_id       
    ) > 0       
    and (select COUNT(*) from tbl_InventoryResult with (nolock) where InventoryTask_id = @Task_id         
    ) = 0       
   begin      
    exec pdt_ErrorMessage      
      @TerminalAddress = @TerminalAddress,      
      @Session_id = @Session_id,      
      @Line1 = 'ВНИМАНИЕ В ЯЧЕЙКЕ',      
      @Line2 = 'ИМЕЮТСЯ ОСТАТКИ,',      
      @Line3 = 'ЯЧЕЙКА ОБНУЛЕНА!!!',      
      @ReturnProc = @InventoryTaskProc         
     return;      
   end       
        
   select [NEXTPROC] = @InventoryTaskProc      
   return;      
  end      
        
  select top 1 @StorageObject_id = tid from StorageObjects with (nolock)      
  where Barcode = @StorageObjectBarcode --and Location_id = @TaskLocation_id      
   and tid = isnull(@TaskStorageObject_id, tid)      
         
  -- можно привязать любой объект складирования к ячейке      
  -- если на нем нет остатков и дочерних объектов складирования      
        
  -- при этом нужно удалить резерв места в других местах      
  -- и создать резерв места в данную ячейку(создать безусловно)      
  if @StorageObject_id is NULL and @TaskStorageObject_id is null      
  begin      
   select top 1 @StorageObject_id = tid from StorageObjects with (nolock)      
   where Barcode = @StorageObjectBarcode      
         
   if @StorageObject_id is not null      
   begin      
    -- Содержит запас      
    if exists(      
     select top 1 1 from WarehouseSummary with (nolock)      
     where StorageObject_id = @StorageObject_id and BaseQuantity > 0      
    )      
    begin      
     set @StorageObject_id = NULL      
    end      
          
    -- Является мастер-объектом для объектов складирования с запасом      
    if @StorageObject_id is not null      
    and exists(      
     select top 1 1 from StorageObjects as a with (nolock)      
     join WarehouseSummary as b with (nolock) on a.tid = b.StorageObject_id      
     where b.BaseQuantity > 0 and a.BottomStorageObject_id = @StorageObject_id      
    )      
    begin      
     set @StorageObject_id = NULL      
    end      
          
    if @StorageObject_id is not null      
    begin      
     -- создаем резерв места безусловно      
     select top 1       
      @Transaction_id = a.Transaction_id      
     from InventoryTasks as a with (nolock)      
     where a.tid = @Task_id      
           
     exec app_CreateLocationsReserve      
      @StorageObject_id = @StorageObject_id,      
      @Location_id = @TaskLocation_id,      
      @Transaction_id = @Transaction_id      
     -- привязываем объект к ячейке      
     update StorageObjects      
     set Location_id = case when IsFixed = 1 then Location_id else @TaskLocation_id end      
     where tid = @StorageObject_id      
     -- удаляем резервы места в других местах      
     delete from LocationsReserve      
     where StorageObject_id = @StorageObject_id and Location_id <> @TaskLocation_id      
    end      
   end      
  end      
      
  if @StorageObject_id is NULL      
  begin      
   exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'NextMode',      
     @VariableValue = 'GetStorageObject'      
           
   exec pdt_ErrorMessage      
     @TerminalAddress = @TerminalAddress,      
     @Session_id = @Session_id,      
     @Line1 = 'НЕВЕРНЫЙ ШТРИХКОД',      
     @Line2 = 'ОБЪЕКТА СКЛАДИРОВАНИЯ',      
     @ReturnProc = @CurrentProc      
 return;      
  end      
      
        
  delete from @terminalvariables      
      
  INSERT @terminalvariables (VariableName, VariableValue)      
  VALUES      
   ('StorageObject_id',cast(@StorageObject_id as varchar(20))),      
   ('NextMode','GetObjectUnit');      
      
  EXEC [dbo].[pdt_SetTerminalVariables] @PDTGuid = @ThisGUID, @Variables = @terminalvariables;      
      
      
  if exists      
  (      
   select * from StorageObjects with(nolock)      
   where tid = @StorageObject_id and IsFixed = 1      
  )      
  or exists      
  (      
   select top 1 1 from InventoryTasks with(nolock)      
   where tid = @Task_id and Material_id is not null      
  )      
  begin      
   exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'NextMode',      
    @VariableValue = 'GetOwner'      
        
   select [NEXTPROC] = @CurrentProc      
   return;      
  end      
          
  select [NEXTPROC] = @CurrentProc      
  return;      
 end      
      
 if @NextMode = 'GetObjectUnit'      
 begin      
  exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'NextMode',      
    @VariableValue = 'SetObjectUnit'      
      
  if (select count(*) from StorageUnits with (nolock)      
   where isnull(IsForStoring,0) = 1 and isnull(IsPallet,0) = 1) = 1      
  begin      
         
   select @StorageUnit_id = tid from StorageUnits with (nolock)      
   where isnull(IsForStoring,0) = 1 and isnull(IsPallet,0) = 1      
         
   exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'StorageUnit_id',      
     @VariableValue = @StorageUnit_id      
           
   select [NEXTPROC] = @CurrentProc      
   return;      
  end        
      
  select      
  [STARTMENU] = dbo.ObjectName(@StorageObject_id) + ':',      
  [EXITOPTION] = 1,      
  [VARIABLE] = 'StorageUnit_id'      
      
/*  select      
  [ITEM_ID] = a.tid,       
  [ITEM_NAME] = a.NameRU      
  from StorageUnits as a with (nolock)      
  where a.IsForStoring = 1      
  order by       
   a.NameRU      
*/      
  select      
  [ITEM_ID] = case when b.tid is NULL then a.tid else -2 end,       
  [ITEM_NAME] = a.NameRU      
  from StorageUnits as a with (nolock)      
  left join StorageObjects as b with (nolock) on a.tid = b.StorageUnit_id      
   and b.tid = @StorageObject_id      
  where isnull(IsForStoring,0) = 1 and isnull(IsPallet,0) = 1      
  order by case      
   when b.tid is not NULL then 1      
   else 0      
  end desc, a.NameRU asc      
      
  select      
  [EXECUTEMENU] = 'Menu',      
  [NEXTPROC] = @CurrentProc      
  return;      
 end      
      
 if @NextMode = 'SetObjectUnit'      
 begin      
  if isnull(@StorageUnit_id, -1) = -1      
  begin      
   exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'NextMode',      
     @VariableValue = 'GetStorageObject'      
        
   select [NEXTPROC] = @CurrentProc      
   return;      
  end      
      
  if @StorageUnit_id <> -2 and      
   (select count(*) from StorageObjects with (nolock)      
   where tid = @StorageObject_id and StorageUnit_id = @StorageUnit_id) = 0      
  begin      
      
   set @SystemTransaction_id = dbo.GetSystemTransaction()      
      
   insert into ManualAllocationLog ( Transaction_id, StorageObject_id, CurrentLocation_id, TargetLocation_id, IsCompleted, ProductionResource_id, Materials )      
   select @SystemTransaction_id, @StorageObject_id, @TaskLocation_id, @TaskLocation_id, 1, @ProductionResource_id, 'Смена типа объекта складирования при инвентаризации "'+b.NameRU+'" -> "'+c.NameRU+'"'      
   from StorageObjects as a with (nolock)      
   join StorageUnits as b with (nolock) on b.tid = a.StorageUnit_id      
   join StorageUnits as c with (nolock) on c.tid = @StorageUnit_id and c.tid <> b.tid      
   where a.tid = @StorageObject_id      
      
   update StorageObjects set StorageUnit_id = @StorageUnit_id      
   where tid = @StorageObject_id and StorageUnit_id <> @StorageUnit_id      
  end      
      
  /*      
  if exists      
  (      
   select * from StorageObjects with(nolock)      
   where tid = @StorageObject_id and IsFixed = 1      
  )      
  begin      
  */      
   exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'NextMode',      
    @VariableValue = 'GetOwner'      
        
   select [NEXTPROC] = @CurrentProc      
   return;      
  /*end*/      
      
  /*      
  exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'NextMode',      
    @VariableValue = 'GetStockType'      
        
  select [NEXTPROC] = @CurrentProc      
  return;      
  */      
 end      
      
 if @NextMode = 'GetOwner'      
 begin      
  exec pdt_SetTerminalVariable      
   @TerminalAddress = @TerminalAddress,      
   @VariableName = 'NextMode',      
   @VariableValue = 'SetOwner'      
      
  declare @Debtors as table (Debtor_id int)      
        
  insert into @Debtors (Debtor_id)      
  select  distinct Debtor_id       
  from ContractPortalAccess with (nolock)      
        
  select      
  [STARTMENU] = 'Владелец запаса:',      
  [EXITOPTION] = 1,      
  [VARIABLE] = 'MaterialIn.OwnerDebtor_id'      
      
  select      
  [ITEM_ID] = b.tid,  [ITEM_NAME] = ISNULL(b.ShortName, b.FullName)      
  from @Debtors as a       
  join Debtors as b on a.Debtor_id = b.tid       
      
      
  select      
  [EXECUTEMENU] = 'DebtorMenu',      
  [NEXTPROC] = @CurrentProc      
  return;      
 end      
      
 if @NextMode = 'SetOwner'      
 begin      
  if isnull(@OwnerDebtor_id, -1) = -1      
  begin      
   exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'NextMode',      
     @VariableValue = 'GetStorageObject'      
      
   select [NEXTPROC] = @CurrentProc      
   return;      
  end       
       
    exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'NextMode',      
    @VariableValue = 'GetBottomOrMaterial'--'GetStockType'     
     
  
       
  select [NEXTPROC] = @CurrentProc      
  return;      
 end      
      
 if @NextMode = 'GetStockType'      
 begin      
        
  exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'NextMode',      
    @VariableValue = 'GetStorageObject'--'SetStockType'      
        
  select      
  [STARTMENU] = 'Вид запаса:',      
  [EXITOPTION] = 1,      
  [VARIABLE] = 'MaterialIn.StockType_id'      
      
  select [ITEM_ID] = tid,        
  [ITEM_NAME] = NameRu       
  from StockTypes with (nolock)      
  where IsUsedWithPdt = 1      
      
  select      
  [EXECUTEMENU] = 'StockTypeMenu',      
  [NEXTPROC] = @CurrentProc      
  return;      
 end      
      
 if @NextMode = 'SetStockType'      
 begin      
  if isnull(@StockType_id, -1) = -1      
  begin      
   exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'NextMode',      
     @VariableValue = 'GetStorageObject'      
      
   select [NEXTPROC] = @CurrentProc      
   return;      
  end      
        
  if (select COUNT(tid) from MaterialTypeQuality (nolock)      
   where StockType_id = @StockType_id) > 0      
  begin      
   exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'NextMode',      
    @VariableValue = 'GetBottomOrMaterial'      
         
   select      
   [STARTMENU] = 'Вид отклонения:',      
   [EXITOPTION] = 1,      
   [VARIABLE] = 'MaterialIn.DiscountCategory_id'      
      
   select [ITEM_ID] = DiscountCategory_id,        
   [ITEM_NAME] = NameRu       
   from MaterialTypeQuality with (nolock)      
   where IsIncomeUse = 1 and StockType_id = @StockType_id      
      
   select      
   [EXECUTEMENU] = 'DiscountCategoryMenu',      
   [NEXTPROC] = @CurrentProc      
   return;      
  end      
        
  exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'NextMode',      
     @VariableValue = 'GetBottomOrMaterial'      
        
  select [NEXTPROC] = @CurrentProc      
  return;      
 end      
      
 if @NextMode = 'GetBottomOrMaterial'      
 begin      
  delete from @terminalvariables      
      
  INSERT @terminalvariables (VariableName, VariableValue)      
  VALUES      
   ('BoxStorageObject_id',NULL),      
   ('NextMode','SetBottomOrMaterial'  
    
     
   );      
      
  EXEC [dbo].[pdt_SetTerminalVariables] @PDTGuid = @ThisGUID, @Variables = @terminalvariables;      
      
  select      
  [PRINTMODE] = 'CLEAR', [PRINTMODE] = 'CLSCR', [PRINTMODE] = 'INVERSE', [PRINTMODE] = 'CENTERED',      
  [XCOOR] = 0, [YCOOR] = 00, [TEXT] = 'ИНВЕНТАРИЗАЦИЯ', [METHOD] = 'PRINT',      
  [PRINTMODE] = 'CLEAR', [PRINTMODE] = 'NORMAL', [PRINTMODE] = 'CENTERED',      
  [XCOOR] = 0, [YCOOR] = 02, [TEXT] = dbo.ObjectName(@StorageObject_id), [METHOD] = 'PRINT',      
  [XCOOR] = 0, [YCOOR] = 04, [TEXT] = 'СКАНИРУЙТЕ ШТРИХКОД', [METHOD] = 'PRINT',      
  [XCOOR] = 0, [YCOOR] = 05, [TEXT] = 'МАТЕРИАЛА', [METHOD] = 'PRINT'      
        
  if not exists      
  (      
   select * from StorageObjects with(nolock)      
   where tid = @StorageObject_id and IsFixed = 1      
  ) and      
  not exists      
  (      
   select top 1 1 from InventoryTasks with(nolock)      
   where tid = @Task_id and Material_id is not null      
  )      
  begin      
   select      
   [XCOOR] = 0, [YCOOR] = 06, [TEXT] = 'ИЛИ', [METHOD] = 'PRINT',      
   [XCOOR] = 0, [YCOOR] = 07, [TEXT] = 'ДОЧЕРНЕГО ОС', [METHOD] = 'PRINT'      
  end      
      
  select      
    [XCOOR] = 0, [YCOOR] = 09, [TKEY] = '1', [TEXT] = '<1>-ВВЕСТИ ШТРИХКОД', [METHOD] = 'PRINT',      
    [XCOOR] = 0, [YCOOR] = 10, [TEXT] = '    МАТЕРИАЛА      ', [METHOD] = 'PRINT',      
     [XCOOR] = 0, [YCOOR] = 12, [TKEY] = '0', [TEXT] = '<0> - ЗАВЕРШИТЬ', [METHOD] = 'PRINT',      
  [PRINTMODE] = 'CLEAR', [PRINTMODE] = 'INVERSE', [PRINTMODE] = 'CENTERED',      
  [XCOOR] = 0, [YCOOR] = 14, [TKEY] = 'X', [TEXT] = '<X> - ВЫХОД', [METHOD] = 'PRINT',      
  [VARIABLE] = 'StorageObjectBarcode', [METHOD] = 'WAITSCAN', [NEXTPROC] = @CurrentProc      
  return;      
 end      
      
 if @NextMode = 'SetBottomOrMaterial'      
 begin      
  if isnull(@StorageObjectBarcode, '') in ('x', 'X')      
  begin      
   exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'NextMode',      
     @VariableValue = 'GetStorageObject'      
      
   select [NEXTPROC] = @CurrentProc      
   return;      
  end        
      
  if isnull(@StorageObjectBarcode, '') = '0'      
  begin      
   if @Inventory_Type = 0      
   begin      
         
    exec tsk_SetInventoryJob      
      @Session_id = @Session_id,      
      @Task_id = @Task_id      
      
    select [NEXTPROC] = @InventoryTaskProc      
    return;      
   end else      
   begin      
    if  (select isnull(IsFixed,0) from StorageObjects with (nolock)      
     where tid = @TaskStorageObject_id) = 1 begin      
     exec tsk_SetInventoryJob      
       @Session_id = @Session_id,      
       @Task_id = @Task_id      
      
     select [NEXTPROC] = @InventoryTaskProc      
     return;      
    end else begin               
     exec pdt_SetTerminalVariable      
       @TerminalAddress = @TerminalAddress,      
       @VariableName = 'NextMode',      
       @VariableValue = 'GetStorageObject'      
     select [NEXTPROC] = @CurrentProc      
     return;      
    end      
   end      
  end      
      
  if ltrim(isnull(@StorageObjectBarcode,'')) = ''      
  begin      
   exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'NextMode',      
     @VariableValue = 'GetBottomOrMaterial'      
           
   exec pdt_ErrorMessage      
     @TerminalAddress = @TerminalAddress,      
     @Session_id = @Session_id,      
     @Line1 = 'ПУСТОЙ',      
     @Line2 = 'ШТРИХКОД',      
     @ReturnProc = @CurrentProc      
   return;      
  end      
        
  if isnull(@StorageObjectBarcode, '') = '1' or      
   (select count(*) from MaterialUnitBarcodes with (nolock)      
   where Barcode = @StorageObjectBarcode ) > 0 or      
   (select count(*) from Materials with (nolock)      
   where NameEN = @StorageObjectBarcode) > 0      
  begin      
   delete from @terminalvariables      
      
   INSERT @terminalvariables (VariableName, VariableValue)      
   VALUES      
    ('MaterialIn.MaterialBarcode',@StorageObjectBarcode),      
    ('NextMode','GetMaterial');      
      
   EXEC [dbo].[pdt_SetTerminalVariables] @PDTGuid = @ThisGUID, @Variables = @terminalvariables;      
      
   select [NEXTPROC] = @CurrentProc      
   return;      
  end      
        
  set @BoxStorageObject_id = NULL      
      
  select top 1 @BoxStorageObject_id = tid from StorageObjects with (nolock)      
  where Barcode = @StorageObjectBarcode      
      
  if @BoxStorageObject_id is NULL      
  begin      
   exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'NextMode',      
     @VariableValue = 'GetBottomOrMaterial'      
           
   exec pdt_ErrorMessage      
     @TerminalAddress = @TerminalAddress,      
     @Session_id = @Session_id,      
     @Line1 = 'НЕВЕРНЫЙ ШТРИХКОД',      
     @Line2 = 'ОБЪЕКТА СКЛАДИРОВАНИЯ',      
     @ReturnProc = @CurrentProc      
   return;      
  end      
    
  delete from @terminalvariables      
      
  INSERT @terminalvariables (VariableName, VariableValue)      
  VALUES      
   ('BoxStorageObject_id',cast(@BoxStorageObject_id as varchar(20))),      
   ('NextMode','GetBoxObjectUnit');      
      
  EXEC [dbo].[pdt_SetTerminalVariables] @PDTGuid = @ThisGUID, @Variables = @terminalvariables;      
          
  select [NEXTPROC] = @CurrentProc      
  return;      
 end     
      
 if @NextMode = 'GetBoxObjectUnit'      
 begin      
  exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'NextMode',      
    @VariableValue = 'SetBoxObjectUnit'      
      
  if (select count(*) from StorageUnits with (nolock)      
   where isnull(IsForStoring,0) = 1 and isnull(IsBox,0) = 1) = 1      
  begin      
         
   select @StorageUnit_id = tid from StorageUnits with (nolock)      
   where isnull(IsForStoring,0) = 1 and isnull(IsBox,0) = 1      
         
   exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'StorageUnit_id',      
     @VariableValue = @StorageUnit_id      
           
   select [NEXTPROC] = @CurrentProc      
   return;      
  end        
      
  select      
  [STARTMENU] = dbo.ObjectName(@BoxStorageObject_id) + ':',      
  [EXITOPTION] = 1,      
  [VARIABLE] = 'StorageUnit_id'      
      
/*  select      
  [ITEM_ID] = a.tid,       
  [ITEM_NAME] = a.NameRU      
  from StorageUnits as a with (nolock)      
  where a.IsForStoring = 1      
  order by       
   a.NameRU      
*/      
  select      
  [ITEM_ID] = case when b.tid is NULL then a.tid else -2 end,       
  [ITEM_NAME] = a.NameRU      
  from StorageUnits as a with (nolock)      
  left join StorageObjects as b with (nolock) on a.tid = b.StorageUnit_id      
   and b.tid = @BoxStorageObject_id      
  where isnull(IsForStoring,0) = 1 and isnull(IsBox,0) = 1      
  order by case      
   when b.tid is not NULL then 1      
   else 0      
  end desc, a.NameRU asc      
      
  select      
  [EXECUTEMENU] = 'Menu',      
  [NEXTPROC] = @CurrentProc      
  return;      
 end      
      
 if @NextMode = 'SetBoxObjectUnit'      
 begin      
  if isnull(@StorageUnit_id, -1) = -1      
  begin      
   exec pdt_SetTerminalVariable      
     @TerminalAddress = @TerminalAddress,      
     @VariableName = 'NextMode',      
     @VariableValue = 'GetBottomOrMaterial'      
        
   select [NEXTPROC] = @CurrentProc      
   return;      
  end      
      
  if @StorageUnit_id <> -2 and      
   (select count(*) from StorageObjects with (nolock)      
   where tid = @BoxStorageObject_id and StorageUnit_id = @StorageUnit_id) = 0      
  begin      
      
   set @SystemTransaction_id = dbo.GetSystemTransaction()      
      
   insert into ManualAllocationLog ( Transaction_id, StorageObject_id, CurrentLocation_id, TargetLocation_id, IsCompleted, ProductionResource_id, Materials )      
   select @SystemTransaction_id, @BoxStorageObject_id, @TaskLocation_id, @TaskLocation_id, 1, @ProductionResource_id, 'Смена типа объекта складирования при инвентаризации "'+b.NameRU+'" -> "'+c.NameRU+'"'      
   from StorageObjects as a with (nolock)      
   join StorageUnits as b with (nolock) on b.tid = a.StorageUnit_id      
   join StorageUnits as c with (nolock) on c.tid = @StorageUnit_id and c.tid <> b.tid      
   where a.tid = @BoxStorageObject_id      
      
   update StorageObjects set StorageUnit_id = @StorageUnit_id      
   where tid = @BoxStorageObject_id and StorageUnit_id <> @StorageUnit_id      
  end      
      
  exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'NextMode',      
    @VariableValue = 'GetMaterial'      
        
  select [NEXTPROC] = @CurrentProc      
  return;      
 end      
      
 if @NextMode = 'GetMaterial'      
 begin      
       
  exec app_SetPDTReturn      
    @Session_id = @Session_id,      
    @ReturnMode = 'SetMaterial',      
    @ReturnProc = @CurrentProc      
          
  if isnull((      
   select IsFixed from StorageObjects with (nolock)      
   where tid = @StorageObject_id      
  ), 0) = 1      
  begin      
   set @Prompt = @LocationName      
  end else      
  begin      
   set @Prompt = dbo.ObjectName(@StorageObject_id)      
  end      
      
  delete from @terminalvariables      
        
  if @BoxStorageObject_id is null      
  begin      
      
   INSERT @terminalvariables (VariableName, VariableValue)      
   VALUES      
    ('MaterialIn.StorageObject_id',cast(@StorageObject_id as varchar(20))),      
    ('NextMode','SetMaterial');      
      
  end else      
  begin      
      
   INSERT @terminalvariables (VariableName, VariableValue)      
   VALUES      
    ('MaterialIn.StorageObject_id',cast(@BoxStorageObject_id as varchar(20))),      
    ('NextMode',NULL);      
      
   set @Prompt = @Prompt + '/' + dbo.ObjectName(@BoxStorageObject_id)      
  end      
      
  INSERT @terminalvariables (VariableName, VariableValue)      
  VALUES      
   ('MaterialIn.Title','ИНВЕНТАРИЗАЦИЯ'),      
   ('MaterialIn.Prompt',@Prompt),      
   ('MaterialIn.GivenMaterial_id',cast(@TaskMaterial_id as varchar(20))),      
   ('MaterialIn.IncomeTransaction_id',NULL);      
      
  EXEC [dbo].[pdt_SetTerminalVariables] @PDTGuid = @ThisGUID, @Variables = @terminalvariables;      
        
  select [NEXTPROC] = @StrategyProcedure      
  return;      
 end      
      
 if @NextMode = 'SetMaterial'      
 begin      
  exec pdt_GetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'MaterialIn.ExitKey',      
    @VariableValue = @ExitKey output      
          
  if @ExitKey = 'X'      
  begin      
   insert into @Rows (Row_id)      
   select tid from tbl_InventoryResult with (nolock)      
   where InventoryTask_id = @Task_id      
         
   delete from tbl_InventoryResult where tid in (select Row_id from @Rows)      
         
   select @Transaction_id = a.Transaction_id, @IsAutoTask = c.IsAutoTask, @IsSelf = a.IsSelf      
   from InventoryTasks as a with (nolock)      
   join hdr_ObjectInventory as b with (nolock) on a.Transaction_id = b.Transaction_id      
   join InventoryTypes as c with (nolock) on b.InventoryType_id = c.tid      
   where a.tid = @Task_id      
        
   if isnull(@IsAutoTask, 0) = 1      
   begin      
    update InventoryTasks set TaskDate = NULL, ProductionResource_id = NULL      
    where tid = @Task_id      
   end      
         
   if isnull(@IsSelf, 0) = 1      
   begin      
    delete from InventoryTasks where tid = @Task_id      
          
    update Transactions set TransactionStatus = 1      
    where tid = @Transaction_id      
   end      
         
   select [NEXTPROC] = 'pdt_Menu'      
   return;      
  end      
        
  if @ExitKey = '0'      
  begin      
         
   --set @TaskStorageObject_id = NULL      
         
   --select top 1      
   -- @TaskStorageObject_id = StorageObject_id      
   --from InventoryTasks with(nolock)      
   --where tid = @Task_id      
      
   ----Один объект - одна ячейка (глубина инвентаризации - ячейка, объект)      
   --if @TaskStorageObject_id is not NULL begin      
      
   -- exec tsk_SetInventoryJob      
   --   @Session_id = @Session_id,      
   --   @Task_id = @Task_id      
            
   -- select [NEXTPROC] = @InventoryTaskProc      
   -- return;      
   --end      
      
   if @BoxStorageObject_id is null      
   begin      
    if @Inventory_Type = 0      
    begin      
          
     exec tsk_SetInventoryJob      
       @Session_id = @Session_id,      
       @Task_id = @Task_id      
      
     select [NEXTPROC] = @InventoryTaskProc      
     return;      
    end else      
    begin      
     if  (select isnull(IsFixed,0) from StorageObjects with (nolock)      
      where tid = @TaskStorageObject_id) = 1 begin      
      exec tsk_SetInventoryJob      
        @Session_id = @Session_id,      
        @Task_id = @Task_id      
      
      select [NEXTPROC] = @InventoryTaskProc      
      return;      
     end else begin               
      exec pdt_SetTerminalVariable      
        @TerminalAddress = @TerminalAddress,      
        @VariableName = 'NextMode',      
        @VariableValue = 'GetStorageObject'      
     end      
    end      
   end else      
   begin      
         
    exec pdt_SetTerminalVariable      
      @TerminalAddress = @TerminalAddress,      
      @VariableName = 'NextMode',      
      @VariableValue = 'GetBottomOrMaterial'      
         
   end      
         
   select [NEXTPROC] = @CurrentProc      
   return;      
  end      
        
  if @StockCategory_id is null set @StockCategory_id = dbo.DefStockCategory()            
          
  select @Quantity = @BaseQuantity / UnitKoeff from MaterialUnits with (nolock)      
  where tid = @MaterialUnit_id      
      
  set @BottomStorageObject_id = NULL      
  if @BoxStorageObject_id is not null begin      
   set @BottomStorageObject_id = @StorageObject_id      
   set @MasterStorageObject_id = @BoxStorageObject_id      
  end else set @MasterStorageObject_id = @StorageObject_id      
      
  exec tsk_SetInventoryJob      
    @Session_id = @Session_id,      
    @Task_id = @Task_id,      
    @Location_id = @TaskLocation_id,      
    @StorageObject_id = @MasterStorageObject_id,      
    @Material_id = @Material_id,      
    @Quantity = @Quantity,      
    @MaterialUnit_id = @MaterialUnit_id,      
    @StockType_id = @StockType_id,      
    @StockCategory_id = @StockCategory_id,      
    @BarcodeObject_id = @BarcodeObject_id,      
    @MaterialWeight = @MaterialWeight,      
    @BottomStorageObject_id = @BottomStorageObject_id      
      
      
  if exists      
  (      
   select top 1 1 from InventoryTasks with(nolock)      
   where tid = @Task_id and Material_id is not null      
  )      
  begin      
[color=red]   exec tsk_SetInventoryJob      
     @Session_id = @Session_id,      
     @Task_id = @Task_id  [/color]    
      
   select [NEXTPROC] = @InventoryTaskProc      
   return;      
  end      
      
  exec app_SetPDTReturn      
    @Session_id = @Session_id,      
    @ReturnMode = 'SetMaterial',      
    @ReturnProc = @CurrentProc      
      
  exec pdt_SetTerminalVariable      
    @TerminalAddress = @TerminalAddress,      
    @VariableName = 'NextMode',      
    @VariableValue = NULL      
      
      
  select [NEXTPROC] = @StrategyProcedure      
  return;      
 end      
      
 select [NEXTPROC] = 'pdt_Terminate'      
end 


В итоге. По нажатии кнопки 0 происходить обнуление остатков.
Создается задание на инвентаризацию этой процедурой

exec tsk_SetInventoryJob
@Session_id = @Session_id,
@Task_id = @Task_id


ВЫделил красным для простоты.
Я думаю,что где-то в ней косяк.
Вот сама процедура создания задания tsk_SetInventoryJob

+
CREATE procedure [dbo].[tsk_SetInventoryJob] (  
 @Session_id int,  
 @Task_id int,  
 @Location_id int = NULL,  
 @StorageObject_id int = NULL,  
 @Material_id int = NULL,  
 @Quantity decimal(25,6) = NULL,  
 @MaterialUnit_id int = NULL,  
 @StockType_id int = NULL,  
 @StockCategory_id int = NULL,  
 @BarcodeObject_id int = NULL,   
 @MaterialWeight decimal(25,6) = NULL,  
 @BottomStorageObject_id int = NULL    
) as begin  
 declare @ProductionResource_id int  
 select @ProductionResource_id = ProductionResource_id from ProductionResourceSessions with (nolock)  
 where tid = @Session_id  
  
 declare @TaskLocation_id int, @TaskStorageObject_id int, @TaskMaterial_id int,   
   @Transaction_id int, @Row_id int  
 select  
 @TaskLocation_id = Location_id, @TaskStorageObject_id = StorageObject_id, @TaskMaterial_id = Material_id,   
 @Transaction_id = Transaction_id, @Row_id = Row_id  
 from InventoryTasks with (nolock)   
 where tid = @Task_id   
  
 declare @IsAutoCommit int  
 select @IsAutoCommit = isnull(c.IsAutoCommit, 0)   
 from tbl_ObjectInventory as a with (nolock)  
 join hdr_ObjectInventory as  b with (nolock) on a.Transaction_id = b.Transaction_id  
 join InventoryTypes as c with (nolock) on b.InventoryType_id = c.tid  
 where a.tid = @Row_id  
  
 if @Transaction_id is NULL goto terminate  
   
 if isnull(@Quantity, 0) = 0 begin  
  if (  
   select count(*) from tbl_InventoryResult with (nolock)  
   where InventoryTask_id = @Task_id  
  ) = 0 begin  
   insert into tbl_InventoryResult (Transaction_id, Row_id, ProductionResource_id, InventoryTask_id,  
   Location_id, StorageObject_id, Material_id)  
   values (@Transaction_id, @Row_id, @ProductionResource_id, @Task_id,  
   @TaskLocation_id, @TaskStorageObject_id, @TaskMaterial_id)  
  end  
    
  update InventoryTasks set FinishDate = getdate()  
  where tid = @Task_id  
    
  if @TaskStorageObject_id is not NULL begin  
   update StorageObjects set IsNeedCheck = 0  
   where tid = @TaskStorageObject_id  
  end  
   
  if isnull(@IsAutoCommit, 0) = 1 begin  
   exec app_ProcessInventoryTransaction  
     @Transaction_id = @Transaction_id  
  end  
 end else begin  
  insert into tbl_InventoryResult (Transaction_id, Row_id, ProductionResource_id, InventoryTask_id,  
  Location_id, StorageObject_id, Material_id, Quantity, MaterialUnit_id,  
  StockType_id, StockCategory_id, BarcodeObject_id, MaterialWeight, BottomStorageObject_id)  
  values (@Transaction_id, @Row_id, @ProductionResource_id, @Task_id,  
  @Location_id, @StorageObject_id, @Material_id, @Quantity, @MaterialUnit_id,  
  @StockType_id, @StockCategory_id, @BarcodeObject_id, @MaterialWeight, @BottomStorageObject_id)  
 end  
terminate:  
end  


Сообщение было отредактировано: 12 май 17, 14:42
12 май 17, 12:55    [20475988]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
Остается понять,какое именно значение не может система изменить и вот с этим проблема.И как отследить не пойму. Может быть try catch?
12 май 17, 12:57    [20475998]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
o-o
Guest
в этой процедуре НЕТ alter database.
если эта вызывает другие, ищите уже сами, в какой из них alter database
12 май 17, 13:08    [20476075]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
o-o,

Нашел единственную процедуру,в которой Alter Совпадает с временем ошибки.

+
CREATE procedure [dbo].[AsyncSQL]  
 @ChannelName varchar(100) = 'PLATO',  
 @SQL varchar(8000),  
 @Readers int = 3  
as   
begin  
 declare @DynamicSQL varchar(5000)  
 exec InitBroker  
  
 declare @MaxTasks int, @DialogGUID uniqueidentifier  
  
-- begin transaction  
 select top 1 @MaxTasks = MaxTasks, @DialogGUID = DialogGUID   
 from AsyncChannels with(nolock) /*(tablockx)*/ where ChannelName = @ChannelName  
  
 if @MaxTasks is NULL begin  
  set @MaxTasks = isnull(@Readers, 1)  
  
  insert into AsyncChannels (ChannelTitle, ChannelName,  
  ChannelDescription, MaxTasks) values (@ChannelName,  
  @ChannelName, 'Автоматически созданный однозадачный канал', @MaxTasks)  
 end  
-- commit transaction  
  
 declare @QueueName varchar(100), @FromServiceName varchar(100), @ToServiceName varchar(100), @ListenerName varchar(100)  
 set @QueueName = 'sq_' + @ChannelName  
 set @FromServiceName = 'fs_' + @ChannelName  
 set @ToServiceName = 'ts_' + @ChannelName  
 set @ListenerName = 'ls_' + @ChannelName  
  
 IF OBJECT_ID(@ListenerName, 'P') IS NULL  
 BEGIN  
  SET @DynamicSQL = dbo.fn_ListenerProcDefinition(@ChannelName, 1);  
  EXEC (@DynamicSQL);  
 END  
  
-- begin transaction  
 if (select count(*) from [sys].[objects] with(nolock) where [type] = 'SQ' and [name] = 'target_' + @QueueName) = 0 begin  
  set @DynamicSQL = 'create queue target_' + @QueueName  
  exec (@DynamicSQL)  
  set @DynamicSQL = 'alter queue target_' + @QueueName + ' with status = on, retention = off, activation (status = on, procedure_name = ' + @ListenerName + ', max_queue_readers = ' + cast(@MaxTasks as varchar(20)) + ', execute as self);'  
  exec (@DynamicSQL)  
 end  
  
 if (select count(*) from [sys].[objects] with(nolock) where [type] = 'SQ' and [name] = 'initiator_' + @QueueName) = 0 begin  
  set @DynamicSQL = '  
  create queue initiator_' + @QueueName + ';  
  create service ' + @FromServiceName + ' on queue initiator_' + @QueueName + ' (PlainContract);  
  create service ' + @ToServiceName + ' on queue target_' + @QueueName + ' (PlainContract);'  
  exec (@DynamicSQL)  
 end  
-- commit transaction  
  
 declare @IsDialogOpened bit  
 if @DialogGUID is NOT NULL begin  
  if (select count(*) from [sys].[conversation_endpoints] with(nolock)   
   where [conversation_handle] = @DialogGUID) = 0 set @IsDialogOpened = 0 else set @IsDialogOpened = 1  
 end else set @IsDialogOpened = 0  
  
 if @IsDialogOpened = 0 begin  
  set @DynamicSQL = '  
  declare @dialog_id uniqueidentifier;  
  begin dialog conversation @dialog_id  
  from service ' + @FromServiceName + ' to service ''' + @ToServiceName + ''', ''CURRENT DATABASE''  
  on contract PlainContract with encryption = off;  
  
  update AsyncChannels set DialogGUID = @dialog_id where ChannelName = ''' + @ChannelName + ''';  
  '  
  exec (@DynamicSQL)  
  
  select top 1 @DialogGUID = DialogGUID from AsyncChannels  with(nolock) where ChannelName = @ChannelName  
 end;  
  
-- insert into AsyncDialogLog ([SQL], ChannelName) values (@SQL, @ChannelName);  
  
 send on conversation @DialogGUID message type PlainText (@SQL);  
end  
  
  


Создание каналов для брокера

Сообщение было отредактировано: 12 май 17, 14:41
12 май 17, 13:19    [20476150]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
o-o
Guest
Максим Александровитч,
ALTER кого/чего совпадает?
у вас валится команда ALTER DATABASE, где она?
12 май 17, 13:29    [20476231]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
o-o,

В этой процедуре и в любой вложенной нет ALTER DATABASE
12 май 17, 13:35    [20476273]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
Но я нашел альтер в дин скуле
if (select count(*) from [sys].[objects] with(nolock) where [type] = 'SQ' and [name] = 'target_' + @QueueName) = 0 begin
set @DynamicSQL = 'create queue target_' + @QueueName
exec (@DynamicSQL)
set @DynamicSQL = 'alter queue target_' + @QueueName + ' with status = on, retention = off, activation (status = on, procedure_name = ' + @ListenerName + ', max_queue_readers = ' + cast(@MaxTasks as varchar(20)) + ', execute as self);'
exec (@DynamicSQL)

select *
from sys.objects
where type='SQ' and name like '%inve%'

Выдал строку создания канала для брокера для проведения инвентаризации.
target_sq_ProcessOperativeInventory

Можно как-то выловить ошибку ?
12 май 17, 13:37    [20476288]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7663
Максим Александровитч,

где вы видите это сообщение:
1/5069: ALTER DATABASE statement failed?
12 май 17, 13:43    [20476330]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
Все ошибки, которые выполняются через специальное ПО логируются этой процедурой.
Логируются в таблицу PDTErrors
Вот сстрока ошибки, о которой идет речь
tid TerminalAddress RecordDate ErrorMessage SQLStatement
384 PC-FAAF1B44-93F5F257 2017-05-12 13:32:12.507 1/5069: ALTER DATABASE statement failed. EXEC [dbo].[pdt_Inventory_Main] @Session_id = 14231, @TerminalAddress = 'PC-FAAF1B44-93F5F257'


+
CREATE PROCEDURE [dbo].[pdt_PDTClient_Next]  
 @TerminalAddress varchar(200),  
 @Session_id int,  
 @ProcedureName varchar(200)  
AS  
-- хп вызова следующей процедуры ТР  
BEGIN  
 SET NOCOUNT ON;  
  
 DECLARE @DynSQL varchar(MAX),  
  @Name varchar(200),  
  @Value varchar(200),  
  @Schema varchar(200),  
  @PDTGuid uniqueidentifier;    
  
 SET @Schema = ISNULL(PARSENAME(@ProcedureName, 2), SCHEMA_NAME());  
 SET @ProcedureName = PARSENAME(@ProcedureName, 1);  
  
    EXEC dbo.pdt_GetTerminalGUID  
        @TerminalAddress = @TerminalAddress,    
        @GUID = @PDTGuid OUTPUT;  
  
 SET @DynSQL = (  
  SELECT sr.name + ' = ' + VarVal.Val + ', '  
  FROM sys.procedures sp  
  JOIN sys.parameters sr on sr.object_id = sp.object_id  
  --OUTER APPLY dbo.pdt_TerminalVariables(@PDTGuid, sr.name) AS tv  
  OUTER APPLY (  
   SELECT tv.VariableName, tv.VariableValue  
   FROM dbo.TerminalVariables AS tv with (nolock)  
   WHERE tv.TerminalGuid = @PDTGuid  
    AND (replace('@' + tv.VariableName, '@@', '@') = sr.name)  
   ) AS tv  
  OUTER APPLY (SELECT CASE  
    WHEN sr.name = '@TerminalAddress' THEN '''' + @TerminalAddress + ''''  
    WHEN sr.name = '@Session_id' THEN CAST(@Session_id AS varchar(100))  
    ELSE ISNULL('''' + tv.VariableValue + '''', 'NULL')  
   END  
  ) AS VarVal(Val)  
  WHERE sp.name = @ProcedureName and OBJECT_SCHEMA_NAME(sp.object_id) = @Schema    
  FOR XML PATH('')  
 );  
  
  
 IF @DynSQL IS NULL  
  RETURN -1; -- что-то пошло не так  
  
 SET @DynSQL = 'EXEC [' + @Schema + '].[' + @ProcedureName + '] ' + @DynSQL;  
 SET @DynSQL = LEFT(@DynSQL, LEN(@DynSQL) - 1); -- убрать запятую  
  
    
 DECLARE @Rows TABLE (Row_id int NOT NULL)  
 DECLARE @Error varchar(max)  
  
 if 1=2 begin  --- Логирование сессий на терминалах (на эмуляторе не работает)  
  declare @NextMode varchar(250)  
  exec pdt_GetTerminalVariable  
    @TerminalAddress = @TerminalAddress,  
    @VariableName = 'NextMode',  
    @VariableValue = @NextMode output  
  /*  
  insert into PDTSessionLog ( Session_id, TerminalAddress, ProductionResource_id, LastActionDate, StoredProcedure, NextMode )  
  select a.tid, @TerminalAddress, a.ProductionResource_id, a.LastActionDate, left(@DynSQL,240), @NextMode  
  from ProductionResourceSessions as a with (nolock) where a.tid = @Session_id  
  */  
 end  
  
 BEGIN TRY  
  EXEC (@DynSQL)  
 END TRY  
 BEGIN CATCH  
  SET @Error = ISNULL(CAST(ERROR_LINE() AS varchar(20)), '-') + '/'   
   + ISNULL(CAST(ERROR_NUMBER() AS varchar(20)), '-') + ': ' + ERROR_MESSAGE()  
  
  INSERT INTO dbo.PDTErrors (TerminalAddress, ErrorMessage, SQLStatement)  
  OUTPUT inserted.tid INTO @Rows(Row_id)  
  VALUES (@TerminalAddress, @Error, @DynSQL)  
  
  SELECT [ERRORCODE] = Row_id + 1  
  FROM @Rows  
 END CATCH  
  
 RETURN 0  
END  
  
  
  


Сообщение было отредактировано: 12 май 17, 14:40
12 май 17, 13:56    [20476389]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
aleks2
Guest
1. Если вы уверены, что валится
EXEC [dbo].[pdt_Inventory_Main] @Session_id = 14231, @TerminalAddress = 'PC-FAAF1B44-93F5F257'
2. Открываем соединение в SSMS к серверу.
3. Создаем Новый запрос.
4. Копируем туды
begin transaction;
EXEC [dbo].[pdt_Inventory_Main] @Session_id = 14231, @TerminalAddress = 'PC-FAAF1B44-93F5F257';
rollback transaction;

5. Выбираем нужную базу.
6. Нажимаем Выполнить.
7. Если ошибка воспроизводится - есть предмет для разбираловки.
6. Иначе - соплежуйство.
12 май 17, 14:06    [20476439]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
aleks2,

Понял.Спасибо.Буду пробовать.
12 май 17, 14:11    [20476463]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
Ошибки нет, и показывает процедуру
NEXTPROC
[dbo].[pdt_Inventory_Task]

Означает,что в случае ошибки система откатывается назад к процедуре, из которой был вызов основной?
12 май 17, 14:16    [20476495]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36905
Модератор: Уважаемый Максим Александровитч! Потрудитесь, пожалуйста, упаковывать ваши бесконечные портянки кода в тег [spoiler], иначе буду стирать сообщения на месте.


Сообщение было отредактировано: 12 май 17, 14:44
12 май 17, 14:37    [20476603]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
aleks2
Guest
Максим Александровитч
Ошибки нет, и показывает процедуру
NEXTPROC
[dbo].[pdt_Inventory_Task]

Означает,что в случае ошибки система откатывается назад к процедуре, из которой был вызов основной?


Где "показывает"?
Вы бредите.
12 май 17, 15:19    [20476768]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
Максим Александровитч
Ошибки нет, и показывает процедуру
NEXTPROC
[dbo].[pdt_Inventory_Task]
Чего?
Вы запустили SSMS, там открыли редактор, вставили туда:
begin transaction;
EXEC [dbo].[pdt_Inventory_Main] @Session_id = 14231, @TerminalAddress = 'PC-FAAF1B44-93F5F257';
rollback transaction;
Запустили.

Внизу появилось окошко результата, и там текст:
NEXTPROC
[dbo].[pdt_Inventory_Task]

Вы можете скрин окна SSMS приложить?
12 май 17, 15:42    [20476892]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
alexeyvg
Вы можете скрин окна SSMS приложить?
А, ну да, это же вывод:
select [NEXTPROC] = @CurrentProc

Что то мне подсказывает, что дело в приложении.
Нужно профайлером смотреть.
12 май 17, 15:45    [20476903]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
invm
Member

Откуда: Москва
Сообщений: 9279
Максим Александровитч
Означает,что в случае ошибки система откатывается назад к процедуре, из которой был вызов основной?
Нет. Это, вероятнее всего, означает, что alter database выполняет ваше "специальное ПО" по собственной инициативе.
Оно же не полностью логирует ошибки, т.к. ошибка 5069 вторична, перед ней должна быть еще одна, показывающая из-за чего именно случился alter database failed.
12 май 17, 15:46    [20476908]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
Вот скриншот

К сообщению приложен файл. Размер - 114Kb
12 май 17, 16:20    [20477049]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
invm
Максим Александровитч
Означает,что в случае ошибки система откатывается назад к процедуре, из которой был вызов основной?
Нет. Это, вероятнее всего, означает, что alter database выполняет ваше "специальное ПО" по собственной инициативе.
Оно же не полностью логирует ошибки, т.к. ошибка 5069 вторична, перед ней должна быть еще одна, показывающая из-за чего именно случился alter database failed.


Я понимаю.Так вот весь вопрос в том,как же выцепить ошибку.
12 май 17, 16:21    [20477053]     Ответить | Цитировать Сообщить модератору
 Re: 1/5069: ALTER DATABASE statement failed  [new]
Максим Александровитч
Member

Откуда:
Сообщений: 506
alexeyvg,

Это идея.
12 май 17, 16:21    [20477055]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить