Friday, March 9, 2012

Problem when creating SP in sql2005

Hi All,

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

Naam_Spel = @.Naam_Spel,

probably it is something stupid but i keep staring at it frustrates me!

heres the complete sp:

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 WimmoMAX isn't a valid dimension as used in @.Omschrijving varchar(MAX),-PatP|||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 '='.|||This is the table on which the sp should update:

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])|||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|||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!|||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|||No probs, thought you were thinking sql 2000!
But as far as you can see the syntax isn't wrong right?|||This might be a problem (double =)

LAnd = =@.Land,|||Have you tried putting parentheses around the parameter list, so:

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|||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!!

No comments:

Post a Comment