SQL 2016 - Problemas com Retorno de Dados - Stored Procedure

Question

Prezados,

Tenho uma aplicação em ASP Clássico realizando chamadas de Stored Procedures para realizar comandos de Insert, Alter... . Estava utilizando via SQL 2008. O servidor foi migrado para a versão SQL 2016 e desde então, qualquer aplicação que realiza conexão com estas procedures, não retorna apenas os dados de mensagem, por exemplo: Select msg= 'Cadastro Concluido'

O restante dos comandos, realiza normalmente. Realizei um teste executando a Proc direto no SQL e o mesmo retorna sem erro. Mas aplicações externas continuam a não retornar esta parte. abaixo segue um exemplo (Não retorna apenas o "MSG"):

Parte do Código da Procedure:

INSERT INTO DBO.TB_CAD_FUNCIONARIO_INATIVO
      (
       CFUNC,
       IFUNC,     
       CCENTRO_COMPT,
       
      )
      VALUES
      (
       @CFUNC,
       @IFUNC,     
       @CCENTRO_COMPT,
       
      )
    
     SELECT MSG = 'Alteração realizada, funcionário inserido no Cadastro de Inativos.'
    END

Exemplo Utilizando VBA

Sub teste()
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim provider As String

If Conn.State = adStateOpen Then Conn.Close

'Set Conn = Server.CreateObject("ADODB.Connection")
'Conn.Open "Driver={Sql Server};Server=D4220S028\MSSQL2000A;uid=usuFuncionarios;Pwd=@_homemdeferro;database=DB_RH"
provider = "PROVIDER=SqlOledb.1;Data Source=D4220S028;Database=DB_RH;User ID=usuFuncionarios;Password=@_homemdeferro"
Conn.ConnectionString = provider
Conn.Open

'Set RS = Server.CreateObject("ADODB.Recordset")

'Insere dados na tabela do banco SQL, via store procedure
Sql = "SP_ALT_FUNCIONARIO 9209166,'CARLOS FREDERICO RODRIGUES DE FREITAS',21"
MsgBox RS("MSG")
'RS.Open Sql, Conn
Set RS = Conn.Execute(Sql)

Desde já agradeço pelo apoio.

Answer 1

Dimi,

Você esta chamando esta Stored Procedure na sua aplicação de que forma?

A mesma estava registrada no antigo servidor como um assembly CLR?

---

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

Answer 2

Não consegui entender realmente seu erro, poderia ser mais claro, o que esta retornando em sua procedure ?

outro detalhe em sua procedure não existe tratamento de erro e pelo pouco que estou vendo quando houver erro de gravação não traz nada ou dependendo do erro pode até mesmo trazer a mensagem de registro inserido com sucesso.

Procure usar transação e try catch

Detalhe melhor seu erro e/ou post sua procedure completa que te devolvo uma com tratamento de erro correto.

Se a resposta contribuiu com seu aprendizado por favor marque como Útil
Se solucionou seu problema por favor marque como Resposta
Atenção, se seu problema foi resolvido não deixe o post aberto  

Visite :  www.codigoexpresso.com.br

Answer 3

Dimi,

No exemplo que você colocou, você esta tendo exibir o retorno da sua procedure  antes dela ser executada, o comando  MsgBox RS("MSG") tem que estar depois do comando Conn.Execute(Sql)

Sql = "SP_ALT_FUNCIONARIO 9209166,'CARLOS FREDERICO RODRIGUES DE FREITAS',21"
'RS.Open Sql, Conn
Set RS = Conn.Execute(Sql)

MsgBox RS("MSG")

Answer 4

Dimi,

No exemplo que você colocou, você esta tendo exibir o retorno da sua procedure  antes dela ser executada, o comando  MsgBox RS("MSG") tem que estar depois do comando Conn.Execute(Sql)

Sql = "SP_ALT_FUNCIONARIO 9209166,'CARLOS FREDERICO RODRIGUES DE FREITAS',21"
'RS.Open Sql, Conn
Set RS = Conn.Execute(Sql)

MsgBox RS("MSG")

Ola...eu fiz essa correção e ainda ele apresenta erro. A execução da Procedure, no caso realizando um "Alter", esta ocorrendo normalmente, só essa mensagem que nao retorna mesmo.

Answer 5

Deleted

Answer 6

Eu vou colocar o Código da Proc inteira, eu havia colocado somente um exemplo da estrutura que esta. Quando falo retorno da mensagem, há o comando na Procedure  "Select MSG= 'Mensagem'  ". Existem estruturas de decisoes que retornam esse "MSG" de algum modo. Essa mensagem é exibida na aplicacao externa. A conexão nessa Procedure esta utilizando ADO. A estrutura do código abaixo da Procedure e o codigo ASP estavam funcionando OK. Porem houve a migracao do Servidor SQL da versao 2008 para 2016. E essa estrutura parou de retornar somente o valor do Select MSG= 'Mensagem' . Nessa Procedure tem comandos de Alter na tabela e continuam funcionando corretamente. O código VBA que citei acima foi outro teste de Mesa nesta mesma Procedure, e ocorre o mesmo problema. Não estou sabendo descobrir se o problema é alguma configuracao no SQL ou estrutura do código da Procedure. 

Segue os códigos:

Código da Procedure:

USE [DB_RH]
GO
/****** Object:  StoredProcedure [dbo].[SP_ALT_FUNCIONARIO]    Script Date: 09/11/2018 08:56:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

GO
ALTER PROCEDURE [dbo].[SP_ALT_FUNCIONARIO]   

	--DECLARAÇÃO DAS VARIAVEIS DA TABELA
	(
		@CFUNC				int,
		@IFUNC				varchar (100),
		@CCENTRO_COMPT		int,
		@CCARGO				int,
		@CHORARIO			int,
		@CMOTIVO			int,
		@ISEXO				char (1),	
		@CCPF				varchar (20),
		@CRG				varchar (20),
		@IORGAO_EMIS		varchar (6),
		@CCART_PROF			varchar (10),
		@CCART_SERIE		varchar (10),
		@CPIS				varchar (20),
		@IENDCO				varchar (100),
		@IBAIRRO			varchar (25),
		@ICIDADE			varchar (30),
		@IUF				varchar (30),	
		@CCEP				varchar (10),
		@IEMAIL				varchar (40),
		@CRAMAL				varchar (16),
		@CTELEFONE			varchar (16),
		@CTEL_RECADO		varchar (16),
		@CTEL_CELULAR		varchar (16),
		@DADMISSAO_ORG		smalldatetime,
		@DADMISSAO_OM		smalldatetime,
		@DADMIS_CARGO		smalldatetime,
		@DREAJ_SALARIAL		smalldatetime,
		@DNASC				smalldatetime,
		@IEST_CIVIL			varchar (18),	
		@QFILHOS			int,
		@CJUNC				int,
		@CJUNC_DIG			tinyint,
		@CCTA_PRODT			int,
		@CCTA_DIG			tinyint,
		@IOBS				varchar (100),
		@CSTAT_ATIV_FUNC		tinyint,
		@CSTAT_APOSEN_INSS		char (1),
		@CSTAT_APOSEN_PREVIDENCIA	char (1),
		@XSAIDA				char (1),
		@DDEMISSAO			smalldatetime,
		@DTRANSFERENCIA		smalldatetime,
		@CJUNC_DESTINO		int,
		@IJUNC_DESTINO		varchar (100),
		@VENCIMENTO			numeric(19,2),
		@ANOBCO				smallint,
		@MESBCO				smallint,
		@ANOOM				smallint,
		@MESOM				smallint,
		@ANOCARGO			smallint,
		@MESCARGO 			smallint,
		@ANOREAJ			smallint,
		@MESREAJ			smallint,
		@IDADE 				smallint,
		@NIVELH				int,
		@NOMEH				varchar (20),
		@DTRANSFDOM			smalldatetime,
		@CPROCEDENCIA		int,
		@IPROCEDENCIA		varchar (100),		
		@IOBSHORARIO		varchar (100),
		@USUARIO			int
	) 

	AS

	--VERIFICA SE É SAÍDA DO DEPARTAMENTO
	IF @XSAIDA = 'N'

		BEGIN			

			--SOMENTE ATUALIZA OS CAMPOS DA TABELA DE FUNCIONÁRIO
				UPDATE  DBO.TB_CAD_FUNCIONARIO
				SET 	IFUNC = @IFUNC,
						CCENTRO_COMPT = @CCENTRO_COMPT,
						CCARGO = @CCARGO,
						CHORARIO = @CHORARIO,
						CMOTIVO = @CMOTIVO,
						ISEXO = @ISEXO,
						CCPF = @CCPF,
						CRG = @CRG,
						IORGAO_EMIS = @IORGAO_EMIS,
						CCART_PROF = @CCART_PROF,
						CCART_SERIE = @CCART_SERIE,
						CPIS = @CPIS,
						IENDCO = @IENDCO,
						IBAIRRO = @IBAIRRO,
						ICIDADE = @ICIDADE,
						IUF = @IUF,
						CCEP = @CCEP,
						IEMAIL_CONTATO = @IEMAIL,
						CRAMAL = @CRAMAL,		
						CTELEFONE = @CTELEFONE,
						CTEL_RECADO = @CTEL_RECADO,
						CTEL_CELULAR = @CTEL_CELULAR,
						DADMISSAO_ORG = @DADMISSAO_ORG,		
						DADMISSAO_OM = @DADMISSAO_OM,
						DADMIS_CARGO = @DADMIS_CARGO,
						DREAJ_SALARIAL = @DREAJ_SALARIAL,
						DNASC = @DNASC,
						IEST_CIVIL = @IEST_CIVIL,
						QFILHOS = @QFILHOS,
						CJUNC = @CJUNC,		
						CJUNC_DIG = @CJUNC_DIG,
						CCTA_PRODT = @CCTA_PRODT,
						CCTA_DIG = @CCTA_DIG,
						IOBS = @IOBS,
						CSTAT_ATIV_FUNC = @CSTAT_ATIV_FUNC,	
						CSTAT_APOSEN_INSS = @CSTAT_APOSEN_INSS,
						CSTAT_APOSEN_PREVIDENCIA = @CSTAT_APOSEN_PREVIDENCIA,	
						CNIVEL_FUNCIONAL = @NIVELH,
						DTRANSFDOM = @DTRANSFDOM,
						CPROCEDENCIA = @CPROCEDENCIA,
						IPROCEDENCIA = @IPROCEDENCIA,		
						IOBSHORARIO = @IOBSHORARIO,
	            		DATUALIZ = GETDATE(),
	            		USUARIO = @USUARIO
			WHERE CFUNC = @CFUNC			
			
			----------------------------------------------------------------------------------------
			--INSERE DADOS NA TABELA DE HISTÓRICO DOS FUNCIONÁRIOS 
			INSERT INTO DBO.TB_CAD_FUNCIONARIO_HIST
			(
				CFUNC,
				IFUNC,
				CNIVEL_FUNCIONAL,
				CCENTRO_COMPT,
				CCARGO,
				CHORARIO,
				CMOTIVO,
				ISEXO,
				CCPF,
				CRG,
				IORGAO_EMIS,
				CCART_PROF,
				CCART_SERIE,
				CPIS,
				IENDCO,
				IBAIRRO,
				ICIDADE,
				IUF,
				CCEP,
				IEMAIL_CONTATO,
				CRAMAL,
				CTELEFONE,
				CTEL_RECADO,
				CTEL_CELULAR,
				DADMISSAO_ORG,
				DADMISSAO_OM,				
				DNASC,
				IEST_CIVIL,
				QFILHOS,
				CJUNC,
				CJUNC_DIG,
				CCTA_PRODT,
				CCTA_DIG,
				IOBS,
				CSTAT_ATIV_FUNC,
				CSTAT_APOSEN_INSS,
				CSTAT_APOSEN_PREVIDENCIA,
				DTRANSFDOM,
				IOBSHORARIO,
				CPROCEDENCIA,
				IPROCEDENCIA,
				DATUALIZ,
				USUARIO
 			)
			VALUES
			(
				@CFUNC,
				@IFUNC,
				@NIVELH,
				@CCENTRO_COMPT,
				@CCARGO,
				@CHORARIO,
				@CMOTIVO,
				@ISEXO,
				@CCPF,
				@CRG,
				@IORGAO_EMIS,
				@CCART_PROF,
				@CCART_SERIE,
				@CPIS,
				@IENDCO,
				@IBAIRRO,
				@ICIDADE,
				@IUF,
				@CCEP,
				@IEMAIL,
				@CRAMAL,
				@CTELEFONE,
				@CTEL_RECADO,
				@CTEL_CELULAR,
				@DADMISSAO_ORG,
				@DADMISSAO_OM,				
				@DNASC,
				@IEST_CIVIL,
				@QFILHOS,
				@CJUNC,
				@CJUNC_DIG,
				@CCTA_PRODT,
				@CCTA_DIG,
				@IOBS,
				@CSTAT_ATIV_FUNC,
				@CSTAT_APOSEN_INSS,
				@CSTAT_APOSEN_PREVIDENCIA,	
				@DTRANSFDOM,
				@IOBSHORARIO,
				@CPROCEDENCIA,
				@IPROCEDENCIA,
				GETDATE(), 
				@USUARIO	
			)				
			
			 SELECT MSG = 'Alteração realizada. '
		END		
	ELSE	
		--É SAÍDA DO DEPARTAMENTO
		BEGIN			
			--VERIFICA SE EXISTE CÓDIGO NA TABELA DE EQUIPE
			IF  @CFUNC IN (SELECT DISTINCT(CDIRETOR) AS CODSUP FROM DBO.VW_CENTROCOMPT_COMPLETO
					   WHERE CDIRETOR > 0
					 UNION
					   SELECT DISTINCT(CSUPERINTENDENTE) AS CODSUP FROM DBO.VW_CENTROCOMPT_COMPLETO
					   WHERE CSUPERINTENDENTE > 0
					 UNION
					   SELECT DISTINCT(CGERENTE_DEP) AS CODSUP FROM DBO.VW_CENTROCOMPT_COMPLETO
					   WHERE CGERENTE_DEP > 0 
					 UNION
					   SELECT DISTINCT(CCOORDENADOR) AS CODSUP FROM DBO.VW_CENTROCOMPT_COMPLETO
					   WHERE CCOORDENADOR > 0 
					 UNION
					   SELECT DISTINCT(CSUPERVISOR) AS CODSUP FROM DBO.VW_CENTROCOMPT_COMPLETO
					   WHERE CSUPERVISOR > 0 )
  				BEGIN
					 SELECT MSG = 'Alteração não realizada, há Equipes subordinadas a este funcionário.'
				END	
			ELSE
				BEGIN
					--INCLUI OS DADOS NA TABELA DE FUNCIONÁRIO INATIVOS
					INSERT INTO DBO.TB_CAD_FUNCIONARIO_INATIVO
						(
							CFUNC,
							IFUNC,					
							CCENTRO_COMPT,
							CCARGO,
							CHORARIO,	
							CMOTIVO,
							ISEXO,
							CCPF,
							CRG,
							IORGAO_EMIS,
							CCART_PROF,
							CCART_SERIE,
							CPIS,
							IENDCO,
							IBAIRRO,
							ICIDADE,
							IUF,
							CCEP,
							IEMAIL_CONTATO,
							CRAMAL,		
							CTELEFONE,
							CTEL_RECADO,
							CTEL_CELULAR,
							DADMISSAO_ORG,		
							QANO_ORG,
							QMES_ORG,
							DADMISSAO_OM,
							QANO_OM,
							QMES_OM,
							DADMIS_CARGO,
							QANO_CARGO,
							QMES_CARGO,
							DREAJ_SALARIAL,
							QANO_REAJ,
							QMES_REAJ,
							DNASC,
							QIDADE,
							IEST_CIVIL,
							QFILHOS,
							CJUNC,		
							CJUNC_DIG,
							CCTA_PRODT,
							CCTA_DIG,
							IOBS,
							CSTAT_ATIV_FUNC,	
							CSTAT_APOSEN_INSS,
							CSTAT_APOSEN_PREVIDENCIA,				            					
							DDEMISSAO,
							DTRANSFERENCIA,
							CJUNC_DESTINO,
							IJUNC_DESTINO,
							VVENCIMENTO,	
							CNIVEL_FUNCIONAL,
							INIVEL_FUNCIONAL,
							DTRANSFDOM,
							CPROCEDENCIA,
							IPROCEDENCIA,		
							IOBSHORARIO,									
							DATUALIZ,
							USUARIO
						)
						VALUES
						(
							@CFUNC,
							@IFUNC,					
							@CCENTRO_COMPT,
							@CCARGO,
							@CHORARIO,	
	 						@CMOTIVO,
							@ISEXO,
							@CCPF,
							@CRG,
							@IORGAO_EMIS,
							@CCART_PROF,
							@CCART_SERIE,
							@CPIS,
							@IENDCO,
							@IBAIRRO,
							@ICIDADE,
							@IUF,
							@CCEP,
							@IEMAIL,
							@CRAMAL,		
							@CTELEFONE,
							@CTEL_RECADO,
							@CTEL_CELULAR,
							@DADMISSAO_ORG,
							@ANOBCO,			
							@MESBCO,
							@DADMISSAO_OM,
							@ANOOM,
							@MESOM,
							@DADMIS_CARGO,
							@ANOCARGO,
							@MESCARGO,
							@DREAJ_SALARIAL,
							@ANOREAJ,
							@MESREAJ,
							@DNASC,
							@IDADE,
							@IEST_CIVIL,
							@QFILHOS,
							@CJUNC,		
							@CJUNC_DIG,
							@CCTA_PRODT,
							@CCTA_DIG,
							@IOBS,
							@CSTAT_ATIV_FUNC,	
							@CSTAT_APOSEN_INSS,
							@CSTAT_APOSEN_PREVIDENCIA,	
							@DDEMISSAO,
							@DTRANSFERENCIA,
							@CJUNC_DESTINO,
							@IJUNC_DESTINO,
							@VENCIMENTO,	
							@NIVELH,
							@NOMEH,		
							@DTRANSFDOM,
							@CPROCEDENCIA,
							@IPROCEDENCIA,		
							@IOBSHORARIO,  							          	
							GETDATE(),
							@USUARIO	
						)
				
					SELECT MSG = 'Alteração realizada, funcionário inserido no Cadastro de Inativos.'
				END
	END

Código ASP Clássico (trecho da chamada da Procedure):

'Cria conexão com o SQL
	Set RS = Server.CreateObject("ADODB.Recordset")	

	'Insere dados na tabela do banco SQL, via store procedure
	SQL = "SP_ALT_FUNCIONARIO "& xCodFunc &",'"& xIFunc &"',"& xEquipe &","& xCodCargo &","& xCodHora &","& xCodMotivo &",'"& xSexo &"','"& xCPF &"','"& xRG &"','"& xOrgEm &"','"& xCartProf &"','"& xCartSerie &"'," &_
		  "'" & xPIS &"','"& xEnd &"','"& xBairro &"','"& xCidade &"','"& xUF &"','"& xCEP &"','"& xEmail &"','"& xRamal &"','"& xTel &"','"& xTelRec &"','"& xCel &"','"& xDtAdmBanco &"','"& xDtAdmOM &"','"& xDtAdmCargo &"','"& xDtReajuste &"','"& xDtNasc &"'," &_
		  "'" & xEstCivil &"',"& xFilhos &","& xAgencia &","& xDigAg &","& xConta &","& xDigCC &",'"& xOBS &"',"& xStatusFunc &",'"& xApINSS &"','"& xApPP &"','"& xSaida &"','"& xDtSaida &"','"& xDtTransfer &"',"& xCodTransfer &",'"& xNomTransfer &"',"& xVencimento &","& xAnoBco &","& xMesBco &"," &_
		  "" & xAnoOM &","& xMesOM &","& xAnoCargo &","& xMesCargo &","& xAnoReaj &","& xMesReaj &","& xIdade &","& vNivelH &",'"& vNomeH &"','"& xDtTransfDOM &"',"& xCodProced &",'"& xIProced &"','"& xObsHora &"',"	& xUsuar & ""
	Set RS = Conn.execute(SQL)

	'Mensagem de mensagem e fecha janela
	Response.Write(RS("MSG"))
	Response.Write("<script language='javascript'>")
	Response.Write("alert('"& RS("MSG") &"');")		
	Response.Write ("window.open('FrameFuncionario.asp','main');")
	Response.Write("</script>")

	'Fecha conexão
	set sRS = nothing 
    set RS = nothing

end if%>	

Código VBA usado para o Teste de Mesa com a mesma Procedure:

Sub teste()
 Dim Conn As New ADODB.Connection
 Dim RS As New ADODB.Recordset
 Dim provider As String


 If Conn.State = adStateOpen Then Conn.Close


 'Set Conn = Server.CreateObject("ADODB.Connection")
 'Conn.Open "Driver={Sql Server};Server=D4220S028\MSSQL2000A;uid=usuFuncionarios;Pwd=@_homemdeferro;database=DB_RH"
 provider = "PROVIDER=SqlOledb.1;Data Source=D4220S028;Database=DB_RH;User ID=usuFuncionarios;Password=@_homemdeferro"
 Conn.ConnectionString = provider
 Conn.Open

'Set RS = Server.CreateObject("ADODB.Recordset")

'Insere dados na tabela do banco SQL, via store procedure
 Sql = "SP_ALT_FUNCIONARIO 9209166,'CARLOS FREDERICO RODRIGUES DE FREITAS',21"
 RS.Open Sql, Conn
 Set RS = Conn.Execute(Sql)
 MsgBox RS("MSG")

Desde ja agradeço pelo apoio!!!

[]'s

Answer 7

Deleted

Answer 8

José,

Verifiquei a questão do Provider e fiz um teste alterando a conexão e funcionou, ainda nao entendi muito bem o porque, alterei para uma conexão ODBC e o Select MSG= 'Mensagem' retornou o valor . Se souberem me dizer o o porque disso!! (desculpe talves pela pergunta, mas eu estou na peregrinacao de aprender mais a fundo SQL server..rs)

Linha Antiga:

Conn.Open "Driver={Sql Server};Server=D4220S028;uid=UsuPalestra;Pwd=@_matrix;database=Db_ProducaoOeM"

Linha Alterada:

Conn.Open"PROVIDER=SqlOledb;Data Source=D4220S028;Database=Db_ProducaoOeM;User ID=UsuPalestra;Password=@_matrix"

Desde ja agradeço pelo apoio de todos!!