Procedure in SQL 2000.

LelloLello
LelloLello used Ask the Experts™
on
I have a stored procedure on my SQL 2000. How i can run this in SQL 2000.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
exec YourStoredProcedureName

Author

Commented:
So i have two, this the first one
create procedure sp_CreateYear(@NewYear integer)
as
begin
  declare @local_var integer
  declare @kount integer

truncate table Year_Prep

select @kount = count(*)
from name
where ID NOT IN (select id from CIA_MULTIPLE where [CIA_YEAR] = @NewYear)

update Counter set
  LAST_VALUE = LAST_VALUE+@kount,
  LAST_UPDATED = getdate(),
  UPDATED_BY = user_name()
  where COUNTER_NAME = 'CIA_MULTIPLE'

select @local_var = LAST_VALUE
from Counter
where COUNTER_NAME = 'CIA_MULTIPLE'

select @local_var = @local_var - @kount

insert into Year_Prep (ID, NEW_YEAR)
select ID, @NewYear
from name
where ID NOT IN (select id from CIA_MULTIPLE where [CIA_YEAR] = @NewYear)

insert into CIA_MULTIPLE (ID, SEQN, [CIA_YEAR])
select ID, SEQN+@local_var, NEW_YEAR
from Year_Prep

end


GO


this is the second one.



CREATE  PROCEDURE sp_CreateCIAYearDescendingOrder AS
/* -------------------------------------------------------------*
 * Project:             
 * iMIS:            
 * Created By:        
 * Last Modified:        09/22/03

records are inserted once a year into the two tables cia_multiple and IAA_MULTIPLE. When the user receives the
data from the member the information is entered into the current year.

They want the current year at the top - descending order. Easier to review/enter most current year, avoids
scrolling down.


 *______________________________________________________________*/

SET NOCOUNT ON

declare @SPMSG             varchar(255)
declare @nError       integer
declare @bTrans            bit

declare @nprep_count      integer
declare @nprod_count      integer

--Clear temp table
truncate table CIA_MULTIPLE_PREP

select @nError=@@ERROR
if @nError<>0
BEGIN
   select @SPMSG =  'ERROR - Truncate CIA_MULTIPLE_PREP'
   goto SP_ERR
END

--Load temp table in proper order with year descending
Insert CIA_MULTIPLE_PREP
(
[ID],
[CIA_YEAR],
[BALLOT_1],
[BALLOT_2],
[IAA_BATCH],
[IAA_ASTIN],
[IAA_AFIR],
[IAA_IAA],
[IAA_IACA]
)
select
[ID],
[CIA_YEAR],
[BALLOT_1],
[BALLOT_2],
[IAA_BATCH],
[IAA_ASTIN],
[IAA_AFIR],
[IAA_IAA],
[IAA_IACA]
from CIA_MULTIPLE
order by ID, [CIA_YEAR] DESC

select @nError=@@ERROR
if @nError<>0
BEGIN
   select @SPMSG =  'ERROR - Insert CIA_MULTIPLE_PREP'
   goto SP_ERR
END

select @nprep_count = count(*) from  CIA_MULTIPLE_PREP
select @nprod_count = count(*) from  CIA_MULTIPLE

select '@nprep_count = ' + cast(@nprep_count as varchar(10))
select '@nprod_count = ' + cast(@nprod_count as varchar(10))

if @nprep_count <> @nprod_count BEGIN
   select @SPMSG =  'ERROR - Recounts do not match CIA_MULTIPLE_PREP/CIA_MULTIPLE'
   goto SP_ERR
   END

begin   transaction
Set @bTrans = 1

--clear ud table
truncate table CIA_MULTIPLE

select @nError=@@ERROR
if @nError<>0
BEGIN
   select @SPMSG =  'ERROR - Truncate CIA_MULTIPLE'
   goto SP_ERR
END

--Load ud table from temp table with year descending
Insert CIA_MULTIPLE
(
[ID],
[SEQN],
[CIA_YEAR],
[BALLOT_1],
[BALLOT_2],
[IAA_BATCH],
[IAA_ASTIN],
[IAA_AFIR],
[IAA_IAA],
[IAA_IACA]
)
select
[ID],
[SEQN],
[CIA_YEAR],
[BALLOT_1],
[BALLOT_2],
[IAA_BATCH],
[IAA_ASTIN],
[IAA_AFIR],
[IAA_IAA],
[IAA_IACA]
from CIA_MULTIPLE_PREP
order by ID, [CIA_YEAR] DESC

select @nError=@@ERROR
if @nError<>0
BEGIN
   select @SPMSG =  'ERROR - Insert CIA_MULTIPLE_PREP'
   goto SP_ERR
END

declare @maxSeqn int

select @maxSeqn = max(seqn) from cia_multiple

update counter
set last_value = @maxSeqn
from counter
where counter_name = 'CIA_MULTIPLE'

select @SPMSG =  'Complete'
select @SPMSG

SP_EXIT:

if @bTrans = 1 begin
      If @nError <> 0
            rollback  transaction
      else
            commit transaction
      end

return @nError

SP_ERR:
select @SPMSG =  @SPMSG + ' ERROR = '+ convert(varchar(50),@nError) + ' DateTime = '+ convert(varchar(50),GetDate())

RAISERROR ('sp_IMPORTFR_Receipts: %d ', 25, 1, @SPMSG)  with LOG

goto SP_EXIT

/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CIA_MULTIPLE_PREP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CIA_MULTIPLE_PREP]

CREATE TABLE [dbo].[CIA_MULTIPLE_PREP] (
      [ID] [varchar] (10) NOT NULL ,
      [SEQN] [int] IDENTITY NOT NULL ,
      [CIA_YEAR] [float] NOT NULL ,
      [BALLOT_1] [int] NOT NULL ,
      [BALLOT_2] [int] NOT NULL ,
      [IAA_BATCH] [int] NOT NULL ,
      [IAA_ASTIN] [bit] NOT NULL ,
      [IAA_AFIR] [bit] NOT NULL ,
      [IAA_IAA] [bit] NOT NULL ,
      [IAA_IACA] [bit] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[CIA_MULTIPLE_PREP] WITH NOCHECK ADD
      CONSTRAINT [DF__CIAP_MULTIPLE__ID__65C116E7] DEFAULT ('') FOR [ID],
      CONSTRAINT [DF__CIAP_MULTI__CIA_Y__67A95F59] DEFAULT (0) FOR [CIA_YEAR],
      CONSTRAINT [DF__CIAP_MULTI__BALLO__689D8392] DEFAULT (0) FOR [BALLOT_1],
      CONSTRAINT [DF__CIAP_MULTI__BALLO__6991A7CB] DEFAULT (0) FOR [BALLOT_2],
      CONSTRAINT [DF__CIAP_MULTI__IAA_B__6A85CC04] DEFAULT (0) FOR [IAA_BATCH],
      CONSTRAINT [DF__CIAP_MULTI__IAA_A__6B79F03D] DEFAULT (0) FOR [IAA_ASTIN],
      CONSTRAINT [DF__CIAP_MULTI__IAA_A__6C6E1476] DEFAULT (0) FOR [IAA_AFIR],
      CONSTRAINT [DF__CIAP_MULTI__IAA_I__6D6238AF] DEFAULT (0) FOR [IAA_IAA],
      CONSTRAINT [DF__CIAP_MULTI__IAA_I__6E565CE8] DEFAULT (0) FOR [IAA_IACA],
      CONSTRAINT [pkCIAP_MULTIPLEID] PRIMARY KEY  CLUSTERED
      (
            [ID],
            [SEQN]
      )  ON [PRIMARY]


SELECT     ID, SEQN, CIA_YEAR
FROM         CIA_MULTIPLE   --CIATest db
WHERE     (ID = '650069')

--BEFORE
ID         SEQN        CIA_YEAR                                              
---------- ----------- -----------------------------------------------------
650069     55          2002.0
650069     4041        2003.0
650069     6467        1999.0
650069     7207        1998.0
650069     7959        1997.0
650069     8506        1996.0

--After
ID         SEQN        CIA_YEAR                                              
---------- ----------- -----------------------------------------------------
650069     1103        2003.0
650069     1104        2002.0
650069     1105        1999.0
650069     1106        1998.0
650069     1107        1997.0
650069     1108        1996.0


SELECT     ID, SEQN, YEAR
FROM         IAA_MULTIPLE  -- IAATest db
WHERE     (ID = '100001')

--BEFORE
ID         SEQN        YEAR
---------- ----------- ----
100001     70774       2003
100001     34208       2002
100001     2           2001
100001     165         2000
100001     0           1999

--After
ID         SEQN        YEAR
---------- ----------- ----
100001     6           2003
100001     7           2002
100001     8           2001
100001     9           2000
100001     10          1999

*/

GO

Author

Commented:
where i can type this... i didn't find the task ?

exec YourStoredProcedureName
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
There are a variety of ways.  Easiest might be run Sql Server's Enterprise Manager and open a query window connecting to the sql server.  Put this in the window and execute:

Exec myprocnamehere

You might make sure you know what it does before running it as it might need to be run from a certain account or a certain database.

Author

Commented:
my sql

Author

Commented:
How do you open a query window connecting to the sql server.  Put this in the window and execute:  I have SQL Server Enterprise Manager 2000.

Author

Commented:
I have SQL Server Group
            LOCAL
                   Databases
                           MyDATABASE
                                       Stored Procedures.

Author

Commented:
Any idea where i should type the exec ????

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial