Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    Lightbulb Unanswered: Problem when creating SP in sql2005

    Hi All,

    I am trying to create this stored procedure but it keeps complaining about this line:

    Code:
    Naam_Spel = @Naam_Spel,
    probably it is something stupid but i keep staring at it frustrates me!

    heres the complete sp:

    Code:
    USE [Ebdata]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROC UpdateEprom
    
    
    		   @EpromID bigint,
    		   @Naam_Spel  varchar(50),
               @Versie  varchar(10),
               @Checksum1 varchar(8),
               @Checksum2 varchar(8),
               @Omschrijving varchar(MAX),
               @Datum_vrijgave datetime,
               @EpromType int,
               @Land varchar (50),
               @Kabinet varchar(50),
               @Merk int,
               @Wet int,
               @EpromFile varchar(100),
               @EpromSize varchar(100),
               @SourceFile varchar(100),
               @SourceSize varchar(100),
               @Active int,
               @UpdateUser uniqueidentifier
    
    		AS
    		update EB_Eprom
    		SET 
    			Naam_Spel = @Naam_Spel,
    			Versie = @Versie,
    			Checksum1 = @Checksum1,
    			Checksum2 = @Checksum2,
    			Omschrijving = @Omschrijving,
    			Datum_Vrijgave = @Datum_Vrijgave,
    			EpromType = @EpromType,
    			LAnd = =@Land,
    			Kabinet = @Kabinet,
    			Merk = @Merk,
    			Wet = @wet,
    			EpromFile = @EpromFile,
    			EpromSize = @EpromSize,
    			SourceFile = @SourceFile,
    			SourceSize = @SourceSize,
    			Active = @Active,
    			UpdateUser = @UpdateUser,
    			UpdateDate = getdate()
    
    
    
    			where EpromID = @EpromID
    Hope that someone don't have the same stringproblems as i have!

    Cheers Wimmo

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    MAX isn't a valid dimension as used in
    Code:
    @Omschrijving varchar(MAX),
    -PatP

  3. #3
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Thanx for your reply and sorry for the double post,

    I changed it to 100 but still gives me the same error
    btw this is the error i get:

    /Msg 102, Level 15, State 1, Procedure UpdateEprom, Line 34
    Incorrect syntax near '='.
    Last edited by Wimmo; 04-16-06 at 09:47.

  4. #4
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    This is the table on which the sp should update:

    Code:
    USE [Ebdata]
    GO
    /****** Object:  Table [dbo].[EB_Eprom]    Script Date: 04/16/2006 14:48:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[EB_Eprom](
    	[EpromId] [bigint] IDENTITY(100,5) NOT NULL,
    	[Naam_Spel] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    	[Versie] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    	[Checksum1] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    	[Checksum2] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    	[Omschrijving] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
    	[Datum_vrijgave] [datetime] NULL,
    	[EpromType] [int] NULL CONSTRAINT [DF_EB_Eprom_EpromType]  DEFAULT ((7)),
    	[Land] [bigint] NULL CONSTRAINT [DF_EB_Eprom_Land]  DEFAULT ((0)),
    	[Kabinet] [int] NULL CONSTRAINT [DF_EB_Eprom_Kabinet]  DEFAULT ((0)),
    	[Merk] [int] NULL CONSTRAINT [DF_EB_Eprom_Merk]  DEFAULT ((0)),
    	[Wet] [int] NULL CONSTRAINT [DF_EB_Eprom_Wet]  DEFAULT ((0)),
    	[EpromFile] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
    	[EpromSize] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
    	[SourceFile] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
    	[SourceSize] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
    	[Active] [int] NULL CONSTRAINT [DF_EB_Eprom_Active]  DEFAULT ((2)),
    	[CreateDate] [datetime] NULL CONSTRAINT [DF_EB_Eprom_CreateDate]  DEFAULT (getdate()),
    	[CreatorID] [uniqueidentifier] NULL,
    	[UpdateUser] [uniqueidentifier] NULL,
    	[UpdateDate] [datetime] NULL,
    	[Downloaded] [bigint] NULL,
    	[SourceDownloaded] [bigint] NULL,
     CONSTRAINT [PK_EB_Eprom] PRIMARY KEY CLUSTERED 
    (
    	[EpromId] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    USE [Ebdata]
    GO
    ALTER TABLE [dbo].[EB_Eprom]  WITH CHECK ADD  CONSTRAINT [FK_EB_Eprom_Alg_Landen] FOREIGN KEY([Land])
    REFERENCES [dbo].[Alg_Landen] ([CountryCode])
    GO
    ALTER TABLE [dbo].[EB_Eprom]  WITH NOCHECK ADD  CONSTRAINT [FK_EB_Eprom_aspnet_Users2] FOREIGN KEY([CreatorID])
    REFERENCES [dbo].[aspnet_Users] ([UserId])
    NOT FOR REPLICATION 
    GO
    ALTER TABLE [dbo].[EB_Eprom]  WITH NOCHECK ADD  CONSTRAINT [FK_EB_Eprom_aspnet_Users3] FOREIGN KEY([UpdateUser])
    REFERENCES [dbo].[aspnet_Users] ([UserId])
    NOT FOR REPLICATION 
    GO
    ALTER TABLE [dbo].[EB_Eprom]  WITH CHECK ADD  CONSTRAINT [FK_EB_Eprom_EB_Active] FOREIGN KEY([Active])
    REFERENCES [dbo].[EB_Active] ([ActiveID])
    GO
    ALTER TABLE [dbo].[EB_Eprom]  WITH CHECK ADD  CONSTRAINT [FK_EB_Eprom_EB_EpromType] FOREIGN KEY([EpromType])
    REFERENCES [dbo].[EB_EpromType] ([TypeID])
    GO
    ALTER TABLE [dbo].[EB_Eprom]  WITH CHECK ADD  CONSTRAINT [FK_EB_Eprom_EB_Kabinet] FOREIGN KEY([Kabinet])
    REFERENCES [dbo].[EB_Kabinet] ([KabinetID])
    GO
    ALTER TABLE [dbo].[EB_Eprom]  WITH CHECK ADD  CONSTRAINT [FK_EB_Eprom_EB_Merk] FOREIGN KEY([Merk])
    REFERENCES [dbo].[EB_Merk] ([MerkId])
    GO
    ALTER TABLE [dbo].[EB_Eprom]  WITH CHECK ADD  CONSTRAINT [FK_EB_Eprom_EB_Wet] FOREIGN KEY([Wet])
    REFERENCES [dbo].[EB_Wet] ([WetId])

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The MAX shows in your CREATE TABLE statement too. I suspect that the table structure is somehow corrupt, even though I can't explain why... The table shouldn't even exist as far as I can tell!

    Try to rebuild the table structure using something other than MAX to see if that fixes the problem.

    -PatP

  6. #6
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Quote Originally Posted by Pat Phelan
    The MAX shows in your CREATE TABLE statement too. I suspect that the table structure is somehow corrupt, even though I can't explain why... The table shouldn't even exist as far as I can tell!

    Try to rebuild the table structure using something other than MAX to see if that fixes the problem.

    -PatP
    I don't understand either because i created the table visual using SQL Server management studio where the nvarchar(max) is a default datatype and
    varchar(MAX) and Varbinary(MAX)


    but changed it to 4000 and still the same error keeps coming back!

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, brain fart on my part. I was thinking in SQL 2000 terms, not SQL 2005.

    I can't connect to a machine with SQL 2005 on it right now, so I can't test your code. Maybe someone else can help before I can connect, if not I'll check it out later.

    Sorry for my confusion.

    -PatP

  8. #8
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    No probs, thought you were thinking sql 2000!
    But as far as you can see the syntax isn't wrong right?

  9. #9
    Join Date
    Jan 2004
    Location
    Tacoma, Wa
    Posts
    15
    This might be a problem (double =)


    LAnd = =@Land,

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Have you tried putting parentheses around the parameter list, so:
    Code:
    CREATE PROC UpdateEprom
    
    
    		  ( @EpromID bigint,
    		   @Naam_Spel  varchar(50),
               @Versie  varchar(10),
               @Checksum1 varchar(8),
               @Checksum2 varchar(8),
               @Omschrijving varchar(MAX),
               @Datum_vrijgave datetime,
               @EpromType int,
               @Land varchar (50),
               @Kabinet varchar(50),
               @Merk int,
               @Wet int,
               @EpromFile varchar(100),
               @EpromSize varchar(100),
               @SourceFile varchar(100),
               @SourceSize varchar(100),
               @Active int,
               @UpdateUser uniqueidentifier)
    
    		AS
    		update EB_Eprom
    		SET 
    			Naam_Spel = @Naam_Spel,
    			Versie = @Versie,
    			Checksum1 = @Checksum1,
    			Checksum2 = @Checksum2,
    			Omschrijving = @Omschrijving,
    			Datum_Vrijgave = @Datum_Vrijgave,
    			EpromType = @EpromType,
    			LAnd = =@Land,
    			Kabinet = @Kabinet,
    			Merk = @Merk,
    			Wet = @wet,
    			EpromFile = @EpromFile,
    			EpromSize = @EpromSize,
    			SourceFile = @SourceFile,
    			SourceSize = @SourceSize,
    			Active = @Active,
    			UpdateUser = @UpdateUser,
    			UpdateDate = getdate()
    
    
    
    			where EpromID = @EpromID

  11. #11
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Quote Originally Posted by revdrwebb
    This might be a problem (double =)


    LAnd = =@Land,
    Thank you very much, i have to quit doing these things on an eastern morning!
    I am a bit ashamed know!

    Cheers Wim

    Thanx guys for all the help!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •