none
SET FMTONLY OFF Descontinuado E substituído por procedures de sistemas que não resolvem o problema no uso com EF6 RRS feed

  • Pergunta

  • Olá,

    Em desenvolvimento de um sistema corporativo, me deparei com um problema que sinceramente não deveria existir.

    Estou envolvido num projeto que utiliza Entity Framework 6, com acesso a stored procedures num banco de dados Sql Server 2012.

    Por questões inerentes ao EF6, em algumas situações, não consigo obter as estruturas ditas Complex Types de retorno das procedures, e pesquisando na internet, descobri o recurso SET FMTONLY OFF, que permite ao Entity Framework enxergar as estruturas complexas retornadas, permitindo que eu acesse essas estruturas via código.

    Mas, ao mesmo tempo, no site da Microsoft que explica o recurso, existe um comentário que informa que esse recurso foi descontinuado, e que em lugar dele, foram criadas 4 novas procedures de sistemas, conforme texto copiado do site :

    <token space="preserve" style="box-sizing:border-box;">Observação</token>


    Não use este recurso. <sentence data-original-title="" data-source="This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)." data-source-localized="0" style="box-sizing:border-box;" title="">Esse recurso foi substituído pelo sp_describe_first_result_set (Transact-SQL),sp_describe_undeclared_parameters (Transact-SQL)DM exec_describe_first_result_set (Transact-SQL), e DM exec_describe_first_result_set_for_object (Transact-SQL).</sentence>

    Pois bem, muitas das procedures usadas pelo sistema que estou envolvido, utilizam internamente tabelas temporárias, e se observarmos na descrição de erros dessas procedures, veremos que todas não aceitam procedures que usem tabelas temporárias.

    Portanto, qual caminho seguir ? A Microsoft descontinua e não indica o uso desse recurso em produção, e informa que agora devemos passar a usar essas procedures, mas todas não atendem a uma necessidade que o recurso anterior atendia com 100% de sucesso.

    Portanto, venho aos senhores fazer as seguintes perguntas:

    1 - Não posso mesmo mais usar o recurso SET FMTONLY OFF no meu sistema ?

    2 - Cheguei a implementar o comando nas procedure, no banco SQL Server 2012, e funcionou. Mesmo assim, não posso mais usar ou posso usar, mas com resalvas ?

    3 - O que vai acontecer se eu mantiver o recurso e o banco sofrer updates ?

    4 - Como usar um recurso que permita o EF6 enxergar os Complex Types das procedures, nos casos onde temos procedures que retornam resultados sobre tabelas temporárias ou mesmo que manipulam tabelas temporárias internamente ?

    Para ilustrar o problema, encontrei esse site https://connect.microsoft.com/SQLServer/feedback/details/782038/set-fmtonly-deprecated-in-sql-2012-without-full-replacement , que menciona que o problema já havia sido detectado e aberto um pedido de correção, mas que a Microsoft fechou o chamado e não corrigiu o bug, alegando não era prioridade.

    Embora o site seja antigo, pelas minhas pesquisas, não tenho achado nada que diga o contrário.

    Ou seja, um recurso que funciona é descontinuado, os recursos substitudos não resolvem o problema, e a empresa vem alegar que não é prioridade fazer esse ajuste.... e como ficam os milhões de usuários que usam a plataforma MS (Asp.Net + EF + SQL Server + Procedures) ???

    Atenciosamente,


    Olivertech




    sexta-feira, 31 de março de 2017 16:49

Todas as Respostas

  • Maclau,

    Sim você pode usar mantendo o nível de compatibilidade do seu banco de dados na versão 110 e até que o SQL Server 2012 mantenha em uso e suportado pela Microsoft.

    Mas não seria o caso de ao invés de usar temp table, implementar estas tabelas como auxiliares em seu ambiente?


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    domingo, 2 de abril de 2017 01:18
  • Olá Junior,

    Estou tendo enorme dificuldade em fazer o EF6 se entender com as procedures do sistema que estamos desenvolvendo.

    A empresa tem um enorme legado e algumas procedures são extensas e tem muita lógica interna.

    Abaixo, listo uma das procedures que não consigo obter a lista de retorno. O resultado desta procedure do exemplo, pode ser um único número (no formato de varchar) ou uma lista de números (todos como varchar), conforme pode ser visto na imagem em anexo.

    Só que se tendo acessar a procedure pelo EF, me retorna uma lista de um único elemento com NULL.

    Abaixo a procedure -  reparar que tem um trecho logo no inicio (com parte comentada) que tentei fazer alguns artifícios para tentar fazer reconhecer o retorno da procedure, conseguindo identificar o Complex Type. Em todos os casos, eu não consigo obter o retorno. Eu consigo reconhecer o Complex Type, mas a procedure não retorna o que deveria.

    ALTER PROCEDURE [dbo].[usp_NSC_DistribuiNumerosDaSorteOtimizadoPorCarga]
    ----------------------------------------------------------------------------------------------
    -- P a r a m e t r o s
    ----------------------------------------------------------------------------------------------
        @DtCompetencia VARCHAR(6) = NULL,
        @QtdNumerosDaSorte INT = NULL,
        @TamanhoSerie INT = 100000,
        @SerieId INT = NULL
    AS
        BEGIN
    
            SET NOCOUNT ON;
    
    		---------------------------------------------------------------------------
    		-- Declarando + Dummy Query para permitir o EF obter o Complex Data Type
    		---------------------------------------------------------------------------
    		--IF (1=0) 
    		--BEGIN
    			SET FMTONLY OFF;
    		--	DECLARE @TabelaResultado TABLE (
    		--		Numero VARCHAR(5) NOT NULL
    		--	);
    		--	SELECT Numero FROM @TabelaResultado WHERE (1=0)
    		--END
    
    ----------------------------------------------------------------------------------------------
    --	D e c l a r a ç ã o    d e    V a r i á v e i s
    ----------------------------------------------------------------------------------------------
    		IF OBJECT_ID('tempdb..#valoresCarga') is not null
    			DROP TABLE #valoresCarga
    		IF OBJECT_ID('tempdb..#valoresCargaPorNumero') is not null
    			DROP TABLE #valoresCargaPorNumero
    
    		CREATE TABLE #valoresCarga (
    			[Id] INT IDENTITY(1,1),
    			[NumeroProcesso] VARCHAR(20),
    			[SerieId] INT,
    			[Numero] INT,
    			[Carga] NUMERIC(19, 10),
    			[Multiplo] NUMERIC(19, 10),
    			[Subtotal] NUMERIC(19, 10)
    		)
    		CREATE NONCLUSTERED INDEX ind_vc_1 ON #valoresCarga (Numero)
    		CREATE NONCLUSTERED INDEX ind_vc_2 ON #valoresCarga (SerieId,Numero)
    
    		CREATE TABLE #valoresCargaPorNumero (
    			Id INT IDENTITY(1,1),
    			Numero INT,
    			Carga NUMERIC(16, 10)
    		)
    		CREATE NONCLUSTERED INDEX ind_vcn_1 ON #valoresCargaPorNumero (Numero)
    		
    		DECLARE @TabelaResultado TABLE (
    			Numero VARCHAR(5) NOT NULL
    		)
    		
    		DECLARE @qtdResultado INT
    
    ----------------------------------------------------------------------------------------------
    --	L ó g i c a     P r i n c i p a l
    ----------------------------------------------------------------------------------------------
    
            BEGIN TRY
    
    	-- Busca o valor da competencia aberta 
                IF (@DtCompetencia is null)
    				SELECT TOP 1 @DtCompetencia = DT_COMPETENCIA
    				FROM RECEITAS (NOLOCK)
    				WHERE Fechado = 0
    
    	-- Preenche as tabelas temporárias com os dados 
    			INSERT INTO #valoresCarga
    			SELECT pr.NR_PROCESSO, ser.Id as SERIE_ID, num.NUMERO, left(nrval.Vlr_Original,8) as Carga, left(msor.Multiplo,8) as Multiplo,
    				 left((nrval.Vlr_Original * msor.Multiplo), charindex('.', (nrval.Vlr_Original * msor.Multiplo)) - 1) as Subtotal
    			FROM SERIES ser
    			INNER JOIN NRVALIDOS nrval WITH (NOLOCK) on ser.Id = nrval.SER_ID and ser.DT_COMPETENCIA = @DtCompetencia
    			INNER JOIN SERIE_DISTRIBUICOES sed WITH (NOLOCK) on ser.SED_ID = sed.id
    			INNER JOIN PLANOS pla WITH (NOLOCK) on sed.PLANO_ID = pla.Id
    			INNER JOIN CfgPlano cfpla with (NOLOCK) on pla.id = cfpla.IdPlano and cfpla.ESerieFechada =0
    			INNER JOIN PROCESSOS pr WITH (NOLOCK) on pla.PROC_ID = pr.Id 
    			INNER JOIN PROCESSOS_MULTIPLOS msor WITH (NOLOCK) on pr.Id = msor.PROC_ID
    			INNER JOIN HISTORICO_PROCESSOS hpro WITH (NOLOCK) on pr.Id = hpro.PROC_ID and hpro.SorteioProprio = 'N'
    			RIGHT JOIN NUMEROS num on num.NUMERO = nrval.NUMERO
    			WHERE	/*(pr.NR_PROCESSO IS NULL
    					 OR pr.NR_PROCESSO = ISNULL(@NumeroProcesso, pr.NR_PROCESSO))
    			AND		*/num.NUMERO < @TamanhoSerie
    
    			INSERT INTO #valoresCargaPorNumero
    			SELECT a.Numero, sum(isnull(b.Subtotal, 0)) as Carga
    			FROM #valoresCarga a
    			LEFT JOIN #valoresCarga b on a.id = b.id
    			GROUP BY a.Numero
    			
    	-- Obtém '@QtdNumerosDaSorte' números da sorte aleatórios com menor carga para uma série determinada ou não por parâmetro
    			IF(@SerieId IS NOT NULL)
    			BEGIN			
    				INSERT INTO @TabelaResultado
    				SELECT TOP (ISNULL(@QtdNumerosDaSorte, 1)) num.Numero
    				FROM #valoresCarga vc
    				JOIN #valoresCargaPorNumero vcn on vc.Numero = vcn.Numero
    				RIGHT JOIN NUMEROS num on num.Numero = vcn.Numero
    				WHERE num.NUMERO < @TamanhoSerie
    				AND num.NUMERO NOT IN (SELECT	Numero
    									   FROM		#valoresCarga
    									   WHERE	SerieId = ISNULL(@SerieID, vc.SerieId))
    				GROUP BY num.Numero, vcn.Carga
    				ORDER BY vcn.Carga asc, newid()
    			END
    			ELSE
    			BEGIN
    				INSERT INTO @TabelaResultado
    				SELECT TOP (ISNULL(@QtdNumerosDaSorte, 1)) cn.Numero
    				FROM #valoresCargaPorNumero cn
    				ORDER BY cn.Carga asc, newid()
    			END
    			
    			SELECT @qtdResultado = COUNT (*)
    			FROM @TabelaResultado
    
    	-- Exibe o resultado da procedure
    			IF (@qtdResultado = ISNULL(@QtdNumerosDaSorte, 1))
    				SELECT RIGHT(CASE 
    								WHEN @TamanhoSerie = 1000 THEN '000'
    								WHEN @TamanhoSerie = 10000 THEN '0000'
    								WHEN @TamanhoSerie = 100000 THEN '00000'
    							 END + CONVERT(VARCHAR(5), Numero), 5) AS Numero
    				FROM @TabelaResultado
    			ELSE
    				SELECT -1
    
            END TRY	
    
    --------------------------------------------------------------------------------------------
    -- T r a t a m e n t o     d e     E r r o 	
    --------------------------------------------------------------------------------------------
    
            BEGIN CATCH
    
                SELECT
    					--ERROR_PROCEDURE() AS ErrorProcedure,
                        --ERROR_LINE() AS ErrorLine,
                        --ERROR_NUMBER() AS ErrorNumber,
                        ERROR_MESSAGE() AS ErrorMessage
                        --ERROR_SEVERITY() AS ErrorSeverity,
                        --ERROR_STATE() AS ErrorState;
    
            END CATCH
    
        END

    Abaixo, o meu código usado pra chamar a procedure :

            public List<string> DistribuiNumerosDaSorteOtimizadoPorCarga(NSC_DistribuiNumerosDaSorteOtimizadoPorCargaDTO dto)
            {
                try
                {
                    List<string> result = new List<string>();
    
                    List<NumerosDaSorte_ResultDTO> procResult = _context.Database.SqlQuery<NumerosDaSorte_ResultDTO>("usp_NSC_DistribuiNumerosDaSorteOtimizadoPorCarga " +
                                                                         "@DtCompetencia, " +
                                                                         "@QtdNumerosDaSorte, " +
                                                                         "@TamanhoSerie, " +
                                                                         "@SerieId",
                                                        new SqlParameter("DtCompetencia", (object)dto.DtCompetencia ?? DBNull.Value),
                                                        new SqlParameter("QtdNumerosDaSorte", (object)dto.QtdNumerosDaSorte ?? DBNull.Value),
                                                        new SqlParameter("TamanhoSerie", (object)dto.TamanhoSerie ?? DBNull.Value),
                                                        new SqlParameter("SerieId", (object)dto.SerieId ?? DBNull.Value)).ToList();
    
                    foreach (NumerosDaSorte_ResultDTO row in procResult)
                    {
                        result.Add(row.Numero);
                    }
    
                    return result;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

    E abaixo, o que retorna no EF:

    Finalmente aqui, a procedure funcionando no banco:

    Esse problema ocorre em algumas procedures, onde ou não consigo reconhecer o Complex Type de retorno, ou não consigo retornar os valores que retorno se rodar a procedure pelo banco.

    Preciso da sua ajuda, para entender onde está o problema e o que posso fazer pra contornar essa situação... já estou há dias parado nisso, sem evoluir o projeto e meu prazo está acabando pra ficar tentando resolver isso, sem sucesso... já pesquisei de tudo e já fiz diversas mudanças, mas não posso mexer na lógica da procedure, até porque nem fui eu quem fez, logo, o que posso é fazer pequenos ajustes, mas não alterar a lógica da mesma profundamente.

    Abraços.


    Olivertech

    sexta-feira, 7 de abril de 2017 21:13
  • Maclau,

    Ok, vamos tentar ajudar, mas nem sempre conseguimos chegar na solução final!!!

    O complex type que você se referi esta retornando qual conjunto de dados?


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sábado, 8 de abril de 2017 14:17
  • Olá amigo,

    Então, no VS 2013, ele retorna um array de uma posição, com null, conforme imagem que enviei do VS.

    Não retorna nenhum dado.

    Qto ao Complex Type, o mesmo não foi criado automaticamente quando importei a procedure. Eu tive de criar manual, indo na edição da procedure, solicitando que ele mapeie o retorno e criando o Complex Type.

    Qdo uso os recursos abaixo habilitado na procedure, consigo reconhecer o retorno e inicialmente está correto.

    		IF (1=0) 
    		BEGIN
    			SET FMTONLY OFF;
    		-	DECLARE @TabelaResultado TABLE (
    				Numero VARCHAR(5) NOT NULL
    			);
    			SELECT Numero FROM @TabelaResultado WHERE (1=0)
    		END

    Aí o retorno que espero é um elemento string ou um array de strings. A procedure retorna numeros, mas é feito um cast nesses numeros para varchar, para que eles sejam concatenados com zeros a esquerda.

    Bem. A procedure quando executada manualmente dentro do SQL Server, funciona 100%, mas se tento acessar a mesma pelo mapeamento, não consigo nada... sempre vem esse array de 1 posição com NULL.

    Minha dúvida é se isso se deve a algum recurso usado na procedure em anexo, que possa estar causando esse problema, ou se isso é uma deficiência do EF.

    Já fiz diversas pesquisas e sempre encontro as mesmas sugestões, de fazer como fiz acima, ou seja, criar uma especie de contract, forçando o EF a reconhecer o retorno ou mesmo usar o comando que hoje é considerado descontinuado (SET FMTONLY OFF).

    Não encontro explicações mais detalhes por exemplo, explicado como devo proceder com o desenvolvimento das procedures, se espero mapea-las no EF, pois a princípio, o que se entende é que o EF6 não tem problemas com o mapeamento de procedures e que tudo é simples de usar, mas não me parece uma verdade, pois tenho tido enormes dificuldades, quando as procedures deixam de ser triviais, e passam a ser mais complexas, com uso de tables, condições, try catch etc.

    É nessa linha que quero entender, se existem comando que não devem ser usados dentro das procedures, que causem impactos no mapping, ou se infelizmente estamos nos deparando com uma deficiência do EF6, para procedures mais complexas.

    Abraços.


    Olivertech

    sábado, 8 de abril de 2017 22:21
  • Maclau,

    Você necessita realmente utilizar um array? Parece-me que o problema esta justamente na dificuldade do SQL Server 2012 identificar e manipular esta estrutura.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 11 de abril de 2017 22:53