Не выполняется хранимая процедура

Написал хранимую процедуру, которая должна вставлять в таблицу БД данные из excel файла. Для этого был установлен поставщик Microsof.ACE.OLEDB.12.0 и создан связанный сервер.

Пытался вызвать ее сначала так:

var Params = System.CreateObject('TSObjectLibrary.Parameters');  
AddParameter(Params, pdtGUID, ID).Name = 'FileID';
AddParameter(Params, pdtGUID, Connector.CurrentUser.ContactID).Name = 'ContactID';      
var SQL = "exec spChangeTariffsMagistral :FileID, :ContactID";
Connector.DBEngine.ExecuteCustomSQL(SQL, Params);

Потом так:

var sqChangeTariffsMagistral = Services.GetNewItemByUSI('sq_ChangeTariffsMagistral');
sqChangeTariffsMagistral.Parameters.ItemsByName('FileID').Value = ID;
sqChangeTariffsMagistral.Parameters.ItemsByName('ContactID').Value = Connector.CurrentUser.ContactID;
var DatasetResult = sqChangeTariffsMagistral.Open();

Оба метода приводят к ошибке -
в первом случае:
TSMSSQLEngineLibrary.MSSQLEngine: Не удалось начать транзакцию для поставщика OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "LS_EXCEL".
Поставщик OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "LS_EXCEL" вернул сообщение "Уровень изолированности, а также его усиление не поддерживаются."

во втором случае:
"TSObjectLibrary.SelectQuery: Ошибка открытия источника данных "".
Оригинальное сообщение об ошибке: Не удалось начать транзакцию для поставщика OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "LS_EXCEL".
Поставщик OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "LS_EXCEL" вернул сообщение "Уровень изолированности, а также его усиление не поддерживаются.""

У меня много процедур, которые запрашивают данные из БД Oracle с помощью связанных серверов, и никогда никаких проблем не было.
Может что-то на своем сервере нужно подкрутить? Как заставить работать процедуру? В Менеджмент Студии работает без проблем.

Версия 3.0.2.244
Сервер MS SQL 2014

Нравится

15 комментариев

Я вот так делал, всё ок:

var SQL  = 'UPDATE tbl_Opportunity set ImportMark = null where ImportMark is not null and TypeID = ';
SQL += "'{A6CD76D6-E61F-4755-B223-AC33C68F870F}'";
var Parameters = System.CreateObject('TSObjectLibrary.Parameters');	
CreateSPParameter(Parameters, 'SQLText', pdtString, SQL);
var SQLCommand = 'execute [dbo].[tsp_UpdateWorkspaceRecords] :SQLText';
Connector.DBEngine.ExecuteCustomSQL(SQLCommand, Parameters);

У меня используется связанный сервер, у которого источник данных - excel файл. В процедуре этот файл выбирается из BLOB-а и сохраняется на диск. Даже простой селект приводит к указанной выше ошибке.

select * FROM [LS_EXCEL]...[Магистрали$]

При этом в студии всё работает.

Нашел что нужно изменить тип транзакции для ADO соединения:

AdoLoadUdl.IsolationLevel:=ilChaos

но это в Delphi. Есть ли возможность управлять этим из TSAdmin даже не знаю.

Можно попробовать в хранимке использовать

SET TRANSACTION ISOLATION LEVEL

https://technet.microsoft.com/en-us/library/ms189542(v=sql.105).aspx

не помогает...

мне кажется собака зарыта тут:

"MSDN" написал:
Если инструкция SET TRANSACTION ISOLATION LEVEL использовалась в хранимой процедуре или триггере, то при возврате управления из них уровень изоляции будет изменен на тот, который действовал на момент их вызова. Например, если уровень изоляции REPEATABLE READ устанавливается в пакете, а пакет затем вызывает хранимую процедуру, которая меняет уровень изоляции на SERIALIZABLE, при возвращении хранимой процедурой управления пакету, настройки уровня изоляции меняются назад на REPEATABLE READ.

SSV, мой совет - пишите на support@terrasoft.ru. Тут надо уточнять у спецов с ядра как быть.

наверное быстрее будет кодом в Террасофте прочитать экселевский файл, чем дождаться ответа от саппорта...

Здравствуйте.

Приложите листинг хранимой процедуры, пожалуйста.

В рабочей процедуре должен выполняться последний запрос с инсёртом, но не выполняется ни один, все приводят к ошибкам, которые были перечислены выше.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spChangeMagistral]
	@FileID uniqueidentifier,
	@ContactID uniqueidentifier
AS
DECLARE @BufLen int
DECLARE @BufPos int
DECLARE @Buffer varbinary(4096)
DECLARE @Stream int
DECLARE @HR int
DECLARE @Filename varchar(max)
declare @CurrentDate datetime
BEGIN
 
	SELECT @BufPos = 1, @BufLen = DATALENGTH(DataFile) FROM [dbo].tbl_FileTariffs WHERE ID = @FileID
	set @Filename = 'E:\TariffXLS\magistral.xlsx'
 
    EXEC @HR = sp_OACreate 'ADODB.Stream',@Stream OUT
    EXEC @HR = sp_OASetProperty @Stream,'Type',1
	EXEC @HR = sp_OASetProperty @Stream,'Mode',3
    EXEC @HR = sp_OAMethod @Stream,'Open'
 
    WHILE @BufLen > 0 BEGIN
        SELECT @Buffer = SUBSTRING(DataFile,@BufPos,4096) FROM [dbo].tbl_FileTariffs WHERE id = @FileID
        EXEC @HR = sp_OAMethod @Stream,'Write',null,@Buffer
        SELECT @BufLen = @BufLen - 4096, @BufPos = @BufPos + 4096
    END
 
    EXEC @HR = sp_OAMethod @Stream, 'SaveToFile',Null, @Filename, 2
	exec @Filename = sp_OAGetErrorInfo @HR
    EXEC @HR = sp_OAMethod @Stream, 'Close'
    EXEC @HR = sp_OADestroy @Stream
 
	set @CurrentDate = GETDATE()
 
        --select * FROM [Link_EXCE]...[Магистрали$]
 
	--select * from openquery(Link_EXCEL,' select * from [Магистрали$]')
 
	--select f1, f2, f3, f4*1000, f5, f26, cast(f27 as decimal(18,2)), f28, f29, f30, f31, f32, f33 FROM [Link_EXCEL]...[Магистрали$] where f1 is not null and f2 is not null and f1 <> 'Название';
 
	--SELECT * FROM OpenrowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\TariffXLS\magistral.xlsx;','SELECT * FROM [Магистрали$]') 
 
	--SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','E:\TariffXLS\magistral.xlsx;Extended Properties=EXCEL 12.0')...[Магистрали$]
 
	/*
	insert into tbl_LineFiberTariffs_TEST (id,CreatedOn, CreatedByID, ModifiedOn, ModifiedByID,
	Name,Address1,Address2,LengthLine,ProjectNumber,CountFiberID,SaleLine,ServiceLine,RentOneTime10year,RentMonth10year,RentOneTimeOperator,
	RentMonthOperator,RentOneTimeCorp,RentMonthCorp,BeginDate,EndDate, FileID)
	SELECT newid(),@CurrentDate,@ContactID,@CurrentDate,@ContactID, f1, f2, f3, f4*1000, f5, fc.ID, f26, 
	cast(f27 as decimal(18,2)), f28, f29, f30, f31, f32, f33, @CurrentDate, null, @FileID
	FROM [Link_EXCEL]...[Магистрали$] ls
	left outer join tbl_FiberCount fc on fc.CountValue = cast(ls.f6 as varchar(20))
	where f1 is not null and f2 is not null and f4 is not null
	*/
END

а вы не пробовали первую часть (с редактированием файла) вынести в отдельную процедуру от второй части (с чтением и инсертом)?
другими словами не блокирует ли ADODB.Stream файл при работе через него? все-таки у вас разные библиотеки используются в разных частях процедуры (во второй - OLEDB)

"Андросов Дмитрий" написал:

а вы не пробовали первую часть (с редактированием файла) вынести в отдельную процедуру от второй части (с чтением и инсертом)?

другими словами не блокирует ли ADODB.Stream файл при работе через него? все-таки у вас разные библиотеки используются в разных частях процедуры (во второй - OLEDB)


я оставлял в процедуре только один запрос, в смысле в процедуре был вообще только это запрос, без выгрузки файла из БД:

select * FROM [Link_EXCE]...[Магистрали$]

т.е. файл уже лежал на диске, но ошибки всё те же...

SSV, предлагаю искать другие пути. Воспользоваться стандартным импортом с екселя, например, либо написать свою функцию по парсингу Excel определенного формата.

я вынес часть процедуры, которая инсёртит данные, в JOB, который запускается из процедуры с помощью sp_start_job...

Да, хорошая идея!

Показать все комментарии