SEO

#1 (**permalink**) 02-28-2008, 06:48 PM

Hi all,
We have stored procedure, which basically strips a header and footer of
a table (first and last line). The procedure is written like so:

CREATE procedure stage.usp_StripSMMT
as

declare @count int
declare @smmt_count int
declare @check char(3)

select @count = (select count(*) from stage.smmt_test)

select @smmt_count = (select substring(linein, 76, 5) from
stage.smmt_test
where pk = @count)

if (@count - @smmt_count) = 2
begin
delete from stage.smmt_test where pk in (1, @count)
end

GO

Ever since I accidently over wrote this (with exactly the same
procedure!) its suddenly stopped working!?

Any ideas would be most gratefully received.
Sam

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#2 (**permalink**) 02-28-2008, 06:49 PM

Hi

In what way is it not working?

Is it that you have not granted permissions to run it?

Do you get non numeric values from
select substring(linein, 76, 5) from stage.smmt_test ?

Have you changed the ANSI_NULLS setting for the stored procedure?

You have not posted DDL and example data for the tables used. If pk is an
identity, the highest value will almost certainly not be the number of rows
in the table.

John

"Sam G" <moby@spamhole.com> wrote in message
news:3fb4ee22$0$202$75868355@news.frii.net...
> Hi all,
> We have stored procedure, which basically strips a header and footer of
> a table (first and last line). The procedure is written like so:
>
> CREATE procedure stage.usp_StripSMMT
> as
>
> declare @count int
> declare @smmt_count int
> declare @check char(3)
>
> select @count = (select count(*) from stage.smmt_test)
>
> select @smmt_count = (select substring(linein, 76, 5) from
> stage.smmt_test
> where pk = @count)
>
> if (@count - @smmt_count) = 2
> begin
> delete from stage.smmt_test where pk in (1, @count)
> end
>
> GO
>
> Ever since I accidently over wrote this (with exactly the same
> procedure!) its suddenly stopped working!?
>
> Any ideas would be most gratefully received.
> Sam
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Corrupt Stored Procedure?