Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

• river1

  SSC-Insane

  Points: 24315

  More actions

  May 23, 2012 at 6:55 am

  #261892

  Hi,

  I receive this message when I execute the following sp:

  message:

  Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

  SP Code:

  ALTER PROCEDURE [dbo].[spSGCT_IMPORTA_SGCTLocais]

  (@Caminho as varchar (500), @extensao as varchar(5),

  @nomeficheiro as varchar (500))

  AS

  BEGIN

  set nocount on

  DECLARE @cmd as varchar(500), @NomeExacto as varchar(500), @directoria as varchar(500)

  , @codrf as varchar(5), @caminhoExacto as varchar(1000) , @i int, @File varchar(1000)

  , @Sessao varchar(5), @data as datetime, @Codigo as int,@erro as int

  exec spSGCT_DROPTABLES_SGRS

  exec spSGCT_CREATETABLES_SGRS

  --Apaga ficheiros que possam estar na pasta

  EXEC sp_SGCT_APAGAFICHEIROS_SGCTLOCAIS 1, @caminho

  --Verifica se existem ficheiros compactados na directoria

  select @File = @caminho + '\'+@nomeficheiro+'*.*'

  select @cmd = 'dir /B ' + @file

  insert a1 EXEC master..xp_cmdshell @cmd

  while (select count(*) from a1 where s is not null and s like '%'+@nomeficheiro+'%') <> 0

  begin

  set @nomeexacto = (select top 1 s from a1 where s like '%'+@nomeficheiro+'%')

  set @cmd = 'mkdir '+@caminho+'\'+@nomeexacto+'_Dir\'

  exec master..xp_cmdshell @cmd, no_output

  set @directoria = ''+@caminho+'\'+@nomeexacto+'_Dir\'

  set @caminhoexacto =@caminho +'\'+@nomeexacto

  select @cmd = '"C:\Programas\WinRAR\WinRAR.exe" x -u '+@caminhoexacto+' '+@directoria+''

  exec master..xp_cmdshell @cmd, no_output

  exec('BULK INSERT sessoes2 FROM '''+@directoria+'\sessao.txt''

  with (FIRSTROW =2,FIELDTERMINATOR =''|'',

  ROWTERMINATOR ='''', tablock)')

  exec('BULK INSERT codrf FROM '''+@directoria+'\CONFIGRF.txt''

  with (FIRSTROW =2,FIELDTERMINATOR =''|'',

  ROWTERMINATOR ='''', tablock)')

  insert into FicheirosImportar

  select a.*,b.*,@nomeexacto,@caminhoexacto,0 from sessoes2 a, codrf b

  -- --verifica se é para ser considerado para importar

  -- if (select count(*) from sincronismo_sgrs a inner join sessoes1 b

  -- on a.cod_rep_fiscal = b.codrf and a.sessao >= b.sessao) = 0

  --

  -- begin

  --

  -- set @sessao = (select sessao from sessoes1 where sessao is not null)

  -- set @codrf =(select codrf from sessoes1 where sessao =@sessao)

  --

  -- if (select count(*) from FicheirosImportar where codrf =@codrf and sessao >=@sessao ) =0

  -- begin

  --

  --

  -- delete from FicheirosImportar where codrf =@codrf

  -- insert into FicheirosImportar values (@sessao, @codrf,@nomeexacto,@directoria)

  -- end

  -- end

  delete from sessoes2

  delete from codrf

  delete from a1 where s =@nomeexacto

  end

  -- if (select count(*) from FicheirosImportar ) <> 0

  --

  -- begin

  --

  --

  --

  --

  -- while (select count(*) from FicheirosImportar) <> 0

  -- begin

  --

  -- set @sessao = (select top 1 sessao from FicheirosImportar)

  -- set @codrf =(select top 1 codrf from FicheirosImportar where sessao =@sessao)

  -- set @directoria = (select caminho from FicheirosImportar where sessao =@sessao and codrf =@codrf)

  -- set @nomeexacto = (select Nome from FicheirosImportar where sessao =@sessao and codrf =@codrf)

  -- set @caminhoexacto =@directoria+replace(@nomeexacto,'rar','txt')

  --

  --

  -- exec('BULK INSERT ctbs1 FROM '''+@caminhoexacto+'''

  -- with (FIRSTROW =2,FIELDTERMINATOR =''|'',ROWTERMINATOR ='''',tablock,

  -- DATAFILETYPE =''char'')')

  --

  --

  --

  -- if (select count(*) from ctbs1) <> 0

  -- begin

  --

  -- insert into SINCRONISMO_SGRS_DETALHES (nif_antigo,status_actual,novo_status, validade)

  -- select a.nif_antigo,b.status,a.status, convert(datetime, replace(a.validade,'|',''))

  -- from ctbs1 a inner join contribuintes b

  -- on b.nif_antigo =a.nif_antigo

  --

  -- update SINCRONISMO_SGRS_DETALHES set validade =null where novo_status <> 'S'

  --

  --

  -- set @erro = @@error

  --

  -- if @erro = 0

  -- begin

  --

  -- update contribuintes set status = b.novo_status, dt_alteracao =getdate() , dt_validade = b.validade

  -- from SINCRONISMO_SGRS_DETALHES b inner join contribuintes a

  -- on a.nif_antigo =b.nif_antigo and b.COD_SINCRONISMO is null and a.status <> 'I'

  --

  -- set @erro = @@error

  -- end

  --

  --

  -- if @erro = 0

  -- begin

  -- insert into TRILHA_ALTERACOES_CENTRAL (UTILIZADOR , DT_ALTERACAO,

  -- TP_ALTERACAO ,NIF_ANTIGO,TABELA,CAMPO ,VALOR_OLD ,VALOR_NEW ,DESCRICAO_OLD ,DESCRICAO_NEW)

  -- select 'administrador',a.dt_alteracao,'SGRS',

  -- a.nif_antigo,'Contribuintes', 'Status',b.status_actual,b.novo_status,NULL,NULL

  -- from contribuintes a inner join SINCRONISMO_SGRS_DETALHES b

  -- on a.nif_antigo = b.nif_antigo and b.COD_SINCRONISMO is null

  -- set @erro = @@error

  -- end

  --

  -- if @erro = 0

  -- begin

  -- set @data = (select convert(varchar,getdate(),120))

  -- set @erro = @@error

  -- end

  --

  -- if @erro = 0

  -- begin

  -- exec('INSERT SINCRONISMO_SGRS values ('''+@data+''','''+@nomeexacto+''','+@SESSAO+','''+@codrf+''',1)')

  -- set @erro = @@error

  -- end

  --

  -- if @erro = 0

  -- begin

  -- set @codigo = (select codigo from sincronismo_sgrs where sessao =@sessao and cod_rep_fiscal=@codrf)

  -- set @erro = @@error

  -- end

  --

  -- if @erro = 0

  -- begin

  -- UPDATE SINCRONISMO_SGRS_DETALHES

  -- SET COD_SINCRONISMO =@codigo WHERE COD_SINCRONISMO IS NULL

  -- set @erro = @@error

  -- end

  --

  --

  --

  -- delete from FicheirosImportar where sessao =@sessao and codrf =@codrf

  -- delete from ctbs1

  -- end

  --

  -- end

  -- end

  --EXEC sp_SGCT_APAGAFICHEIROS 2

  EXEC spSGCT_DROPTABLES_SGCTLOCAIS

  END

  Exec spSGCT_IMPORTA_SGCTLocais 'c:\import\', '.rar', 'PastaExportacao SGCTlocal'

  Can someone help me understanding why this happens?~

  Thanks

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  May 23, 2012 at 7:55 am

  #1491771

  With well over 500 points I find it amazing you would post this. There is no ddl for any of the tables, there are multiple additional procs being called and no explanation given about what you are trying to do.

  Quite frankly the code you posted is a mess. There are over 100 lines of commented code. We are all volunteers around here so help us to help you.

  I ran your code through the formatter at http://poorsql.com/%5B/url%5D (after removing all the removed code). Here is the code in a format that is far more legible.

  ALTER PROCEDURE [dbo].[spSGCT_IMPORTA_SGCTLocais] (

  @Caminho AS VARCHAR(500)

  ,@extensao AS VARCHAR(5)

  ,@nomeficheiro AS VARCHAR(500)

  )

  AS

  BEGIN

  SET NOCOUNT ON

  DECLARE @cmd AS VARCHAR(500)

  ,@NomeExacto AS VARCHAR(500)

  ,@directoria AS VARCHAR(500)

  ,@codrf AS VARCHAR(5)

  ,@caminhoExacto AS VARCHAR(1000)

  ,@i INT

  ,@File VARCHAR(1000)

  ,@Sessao VARCHAR(5)

  ,@Data AS DATETIME

  ,@Codigo AS INT

  ,@erro AS INT

  EXEC spSGCT_DROPTABLES_SGRS

  EXEC spSGCT_CREATETABLES_SGRS

  --Apaga ficheiros que possam estar na pasta

  EXEC sp_SGCT_APAGAFICHEIROS_SGCTLOCAIS 1

  ,@caminho

  --Verifica se existem ficheiros compactados na directoria

  SELECT @File = @caminho + '\' + @nomeficheiro + '*.*'

  SELECT @cmd = 'dir /B ' + @file

  INSERT a1

  EXEC master..xp_cmdshell @cmd

  WHILE (

  SELECT count(*)

  FROM a1

  WHERE s IS NOT NULL

  AND s LIKE '%' + @nomeficheiro + '%'

  ) <> 0

  BEGIN

  SET @nomeexacto = (

  SELECT TOP 1 s

  FROM a1

  WHERE s LIKE '%' + @nomeficheiro + '%'

  )

  SET @cmd = 'mkdir ' + @caminho + '\' + @nomeexacto + '_Dir\'

  EXEC master..xp_cmdshell @cmd

  ,no_output

  SET @directoria = '' + @caminho + '\' + @nomeexacto + '_Dir\'

  SET @caminhoexacto = @caminho + '\' + @nomeexacto

  SELECT @cmd = '"C:\Programas\WinRAR\WinRAR.exe" x -u ' + @caminhoexacto + ' ' + @directoria + ''

  EXEC master..xp_cmdshell @cmd

  ,no_output

  EXEC (

  'BULK INSERT sessoes2 FROM ''' + @directoria + '\sessao.txt''

  with (FIRSTROW =2,FIELDTERMINATOR =''|'',

  ROWTERMINATOR ='''', tablock)'

  )

  EXEC (

  'BULK INSERT codrf FROM ''' + @directoria + '\CONFIGRF.txt''

  with (FIRSTROW =2,FIELDTERMINATOR =''|'',

  ROWTERMINATOR ='''', tablock)'

  )

  INSERT INTO FicheirosImportar

  SELECT a.*

  ,b.*

  ,@nomeexacto

  ,@caminhoexacto

  ,0

  FROM sessoes2 a

  ,codrf b

  DELETE

  FROM sessoes2

  DELETE

  FROM codrf

  DELETE

  FROM a1

  WHERE s = @nomeexacto

  END

  EXEC spSGCT_DROPTABLES_SGCTLOCAIS

  END

  I don't see anything in your code here that would cause this error. That means it is somewhere in one of your other procs, or a trigger on one of these tables.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• river1

  SSC-Insane

  Points: 24315

  More actions

  May 23, 2012 at 7:59 am

  #1491777

  It was one of other procs.

  Thanks