Выполнение кода на стороне SQL Server

[ansoft]

Цитата:

Коллеги, вы когда в ManagementStudio делаете Select Top 1000 Rows из какой-нибудь базы, у вас какой контекст БД используется?... правильный ответ "master"
То есть подключаясь к одной БД, получить данные из другой нет вообще никаких проблем, кроме прав. Аналогичная ситуация и с хранимками.

Я обычно переключаюсь на нужную БД, чтобы не добавлять префикс БД, иначе объект в БД не будет найден.

P.S. Видно потому и забыл напрочь про этот префикс

[dech]

Добрый вечер, уважаемые коллеги.
Разобрался с этой темой, но не до конца. В хранимой процедуре обращаюсь к другой БД, извлекаю из нее данные, читаю в AX следующим методом:

public static server void testExport()
{
    Connection          conn;
    Statement           stmt;
    str                 expr = "EXEC [dbo].[usp_DenisTest]";
    ResultSet           rs;
    SqlStatementExecutePermission   perm = new SqlStatementExecutePermission(expr);
    ;

    conn = new Connection();

    perm.assert();

    stmt = conn.createStatement();

    // BP deviation documented
    rs = stmt.executeQuery(expr);

    while (rs.next())
    {
        info(rs.getString(1));
    }

    rs.close();

    CodeAccessPermission::revertAssert();
}

Текст хранимой процедуры - следующий:

USE [PRD]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_DenisTest]
AS
BEGIN
SELECT ProdId, WrkCtrId, ItemId FROM [CSF_PRD].[dbo].[SFMatl] AS SFMatl
WHERE SFMatl.CompanyId = 'dat'
  AND SFMatl.WrkCtrId = 'E01'
  AND SFMatl.PrOdId = 'W000004'

END

Главное - проставить в SQL Server разрешения на запуск нужной SP для AOS-аккаунта. Все отлично работает.
Но стоит только настроить процедуру на работу с временными таблицами, как возникает ошибка такого рода: SQL error description: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
Вот текст процедуры, использующей временную таблицу:

USE [PRD]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_DenisTest2]
AS
BEGIN

SET NOCOUNT ON

CREATE TABLE #TempCustTable (
        AccountNum varchar(10) COLLATE Latin1_General_CI_AI
      , CustName varchar(50) COLLATE Latin1_General_CI_AI
)

INSERT INTO #TempCustTable (AccountNum, CustName)
SELECT AccountNum, Name FROM CustTable
WHERE CustTable.DataAreaId = 'dat'
  AND CustTable.Name LIKE 'S%'
  
SET NOCOUNT OFF

SELECT * FROM #TempCustTable

DROP TABLE #TempCustTable

END

Также пробовал менять SET NOCOUNT, но как видите, особого результата не добился.
Может кто-нибудь подсказать, как мне использовать временные таблицы на стороне SQL Server, чтобы можно было нормально прочитать выборку в AX?
P.S. Спешу напомнить, что мне обязательно нужно обратиться к другой базе, поэтому использую хранимые процедуры.

[ansoft]

Цитата:

DROP TABLE #TempCustTable

Уберите... SQL сам ее почистит при закрытии сессии, а так вы ее убиваете и хотите с нее данные брать

Последней строкой хранимой процедуры должна быть

Цитата:

SELECT * FROM #TempCustTable

[Владимир Максимов]

Цитата:

Сообщение от dech

Но стоит только настроить процедуру на работу с временными таблицами, как возникает ошибка такого рода: SQL error description: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
Вот текст процедуры, использующей временную таблицу:

BEGIN

SET NOCOUNT ON

(...)  

SET NOCOUNT OFF

(...)

END

Также пробовал менять SET NOCOUNT, но как видите, особого результата не добился.
Может кто-нибудь подсказать, как мне использовать временные таблицы на стороне SQL Server, чтобы можно было нормально прочитать выборку в AX?

SET NOCOUNT ON надо устанавливать один раз. В начале процедуры. И больше ее не трогать. Она влияет только и исключительно на некие служебные сообщения. Некий "отчеты" об успешности или не успешности выполнения той или иной команды. Подавляет "статистику" выполнения команды. Вывод результатов прямых команд SELECT-SQL - не подавляет

Вы сами спровоцировали ошибку снова включив ее перед выполнением служебной команды DROP TABLE. Т.е. завершающая команда DROP TABLE #TempCustTable при настройке SET NOCOUNT OFF привела к формированию некоего "мусора" который и вызвал ошибку ODBC.

PS: Правилом хорошего тона считается предварительно проверить факт существования таблицы как перед ее созданием, так и перед ее удалением. Т.е. обычно пишут так

begin
SET NOCOUNT ON

if object_id('Tempdb..#TempCustTable') is not null DROP TABLE #TempCustTable

create table #TempCustTable (...)

insert ...
select ...

if object_id('Tempdb..#TempCustTable') is not null DROP TABLE #TempCustTable
end

Цитата:

Сообщение от dech

P.S. Спешу напомнить, что мне обязательно нужно обратиться к другой базе, поэтому использую хранимые процедуры.

Для этого хранимая процедура не обязательно. Вы вполне можете добавить обращение к другой базе в прямом запросе. Т.е. "FROM [CSF_PRD].[dbo].[SFMatl]" можно написать напрямую в команде передаваемой в stmt.executeQuery()

[dech]

Ок, вот готовая процедура, которая у нас уже довольно давно используется в SSRS отчете:

USE [PRD]
GO
/****** Object:  StoredProcedure [dbo].[usp_ReportEOLYield]    Script Date: 08/16/2013 05:24:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_ReportEOLYield]
--DECLARE
        @StartDate DATETIME
      , @EndDate DATETIME
      , @WorkCenters VARCHAR(MAX)
      , @Shifts VARCHAR(MAX)
AS
BEGIN
--SET @StartDate = '2012-09-23'
--SET @EndDate = '2012-10-23'
--SET @WorkCenters = 'V01,V02'
--SET @Shifts = 'A,B,C,D'

--SET @StartDate = '2012-04-01 21:00:00'
--SET @EndDate = '2012-04-01 22:00:00'
--SET @WorkCenters = 'E02,E03,E04'
--SET @Shifts = 'A,B,C,D'

DECLARE @WorkCenterTable TABLE(ListItem VARCHAR(100) COLLATE Latin1_General_CI_AI)
DECLARE @ShiftTable TABLE(ListItem VARCHAR(100) COLLATE Latin1_General_CI_AI)
INSERT INTO @WorkCenterTable SELECT ListItem FROM QALab.[dbo].[SplitListItemsIntoTable](@WorkCenters)
INSERT INTO @ShiftTable SELECT ListItem FROM QALab.[dbo].[SplitListItemsIntoTable](@Shifts)

SET NOCOUNT ON

IF OBJECT_ID('Temp_DB..#EOLTable') IS NOT NULL DROP TABLE #EOLTable
	
CREATE TABLE #EOLTable(
      WorkCenter varchar(10) COLLATE Latin1_General_CI_AI
      , Prod varchar(20) COLLATE Latin1_General_CI_AI
      , Item varchar(20) COLLATE Latin1_General_CI_AI
      , Job varchar(20) COLLATE Latin1_General_CI_AI
      , Serial varchar(20) COLLATE Latin1_General_CI_AI
      , FeedStock varchar(20) COLLATE Latin1_General_CI_AI
      , Date datetime
      , Qty numeric(28,12)
      , UOM varchar(15) COLLATE Latin1_General_CI_AI
      , Scrap numeric(28,12)
      , ScrapCode varchar(20) COLLATE Latin1_General_CI_AI
      , Descript varchar(50) COLLATE Latin1_General_CI_AI
      , WrkCtrGroup varchar(10) COLLATE Latin1_General_CI_AI
      , InventTransId varchar(20) COLLATE Latin1_General_CI_AI
)

INSERT INTO #EOLTable(WorkCenter, Prod, Item, Job, Serial, Date, Qty, UOM, Scrap, ScrapCode,
	Descript, WrkCtrGroup,
	InventTransId)
SELECT SFMatl.WrkCtrId
	, ProdTable.DIMENSION2_
	, RTRIM(CASE WHEN SFMatl.ItemId LIKE 'Waste%' THEN '' ELSE SFMatl.ItemId END)
		+ CASE(RTRIM(ColorId)) WHEN '' THEN '' ELSE '-' + RTRIM(SizeId) + '-' + RTRIM(ColorId) END
	, UPPER(RTRIM(SFMatl.ProdId))
	, ppoSerialNum
	, ShiftTime
	, CASE WHEN SFMatl.WasteId <> '' AND TranType = 'OFF-WST' OR SFMatl.ItemId LIKE 'Waste%' THEN 0 ELSE StkQty END
	, StkUnit
	, CASE WHEN SFMatl.WasteId <> '' AND TranType = 'OFF-WST' OR SFMatl.ItemId LIKE 'Waste%' THEN StkQty ELSE 0 END
	, CASE WHEN SFMatl.ItemId LIKE 'Waste%' AND SFMatl.WasteId = '' THEN SFMatl.ItemId ELSE SFMatl.WasteId END
	, WasteCaption
	, WrkCtrTable.WrkCtrGroupId
	, SFMatl.InventTransId
FROM CSF_PRD.dbo.SFMatl SFMatl WITH (NOLOCK)
INNER JOIN CSF_PRD.dbo.WasteId WasteId
   ON WasteId.WasteId = SFMatl.WasteId -- Old behaviour: CASE WHEN ItemId LIKE 'Waste%' AND SFMatl.WasteId = '' THEN ItemId ELSE SFMatl.WasteId END
INNER JOIN ProdTable
   ON ProdTable.DataAreaId = SFMatl.CompanyId
  AND ProdTable.ProdId = SFMatl.ProdId
INNER JOIN WrkCtrTable
   ON WrkCtrTable.DataAreaId = ProdTable.DataAreaId
  AND WrkCtrTable.WrkCtrId = SFMatl.WrkCtrId
WHERE
      SFMatl.CompanyId = 'clt'
	  AND ProdTable.DIMENSION3_ = 'FSM'
      AND RTRIM(ColorId) = ''
      AND SFMatl.TranType IN ('OFF-FIN','OFF-WST')
      AND ShiftTime >= @StartDate
      AND ShiftTime < @EndDate
	  AND WrkCtrTable.WrkCtrGroupId NOT IN ('FSMSLIT','FSMREW','FSMDEP2','FSMDEP1','CCDFSMSLIT','CCDFSMREW','CCDFSMDEP2','CCDFSMDEP1')
	  AND [dbo].[udfGetShift] (ShiftTime) IN (SELECT ListItem FROM @ShiftTable)
      AND SFMatl.WrkCtrId IN (SELECT ListItem FROM @WorkCenterTable)
      AND SerialNum IN (SELECT Matl.SerialNum FROM CSF_PRD.dbo.SFMatl AS Matl
						WHERE Matl.ContainerNum = SFMatl.ContainerNum
						  AND Matl.ppoSerialNum = SFMatl.ppoSerialNum
						  AND Matl.PrOdId = SFMatl.PrOdId
						GROUP BY Matl.SerialNum
						HAVING SUM(Matl.StkQty) <> 0);

WITH MyCTE(Item, SerialNum) AS 
	(SELECT pmtOn.Item, pmtOff.SerialNum FROM ppoProdMatlTrace pmtOff WITH (NOLOCK) 
		INNER JOIN ppoProdMatlTrace pmtOn WITH (NOLOCK) 
		   ON pmtOff.DataAreaId = pmtOn.DataAreaId 
		  AND pmtOff.PrOdSet = pmtOn.PrOdSet 
		  AND pmtOff.Ply = pmtOn.MakingPly 
		  AND pmtOff.Type = 0 
		  AND pmtOn.Type = 1
		WHERE pmtOff.DataAreaId = N'clt'
		  AND pmtOff.SERIALNUM IN (SELECT DISTINCT SERIAL FROM #EOLTable))
		  
UPDATE #EOLTable
SET FeedStock = (SELECT MAX(Item) FROM MyCTE
					WHERE MyCTE.SerialNum = Serial)
	, Item = CASE WHEN Item = '' OR LEFT(Item, 1) = '-' THEN 
		(SELECT MAX(ItemId) FROM InventTrans 
			WHERE InventTrans.DataAreaId = N'clt' 
			  AND InventTrans.InventTransId = #EOLTable.InventTransId) + Item
		ELSE Item END

SELECT * FROM #EOLTable

--DROP TABLE #EOLTable
END

Мне нужно всего-то добавить еще 1 параметр и пару полей во временную таблицу для вывода новых данных. И все это дело выгружать в эксель. Но никак не получается вызвать её правильно.

[Владимир Максимов]

А в чем проблема-то? Для начала получается ли вызывать эту процедуру "как есть"? Без модификаций.

Вот тестовый пример, который нормально отрабатывает. Реализован как метод run() некоего тестового класса, который запускается на стороне сервера. Напомню, что Job запускается на клиенте, поэтому для целей тестирования - не годится (ну, или нужно делать пункт меню, запускающий Job, чтобы у этого пункта меню RunOn = server)

void run()
{
    Connection  con = new Connection();
    Statement   stmt = con.createStatement();
    ResultSet   r;
    str         sql;
    SqlStatementExecutePermission perm;
    ;
 
    sql =   'set nocount on;' +
            ' if object_id(\'tempdb..#test\') is not null drop table #test;' +
            ' create table #test (f1 int, f2 varchar(10));' +
            ' insert into #test values (1,\'test1\');' +
            ' insert into #test values (2,\'test2\');' +
            ' update #test set f2 = \'test11\' where f1=1;' +
            ' select * from #test;' +
            ' if object_id(\'tempdb..#test\') is not null drop table #test;' +
            '';
 
    // Set code access permission to help protect the use of
    // Statement.executeUpdate.
    perm = new SqlStatementExecutePermission(sql);
    perm.assert();
 
    try
    {
        r = stmt.executeQuery(sql);
        while (r.next())
        {
            print r.getString(2);
        }
    }
    catch (exception::Error)
    {
        print "An error occured in the query.";
    }
    pause;
    // Code access permission scope ends here.
    CodeAccessPermission::revertAssert();
}

Как видно, в этом примере есть и модификации до собственно выборки и удаление таблицы после выборки. И нечего. Вполне себе нормально работает...

[dech]

Спасибо, разобрался в чем дело. По недосмотру, SET NOCOUNT ON был поставлен не в самое начало. До него были 4 команды, из-за которых возникала ошибка:

DECLARE @WorkCenterTable TABLE(ListItem VARCHAR(100) COLLATE Latin1_General_CI_AI)
DECLARE @ShiftTable TABLE(ListItem VARCHAR(100) COLLATE Latin1_General_CI_AI)
INSERT INTO @WorkCenterTable SELECT ListItem FROM QALab.[dbo].[SplitListItemsIntoTable](@WorkCenters)
INSERT INTO @ShiftTable SELECT ListItem FROM QALab.[dbo].[SplitListItemsIntoTable](@Shifts)

Теперь все отлично работает.

[Товарищ ♂uatr]

Добрый день!
Подскажите, пожалуйста, есть временная таблица (tempdb) формируемая прямым SQL запросом (в целом, она необходима для формирования данных в SSRS отчете).
В Аксапте содержатся метаданные таблицы, в БД содержатся сами данные.
Вопрос состоит в том, чем получить данные в Аксапте (например, для формы)? Знаю, что при работе с таблицами типа tempdb используется метод linkPhysicalTableInstance принимающий курсор, проблема в получении этого самого курсора.

[AndyD]

Вы несколько не с той стороны подходите))

Вам надо:
1. Определить табличную переменную в Аксапте
2. Создать через эту табличную переменную запись (при этом происходит физическое создание таблицы в базе tempDb на сервере).
3. Передать на сторону SQL имя таблицы, полученное с помощью метода [табличная переменная].getPhysicalTableName().
4. По этому имени любым доступным на сиквеле способом создать записи в таблице через tempdb.dbo.[полученное из Аксапты имя таблицы] (на забывая удалить запись, созданную в п. 2)
5. После возвращения в Аксапту - можно простыми while select пройти по созданным в сиквеле записям, либо передать табличную переменную дальше на обработку - прилинковать к курсору датасорса, например, через вызов linkPhysicalTableInstance()

[Товарищ ♂uatr]

Цитата:

Сообщение от AndyD

Вы несколько не с той стороны подходите))

Спасибо! В П.2 можно выбрать запись, при этом действии таблица тоже создается.