[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL 2000 - Query Syntax Error

Posted on 2014-01-15
3
Medium Priority
?
740 Views
Last Modified: 2014-01-15
Hello Experts,

I am getting this error on SQL Server 2000:

CREATE PROCEDURE dbo.AcpReport_HR_CoilProduction
      
AS
BEGIN
      
      SET NOCOUNT ON;
   
      DECLARE @Braze1Shift TABLE(SNo INT NOT NULL IDENTITY(1,1), JobTitle Varchar(255) NOT NULL ,   Name Varchar(255) NOT NULL ,  NUM Varchar(255) NOT NULL  );

      SET IDENTITY_INSERT @Braze1Shift ON;
      
      Insert Into @Braze1Shift(JobTitle,Name,NUM )
      SELECT JobTitle, LastName + ',' + FirstName as 'Name', NUM
      FROM [AIO_Test_Results].[dbo].[EmployeeInfo] Where [Group] = 'PRODUCTION'
      and Dept = 'BRAZE' and TRAIN IS NULL and Jobtitle <> 'LEAD' AND Shift = 1
      Order By LastName

      SET IDENTITY_INSERT @Braze1Shift OFF;

      Select * from @Braze1Shift;
   
   
END
GO



Error:

Msg 170, Level 15, State 1, Procedure AcpReport_HR_CoilProduction, Line 14
Line 14: Incorrect syntax near '@Braze1Shift'.

Msg 170, Level 15, State 1, Procedure AcpReport_HR_CoilProduction, Line 21
Line 21: Incorrect syntax near '@Braze1Shift'.
0
Comment
Question by:mani_sai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 2

Accepted Solution

by:
Larso76 earned 2000 total points
ID: 39782441
Change script to

CREATE PROCEDURE dbo.AcpReport_HR_CoilProduction
     
AS
BEGIN
     
      SET NOCOUNT ON;
   
     create TABLE #Braze1shift (SNo INT NOT NULL IDENTITY(1,1), JobTitle Varchar(255) NOT NULL ,   Name Varchar(255) NOT NULL ,  NUM Varchar(255) NOT NULL  );

      SET IDENTITY_INSERT Braze1Shift ON;
     
      Insert Into #Braze1shift(JobTitle,Name,NUM )
      SELECT JobTitle, LastName + ',' + FirstName as 'Name', NUM
      FROM [AIO_Test_Results].[dbo].[EmployeeInfo] Where [Group] = 'PRODUCTION'
      and Dept = 'BRAZE' and TRAIN IS NULL and Jobtitle <> 'LEAD' AND Shift = 1
      Order By LastName

      SET IDENTITY_INSERT #Braze1Shift OFF;

      Select * from #Braze1Shift;
   
   
END
GO
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39782468
SQL 2000 doesn't have table variables. You need to use temporary tables as per the previous comment.
0
 
LVL 8

Author Closing Comment

by:mani_sai
ID: 39782487
Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question