> I found a strange behaviour in SQL 2005.
> In my soterd proc i've got something like this:
>
> /* My Code (wrong???)*/
>  ...
> if @MyVariable1 = 1
>  set @proD = @proD - @nCurPro + 1 +
>              ISNULL((SELECT SUM(ISNULL(TIP_PROFTOT, 1))
>                      FROM RUN_MISSIONI
>                      INNER JOIN DAT_UDC ON (UDC_UDC = MIS_UDC)
>                      LEFT OUTER JOIN DAT_UDC_CEL ON (TIP_TUDC = UDC_TUDC
>                      AND TIP_TIPOCELLA = @tipoCella)
>                      WHERE
>                      MIS_D_CORRIDOIO = @corD AND
>                      MIS_D_CELLA = @celD AND
>                      MIS_D_TEO = 1
>                     ), 0)
>
> setCellaDest:
>   ...
> /* End My Code (wrong???)*/
>
> My stored proc usually work well without problems. Sometimes (random) start
> raising error:
> Msg 102, Level 15, State 1, Procedure ws_L2_SetCellaMiss, Line 539
> Incorrect syntax near s.
>
> Using the begin end the procedure works well ever, without problems:
>
> /* My Code (good)*/
>  ...
> if @MyVariable1 = 1
> begin
>  set @proD = @proD - @nCurPro + 1 +
>              ISNULL((SELECT SUM(ISNULL(TIP_PROFTOT, 1))
>                      FROM RUN_MISSIONI
>                      INNER JOIN DAT_UDC ON (UDC_UDC = MIS_UDC)
>                      LEFT OUTER JOIN DAT_UDC_CEL ON (TIP_TUDC = UDC_TUDC
>                      AND TIP_TIPOCELLA = @tipoCella)
>                      WHERE
>                      MIS_D_CORRIDOIO = @corD AND
>                      MIS_D_CELLA = @celD AND
>                      MIS_D_TEO = 1
>                     ), 0)
> end
> setCellaDest:
>   ...
> /* End My Code (good)*/
>
> I call my procedure from a c# application with ADO.net.  What i d'ont
> understand is that teh procedure gives a SINTAX ERROR! If i recompile the
> procedure from SQL Server management studio the procedure recompiles well,
> withou error, and for a bit it works well. After a bit the error raise again.
> Someone has seen something like this?
>
> Thanks!
>

"checcouno" <checco***@discussions.microsoft.com> escribió en el mensaje
news:64C0426D-8AD6-4C2C-A108-9FBB68F6E380@microsoft.com...
| I found a strange behaviour in SQL 2005.
| In my soterd proc i've got something like this:
|
| /* My Code (wrong???)*/
|  ...
| if @MyVariable1 = 1
|  set @proD = @proD - @nCurPro + 1 +
|              ISNULL((SELECT SUM(ISNULL(TIP_PROFTOT, 1))
|                      FROM RUN_MISSIONI
|                      INNER JOIN DAT_UDC ON (UDC_UDC = MIS_UDC)
|                      LEFT OUTER JOIN DAT_UDC_CEL ON (TIP_TUDC = UDC_TUDC
|                      AND TIP_TIPOCELLA = @tipoCella)
|                      WHERE
|                      MIS_D_CORRIDOIO = @corD AND
|                      MIS_D_CELLA = @celD AND
|                      MIS_D_TEO = 1
|                     ), 0)
|
| setCellaDest:
|   ...
| /* End My Code (wrong???)*/
|
| My stored proc usually work well without problems. Sometimes (random)
start
| raising error:
| Msg 102, Level 15, State 1, Procedure ws_L2_SetCellaMiss, Line 539
| Incorrect syntax near s.
|
| Using the begin end the procedure works well ever, without problems:
|
| /* My Code (good)*/
|  ...
| if @MyVariable1 = 1
| begin
|  set @proD = @proD - @nCurPro + 1 +
|              ISNULL((SELECT SUM(ISNULL(TIP_PROFTOT, 1))
|                      FROM RUN_MISSIONI
|                      INNER JOIN DAT_UDC ON (UDC_UDC = MIS_UDC)
|                      LEFT OUTER JOIN DAT_UDC_CEL ON (TIP_TUDC = UDC_TUDC
|                      AND TIP_TIPOCELLA = @tipoCella)
|                      WHERE
|                      MIS_D_CORRIDOIO = @corD AND
|                      MIS_D_CELLA = @celD AND
|                      MIS_D_TEO = 1
|                     ), 0)
| end
| setCellaDest:
|   ...
| /* End My Code (good)*/
|
| I call my procedure from a c# application with ADO.net.  What i d'ont
| understand is that teh procedure gives a SINTAX ERROR! If i recompile the

| procedure from SQL Server management studio the procedure recompiles well,
| withou error, and for a bit it works well. After a bit the error raise
again.
| Someone has seen something like this?
|
| Thanks!
|

"checcouno" <checco***@discussions.microsoft.com> wrote in message
news:64C0426D-8AD6-4C2C-A108-9FBB68F6E380@microsoft.com...
>I found a strange behaviour in SQL 2005.
> In my soterd proc i've got something like this:
>
> /* My Code (wrong???)*/
> ...
> if @MyVariable1 = 1
> set @proD = @proD - @nCurPro + 1 +
>             ISNULL((SELECT SUM(ISNULL(TIP_PROFTOT, 1))
>                     FROM RUN_MISSIONI
>                     INNER JOIN DAT_UDC ON (UDC_UDC = MIS_UDC)
>                     LEFT OUTER JOIN DAT_UDC_CEL ON (TIP_TUDC = UDC_TUDC
>                     AND TIP_TIPOCELLA = @tipoCella)
>                     WHERE
>                     MIS_D_CORRIDOIO = @corD AND
>                     MIS_D_CELLA = @celD AND
>                     MIS_D_TEO = 1
>                    ), 0)
>
> setCellaDest:
>  ...
> /* End My Code (wrong???)*/
>
> My stored proc usually work well without problems. Sometimes (random)
> start
> raising error:
> Msg 102, Level 15, State 1, Procedure ws_L2_SetCellaMiss, Line 539
> Incorrect syntax near s.
>
> Using the begin end the procedure works well ever, without problems:
>
> /* My Code (good)*/
> ...
> if @MyVariable1 = 1
> begin
> set @proD = @proD - @nCurPro + 1 +
>             ISNULL((SELECT SUM(ISNULL(TIP_PROFTOT, 1))
>                     FROM RUN_MISSIONI
>                     INNER JOIN DAT_UDC ON (UDC_UDC = MIS_UDC)
>                     LEFT OUTER JOIN DAT_UDC_CEL ON (TIP_TUDC = UDC_TUDC
>                     AND TIP_TIPOCELLA = @tipoCella)
>                     WHERE
>                     MIS_D_CORRIDOIO = @corD AND
>                     MIS_D_CELLA = @celD AND
>                     MIS_D_TEO = 1
>                    ), 0)
> end
> setCellaDest:
>  ...
> /* End My Code (good)*/
>
> I call my procedure from a c# application with ADO.net.  What i d'ont
> understand is that teh procedure gives a SINTAX ERROR! If i recompile the
> procedure from SQL Server management studio the procedure recompiles well,
> withou error, and for a bit it works well. After a bit the error raise
> again.
> Someone has seen something like this?
>
> Thanks!
>

"checcouno" <checco***@discussions.microsoft.com> wrote in message
news:ACAE9075-AA95-46FA-B11B-D23C40FBC711@microsoft.com...
> Well. This was a previous stored proc. working perfectly on SQL 2000.
> It's quite long...
>
> The problem is real because i've got it in two different server, different
> production site: the same problem in the same stored in the same row.
> I've got a lot of sp form sql2000, i hope they works good...
>
> /*===============START CODE==============*/
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
>
> ALTER              PROCEDURE [dbo].[ws_L2_SetCellaMiss]
> (
> @idMissione int,
> @celD int OUTPUT,
> @posD int OUTPUT,
> @proD int OUTPUT,
> @errore varchar(255) OUTPUT,
> @culla int = 0,
> @manuale bit = 0,
> @d_forzata bit = 0
> )
> AS
> SET NOCOUNT ON
> SET XACT_ABORT ON
>
> declare @retVal int,
>        @udc decimal(15),
>        @corD int, @fDestTeo bit, @corS int,
>        @L2Sta varchar(5), @L1Sta varchar(5), @gestL1 varchar(30),
>        @nCurLar int, @nCurPro int, @tipoDeclass varchar(5),
>        @udcPred varchar(5), @udcTipo varchar(5), @Ord varchar(20), @claAlt
> int,
>        @claMov int, @fami varchar(5),
>        @corAbiIng bit, @corCulAbi int, @largTotCella int, @tipoCella
> varchar(5),
>        @curTipoCella varchar(5), @curClaAlt int,
>        @art varchar(50), @sub1 varchar(50), @sub2 varchar(50), @staMate
> varchar(5), @tipoConf varchar(5),
>        @tipoGest varchar(5), @fifoV int, @dProd datetime,
>        @ingChoiceArt int,
>        @corRis int, @celRis int, @pesoUdc decimal(8, 3),
>        @fTransaction bit,
>        @celSLato varchar(5), @ccLato bit,
>        @corLib int, @corLibDx int
>
> /* Inizializza valori ritorno e variabili */
> set @errore = ''
>
> set @retVal = 0
> set @corS = 0
> set @corD = 0
> set @celD = 0
> set @proD = 1
> set @posD = 1
>
> /* Controllo se sono già attive transazioni*/
> set @fTransaction = CASE WHEN @@trancount > 0 THEN 1 ELSE 0 END
>
> /* Apro una nuova transazione solo nel caso in cui non ci siano giÃ
> transazioni attive */
> if @fTransaction = 0
> BEGIN TRANSACTION
>
> /* Interblocco con gestione ricerca corridoi */
> EXEC @retVal = ws_L2_LockIngresso @errore OUTPUT
> if @retVal <> 0
> Goto exit_fn
>
> /* Ricerca dati missione */
> SELECT @udc = MIS_UDC, @corS = MIS_S_CORRIDOIO,
>       @corD = MIS_D_CORRIDOIO, @celD = MIS_D_CELLA, @fDestTeo = MIS_D_TEO,
>       @L2Sta = MIS_L2STA, @L1Sta = MIS_L1STA, @gestL1 = MIS_L1_GESTORE,
>       @culla = CASE WHEN @culla = 0 THEN MIS_TCULLA ELSE @culla END,
>       @udcPred = UDC_UDCPRED, @udcTipo = UDC_TIPOUDC, @tipoDeclass =
> UPR_TIPODECLASS,
>       @Ord = UDC_ORDINE, @claAlt = UDC_CLAALT,
>       @claMov = CASE WHEN UDC_ORDINE <> '' THEN 1 ELSE UDC_CLAMOV END,
>       @fami = UDC_FAMIUDC,
>       @corAbiIng = COR_ABIING, @corCulAbi = COR_CULABI,
>       @corRis = UDC_RIS_CORRIDOIO, @celRis = UDC_RIS_CELLA,
>       @pesoUdc = UDC_PESOREALE,
>       @celSLato = CEL_LATO, @ccLato = UDT_CC_LATO,
>       @corLib = COR_CELLE_LIB, @corLibDx = COR_CELLE_LIB_DX
> FROM RUN_MISSIONI
> INNER JOIN DAT_UDC ON (UDC_UDC = MIS_UDC)
> INNER JOIN DAT_UDC_PRED ON (UPR_UDCPRED = UDC_UDCPRED)
> INNER JOIN TIPI_UDC ON (UDT_TIPOUDC = UDC_TIPOUDC)
> INNER JOIN MAG_CORRIDOI ON (COR_CORRIDOIO = MIS_D_CORRIDOIO)
> INNER JOIN TIPI_ESITI_MISSIONI ON (ESM_ESMISS = MIS_ESMISS)
> INNER JOIN MAG_CELLE ON (CEL_CORRIDOIO = MIS_S_CORRIDOIO AND CEL_CELLA =
> MIS_S_CELLA)
> WHERE
> MIS_MISSIONE = @idMissione
> if @@rowcount < 1
> begin
> SELECT @retVal = -2, @errore = dbo.fn_td('Missione non trovata !')
>
> Goto exit_fn
> end
>
> /* Check vari */
> if (@L2Sta <> 'WI' and @L1Sta <> 'PR') or @corD = 0
> begin
> SELECT @retVal = -2, @errore = dbo.fn_td('Stato non corretto !')
> GoTo exit_fn
> end
>
> /* Il corridoio deve essere abilitato in ingresso solo se non si tratta di
> un cella-cella */
> if @corD <> @corS AND @corAbiIng = 0
> begin
> SELECT @retVal = 1, @errore = dbo.fn_td('Corridoio non abilitato !')
> GoTo exit_fn
> end
>
> /* Azzero la cella destinazione (se valorizzata), per calcolare quella
> ottima */
> if @celD > 0
> begin
> UPDATE RUN_MISSIONI SET MIS_D_CELLA = 0, MIS_D_TEO = 0 WHERE MIS_MISSIONE
> =
> @idMissione
> set @celD = 0
> end
>
> /* Se cella riservata da Udc --> seleziona tale cella (se non in errore e
> abilitata) */
> if @corRis = @corD and @celRis > 0
> begin
> SELECT TOP 1 @celD = CEL_CELLA,
>              @proD = CASE WHEN CEL_LARGTOT > 1 THEN 1 ELSE CEL_PROFMAX
> END,
>              @posD = ISNULL(CSL_POS, 1),
>              @largTotCella = CEL_LARGTOT, @tipoCella = CEL_TIPOCELLA,
>              @nCurLar = TIP_LARGTOT, @nCurPro = TIP_PROFTOT
> FROM MAG_CELLE
> INNER JOIN MAG_CORRIDOI ON (COR_CORRIDOIO = CEL_CORRIDOIO)
> INNER JOIN DAT_UDC_CEL ON (TIP_TIPOUDC = @udcTipo AND TIP_TIPOCELLA =
> CEL_TIPOCELLA)
> LEFT OUTER JOIN MAG_CELLE_LARG_SPAZI ON (CSL_CORRIDOIO = CEL_CORRIDOIO AND
> CSL_CELLA = CEL_CELLA)
> WHERE
> CEL_CORRIDOIO = @corRis AND
> CEL_CELLA = @celRis AND
> CEL_STACELLA = 'RIS' AND
> CEL_ABI = 1 AND
> CEL_INIBITA = 0 AND
> (CEL_CULMASK & @corCulAbi) <> 0 AND
> (@culla = 0 OR (CEL_CULMASK & power(2, @culla - 1)) <> 0)
> ORDER BY ISNULL(CSL_LARGFREE, 0), ISNULL(CSL_POS, 1)
>
> /* Se cella prenotata e' corretta, va alla parte di gestione; altrimenti,
> cerca cella secondo i criteri normali */
> if @celD > 0
>  goto fillCellaDest
>
> /* Nel caso di corridoi manuali se la cella forzata non e' raggiungibile
>    visualizzo errore, negli altri si lascia cercare in automatico un'altra
> cella */
> if @d_forzata = 1
> begin
>  SELECT @retVal = -1, @errore = dbo.fn_td('Cella di destinazione non
> raggiungibile !')
>  GoTo exit_fn
> end
> end
>
> /* Tabella che associa, ad ogni tipo cella, la max. prof. e la max.
> larghezza delle udc che puo' contenere */
> declare @tabTipoMaxProfLarg table (TIPOCELLA varchar(5) collate
> database_default,
>                                   PROFMAX int,
>                                   LARGMAX int
>                                  )
> INSERT INTO @tabTipoMaxProfLarg (TIPOCELLA, PROFMAX, LARGMAX)
> SELECT TIP_TIPOCELLA, MAX(TIP_PROFTOT), MAX(TIP_LARGTOT)
> FROM DAT_UDC_CEL
> GROUP BY TIP_TIPOCELLA
>
> /* Tabella celle libere */
> declare @tabTipoAltClaLato table (LATO varchar(5) collate
> database_default,
>                                  TIPOCELLA varchar(5) collate
> database_default,
>                                  MAXPESOUDC decimal(8, 3),
>                                  CLAALT int,
>                                  CLAMOV int,
>                                  TOT int
>                                 )
> INSERT INTO @tabTipoAltClaLato (LATO,
>                                TIPOCELLA, MAXPESOUDC, CLAALT, CLAMOV,
>                                TOT
>                               )
> SELECT CEL_LATO,
>        CEL_TIPOCELLA, CEL_MAXPESOUDC, CEL_CLAALT, CEL_CLAMOV,
>        SUM(CASE WHEN CEL_ABI = 1 AND
>                      CEL_STACELLA IN ('L', 'IP', 'IV') AND
>                      (CEL_CULMASK & COR_CULABI) <> 0 AND
>                      ((CEL_LARGTOT > 1 AND CEL_LARGMAX >= LARGMAX) OR
>                       (CEL_LARGTOT = 1 AND CEL_PROFMAX >= PROFMAX)
>                      )
>                 THEN 1
>                 ELSE 0
>                 END
>           )
> FROM MAG_CELLE
> INNER JOIN MAG_CORRIDOI ON (COR_CORRIDOIO = CEL_CORRIDOIO)
> INNER JOIN @tabTipoMaxProfLarg ON (TIPOCELLA = CEL_TIPOCELLA)
> WHERE CEL_CORRIDOIO = @corD AND
>       CEL_CELLA <> 0 AND
>       CEL_INIBITA = 0
> GROUP BY CEL_LATO,
>          CEL_TIPOCELLA, CEL_MAXPESOUDC, CEL_CLAALT, CEL_CLAMOV
>
> /* Se Udc monoreferenza --> cerca dati articolo contenuto */
> if @fami = 'M'
> begin
> exec @retVal = ws_UTI_GetDatiArticoloUdc @udc, @errore OUTPUT,
>                                          @art OUTPUT, @sub1 OUTPUT, @sub2
> OUTPUT,
>                                          @staMate OUTPUT, @tipoConf
> OUTPUT,
> @dProd OUTPUT,
>                                          @tipoGest OUTPUT, null, @fifoV
> OUTPUT
> if @retVal <> 0
>  GoTo exit_fn
>
> /* Parametro per indicare quali sottocodici considerare */
> set @ingChoiceArt = dbo.fn_getNumFromWork('ING_CHOICE_ART', 0)
> if @ingChoiceArt < 0
> begin
>  SELECT @retVal = -2, @errore = dbo.fn_td('Parametro scelta articolo in
> base ai sottocodici errato !')
>  GoTo exit_fn
> end
> end
>
> /* Loop su possibili tipi celle, altezze celle, classi movimentazione
> celle */
> DECLARE tip_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY
> FOR
> SELECT TIP_TIPOCELLA, TIP_LARGTOT, TIP_PROFTOT
> FROM DAT_UDC_CEL
> WHERE
> TIP_TIPOUDC = @udcTipo
> ORDER BY TIP_PRIO
>
> OPEN tip_cursor
>
> /* Loop: tipo cella, altezza, classe movimentazione */
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM tip_cursor INTO @curTipoCella, @nCurLar, @nCurPro
> if @@FETCH_STATUS <> 0
>  break
>
> DECLARE alt_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY
> FOR
>  SELECT ALT_CLAALT
>  FROM CLA_ALT
>  WHERE
>  ALT_CLAALT >= @claAlt
>  ORDER BY ALT_CLAALT
>
> OPEN alt_cursor
>
> WHILE 1 = 1
> BEGIN
>  FETCH NEXT FROM alt_cursor INTO @curClaAlt
>  if @@FETCH_STATUS <> 0
>   break
>
>    /* Tenta di accorpare in cella contenente altra Udc "analoga"
>      (si assume di non avere altre Udc incompatibili in cella, per non
> complicare antetempo
>       le queries
>      )
>    */
>    /* Casistica */
>    if @Ord <> ''
>    begin
>     /* Udc Ordine: Cerca cella con altra Udc dello stesso ordine */
>     SELECT TOP 1 @celD = CEL_CELLA, @proD = CEL_PROFMAX,
>                  @largTotCella = CEL_LARGTOT, @tipoCella = CEL_TIPOCELLA
>     FROM DAT_UDC WITH(INDEX(IX_DAT_UDC_ORDINE))
>     INNER JOIN MAG_CELLE WITH(INDEX(PK_MAG_CELLE)) ON (CEL_CORRIDOIO =
> UDC_CORRIDOIO AND CEL_CELLA = UDC_CELLA)
>     INNER JOIN @tabTipoAltClaLato AS tTipoAltClaLato ON
> (tTipoAltClaLato.LATO = CEL_LATO AND
>
> tTipoAltClaLato.TIPOCELLA = CEL_TIPOCELLA AND
>
> tTipoAltClaLato.MAXPESOUDC = CEL_MAXPESOUDC AND
>
> tTipoAltClaLato.CLAALT = CEL_CLAALT AND
>
> tTipoAltClaLato.CLAMOV = CEL_CLAMOV)
>     WHERE
>     UDC_UBIUDC = 'M' AND
>     UDC_STADISPUDC = 'D' AND
>     UDC_NESE = 0 AND
>     UDC_ORDINE = @ord AND
>     CEL_CORRIDOIO = @corD AND
>     (CEL_STACELLA IN ('L', 'IV') OR (@fDestTeo = 1 AND CEL_STACELLA =
> 'IP')) AND
>     CEL_ABI = 1 AND
>     CEL_INIBITA = 0 AND
>     (CEL_LARGTOT = 1 AND CEL_PROFMAX >= @nCurPro) AND
>     (CEL_CULMASK & @corCulAbi) <> 0 AND
>     (@culla = 0 OR (CEL_CULMASK & power(2, @culla - 1)) <> 0) AND
>     CEL_TIPOCELLA = @curTipoCella AND
>     CEL_CLAALT = @curClaAlt AND
>     (@tipoDeclass = 'T' OR
>      (@tipoDeclass = 'D' AND CEL_CLAMOV = @claMov) OR
>      (@tipoDeclass = 'A' AND CEL_CLAMOV >= @claMov)
>     ) AND
>     CEL_MAXPESOUDC >= @pesoUdc AND
>     (@corS <> @corD OR (@ccLato = 0 OR (CEL_LATO = @celSLato))) AND
>     (@corLibDx = 0 OR
>      CASE WHEN CEL_LATO = 'D' THEN @corLibDx ELSE @corLib - @corLibDx END
> <
> tTipoAltClaLato.TOT
>     )
>     ORDER BY CASE WHEN CEL_CLAMOV = @claMov THEN -1
>                   WHEN CEL_CLAMOV > @claMov THEN CEL_CLAMOV
>                   ELSE 9999999999 - CEL_CLAMOV
>                   END,
>              CEL_MAXPESOUDC,
>              CEL_PROFTOT, CEL_PROFMAX DESC,
>              CEL_PRIO
>     OPTION (FORCE ORDER)
>    end
>    ELSE
>    if @fami = 'M'
>    begin
>     /* Udc monoreferenza: Cerca cella con altra Udc monor. dello stesso
> articolo */
>     SELECT TOP 1 @celD = CEL_CELLA, @proD = CEL_PROFMAX,
>                  @largTotCella = CEL_LARGTOT, @tipoCella = CEL_TIPOCELLA
>     FROM DAT_SCOMPART WITH(INDEX(IX_DAT_SCOMPART_ARTICOLO))
>     INNER JOIN DAT_UDC WITH(INDEX(PK_DAT_UDC)) ON (UDC_UDC = SCO_UDC)
>     INNER JOIN MAG_CELLE WITH(INDEX(PK_MAG_CELLE)) ON (CEL_CORRIDOIO =
> UDC_CORRIDOIO AND CEL_CELLA = UDC_CELLA)
>     INNER JOIN @tabTipoAltClaLato AS tTipoAltClaLato ON
> (tTipoAltClaLato.LATO = CEL_LATO AND
>
> tTipoAltClaLato.TIPOCELLA = CEL_TIPOCELLA AND
>
> tTipoAltClaLato.MAXPESOUDC = CEL_MAXPESOUDC AND
>
> tTipoAltClaLato.CLAALT = CEL_CLAALT AND
>
> tTipoAltClaLato.CLAMOV = CEL_CLAMOV)
>     WHERE
>     UDC_UBIUDC = 'M' AND
>     UDC_STADISPUDC = 'D' AND
>     UDC_STAPRENUDC = 'D' AND
>     UDC_NESE = 0 AND
>     UDC_FAMIUDC = 'M' AND
>     SCO_ARTICOLO = @art AND
>     ((@ingChoiceArt & 1) = 0 OR SCO_SUB1 = @sub1) AND
>     ((@ingChoiceArt & 2) = 0 OR SCO_SUB2 = @sub2) AND
>     ((@ingChoiceArt & 4) = 0 OR SCO_STAMATE = @staMate) AND
>     ((@ingChoiceArt & 8) = 0 OR SCO_TIPOCONF = @tipoConf) AND
>     (@tipoGest = 'V' or
>      abs(datediff(hh, SCO_DPRO, @dProd)) <= @fifoV
>     ) AND
>     CEL_CORRIDOIO = @corD AND
>     (CEL_STACELLA IN ('L', 'IV') OR (@fDestTeo = 1 AND CEL_STACELLA =
> 'IP')) AND
>     CEL_ABI = 1 AND
>     CEL_INIBITA = 0 AND
>     (CEL_LARGTOT = 1 AND CEL_PROFMAX >= @nCurPro) AND
>     (CEL_CULMASK & @corCulAbi) <> 0 AND
>     (@culla = 0 OR (CEL_CULMASK & power(2, @culla - 1)) <> 0) AND
>     CEL_TIPOCELLA = @curTipoCella AND
>     CEL_CLAALT = @curClaAlt AND
>     (@tipoDeclass = 'T' OR
>      (@tipoDeclass = 'D' AND CEL_CLAMOV = @claMov) OR
>      (@tipoDeclass = 'A' AND CEL_CLAMOV >= @claMov)
>     ) AND
>     CEL_MAXPESOUDC >= @pesoUdc AND
>     (@corS <> @corD OR (@ccLato = 0 OR (CEL_LATO = @celSLato))) AND
>     (@corLibDx = 0 OR
>      CASE WHEN CEL_LATO = 'D' THEN @corLibDx ELSE @corLib - @corLibDx END
> <
> tTipoAltClaLato.TOT
>     )
>     ORDER BY CASE WHEN CEL_CLAMOV = @claMov THEN -1
>                   WHEN CEL_CLAMOV > @claMov THEN CEL_CLAMOV
>                   ELSE 9999999999 - CEL_CLAMOV
>                   END,
>              CEL_MAXPESOUDC,
>              abs(datediff(hh, SCO_DPRO, @dProd)),
>              CEL_PROFTOT, CEL_PROFMAX DESC,
>              CEL_PRIO
>     OPTION (FORCE ORDER)
>    end
>    ELSE
>    if @fami = 'V'
>    begin
>     /* Udc vuota: Cerca cella con altra Udc vuota dello stesso tipo
> predefinito */
>     SELECT TOP 1 @celD = CEL_CELLA, @proD = CEL_PROFMAX,
>                  @largTotCella = CEL_LARGTOT, @tipoCella = CEL_TIPOCELLA
>     FROM DAT_UDC
>     INNER JOIN MAG_CELLE ON (CEL_CORRIDOIO = UDC_CORRIDOIO AND CEL_CELLA =
> UDC_CELLA)
>     INNER JOIN @tabTipoAltClaLato AS tTipoAltClaLato ON
> (tTipoAltClaLato.LATO = CEL_LATO AND
>
> tTipoAltClaLato.TIPOCELLA = CEL_TIPOCELLA AND
>
> tTipoAltClaLato.MAXPESOUDC = CEL_MAXPESOUDC AND
>
> tTipoAltClaLato.CLAALT = CEL_CLAALT AND
>
> tTipoAltClaLato.CLAMOV = CEL_CLAMOV)
>     WHERE
>     UDC_UBIUDC = 'M' AND
>     UDC_STADISPUDC = 'D' AND
>     UDC_STAPRENUDC = 'D' AND
>     UDC_NESE = 0 AND
>     UDC_FAMIUDC = 'V' AND
>     UDC_UDCPRED = @udcPred AND
>     CEL_CORRIDOIO = @corD AND
>     (CEL_STACELLA IN ('L', 'IV') OR (@fDestTeo = 1 AND CEL_STACELLA =
> 'IP')) AND
>     CEL_ABI = 1 AND
>     CEL_INIBITA = 0 AND
>     (CEL_LARGTOT = 1 AND CEL_PROFMAX >= @nCurPro) AND
>     (CEL_CULMASK & @corCulAbi) <> 0 AND
>     (@culla = 0 OR (CEL_CULMASK & power(2, @culla - 1)) <> 0) AND
>     CEL_TIPOCELLA = @curTipoCella AND
>     CEL_CLAALT = @curClaAlt AND
>     (@tipoDeclass = 'T' OR
>      (@tipoDeclass = 'D' AND CEL_CLAMOV = @claMov) OR
>      (@tipoDeclass = 'A' AND CEL_CLAMOV >= @claMov)
>     ) AND
>     CEL_MAXPESOUDC >= @pesoUdc AND
>     (@corS <> @corD OR (@ccLato = 0 OR (CEL_LATO = @celSLato))) AND
>     (@corLibDx = 0 OR
>      CASE WHEN CEL_LATO = 'D' THEN @corLibDx ELSE @corLib - @corLibDx END
> <
> tTipoAltClaLato.TOT
>     )
>     ORDER BY CASE WHEN CEL_CLAMOV = @claMov THEN -1
>                   WHEN CEL_CLAMOV > @claMov THEN CEL_CLAMOV
>                   ELSE 9999999999 - CEL_CLAMOV
>                   END,
>              CEL_MAXPESOUDC,
>              CEL_PROFTOT, CEL_PROFMAX DESC,
>              CEL_PRIO
>    end
>
>    /* Se trovata cella, va alla parte di gestione */
>    if @celD > 0
>     goto fillCellaDest
>
>    /* Se non effettuato accorpamento, ricerca cella libera */
>    SELECT TOP 1 @celD = CEL_CELLA, @proD = CASE WHEN CEL_LARGTOT > 1 THEN
> 1
> ELSE CEL_PROFMAX END,
>                 @posD = ISNULL(CSL_POS, 1),
>                 @largTotCella = CEL_LARGTOT, @tipoCella = CEL_TIPOCELLA
>    FROM MAG_CELLE
>    INNER JOIN @tabTipoAltClaLato AS tTipoAltClaLato ON
> (tTipoAltClaLato.LATO = CEL_LATO AND
>
> tTipoAltClaLato.TIPOCELLA = CEL_TIPOCELLA AND
>
> tTipoAltClaLato.MAXPESOUDC = CEL_MAXPESOUDC AND
>
> tTipoAltClaLato.CLAALT = CEL_CLAALT AND
>
> tTipoAltClaLato.CLAMOV = CEL_CLAMOV)
>    LEFT OUTER JOIN MAG_CELLE_LARG_SPAZI ON (CSL_CORRIDOIO = CEL_CORRIDOIO
> AND CSL_CELLA = CEL_CELLA)
>    WHERE
>    CEL_CORRIDOIO = @corD AND
>    (CEL_STACELLA IN ('L', 'IV') OR (@fDestTeo = 1 AND CEL_STACELLA =
> 'IP'))
> AND
>    CEL_ABI = 1 AND
>    CEL_INIBITA = 0 AND
>    ((CEL_LARGTOT > 1 AND /*CEL_LARGMAX >= @nCurLar*/ ISNULL(CSL_LARGFREE,
> 0) >= @nCurLar) OR
>     (CEL_LARGTOT = 1 AND CEL_PROFMAX >= @nCurPro)
>    ) AND
>    (CEL_CULMASK & @corCulAbi) <> 0 AND
>    (@culla = 0 OR (CEL_CULMASK & power(2, @culla - 1)) <> 0) AND
>    CEL_TIPOCELLA = @curTipoCella AND
>    CEL_CLAALT = @curClaAlt AND
>    (@tipoDeclass = 'T' OR
>     (@tipoDeclass = 'D' AND CEL_CLAMOV = @claMov) OR
>     (@tipoDeclass = 'A' AND CEL_CLAMOV >= @claMov)
>    ) AND
>    CEL_MAXPESOUDC >= @pesoUdc AND
>    (@corS <> @corD OR (@ccLato = 0 OR (CEL_LATO = @celSLato))) AND
>    (@corLibDx = 0 OR
>     CASE WHEN CEL_LATO = 'D' THEN @corLibDx ELSE @corLib - @corLibDx END <
> tTipoAltClaLato.TOT
>    )
>    ORDER BY CASE WHEN CEL_CLAMOV = @claMov THEN -1
>                  WHEN CEL_CLAMOV > @claMov THEN CEL_CLAMOV
>                  ELSE 9999999999 - CEL_CLAMOV
>                  END,
>             CEL_MAXPESOUDC,
>             CEL_PROFTOT, ISNULL(CSL_LARGFREE, 0), CEL_PROFMAX DESC,
>             CEL_PRIO
>    /* Se trovata cella, va alla parte di gestione */
>    if @celD > 0
>     goto fillCellaDest
> END
>
> CLOSE alt_cursor
> DEALLOCATE alt_cursor
> END
>
> fillCellaDest:
>
> /* Cella non trovata */
> if @celD <= 0
> begin
> SELECT @retVal = 1, @errore = dbo.fn_td('Non trovata cella in corridoio
> !')
> GoTo exit_fn
> end
>
> /* Sistemazione profondita' */
> if @largTotCella = 1
> set @proD = @proD - @nCurPro + 1 +
>             ISNULL((SELECT SUM(ISNULL(TIP_PROFTOT, 1))
>                     FROM RUN_MISSIONI
>                     INNER JOIN DAT_UDC ON (UDC_UDC = MIS_UDC)
>                     LEFT OUTER JOIN DAT_UDC_CEL ON (TIP_TIPOUDC =
> UDC_TIPOUDC AND TIP_TIPOCELLA = @tipoCella)
>                     WHERE
>                     MIS_D_CORRIDOIO = @corD AND
>                     MIS_D_CELLA = @celD AND
>                     MIS_D_TEO = 1
>                    ), 0)
>
> setCellaDest:
>
> /* Setta cella destinazione sulla missione */
> if @L2Sta = 'WI'
> begin
> UPDATE RUN_MISSIONI SET MIS_L2STA = 'WL', MIS_L1STA = 'PR',
>                         MIS_D_CELLA = @celD, MIS_D_POS = @posD, MIS_D_PRO
> =
> @proD,
>                         MIS_TCULLA = CASE WHEN @culla > 0 THEN @culla ELSE
> MIS_TCULLA END,
>                         MIS_L1PACO = 1, MIS_L1STCO = 1, MIS_L1OPCO = 1,
>                         MIS_NOTE = ''
> WHERE
> MIS_MISSIONE = @idMissione
> end
> ELSE
> begin
> UPDATE RUN_MISSIONI SET MIS_D_CELLA = @celD, MIS_D_POS = @posD, MIS_D_PRO
> =
> @proD,
>                         MIS_TCULLA = CASE WHEN @culla > 0 THEN @culla ELSE
> MIS_TCULLA END,
>                         MIS_NOTE = ''
> WHERE
> MIS_MISSIONE = @idMissione
> end
>
>
> /* Eventuale sprenotazione cella non scelta */
> if @corRis <> 0 AND @celRis <> 0 AND (@corD <> @corRis OR @celD <>
> @celRis)
>
> begin
> UPDATE DAT_UDC SET UDC_RIS_CORRIDOIO = 0, UDC_RIS_CELLA = 0
> WHERE
> UDC_UDC = @udc
> end
>
> /* Ottimizzazione: pone missione subito in esecuzione (normalmente non
> esistono prerequisiti) */
> if @L2Sta = 'WI'
> begin
> exec @retVal = ws_L1_GestMissPrq @gestL1, null, @errore OUTPUT,
> @idMissione
> if @retVal <> 0
>  goto exit_fn
> end
>
> exit_fn:
> if @fTransaction = 0
> begin
>  if @retVal <> 0
>  begin
>   ROLLBACK TRANSACTION
>   set @errore = dbo.fn_InsTextStr(dbo.fn_td('Missione {0}, Udc {1} : '),
> ltrim(str(@idMissione)), ltrim(str(@udc)), default, default, default) +
> @errore
>  end
>  ELSE
>   COMMIT TRANSACTION
> end
>
> return @retVal
>
> /*=============END CODE==============*/

"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you show the procedure call that generates this error?  Can you show the
> definition for dbo.fn_td and ws_L1_GestMissPrq?  Can you identify line 539
> in this code?  (Because I think the line #s are messed up due to word wrap.)
>
> A

"checcouno" <checco***@discussions.microsoft.com> wrote in message
news:1459FD71-8841-4866-BBD3-240066C5F3AE@microsoft.com...
>
> Ok: error line in my error message is 474 (the previous was wrong, sorry)
> and line 474 is:
> SELECT @retVal = 1, @errore = dbo.fn_td('Non trovata cella in corridoio
!')
>
>
> /* My code */
> ...
>  if @celD <= 0
>  begin
>  SELECT @retVal = 1, @errore = dbo.fn_td('Non trovata cella in corridoio
!')
>  GoTo exit_fn
>  end
>
>  /* Sistemazione profondita' */
>  if @largTotCella = 1
>  set @proD = @proD - @nCurPro + 1 +
>  ISNULL((SELECT SUM(ISNULL(TIP_PROFTOT, 1))
>  FROM RUN_MISSIONI
>  INNER JOIN DAT_UDC ON (UDC_UDC = MIS_UDC)
>  LEFT OUTER JOIN DAT_UDC_CEL ON (TIP_TIPOUDC =
>  UDC_TIPOUDC AND TIP_TIPOCELLA = @tipoCella)
>  WHERE
>  MIS_D_CORRIDOIO = @corD AND
>  MIS_D_CELLA = @celD AND
>  MIS_D_TEO = 1 ), 0)
>
>  setCellaDest:
>
> ...
>
> I post also the fn_td function:
>
> /* Funzione per la traduzione dei testi
>    Parametri: messaggio in italiano
>    Rende: Messaggio tradotto nella lingua dell'utente Sql connesso
> */
> ALTER        FUNCTION [dbo].[fn_td](@msgIta as varchar(500))
> RETURNS varChar(500)
> AS
> BEGIN
>  declare @codLingua as varchar(5),
>          @msg as varChar(500)
>
>  /* Se lingua Italiana --> rende messaggio invariato */
>  if @@language = 'ITALIANO'
>   return @msgIta
>
>  /* Normalizza lingua ai primi 5 caratteri di @@language */
>  set @codLingua = left(@@language, 5)
>
>  /* Ricava messaggio tradotto
>     (se non lo trova, mette il messaggio non tradotto)  */
>  SELECT TOP 1 @msg = LAN_TRA
>  FROM UTI_LINGUA
>  WHERE LAN_TIPOLINGUA = @codLingua AND
>        LAN_ITA = @msgIta
>  if ISNULL(@msg, '') = '' set @msg = @msgIta
>
>  return @msg
> END
>
>
> In this function i translate my strings depending on @@language. In this
> case i don't translate because @@language is ITALIANO.
>
> What i don't unterstand is why this error is raised up only sometimes
while
> the sp is called everytime! And adding begin end the problem disappears...
>
> Thanks!
>
>
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
> > Can you show the procedure call that generates this error?  Can you show
the
> > definition for dbo.fn_td and ws_L1_GestMissPrq?  Can you identify line
539
> > in this code?  (Because I think the line #s are messed up due to word
wrap.)
> >
> > A
>
>

"Jim Underwood" wrote:

>
> If the problem only occurs sometimes, then how do you know that it
> disapeared?  In order to know it is fixed, first you have to be able to
> reproduce it.
>
> I am assuming that the error only occurs when certain parameters are passed
> to the stored procedure, in which case you need to determine what those
> parameters are in order to find the problem.
>

"checcouno" <checco***@discussions.microsoft.com> wrote in message
news:0F181134-2A69-489E-98DF-1B3CAF66AD60@microsoft.com...
> Well the error starts raising random, but when start raising, it ever
raise
> untile i don't recomplie the stored procedure, then for a time (few hours)
it
> works perfectly then stars again to raise error...
> When is raising if i don't recompile (or i add the begin end) it coninute
to
> raise and catching my sp call.
> If i add begin end the problems disappears and never catch again.
>
> Thanks
>
>
> "Jim Underwood" wrote:
>
> >
> > If the problem only occurs sometimes, then how do you know that it
> > disapeared?  In order to know it is fixed, first you have to be able to
> > reproduce it.
> >
> > I am assuming that the error only occurs when certain parameters are
passed
> > to the stored procedure, in which case you need to determine what those
> > parameters are in order to find the problem.
> >
>

"Aaron Bertrand [SQL Server MVP]" wrote:

> I think the syntax error is raised elsewhere, since there is no standalone
> 's' here, and since I can't generate the error with or without the begin/end
> using a syntax check.  Can you show us all of the code so we can attempt to
> reproduce it and don't have to figure out all of your variable declarations?
>
>
>
> "checcouno" <checco***@discussions.microsoft.com> wrote in message
> news:64C0426D-8AD6-4C2C-A108-9FBB68F6E380@microsoft.com...
> >I found a strange behaviour in SQL 2005.
> > In my soterd proc i've got something like this:
> >
> > /* My Code (wrong???)*/
> > ...
> > if @MyVariable1 = 1
> > set @proD = @proD - @nCurPro + 1 +
> >             ISNULL((SELECT SUM(ISNULL(TIP_PROFTOT, 1))
> >                     FROM RUN_MISSIONI
> >                     INNER JOIN DAT_UDC ON (UDC_UDC = MIS_UDC)
> >                     LEFT OUTER JOIN DAT_UDC_CEL ON (TIP_TUDC = UDC_TUDC
> >                     AND TIP_TIPOCELLA = @tipoCella)
> >                     WHERE
> >                     MIS_D_CORRIDOIO = @corD AND
> >                     MIS_D_CELLA = @celD AND
> >                     MIS_D_TEO = 1
> >                    ), 0)
> >
> > setCellaDest:
> >  ...
> > /* End My Code (wrong???)*/
> >
> > My stored proc usually work well without problems. Sometimes (random)
> > start
> > raising error:
> > Msg 102, Level 15, State 1, Procedure ws_L2_SetCellaMiss, Line 539
> > Incorrect syntax near s.
> >
> > Using the begin end the procedure works well ever, without problems:
> >
> > /* My Code (good)*/
> > ...
> > if @MyVariable1 = 1
> > begin
> > set @proD = @proD - @nCurPro + 1 +
> >             ISNULL((SELECT SUM(ISNULL(TIP_PROFTOT, 1))
> >                     FROM RUN_MISSIONI
> >                     INNER JOIN DAT_UDC ON (UDC_UDC = MIS_UDC)
> >                     LEFT OUTER JOIN DAT_UDC_CEL ON (TIP_TUDC = UDC_TUDC
> >                     AND TIP_TIPOCELLA = @tipoCella)
> >                     WHERE
> >                     MIS_D_CORRIDOIO = @corD AND
> >                     MIS_D_CELLA = @celD AND
> >                     MIS_D_TEO = 1
> >                    ), 0)
> > end
> > setCellaDest:
> >  ...
> > /* End My Code (good)*/
> >
> > I call my procedure from a c# application with ADO.net.  What i d'ont
> > understand is that teh procedure gives a SINTAX ERROR! If i recompile the
> > procedure from SQL Server management studio the procedure recompiles well,
> > withou error, and for a bit it works well. After a bit the error raise
> > again.
> > Someone has seen something like this?
> >
> > Thanks!
> >
>
>
>